Skip to content

Oracle full table scan 的跟踪监视

最近一个客户的数据库反应不定时的性能问题,经过跟踪发现是一系列的全表扫相关SQL,其中还碰到了比较诡异的全表扫都是顺序读的情况.和周亮以前也讨论过此类情况只是当时这方面的事情并没有太多的眉目,这次跟踪发现了是和undo一致性读有关,还存在大量的row chains.当然这里不讨论这个case,这个case可能会在新书中描述.这个文章不描述具体的原理,只是作为一个对数据库全表扫相关信息收集诊断建议.

在跟踪某一特定的事件时候,有时候没有监控系统或者其他相关捕获信息的手段,则诊断起来比较耗时间,比如跟踪系统中全表扫的情况,默认情况下是只能获取当时时刻具体的情况,这里介绍通过AWR记录中来获取相关的信息,由于AWR从v$sql里面获取的sql是有条件限制的,因此该方法并不能补全数据库中所有的全表扫相关sql(其他情况也是),因为awr收集sql的情况是以statistics_level 的设置为依赖,typical(top 30)或者all(top 100)收集的top sql信息不同.

 

1.周期的全表扫信息

col c1 heading "Day|Hour" format a20 
col c2 heading "Full TABLE scan|Count" format 999,999
BREAK ON c1 skip 2
BREAK ON c2 skip 2
SELECT TO_CHAR(sn.begin_interval_time,'hh24') c1,
COUNT(1) c2
FROM dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn,
dba_segments o
WHERE p.object_owner <> 'SYS'
AND p.object_owner = o.owner
AND p.object_name = o.segment_name
AND o.blocks > 1000
AND p.operation LIKE '%TABLE ACCESS%'
AND p.options LIKE '%FULL%'
AND p.sql_id = s.sql_id
AND s.snap_id = sn.snap_id
GROUP BY TO_CHAR(sn.begin_interval_time,'hh24')
ORDER BY 1;

查询结果类似如下:

Large Full-table scans Per Snapshot Period
Begin
 Interval FTS
 time Count
 -------------------- --------
 04-10-18 11 4
 04-10-21 17 1
 04-10-21 23 2
 04-10-22 15 2
 04-10-22 16 2
 04-10-22 23 2
 04-10-24 00 2

2.每小时的全表扫信息

col c1 heading "Day|Hour" format a20 
col c2 heading "Full TABLE scan|Count" format 999,999
BREAK ON c1 skip 2
BREAK ON c2 skip 2
SELECT TO_CHAR(sn.begin_interval_time,'hh24') c1,
  COUNT(1) c2
FROM dba_hist_sql_plan p,
  dba_hist_sqlstat s,
  dba_hist_snapshot sn,
  dba_segments o
WHERE  p.object_owner <> 'SYS'
AND p.object_owner                                    = o.owner
AND p.object_name                                     = o.segment_name
AND o.blocks                                          > 1000
AND p.operation LIKE '%TABLE ACCESS%'
AND p.options LIKE '%FULL%'
AND p.sql_id  = s.sql_id
AND s.snap_id = sn.snap_id
GROUP BY TO_CHAR(sn.begin_interval_time,'hh24')
ORDER BY 1;

查询结果类似如下:

Large Table Full-table scans
Averages per Hour
 
Day                       FTS
Hour                    Count
-------------------- --------
00                          4
10                          2
11                          4
12                         23
13                         16
14                          6
15                         17
16                         10
17                         17
18                         21
19                          1
23                          6

3.基于星期的全表扫信息

col c1 heading "Week|Day" format a20 
col c2 heading "Full TABLE scan|Count" format 999,999
BREAK ON c1 skip 2
BREAK ON c2 skip 2
SELECT TO_CHAR(sn.begin_interval_time,'day') c1,
  COUNT(1) c2
FROM dba_hist_sql_plan p,
  dba_hist_sqlstat s,
  dba_hist_snapshot sn,
  dba_segments o
WHERE  p.object_owner <> 'SYS'
AND p.object_owner                                    = o.owner
AND p.object_name                                     = o.segment_name
AND o.blocks                                          > 1000
AND p.operation LIKE '%TABLE ACCESS%'
AND p.options LIKE '%FULL%'
AND p.sql_id  = s.sql_id
AND s.snap_id = sn.snap_id
GROUP BY TO_CHAR(sn.begin_interval_time,'day')
ORDER BY 1;

查询结果类似如下:

 
Week                      FTS
Day                     Count
-------------------- --------
sunday                      2
monday                     19
tuesday                    31
wednesday                  34
thursday                   27
friday                     15
Saturday                    2

以上脚本作者为Donald K. Burleson.
我对此脚本做了些变更,除了生成html格式外额外加入索引类的信息,变更的脚本未在blog上发布.

使用MDATA恢复drop的对象

olm@hc10 /home/oracle/MDATA$ sqlplus hc/hc

SQL*Plus: Release 10.2.0.5.0 – Production on Tue Jan 20 22:21:20 2015

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table mdata_test ( a number,b varchar2(10),c nvarchar2(30),d varchar2(20),e date);

Table created.

SQL> insert into mdata_test select rownum,lpad(‘x’,10),’NC测试’ || rownum, ‘ZHS测试’|| rownum,sysdate+dbms_random.value(0,100) from dba_objects where rownum< =20000;

20000 rows created.

SQL> commit;

Commit complete.

SQL> select object_id,object_name from dba_objects where object_name=’MDATA_TEST';

OBJECT_ID
———-
OBJECT_NAME
——————————————————————————–
51997
MDATA_TEST

SQL> drop table mdata_test_bak purge;

Table dropped.

SQL> create table mdata_test_bak as select * from mdata_test;

Table created.

SQL> drop table mdata_test purge;

Table dropped.

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
olm@hc10 /home/oracle/MDATA$ ./MDATA.sh

MDATA for oracle 9I,10G,11G, release 3.0.3
(@)copyright LUDATOU,HC all rights reserved.

Web:www.ludatou.com,www.hcdba.com
Email:feigigi@qq.com,564439763@qq.com
QQ group:66809572

loading default config…….
load config file ‘config.txt’ successful
loading default asm disk file ……
start loading default control file ……
load control file ‘control.txt’ successful
load BOOTSTRAP$ success.
load TAB$ success.
load COL$ success.
load OBJ$ success.
load USER$ success.
load PROPS$ success.
load TABPART$ success.
load TABSUBPART$ success.
load IND$ success.
MDATA>scan extent tablespace 4
scan extent start: Tue Jan 20 22:23:10 CST 2015
scanning extent…
scanning extent finished.
scan extent completed: Tue Jan 20 22:23:12 CST 2015
MDATA>unload object 51994 column number varchar2 nvarchar2 varchar2 date
Unloading Object,object ID: 51994, Cluster: 0
-1 rows unloaded
MDATA>unload object 51997 column number varchar2 nvarchar2 varchar2 date
Unloading Object,object ID: 51997, Cluster: 0
20000 rows unloaded
MDATA>exit
olm@hc10 /home/oracle/MDATA$ cd ddl
olm@hc10 /home/oracle/MDATA/ddl$ sqlplus hc/hc

SQL*Plus: Release 10.2.0.5.0 – Production on Tue Jan 20 22:23:56 2015

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> host ls
appclean.sql MDATA_0000051979.sql MDATA_0000051994.sql SYS.IND.sql SYS.TABPART.sql SYS.USER.sql
app.sql MDATA_0000051981.sql MDATA_0000051997.sql SYS.PROPS.sql SYS.TABSUBPART.sql

SQL> host ls -lrt
total 44
-rw-r–r– 1 oracle oinstall 281 Oct 6 16:57 appclean.sql
-rw-r–r– 1 oracle oinstall 1909 Oct 6 17:44 app.sql
-rw-r–r– 1 oracle oinstall 249 Jan 20 16:22 MDATA_0000051979.sql
-rw-r–r– 1 oracle oinstall 249 Jan 20 16:53 MDATA_0000051981.sql
-rw-r–r– 1 oracle oinstall 249 Jan 20 22:20 MDATA_0000051994.sql
-rw-r–r– 1 oracle oinstall 1328 Jan 20 22:23 SYS.USER.sql
-rw-r–r– 1 oracle oinstall 193 Jan 20 22:23 SYS.PROPS.sql
-rw-r–r– 1 oracle oinstall 1367 Jan 20 22:23 SYS.TABSUBPART.sql
-rw-r–r– 1 oracle oinstall 1277 Jan 20 22:23 SYS.TABPART.sql
-rw-r–r– 1 oracle oinstall 1761 Jan 20 22:23 SYS.IND.sql
-rw-r–r– 1 oracle oinstall 249 Jan 20 22:23 MDATA_0000051997.sql

SQL> @MDATA_0000051997.sql

Table created.

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
olm@hc10 /home/oracle/MDATA/ddl$ cd ../data
olm@hc10 /home/oracle/MDATA/data$ sqlldr “‘hc/hc’ control=MDATA_0000051997.ctl”

SQL*Loader: Release 10.2.0.5.0 – Production on Tue Jan 20 22:24:51 2015

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Commit point reached – logical record count 6502
Commit point reached – logical record count 13004
Commit point reached – logical record count 19506
Commit point reached – logical record count 20000
olm@hc10 /home/oracle/MDATA/data$ sqlplus / as sysdba;

SQL*Plus: Release 10.2.0.5.0 – Production on Tue Jan 20 22:25:08 2015

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn hc/hc
Connected.
SQL> select * from mdata_test_bak minus select * from MDATA_0000051997;

no rows selected