For finding out what happened with queries, here are my 2 favorite queries..
For searching the awr and previous executions
select * from table(dbms_xplan.display_awr('3cmh637q9msjs',null,null,'advanced +peeked_binds'));
For looking through the cursor cache
select * from table(dbms_xplan.display_cursor('3cmh637q9msjs',null,'advanced +peeked_binds'));
The output looks something like this...
For searching the awr and previous executions
select * from table(dbms_xplan.display_awr('3cmh637q9msjs',null,null,'advanced +peeked_binds'));
For looking through the cursor cache
select * from table(dbms_xplan.display_cursor('3cmh637q9msjs',null,'advanced +peeked_binds'));
The output looks something like this...
line 8: SQLPLUS Command Skipped: set linesize 132
line 9: SQLPLUS Command Skipped: set pagesize 0
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3j9yx7t5abcyg
--------------------
/* OracleOEM */ SELECT m.tablespace_name, m.used_percent,
(m.tablespace_size - m.used_space)*t.block_size/1024/1024 mb_free
FROM dba_tablespace_usage_metrics m, dba_tablespaces t, v$parameter p
WHERE p.name='statistics_level' and p.value!='BASIC' AND
t.tablespace_name = m.tablespace_name
Plan hash value: 125441316
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 19 (100)| | | | |
| 1 | NESTED LOOPS | | 1 | 2209 | 19 (43)| 00:00:01 | | | |
| 2 | NESTED LOOPS | | 1 | 2196 | 19 (43)| 00:00:01 | | | |
| 3 | MERGE JOIN CARTESIAN | | 1 | 2171 | 18 (45)| 00:00:01 | | | |
| 4 | HASH JOIN | | 1 | 2115 | 1 (100)| 00:00:01 | | | |
| 5 | FIXED TABLE FULL | X$KSPPI | 1 | 81 | 0 (0)| | | | |
| 6 | FIXED TABLE FULL | X$KSPPCV | 5 | 10170 | 0 (0)| | | | |
| 7 | BUFFER SORT | | 82 | 4592 | 18 (45)| 00:00:01 | | | |
| 8 | VIEW | DBA_TABLESPACE_USAGE_METRICS | 82 | 4592 | 17 (42)| 00:00:01 | | | |
| 9 | SORT UNIQUE | | 82 | 7594 | 17 (53)| 00:00:01 | | | |
| 10 | UNION-ALL | | | | | | | | |
| 11 | MERGE JOIN | | 80 | 5360 | 9 (12)| 00:00:01 | | | |
| 12 | TABLE ACCESS CLUSTER | TS$ | 31 | 868 | 8 (0)| 00:00:01 | | | |
| 13 | INDEX FULL SCAN | I_TS# | 1 | | 1 (0)| 00:00:01 | | | |
| 14 | SORT JOIN | | 100 | 3900 | 1 (100)| 00:00:01 | | | |
| 15 | FIXED TABLE FULL | X$KTTETS | 100 | 3900 | 0 (0)| | | | |
| 16 | HASH GROUP BY | | 1 | 90 | 3 (67)| 00:00:01 | | | |
| 17 | NESTED LOOPS | | 1 | 90 | 1 (0)| 00:00:01 | | | |
| 18 | FIXED TABLE FULL | X$KTTEFINFO | 1 | 65 | 0 (0)| | | | |
| 19 | TABLE ACCESS CLUSTER | TS$ | 1 | 25 | 1 (0)| 00:00:01 | | | |
| 20 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| | | | |
| 21 | HASH GROUP BY | | 1 | 2144 | 4 (75)| 00:00:01 | | | |
| 22 | NESTED LOOPS | | | | | | | | |
| 23 | NESTED LOOPS | | 1 | 2144 | 2 (50)| 00:00:01 | | | |
| 24 | HASH JOIN | | 1 | 2122 | 1 (100)| 00:00:01 | | | |
| 25 | PX COORDINATOR | | 1 | 65 | 0 (0)| | | | |
| 26 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 65 | 0 (0)| | Q1,00 | P->S | QC (RAND) |
| 27 | VIEW | GV$FILESPACE_USAGE | | | | | Q1,00 | PCWP | |
| 28 | FIXED TABLE FULL | X$KTTEFINFO | 1 | 65 | 0 (0)| | Q1,00 | PCWP | |
| 29 | PX COORDINATOR | | 1 | 2057 | 1 (100)| 00:00:01 | | | |
| 30 | PX SEND QC (RANDOM) | :TQ20000 | 1 | 2115 | 1 (100)| 00:00:01 | Q2,00 | P->S | QC (RAND) |
| 31 | VIEW | GV$PARAMETER | | | | | Q2,00 | PCWP | |
| 32 | HASH JOIN | | 1 | 2115 | 1 (100)| 00:00:01 | Q2,00 | PCWP | |
| 33 | FIXED TABLE FULL | X$KSPPI | 1 | 81 | 0 (0)| | Q2,00 | PCWP | |
| 34 | FIXED TABLE FULL | X$KSPPCV | 100 | 198K| 0 (0)| | Q2,00 | PCWP | |
| 35 | INDEX UNIQUE SCAN | I_TS1 | 1 | | 0 (0)| | | | |
| 36 | TABLE ACCESS BY INDEX ROWID| TS$ | 1 | 22 | 1 (0)| 00:00:01 | | | |
| 37 | TABLE ACCESS BY INDEX ROWID | TS$ | 1 | 25 | 1 (0)| 00:00:01 | | | |
| 38 | INDEX UNIQUE SCAN | I_TS1 | 1 | | 0 (0)| | | | |
| 39 | FIXED TABLE FIXED INDEX | X$KCFISTSA (ind:1) | 3 | 39 | 0 (0)| | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$CBAA6355
5 - SEL$CBAA6355 / X@SEL$12
6 - SEL$CBAA6355 / Y@SEL$12
8 - SET$1 / M@SEL$1
9 - SET$1
11 - SEL$2
12 - SEL$2 / T@SEL$2
13 - SEL$2 / T@SEL$2
15 - SEL$2 / TSTAT@SEL$2
16 - SEL$C8360722
18 - SEL$C8360722 / X$KTTEFINFO@SEL$5
19 - SEL$C8360722 / T@SEL$3
20 - SEL$C8360722 / T@SEL$3
21 - SEL$6
25 - SEL$7 / F@SEL$6
28 - SEL$7 / X$KTTEFINFO@SEL$7
29 - SEL$8 / PARAM@SEL$6
32 - SEL$8
33 - SEL$8 / X@SEL$8
34 - SEL$8 / Y@SEL$8
35 - SEL$6 / T@SEL$6
36 - SEL$6 / T@SEL$6
37 - SEL$CBAA6355 / TS@SEL$9
38 - SEL$CBAA6355 / TS@SEL$9
39 - SEL$CBAA6355 / TSATTR@SEL$9
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
OPT_PARAM('_optimizer_fast_pred_transitivity' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$C8360722")
MERGE(@"SEL$7286615E")
OUTLINE_LEAF(@"SEL$7")
OUTLINE_LEAF(@"SEL$8")
OUTLINE_LEAF(@"SEL$6")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$CBAA6355")
MERGE(@"SEL$9")
MERGE(@"SEL$E3DF9B48")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$7286615E")
MERGE(@"SEL$5")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$9")
OUTLINE(@"SEL$E3DF9B48")
MERGE(@"SEL$42DFC41A")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$10")
OUTLINE(@"SEL$42DFC41A")
MERGE(@"SEL$12")
OUTLINE(@"SEL$11")
OUTLINE(@"SEL$12")
FULL(@"SEL$CBAA6355" "X"@"SEL$12")
FULL(@"SEL$CBAA6355" "Y"@"SEL$12")
NO_ACCESS(@"SEL$CBAA6355" "M"@"SEL$1")
INDEX_RS_ASC(@"SEL$CBAA6355" "TS"@"SEL$9" ("TS$"."NAME"))
FULL(@"SEL$CBAA6355" "TSATTR"@"SEL$9")
LEADING(@"SEL$CBAA6355" "X"@"SEL$12" "Y"@"SEL$12" "M"@"SEL$1" "TS"@"SEL$9" "TSATTR"@"SEL$9")
USE_HASH(@"SEL$CBAA6355" "Y"@"SEL$12")
USE_MERGE_CARTESIAN(@"SEL$CBAA6355" "M"@"SEL$1")
USE_NL(@"SEL$CBAA6355" "TS"@"SEL$9")
USE_NL(@"SEL$CBAA6355" "TSATTR"@"SEL$9")
NO_ACCESS(@"SEL$6" "F"@"SEL$6")
NO_ACCESS(@"SEL$6" "PARAM"@"SEL$6")
INDEX(@"SEL$6" "T"@"SEL$6" ("TS$"."NAME"))
LEADING(@"SEL$6" "F"@"SEL$6" "PARAM"@"SEL$6" "T"@"SEL$6")
USE_HASH(@"SEL$6" "PARAM"@"SEL$6")
USE_NL(@"SEL$6" "T"@"SEL$6")
NLJ_BATCHING(@"SEL$6" "T"@"SEL$6")
USE_HASH_AGGREGATION(@"SEL$6")
FULL(@"SEL$C8360722" "X$KTTEFINFO"@"SEL$5")
INDEX(@"SEL$C8360722" "T"@"SEL$3" "I_TS#")
LEADING(@"SEL$C8360722" "X$KTTEFINFO"@"SEL$5" "T"@"SEL$3")
USE_NL(@"SEL$C8360722" "T"@"SEL$3")
USE_HASH_AGGREGATION(@"SEL$C8360722")
INDEX(@"SEL$2" "T"@"SEL$2" "I_TS#")
FULL(@"SEL$2" "TSTAT"@"SEL$2")
LEADING(@"SEL$2" "T"@"SEL$2" "TSTAT"@"SEL$2")
USE_MERGE(@"SEL$2" "TSTAT"@"SEL$2")
PX_JOIN_FILTER(@"SEL$2" "TSTAT"@"SEL$2")
FULL(@"SEL$7" "X$KTTEFINFO"@"SEL$7")
FULL(@"SEL$8" "X"@"SEL$8")
FULL(@"SEL$8" "Y"@"SEL$8")
LEADING(@"SEL$8" "X"@"SEL$8" "Y"@"SEL$8")
USE_HASH(@"SEL$8" "Y"@"SEL$8")
END_OUTLINE_DATA
*/
Note
-----
- statement not queuable: gv$ statement
158 rows selected