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

This article mainly introduces "what are the methods of oracle query execution plan". In the daily operation, I believe that many people have doubts about the method of oracle query execution plan. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "what are the methods of oracle query execution plan?" Next, please follow the editor to study!

1.explain plan for

-- No need to execute, fast and convenient

-- No statistics, logical reading, recursion, etc.

It is impossible to tell how many lines have been processed

It is impossible to tell how many times the table has been accessed

Explain plan for select * from t _ 1 where t.type=t1.object_name

Select * from table (dbms_xplan.display ())

Plan hash value: 2914261090

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

| | 0 | SELECT STATEMENT | | 1 | 221 | 244 (0) | 00:00:01 |

| | * 1 | HASH JOIN | | 1 | 221 | 244 (0) | 00:00:01 |

| | 2 | TABLE ACCESS FULL | T | 1 | 142 | 122 (0) | 00:00:01 |

| | 3 | TABLE ACCESS FULL | T1 | 1 | 79 | 122 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-access ("T". "TYPE" = "T1". "OBJECT_NAME")

Note

-

-dynamic statistics used: dynamic sampling (level=2)

19 rows selected.

2.set autotrace on

-- output statistical information

You have to wait for the statement to be executed before you get the result-- you can't see how many times the table has been accessed

Set autotrace on-set autotrace traceonly does not output results--

Select * from t _ 1 where t.type=t1.object_name

Execution Plan

Plan hash value: 2914261090

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

| | 0 | SELECT STATEMENT | | 1 | 221 | 244 (0) | 00:00:01 |

| | * 1 | HASH JOIN | | 1 | 221 | 244 (0) | 00:00:01 |

| | 2 | TABLE ACCESS FULL | T | 1 | 142 | 122 (0) | 00:00:01 |

| | 3 | TABLE ACCESS FULL | T1 | 1 | 79 | 122 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-access ("T". "TYPE" = "T1". "OBJECT_NAME")

Note

-

-dynamic statistics used: dynamic sampling (level=2)

Statistics

4 recursive calls

0 db block gets

896 consistent gets

0 physical reads

0 redo size

889 bytes sent via SQL*Net to client

408 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

3 rows processed

3.statistics_level=all

-- you can see how many times the table has been accessed from Starts. EmurRowsMore AripRows predicts the number of rows and true implementations, and buffer is a real logical read.

There is no result until the statement is executed. The result cannot be controlled, and the recursive call and logical reading cannot be seen.

Alter session set statistics_level=all

Select * from t _ 1 where t.type=t1.object_name

Select * from table (dbms_xplan.display_cursor (null,null,'allstats last'))

-

| | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |

-

| | 0 | SELECT STATEMENT | | 1 | | 3 | 00001 | 00.01 | 896 |

| | * 1 | HASH JOIN | | 1 | 1 | 3 | 00lv 00.01 | 896 | 1695k | 1695k | 787k (0) |

| | 2 | TABLE ACCESS FULL | T | 1 | 1 | 4 | 00lv 00.01 | 447 |

| | 3 | TABLE ACCESS FULL | T1 | 1 | 1 | 5 | 00lv 00.01 | 449 |

-

Predicate Information (identified by operation id):

1-access ("T". "TYPE" = "T1". "OBJECT_NAME")

Note

-

-dynamic statistics used: dynamic sampling (level=2)

4.dbms_xplan.display_cursor

Know that sql_id can come up with the real execution plan immediately and directly.

-- there is no relevant statistics (logical reads, etc.)-- it is impossible to determine how many times it has been executed-- it is impossible to determine how many times the table has been accessed.

5qn0b7zft4s04

Select * from table (dbms_xplan.display_cursor ('sql_id'))-- shared pool acquisition

Select * from table (dbms_xplan.display_awr ('sql_id'))-- obtained from the awr performance view

Select * from table (dbms_xplan.display_cursor ('5qn0b7zft4s04'))

Select * from table (dbms_xplan.display_awr ('5qn0b7zft4s04'))

Plan hash value: 2914261090

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

| | 0 | SELECT STATEMENT | 244100 | | |

| | * 1 | HASH JOIN | | 1 | 221 | 244 (0) | 00:00:01 |

| | 2 | TABLE ACCESS FULL | T | 1 | 142 | 122 (0) | 00:00:01 |

| | 3 | TABLE ACCESS FULL | T1 | 1 | 79 | 122 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-access ("T". "TYPE" = "T1". "OBJECT_NAME")

Note

-

-dynamic statistics used: dynamic sampling (level=2)

5.10046 trace tracking

You can see the wait event of the statement, and you can see the function call in sql

You can see the number of lines processed and physical reads-parsing time and execution time

-- easy to track the entire package

Alter session set events' 10046 trace name context forever,level 12 hours; enable tracking

Execute statement

Alter session set events' 10046 trace name context off'

Find the file

Tkprof trc file target file sys=no sort=prsela,exeela,fchela

[oracle@oracle1 ~] $cat 1.txt

TKPROF: Release 19.0.0.0.0-Development on Wed Mar 11 10:25:48 2020

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Trace file: NGENPR_ora_6661.trc

Sort options: prsela exeela fchela

*

Count = number of times OCI procedure was executed

Cpu = cpu time in seconds executing

Elapsed = elapsed time in seconds executing

Disk = number of physical reads of buffers from disk

Query = number of buffers gotten for consistent read

Current = number of buffers gotten in current mode (usually for update)

Rows = number of rows processed by the fetch or execute call

*

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

Call count cpu elapsed disk query current rows

--

Parse 2 0.00 0.00 0 138 00

Execute 2 0.00 0.00 00 00

Fetch 2 0.00 0.00 0 760 0 3

--

Total 6 0.00 0.00 0 898 0 3

Misses in library cache during parse: 1

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

-- Waited--

Disk file operations I/O 2 0.00 0.00

SQL*Net message to client 3 0.00 0.00

SQL*Net message from client 3 8.24 13.24

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

Call count cpu elapsed disk query current rows

--

Parse 3 0.00 0.00 00 00

Execute 3 0.00 0.00 00 00

Fetch 3 0.00 0.00 0 136 0 2

--

Total 9 0.00 0.00 0 136 0 2

Misses in library cache during parse: 3

Misses in library cache during execute: 1

2 user SQL statements in session.

3 internal SQL statements in session.

5 SQL statements in session.

*

Trace file: NGENPR_ora_6661.trc

Trace file compatibility: 12.2.0.0

Sort options: prsela exeela fchela

1 session in tracefile.

2 user SQL statements in trace file.

3 internal SQL statements in trace file.

5 SQL statements in trace file.

5 unique SQL statements in trace file.

167 lines in trace file.

5 elapsed seconds in trace file.

6 awrsqlrpt

@? / rdbms/admin/awrsqlrpt

Begin end snap

Sql_id

The difference between the six methods

1. If the result doesn't come out, you can only use 1

two。 The easier way is 1 or 2.

3. Observing multiple execution plans can only use 4 and 6

4. If the statement is complex and functions are involved, you can only use 5

5. A real execution plan cannot be used with 1

6. To get the number of times the table has been accessed, you can only use 3

At this point, the study on "what are the methods of oracle query execution plan" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.