obtain SQL There are many ways to implement a plan , But sometimes it gets SQL The execution plan is not accurate , Only in SQL After the actual implementation of SQL PLAN It's true and accurate , The other way ( Such as ,explain plan) All the execution plans obtained may be due to binding variables and SQL The obtained execution plan may be inaccurate due to the influence of execution environment . about AUTOTRACE switch , When executed SET AUTOT ON and SET AUTOT TRACE The order is at this time SQL It's actually done , So the execution plan obtained at this time is accurate . and SET AUTOT TRACE EXP The order is slightly different , When it comes to SELECT When the sentence is SQL Statements don't actually execute , The execution plan obtained at this time and directly used explain plan The result of the command is consistent , But for DML The command actually executes the SQL. Here's an example , explain SET AUTOT TRACE EXP It's not really going to work SELECT sentence , Take this as an example to show how to determine SQL Whether it is actually implemented . First , We 'll do it directly SQL, Can be in V$SQLAREA See in EXECUTIONS The value of is 1.

SQL> SELECT PCT_USED,PCT_FREE FROM TEST_ENV.TB_TABLE_LIST WHERE TABLE_NAME='DBMS_SQLPATCH_STATE';

PCT_USED PCT_FREE
---------- ----------
40 10

SQL> SELECT EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT LIKE 'SELECT PCT_USED,PCT_FREE FROM TEST_%';

EXECUTIONS
----------
1

SQL> ALTER SYSTEM FLUSH SHARED_POOL;-- This operation is for the convenience of later query , The production environment should not be implemented

The system has changed .
open SET AUTOT TRACE EXP, Then repeat the above SQL, You can see EXECUTIONS The value of is 0, indicate SQL There's no real execution .

SQL> SET AUTOT TRACE EXP
SQL> SELECT PCT_USED,PCT_FREE FROM TEST_ENV.TB_TABLE_LIST WHERE TABLE_NAME='DBMS_SQLPATCH_STATE';

Implementation plan
----------------------------------------------------------
Plan hash value: 3473397811

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TB_TABLE_LIST | 1 | 23 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TB_TABLE_LIST_TBNAME | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

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

2 - access("TABLE_NAME"='DBMS_SQLPATCH_STATE')

SQL> SET AUTOTRACE OFF
SQL> SELECT EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT LIKE 'SELECT PCT_USED,PCT_FREE FROM TEST_%';

EXECUTIONS
----------
0

Another way to get SQL The real way to implement a plan is through 10046 event , In this way, we can see more fine-grained results , Such as the logical reading performed by each step 、 Physics reading and time spent, etc , This is for some complex SQL It's useful for diagnosis .

What is? 10046 event ?
10046 event yes ORACLE Provides a performance analysis tool , Can be used to track a certain session Executed SQL Statement or PL/SQL Wait events encountered in each phase of a statement 、 Logical reading of consumption 、 Physical reads 、 Time consumed 、 Execution plan, etc . 10046 event There are the following 9 An effective tracking level :

level 1 Enable standard SQL_TRACE The function of , Including time consumption 、 The number of rows processed , The number of physical reading and writing 、 Execution plan, etc
level 4 stay level 1 On the basis of variable binding
level 8 stay level 1 There are more waiting events on the basis of
level 12 namely level 4+level 8
level 16 level 1 Generate execution plan information for each execution based on (11.1 Only later versions have )
level 28 level 4+level 8+level 16
level 32 Than level 1 Less execution plan information (11.1 Only later versions have )
level 64 stay level 1 On the basis of , If a cursor takes more time than last executed 1min The above will also generate execution plan information (11.2 Only later versions have )
level 68 namely leve4 + level 64
How to use ?
Through the following three steps, we can go through 10046 event To get the execution plan :

At present session Activate 10046 event, There are two ways :
alter session set events '10046 www.shenzhenztgs.com trace name context forever,level 12' oradebug setmypid oradebug event 10046 trace name context forever,level 12

Execution target SQL
At present session close 10046 event:
alter session set events '10046 trace name context off' oradebug event 10046 trace name context off

Recommended oradebug The way , So that you can get through oradebug tracefile_name To get tracefile The absolute path of . For directly obtained tracefile Is not intuitive , It looks very hard , Can pass ORACLE Provided tkprof Come on, get it trace Translation of documents

Case operation
SQL> oradebug setmypid
Processed statements
SQL> oradebug event 10046 trace name context forever,level 12
Processed statements

SQL> select a.table_name,a.pct_used,b.column_name from test_env.tb_table_list a,test_env.tb_column_list b
where a.owner=b.owner
and a.table_name=www.javachenglei.com/ b.table_name
and b.table_name='DBMS_SQLPATCH_STATE';

.
.
.
.
DBMS_SQLPATCH_STATE 40 S_CACHED_LSINVENTORY

Have chosen 27 That's ok .

SQL> oradebug tracefile_name
C:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_3016.trc
SQL> oradebug event 10046 trace name context off
Processed statements
SQL>

C:\Users\Administrator>tkprof C:www.mhylpt.com/ \APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_2548.trc C:\explain_orcl_ora_2548.txt

TKPROF: Release 18.0.0.0.0 - Development on Friday 4 month 5 16:05:18 2019

Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
The results are as follows :

********************************************************************************

select a.table_name,a.pct_used,b.column_name from test_env.tb_table_list a,test_env.tb_column_list b
where a.owner=b.owner
and a.table_name=b.table_name
and b.table_name='DBMS_SQLPATCH_STATE'

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.01 0.07 856 863 0 27
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.03 0.08 856 863 0 27

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
27 27 27 HASH JOIN (cr=863 pr=856 pw=0 time=78538 us starts=1 cost=246 size=63 card=1)
1 1 1 TABLE ACCESS FULL TB_TABLE_LIST (cr=101 pr=99 pw=0 time=23646 us starts=1 cost=31 size=26 card=1)
27 27 27 TABLE ACCESS FULL TB_COLUMN_LIST (cr=762 pr=757 pw=0 time=42700 us starts=1 cost=214 size=444 card=12)

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
db file sequential read 2 0.01 0.01
db file scattered read 36 0.01 0.04
SQL*Net message from client 3 6.98 7.06
PGA memory operation 1 0.00 0.00

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.01 0.07 856 863 0 27
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.03 0.08 856 863 0 27

Misses in 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 6 0.00 0.00
SQL*Net message from client 6 80.00 104.95
db file sequential read 2 0.01 0.01
PGA memory operation 1 0.00 0.00
Disk file operations I/O 5 0.00 0.00
db file scattered read 36 0.01 0.04

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.00 0 0 0 0
Execute 124 0.01 0.04 0 0 0 0
Fetch 233 0.01 0.04 10 399 0 422
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 359 0.04 0.08 10 399 0 422

Misses in library cache during parse: 2
Misses in library cache during execute: 15

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 10 0.00 0.04
PGA memory operation 7 0.00 0.00
Disk file operations I/O 1 0.00 0.00

1 user SQL statements in session.
19 internal SQL statements in session.
20 SQL statements in session.
********************************************************************************
Trace file: C:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_2548.trc
Trace file compatibility: 12.2.0.0
Sort options: default

1 session in tracefile.
1 user SQL statements in trace file.
19 internal SQL statements in trace file.
20 SQL statements in trace file.
20 unique SQL statements in trace file.
2176 lines in trace file.
0 elapsed seconds in trace file.
You can see from the results SQL Implementation plan of 、 Each stage (Parse、Execute、Fetch)cpu The number of rows returned by time 、 The waiting events involved 、row source Details of the operation, etc . When we go through AUTOTRACE as well as DBMS_XPLAN The way is not certain SQL Specific performance issues ,10046 Let's get more details to locate related performance issues .

adopt 10046 event To get more relevant articles about the real implementation plan

  1. EXPLAIN PLAN obtain SQL Statement execution plan

    One . obtain SQL Statement execution plan 1. Use explain plan Load the execution plan into the table plan_table, Then query the table to get the estimated execution plan 2. Enable execution plan tracking , namely autotrace function 3 ...

  2. Oracle Query the real execution plan

    What is a real implementation plan obtain Oracle Implementation plan of , There are several ways .( This article USES the Oracle 11g XE edition , And ordinary users scott Sign in ) explain plan for There are two steps : explain pl ...

  3. Oracle The implementation plan of the project

    1 Check the execution plan 1 explan plan 2 dbms_xplan package 3 sqlplus in set autot 4 10046 5 awr The report 6 Some ready-made scripts 7 10053 The most common is just before 4 Kind of ...

  4. Oracle To understand Oracle Implementation plan

    @ Catalog 1. Introduction to the preface of the article 2. What is execution plan ? 3. How to check the execution plan ? 4. See the real implementation plan 5. understand Oracle Implementation plan 5.1 see explain 5.2 explain Execution order 5.3 interview ...

  5. oracle Get the execution plan and the pros and cons Detailed explanation

    One . Get the execution plan 6 Methods ( The detailed steps are explained in the comments section at the beginning of each example ):1. explain plan for obtain : 2. set autotrace on : 3. statistics_leve ...

  6. Oracle Analysis of several methods to obtain execution plan in

    The following is right Oracle In this paper, several methods to obtain the execution plan are analyzed and introduced in detail , Friends in need can refer to     1. Estimate the implementation plan - Explain PlanExplain plan With SQL Statement as input , Get this S ...

  7. 【Oracle】 obtain SQL Implementation plan

    One .plsql developer Tools F5         stay sqldeveloper Choose sql Press F5 You can see the execution plan         

  8. Oracle in SQL tuning (SQL TUNING) The most authoritative access to SQL Implementation plan

    This document is based on the relevant data . Sum up , Main explanation Oracle In the database , obtain SQL The most authoritative statement execution plan . The right way . step , Besides , In addition, the significance and usage of the options in each method are described in detail , In order to facilitate you and your daily work ...

  9. see SQL The actual execution plan of the statement

    DBMS_XPLAN In bag display_cursor Function is different from display function ,display_cursor Used for display SQL The actual execution plan of the statement , in the majority of cases , Showing the actual execution plan helps to better analyze SQ ...

Random recommendation

  1. Oracle Logical migration of a business user and data

    1. Determine the basic information 2. Source database export 3. Destination database import 4. Logical migration considerations 1. Determine the basic information Determine the basic information : The system type of the source database :________ Source database address :__.__.__.__ Source database version ...

  2. loadView load ( Transform into ScrollView)

    /**loadView load , Will be systematic view Transform into ScrollView*/ - (void)loadView{ [super loadView]; UIScrollView *mainScroll = ...

  3. vmware12 use unlocker206 Can I unlock it OS X System

    To download UnLocker2061.zip 2. Select the virtual machine and right-click --> attribute 3. Will download unlocker2061 After decompression, the file is put into VMware Installation directory   choice win-install.cmd file   Right ...

  4. Angular - - ngChange、ngChecked、ngClick、ngDblclick

    ngChange When the user changes the input , Execute the given expression . Expressions are executed immediately , This and javascript Of onChange Only when the change that triggers the event ends does the event perform differently . Format :ng-change=”v ...

  5. SpringCloud Note 6 :Hystrix

    Catalog Hystrix What is it? ? Hystrix Service failure newly build Hystrix project modify yml Maven Of pom.xml add to hystrix quote modify Controller Hystrix service degradation modify api ...

  6. Android UI Component of the custom control implementation IP Address control

    http://www.cnblogs.com/razerlack/p/4273282.html

  7. Vue.js Learn how to use it ( Two )—— Custom instruction

    Custom instruction In addition to the core function default built-in instructions ,Vue It is also allowed to register custom instructions . Custom directives can define global directives , You can also define local instructions . Use directives Options come from defining instructions . Define global instructions : <div ...

  8. Java Decompile code analysis ( One )

    How to read this decompiled file , No joy, no spray. . assignment Node node; Node node1 = _$3.getChildNodes().item(0); node1; node1; JVM INS ...

  9. 65. XPages Custom control ( 3、 ... and ) Advanced search 3

    RecordView The complete code of the two files of the control is given at the end of this article . Though complete , These two documents alone ,RecordView Control doesn't work yet , Because other custom controls are also referenced in these two files , Called as managed bea ...

  10. python And datetime Library Learning

    # -*- coding:utf-8 -*- import refrom datetime import datetime, timezone, timedelta def rec_time():   ...