So zeigen Sie den Oracle-Ausführungsplan an

Was ist ein Ausführungsplan?

SQL ist eine dumme Sprache. Jede Bedingung ist eine Voraussetzung für unterschiedliche Ausführungspläne. Oracle muss eine Wahl treffen und kann jeweils nur einen Zugriffspfad haben. Der Ausführungsplan ist eine Beschreibung des Ausführungsprozesses oder Zugriffspfads einer Abfrageanweisung in Oracle.

Auswahl des Ausführungsplans:

Normalerweise verfügt eine SQL über mehrere Ausführungspläne. Wie wählen wir also aus? Derjenige mit geringerem Ausführungsaufwand bedeutet bessere Leistung und schnellere Geschwindigkeit. Dieser Prozess wird als Oracle-Analyseprozess bezeichnet, und dann wird Oracle den besseren Ausführungsplan in den Shared Pool von SGA einfügen. Sie müssen es nur aus dem gemeinsam genutzten Pool abrufen und müssen es nicht erneut analysieren.

Grundlage für die Auswahl des Ausführungsplans:

Wählen Sie einen Ausführungsplan basierend auf statistischen Informationen aus.

Statistische Informationen:

Was sind statistische Informationen: Anzahl der Datensätze, Anzahl der Blöcke usw., siehe dba_tables/dba_indexes für Details

Dynamisch Stichprobe:

Oracle sammelt normalerweise jeden Tag zu einer bestimmten Zeit statistische Informationen. Wie sammelt Oracle statistische Informationen für neu erstellte Tabellen? Verwenden Sie dynamisches Sampling.

Autotrace aktivieren
Liniengröße 1000 festlegen
--SQL-Anweisung ausführen
--für diese Anweisung verwendetes dynamisches Sampling (Stufe=2) Der Schlüssel

Sechs Ausführungspläne

Oracle bietet 6 Methoden zur Erfassung von Ausführungsplänen mit jeweils unterschiedlichem Schwerpunkt:

Befolgen Sie bei der Auswahl im Allgemeinen die folgenden Regeln:

1. Wenn es lange dauert, bis die SQL-Ausführung Ergebnisse liefert, verwenden Sie Methode 1: Plan erklären für

2 Der einfachste Weg, einen bestimmten SQL-Plan zu verfolgen, ist Methode 1: Plan erklären für , gefolgt von Methode 2: Autotrace aktivieren

3 Wenn Sie mehrere Ausführungspläne einer bestimmten SQL überprüfen möchten, können Sie nur Methode 4: dbms_xplan.display_cursor oder Methode 6: awrsqrpt.sql

verwenden

4. Wenn die SQL eine Funktion enthält und die Funktion SQL enthält, gibt es mehrere Ebenen von Aufrufen. Wenn Sie sie genau analysieren möchten, können Sie nur Methode 5: 10046-Tracking verwenden

5. Um den tatsächlichen Ausführungsplan anzuzeigen, können Sie nicht Methode 1: Plan erklären und Methode 2: Autotrace aktivieren verwenden kann nur Methode 3 verwenden: Statistics_level = all

So sammelt Oracle Statistiken:

1 Indexstatistiken in einem bestimmten Zeitraum (Standard Montag bis Freitag: 22:00 Uhr, Samstag und Sonntag: 06:00 Uhr), Benutzer können diese selbst anpassen, hauptsächlich um Spitzenzeiten zu vermeiden

2 und Indizes haben einen Schwellenwert, und die Analyse wird nur dann automatisch durchgeführt, wenn der Schwellenwert überschritten wird. Wenn die Datenänderungen nicht groß sind, analysiert Oracle sie nicht.

3. Die Erfassungsmethode ist flexibel. Dies kann für eine bestimmte Partition der Partitionstabelle durchgeführt werden und ein paralleler Mechanismus kann zum Sammeln von Tabellen- und Indexinformationen verwendet werden.

So sammeln Sie statistische Informationen:

--Sammeln Sie Tabellenstatistiken Informationen

exec dbms_stats.gather_table_stats(ownname => 'AAA', tabname => 'TEST02',estimate_percent =>
10,method_opt => 'for all indexed columns');

--Indexstatistiken sammeln

exec dbms_stats.gather_index_stats(ownname => 'AAA',indname => 'ID_IDX',estimate_percent =>
10,degree => '4');

--Tabellen- und Indexstatistiken sammeln

exec dbms_stats.gather_table_stats(ownname => 'AAA',tabname => 'TEST02',estimate_percent =>
10,method_opt => 'for all indexed columns',cascade => true);

(1) Plan erklären für

SQL> show user
     USER 为 "HR"
SQL> set linesize 1000
SQL> set pagesize 2000
SQL> explain plan for
2 select *
3 from employees,jobs
4 where employees.job_id=jobs.job_id
5 and employees.department_id=50;
已解释。
 
SQL> select * from table(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------
Plan hash value: 303035560
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 45 | 4590 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 45 | 4590 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 627 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 45 | 3105 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 45 | 3105 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
filter("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
5 - filter("EMPLOYEES"."DEPARTMENT_ID"=50)
已选择19行。

Vorteile: Nein müssen tatsächlich ausgeführt werden.

Nachteile:

1 Es werden keine relevanten statistischen Informationen ausgegeben, z. B. wie viele logische Lesevorgänge, wie viele physische Lesevorgänge und wie viele rekursive Aufrufe generiert;

2. Es ist unmöglich zu beurteilen, wie viele Zeilen verarbeitet wurden

3. Es ist unmöglich zu beurteilen, wie oft die Tabelle ausgeführt wurde

( 2) Autotrace einschalten

Verwendung:

Befehlsfunktion:

SET AUTOT[RACE] OFF, um AutoTrace zu stoppen

SET AUTOT[RACE] ON auf Schalten Sie AutoTrace ein und zeigen Sie AUTOTRACE-Informationen und SQL-Ausführungsergebnisse an

SET AUTOT[RACE] TRACEONLY, um AutoTrace einzuschalten, zeigen Sie nur AUTOTRACE-Informationen an

SET AUTOT[RACE] ON EXPLAIN Schalten Sie AutoTrace ein, zeigen Sie nur EXPLAIN-Informationen von AUTOTRACE an
AUTOT[RACE] AUF STATISTIKEN EINSTELLEN. AutoTrace einschalten, nur AUTOTRACE-STATISTIK-Informationen anzeigen werden generiert, wie viele rekursive Aufrufe, wie viele physische Lesevorgänge usw.);

2. Der Ausführungsplan kann zwar erst ausgegeben werden, nachdem die Anweisung ausgeführt wurde, aber der Traceonly-Schalter kann verwendet werden um zu steuern, dass die Rückgabeergebnisse nicht auf dem Bildschirm ausgegeben werden;

Sie müssen warten, bis die SQL-Anweisung ausgeführt wird

2. Es ist nicht möglich zu sehen, wie oft auf die Tabelle zugegriffen wurde >Schritt 2: Führen Sie das zu analysierende SQL aus

Schritt 3: select * from table(dbms_xplan.display_cursor('sql_id/hash_value',null,'allstats last'));

SQL> set autotrace on
SQL> select * from employees,jobs where employees.job_id=jobs.job_id and employees.department_id=50;
--输出结果（略）
-- ...
已选择45行。
 
执行计划
----------------------------------------------------------
Plan hash value: 303035560
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 45 | 4590 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 45 | 4590 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 627 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 45 | 3105 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 45 | 3105 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
filter("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
5 - filter("EMPLOYEES"."DEPARTMENT_ID"=50)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
5040 bytes sent via SQL*Net to client
433 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
45 rows processed

Schlüsselwortinterpretation:

Anfänge: die Anzahl der SQL-Ausführungen;

2 3. R-Zeilen: Die tatsächliche Anzahl der vom Ausführungsplan zurückgegebenen Zeilen; A-Zeit: Die Ausführungszeit jedes Schritts (HH:MM:SS.FF). Sie können wissen, wo die SQL-Zeit verbracht wird;

5 Vorteile:

1. Kann klar sein, wie oft auf die Tabelle zugegriffen wurde

2、可以从E-Rows和A-Rows得到预测的行数和真实的行数，从而可以准确判断Oracle评估是否准确；

3、虽然没有准确的输出运行时的相关统计信息，但是执行计划中的Buffers就是真实的逻辑读的数值；

缺点：

1、必须要等执行完后才能输出结果；

2、无法控制结果打屏输出，不像autotrace可以设置traceonly保证不输出结果；

3、看不出递归调用，看不出物理读的数值

（4）dbms_xplan.display_cursor获取

步骤1：select * from table( dbms_xplan.display_cursor('&sql_id') ); --该方法是从共享池得到

注释：

1、还有1种方法，select * from table( dbms_xplan.display_awr('&sql_id') ); --该方法是从awr性能视图里面获取

2、如果有多个执行计划，可用以下方法查出：

select * from table(dbms_xplan.display_cursor('&sql_id',0));
select * from table(dbms_xplan.display_cursor('&sql_id',1));
*/
SQL> select * from table(dbms_xplan.display_cursor('5hkd01f03y43d'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 5hkd01f03y43d, child number 0
-------------------------------------
select * from test where table_name = 'LOG$'
Plan hash value: 2408911181
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 241 | 2 (0)|
|* 2 | INDEX RANGE SCAN | IDX_TEST_1 | 1 | | 1 (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TABLE_NAME"='LOG$')
19 rows selected

注释：如何查看1个sql语句的sql_id，可直接查看v$sql

优点：

1、知道sql_id即可得到执行计划，与explain plan for一样无需执行；

2、可得到真实的执行计划

缺点：

1、没有输出运行的统计相关信息；

2、无法判断处理了多少行；

3、无法判断表被访问了多少次；

（5）事件10046 trace跟踪

步骤1：alter session set events '10046 trace name context forever,level 12'; --开启追踪

步骤2：执行sql语句；

步骤3：alter session set events '10046 trace name context off'; --关闭追踪

步骤4：找到跟踪后产生的文件（开启10046前先用‘ls -lrt’看一下文件，执行结束后再看哪个是多出来的文件即可）

步骤5：tkprof trc文件 目标文件 sys=no sort=prsela,exeela,fchela --格式化命令

优点：

1、可以看出sql语句对应的等待事件；

2、如果函数中有sql调用，函数中有包含sql，将会被列出，无处遁形；

3、可以方便的看处理的行数，产生的逻辑物理读；

4、可以方便的看解析时间和执行时间；

5、可以跟踪整个程序包

缺点：

1、步骤繁琐；

2、无法判断表被访问了多少次；

3、执行计划中的谓词部分不能清晰的展现出来

推荐：Oracle数据库学习教程

Das obige ist der detaillierte Inhalt vonSo zeigen Sie den Oracle-Ausführungsplan an. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!