Home > DB_LEVEL > Get the explain of a SQL pattern using dbms_xplan.display_cursor.

Get the explain of a SQL pattern using dbms_xplan.display_cursor.

February 7, 2014

How to get the details of the SQL which a particular user executes. There are many ways which we can use, one of the way is below.

1) you need to provide the pattern of the sql.
2) you need to provide the owner of the sql. i.e. who is executing the SQL. must be in CAPS.

Steps.

1) select cursor(select * from table(dbms_xplan.display_cursor(s.sql_id,s.child_number,’allstats last’))) from v$sql s where s.parsing_schema_name=’SCOTT’ and s.sql_text like ‘select%count(1)%’;

For your easiness i am here with attaching the output.
=========================================================
SQL> SET LINES 120
SQL> SET PAGES 120
SQL> select cursor(select * from table(dbms_xplan.display_cursor(s.sql_id,s.child_number,’allstats last’))) from v$sql s where s.parsing_schema_name=’SCOTT’ and s.sql_text like ‘select%count(1)%’;

CURSOR(SELECT*FROMTA
——————–
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

PLAN_TABLE_OUTPUT
————————————————————————————————————————
SQL_ID 1t385yygmyypw, child number 0
————————————-
select count(1) from emp

Plan hash value: 2937609675

————————————————————————————-
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 |
| 2 | INDEX FULL SCAN| PK_EMP | 1 | 14 | 14 |00:00:00.01 | 1 |
————————————————————————————-

14 rows selected.

CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

PLAN_TABLE_OUTPUT
————————————————————————————————————————
SQL_ID 86rr557mgmdaz, child number 0
————————————-
select count(1) from bonus

Plan hash value: 4224507826

—————————————————————————-
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
—————————————————————————-
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 |
| 2 | TABLE ACCESS FULL| BONUS | 1 | 1 | 0 |00:00:00.01 |
—————————————————————————-

14 rows selected.

CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

PLAN_TABLE_OUTPUT
————————————————————————————————————————
SQL_ID g6wjfp8ujbqac, child number 0
————————————-
select count(1) from tab

Plan hash value: 1067070269

————————————————————————————————————————
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
————————————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 17 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 17 | | | |
|* 2 | FILTER | | 1 | | 5 |00:00:00.01 | 17 | | | |
| 3 | NESTED LOOPS OUTER | | 1 | 1224 | 5 |00:00:00.01 | 17 | | | |
|* 4 | HASH JOIN | | 1 | 1224 | 5 |00:00:00.01 | 4 | 1180K| 1180K| 1259K (0)|
| 5 | INDEX FULL SCAN | I_USER2 | 1 | 93 | 94 |00:00:00.01 | 1 | | | |
|* 6 | INDEX RANGE SCAN | I_OBJ5 | 1 | 1224 | 5 |00:00:00.01 | 3 | | | |
| 7 | TABLE ACCESS CLUSTER| TAB$ | 5 | 1 | 5 |00:00:00.01 | 13 | | | |
|* 8 | INDEX UNIQUE SCAN | I_OBJ# | 5 | 1 | 5 |00:00:00.01 | 7 | | | |
| 9 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 10 | INDEX FULL SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 11 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
————————————————————————————————————————

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

2 – filter(((“O”.”TYPE#”4 AND “O”.”TYPE#”5 AND “O”.”TYPE#”7 AND “O”.”TYPE#”8 AND “O”.”TYPE#”9 AND
“O”.”TYPE#”10 AND “O”.”TYPE#”11 AND “O”.”TYPE#”12 AND “O”.”TYPE#”13 AND “O”.”TYPE#”14 AND
“O”.”TYPE#”22 AND “O”.”TYPE#”87 AND “O”.”TYPE#”88) OR BITAND(“U”.”SPARE1″,16)=0 OR
(INTERNAL_FUNCTION(“O”.”TYPE#”) AND ((SYS_CONTEXT(‘userenv’,’current_edition_name’)=’ORA$BASE’ AND
“U”.”TYPE#”2) OR (“U”.”TYPE#”=2 AND “U”.”SPARE2″=TO_NUMBER(SYS_CONTEXT(‘userenv’,’current_edition_id’)))
OR

IS NOT NULL))))
4 – access(“O”.”OWNER#”=”U”.”USER#”)
6 – access(“O”.”SPARE3″=USERENV(‘SCHEMAID’) AND “O”.”TYPE#”>=2 AND “O”.”LINKNAME” IS NULL AND “O”.”TYPE#”<=5)
filter(("O"."TYPE#"=2 AND “O”.”LINKNAME” IS NULL))
8 – access(“O”.”OBJ#”=”T”.”OBJ#”)
10 – access(“U2”.”TYPE#”=2 AND “U2”.”SPARE2″=TO_NUMBER(SYS_CONTEXT(‘userenv’,’current_edition_id’)))
filter((“U2”.”TYPE#”=2 AND “U2”.”SPARE2″=TO_NUMBER(SYS_CONTEXT(‘userenv’,’current_edition_id’))))
11 – access(“O2”.”DATAOBJ#”=:B1 AND “O2”.”TYPE#”=88 AND “O2″.”OWNER#”=”U2″.”USER#”)

40 rows selected.

CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

PLAN_TABLE_OUTPUT
————————————————————————————————————————
SQL_ID g6wjfp8ujbqac, child number 1
————————————-
select count(1) from tab

Plan hash value: 1067070269

————————————————————————————————————————
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
————————————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 17 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 17 | | | |
|* 2 | FILTER | | 1 | | 5 |00:00:00.01 | 17 | | | |
| 3 | NESTED LOOPS OUTER | | 1 | 1224 | 5 |00:00:00.01 | 17 | | | |
|* 4 | HASH JOIN | | 1 | 1224 | 5 |00:00:00.01 | 4 | 1180K| 1180K| 1242K (0)|
| 5 | INDEX FULL SCAN | I_USER2 | 1 | 93 | 94 |00:00:00.01 | 1 | | | |
|* 6 | INDEX RANGE SCAN | I_OBJ5 | 1 | 1224 | 5 |00:00:00.01 | 3 | | | |
| 7 | TABLE ACCESS CLUSTER| TAB$ | 5 | 1 | 5 |00:00:00.01 | 13 | | | |
|* 8 | INDEX UNIQUE SCAN | I_OBJ# | 5 | 1 | 5 |00:00:00.01 | 7 | | | |
| 9 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 10 | INDEX FULL SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 11 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
————————————————————————————————————————

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

2 – filter(((“O”.”TYPE#”4 AND “O”.”TYPE#”5 AND “O”.”TYPE#”7 AND “O”.”TYPE#”8 AND “O”.”TYPE#”9 AND
“O”.”TYPE#”10 AND “O”.”TYPE#”11 AND “O”.”TYPE#”12 AND “O”.”TYPE#”13 AND “O”.”TYPE#”14 AND
“O”.”TYPE#”22 AND “O”.”TYPE#”87 AND “O”.”TYPE#”88) OR BITAND(“U”.”SPARE1″,16)=0 OR
(INTERNAL_FUNCTION(“O”.”TYPE#”) AND ((SYS_CONTEXT(‘userenv’,’current_edition_name’)=’ORA$BASE’ AND
“U”.”TYPE#”2) OR (“U”.”TYPE#”=2 AND “U”.”SPARE2″=TO_NUMBER(SYS_CONTEXT(‘userenv’,’current_edition_id’)))
OR

IS NOT NULL))))
4 – access(“O”.”OWNER#”=”U”.”USER#”)
6 – access(“O”.”SPARE3″=USERENV(‘SCHEMAID’) AND “O”.”TYPE#”>=2 AND “O”.”LINKNAME” IS NULL AND “O”.”TYPE#”<=5)
filter(("O"."TYPE#"=2 AND “O”.”LINKNAME” IS NULL))
8 – access(“O”.”OBJ#”=”T”.”OBJ#”)
10 – access(“U2”.”TYPE#”=2 AND “U2”.”SPARE2″=TO_NUMBER(SYS_CONTEXT(‘userenv’,’current_edition_id’)))
filter((“U2”.”TYPE#”=2 AND “U2”.”SPARE2″=TO_NUMBER(SYS_CONTEXT(‘userenv’,’current_edition_id’))))
11 – access(“O2”.”DATAOBJ#”=:B1 AND “O2”.”TYPE#”=88 AND “O2″.”OWNER#”=”U2″.”USER#”)

Note
—–
– cardinality feedback used for this statement

44 rows selected.

CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

PLAN_TABLE_OUTPUT
————————————————————————————————————————
SQL_ID 5vn2y242uvqwa, child number 0
————————————-
select count(1) from dept

Plan hash value: 3051237957

————————————————————————————–
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 |
| 2 | INDEX FULL SCAN| PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 |
————————————————————————————–

14 rows selected.

SQL>

Advertisements
Categories: DB_LEVEL
%d bloggers like this: