Watching SQL Execute on Oracle - Part II - Page 2

July 9, 2004

V$SQL_OPTIMIZER_ENV

During the course and life of a database, applications and sessions typically will begin to alter their environments to optain the best performance. It can sometimes be very hard to track down anyone with this information, let alone extract the information from either in-house or third party code. For this reason you can use the V$SQL_OPTIMIZER_ENV view to pull from memory the actual optimizer environment the individual SQL is executing. Table 3 shows the subset of columns that are used from this view in order to see the values for the optimizer's environment. Listing 3 shows the SQL to do this join for active SQL and a subset of the output it generates.

Table 3
V$SQL_OPTIMIZER_ENV column information

Column	Description
`ADDRESS`	This is the address to the parent of this cursor/sql
`HASH_VALUE`	This is the hash value to the parent statement in the library cache
`NAME`	Parameter name
`ISDEFAULT`	Show if the parameter is set to its default value.
`VALUE`	Parameter value

Listing 3
Extracting the optimizer environment settings for SQL that is executing

select sesion.sid,
       sesion.username,
       name,
       isdefault,
       value
  from v$sql_optimizer_env sql_optimizer_env, v$session sesion
 where sesion.sql_hash_value = sql_optimizer_env.hash_value
   and sesion.sql_address    = sql_optimizer_env.address
   and sesion.username is not null

Optimizer environment settings for current active SQL
SID USERNAME     NAME                                     ISD VALUE
---- ------------ ---------------------------------------- --- ----------
149 JKOOPMANN    parallel_execution_enabled               YES true
149 JKOOPMANN    optimizer_features_enable                YES 10.1.0
149 JKOOPMANN    cpu_count                                YES 2
149 JKOOPMANN    active_instance_count                    YES 1
149 JKOOPMANN    parallel_threads_per_cpu                 YES 2
149 JKOOPMANN    hash_area_size                           YES 131072
149 JKOOPMANN    bitmap_merge_area_size                   YES 1048576
149 JKOOPMANN    sort_area_size                           YES 65536
149 JKOOPMANN    sort_area_retained_size                  YES 0

Extract the Execution Plan

If you are tired of extracting the raw SQL_TEXT and formatting it and then running it through the old method of producing explain plans, this section is for you. There is an Oracle object called DBMS_XPLAN that allows you to supply the SQL_ID and SQL_CHILD_NUMBER and have it produce a nicely formatted explain output. Listing 4 gives an example SQL query that uses the SQL_ID and SQL_CHILD_NUMBER we obtained earlier from V$SESSION and gives the output of the query.

Listing 4
Extracting the optimizer environment settings for SQL that is executing

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('5qk509xugpmpv'),1));

Explain output

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

SQL_ID  5qk509xugpmpv, child number 1
-------------------------------------
SELECT count(*) FROM sys.dba_tables WHERE owner = :1

Plan hash value: 580517646

----------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name             | Rows  | Bytes | Cost (%CPU)|Time|
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                  |       |       |   242 (100)|    |
|   1 |  SORT AGGREGATE                       |                  |     1 |   149 |            |    |
|   2 |   NESTED LOOPS                        |                  |     1 |   149 |   242   (1)| 03 |
|   3 |    MERGE JOIN CARTESIAN               |                  |     1 |   136 |   218   (1)| 03 |
|   4 |     NESTED LOOPS OUTER                |                  |     1 |    81 |   194   (1)| 03 |
|   5 |      NESTED LOOPS OUTER               |                  |     1 |    78 |   193   (1)| 03 |
|   6 |       NESTED LOOPS OUTER              |                  |     1 |    70 |   192   (1)| 03 |
|   7 |        NESTED LOOPS OUTER             |                  |     1 |    59 |   191   (1)| 03 |
|   8 |         NESTED LOOPS                  |                  |     1 |    54 |   191   (1)| 03 |
|*  9 |          HASH JOIN                    |                  |     8 |   368 |   183   (1)| 03 |
|  10 |           NESTED LOOPS                |                  |     7 |   119 |     4   (0)| 01 |
|  11 |            TABLE ACCESS BY INDEX ROWID| USER$            |     1 |    14 |     1   (0)| 01 |
|* 12 |             INDEX UNIQUE SCAN         | I_USER1          |     1 |       |     0   (0)|    |
|  13 |            TABLE ACCESS FULL          | TS$              |     7 |    21 |     3   (0)| 01 |
|* 14 |           TABLE ACCESS FULL           | TAB$             |    16 |   464 |   178   (0)| 03 |
|* 15 |          TABLE ACCESS BY INDEX ROWID  | OBJ$             |     1 |     8 |     1   (0)| 01 |
|* 16 |           INDEX UNIQUE SCAN           | I_OBJ1           |     1 |       |     0   (0)|    |
|* 17 |         INDEX UNIQUE SCAN             | I_OBJ1           |     1 |     5 |     0   (0)|    |
|  18 |        TABLE ACCESS CLUSTER           | SEG$             |     1 |    11 |     1   (0)| 01 |
|* 19 |         INDEX UNIQUE SCAN             | I_FILE#_BLOCK#   |     1 |       |     0   (0)|    |
|  20 |       TABLE ACCESS BY INDEX ROWID     | OBJ$             |     1 |     8 |     1   (0)| 01 |
|* 21 |        INDEX UNIQUE SCAN              | I_OBJ1           |     1 |       |     0   (0)|    |
|  22 |      TABLE ACCESS CLUSTER             | USER$            |     1 |     3 |     1   (0)| 01 |
|* 23 |       INDEX UNIQUE SCAN               | I_USER#          |     1 |       |     0   (0)|    |
|  24 |     BUFFER SORT                       |                  |     1 |    55 |   217   (1)| 03 |
|* 25 |      FIXED TABLE FULL                 | X$KSPPI          |     1 |    55 |    24   (0)| 01 |
|* 26 |    FIXED TABLE FIXED INDEX            | X$KSPPCV (ind:2) |     1 |    13 |    24   (0)| 01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   9 - access("T"."TS#"="TS"."TS#")
  12 - access("U"."NAME"=:1)
  14 - filter(BITAND("T"."PROPERTY",1)=0)
  15 - filter("O"."OWNER#"="U"."USER#")
  16 - access("O"."OBJ#"="T"."OBJ#")
  17 - access("T"."BOBJ#"="CO"."OBJ#")
  19 - access("T"."TS#"="S"."TS#" AND "T"."FILE#"="S"."FILE#" AND "T"."BLOCK#"="S"."BLOCK#")
  21 - access("T"."DATAOBJ#"="CX"."OBJ#")
  23 - access("CX"."OWNER#"="CU"."USER#")
  25 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
  26 - filter("KSPPI"."INDX"="KSPPCV"."INDX")

`V$SQL_PLAN_STATISTICS`

Typically when looking at execution statistics for a SQL statement, DBAs will go to the V$SQLAREA view. The only problem with going to the V$SQLAREA view is that it is an accumulation of statistics for a particular SQL statement. If you want single run statistics for an SQL statement then you can go to the V$SQL_PLAN_STATISTICS view as there are columns in this view that report on the last time the statement was executed. The exciting thing about this view is that it tells you the actual statistics for each step in the execution plan. Table 5 shows a subset of the columns for this view. More than these columns are of importance and you should venture to take a look at them. Listing 5 gives an example of how to extract the statistics for the active executing SQL by joining to the V$SESSION view.

Table 5
Subset of columns for V$SQL_PLAN_STATISTICS

Column	Description
`ADDRESS`	This is the address to the parent of this cursor/sql
`HASH_VALUE`	This is the hash value to the parent statement in the library cache
`PLAN_HASH_VALUE`	Plan hash value
`OPERATION_ID`	The number for each step in the execution plan
`OUTPUT_ROWS`	The number of rows returned
`LAST_CR_BUFFER_GETS`	Number of consistent gets for the last run of the plan for a given step.
`LAST_DISK_READS`	Number of physical disk reads for the last run of the plan for a given step

Listing 5
Extracting the statistics for a single execution of a SQL statement

select sesion.sid,
       sesion.username,
       sql_plan_statistics.operation_id        "Id",
       sql_plan_statistics.last_output_rows    "Rows",
       sql_plan_statistics.last_cr_buffer_gets "Consistent Gets",
       sql_plan_statistics.last_disk_reads     "Disk Reads"
  from v$sql_plan_statistics sql_plan_statistics, v$session sesion
 where sesion.sql_hash_value = sql_plan_statistics.hash_value
   and sesion.sql_address    = sql_plan_statistics.address
   and sesion.username is not null

Explain output

SID USERNAME               Id       Rows Consistent Gets Disk Reads
--- -------------- ---------- ---------- --------------- ----------
149 JKOOPMANN               1          1            8594          0
149 JKOOPMANN               2        643            8594          0
149 JKOOPMANN               3        643            8594          0
149 JKOOPMANN               4        643            8594          0
149 JKOOPMANN               5        643            7540          0
149 JKOOPMANN               6        643            6369          0
149 JKOOPMANN               7        643            4614          0
149 JKOOPMANN               8        643            3969          0
149 JKOOPMANN               9       1572             823          0
149 JKOOPMANN              10          7              11          0
149 JKOOPMANN              11          1               2          0
149 JKOOPMANN              12          1               1          0
149 JKOOPMANN              13          7               9          0
149 JKOOPMANN              14       1572             812          0
149 JKOOPMANN              15        643            3146          0
149 JKOOPMANN              16       1572            1574          0
149 JKOOPMANN              17         50             645          0
149 JKOOPMANN              18        555            1755          0
149 JKOOPMANN              19        555             645          0
149 JKOOPMANN              20        526            1171          0
149 JKOOPMANN              21        526             645          0
149 JKOOPMANN              22        526            1054          0
149 JKOOPMANN              23        526               2          0
149 JKOOPMANN              24        643               0          0
149 JKOOPMANN              25          1               0          0
149 JKOOPMANN              26        643               0          0

Determining what SQL is executing within the Oracle engine and watching its access path and the true statistical values accumulated for each run are invaluable. Give these scripts a try, add to them, and you too can begin to finally determine what SQL is executing within your environment.

» See All Articles by Columnist James Koopmann

Go to page: Prev 1 2

Watching SQL Execute on Oracle - Part II - Page 2

V$SQL_OPTIMIZER_ENV

Extract the Execution Plan

`V$SQL_PLAN_STATISTICS`

Whitepapers and eBooks

Webcasts

Downloads and eKits

Tutorials and Demos