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
Comment and Contribute
» 
IE 7
Firefox 2.0
