Shulou(Shulou.com)06/01 Report--

one。 Six ways to get the execution plan (the detailed steps are explained in the comments section at the beginning of each example):

1. Explain plan for acquisition

2. Set autotrace on

3. Statistics_level=all

4. Directly obtain the input sql_id parameters through dbms_xplan.display_cursor

5. 10046 trace tracking

6. Awrsqrpt.sql

1. Explain plan for acquisition; (similar to F5 in PLSQL DEVELOPE)

/ *

Step 1:explain plan for "your SQL"

Step 2:select * from table (dbms_xplan.display ())

, /

Set linesize 1000

Set pagesize 2000

Explain plan for

SELECT *

FROM t1, t2

WHERE t1.id = t2.t1_id

AND t1.n in (18cm19)

Select * from table (dbms_xplan.display ())

PLAN_TABLE_OUTPUT

-

Plan hash value: 3532430033

-

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

-

| | 0 | SELECT STATEMENT | | 2 | 8138 | 6 (0) | 00:00:01 |

| | 1 | NESTED LOOPS | | |

| | 2 | NESTED LOOPS | | 2 | 8138 | 6 (0) | 00:00:01 |

| | 3 | INLIST ITERATOR | | |

| | 4 | TABLE ACCESS BY INDEX ROWID | T1 | 2 | 4056 | 2 (0) | 00:00:01 |

| | * 5 | INDEX RANGE SCAN | T1roomN | 1 | | 1 (0) | 00:00:01 |

| | * 6 | INDEX RANGE SCAN | T2_T1_ID | 1 | | 1 (0) | 00:00:01 |

| | 7 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 2041 | 2 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

5-access ("T1". "N" = 18 OR "T1". "N" = 19)

6-access ("T1". "ID" = "T2". "T1_ID")

Note

-

-dynamic sampling used for this statement (level=2)

24 rows have been selected.

/ *

Advantages: 1. No need to really execute, fast and convenient

Defects: 1. There is no output of runtime-related statistics (how many logical reads, how many recursive calls, how many physical reads)

two。 It is impossible to tell how many lines have been processed

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

Indeed, after all, how to know the statistical information generated by the real operation if it is not really implemented.

, /

2. Set autotrace on

/ *

Step 1:set autotrace on

Step 2: execute your SQL here, and the result will be output later.

In addition, there are several ways:

Set autotrace on (get the execution plan and output the running result)

Set autotrace traceonly (get the execution plan and do not output the running result)

Set autotrace traceonly explain (get the execution plan, do not output the running result and statistics part, only show the execution plan part)

Set autotrace traceonl statistics (do not output the run result and execution plan, only show the statistics part)

, /

Set autotrace on

SELECT *

FROM t1, t2

WHERE t1.id = t2.t1_id

AND t1.n in (18cm19)

Carry out the plan

Plan hash value: 3532430033

-

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

-

| | 0 | SELECT STATEMENT | | 2 | 8138 | 6 (0) | 00:00:01 |

| | 1 | NESTED LOOPS | | |

| | 2 | NESTED LOOPS | | 2 | 8138 | 6 (0) | 00:00:01 |

| | 3 | INLIST ITERATOR | | |

| | 4 | TABLE ACCESS BY INDEX ROWID | T1 | 2 | 4056 | 2 (0) | 00:00:01 |

| | * 5 | INDEX RANGE SCAN | T1roomN | 1 | | 1 (0) | 00:00:01 |

| | * 6 | INDEX RANGE SCAN | T2_T1_ID | 1 | | 1 (0) | 00:00:01 |

| | 7 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 2041 | 2 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

5-access ("T1". "N" = 18 OR "T1". "N" = 19)

6-access ("T1". "ID" = "T2". "T1_ID")

Note

-

-dynamic sampling used for this statement (level=2)

Statistical information

0 recursive calls

0 db block gets

12 consistent gets

0 physical reads

0 redo size

1032 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

2 rows processed

/ *

Advantages: 1. You can output statistics about the runtime (how many logical reads, how many recursive calls, how many physical reads)

two。 Although you have to wait for the statement to be executed before you can output the execution plan, you can have a traceonly switch to control the output of the returned result without hitting the screen.

Defects: 1. You must wait until the statement is actually executed before you can produce a result.

two。 You cannot see how many times the table has been accessed.

, /

3. Statistics_level=all

/ *

Step 1:alter session set statistics_level=all

Step 2: execute your SQL here

Step 3:select * from table (dbms_xplan.display_cursor (null,null,'allstats last'))

Also note:

1. If you use the / * + gather_plan_statistics * / method, you can omit step 1 and go straight to step 2, step 3.

two。 Keyword interpretation (OMem, 1Mem and User-Mem will be seen in subsequent courses):

The number of times Starts was executed for this sql.

The number of rows expected by E-Rows for the execution plan.

A-Rows is the actual number of rows returned. By comparing A-Rows with E-Rows, you can determine which step of the execution plan went wrong.

A-Time is the time it takes to actually execute each step (HH:MM:SS.FF), and from this line you can know where the sql was spent.

Buffers is the logical or consistent read actually performed at each step.

Reads is a physical read.

OMem: the total size of the workspace in the private memory (PGA) used by the current operation to complete all memory Work Aera operations

This data is estimated from optimizer statistics and performance data from the previous execution.

1Mem: when the workspace size does not meet the required size for the operation, you need to write part of the data to the temporary disk space (if you only need to write once to complete the operation

It is said to pass once, One-Pass; otherwise passed many times, Multi_Pass). This column of data is the memory needed to write to disk at one time during the last execution of the statement.

Size, which is estimated from optimizer statistics and performance data from the previous execution

User-Mem: the size of the memory workspace used for the current operation during the last execution of the statement. The size of the memory workspace in parentheses is (the number of disk swaps that occur. Once is One-Pass.

Multi_Pass if it is greater than 1 time, or OPTIMAL if no disk is used)

OMem and 1Mem are the evaluation values of memory required for execution, 0Mem is the evaluation value of memory required for the optimal execution mode, and 1Mem is the evaluation value of memory required for one-pass mode.

0Universe M is the number of times of optimal / one-pass/multipass execution. Memory consumed by Used-Mem

, /

Set autotrace off

Alter session set statistics_level=all

SELECT *

FROM t1, t2

WHERE t1.id = t2.t1_id

AND t1.n in (18cm19)

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

PLAN_TABLE_OUTPUT

-

SQL_ID 1a914ws3ggfsn, child number 0

-

SELECT * FROM T1, T2 WHERE t1.id = t2.t1_id AND t1.n in (185.19)

Plan hash value: 3532430033

-

| | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |

-

| | 0 | SELECT STATEMENT | | 1 | | 2 | 000.00 SELECT STATEMENT 00.01 | 12 |

| | 1 | NESTED LOOPS | | 1 | | 2 | 000.00 NESTED LOOPS 00.01 | 12 |

| | 2 | NESTED LOOPS | | 1 | 2 | 2 | 000.00 NESTED LOOPS 00.01 | 10 |

| | 3 | INLIST ITERATOR | | 1 | | 2 | 000.00 INLIST ITERATOR 00.01 | 5 |

| | 4 | TABLE ACCESS BY INDEX ROWID | T1 | 2 | 2 | 2 | 00lv 00.01 | 5 |

| | * 5 | INDEX RANGE SCAN | T1roomN | 2 | 1 | 2 | 00VOULARO 00.01 | 3 |

| | * 6 | INDEX RANGE SCAN | T2_T1_ID | 2 | 1 | 2 | 00Rank 00.01 | 5 |

| | 7 | TABLE ACCESS BY INDEX ROWID | T2 | 2 | 1 | 2 | 00lv 00.01 | 2 |

-

Predicate Information (identified by operation id):

5-access (("T1". "N" = 18 OR "T1". "N" = 19))

6-access ("T1". "ID" = "T2". "T1_ID")

Note

-

-dynamic sampling used for this statement (level=2)

29 rows have been selected.

/ *

Advantages: 1. You can clearly tell how much the table is accessed from STARTS.

two。 The predicted number of rows and the real number of rows can be clearly obtained from E-ROWS and A-ROWS, so that we can accurately judge whether the Oracle evaluation is accurate.

3. Although there is no specific output runtime related statistics, but the BUFFERS in the execution plan is the real logical read how much

Defects: 1. You must wait until the statement is actually executed before you can produce a result.

two。 It is impossible to control the output of the recording screen, unlike autotrace, which has traceonly to control not to output the result on the screen.

3. Can not see the number of recursive calls, can not see how much physical reading (but logical reading is the key)

, /

4. Enter the sql_id parameter through dbms_xplan.display_cursor to obtain it directly (after you know the sql_id, bring it in directly, simple, step 1)

/ *

Step 1: select * from table (dbms_xplan.display_cursor ('& sq_id')); (this method is obtained from the shared pool)

Note:

1. There is another method, select * from table (dbms_xplan.display_awr ('& sq_id')); (this is obtained in the awr performance view)

two。 If you have multiple execution plans, you can find out in a similar way

Select * from table (dbms_xplan.display_cursor ('cyzznbykb509s',0))

Select * from table (dbms_xplan.display_cursor ('cyzznbykb509s',1))

, /

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

PLAN_TABLE_OUTPUT

-

SQL_ID 1a914ws3ggfsn, child number 0

-

SELECT * FROM T1, T2 WHERE t1.id = t2.t1_id AND t1.n in (185.19)

Plan hash value: 3532430033

-

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

-

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

| | 1 | NESTED LOOPS | | |

| | 2 | NESTED LOOPS | | 2 | 8138 | 6 (0) | 00:00:01 |

| | 3 | INLIST ITERATOR | | |

| | 4 | TABLE ACCESS BY INDEX ROWID | T1 | 2 | 4056 | 2 (0) | 00:00:01 |

| | * 5 | INDEX RANGE SCAN | T1roomN | 1 | | 1 (0) | 00:00:01 |

| | * 6 | INDEX RANGE SCAN | T2_T1_ID | 1 | | 1 (0) | 00:00:01 |

| | 7 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 2041 | 2 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

5-access (("T1". "N" = 18 OR "T1". "N" = 19))

6-access ("T1". "ID" = "T2". "T1_ID")

Note

-

-dynamic sampling used for this statement (level=2)

/ *

Advantages: 1. Knowing that sql_id can get an execution plan immediately, like explain plan for, there is no need to execute.

two。 You can get a real execution plan. Stop, wait, wait, wait.

Defect 1. There is no output of runtime-related statistics (how many logical reads, how many recursive calls, how many physical reads)

two。 It is impossible to tell how many lines have been processed

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

, /

5. 10046 trace tracking

/ *

Step 1:alter session set events' 10046 trace name context forever,level 12 minutes; (enable tracking)

Step 2: execute your statement

Step 3:alter session set events' 10046 trace name context off'; (turn off tracing)

Step 4: find the file generated by the trace

Step 5:tkprof trc file target file sys=no sort=prsela,exeela,fchela (format command)

, /

Set autotace off

Alter session set statistics_level=typical

Alter session set events' 10046 trace name context forever,level 12'

SELECT *

FROM t1, t2

WHERE t1.id = t2.t1_id

AND t1.n in (18cm19)

Alter session set events' 10046 trace name context off'

Select d.value

| |'/'|

| | LOWER (RTRIM (i.INSTANCE, CHR (0) |

| |'_ ora_' |

| | p.spid |

| '.trc' trace_file_name |

From (select p.spid

From v$mystat mpen vain session s, v$process p

Where m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p

(select t.INSTANCE

FROM v$thread tje vain parameter v

WHERE v. nameplate thread'

AND (v.VALUE=0 OR t.thread#=to_number (v.value) I

(select value

From v$parameter

Where name='user_dump_dest') d

Exit

Tkprof d:\ oracle\ diag\ rdbms\ test11g\ test11g\ trace/test11g_ora_2492.trc d:\ 10046.txt sys=no sort=prsela,exeela,fchela

SELECT *

FROM t1, t2

WHERE t1.id = t2.t1_id

AND t1.n in (18cm19)

Call count cpu elapsed disk query current rows

--

Parse 1 0.00 0.00 00 00

Execute 1 0.00 0.00 00 00

Fetch 2 0.00 0.00 0 12 0 2

--

Total 4 0.00 0.00 0 12 0 2

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 94

Rows Row Source Operation

2 NESTED LOOPS (cr=12 pr=0 pw=0 time=0 us)

2 NESTED LOOPS (cr=10 pr=0 pw=0 time=48 us cost=6 size=8138 card=2)

2 INLIST ITERATOR (cr=5 pr=0 pw=0 time=16 us)

2 TABLE ACCESS BY INDEX ROWID T1 (cr=5 pr=0 pw=0 time=0 us cost=2 size=4056 card=2)

2 INDEX RANGE SCAN T1mm N (cr=3 pr=0 pw=0 time=0 us cost=1 size=0 card=1) (object id 108621)

2 INDEX RANGE SCAN T2_T1_ID (cr=5 pr=0 pw=0 time=0 us cost=1 size=0 card=1) (object id 108622)

2 TABLE ACCESS BY INDEX ROWID T2 (cr=2 pr=0 pw=0 time=0 us cost=2 size=2041 card=1)

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

-- Waited--

SQL*Net message to client 2 0.00 0.00

SQL*Net message from client 2 1.31 1.31

/ *

Advantages: 1. You can see the wait event corresponding to the SQL statement

two。 If there are function calls in the SQL statement and SQL in the SQL, they will all be listed with nowhere to hide.

3. You can easily see the number of rows processed and the resulting physical and logical reads.

4. It is convenient to see the parsing time and execution time.

5. You can track the entire package

Defects: 1. The steps are tedious and troublesome.

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

3. The predicate part of the execution plan cannot be clearly shown.

, /

6. Awrsqrpt.sql

/ *

Step 1:@?/rdbms/admin/awrsqrpt.sql

Step 2: select the breakpoints you want (begin snap and end snap)

Step 3: enter your sql_id

, /

two。 Applicable situation analysis

1. If a SQL takes a very long time to get a result, or even too slow to return a result, you can only use method 1 to see the execution plan.

two。 The easiest way to track a SQL is method 1, followed by method 2

3. If you want to observe that a SQL has multiple execution plans, you can only use methods 4 and 6.

4. If there are multiple functions in SQL, and there are multi-layer recursive calls such as SQL in the functions, you can only use method 5 to analyze them accurately.

5. To ensure that you see the real execution plan, you cannot use methods 1 and 2.

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

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.