数据库中如何获取方法、查看执行顺序、统计信息

小编给大家分享一下数据库中如何获取方法、查看执行顺序、统计信息，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

个人常用的获取执行计划的方法
1.select * from table(dbms_xplan.display_cursor('v$sql.sql_id',v$sql.CHILD_NUMBER,'advanced'));  --只有执行计划
2.set autotrace traceonly exp  --只有执行计划
1与2的区别在于当SQL有绑定变量时，1更准确(毕竟sql先执行再获取的执行计划)，2可能不准确，其他情况下两者获取的执行计划基本一样



执行计划指标和统计信息指标值的总结：
1.sql执行多次不改变arraysize的情况下，每次的consistent gets不会变
2.sql执行一次改变一次arraysize的情况下，每次的consistent gets会变，但是v$sql_plan中记录的CPU_COST并没有改变，说明v$sql_plan.CPU_COST是CHILD_NUMBER对应的SQL在第一次执行时生成的
3.执行计划中的Cost (%CPU)等于v$sql_plan.COST而非v$sql_plan.CPU_COST
4.只是一个select(非select for update)时，db block gets是0
5.逻辑读（consistent gets+db block gets）和每批次处理的数据行的大小是有一定关系的。每批次处理的数据行越大，则逻辑读越小。所以减少逻辑读的一个方法就是增加arraysize。
6.执行计划中的Cost (%CPU)只和按物理相关，所以修改arraysize是没有办法减少物理读的，也就是Cost (%CPU)不变




统计信息各个指标的解释可以在官方文档关于Statistics Descriptions中找到
recursive calls:Number of recursive calls generated at both the user and system level
db block gets:Number of times a CURRENT block was requested(DML产生的逻辑读).
consistent gets:Number of times a consistent read was requested for a block(select产生的逻辑读).
physical reads:Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache（物理读）.
redo size:Total amount of redo generated in bytes
bytes sent via SQL*Net to client:Total number of bytes sent to the client from the foreground processes(可以理解为sql的查询结果的字节数)
bytes received via SQL*Net from client:Total number of bytes received from the client over Oracle Net Services 
SQL*Net roundtrips to/from client:Total number of Oracle Net Services messages sent to and received from the client(客户端和数据库服务器之间的交互次数,与arraysize(行预取数量)和rows processed相关,等于rows processed/arraysize)
sorts (memory):Number of sort operations that were performed completely in memory and did not require any disk writes
sorts (disk):Number of sort operations that required at least one disk write
rows processed:Total number of rows that the parsed SQL statement returns(可以理解为sql的查询结果的行数)



看执行计划的方法：
1、从上往下，第一个没有子节点的步骤先执行
2、对于兄弟节点，运用第一点,即靠上的节点先执行。
3、所有兄弟节点执行完以后，执行父节点。

执行顺序的原则是：由上至下找到第一个并列的两列开始，从上至下，从右向左
由上至下：在执行计划中一般含有多个节点，相同级别(或并列)的节点，靠上的优先执行，靠下的后执行
从右向左：在某个节点下还存在多个子节点，先从最靠右的子节点开始执行。


执行计划顺序为3、2、5、4、1、0


执行计划顺序为3、5、4、2、6、1、0


执行计划顺序为4、3、6、5、2、8、7、1、0


执行计划顺序为3、5、6、4、1、0




四种获取执行计划的方法
1.使用explain plan for和DBMS_XPLAN.DISPLAY
SQL> explain plan for select * from te123;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
或
SQL> SELECT id,operation,options,object_name,position from plan_table

2.使用autotrace
set autotrace on
1、执行sql
2、显示sql的结果集
3、显示执行计划
4、显示sql执行后的统计信息
-------------------------
set autotrace traceonly
or
set autotrace traceonly exp stat 
1、执行sql
2、显示执行计划
3、显示sql执行后的统计信息
-------------------------
set autotrace traceonly exp | explain
1、显示执行计划(select不会执行sql即V$SQL.EXECUTIONS不会增加，但是insert、update、delete会执行SQL的即V$SQL.EXECUTIONS会增加)
-------------------------
set autotrace traceonly stat | statistics
1、执行sql
2、显示sql执行后的统计信息

3.使用dbms_xplan.display_cursor
select * from table(dbms_xplan.display_cursor('v$sql.sql_id',v$sql.CHILD_NUMBER,'advanced'));
官方文档对display_cursor这个函数的说明里面没有advanced这个参数值，只有BASIC、TYPICAL、ALL这几个，不过实践中发现advanced这个参数值显示的内容比这几个参数值显示的都多

4.使用v$sql_plan
v$sql_plan也可以来查询某个对象的SQL执行计划
SQL>select id,operation,options,object_name,object_owner from v$sql_plan where object_name='TABLE_NAME'





如下实验证明增加arraysize可以减少逻辑读（其中Elapsed时间到达一定阶段就不再减少了），但是物理读不会减少即Cost (%CPU)不变
SQL> set timing on
SQL> set arraysize 15
SQL> select * from t1;
1024000 rows selected.
Elapsed: 00:00:11.22
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   512K|    41M|  1785   (1)| 00:00:22 |
|   1 |  TABLE ACCESS FULL| T1   |   512K|    41M|  1785   (1)| 00:00:22 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      93297  consistent gets
      12933  physical reads
          0  redo size
   96936855  bytes sent via SQL*Net to client
     751345  bytes received via SQL*Net from client
      68268  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1024000  rows processed

SQL> set arraysize 1500
SQL> select * from t1;
1024000 rows selected.
Elapsed: 00:00:07.43
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   512K|    41M|  1785   (1)| 00:00:22 |
|   1 |  TABLE ACCESS FULL| T1   |   512K|    41M|  1785   (1)| 00:00:22 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      26518  consistent gets
      12933  physical reads
          0  redo size
   88150935  bytes sent via SQL*Net to client
       7921  bytes received via SQL*Net from client
        684  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1024000  rows processed

SQL> set arraysize 5000
SQL> select * from t1;
1024000 rows selected.
Elapsed: 00:00:07.65
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   512K|    41M|  1785   (1)| 00:00:22 |
|   1 |  TABLE ACCESS FULL| T1   |   512K|    41M|  1785   (1)| 00:00:22 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      26043  consistent gets
      12933  physical reads
          0  redo size
   88088795  bytes sent via SQL*Net to client
       2663  bytes received via SQL*Net from client
        206  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1024000  rows processed
SQL>







如下证明执行计划中的Cost (%CPU)等于v$sql_plan.COST
SQL> set autotrace traceonly
SQL> select IMEID1,IMEID2,IMEID3,IMEID4,PALLET,SPARE4 from recordlist where MAINID<100000000;
419680 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 477921739
----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |    10 |  1600 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| RECORDLIST           |    10 |  1600 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_RECORDLIST_MAINID |  5778 |       |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("MAINID"<100000000)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      69335  consistent gets
      13843  physical reads
          0  redo size
   20229455  bytes sent via SQL*Net to client
     308282  bytes received via SQL*Net from client
      27980  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     419680  rows processed
SQL>


SQL> select sql_id,OPERATION,OPTIONS,OBJECT_NAME,OPTIMIZER,ID,COST,CARDINALITY,BYTES,CPU_COST,IO_COST from v$sql_plan where sql_id='cndu66r2wpa63' and CHILD_NUMBER=0;
SQL_ID          OPERATION        OPTIONS         OBJECT_NAME          OPTIMIZER   ID COST CARDINALITY   BYTES CPU_COST IO_COST
--------------- ---------------- --------------- -------------------- ---------- --- ---- ----------- ------- -------- -------
cndu66r2wpa63   SELECT STATEMENT                                      FIRST_ROWS   0    5
cndu66r2wpa63   TABLE ACCESS     BY INDEX ROWID  RECORDLIST                        1    5          10    1600    42307       5
cndu66r2wpa63   INDEX            RANGE SCAN      IX_RECORDLIST_MAINID              2    4        5778            30486       4

如下，sql执行多次不改变arraysize的情况下，每次的consistent gets不会变
SQL> set timing on
SQL> set linesize 200
SQL> set autotrace traceonly
SQL> select IMEID1,IMEID2,IMEID3,IMEID4,PALLET,SPARE4 from recordlist where MAINID<100000000;
419680 rows selected.
Elapsed: 00:00:02.30
Execution Plan
----------------------------------------------------------
Plan hash value: 477921739
----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |    10 |  1600 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| RECORDLIST           |    10 |  1600 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_RECORDLIST_MAINID |  5778 |       |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("MAINID"<100000000)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      69335  consistent gets
          0  physical reads
          0  redo size
   20229455  bytes sent via SQL*Net to client
     308282  bytes received via SQL*Net from client
      27980  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     419680  rows processed

SQL>
SQL>
SQL>
SQL> select IMEID1,IMEID2,IMEID3,IMEID4,PALLET,SPARE4 from recordlist where MAINID<100000000;
419680 rows selected.
Elapsed: 00:00:02.23
Execution Plan
----------------------------------------------------------
Plan hash value: 477921739
----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |    10 |  1600 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| RECORDLIST           |    10 |  1600 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_RECORDLIST_MAINID |  5778 |       |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("MAINID"<100000000)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      69335  consistent gets
          0  physical reads
          0  redo size
   20229455  bytes sent via SQL*Net to client
     308282  bytes received via SQL*Net from client
      27980  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     419680  rows processed

SQL>

以上是“数据库中如何获取方法、查看执行顺序、统计信息”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！