The utility script xplan dumps all the most important informations about an SQL statement that are needed for Performance Tuning, in a concise report file (or files).
In detail, it fetches and prints from the library cache the SQL statements whose text matches a given "like" expression (or hash_value, sql_id, etc.); for every statement, it prints its associated plan, execution statistics, row source operation statistics, SQL environment, peeked binds values, etc.
Most importantly, for every table accessed by the statement, it prints the definition of its columns, indexes, constraints, partitions - together with the CBO-related statistics of all of them.
As of version 2.0, it also prints the wait event profile from ASH (Active Session History), and definitions of referenced views, packages, triggers, sequences, etc.
Xplan is fully documented in the main script (xplan.sql) header.
Here is a run example on Oracle 11g (from demo script xplan_showcase.sql):
SQL> @xplan "%xplan_test_marker%" ""
Misc database infos :
xplan version 2.0.1 08-Aug-2009 (C) Copyright 2008-2009 Alberto Dell'Era, www.adellera.it
db_name=oracle11 instance_name=oracle11g version=11.1.0.7.0 (compatible = 11.1.0.0.0)
Instance CBO-related parameters :
optimizer parameters instance(sys) settings:
------------------------------------------------- --------------------------------------------- ---------------------------------------------
|optimizer param name |value | |optimizer param name |value | |optimizer param name |value |
------------------------------------------------- --------------------------------------------- ---------------------------------------------
|active_instance_count | 1 | |optimizer_index_caching | 0 | |parallel_threads_per_cpu | 2 |
|bitmap_merge_area_size | 1048576 | |optimizer_index_cost_adj | 100 | |pga_aggregate_target | 104448 KB |
|cell_offload_compaction |ADAPTIVE | |optimizer_mode |all_rows | |query_rewrite_enabled | true |
|cell_offload_plan_display | AUTO | |optimizer_secure_view_merging | true | |query_rewrite_integrity | enforced |
|cell_offload_processing | true | |optimizer_use_invisible_indexes | false | |result_cache_mode | MANUAL |
|cpu_count | 1 | |optimizer_use_pending_statistics | false | |skip_unusable_indexes | true |
|cursor_sharing | exact | |optimizer_use_sql_plan_baselines | true | |sort_area_retained_size | 0 |
|db_file_multiblock_read_count | 46 | |parallel_ddl_mode | enabled | |sort_area_size | 65536 |
|hash_area_size | 131072 | |parallel_degree | 0 | |star_transformation_enabled | false |
|is_recur_flags | 0 | |parallel_dml_mode |disabled | |statistics_level | typical |
|optimizer_capture_sql_plan_baselines | false | |parallel_execution_enabled | true | |transaction_isolation_level |read_commited |
|optimizer_dynamic_sampling | 2 | |parallel_query_default_dop | 0 | |workarea_size_policy | auto |
|optimizer_features_enable |11.1.0.7 | |parallel_query_mode | enabled | ---------------------------------------------
------------------------------------------------- ---------------------------------------------
CBO system statistics :
optimizer system statistics:
---------------------------------------- -------------------------- --------------------------
|system statistic |value | |system statistic |value | |system statistic |value |
---------------------------------------- -------------------------- --------------------------
|status | completed | |cpuspeednw | 1534 | |ioseektim | 10 |
|gathering start |2009-08-09/18:37:00 | |sreadtim | 14 | |iotfrspeed | 4096 |
|gathering stop |2009-08-09/18:39:00 | |mreadtim | 13 | |maxthr | null |
|cpuspeed | 406 | |mbrc | 6 | |slavethr | null |
---------------------------------------- -------------------------- --------------------------
Misc statement infos :
sql_id=cdcyga72r9f01 hash=3312760833 child_number=0 plan_hash=4202265887 module=SQL*Plus
first_load: 2009/08/06 17:19:34 last_load: 2009/08/06 17:19:34 last_active: 2009/08/06 17:19:38
parsed_by=DELLERA inst_id=1
Statistics from v$sql ( /exec is the stat value divided by v$sql.executions) :
-------------------------------------- -------------------------------- ----------------------------------------------
|gv$sql statname |total |/exec | |gv$sql statname |total |/exec | |gv$sql statname |total |/exec |
-------------------------------------- -------------------------------- ----------------------------------------------
|executions | 1 | | |sorts | 1 | 1.0 | |users_executing | 0 | .0 |
|rows_processed | 1 | 1.0 | |fetches | 1 | 1.0 | |application wait (usec) | 0 | .0 |
|buffer_gets | 65227 | 65227.0 | |end_of_fetch_c | 1 | 1.0 | |concurrency wait (usec) | 0 | .0 |
|disk_reads | 229 | 229.0 | |parse_calls | 1 | 1.0 | |cluster wait (usec) | 0 | .0 |
|direct_writes | 0 | .0 | |sharable_mem |28406 | | |user io wait (usec) |1487546 |1487546.0 |
|elapsed (usec) |4215437 |4215437.0 | |persistent_mem |29032 | | |plsql exec wait (usec) | 18 | 18.0 |
|cpu_time (usec) |2663831 |2663831.0 | |runtime_mem |28028 | | |java exec wait (usec) | 0 | .0 |
-------------------------------------- -------------------------------- ----------------------------------------------
Statement text:
SELECT /*+ index(t,t_fbi) ordered use_nl(v) xplan_test_marker */ T.RR, PLSQL_FUNC(MAX(T.X)) FROM T, V WHERE UPPER(T.X) >= '0' AND T.X > :B1 AND V.RR
='x' GROUP BY T.RR ORDER BY T.RR
Names of non-table objects depended on (from v$object_dependency) - full definition at the bottom of the report :
- depends on view DELLERA.V
- depends on function DELLERA.PLSQL_FUNC
Peeked binds values:
peeked binds values: :B1 = 0
peeked binds types : :B1 = number(22)
Plan (format similar to dbms_xplan one) :
----------------------------------------------------------------------------------------
|Id|Operation |Name |TabName|Erows |Arows |Cost |IoCost|Psta|Psto|IdP|
-----------------------------------------------------last-------------------------------
| 0|SELECT STATEMENT | | | | 1|12908| | | | |
| 1| SORT GROUP BY | | | 1| 1|12908| 12828| | | |
| 2| NESTED LOOPS | | |999500|1000000|12858| 12828| | | |
| 3| PARTITION RANGE ITERATOR| | | 1000| 1000| 97| 97|KEY |3 | 3|
| 4| INDEX UNIQUE SCAN |T_PK |T | 1000| 1000| 97| 97|KEY |3 | 3|
| 5| INDEX RANGE SCAN |T_FBI|T | 64| 1000| 50| 50|KEY |3 | 3|
| 6| PARTITION RANGE ALL | | | 1000|1000000| 13| 13|1 |3 | 6|
| 7| INDEX FAST FULL SCAN |T_PK |T | 1000|1000000| 13| 13|1 |3 | 6|
----------------------------------------------------------------------------------------
. 4 - access[ "T"."X">:B1 AND "T"."SYS_NC00004$">='0' ]
. 5 - access[ "T"."X">:B1 AND "T"."SYS_NC00004$">='0' ]
. - filter[ "T"."SYS_NC00004$">='0' ]
. 7 - filter[ ("RR"='x' AND "X">0) ]
Wait event profile (from ASH):
----------------------------------
|ash event |cnt|% |
----------------------------------
|db file sequential read| 2|66.7|
|cpu | 1|33.3|
----------------------------------
Main plan statistics :
CR=Consistent Reads, CU=CUrrent reads, DR=Disk Reads, DW=Disk Writes, etc.
---------------------------------------------------------------------------------------
|Id|Starts|CR |CU |DR |DW |Ela |E0ram|E1ram|Aram|Policy|A01M |0/1/M|ActTim |
----last---last--last-last-last-last----------------last--------last----------avg------
| 0| 1|63132| 0| 103| 0|2128785| | | | | | | |
| 1| 1|63132| 0| 103| 0|2128785| 3072| 3072|2048|MANUAL|OPTIMAL|1/0/0|24498190|
| 2| 1|63132| 0| 103| 0|1020064| | | | | | | |
| 3| 1| 1050| 0| 51| 0| 199885| | | | | | | |
| 4| 3| 1050| 0| 51| 0| 200522| | | | | | | |
| 5| 3| 50| 0| 50| 0| 139705| | | | | | | |
| 6| 1000|62082| 0| 52| 0|1039088| | | | | | | |
| 7| 3000|62082| 0| 52| 0| 60188| | | | | | | |
--------------------------------usec----KB----KB----KB------------------#-----msec-----
note: stats Aram, A01M, 0/1/M, ActTim do not seem to be always accurate.
Additional plan details:
-------------------------------------------------------------------------------------------------------------------------------
|Id|Qb_name |ObjAlias|ObjType |Obj# |BaseObj#|Projection |
-------------------------------------------------------------------------------------------------------------------------------
| 0| | | | | | |
| 1|SEL$F5BB74E1| | | | |(#keys=1) "T"."RR"[VARCHAR2,1], MAX("T"."X")[22] |
| 2| | | | | |"T"."X"[NUMBER,22], "T"."RR"[VARCHAR2,1] |
| 3| | | | | |"T"."X"[NUMBER,22], "T"."RR"[VARCHAR2,1] |
| 4|SEL$F5BB74E1|T@SEL$1 |INDEX (UNIQUE)|74676| 74668|"T"."X"[NUMBER,22], "T"."RR"[VARCHAR2,1] |
| 5|SEL$F5BB74E1|T@SEL$1 |INDEX |74685| 74668|"T".ROWID[ROWID,1234], "T"."X"[NUMBER,22], "PADDING"[VARCHAR2,1200], "|
| 6| | | | | | |
| 7|SEL$F5BB74E1|T@SEL$2 |INDEX (UNIQUE)|74676| 74668| |
-------------------------------------------------------------------------------------------------------------------------------
CBO-related parameters different from instance ones:
WARNING: 6 params in gv$sql_optimizer_env are not the same as instance ones:
---------------------------------- -------------------------------- -------------------------------
|optimizer param name |value | |optimizer param name |value | |optimizer param name |value |
---------------------------------- -------------------------------- -------------------------------
|_smm_auto_cost_enabled | false | |sort_area_size |2000000 | |statistics_level | all |
|hash_area_size |2000000 | |sqlstat_enabled | true | |workarea_size_policy |manual |
---------------------------------- -------------------------------- -------------------------------
Accessed table(s) informations :
############################################# table DELLERA.T ###
PARTITIONED BY RANGE ( X, PADDING )
IOT
Accessed table columns definitions and constraints :
Note that the FBI expression for hidden columns is provided
Note the concise index/constraint report on the right
E.g. Index #4 is a Unique index on (X,PADDING)
Index #1 is a non-unique index on (X, UPPER(TO_CHAR("X")), PADDING )
Primary Key is on (X,PADDING)
Unique Constraint U2 is on (PADDING,X)
Unique Constraint U1 is referenced (R) by some FK from another table
Foreign Key R1 is from column RR
-------------------------------------------------------------------------------
|Id|IId|ColName |Type |Null|Expression|1|2|3|4|5|P|U1|U2|R1|
-----------------------------------------------trunc------------U-U------------
| 1| 1|X |NUMBER |NOT | |1|1|2|1| |1| |2 | |
| 2| 2|PADDING |VARCHAR2 (1200 byte)|NOT | |3| |1|2|1|2|R1|1 | |
| 3| 3|RR |VARCHAR2 (1 byte) |yes | | | | | | | | | |1 |
| | 4|SYS_NC00004$|VARCHAR2 (40 byte) |yes |UPPER(TO_C|2| | | | | | | | |
| | 5|SYS_NC00005$|VARCHAR2 (6 byte) |yes |CASE "X" W| |2| | | | | | | |
-------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------
|ColName |Expression (full) |
---------------------------------------------------------------------------------------------------------------------------
|SYS_NC00004$|UPPER(TO_CHAR("X")) |
|SYS_NC00005$|CASE "X" WHEN 0 THEN 'pippo' WHEN 1 THEN 'uuiio' WHEN 3 THEN 'uuciio' WHEN 4 THEN 'uuieio' ELSE 'pppppp' END|
---------------------------------------------------------------------------------------------------------------------------
Accessed table CBO statistics (for partitions too) :
-------------------------------------------------------------------------------
|Pid|Partition|num_rows|avg_row_len|sample_size|last_analyzed |parallel |
-------------------------------------------------------------------------------
| | | 1000| 317| 1000|2009/08/06 17:19:27| 1|
| 1|P1 | 100| 315| 100|2009/08/06 17:19:27| |
| 2|P2 | 100| 317| 100|2009/08/06 17:19:27| |
| 3|POTHER | 800| 317| 800|2009/08/06 17:19:27| |
-------------------------------------------------------------------------------
Accessed table columns CBO statistics (for partitions too) :
---------------------------------------------------------------------------------------------------------
|ColName |Partition|ndv |dens*#rows|num_nulls|#bkts|hist|avg_col_len|sample_size|last_analyzed |
---------------------------------------------------------------------------------------------------------
|X | |1000| 1.0| 0| 254|HB | 4| 1000|2009/08/06 17:19:27|
|PADDING | |1000| 1.0| 0| 254|HB | 301| 1000|2009/08/06 17:19:27|
|RR | | 1| 0.5| 0| 1|FREQ| 2| 1000|2009/08/06 17:19:27|
|SYS_NC00004$| |1000| 1.0| 0| 254|HB | 4| 1000|2009/08/06 17:19:27|
|SYS_NC00005$| | 4| 0.5| 0| 4|FREQ| 7| 1000|2009/08/06 17:19:27|
|X |P1 | 100| 5.0| 0| 100|HB | 3| 100|2009/08/06 17:19:27|
|X |P2 | 100| 5.0| 0| 100|HB | 4| 100|2009/08/06 17:19:27|
|X |POTHER | 800| 1.3| 0| 254|HB | 4| 800|2009/08/06 17:19:27|
|PADDING |P1 | 100| 5.0| 0| 100|HB | 301| 100|2009/08/06 17:19:27|
|PADDING |P2 | 100| 5.0| 0| 100|HB | 301| 100|2009/08/06 17:19:27|
|PADDING |POTHER | 800| 1.3| 0| 254|HB | 301| 800|2009/08/06 17:19:27|
|RR |P1 | 1| 5.0| 0| 1|FREQ| 2| 100|2009/08/06 17:19:27|
|RR |P2 | 1| 5.0| 0| 1|FREQ| 2| 100|2009/08/06 17:19:27|
|RR |POTHER | 1| 0.6| 0| 1|FREQ| 2| 800|2009/08/06 17:19:27|
|SYS_NC00004$|P1 | 100| 5.0| 0| 100|HB | 3| 100|2009/08/06 17:19:27|
|SYS_NC00004$|P2 | 100| 5.0| 0| 100|HB | 4| 100|2009/08/06 17:19:27|
|SYS_NC00004$|POTHER | 800| 1.3| 0| 254|HB | 5| 800|2009/08/06 17:19:27|
|SYS_NC00005$|P1 | 4| 5.0| 0| 4|FREQ| 7| 100|2009/08/06 17:19:27|
|SYS_NC00005$|P2 | 1| 5.0| 0| 1|FREQ| 7| 100|2009/08/06 17:19:27|
|SYS_NC00005$|POTHER | 1| 0.6| 0| 1|FREQ| 7| 800|2009/08/06 17:19:27|
---------------------------------------------------------------------------------------------------------
Accessed table index(es) definitions and CBO statistics (for partitions too) :
### index #1: DELLERA.T_FBI
on DELLERA.T ( X, SYS_NC00004$, PADDING )
NONUNIQUE FUNCTION-BASED B+TREE
LOCAL PARTITIONED BY RANGE ( X, PADDING )
---------------------------------------------------------------------------------------------------
|Partition|distinct_keys|num_rows|blevel|leaf_blocks|cluf|sample_size|last_analyzed |parallel|
---------------------------------------------------------------------------------------------------
| | 1000| 1000| 1| 47| 47| 1000|2009/08/06 17:19:30|1 |
|P1 | 100| 100| 1| 5| 5| 100|2009/08/06 17:19:30| |
|P2 | 100| 100| 1| 5| 5| 100|2009/08/06 17:19:30| |
|POTHER | 800| 800| 1| 37| 37| 800|2009/08/06 17:19:30| |
---------------------------------------------------------------------------------------------------
### index #2: DELLERA.T_FBI2
on DELLERA.T ( X, SYS_NC00005$ )
NONUNIQUE FUNCTION-BASED B+TREE
-----------------------------------------------------------------------------------------
|distinct_keys|num_rows|blevel|leaf_blocks|cluf|sample_size|last_analyzed |parallel|
-----------------------------------------------------------------------------------------
| 1000| 1000| 1| 46| 47| 1000|2009/08/06 17:19:30|1 |
-----------------------------------------------------------------------------------------
### index #3: DELLERA.T_IDX
on DELLERA.T ( PADDING, X )
NONUNIQUE B+TREE
LOCAL PARTITIONED BY RANGE ( X, PADDING )
---------------------------------------------------------------------------------------------------
|Partition|distinct_keys|num_rows|blevel|leaf_blocks|cluf|sample_size|last_analyzed |parallel|
---------------------------------------------------------------------------------------------------
| | 1000| 1000| 1| 47| 64| 1000|2009/08/06 17:19:30|1 |
|P1 | 100| 100| 1| 5| 21| 100|2009/08/06 17:19:30| |
|P2 | 100| 100| 1| 5| 5| 100|2009/08/06 17:19:30| |
|POTHER | 800| 800| 1| 37| 38| 800|2009/08/06 17:19:30| |
---------------------------------------------------------------------------------------------------
### index #4: DELLERA.T_PK
on DELLERA.T ( X, PADDING )
UNIQUE IOT - TOP
LOCAL PARTITIONED BY RANGE ( X, PADDING )
---------------------------------------------------------------------------------------------------
|Partition|distinct_keys|num_rows|blevel|leaf_blocks|cluf|sample_size|last_analyzed |parallel|
---------------------------------------------------------------------------------------------------
| | 1000| 1000| 1| 47| 0| 1000|2009/08/06 17:19:29|1 |
|P1 | 100| 100| 1| 5| 0| 100|2009/08/06 17:19:29| |
|P2 | 100| 100| 1| 5| 0| 100|2009/08/06 17:19:29| |
|POTHER | 800| 800| 1| 37| 0| 800|2009/08/06 17:19:29| |
---------------------------------------------------------------------------------------------------
### index #5: DELLERA.T_UQ_1
on DELLERA.T ( PADDING )
UNIQUE B+TREE
-----------------------------------------------------------------------------------------
|distinct_keys|num_rows|blevel|leaf_blocks|cluf|sample_size|last_analyzed |parallel|
-----------------------------------------------------------------------------------------
| 1000| 1000| 1| 46| 231| 1000|2009/08/06 17:19:29|1 |
-----------------------------------------------------------------------------------------
Depended-on non-table objects definitions:
############################################# function DELLERA.PLSQL_FUNC ###
ASSOCIATED STATISTICS: default selectivity (.001) default cost (cpu=100 io=10 net=1)
function plsql_func (p varchar2)
return varchar2
is
begin
return p;
end plsql_func;
############################################# view DELLERA.V ###
view columns: #1 X(NUMBER),#2 PADDING(VARCHAR2),#3 RR(VARCHAR2)
select x, padding, rr
from t
where x > 0
Options and Statement text filter SQL-like expression summary:
PTIONS: inst_id=1 plan_stats=last access_predicates=Y dbms_xplan=N lines=150 ash_profile_mins=15 module= action= hash= sql_id= parsed_by=
child_number= dbms_xplan=N plan_details=Y plan_env=Y tabinfos=Y objinfos=Y partinfos=Y order_by= spool_name=xplan_i1.lst spool_files=single
SQL_LIKE="%xplan_test_marker%"
Licence warning:
-- Warning: since ash_profile_mins > 0, you are using ASH/AWR; make sure you are licensed to use it.