Shulou(Shulou.com)05/31 Report--

Dbms_xplan.display_cursor package and ADVANCED ALLSTATS LAST PEEKED_BINDS what is the difference, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Conclusion 1: using ALL LAST has more Query Block Name / Object Alias and Column Projection Information (column information) than typical.

Conclusion 2:ADVANCED ALLSTATS LAST PEEKED_BINDS has more content than ALL LAST: outline and NOTE, and, of course, binding variable information if binding variables are used.

Conclusion 3: generally speaking, ALL LAST is enough.

Do a comparative experiment with a statement that does not use bound variables:

Select / * weiwei*/ e.enamered.dname from scott.emp eMagne scott.dept d where e.deptno=d.deptno

SQL > SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR)

PLAN_TABLE_OUTPUT

-

SQL_ID 1qwpbwszr5hwb, child number 0

-

Select / * weiwei*/ e.enamered.dname from scott.emp eMagne scott.dept d where

E.deptno=d.deptno

Plan hash value: 844388907

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | 6 (100) | |

| | 1 | MERGE JOIN | | 14 | 308 | 6 (17) | 00:00:01 |

| | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 52 | 2 (0) | 00:00:01 |

| | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0) | 00:00:01 |

| | * 4 | SORT JOIN | | 14 | 126 | 4 (25) | 00:00:01 |

| | 5 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

4-access ("E". "DEPTNO" = "D". "DEPTNO")

Filter ("E". "DEPTNO" = "D". "DEPTNO")

24 rows selected.

Select sql_id,CHILD_NUMBER,sql_text from v$SQL where sql_text like'% weiwei%' and sql_text not like'% like%'

Get a SQL_id of 1qwpbwszr5hwb

Select * from table (dbms_xplan.display_cursor ('1qwpbwszr5hwbjnlwbjcccccccccccccccccccccccccccccccccccccccccccwwpbwszr5hwbmszr5hwbl))

SQL > select * from table (dbms_xplan.display_cursor ('1qwpbwszr5hwbwriting recordnullpaper all LAST'))

PLAN_TABLE_OUTPUT

-

SQL_ID 1qwpbwszr5hwb, child number 0

-

Select / * weiwei*/ e.enamered.dname from scott.emp eMagne scott.dept d where

E.deptno=d.deptno

Plan hash value: 844388907

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | 6 (100) | |

| | 1 | MERGE JOIN | | 14 | 308 | 6 (17) | 00:00:01 |

| | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 52 | 2 (0) | 00:00:01 |

| | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0) | 00:00:01 |

| | * 4 | SORT JOIN | | 14 | 126 | 4 (25) | 00:00:01 |

| | 5 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0) | 00:00:01 |

-

Query Block Name / Object Alias (identified by operation id):

1-SEL$1

2-SEL$1 / D@SEL$1

3-SEL$1 / D@SEL$1

5-SEL$1 / E@SEL$1

Predicate Information (identified by operation id):

4-access ("E". "DEPTNO" = "D". "DEPTNO")

Filter ("E". "DEPTNO" = "D". "DEPTNO")

Column Projection Information (identified by operation id):

1-"D". "DNAME" [VARCHAR2,14], "E". "ENAME" [VARCHAR2,10]

2-"D". "DEPTNO" [NUMBER,22], "D". "DNAME" [VARCHAR2,14]

3-"D" .ROWID [ROWID,10], "D". "DEPTNO" [NUMBER,22]

4-(# keys=1) "E". "DEPTNO" [NUMBER,22], "E". "ENAME" [VARCHAR2,10]

5-"E". "ENAME" [VARCHAR2,10], "E". "DEPTNO" [NUMBER,22]

41 rows selected.

Conclusion 1: using ALL LAST has more Query Block Name / Object Alias and Column Projection Information (column information) than typical.

Compare ALL LAST with ADVANCED ALLSTATS LAST PEEKED_BINDS again

Finally, the most complete one is 65 lines.

Select * from table (dbms_xplan.display_cursor ('1qwpbwszr5hwbpwszr5hwbpjpwszr5hwbppbwszr5hwbppwszr5hwbppwszr5hwbpwszr5hwbpszr5hwbpwszr5hwbpszr5hwbpwszr5hwbpszr5hwbpszr5hwbpszr5hwbpwszr5hwbpwszr5hwbpwszr5hwbpwszr5hwbpwszr5hwbpwszr5hwbpszr5hwb

SQL > select * from table (dbms_xplan.display_cursor ('1qwpbwszr5hwbpjpwszr5hwbppwszr5hwbppwszr5hwbppwszr5hwbppwszr5hwbppszr5hwbpszr5hwbpszr5hwbpszr5hwbpszr5hwbpszr5hwbpszr5hwbpszr5hwbpszr5hwbpszr5hwbpwszr5hwbpwszr5hwbpwszr5hwbpwszr5hwbpwszr5hwbp

PLAN_TABLE_OUTPUT

-

SQL_ID 1qwpbwszr5hwb, child number 0

-

Select / * weiwei*/ e.enamered.dname from scott.emp eMagne scott.dept d where

E.deptno=d.deptno

Plan hash value: 844388907

-

| | Id | Operation | Name | E-Rows | E-Bytes | Cost (% CPU) | E-Time | OMem | 1Mem | Used-Mem |

-

| | 0 | SELECT STATEMENT | 6 (100) | |

| | 1 | MERGE JOIN | | 14 | 308 | 6 (17) | 00:00:01 |

| | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 52 | 2 (0) | 00:00:01 |

| | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0) | 00:00:01 |

| | * 4 | SORT JOIN | | 14 | 126 | 4 (25) | 00:00:01 | 2048 | 2048 | 2048 (0) |

| | 5 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0) | 00:00:01 |

-

Query Block Name / Object Alias (identified by operation id):

1-SEL$1

2-SEL$1 / D@SEL$1

3-SEL$1 / D@SEL$1

5-SEL$1 / E@SEL$1

Outline Data

-

/ * +

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE ('11.2.0.3')

DB_VERSION ('11.2.0.3')

OPT_PARAM ('query_rewrite_enabled'' false')

ALL_ROWS

OUTLINE_LEAF (@ "SEL$1")

INDEX (@ "SEL$1"D" @ "SEL$1" ("DEPT". "DEPTNO"))

FULL (@ "SEL$1"E" @ "SEL$1")

LEADING (@ "SEL$1"D" @ "SEL$1"E" @ "SEL$1")

USE_MERGE (@ "SEL$1"E" @ "SEL$1")

END_OUTLINE_DATA

, /

Predicate Information (identified by operation id):

4-access ("E". "DEPTNO" = "D". "DEPTNO")

Filter ("E". "DEPTNO" = "D". "DEPTNO")

Column Projection Information (identified by operation id):

1-"D". "DNAME" [VARCHAR2,14], "E". "ENAME" [VARCHAR2,10]

2-"D". "DEPTNO" [NUMBER,22], "D". "DNAME" [VARCHAR2,14]

3-"D" .ROWID [ROWID,10], "D". "DEPTNO" [NUMBER,22]

4-(# keys=1) "E". "DEPTNO" [NUMBER,22], "E". "ENAME" [VARCHAR2,10]

5-"E". "ENAME" [VARCHAR2,10], "E". "DEPTNO" [NUMBER,22]

Note

-

-Warning: basic plan statistics not available. These are only collected when:

* hint 'gather_plan_statistics' is used for the statement or

* parameter 'statistics_level' is set to' ALL', at session or system level

Rows selected.

Conclusion 2:ADVANCED ALLSTATS LAST PEEKED_BINDS has more content than ALL LAST: outline and NOTE, and, of course, binding variable information if binding variables are used.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.