SQL调优:Clustering Factor影响数据删除速度一例

事情是这样的,客户有一套核心的10g业务数据库,需要针对个别大表删除2年前的归档数据,这些表都是普通的堆表(heap table),没有使用分区或其他技术。因为考虑到不能影响在线业务,所以不能使用insert append/rename的方式来加速删除,只能老老实实地在匿名PL/SQL块里通过rowid批量删除数据,虽然慢一些但还是能接受的,具体的PL/SQL块如下:

DECLARE
   CURSOR table_name_cur IS SELECT /*+ FULL(a) */ a.rowid from table_name a  where time_column<required_date
   table_name_rec table_name_cur%ROWTYPE;
   row_number number;
BEGIN
   row_number :=0;
   OPEN table_name_cur;
   LOOP
      FETCH table_name_cur INTO table_name_rec;
      IF table_name_cur%NOTFOUND
      THEN
      commit;
         EXIT;
      END IF;
      delete from table_name WHERE rowid = table_name_rec.rowid;
      row_number := row_number + 1;
           if (mod (row_number,1000) =0) then
           insert into delete_rows values (row_number);
           commit;
           end if;
   END LOOP;
  insert into delete_rows values (row_number);
   commit;
   CLOSE table_name_cur;
END;
/

可以看到以上使用一个游标FULL SCAN目标数据表取出所需删除行的rowid,之后在循环中不断fetch出rowid并实际删除数据。

问题出在一张不是非常大的LG表上(不超过10GB),删除这张LG表消耗的时间超过10个小时,而其他更大的表删除也仅用2-3个小时。

针对这一反常现象,客户对删除操作做了10046 level8的跟踪,因为整个删除过程比较长,所以仅 trace了一小段时间,因为这个trace并不完整所以没有办法使用tkprof工具分析该trace。没办法,就分析裸trace信息吧。

从trace内容来看,该时段内主要的等待是db file sequence read(简称DFSR)即数据文件单块读事件,一开始以为是表上有链式行/迁移行造成了大量的DFSR,但客户日常有对该表执行chained rows analyze,没有发现该表上有明显的chained/migrated rows问题。

具体观察该DFSR事件的p1/p2 obj#参数发现这些数据文件单块读主要是针对该LG表的2个索引的,而且最为奇怪的是其中一个索引单块读的频率远多于另外一个索引,比例大约为60:1。这2个索引的差异表现,让我意识到得问题的所在,查看dba_indexes索引视图发现最近一次分析是在4/18日,而2个索引统计信息间最大的差异不在于索引大小,而在于clustering_factor也就是我们说的聚集因子, LG表上大约有6000万条数据,索引A的clustering_factor为170万,而索引B的clustering_factor达到了3400万,即2个索引的聚集因子差20倍,显然这时因为索引A上column更为有序(可能是sequence)而索引B上的字段较为随机造成了这种反差。

因为一开始使用FULL SCAN目标数据表来获取所需的ROWID,所以在实际删除前相关数据行所在的表数据块已经被FULL SCAN读取到buffer cache中了,FULL SCAN使用scattered read多块读,在这里不是主要的性能瓶颈。最主要的问题在于,假设一个table data block中有20行数据,对于clustering_factor较低的索引A而言可能这20行数据都映射到索引的一个leaf block中,而对于clustering_factor很高的索引B而言可能这20行数据需要映射到20个leaf block中,那么如果我们要删除这个数据块中的20行数据,就需要访问索引A上的1个leaf块和索引B上的20个leaf块,因为这些都是历史归档数据,所以日常已经没有业务访问这些old leaf block了,所以这部分的叶子块几乎不会在buffer cache中,服务进程需要把它们”一一”(这里真的是一一,一次读一块)从disk上读取到缓存中;最糟糕的还不止于此,因为实例的buffer cache有限,索引B上的leaf block在读入后可能很快被踢出buffer cache,而因为table与索引B间数据的无序性,这些leaf block可能需要在后续的删除中再次访问,这将对索引B的物理读取代价大大放大了。这种代价反映在Oracle wait interface上就是用户频繁地看到针对某个索引的”db file sequential read”等待事件。

我们通过还原现场,来深入了解一下clustering factor对于删除的影响:

首先要构建一张有一列极端有序,而一列极端无序的数据表
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> create table sample nologging tablespace users as select rownum t1  from dual  connect by level<=900000; 
Table created. 
SQL> alter table sample add t2 number;
Table altered.
SQL> update sample set t2=dbms_random.value(1,999999999999999);
900000 rows updated.
SQL> commit;
Commit complete.
SQL> create index ind_t1 on sample(t1) nologging;
Index created.
SQL> create index ind_t2 on sample(t2) nologging;
Index created.
SQL> exec dbms_stats.gather_table_stats('MACLEAN','SAMPLE',cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> select blocks,NUM_ROWS from dba_tables where table_name='SAMPLE';
BLOCKS   NUM_ROWS
---------- ----------
13213     900000
SQL> select CLUSTERING_FACTOR,LEAF_BLOCKS,DISTINCT_KEYS,index_name from dba_indexes where table_name='SAMPLE';
CLUSTERING_FACTOR LEAF_BLOCKS DISTINCT_KEYS INDEX_NAME
----------------- ----------- ------------- ------------------------------
899782        4148        896896 IND_T2
14975        2004        900000 IND_T1
/* 以上构建了一张90万行的数据表,共13213个block
T1列通过序列产生,较为有序
T2列通过随机数产生,无序
ind_t1索引构建在t1列上,clustering_factor较低14975,接近表上数据块的总数
ind_t2索引构建在t2列上,clustering_factor为899782,接近表上数据行的总数
*/  
SQL> alter session set events '10046 trace name context forever,level 8';
Session altered.
SQL> set timing on;
DECLARE
CURSOR table_name_cur IS SELECT /*+ FULL(a) */ a.rowid from sample a  where t1<=900000;
table_name_rec table_name_cur%ROWTYPE;
row_number number;
BEGIN
row_number :=0;
OPEN table_name_cur;
LOOP
FETCH table_name_cur INTO table_name_rec;
IF table_name_cur%NOTFOUND
THEN
commit;
EXIT;
END IF;
delete from sample WHERE rowid = table_name_rec.rowid;
row_number := row_number + 1;
if (mod (row_number,1000) =0) then
insert into delete_rows values (row_number);
commit;
end if;
END LOOP;
insert into delete_rows values (row_number);
commit;
CLOSE table_name_cur;
END;
/
Elapsed: 00:03:28.52 
观察其trace文件,可以发现在多次EXEC/FETCH后就会紧跟一个db file sequential read等待事件 
众所周知db file sequential read等待事件在如v$session/V$session/v$active_session_history
等动态性能视图中的p1代表file号,p2为block号,p3为读取block总数,一般为1
而在10046 trace中可以直接看到file#,block#,blocks和obj#,为了分辨单块读的对象,可以直接从obj#了解
SQL> select object_id,object_name,object_type from dba_objects where object_name in ('SAMPLE','IND_T1','IND_T2');
OBJECT_ID OBJECT_NAME          OBJECT_TYPE
---------- -------------------- -------------------
1307548 IND_T1               INDEX
1307549 IND_T2               INDEX
1307547 SAMPLE               TABLE
WAIT #3: nam='db file sequential read' ela= 283 file#=6 block#=3311 blocks=1 obj#=1307549 tim=1275797217728516
EXEC #3:c=999,e=349,p=1,cr=2,cu=8,mis=0,r=1,dep=1,og=1,tim=1275797217728552
FETCH #2:c=0,e=5,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=1275797217728578
EXEC #3:c=0,e=49,p=0,cr=1,cu=8,mis=0,r=1,dep=1,og=1,tim=1275797217728641
FETCH #2:c=0,e=4,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=1275797217728663
EXEC #3:c=0,e=36,p=0,cr=1,cu=8,mis=0,r=1,dep=1,og=1,tim=1275797217728712
FETCH #2:c=0,e=3,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=1275797217728732
WAIT #3: nam='db file sequential read' ela= 205 file#=6 block#=3956 blocks=1 obj#=1307549 tim=1275797217728979
EXEC #3:c=0,e=265,p=1,cr=1,cu=8,mis=0,r=1,dep=1,og=1,tim=1275797217729010
FETCH #2:c=0,e=5,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=1275797217729036
[oracle@rh2 udump]$ cat g10r2_ora_5190.trc|grep "db file sequential read"|wc -l
72395
[oracle@rh2 udump]$ cat g10r2_ora_5190.trc|grep "db file sequential read"|grep 1307549|wc -l
67721
[oracle@rh2 udump]$ cat g10r2_ora_5190.trc|grep "db file sequential read"|grep 1307548|wc -l
3878
/* 以上object_id 1307549对应为较高clustering_factor的索引IND_T2,
该索引发生了绝大多数db file sequential read等待
而object_id 1307548对应为较低clustering_factor的索引IND_T1,
该索引发生了较少量的db file sequential read等待
*/
SQL> select sql_id,executions,disk_reads,user_io_wait_time/1000000,elapsed_time/1000000
2  from v$sql
3  where sql_text='DELETE FROM SAMPLE WHERE ROWID = :B1 ';
SQL_ID        EXECUTIONS DISK_READS USER_IO_WAIT_TIME/1000000 ELAPSED_TIME/1000000
------------- ---------- ---------- ------------------------- --------------------
31m4m2drt2t5m     900000      74936                 67.862581           147.743482
[oracle@rh2 udump]$ tkprof g10r2_ora_5190.trc 5190.tkf sys=no
DELETE FROM SAMPLE
WHERE
ROWID = :B1
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 900000     78.67     147.73      74936     916440    6401613      900000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   900001     78.67     147.73      74936     916440    6401613      900000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64     (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on                             Times   Max. Wait  Total Waited
----------------------------------------   Waited  ----------  ------------
SQL*Net message to client                       1        0.00          0.00
SQL*Net message from client                     1       21.99         21.99
 db file sequential read                     72362        0.05         67.60
db file scattered read                        543        0.00          0.25
log file switch completion                     29        0.97          5.81
free buffer waits                             268        0.01          2.83
latch: cache buffers lru chain                  3        0.00          0.00
latch: object queue header operation            5        0.00          0.00
log file switch (checkpoint incomplete)        22        0.97          8.46
latch: In memory undo latch                     2        0.00          0.00
latch: cache buffers chains                     1        0.00          0.00

可能是受限于固化的思维,在我的潜意识中总是觉得clustering_factor聚集因子只会影响select查询语句,而忽略了其对update/delete操作的影响;事实是clustering_factor(注意它只是一个统计信息指标,而非参数)反映了数据在表中的随机分布程度,当表上的数据分布无序时表和索引间的交叉访问将显得很糟糕,这种交叉访问并不局限于查询语句(一个典型可能是INDEX RANGE SCAN-TABLE ACCESS BY INDEX ROWID),也可能发生在DML操作所隐含的维护索引操作中。

显然除了通过以某些列的顺序整理表外没有太好的方法来降低clustering_factor,但实际上这样做是不可能的。首先定期有序化整理的成本过高了,其次如果表上有多个单列上的不同索引,如我们上述演示中的t1、t2列,如果以t2列的顺序整理表那么一个很可能的结果是t1列上的索引的clustering factor猛增,如:

SQL> create table ordered_sample nologging tablespace users as select * From sample order by t2;
Table created.
SQL> truncate table sample;
Table truncated.
SQL> insert /*+ append */ into sample select * from ordered_sample;
900000 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('MACLEAN','SAMPLE',cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> select clustering_factor,index_name from dba_indexes where table_name='SAMPLE';
CLUSTERING_FACTOR INDEX_NAME
----------------- ------------------------------
899782 IND_T1
3983 IND_T2
/* 可以看到ind_t2所以的clustering_factor的确下降了,换得的是ind_t1对应的增长 */

针对由该clustering_factor引起的低效率批量delete/update操作,我们可以通过以下措施减少”db file sequential read”等待的出现:

  1. 通过keep cache保留池等技术将clustering_factor过高的索引缓存住,以避免频繁地单块物理读,从而提高性能
  2. 如果你正在执行一个大作业,那么可以暂时将clustering_factor过高的索引drop掉,在完成操作后再重建该索引,这样起到加速作业的目的

rebuild重建索引在以上案例的情景中获益并不大。

另外”db file sequential read”单块读等待是一种最为常见的物理IO等待事件,这里的sequential指的是将数据块读入到相连的内存空间中(contiguous memory space),而不是指所读取的数据块是连续的。该wait event可能在以下情景中发生:

  1. 最为常见的是执行计划中包含了INDEX FULL SCAN/UNIQUE SCAN,此时出现”db file sequential read”等待是预料之中的,一般不需要我们去特别关注
  2. 当执行计划包含了INDEX RANGE SCAN-(“TABLE ACCESS BY INDEX ROWID”/”DELETE”/”UPDATE”),服务进程将按照”访问索引->找到rowid->访问rowid指定的表数据块并执行必要的操作”顺序访问index和table,每次物理读取都会进入”db file sequential read”等待,且每次读取的都是一个数据块;这种情况下clustering_factor将发挥其作用,需要我们特别去关注,本例中提及的解决方法对这种情景也有效
  3. Extent boundary,假设一个Extent区间中有33个数据块,而一次”db file scattered read”多块读所读取的块数为8,那么在读取这个区间时经过4次多块读取后,还剩下一个数据块,但是请记住多块读scattered read是不能跨越一个区间的(span an extent),此时就会单块读取并出现”db file scattered read”。这是一种正常现象,一般不需要额外关注
  4. 假设某个区间内有8个数据块,它们可以是块a,b,c,d,e,f,g,h,恰好当前系统中除了d块外的其他数据块都已经被缓存在buffer cache中了,而这时候恰好要访问这个区间中的数据,那么此时就会单块读取d这个数据块,并出现”db file sequential read”等待。注意这种情况不仅于表,也可能发生在索引上。这是一种正常现象,一般不需要额外关注
  5. chained/migrated rows即链式或迁移行,这里我们不介绍链式行的形成原因,chained/migrated rows会造成服务进程在fetch一行记录时需要额外地单块读取,从而出现”db file sequential read”。这种现象需要我们特别去关注,因为大量的链式/迁移行将导致如FULL SCAN等操作极度恶化(以往的经验是一张本来全表扫描只需要30分钟的表,在出现大量链式行后,全表扫描需要数个小时),同时也会对其他操作造成不那么明显的性能影响。可以通过监控v$sysstat视图中的”table fetch continued row”操作统计来了解系统中链式/迁移行访问的情况,还可以通过DBA_TBALES视图中的CHAIN_CNT来了解表上的链式/迁移行情况,当然这要求定期收集表上的统计信息;如果没有定期收集的习惯,那么可以配合@?/rdbms/admin/utlchain脚本和analyze table list chained rows 命令来获取必要的链式行信息
  6. 创建Index entry,显然当对表上执行INSERT操作插入数据时,虽然在执行计划中你看不到过多的细节,但实际上我们需要利用索引来快速验证表上的某些约束是否合理,还需要在索引的叶子块中插入相关的记录,此时也可能出现”db file sequential read”等待事件,当然这还和具体的插入的方式有关系。这是一种正常现象,一般不需要额外关注
  7. 针对表上的UPDATE/DELETE,不同于之前提到的”INDEX RANGE SCAN-UPDATE/DELETE”,如果我们使用rowid去更新或删除数据时,服务进程会先访问rowid指向的表块(注意是先访问table block)上的行数据,之后会根据该行上的具体数据去访问索引叶子块(注意Oracle并不知道这些leaf block在哪里,所以这里同样要如range-scan/unique-scan那样去访问index branch block),这些访问都将会是单块读取,并会出现’db file sequential read’,完成必要的读取后才会执行更新或删除的实际EXEC操作,如下例:
以下trace中,obj#=1307547为sample表,而obj#=1307549为sample表上的唯一一个索引 
PARSING IN CURSOR #10 len=58 dep=0 uid=64 oct=6 lid=64 tim=1275805024007795 hv=505118268 ad='d387e470'
update sample set t2=t2+1 where rowid='AAE/OzAAEAAANUEAAQ'
END OF STMT
PARSE #10:c=1999,e=3016,p=1,cr=1,cu=0,mis=1,r=0,dep=0,og=1,tim=1275805024007787
WAIT #10: nam='db file sequential read' ela= 314 file#=4 block#=54532 blocks=1 obj#=1307547 tim=1275805024008308
WAIT #10: nam='db file sequential read' ela= 206 file#=6 block#=20 blocks=1 obj#=1307549 tim=1275805024009235
WAIT #10: nam='db file sequential read' ela= 206 file#=6 block#=742 blocks=1 obj#=1307549 tim=1275805024009496
WAIT #10: nam='db file sequential read' ela= 207 file#=6 block#=24 blocks=1 obj#=1307549 tim=1275805024009750
EXEC #10:c=2000,e=2297,p=6,cr=2,cu=8,mis=0,r=1,dep=0,og=1,tim=1275805024010210   --实际的UPDATE发生在这里

当大量执行这类UPDATE/DELETE操作时将需要频繁地交叉访问表和索引,如果恰好表上的某个索引有较高的clustering_factor的话,那么就会形成本例中的这种性能问题了。实际上当表上有较多索引时,使用rowid来批量update/delete数据这种方式是不被推荐的,仅当表上没有索引时才可能十分高效。如果你坚持要这样做,那么可以参照上面提到的建议。

 

8.BUG!BUG!已知在9i RAC及10g中使用ASM的情况下,存在引发在适用情况下不使用”scattered read”多块读而去使用”sequential read”的BUG。如果你的问题和上述情景都不匹配,但又有大量的”db file sequential read”等待事件,那么你有可能遇到bug了。在这里列出部分已知bug:

Bug# Version Affected
Bug 7243560 – High “db file sequential read” IO times when using ASM 10.2.0.4/11.1.0.7
Bug 7243560: RAPID INCREASE IN DB FILE SEQUENTIAL READ AFTER MOVING TO ASM 10.2.0.3
Bug 9711810: EXCESSIVE DB FILE SEQUENTIAL READS WITH NON COMPLIANT BUFFER CACHE ON RAC 9.2.0.8
Bug 9276739: INSERT STATEMENT HAS SLOW PERFORMANCE WITH DB FILE SEQUENTIAL READ 10.2.0.4
Bug 8625100: EXCESSIVE DB FILE SEQUENTIAL READ ON UNDO 10.2.0.4
Bug 8669544: HIGH DB FILE SEQUENTIAL READ AND GC CR DISK READ WAIT EVENTS DURING FULL SCAN 10.2.0.4
Bug 7427133: AN INSERT CAUSES LOTS OF ‘DB FILE SEQUENTIAL READ’ WAITS FOR THE INDEX BLOCKS 9.2.0.8
Bug 8493139: INCREASE IN DB FILE SEQUENTIAL READ WAITEVENT AFTER MIGRATING TO 10 RAC/ASM 10.2.0.4
Bug 5882268: PERFORMANCE ISSUE WITH ‘DB FILE SEQUENTIAL READ’ 10.2.0.2
Bug 7415702: LOTS OF ‘DB FILE SEQUENTIAL READ’ ON UNDO 10.2.0.3
Bug 5607724: 10202 DB FILE SEQUENTIAL READ THRICE AFTER UPGRADE FROM 9I 10.2.0.2

Extract SQL Plan from AWR

之前有用户在AWR中定位到问题SQL语句后想要了解该SQL statement的具体执行计划,于是就用AWR报告中得到的SQL ID去V$SQL等几个动态性能视图中查询,但发现V$SQL或V$SQL_PLAN视图都已经找不到对应SQL ID的记录,一般来说这些语句已经从shared pool共享池中被替换出去了。

这个时候我们可以尝试使用DBMS_XPLAN.DISPLAY_AWR存储过程来将Oracle Automatic Workload Repository自动负载仓库中记录的SQL语句相关信息抽取出来,如:

SQL> set linesize 200 pagesize 2000;
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
askmaclean.com
SQL> select /* extract_me */ count(*) from maclean;
COUNT(*)
----------
9564
SQL> select sql_id from v$sql where sql_text like '%extract_me%' and sql_text not like '%like%';
SQL_ID
-------------
8vff23q8qp9fj
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
QL> select * from table(dbms_xplan.display_cursor('8vff23q8qp9fj',0));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8vff23q8qp9fj, child number 0
-------------------------------------
select /* extract_me */ count(*) from maclean
Plan hash value: 1679547536
----------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |    34 (100)|          |
|   1 |  SORT AGGREGATE    |         |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MACLEAN | 10438 |    34   (0)| 00:00:01 |
----------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
18 rows selected.
/* 冲刷共享池后v$SQL/V$SQL_PLAN等动态视图内的记录会消失,
需要注意的是如果在AWR快照自动生成之前冲刷了共享池,那么可能丢失SQL的执行统计信息
*/
SQL> alter system flush shared_pool;
System altered.
SQL> select * from table(dbms_xplan.display_cursor('8vff23q8qp9fj',0));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
SQL_ID: 8vff23q8qp9fj, child number: 0 cannot be found
/* 此时就可以利用dbms_xplan.display_awr存储过程来抓取该SQL_ID对应的执行计划 */
SQL>  select * from table(dbms_xplan.display_awr('8vff23q8qp9fj'));
PLAN_TABLE_OUTPUT
--------------------------------------------------
SQL_ID 8vff23q8qp9fj
--------------------
select /* extract_me */ count(*) from maclean
Plan hash value: 1679547536
----------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |    34 (100)|          |
|   1 |  SORT AGGREGATE    |         |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MACLEAN | 10438 |    34   (0)| 00:00:01 |
----------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
18 rows selected.
/* 这里可以代入'all'选项获取更细致的计划信息 */
SQL> select * from table(dbms_xplan.display_awr('8vff23q8qp9fj',null,null,'all'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID 8vff23q8qp9fj
--------------------
select /* extract_me */ count(*) from maclean
Plan hash value: 1679547536
----------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |    34 (100)|          |
|   1 |  SORT AGGREGATE    |         |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MACLEAN | 10438 |    34   (0)| 00:00:01 |
----------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / MACLEAN@SEL$1
Note
-----
- dynamic sampling used for this statement
24 rows selected.
/* 也可以从 dba_hist_sql_plan/dba_hist_sqltext等awr历史视图中直接观察该SQL ID对应的语句 */
SQL> select OPERATION,OPTIONS,OBJECT_NAME from dba_hist_sql_plan where sql_id='8vff23q8qp9fj';
OPERATION                      OPTIONS                        OBJECT_NAME
------------------------------ ------------------------------ -------------------------------
SELECT STATEMENT
SORT                           AGGREGATE
TABLE ACCESS                   FULL                           MACLEAN

Utilize Sql Tuning Advisor from Script

Sql Tuning Advisor是10g以后出现的一个十分有用的调优工具,大多数情况下我们可以通过dbconsole或者Grid Control的web界面调用SQL Advisor;但如果系统中没有配置dbconsole或者Grid Control的话,我们则需要通过手动调用DBMS_SQLTUNE PL/SQL程序包来使用该特性。这里我列出一个针对单个SQL语句Autotune的脚本,具体脚本:

begin
DBMS_SQLTUNE.drop_tuning_task('&task_name');
end;
/
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
my_sqlid varchar2(30);
BEGIN
my_sqlid := '&sqlid';
my_task_name := dbms_sqltune.create_tuning_task(sql_id => my_sqlid,
scope => 'COMPREHENSIVE',
time_limit => 300,
task_name => '&task_name',
description => 'comment'                               
);
END;
/
BEGIN
dbms_sqltune.execute_tuning_task(task_name => '&task_name');
END;
/
SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = '&task_name';
SET LONG 10000
SET LONGCHUNKSIZE 10000
SET LINESIZE 100
set pages 60
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&task_name') FROM DUAL;

具体使用示例:

SQL> begin
DBMS_SQLTUNE.drop_tuning_task('&task_name');
end;
/
Enter value for task_name: newtask
old   3:   DBMS_SQLTUNE.drop_tuning_task('&task_name');
new   3:   DBMS_SQLTUNE.drop_tuning_task('newtask');
begin
*
ERROR at line 1:
ORA-13605: The specified task or object newtask does not exist for the current
user.
ORA-06512: at "SYS.PRVT_ADVISOR", line 2307
ORA-06512: at "SYS.DBMS_ADVISOR", line 172
ORA-06512: at "SYS.DBMS_SQLTUNE", line 751
ORA-06512: at line 3
SQL> DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
my_sqlid varchar2(30);
BEGIN
my_sqlid := '&sqlid';
my_task_name := dbms_sqltune.create_tuning_task(sql_id => my_sqlid,
scope => 'COMPREHENSIVE',
time_limit => 300,
task_name => '&task_name',
description => 'comment'                               
);
END;
/ 
Enter value for sqlid: 17usubxchdf2w
old  11:   my_sqlid := '&sqlid';
new  11:   my_sqlid := '17usubxchdf2w';
Enter value for task_name: new_task 
old  19:						   task_name => '&task_name',
new  19:						   task_name => 'new_task',
SQL> BEGIN
dbms_sqltune.execute_tuning_task(task_name => '&task_name');
END;
/  
Enter value for task_name: new_task
old   3:   dbms_sqltune.execute_tuning_task(task_name => '&task_name');
new   3:   dbms_sqltune.execute_tuning_task(task_name => 'new_task');
PL/SQL procedure successfully completed.
SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = '&task_name';
Enter value for task_name: new_task
old   1: SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = '&task_name'
new   1: SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'new_task'
STATUS
-----------
COMPLETED
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&task_name') FROM DUAL;
Enter value for task_name: new_task
old   1: SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&task_name') FROM DUAL
new   1: SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('new_task') FROM DUAL
DBMS_SQLTUNE.REPORT_TUNING_TASK('NEW_TASK')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : new_task
Tuning Task Owner  : SYS
Workload Type	   : Single SQL Statement
Scope		   : COMPREHENSIVE
Time Limit(seconds): 300
Completion Status  : COMPLETED
Started at	   : 03/25/2011 00:14:41
Completed at	   : 03/25/2011 00:14:45
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID	   : 17usubxchdf2w
SQL Text   : select count(t1) from hashtab
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "SYS"."HASHTAB" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
'HASHTAB', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 485915006
------------------------------------------------------------------------------
| Id  | Operation	   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	     |	   1 |	  13 |	   2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |	     |	   1 |	  13 |		  |	     |
|   2 |   TABLE ACCESS FULL| HASHTAB |	 102 |	1326 |	   2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Gather more plan statistics by gather_plan_statistics hint

在10g以后我们可以通过利用gather_plan_statistics提示来了解更多的SQL执行统计信息,具体使用方法如下:

SQL> set linesize 150
SQL> set pagesize 2000
SQL> set autotrace traceonly exp
SQL> select avg(SALARY),DEPARTMENT_NAME from 
employees e,departments d where e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME;
Execution Plan
----------------------------------------------------------
Plan hash value: 3294250112
---------------------------------------------------------------------------------------------
| Id  | Operation		      | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	      | 	    |	 27 |	621 |	  5  (20)| 00:00:01 |
|   1 |  HASH GROUP BY		      | 	    |	 27 |	621 |	  5  (20)| 00:00:01 |
|   2 |   NESTED LOOPS		      | 	    |	106 |  2438 |	  4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL	      | EMPLOYEES   |	107 |	749 |	  3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |	  1 |	 16 |	  1   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN	      | DEPT_ID_PK  |	  1 |	    |	  0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
SQL> show parameter cursor_sharing
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing			     string	 EXACT
SQL>  show parameter statistics_level
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
statistics_level		     string	 TYPICAL
SQL> set autotrace off;
SQL> select /*+ gather_plan_statistics */   avg(SALARY),DEPARTMENT_NAME from 
employees e,departments d where e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME;
SQL> select * from TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	bctzu9xuxay18, child number 0
-------------------------------------
select /*+ gather_plan_statistics */  avg(SALARY),DEPARTMENT_NAME from employees e,departments d
where e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME
Plan hash value: 3294250112
-------------------------------------------------------------------------------------------------------
| Id  | Operation		      | Name	    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   1 |  HASH GROUP BY		      | 	    |	   1 |	   27 |     11 |00:00:00.01 |	  219 |
|   2 |   NESTED LOOPS		      | 	    |	   1 |	  106 |    106 |00:00:00.01 |	  219 |
|   3 |    TABLE ACCESS FULL	      | EMPLOYEES   |	   1 |	  107 |    107 |00:00:00.01 |	    7 |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |	 107 |	    1 |    106 |00:00:00.01 |	  212 |
|*  5 |     INDEX UNIQUE SCAN	      | DEPT_ID_PK  |	 107 |	    1 |    106 |00:00:00.01 |	  106 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
/* 可以从starts列看到某种操作执行了多少次,例如这里的INDEX UNIQUE SCAN为107次 */
/*也可以通过SQL_ID来定位计划信息 */
SQL> select t.* 
from v$sql s 
, table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALL IOSTATS LAST')) t where s.sql_id = '&SQL_ID' ;
Enter value for sql_id: bctzu9xuxay18 
old   3:    , table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALL IOSTATS LAST')) t where s.sql_id = '&SQL_ID'
new   3:    , table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALL IOSTATS LAST')) t where s.sql_id = 'bctzu9xuxay18'
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	bctzu9xuxay18, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ avg(SALARY),DEPARTMENT_NAME from 
employees e,departments d where
e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME
Plan hash value: 3294250112
---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		      | Name	    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
|   1 |  HASH GROUP BY		      | 	    |	   1 |	   27 |   621 |     5  (20)| 00:00:01 |     11 |00:00:00.01 |	  219 |
|   2 |   NESTED LOOPS		      | 	    |	   1 |	  106 |  2438 |     4	(0)| 00:00:01 |    106 |00:00:00.01 |	  219 |
|   3 |    TABLE ACCESS FULL	      | EMPLOYEES   |	   1 |	  107 |   749 |     3	(0)| 00:00:01 |    107 |00:00:00.01 |	    7 |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |	 107 |	    1 |    16 |     1	(0)| 00:00:01 |    106 |00:00:00.01 |	  212 |
|*  5 |     INDEX UNIQUE SCAN	      | DEPT_ID_PK  |	 107 |	    1 |       |     0	(0)|	      |    106 |00:00:00.01 |	  106 |
---------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / E@SEL$1
4 - SEL$1 / D@SEL$1
5 - SEL$1 / D@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "DEPARTMENT_NAME"[VARCHAR2,30], AVG("SALARY")[22]
2 - "SALARY"[NUMBER,22], "DEPARTMENT_NAME"[VARCHAR2,30]
3 - "SALARY"[NUMBER,22], "E"."DEPARTMENT_ID"[NUMBER,22]
4 - "DEPARTMENT_NAME"[VARCHAR2,30]
5 - "D".ROWID[ROWID,10]
SQL> alter session set statistics_level=ALL;
Session altered.
/* 在session级别设置statistics_level为ALL,可以为我们提供更为详尽的执行统计信息 */

Does GATHER_STATS_JOB gather all objects’ stats every time?

周五在一家客户的调优会议中讨论了一个由于统计信息陈旧导致SQL执行计划偏差的问题,这是一个10g的库并且禁用了自动收集统计信息的定时作业GATHER_STATS_JOB;当问及应用程序开发商为何要禁用自动统计信息收集时,开发商的一位工程师说因为该库的数据量较大,考虑到该JOB每天都会将所有大表统计一遍可能要花费大量时间所以予以停用。

这里就存在一个问题,GATHER_STATS_JOB自动统计作业是每次打开都会将数据库中所有的对象的统计信息都收集一遍吗?细心的朋友一定会发觉实际上该JOB的运行时间是时长时短的,同时绝对不是如这位开发工程师所说的会每天都重复统计所有表。

10g的官方文档中对该GATHER_STATS_JOB描述为”The Scheduler runs this job when the maintenance window is opened. By default, the maintenance window opens every night from 10 P.M. to 6 A.M. and all day on weekends.The stop_on_window_close attribute controls whether the GATHER_STATS_JOB continues when the maintenance window closes. The default setting for the stop_on_window_close attribute is TRUE, causing Scheduler to terminate GATHER_STATS_JOB when the maintenance window closes. The remaining objects are then processed in the next maintenance window.The GATHER_DATABASE_STATS_JOB_PROC procedure collects statistics on database objects when the object has no previously gathered statistics or the existing statistics are stale because the underlying object has been modified significantly (more than 10% of the rows).”

以上这段描述还是比较清晰的,MAINTENANCE_WINDOW_GROUP维护窗口组中的工作日窗口(WEEKNIGHT_WINDOW,周一到周五)会在每个工作日的22:00启动并于第二天的6:00结束,在周末该维护窗口组中的周末窗口(WEEKEND_WINDOW)从周六Sat的0点开始并持续48小时(你不难发现这2个窗口在周六0点到6点之间存在overlay,实际的情况是WEEKEND_WINDOW窗口是从周六的0点整到周一的0点,具体可以观察dba_scheduler_windows视图的NEXT_START_DATE列,这里不再赘述)。在数据库一直打开的情况下,GATHER_STATS_JOB会伴随维护窗口一起被启动,默认情况下如果到维护窗口关闭该JOB仍未结束则将被终止(这取决于该JOB的属性stop_on_window_close),剩下的有待收集信息的对象将在下一个维护窗口中得到处理;如果数据库一直处于关闭的状态,并在某维护窗口的时间范围内该DB被打开,那么相应的维护窗口会被立即激活(ACTIVE),同时GATHER_STATS_JOB自动作业也会被启动,但该自动作业仅会在一个窗口中自动运行一次(因REASON="ORA-01014: ORACLE shutdown in progress"等原因失败的不算做一次)。

以上介绍了GATHER_STATS_JOB的运行周期,和我们要介绍的问题没有直接的联系。我们这里要谈的是,GATHER_STATS_JOB自动统计信息收集作业每次启动时是由针对性地收集统计信息的而非对数据库中所有schema下的对象都分析一遍;以上引用的文字中介绍了该JOB挑选分析对象的条件,即:

  1. 对象之前从未收集过统计信息,或由于某些原因没有统计信息
  2. 对象的统计信息相对陈旧(stale),是否陈旧的评判标准是由上次收集信息到此次收集期间被修改过的行数超过10%

条件1显得理所当然,剔除一些复杂的情况,一个对象没有统计信息的原因往往是这个对象刚刚被创建或者加载到数据库中,并且用户没有手动地去收集过统计信息,那么Oracle有充分的理由去分析这些对象。而后者则体现了查询优化器对统计信息陈旧度的容忍在超过10%的情况下导致执行计划偏差的可能性将大幅上升,为了遏制这种势头有必要再次统计这些对象。

让我们来看看GATHER_STATS_JOB针对”陈旧”(stale)统计信息的实际表现:

SQL> select * from global_name;
GLOBAL_NAME
------------------------------------
www.askmaclean.com
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE	10.2.0.1.0	Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> conn maclean/maclean
Connected.
SQL> create table need_analyze tablespace users as select rownum t1 from dba_objects where rownum<10001;
Table created. 
SQL> select count(*)  from need_analyze;
COUNT(*)
----------
10000
SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';
NUM_ROWS     BLOCKS
---------- ----------
/* 以上创建了一张具有10000行记录的测试用表,因为是新建的所以没有num_rows和blocks等等统计信息 */
/* 手动调用GATHER_STATS_JOB自动作业 */
SQL> begin
dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true);
end;
/
SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';
NUM_ROWS     BLOCKS
---------- ----------
10000	   20
/* 删除999条记录,即不到10%的数据 */
SQL> delete need_analyze where rownum<1000;
999 rows deleted. 
SQL> commit;
Commit complete.
/* 再次调用GATHER_STATS_JOB */
begin
dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true);
end;
/
/* 可以看到统计信息并未被更新 */
SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';
NUM_ROWS     BLOCKS
---------- ----------
10000	   20
SQL> delete need_analyze where rownum<2;
1 row deleted. 
SQL> commit;
Commit complete.
SQL> begin
dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true);
end;
/  2    3    4  
PL/SQL procedure successfully completed.
SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';
NUM_ROWS     BLOCKS
---------- ----------
10000	   20
SQL>  delete need_analyze where rownum<2;
1 row deleted. 
SQL> commit;
Commit complete.
SQL> begin
dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true);
end;
/  2    3    4  
PL/SQL procedure successfully completed.
SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';
NUM_ROWS     BLOCKS
---------- ----------
8999	   20
/* 可以看到修改的行数必须超过10%后才会被收集 */

有的朋友肯定要问Oracle是怎么知道某张表是否有过DML操作,而DML操作又涉及到了多少行数据呢?这都是通过表监控特性(a table monitoring facility)来实现的,当初始化参数STATISTICS_LEVEL设置为TYPICAL或ALL时默认启用这种特性。Oracle会默认监控表上的INSERT,UPDATE,DELETE以及表是否被TRUNCATE截断,并记录这些操作数量的近似值到数据字典。我们可以通过访问user_tab_modifications视图来了解这些信息:

SQL> delete need_analyze;
8999 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from user_tab_modifications where table_name='NEED_ANALYZE';
no rows selected
/* 从实际的DML操作完成到*_tab_modifications视图到更新可能存在几分钟的延迟 */
/* 通过dbms_stats包中的FLUSH_DATABASE_MONITORING_INFO存储过程可以
将这些监控数据刷新到字典中  */
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed
SQL> col table_name for a20
SQL> select table_name,inserts,updates,deletes,timestamp from user_tab_modifications where table_name='NEED_ANALYZE';
TABLE_NAME		INSERTS    UPDATES    DELETES TIMESTAMP
-------------------- ---------- ---------- ---------- ---------
NEED_ANALYZE		      0 	 0	 8999 26-MAR-11
/* 可以看到*_tab_modifications视图中记录了上次收集统计信息以来
NEED_ANALYZE表上删除过8999条记录,因为测试时仅用少量的串行DML,所以这里十分精确 */
SQL> set autotrace on;

/* 通过以上执行计划可以猜测,monitoring监控数据来源于MON_MODS_ALL$基表上 */
SQL> desc sys.MON_MODS_ALL$;
Name					   Null?    Type
----------------------------------------- -------- ----------------------------
OBJ#						    NUMBER
INSERTS					    NUMBER
UPDATES					    NUMBER
DELETES					    NUMBER
TIMESTAMP					    DATE
FLAGS						    NUMBER
DROP_SEGMENTS					    NUMBER
SQL> select * from mon_mods_all$ where obj#=(select object_id from dba_objects where object_name='NEED_ANALYZE');
OBJ#    INSERTS	 UPDATES    DELETES TIMESTAMP	   FLAGS DROP_SEGMENTS
---------- ---------- ---------- ---------- --------- ---------- -------------
52565	    0	       0       8999 26-MAR-11	       0	     0
/* 需要注意的该mon_mods_all$修改监控基表仅记录上次该对象统计信息以来的修改(modify)情况,
并不能做为某张表的实际修改历史记录来利用 */

虽然我们现在对GATHER_STATS_JOB在如何选择分析对象的条件上更清晰了,但是不少朋友可能还是会疑惑难道Oracle不对那些长久以来没有显著修改的表一直不予以收集信息吗?这似乎有悖于我们的常识,试看下例子:

/ * NEED_ANALYZE现在扮演一张静态表,它上次被分析是在2011年3月26日 */
SQL> select last_analyzed from dba_tables where table_name='NEED_ANALYZE';
LAST_ANAL
---------
26-MAR-11
SQL> select sysdate from dual;
SYSDATE
---------
26-MAR-11
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host
[maclean@rh8 ~]$ su - root
Password: 
/* 我们把时钟调快到2012年的12月30日,希望我们能安全度过2012! */
[root@rh8 ~]# date -s "2012-12-30 00:00:00"
Sun Dec 30 00:00:00 CST 2012
[root@rh8 ~]# date
Sun Dec 30 00:00:01 CST 2012
[maclean@rh8 ~]$ exit
exit
SQL> startup;
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size		    1218292 bytes
Variable Size		   75499788 bytes
Database Buffers	   83886080 bytes
Redo Buffers		    7168000 bytes
Database mounted.
Database opened.
SQL> select sysdate from dual;
SYSDATE
---------
30-DEC-12
/* 再次手动调用GATHER_STATS_JOB自动作业 */
SQL> set timing on;
SQL> begin
dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true);
end;
/
2    3    4
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.33
/* :-),运行结束不到1s */
SQL>  select last_analyzed from dba_tables where table_name='NEED_ANALYZE';
LAST_ANAL
---------
26-MAR-11

是的,默认情况下GATHER_STATS_JOB不会反复去分析那些静态表,无论过去”多久”。

好了,我们需要对GATHER_STATS_JOB和DBMS_STATS包下属的统计信息收集存储过程(gather_*_stats)有一个饱满的认识,他们远没有我们想象的那么2,实际上这个GATHER_STATS_JOB调用的PROGRAM存储过程是DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC,虽然这是一个内部存储过程(interal procedure);但实际上它和我们经常手动调用的DBMS_STATS.GATHER_DATABASE_STATS收集整库统计信息的存储过程在以GATHER AUTO选项运行时的行为方式上几乎一致,主要的区别是GATHER_DATABASE_STATS_JOB_PROC总是优先收集那些急需收集统计信息的对象,这保证了在维护窗口关闭之前那些最需要收集的统计信息总是能得到满足。而在手动调用GATHER_DATABASE_STATS等存储过程时因为不需要考虑窗口时间线的限制,而不论优先级。

to be continued ………….

Oracle中可以nologging执行的操作

redo重做日志是Oracle数据库恢复(recovery)的基础;但在很多情况下可以通过禁用重做日志的产生来加速SQL语句的完成,也就是我们所说的可nologging化的操作,这些操作大多是或串行的或并行的数据载入。

那么哪些操作是允许被nologging化的呢?首先需要注意的是普通的DML操作,即:常规insert,update,和delete(以及merge)总是不能被nologging执行的。但以下SQL语句则可以以nologging选项执行:

  • direct load (SQL*Loader)
  • direct load INSERT (using APPEND hint)
  • CREATE TABLE … AS SELECT
  • CREATE INDEX
  • ALTER TABLE … MOVE PARTITION
  • ALTER TABLE … SPLIT PARTITION
  • ALTER INDEX … SPLIT PARTITION
  • ALTER INDEX … REBUILD
  • ALTER INDEX … REBUILD PARTITION
  • INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line

以上列出的语句,其产生undo和redo重做日志几乎可以完全禁绝。因为都是数据载入语句(或者装载索引)其所产生的新的区间(new extent)在执行过程中被标记为无效的(invalid),同时仅有少量的由数据字典变更引起的重做日志会产生。

应用长短链接变更对于Oracle数据库性能的影响

Question:某客户的应用做过变更(短链变长链),现cpu利用率较之前有明显改善,参见附件中的awr报告。想咨询一下sql语句的执行时间,cpu Time等指标,是否会受到短链变长链影响,因为从awr报告看,性能有明显改善。

Load Profile
变更前:

Per Second Per Transaction
Redo size: 244,606.59 13,269.94
Logical reads: 5,964.59 323.58
Block changes: 1,278.41 69.35
Physical reads: 339.03 18.39
Physical writes: 35.30 1.92
User calls: 693.44 37.62
Parses: 241.46 13.10
Hard parses: 0.16 0.01
Sorts: 97.93 5.31
Logons: 16.05 0.87
Executes: 617.55 33.50
Transactions: 18.43

变更后:

Per Second Per Transaction
Redo size: 314,037.68 4,249.08
Logical reads: 7,939.19 107.42
Block changes: 1,629.35 22.05
Physical reads: 221.23 2.99
Physical writes: 41.85 0.57
User calls: 1,005.17 13.60
Parses: 76.15 1.03
Hard parses: 0.16 0.00
Sorts: 37.36 0.51
Logons: 0.36 0.00
Executes: 810.16 10.96
Transactions: 73.91

Top 5 Timed Events
变更前:

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time 2,430 68.6
db file sequential read 84,286 416 5 11.7 User I/O
log file sync 63,773 266 4 7.5 Commit
db file scattered read 74,972 235 3 6.6 User I/O
enq: TX – row lock contention 463 229 494 6.5 Application

变更后:

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time 1,661 74.0
log file sync 167,658 473 3 21.1 Commit
db file sequential read 91,101 411 5 18.3 User I/O
wait for scn ack 145,796 142 1 6.3 Other
log file parallel write 166,143 121 1 5.4 System I/O

Time Model Statistics

变更前:

Statistic Name Time (s) % of DB Time
sql execute elapsed time 2,603.73 73.47
DB CPU 2,430.37 68.58
connection management call elapsed time 511.90 14.45
parse time elapsed 163.60 4.62
PL/SQL execution elapsed time 84.88 2.40
hard parse elapsed time 27.08 0.76
sequence load elapsed time 17.88 0.50
hard parse (sharing criteria) elapsed time 0.01 0.00
repeated bind elapsed time 0.00 0.00
DB time 3,543.74
background elapsed time 513.68
background cpu time 351.72

变更后:

Statistic Name Time (s) % of DB Time
DB CPU 1,661.42 74.02
sql execute elapsed time 1,558.64 69.44
PL/SQL execution elapsed time 66.66 2.97
parse time elapsed 37.24 1.66
hard parse elapsed time 15.09 0.67
connection management call elapsed time 8.37 0.37
sequence load elapsed time 3.53 0.16
PL/SQL compilation elapsed time 0.49 0.02
hard parse (sharing criteria) elapsed time 0.08 0.00
failed parse elapsed time 0.08 0.00
repeated bind elapsed time 0.00 0.00
DB time 2,244.66
background elapsed time 669.28
background cpu time 382.82

性能分析:

从这2个awr报告对比来看修改为长连接后单位小时的CPU TIME与DB TIME均有所下降,CPU TIME从原来的2430s下降到1661s,降幅为769s。但分析2个报告中的每秒逻辑读可以发现修改为长连接后的逻辑读反而增加了。CPU TIME主要可以分为parse cpu,execute cpu和fetch cpu。短连接时一小时的parse time即解析时间为163s;另外因为短连接时每秒登录数达到16个,Oracle为建立连接(connection management call)耗时511s。

总结以下几点:
1.短连接情况下因为新建立的会话没有缓存游标信息,进而导致无法避免大量的软解析,解析消耗了163s的DB TIME。修改为长连接后解析数量明显减少,解析仅消耗37s的DB TIME。
2.短连接情况下每秒登录数达到16次,建立连接(connection)同样会消耗大量的CPU TIME,这里connection management call消耗了约500s的CPU TIME。修改为长连接后每秒Logons数为0.36,节约了大量无谓的CPU浪费。
3.改为长连接后Top SQL的平均每次逻辑读并未下降,部分Top SQL的执行次数还有所增加;可见通过减少不必要的解析和反复建立连接,系统的性能得到了释放
4.原短连接的AWR报告中显示该时段内出现行锁等待(row lock contention)共463次,总耗时为229s,该等待事件也是造成2个报告中DB TIME差异的一个因素。而长连接报告中则没有该等待出现,这可能是出于偶然,也可能是程序修改导致的。

Oracle中可被并行化执行的SQL操作

并不是所有的SQL operations都是可并行化的;不少人认为sort merge join这种排序合并操作是不能并行化的,这显示是一种错误的认识。有了这样一个列表你就可以更好地理解Oracle中的Parallel Execution了:

Parallel Query:
Table scan
Nested loop join
Sort merge join
NOT IN
GROUP BY
Hash join
SELECT DISTINCT
UNION and UNION ALL
Aggregation
PL/SQL functions called from SQL
ORDER BY
DDL:
CREATE TABLE AS SELECT
CREATE INDEX
Rebuild index
Move partition
Split partition
DML:
UPDATE   on partitioned table
DELETE   on partitioned table
MERGE    on partitioned table
INSERT … SELECT
Enable constraint (the table scan is parallelized)
Star transformation

Tom Kyte教你如何制造糟糕的Oracle数据库

这个文档来的有点迟,不过迟到总比不到要好的多。我们可爱的Tom大叔这次很有爱的教导我们如何制造大量性能、管理具糟糕的,无人愿意管理的Oracle数据库。应当说做到这一点是十分不容易的,你需要从不考虑绑定变量,不考虑遵循任何范式同时往一个表里塞上两三百个列(当然有人真的这样做,而且认为这样做没什么不妥),你必须不了解Oracle的任何特性,如果你了解你必然对这些特性嗤之以鼻:”我才不会到那些东西,我用自己的代码来完成”,是的你很满足于一次次地重新制造轮子给自己带来的幸福感,等等。想要制造一个彻头彻底、糟糕到无与伦比的Oracle数据库的你还需要加一把劲,这当然也需要文档的指导,Tom这篇就是为这个来的,读罢这篇Worst Practices你或许还不足以制造世界上最糟糕的Oracle数据库,这不要紧!因为中国有大量的开发糟糕Oracle应用(或者其他什么的)的人才,你们可以组成ACOWPG(ALL CHINA Oracle Worst Practices Group)进一步交流这方面的心得。

好吧,以上以上!如果你和我一样是DBA,工作中有一个这样”蓄意”制造麻烦的家伙就已经够呛了,如果有一堆那么就是doomsday了。
[Read more…]

Script:AWR Trending

Kapil Goyal在他的IOUG presentation中共享了几个很有用的AWR性能诊断脚本,在这里共享一下:

 

 

Select extract(day from snap_interval) * 24 * 60 +
extract(hour from snap_interval) * 60 +
extract(minute from snap_interval) "Snapshot Interval",
extract(day from retention) * 24 * 60 +
extract(hour from retention) * 60 + extract(minute from retention) "Retention Interval(Minutes) ",
extract(day from retention) "Retention(in Days) "
from dba_hist_wr_control;

 

 

System Event Trending

 

event_response.sql
alter session set nls_date_format='dd-mon-yy';
set lines 150 pages 100 echo off feedback off
col date_time heading 'Date time|mm/dd/yy_hh_mi_hh_mi' for a30
col event_name for a26
col waits for 99,999,999,999 heading 'Waits'
col time for 99,999 heading 'Total Wait|Time(sec)'
col avg_wait_ms for 99,999 heading 'Avg Wait|(ms)'
prompt "Enter the date in DD-Mon-YY Format:"
WITH system_event AS
(select sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
se.event_name event_name,
se.total_waits e_total_waits,
lag(se.total_waits, 1) over(order by se.snap_id) b_total_waits,
se.total_timeouts e_total_timeouts,
lag(se.total_timeouts, 1) over(order by se.snap_id) b_total_timeouts,
se.time_waited_micro e_time_waited_micro,
lag(se.time_waited_micro, 1) over(order by se.snap_id) b_time_waited_micro
from dba_hist_system_event se, dba_hist_snapshot sn
where trunc(sn.begin_interval_time) = '&Date'
and se.snap_id = sn.snap_id
and se.dbid = sn.dbid
and se.instance_number = sn.instance_number
and se.dbid = (select dbid from v$database)
and se.instance_number = (select instance_number from v$instance)
and se.event_name = '&event_name') select to_char
(se1.BEGIN_INTERVAL_TIME, 'mm/dd/yy_hh24_mi') || to_char
(se1.END_INTERVAL_TIME, '_hh24_mi') date_time,
se1.event_name,
se1.e_total_waits - nvl(se1.b_total_waits,
0) waits,
(se1.e_time_waited_micro - nvl(se1.b_time_waited_micro,
0)) / 1000000 time,
((se1.e_time_waited_micro - nvl(se1.b_time_waited_micro,
0)) / 1000) / (se1.e_total_waits - nvl(se1.b_total_waits,
0)) avg_wait_ms from system_event se1 where(se1.e_total_waits - nvl(se1.b_total_waits,
0)) > 0 and nvl(se1.b_total_waits,
0) > 0
/

 

 

Load Profile Trending

 

alter session set nls_date_format='dd-mon-yy';
set lines 160 pages 1000 echo off feedback off
col stat_name for a25
col date_time for a40
col BEGIN_INTERVAL_TIME for a20
col END_INTERVAL_TIME for a20
prompt "Enter the date in DD-Mon-YY Format and Stats you want to trend like 'redo size','physical reads','physical writes','session logical reads' etc."
WITH sysstat AS
(select sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
ss.stat_name stat_name,
ss.value e_value,
lag(ss.value, 1) over(order by ss.snap_id) b_value
from dba_hist_sysstat ss, dba_hist_snapshot sn
where trunc(sn.begin_interval_time) = '&Date'
and ss.snap_id = sn.snap_id
and ss.dbid = sn.dbid
and ss.instance_number = sn.instance_number
and ss.dbid = (select dbid from v$database)
and ss.instance_number = (select instance_number from v$instance)
and ss.stat_name = '&stat_name')
select to_char(BEGIN_INTERVAL_TIME, 'mm/dd/yy_hh24_mi') || to_char(END_INTERVAL_TIME, '_hh24_mi') date_time,
stat_name,
round((e_value - nvl(b_value,0)) / (extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60
+ extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60
+ extract(minute from(end_interval_time - begin_interval_time)) * 60 + extract(second from(end_interval_time - begin_interval_time))),0) per_sec
from sysstat where(e_value - nvl(b_value,0)) > 0 and nvl(b_value,0) > 0
/

 

 

Time Model Statistics Trend

 

 

alter session set nls_date_format='dd-mon-yy';
set lines 160 pages 1000
col date_time heading 'Date time' for a40
col stat_name heading 'Statistics Name' for a25
col time heading 'Time (s)' for 99,999,999,999
prompt "Enter the date in DD-Mon-YY Format and Stats you want to trend like 'DBtime', 'DB CPU', 'sql execute elapsed time', 'PL/SQL execution elapsed time','parse time elapsed', 'background elapsed time'"
WITH systimemodel AS
(select sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
st.stat_name stat_name,
st.value e_value,
lag(st.value, 1) over(order by st.snap_id) b_value
from DBA_HIST_SYS_TIME_MODEL st, dba_hist_snapshot sn
where trunc(sn.begin_interval_time) = '&Date'
and st.snap_id = sn.snap_id
and st.dbid = sn.dbid
and st.instance_number = sn.instance_number
and st.dbid = (select dbid from v$database)
and st.instance_number = (select instance_number from v$instance)
and st.stat_name = '&stat_name')
select to_char (BEGIN_INTERVAL_TIME, 'mm/dd/yy_hh24_mi') || to_char (END_INTERVAL_TIME, '_hh24_mi') date_time,
stat_name, round((e_value - nvl(b_value, 0)) / 1000000) time
from systimemodel
where(e_value - nvl(b_value, 0)) > 0 and nvl(b_value, 0) > 0
/

 

 

Want to Know if Execution Plan Changed Recently?

 

 

set lines 150 pages 150
col BEGIN_INTERVAL_TIME for a23
col PLAN_HASH_VALUE for 9999999999
col date_time for a30
col snap_id heading 'SnapId'
col executions_delta heading "No. of exec"
col sql_profile heading "SQL|Profile" for a7
col date_time heading 'Date time'
col avg_lio heading 'LIO/exec' for 99999999999.99
col avg_cputime heading 'CPUTIM/exec' for 9999999.99
col avg_etime heading 'ETIME/exec' for 9999999.99
col avg_pio heading 'PIO/exec' for 9999999.99
col avg_row heading 'ROWs/exec' for 9999999.99
SELECT distinct
s.snap_id ,
PLAN_HASH_VALUE,
to_char(s.BEGIN_INTERVAL_TIME,'mm/dd/yy_hh24mi')|| to_char(s.END_INTERVAL_TIME,'_hh24mi') Date_Time,
SQL.executions_delta,
SQL.buffer_gets_delta/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_lio,
--SQL.ccwait_delta,
(SQL.cpu_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_cputime ,
(SQL.elapsed_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_etime,
SQL.DISK_READS_DELTA/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_pio,
SQL.rows_processed_total/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_row
--,SQL.sql_profile
FROM
dba_hist_sqlstat SQL,
dba_hist_snapshot s
WHERE
SQL.instance_number =(select instance_number from v$instance)
and SQL.dbid =(select dbid from v$database)
and s.snap_id = SQL.snap_id
AND sql_id in
('&SQLID') order by s.snap_id
/

 

 

xp_awr.sql

 

 

select plan_table_output from table (dbms_xplan.display_awr('&sql_id',null,null,
'ADVANCED +PEEKED_BINDS'));

沪ICP备14014813号

沪公网安备 31010802001379号