postgresql 执行计划 cost sqldeveloper 执行计划

转载

网络安全战士 2024-04-01 06:33:10

一、 PL/SQL Dev工具中的F5按钮

这是最简单的获取执行计划的方法，通过SQL语句一键获取，不需要做什么

postgresql 执行计划 cost sqldeveloper 执行计划_数据库

这种方式获取的执行计划是Oracle通过统计信息预估的，因为SQL语句并没有真正执行。这种方式获取的执行计划也不会显示谓词过滤信息和排序信息等其他附加信息。

二、 EXPLAIN PLAN命令

SQL> explain plan for select * from t_bid_app t where t.app_id =:1;
Explained
SQL> select * from table(DBMS_XPLAN.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2111836046
--------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    59 |     2
|   1 |  TABLE ACCESS BY INDEX ROWID| T_BID_APP        |     1 |    59 |     2
|*  2 |   INDEX UNIQUE SCAN         | T_BID_APP_APP_ID |     1 |       |     1
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T"."APP_ID"=TO_NUMBER(:1))
14 rows selected

通过explain plan命令结合DBMS_XPLAN包来获取SQL语句的执行计划。同样，这个执行计划是Oracle用统计信息估算的，SQL语句并没有实际执行，不一定是真实执行时走的执行计划。

三、 DBMS_XPLAN包

除了结合explain plan命令外，DBMS_XPLAN包还提供几种方式获取SQL语句的执行计划。

获取当前session上一条已经执行过的SQL语句的执行计划

SQL> set linesize 100;
SQL> set pagesize 800;
SQL> select * from t_bid_app where app_id=1234;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  b8xdya7qfkucm, child number 0
-------------------------------------
select * from t_bid_app where app_id=1234
Plan hash value: 2111836046
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_BID_APP        |     1 |    59 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T_BID_APP_APP_ID |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T_BID_APP@SEL$1
   2 - SEL$1 / T_BID_APP@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T_BID_APP"@"SEL$1" ("T_BID_APP"."APP_ID"))
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("APP_ID"=1234)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "APP_ID"[NUMBER,22], "T_BID_APP"."MATTER_ID"[NUMBER,22],
       "T_BID_APP"."USER_ID"[NUMBER,22], "T_BID_APP"."QUAL_AUD"[NUMBER,22],
       "T_BID_APP"."SITE_AUD"[NUMBER,22], "T_BID_APP"."QUAL_USER_ID"[NUMBER,22],
       "T_BID_APP"."SITE_USER_ID"[NUMBER,22], "T_BID_APP"."APP_TIME"[DATE,7],
       "T_BID_APP"."QUAL_AUD_TIME"[DATE,7], "T_BID_APP"."SITE_AUD_TIME"[DATE,7]
   2 - "T_BID_APP".ROWID[ROWID,10], "APP_ID"[NUMBER,22]
49 rows selected.

通过这种方式获取的执行计划是真实的，SQL语句已经真实执行过。

通过sql_id获取一条已经执行过的SQL语句的执行计划

SQL> select * from table (dbms_xplan.display_cursor('259n3s6dqgst8',0,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  259n3s6dqgst8, child number 0
-------------------------------------
select * from t_bid_app t where t.app_id=1234
Plan hash value: 2111836046
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_BID_APP        |     1 |    59 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T_BID_APP_APP_ID |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
   2 - SEL$1 / T@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T_BID_APP"."APP_ID"))
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T"."APP_ID"=1234)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "T"."APP_ID"[NUMBER,22], "T"."MATTER_ID"[NUMBER,22], "T"."USER_ID"[NUMBER,22],
       "T"."QUAL_AUD"[NUMBER,22], "T"."SITE_AUD"[NUMBER,22], "T"."QUAL_USER_ID"[NUMBER,22],
       "T"."SITE_USER_ID"[NUMBER,22], "T"."APP_TIME"[DATE,7], "T"."QUAL_AUD_TIME"[DATE,7],
       "T"."SITE_AUD_TIME"[DATE,7]
   2 - "T".ROWID[ROWID,10], "T"."APP_ID"[NUMBER,22]
48 rows selected.

通过这种方式获取的执行计划是真实的，SQL语句已经真实执行过，但需要sql语句未被换出shared pool。如果SQL语句被换出shared pool或者执行alter system flush shared_pool，就无法获取执行计划了

通过AWR报告中的sql_id获取一条已经执行过的SQL语句的执行计划

这种方式常用于查询已经被换出shared pool 但已经被收集到AWR报告中的语句的执行计划。

select * from table(dbms_xplan.display_awr(sql_id))

同样，这种方式获取的执行计划也是真实的。

四、 AutoTrace

通过set autotrace on| traceonly| traceonly explain;来获取执行计划，这种方法虽然也执行了sql语句，但是执行计划不一定是真实的。

SQL> set autotrace on;
SQL> select * from t_bid_app where app_id=1234;
 
no rows selectedExecution Plan
----------------------------------------------------------
Plan hash value: 2111836046
 
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    59 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_BID_APP        |     1 |    59 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T_BID_APP_APP_ID |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("APP_ID"=1234)Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        792  bytes sent via SQL*Net to client
        468  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

引用一个经典的例子(不是我做的)来证明:autotrace on和explain plan的结果是使用index range scan，而实际执行计划是index fast full scan。如下所示

SQL> create table T1 as select * from dba_objects;
Table created.
SQL> insert into t1 select * from t1;
72827 rows created.
SQL> /
145654 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from t1;
  COUNT(*)
----------
    291308
SQL> create index idx_t1 on t1(object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats('SALP','T1');
PL/SQL procedure successfully completed.
SQL> set linesize 120;
SQL> set pagesize 800;
SQL> var x number;
SQL> var y number;
SQL> exec :x :=0
PL/SQL procedure successfully completed.
SQL> exec :y :=100000
PL/SQL procedure successfully completed.
SQL> set autotrace on;
SQL> select count(*) from t1 where object_id between :x and :y;
  COUNT(*)
----------
    291304
Execution Plan
----------------------------------------------------------
Plan hash value: 2351893609
-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |     5 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |        |     1 |     5 |            |          |
|*  2 |   FILTER           |        |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| IDX_T1 |   728 |  3640 |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_NUMBER(:Y)>=TO_NUMBER(:X))
   3 - access("OBJECT_ID">=TO_NUMBER(:X) AND "OBJECT_ID"<=TO_NUMBER(:Y))
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        656  consistent gets
          0  physical reads
          0  redo size
        348  bytes sent via SQL*Net to client
        479  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> set autotrace off;
SQL> explain plan for select count(*) from t1 where object_id between :x and :y;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2351893609
-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |     5 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |        |     1 |     5 |            |          |
|*  2 |   FILTER           |        |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| IDX_T1 |   728 |  3640 |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_NUMBER(:Y)>=TO_NUMBER(:X))
   3 - access("OBJECT_ID">=TO_NUMBER(:X) AND "OBJECT_ID"<=TO_NUMBER(:Y))
16 rows selected.
SQL> select count(*) from t1 where object_id between :x and :y;
  COUNT(*)
----------
    291304
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  9dhu3xk2zu531, child number 0
-------------------------------------
select count(*) from t1 where object_id between :x and :y
Plan hash value: 1410530761
---------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |       |       |   178 (100)|          |
|   1 |  SORT AGGREGATE        |        |     1 |     5 |            |          |
|*  2 |   FILTER               |        |       |       |            |          |
|*  3 |    INDEX FAST FULL SCAN| IDX_T1 |   291K|  1422K|   178   (2)| 00:00:01 |
---------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / T1@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_FFS(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))
      END_OUTLINE_DATA
  */
Peeked Binds (identified by position):
--------------------------------------
   1 - :X (NUMBER): 0
   2 - :Y (NUMBER): 100000
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:Y>=:X)
   3 - filter(("OBJECT_ID">=:X AND "OBJECT_ID"<=:Y))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=0) COUNT(*)[22]

52 rows selected.

五、 10046事件

10046事件是Oracle诊断事件之一，通过下面的命令开启

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

或

oradebug event '10046 trace name context forever, level 12'

开启10046事件的会话会在user_dump_dest或diagnostic_dest目录下生成裸trace文件，通过tkprof格式化后可以成为较友好的格式，其中会记录开启10046期间所有执行过的语句的真实执行计划，因为10046事件是一大块内容，这里就不展开说明了，常规情况下一到四的方法足够使用了。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。