Displays data about a given SQL_ID, including the DML, explain plan, bind history and historical plans.
SQL ID: gp76pvgu2czuu
Show Bind History: [N] :
select * from table( dbms_xplan.display_cursor( 'gp76pvgu2czuu' ) )
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID: gp76pvgu2czuu, child number: 0 cannot be found
2 rows selected.
select * from table( dbms_xplan.display_awr( 'gp76pvgu2czuu', null, null, 'advanced +peeked_binds'))
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gp76pvgu2czuu
--------------------
update AJD_update_list set proc_sts_cd=:b1,queue_dt=sysdate,update_msg=
:b2 where (cid=to_number(:b3) and build_dt=to_date(:b4,'yyyymmddhh24miss
'))
Plan hash value: 547834988
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 9 (100)| |
| 1 | UPDATE | AJD_UPDATE_LIST | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| AJD_UPDATE_LIST | 1 | 480 | 9 (0)| 00:00:01 |
| 3 | INDEX RANGE SCAN | X_AJD_UPDATE_LIST02 | 7 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - UPD$1
2 - UPD$1 / AJD_UPDATE_LIST@UPD$1
3 - UPD$1 / AJD_UPDATE_LIST@UPD$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"UPD$1")
INDEX_RS_ASC(@"UPD$1" "AJD_UPDATE_LIST"@"UPD$1" ("AJD_UPDATE_LIST"."CID"
"AJD_UPDATE_LIST"."PROC_STS_CD"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
3 - :B3 (VARCHAR2(30), CSID=31): '308514406'
4 - :B4 (VARCHAR2(30), CSID=31): '20150929064628'
45 rows selected.
DBA_HIST_SQLBIND
no rows selected
DBA_HIST_SQL_PLAN
Plan Hash Value Id Operation Options Object Owner Object Name Object Alias Object Type Optimize Parent Id Depth Position Search Columns Cost Cardinality Bytes Other Tag Par Start Par Stop Partition Id O Distributio Cpu Cost Io Cost Temp Space Access Predicates Filter Predicates P Time Qblock Name R Timestamp Other Xml
--------------- ---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------- --------------------------------- --------------- -------- ---------- ---------- -------- -------------- ---------- ----------- ---------- ----------------------------- ---------- ---------- ------------ - ----------- ---------- ---------- ---------- ------------------------------ ------------------------------ - ------- ------------ - ------------------- --------------------------------------------------------------------------------
547834988 0 UPDATE STATEMENT ALL_ROWS 0 9 0 9 29/09/2015 06:48:01
547834988 1 UPDATE BOLTON1 AJD_UPDATE_LIST 0 1 1 0 UPD$1 29/09/2015 06:48:01
11.2.0.354783498815973128553330383531343430363230313530393239303634363238<
/hint><
/hint>
547834988 2 TABLE ACCESS BY INDEX ROWID BOLTON1 AJD_UPDATE_LIST AJD_UPDATE_LIST@UPD$1 TABLE 1 2 1 0 9 1 480 71848 9 1 UPD$1 29/09/2015 06:48:01
547834988 3 INDEX RANGE SCAN BOLTON1 X_AJD_UPDATE_LIST02 AJD_UPDATE_LIST@UPD$1 INDEX 2 3 1 1 3 7 22964 3 1 UPD$1 29/09/2015 06:48:01
4 rows selected.
-- Name : sql_id.sql
prompt
prompt Purpose : Shows DML and explain plan for a given SQL_ID.
prompt
-- Date last modified : 2015/09/29
--
-- Oracle Version: 11g
-- Other Oracle versions: should work on 9i and later
----------------------------------------------------------------------------------------
prompt
spool sql_id.rpt
accept x_sql_id prompt "SQL ID: "
prompt
accept x_show_binds prompt "Show Bind History: [N] : " default 'N'
prompt
prompt select * from table( dbms_xplan.display_cursor( '&&x_sql_id' ) );
select * from table( dbms_xplan.display_cursor( '&&x_sql_id' ) );
prompt
prompt select * from table( dbms_xplan.display_awr( '&&x_sql_id', null, null, 'advanced +peeked_binds'));
select * from table( dbms_xplan.display_awr( '&&x_sql_id', null, null, 'advanced +peeked_binds'));
prompt
prompt DBA_HIST_SQLBIND
col VALUE_STRING for a30
select snap_id,
dbid,
instance_number,
sql_id,
name,
position,
dup_position,
datatype,
datatype_string,
character_sid,
precision,
scale,
max_length,
was_captured,
last_captured,
value_string
from DBA_HIST_SQLBIND
where sql_id = '&&x_sql_id'
and was_captured <> 'NO'
and upper( '&&x_show_binds' ) <> 'N'
order by last_captured;
prompt
prompt DBA_HIST_SQL_PLAN
col "Object Node" for a6
col "Object Alias" for a33
col "Object Type" for a15
col "Optimizer" for a8
col "Position" for 999999
col "Search Columns" for 999999
col "Cost" for 999999999
col "Cardinality" for 999999
col "Bytes" for 999999999
col "Other Tag" for a29
col "Par Start" for a10
col "Par Stop" for a10
col "Partition Id" for 999999
col "Other" for a1
col "Distribution" for a11
col "Cpu Cost" for 999999999
col "Io Cost" for 999999999
col "Temp Space" for 999999
col "Projection" for a1
col "Time" for 999999
col "Qblock Name" for a12
col "Remarks" for a1
col "Access Predicates" for a30
col "Filter Predicates" for a30
break on plan_hash_value skip 5
select
-- dbid "Dbid",
-- sql_id "Sql Id",
plan_hash_value "Plan Hash Value",
id "Id",
operation "Operation",
options "Options",
-- object_node "Object Node",
-- object# "Object#",
object_owner "Object Owner",
object_name "Object Name",
object_alias "Object Alias",
object_type "Object Type",
optimizer "Optimizer",
parent_id "Parent Id",
depth "Depth",
position "Position",
search_columns "Search Columns",
cost "Cost",
cardinality "Cardinality",
bytes "Bytes",
other_tag "Other Tag",
partition_start "Par Start",
partition_stop "Par Stop",
partition_id "Partition Id",
other "Other",
distribution "Distribution",
cpu_cost "Cpu Cost",
io_cost "Io Cost",
temp_space "Temp Space",
access_predicates "Access Predicates",
filter_predicates "Filter Predicates",
projection "Projection",
time "Time",
qblock_name "Qblock Name",
remarks "Remarks",
timestamp "Timestamp",
other_xml "Other Xml"
from dba_hist_sql_plan
where sql_id = '&&x_sql_id'
order by id;
spool off