查看SQL执行计划的几种方法:
一、 利用autotrace查看执行计划
注意:autotrace所查询的执行计划并不是真实的执行计划(这个计划是从PLAN_TABLE中来的),是CBO预估的
SQL> set autot
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
方括号内的字母都可以省略。
set autot on ----执行SQL 并且显示执行计划和统计信息
set autot trace ----执行SQL 但不显示运行结果,显示执行计划和统计信息
set autot trace exp ----如果SELECT 就不执行SQL(dml 执行),只显示执行计划
set autot trace stat ----执行SQL,只显示统计信息
注意:使用autotrace去看执行计划,最关心的是物理读,逻辑读和返回的行数。
举例说明:
SQL> set autot trace;
SQL> set line 200;
SQL> set pages 200;
SQL> select * from test where owner='SYS';
已选择109048行。
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18163 | 1755K| 1375 (1)| 00:00:17 |
|* 1 | TABLE ACCESS FULL| TEST | 18163 | 1755K| 1375 (1)| 00:00:17 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
统计信息
----------------------------------------------------------
40 recursive calls
0 db block gets
12245 consistent gets
0 physical reads
0 redo size
4983040 bytes sent via SQL*Net to client
80430 bytes received via SQL*Net from client
7271 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
109048 rows processed
发现相差很大(自我判断和实际返回的行数)于是进行统计信息的收集,再次进行分析执行计划。
SQL> select * from test where owner='SYS';
已选择109048行。
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 109K| 10M| 1376 (1)| 00:00:17 |
|* 1 | TABLE ACCESS FULL| TEST | 109K| 10M| 1376 (1)| 00:00:17 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
12175 consistent gets
0 physical reads
0 redo size
4983040 bytes sent via SQL*Net to client
80430 bytes received via SQL*Net from client
7271 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
109048 rows processed
二、注意:EXPLAIN PLAN FOR ......所查询的执行计划并不是真实的执行计划,是CBO预估的。
首先 EXPLAIN PLAN FOR SQL_TEXT;
显示普通执行计划 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
显示高级执行计划 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT=>'ADVANCED -PROJECTION'));
SQL> explain plan for select * from test where owner='SYS';
SQL> select * from table(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 109K| 10M| 1376 (1)| 00:00:17 |
|* 1 | TABLE ACCESS FULL| TEST | 109K| 10M| 1376 (1)| 00:00:17 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
已选择13行。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT=>'ADVANCED -PROJECTION'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 109K| 10M| 1376 (1)| 00:00:17 |
|* 1 | TABLE ACCESS FULL| TEST | 109K| 10M| 1376 (1)| 00:00:17 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TEST@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "TEST"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
已选择32行。
SQL> explain plan for select ename,deptno from emp where deptno in (select /*+ no_unnest */ deptno from dept where dname='CHICAGO');
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT=>'ADVANCED -PROJECTION'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2809975276
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 45 | 6 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / EMP@SEL$1
3 - SEL$2 / DEPT@SEL$2
4 - SEL$2 / DEPT@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$2" "DEPT"@"SEL$2" ("DEPT"."DEPTNO"))
FULL(@"SEL$1" "EMP"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
ALL_ROWS
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPT" "DEPT" WHERE
"DEPTNO"=:B1 AND "DNAME"='CHICAGO'))
3 - filter("DNAME"='CHICAGO')
4 - access("DEPTNO"=:B1)
已选择43行。
三、特殊执行计划(真正的执行计划)
做这个 一定要关闭 autotrace
ALTER SESSION SET STATISTICS_LEVEL=ALL; ---再运行SQL
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
例子:
create table test as select * from dba_objects;
create index test_owner_indx on test (owner);
SQL> alter session set statistics_level=all;
SQL> select count(*) from test where owner='SCOTT';
COUNT(*)
----------
12
SQL> select count(*) from test where owner='SCOTT';
COUNT(*)
----------
12
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cj55b1jx5mqxt, child number 0
-------------------------------------
select count(*) from test where owner='SCOTT'
Plan hash value: 3066878202
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 2 | INDEX RANGE SCAN| TEST_OWNER_INDX | 1 | 12 | 12 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
Note
-----
- dynamic sampling used for this statement (level=2)
已选择23行。
Starts表示这个操作执行了几次
E-Rows表示优化器估算的行数
A-Rows 表示实际的行数
A-Time 表示这个操作执行的时间(累加的)
Buffers 表示逻辑度(累加的)
方法四、真正的执行计划
查看v$sql_plan表
通过SQL语句的SQL_ID和子游标号,可以在V$SQL_PLAN表中查看到该SQL语句的执行计划。
例如:执行了语句“select * from t1;',先通过v$sqlarea和v$sql找到该语句的SQL_ID和子游标号。
SQL> select sql_id,sql_text from v$sqlarea where sql_text like '%from t1%';
SQL_ID SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
27uhu2q2xuu7r select * from t1
bf45pybkumcx5 select sql_id,sql_text from v$sqlarea where sql_text like '%from t1%'
SQL> select sql_id,child_number,sql_text from v$sql where sql_id='27uhu2q2xuu7r';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ ----------------------------------------------------------------------------------------------------
27uhu2q2xuu7r 0 select * from t1
--通过以上两条查询语句,查得目标SQL语句的SQL_ID为 “27uhu2q2xuu7r”, 子游标号为“0”.
SQL> select timestamp,operation,options,object_name,cost,id,parent_id from v$sql_plan where sql_id='27uhu2q2xuu7r' and child_number=0;
TIMESTAMP OPERATION OPTIONS OBJECT_NAME COST IDPARENT_ID
----------------- -------------------- ---------- ------------------------------ ---------- ---------- ----------
20131221 20:49:14 SELECT STATEMENT 3 0
20131221 20:49:14 TABLE ACCESS FULL T1 3 1 0
(二)、DISPLAY_AWR
显示AWR中的SQL的执行计划,对分析历史SQL慢的相当有帮组
DBMS_XPLAN.DISPLAY_AWR(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER DEFAULT NULL,
db_id IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT TYPICAL);
参数 常用说明
SQL_ID SQL的唯一标识,取SELECT SQL_ID FROM DBA_HIST_SQLTEXT
PLAN_HASH_VALUE 指定SQL语句的PLAN_HASH_VALUE,忽略取全部的SQL_ID下的执行计划
DB_ID 默认取V$DATABASE.DATABASE_ID
FORMAT 输出格式化
默认TYPICAL
ADVANCED +PEEKED_BINDS
例子查看
anit
#默认输出的格式
select * from table(dbms_xplan.display_awr('bjqjt2dfvya84'));
SQL_ID bjqjt2dfvya84
--------------------
select * from emp where empno=7521
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
| 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |
--------------------------------------------------------------------------------------
#通过格式化ADVANCED +PEEKED_BINDS
select * from table(dbms_xplan.display_awr('bjqjt2dfvya84',FORMAT=>'ADVANCED +PEEKED_BINDS'));
SQL_ID bjqjt2dfvya84
--------------------
select * from emp where empno=7521
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
| 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
2 - SEL$1 / EMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('optimizer_dynamic_sampling' 0)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
END_OUTLINE_DATA
*/
一、 利用autotrace查看执行计划
注意:autotrace所查询的执行计划并不是真实的执行计划(这个计划是从PLAN_TABLE中来的),是CBO预估的
SQL> set autot
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
方括号内的字母都可以省略。
set autot on ----执行SQL 并且显示执行计划和统计信息
set autot trace ----执行SQL 但不显示运行结果,显示执行计划和统计信息
set autot trace exp ----如果SELECT 就不执行SQL(dml 执行),只显示执行计划
set autot trace stat ----执行SQL,只显示统计信息
注意:使用autotrace去看执行计划,最关心的是物理读,逻辑读和返回的行数。
举例说明:
SQL> set autot trace;
SQL> set line 200;
SQL> set pages 200;
SQL> select * from test where owner='SYS';
已选择109048行。
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18163 | 1755K| 1375 (1)| 00:00:17 |
|* 1 | TABLE ACCESS FULL| TEST | 18163 | 1755K| 1375 (1)| 00:00:17 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
统计信息
----------------------------------------------------------
40 recursive calls
0 db block gets
12245 consistent gets
0 physical reads
0 redo size
4983040 bytes sent via SQL*Net to client
80430 bytes received via SQL*Net from client
7271 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
109048 rows processed
发现相差很大(自我判断和实际返回的行数)于是进行统计信息的收集,再次进行分析执行计划。
SQL> select * from test where owner='SYS';
已选择109048行。
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 109K| 10M| 1376 (1)| 00:00:17 |
|* 1 | TABLE ACCESS FULL| TEST | 109K| 10M| 1376 (1)| 00:00:17 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
12175 consistent gets
0 physical reads
0 redo size
4983040 bytes sent via SQL*Net to client
80430 bytes received via SQL*Net from client
7271 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
109048 rows processed
二、注意:EXPLAIN PLAN FOR ......所查询的执行计划并不是真实的执行计划,是CBO预估的。
首先 EXPLAIN PLAN FOR SQL_TEXT;
显示普通执行计划 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
显示高级执行计划 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT=>'ADVANCED -PROJECTION'));
SQL> explain plan for select * from test where owner='SYS';
SQL> select * from table(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 109K| 10M| 1376 (1)| 00:00:17 |
|* 1 | TABLE ACCESS FULL| TEST | 109K| 10M| 1376 (1)| 00:00:17 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
已选择13行。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT=>'ADVANCED -PROJECTION'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 109K| 10M| 1376 (1)| 00:00:17 |
|* 1 | TABLE ACCESS FULL| TEST | 109K| 10M| 1376 (1)| 00:00:17 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TEST@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "TEST"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
已选择32行。
SQL> explain plan for select ename,deptno from emp where deptno in (select /*+ no_unnest */ deptno from dept where dname='CHICAGO');
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT=>'ADVANCED -PROJECTION'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2809975276
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 45 | 6 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / EMP@SEL$1
3 - SEL$2 / DEPT@SEL$2
4 - SEL$2 / DEPT@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$2" "DEPT"@"SEL$2" ("DEPT"."DEPTNO"))
FULL(@"SEL$1" "EMP"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
ALL_ROWS
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPT" "DEPT" WHERE
"DEPTNO"=:B1 AND "DNAME"='CHICAGO'))
3 - filter("DNAME"='CHICAGO')
4 - access("DEPTNO"=:B1)
已选择43行。
三、特殊执行计划(真正的执行计划)
做这个 一定要关闭 autotrace
ALTER SESSION SET STATISTICS_LEVEL=ALL; ---再运行SQL
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
例子:
create table test as select * from dba_objects;
create index test_owner_indx on test (owner);
SQL> alter session set statistics_level=all;
SQL> select count(*) from test where owner='SCOTT';
COUNT(*)
----------
12
SQL> select count(*) from test where owner='SCOTT';
COUNT(*)
----------
12
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cj55b1jx5mqxt, child number 0
-------------------------------------
select count(*) from test where owner='SCOTT'
Plan hash value: 3066878202
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 2 | INDEX RANGE SCAN| TEST_OWNER_INDX | 1 | 12 | 12 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
Note
-----
- dynamic sampling used for this statement (level=2)
已选择23行。
Starts表示这个操作执行了几次
E-Rows表示优化器估算的行数
A-Rows 表示实际的行数
A-Time 表示这个操作执行的时间(累加的)
Buffers 表示逻辑度(累加的)
方法四、真正的执行计划
查看v$sql_plan表
通过SQL语句的SQL_ID和子游标号,可以在V$SQL_PLAN表中查看到该SQL语句的执行计划。
例如:执行了语句“select * from t1;',先通过v$sqlarea和v$sql找到该语句的SQL_ID和子游标号。
SQL> select sql_id,sql_text from v$sqlarea where sql_text like '%from t1%';
SQL_ID SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
27uhu2q2xuu7r select * from t1
bf45pybkumcx5 select sql_id,sql_text from v$sqlarea where sql_text like '%from t1%'
SQL> select sql_id,child_number,sql_text from v$sql where sql_id='27uhu2q2xuu7r';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ ----------------------------------------------------------------------------------------------------
27uhu2q2xuu7r 0 select * from t1
--通过以上两条查询语句,查得目标SQL语句的SQL_ID为 “27uhu2q2xuu7r”, 子游标号为“0”.
SQL> select timestamp,operation,options,object_name,cost,id,parent_id from v$sql_plan where sql_id='27uhu2q2xuu7r' and child_number=0;
TIMESTAMP OPERATION OPTIONS OBJECT_NAME COST IDPARENT_ID
----------------- -------------------- ---------- ------------------------------ ---------- ---------- ----------
20131221 20:49:14 SELECT STATEMENT 3 0
20131221 20:49:14 TABLE ACCESS FULL T1 3 1 0
(二)、DISPLAY_AWR
显示AWR中的SQL的执行计划,对分析历史SQL慢的相当有帮组
DBMS_XPLAN.DISPLAY_AWR(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER DEFAULT NULL,
db_id IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT TYPICAL);
参数 常用说明
SQL_ID SQL的唯一标识,取SELECT SQL_ID FROM DBA_HIST_SQLTEXT
PLAN_HASH_VALUE 指定SQL语句的PLAN_HASH_VALUE,忽略取全部的SQL_ID下的执行计划
DB_ID 默认取V$DATABASE.DATABASE_ID
FORMAT 输出格式化
默认TYPICAL
ADVANCED +PEEKED_BINDS
例子查看
anit
#默认输出的格式
select * from table(dbms_xplan.display_awr('bjqjt2dfvya84'));
SQL_ID bjqjt2dfvya84
--------------------
select * from emp where empno=7521
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
| 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |
--------------------------------------------------------------------------------------
#通过格式化ADVANCED +PEEKED_BINDS
select * from table(dbms_xplan.display_awr('bjqjt2dfvya84',FORMAT=>'ADVANCED +PEEKED_BINDS'));
SQL_ID bjqjt2dfvya84
--------------------
select * from emp where empno=7521
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
| 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
2 - SEL$1 / EMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('optimizer_dynamic_sampling' 0)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
END_OUTLINE_DATA
*/