导航：首页 > 数据库 >

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

发表于：2024-10-18 作者：千家信息网编辑

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

千家信息网最后更新 2024年10月18日数据库中如何获取方法、查看执行顺序、统计信息

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

个人常用的获取执行计划的方法
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>