Oracle SQL response time monitoring
SQL Advisors for response time
SQL Response-Time Analysis
Examining the response time of SQL statements became easier in Oracle9i, and with Oracle Database 10g, DBAs have many tools at their disposal to help them track inefficient database code.
Historically the applicable V$ view here has been V$SQLAREA. Starting with Oracle9i, Oracle added the ELAPSED_TIME and CPU_TIME columns, which have been a huge help in determining the actual end user experience of a SQL statement execution (at least, when dividing them by the EXECUTIONS column, which produces the average amount of time per execution).
In Oracle Database 10g, six new wait-related and timing columns have been added to V$SQLAREA:
The new columns are helpful in determining, for example, the amount of time that a procedure spends in PL/SQL code vs. standard SQL execution, and if a SQL statement has experienced any particular user I/O waits. For example, a query you could use to find the top five SQL statements with the highest user I/O waits would be:
order by 5 desc)
where rownum 6;
SQL_TEXT SQL_ID ELAPSED_TIME CPU_TIME USER_IO_WAIT_TIME
————————- ———— ———— ———- —————
select /*+ rule */ bucket db78fxqxwxt7 47815369 19000939 3423
SELECT : SYS_B_0 FROM SY agdpzr94rf6v 36182205 10170226 2649
select obj#,type#,ctime,m 04xtrk7uyhkn 28815527 16768040 1345
select grantee#,privilege 2q93zsrvbdw4 28565755 19619114 803
select /*+ rule */ bucket 96g93hntrzjt 9411028 3754542 606
Of course, getting the SQL statements with the highest elapsed time or wait time is good, but you need more detail to get to the heart of the matter?which is where the V$ACTIVE_SESSION_HISTORY view again comes into play. With this view, you can find out what actual wait events delayed the SQL statement along with the actual files, objects, and object blocks that caused the waits (where applicable).
sql_id = ‘6gvch1xu9ca3g’
a.current_obj# = b.object_id
EVENT TIME_WAITED OWNER OBJECT_NAME file block
————————- ———– —— ——————— —- ——
db file sequential read 27665 SYSMAN MGMT_METRICS_1HOUR_PK 3 29438
db file sequential read 3985 SYSMAN SEVERITY_PRIMARY_KEY 3 52877
Of course, you can use V$ACTIVE_SESSION_HISTORY in a historical fashion to narrow down un-optimized SQL statements for a particular time period. The point is that Oracle Database 10g makes it a lot easier to conduct response-time analysis on SQL statements with simplified data dictionary views, as opposed to the time-consuming trace-and-digest method.
DBAs and performance analysts who manage the performance of Oracle Database 10g will find many of the response-time metrics they’ve yearned for over the years now at their fingertips in the latest release of Oracle’s flagship database. Such statistics will help accelerate the process of finding the proverbial needle in the haystack of large and complex database performance situations.
The V$SQLAREA view provides information about the SQL that is being currently executed or has been executed for the database. The V$SQLAREA view shows all SQL, even if it has been embedded into PL/SQL code or is sent to the database via an external program.
The V$SQLAREA and its companion view V$SQLTEXT provide the text and statistics for each piece of SQL code. The contents of the V$SQLAREA view are shown in Figure 2.
Name Null? Type
—————————————– ——– ————–
Notice that the V$SQLAREA only shows the first 1000 bytes of any SQL. If the SQL is longer than 1000 bytes the V$SQLTEXT view should be used to see the entire text. The critical statistics in this view for tuning code are
SHARABLE_MEM — Amount o sharable memory used by the SQL statement
ELAPSED_TIME — Total elapsed time for all executions
You should notice that most of the statistics are based on total number of executions. It will probably be more meaningful for you to generate a report based on average values for these statistics over all executions as shown in the code section in Figure 3.
SELECT * FROM(
persistent_mem per_mem, runtime_mem run_mem,
ceil(parse_calls/greatest(executions,1)) ave_parse_calls,ceil(Buffer_gets/greatest(executions,1)) ave_buffer_gets,
order by elapsed_time, cpu_time, disk_reads)
where rownum 11
Of course if you aren’t interested in all of the stats the list of columns can be pared down to only those with which you have a concern.
More information on Oracle response time components is available HERE .
Burleson is the American Team
Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum .
Verifyexperience!Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications .
Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail: and include the URL for the page.
The Oracle of Database Support
Copyright 1996 – 2016
All rights reserved by Burleson
Oracle is the registered trademark of Oracle Corporation.