列使用跟踪

最新推荐文章于 2023-03-31 16:20:24 发布

cuian7084

最新推荐文章于 2023-03-31 16:20:24 发布

阅读量131

点赞数

文章标签：数据库

大家都知道，SYS.MON_MODS$跟踪了表的变更信息，该表结构如下：
SQL> desc MON_MODS$
Name          Type   Nullable Default Comments
------------- ------ -------- ------- --------
OBJ#          NUMBER Y
INSERTS       NUMBER Y
UPDATES       NUMBER Y
DELETES       NUMBER Y
TIMESTAMP     DATE   Y
FLAGS         NUMBER Y
DROP_SEGMENTS NUMBER Y

而内部表col_usage$记录了运行时的COLUMN使用信息（包括查询与更新），缺省情况打开，该表结构如下：
SQL> desc col_usage$
Name              Type   Nullable Default Comments
----------------- ------ -------- ------- --------
OBJ#              NUMBER Y
INTCOL#           NUMBER Y
EQUALITY_PREDS    NUMBER Y
EQUIJOIN_PREDS    NUMBER Y
NONEQUIJOIN_PREDS NUMBER Y
RANGE_PREDS       NUMBER Y
LIKE_PREDS        NUMBER Y
NULL_PREDS        NUMBER Y
TIMESTAMP         DATE   Y

隐藏参数_column_tracking_level控制了是否进行列跟踪（=0，禁用该特性）。
以下过程演示了缺省情况下，查询语句影响了是否收集列直方图。

1，系统版本
select * from v$version;
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

--隐藏参数及缺省值
select ksppinm,ksppdesc,ksppstvl from x$ksppi i,x$ksppcv v
where i.indx=v.indx
and i.ksppinm like '%_column_tracking_level%';
_column_tracking_level,column usage tracking,1

2，测试用表
drop table test;

create table test
as
select owner,object_id from dba_objects;

--对象id
SELECT object_name, object_id
FROM dba_objects
WHERE wner = USER
AND object_name = 'TEST';
TEST 107352

--是否有跟踪信息？
select * from col_usage$ where obj#=107352;
now rows selected.

--收集表统计信息
begin
dbms_stats.gather_table_stats(ownname => user,tabname => 'TEST');
end;
/

--直方图
SELECT column_name,endpoint_number,endpoint_value
FROM dba_tab_histograms
WHERE wner = USER
AND table_name = 'TEST';
OWNER 0 3.4371120062219E35
OBJECT_ID 0 7
OWNER 1 4.58306556462839E35
OBJECT_ID 1 107350

--列统计信息
SELECT column_name,num_distinct,histogram
FROM dba_tab_col_statistics
WHERE wner = USER
AND table_name = 'TEST';
OWNER 49 NONE
OBJECT_ID 67633 NONE

3，查询操作影响到该表统计信息的收集
--查询操作
select count(*) from test where wner='SYS';
23257

select count(*) from test where wner='MH';
194

select count(*) from test where wner='MM';
0

--是否有跟踪信息？
select * from col_usage$ where obj#=107352;
no row selected.
--&gt变动还没有更新到col_usage$

--收集统计信息
begin
dbms_stats.gather_table_stats(ownname => user,tabname => 'TEST');
end;
/

--是否有跟踪信息？
SQL> select * from col_usage$ where obj#=107352;

OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- -----------
107352 1 1 0 0 0 0 0 2012-5-3 16

--直方图
SELECT column_name,endpoint_number,endpoint_value
FROM dba_tab_histograms
WHERE wner = USER
AND table_name = 'TEST';
OWNER 5 3.4371120062219E35
OWNER 6 3.44172208564365E35
OWNER 313 3.44299235532519E35
OWNER 478 3.4429923553252E35
OWNER 628 3.4429923553252E35
OWNER 784 3.44299235532521E35
OWNER 869 3.44299235532521E35
OWNER 875 3.44299235532521E35
OWNER 962 3.44299235532521E35
OWNER 1011 3.44299235532521E35
OWNER 1095 3.44299235532521E35
OWNER 1160 3.49208844427158E35
OWNER 1192 3.49594609802084E35
OWNER 1194 3.54421425584994E35
OWNER 1213 3.54644535501845E35
OWNER 1237 3.5958683986314E35
OWNER 1265 3.59594389228078E35
OWNER 1308 3.59594391383625E35
OWNER 1332 3.60058907050643E35
OWNER 1334 3.75508531402007E35
OWNER 1340 3.80822522718164E35
OWNER 1396 3.85717100258496E35
OWNER 1456 4.01192665542225E35
OWNER 1478 4.01267191598645E35
OWNER 1480 4.01415370956876E35
OWNER 1491 4.11590938086903E35
OWNER 1546 4.11738089644256E35
OWNER 1548 4.11860021777884E35
OWNER 1670 4.11860022722044E35
OWNER 1672 4.1192213542137E35
OWNER 1705 4.16709180527619E35
OWNER 1717 4.16709180527619E35
OWNER 1718 4.16830708458966E35
OWNER 1742 4.16830722117732E35
OWNER 1745 4.16945494222267E35
OWNER 1746 4.17053190154266E35
OWNER 1747 4.17053190154716E35
OWNER 3412 4.17113006167251E35
OWNER 3414 4.32325845824965E35
OWNER 3431 4.32420972749854E35
OWNER 5369 4.32772349650604E35
OWNER 5471 4.32772373559899E35
OWNER 5505 4.32772375731125E35
OWNER 5511 4.37559024313807E35
OWNER 5525 4.37559024429058E35
OWNER 5557 4.53134565630609E35
OWNER 5579 4.53298175814007E35
OWNER 5634 4.58306556462839E35
OBJECT_ID 0 8
OBJECT_ID 1 107325

--列统计信息
SELECT column_name,num_distinct,histogram
FROM dba_tab_col_statistics
WHERE wner = USER
AND table_name = 'TEST';
OWNER 48 FREQUENCY
OBJECT_ID 67633 NONE

4，以下是系统运行过程中观察到的col_usage$的维护和使用操作：

SQL> select executions,buffer_gets,sql_text from v$sql where sql_text like '%col_usage$%' order by executions desc;

EXECUTIONS BUFFER_GETS SQL_TEXT
---------- ----------- --------------------------------------------------------------------------------
       512        1747 update sys.col_usage$ set   equality_preds    = equality_preds    + decode(bitan
       192          28 lock table sys.col_usage$ in exclusive mode nowait
         6         217 SELECT /*+ ordered use_nl(o c cu h) index(u i_user1) index(o i_obj2)
         1           5 insert into sys.col_usage$ values (   :objn, :coln,   decode(bitand(:flag,1),0,0

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-710062/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-710062/