Oracle SQL response time monitoring #sql #monitoring, #oracle #sql #response #time #monitoring


#

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:

select *
from
(select
sql_text,
sql_id,
elapsed_time,
cpu_time,
user_io_wait_time
from
sys.v_$sqlarea
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).

For example, let’s say you’ve found a particular SQL statement that appears to be extremely deficient in terms of user I/O wait time. You can issue the following query to get the individual wait events associated with the query along with the corresponding wait times, files, and objects that were the source of those waits:

select
event,
time_waited,
owner,
object_name,
current_file#,
current_block#
from
sys.v_$active_session_history a,
sys.dba_objects b
where
sql_id = ‘6gvch1xu9ca3g’
and
a.current_obj# = b.object_id
and
time_waited 0;

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
—————————————– ——– ————–
SQL_TEXT VARCHAR2(1000)
SHARABLE_MEM NUMBER
PERSISTENT_MEM NUMBER
RUNTIME_MEM NUMBER
SORTS NUMBER
VERSION_COUNT NUMBER
LOADED_VERSIONS NUMBER
OPEN_VERSIONS NUMBER
USERS_OPENING NUMBER
EXECUTIONS NUMBER
USERS_EXECUTING NUMBER
LOADS NUMBER
FIRST_LOAD_TIME VARCHAR2(19)
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
BUFFER_GETS NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
OPTIMIZER_MODE VARCHAR2(25)
PARSING_USER_ID NUMBER
PARSING_SCHEMA_ID NUMBER
KEPT_VERSIONS NUMBER
ADDRESS RAW(4)
HASH_VALUE NUMBER
MODULE VARCHAR2(64)
MODULE_HASH NUMBER
ACTION VARCHAR2(64)
ACTION_HASH NUMBER
SERIALIZABLE_ABORTS NUMBER
CPU_TIME NUMBER
ELAPSED_TIME NUMBER
IS_OBSOLETE VARCHAR2(1)

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

  • PERSISTENT_MEM — Amount of persistent memory used by the statement
  • RUNTIME_MEM — Amount of runtime memory used by the statement
  • SORTS — Total sorts across all executions
  • EXECUTIONS — Total executions
  • PARSE_CALLS — Total parse calls across all executions
  • DISK_READS — Total disk reads across all executions
  • BUFFER_GETS — Total buffer gets across all executions
  • ROWS_PROCESSED — Total rows processed over all executions
  • OPTIMIZER_MODE — Optimizer mode used by statement
  • SERIALIZABLE_ABORTS — Number of serializable aborts over all executions
  • CPU_TIME — Total CPU time used for all executions
  • 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(
    SELECT
    sql_text,
    ceil(cpu_time/greatest(executions,1)) ave_cpu_time,
    ceil(elapsed_time/greatest(executions,1)) ave_elapsed_time,
    ceil(disk_reads/greatest(executions,1)) ave_disk_reads,
    persistent_mem per_mem, runtime_mem run_mem,
    ceil(sorts/greatest(executions,1)) ave_sorts,
    ceil(parse_calls/greatest(executions,1)) ave_parse_calls,ceil(Buffer_gets/greatest(executions,1)) ave_buffer_gets,
    ceil(rows_processed/greatest(executions,1)) ave_row_proc,
    ceil(Serializable_aborts/greatest(executions,1)) ave_ser_aborts
    FROM
    v$sqlarea
    WHERE
    Disk_reads/greatest(executions,1) disk_reads
    OR
    Cpu_time/greatest(executions,1) cpu_time
    OR
    Elapsed_time/greatest(executions,1) elapsed_time
    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.


    Add a Comment

    Your email address will not be published. Required fields are marked *