Oracle execution plan view method and pros and cons comparison

Execute plan six pulse Excalibur

Explain plan for set linesize 1000 set pagesize 2000 Explain plan for SELECT * FROM T1, t2 WHERE t1.id = t2.t1_id AND tl .n in (18, 19); select * from table (dbms_xplan.display()); /* Advantages: 1. No need to really execute, fast and convenient defect 1. No runtime statistics are output (how many logical reads were generated, how many recursive calls, how many physical reads were made); 2. Cannot determine how many rows are processed; 3. Cannot determine how many times the table is accessed. That's true, since there is no actual execution and how do you know the statistics generated by the actual execution? */ set autotrace on /* Run time statistics (how many logical reads were generated, how many recursive calls were made, how many physical reads were made). Although the execution plan can be output only after the statement is executed, the traceOnly switch can be used to control the return of the result without screen output. 1. The statement must be executed before the result can be produced. 2. You cannot see how many times the table has been accessed. Statistics_level =all /* Step 1: ALTER session set STATISticS_level =all Select * from table(DBMS xplay.display_CURSOR (null, NULL, 'allSTATS last')); 1. If you have /*+ gather_plan_statistics */, skip steps 1 and go to steps 2 and 3. (OMem, lMem, and user-mem are used in this course.) E-rows Indicates the expected number of Rows for the execution plan. A-rows is the actual number of Rows returned. A-rows and e-Rows are compared to determine which step of the execution plan went wrong. A-time indicates the actual execution Time of each step (HH:MM: ss.ff). Based on this line, you can know where the SQL Time is. Buffers are the logical or generative reads that are actually performed for each step. Reads are physical Reads. OMem: The total amount of Work in private memory (PGA) used by the current operation to complete all Work Aera operations, which is estimated from optimizer statistics and performance data from the previous execution. LMem: If the workspace size cannot meet the operation requirements, some data needs to be written to the temporary disk space. If the operation can be completed only once, the operation is called one-pass. Otherwise, multiple passes, multi-pass). This column is the amount of memory required for a single write to disk in the last execution of the language, which is estimated from optimizer statistics and performance data from the previous execution. User - Mem: In parentheses, is (Number of disk swaps occurred once: one-pass; if the number is greater than One, multi-pass. If no disk swap is used, OPTIMAL is displayed.) OMem and lMem indicate the memory required for execution, OMem indicates the memory required for OPTIMAL execution mode, and lMem indicates the memory required for one-pass mode. 0/1/M is the number of optimal/one-pass/multipass operations. */ /* -- Advantages: 1. You can clearly deduce from ST block TS how many times the table is accessed; 2. The predicted number of ROWS and the real number of ROWS can be clearly obtained from e-Rows and A-Rows, so as to accurately judge whether the Oracle evaluation is accurate; 3. Although there is no specific output for runtime statistics, BUFFERS in the execution plan are the actual logical read values. Defect 1. The statement must be executed before the result can be produced. 2. There is no control over recording screen output, unlike AutoTrace, which has TraceOnly, which can control the output of screen output. 3. Can't see the number of recursive calls, */ select * from * * step L select * from * * table(dbms_xplan.display_cursor('&sql_id'); Select * from table(dbms_xplan.display_awr('&sql_id'); 2. If there are multiple execution plans, use a similar method to query them: select * from table(dbMS_xplan. display_CURSOR (' cyzznByKB509s',0)); Select * from table (dbms_xplan display_cursor (' cyzznbykb509s', 1)); */ /* One advantage, l. If you know the sql_id, you can get the execution plan immediately. It does not need to be executed like explain plan for. You can get a real execution plan. No runtime statistics (how many logical reads, how many recursive calls, how many physical reads were generated) 2. Cannot tell how many rows are processed; 3. Cannot determine how many times the table is accessed. */ * awrsqrpt. SQL step 1: @? / RDBMS/admin/awrsqrpt. SQL step 2: choose your breakpoint (begin the snap and end the snap) step 3: enter your sql_id six or 10046 trace tracking / * step 1: alter session set events '10046 trace name context forever,level 12'; Step 2 Run the SQL statement. Step 3: ALTER session set events '10046 trace name context off'; Step 5: tkprof TRC file target file sys=no sort=prsela,exeela, fchela (format command) * / Step 4: select d.value || '/' || LOWER(RTRIM(i.INSTANCE,CHR(0))) ||'_ora_' || p.spid ||'.trc' trace_file_name from (select p.spid from v$mystat m, v$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 t, v$parameter v WHERE v.name='thread' AND(v.VALUE=0 OR t.thread#=to_number(v.value)) ) i, (select value from v$parameter where name='user_dump_dest') d; tkprof /oracle/app/oracle/diag/rdbms/PROD/PRODSTB/trace/PROD_ora_7238.trc /tmp/10046_sql.log sys=no sort=prsela,exeela,fchela cat /tmp/10046_sql.log ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 00 0 Execute 2 0.00 00 00 Fetch 2 1.08 1.78 9568 10117 01 ------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- total 6 9568 10117 0 1 Misses in 1.09 1.79 library cache during parse: 1 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to Client 3 0.00 0.00 SQL*Net message from Client 3 31.01 55.20 DB file sequential read 130 0.01 0.13 db File scattered Read 274 0.03 1.07 OVERALL TOTALS FOR ALL STATEMENTS Call count CPU Elapsed Disk Query current Rows -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- the Parse 0.00 0.00 0 0 0 0 0 the Execute 0 0.00 0.00 0 0 0 0 Fetch 0.00 0.00 0 0 0 0 0 -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- ---------- total 0 0.00 0.00 00 0 Misses in library cache during parse: 0 2 user SQL statements in session. 0 internal SQL statements in session. 2 SQL statements in session. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * / * a advantages: 1. If there is a function call in the SQL ten-day, it will be listed, and there is no hiding. We can easily see the number of rows processed, the number of physical logical reads produced, 4. We can easily see the parsing time and execution time I 5. You can trace the entire package. Defects 1. Cumbersome steps, more trouble; Unable to determine how many times the table has been accessed. 3. The predicate part of the execution plan is not clearly displayed. * /Copy the code

What is the difference between

If an SOL execution takes a long time to produce a result or does not return a result, only method 1 can be used. The easiest way to track an SOL is method 1, followed by method 2. To view multiple execution plans for an SOL, use methods 4 and 5. If SOL contains functions and SOL is set within the function, there are multiple calls. Method 6 can only be used for accurate analysis. Methods 1 and 2 cannot be used to ensure that you see the actual execution plan. To get the number of times the table was accessed, you can only use method 3.

Get a summary of methods for the execution plan

SQLHC

As well as explain Plan for, you can also use the F5 key of PL/SQL Developer tool to view the execution plan, SQL Monitor tool can also view, of course more advanced SQLHC, upload SQLHC file, Type T and sqlid to generate. SQLHC is short for SQL Health Check. It can collect SQL related information such as tables, indexes, statistics, optimizer parameters, SQL execution status, waiting events, etc. It can help you check SQL problems and optimize SQL.

Run the sqlplus/as sysdba command

SQL> @/home/oracle/tmp/sqlhc.sql T 9a4tv1dduu9u4

or

SQL> @/home/oracle/tmp/sqlhc.sql

Parameter 1: Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)

Enter value for 1: T

PL/SQL procedure successfully completed.

Parameter 2: SQL_ID of the SQL to be analyzed (required)

Enter value for 2: 9a4tv1dduu9u4 <—- Enter sql_id. Wait about 5 minutes

May be longer or shorter (according to the AWR save cycle, dictionary table size varies greatly, the general system should be able to complete within 5 minutes), no impact on the database. The execution process includes log and screen display. Insert data into plan_table and rollback.

The file name starts with SQLHC, followed by date, time, and SQL_ID. Sqlhc_20211125_1810_9a4TV1dDUu9u4.zip

Four of the HTML files and log.zip are normally present.

The 10053 trace file must be generated at least version 11.2 with the SQL_ID still in the library cache.

If * SQL_monitor. zip is also included in the SQLHC package, your SQL execution time is more than 5 seconds, or the SQL is in parallel and still in SQL Monitor memory when SQLHC is collected. SQL Monitor is very helpful for analyzing SQL execution plans. If you encounter problems and collect SQLHC information in a timely manner, you are more likely to collect SQL Monitor files. If SQLHC is not collected more than half an hour after an SQL execution, SQL Monitor information is very likely to be flushed out of memory.

The three main HTML files analyzed are:

*_health_check.html

*_diagnostics.html

*_execution_plan.html

The content is very rich, you can collect some more to see.