Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 关于AWR报告的dc_histogram
发新帖
gdpr-dba

351

积分

0

好友

8

主题

[RAC Clusterware] 关于AWR报告的dc_histogram

1#
发表于 2013-5-19 19:52:41 | 查看: 6293| 回复: 4
问题源于这几天看的一份AWR报告，报告里dc_histogram_defs的争用很厉害，但是dc_histogram_data确没有发生争用，数据库的cursor_sharing=similar，一位兄弟说cursor_sharing=similar会造成dc_histogram_defs的争用，但是我不太明白为什么。

刘大可以解释一下AWR报告的dc_histogram_defs和dc_histogram_data各代表什么意思么？为什么cursor_sharing=similar会引起dc_histogram_defs争用而不会引起dc_histogram_data的争用？
2#
发表于 2013-5-19 20:21:10
FYI:

BUCKET 163:
  row cache parent object: address=0x8e0af628 cid=16(dc_histogram_defs)
  hash=ebb600a2 typ=11 transaction=(nil) flags=00000002
  own=0x8e0af6f0[0x8e0af6f0,0x8e0af6f0] wat=0x8e0af700[0x8e0af700,0x8e0af700] mode=S
  status=VALID/-/-/-/-/-/-/-/-
  request=N release=FALSE flags=8
  instance lock=QQ ebb600a2 6d0a8b4e
  set=0, complete=FALSE
  set=1, complete=FALSE
  data=
  0000398d 00000001 000001b1 7801000e 070f0770 00002702 00000000 402c0000
  a1fed14a 3f52eb4e c60e3da5 47277c26 526468aa 476d33a6 00000000 00000000
  00000000 402c0000 00000000 407b1000 00000011 6313be0b 76d41e07 852d74e3
  4869dbb4 00000000 00000000 00000000 00000000 9de90010 45239332 6153f178
  546e9ca0 0000d465 00000000 00000000 00000000 00100000 00000002 00000000
  00000001 00000000 00000000 ebb600a2 8e0af628 00000000 93834a40 00000000
  93834a40 00000000 00000000 00000000 d73115a0 00007fc6 00000000 00000000
  00000000 00000000 8e0af628 00000000 8e0af810 00000000 8e0af810 00000000
  8e0af838 00000000 00000008 ebb600a2 6d0a8b4e 00000000 00000101 00000000
  0000011d 00000000 96af1bd8 00000000 978a1c90 00000000 8e0b00e8 00000000
  8e0af1f8 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 8e0b36d8 00000000 00000001 00000095
  00030308 08910321 8e0af8a8 00000000 8e0af8a8 00000000 00000000 00000000
  d73115c0 00007fc6 8e0b3720 00000000 8e0b3720 00000000 00000000 00000000
  06c25c14 00000000 d73115c0 00007fc6 96af1bd8 00000000 8e0af298 00000000
  8e0b0188 00000000 97a84cc8 00000000 00000000 00000009 8e0af918 00000000
  8e0af918 00000000 00000000 00000000 00000000 00000000 8e0af938 00000000
  8e0af938 00000000 8e0af948 00000000 8e0af948 00000000 8e0af958 00000000
  8e0af958 00000000 00000000 00000000 d73115a8 00007fc6 00000000 00000000
  00000000 00000000 8e0af988 00000000 8e0af988 00000000 00000000 00000000
  00000000 00000000 02609720 00007fff 10600160 00000100 00000001 00000000
  00030308 08910321 8e0af8a8 00000000 8e0af8a8 00000000 00000000 00000000
  d73115c0 00007fc6 8e0b3720 00000000 8e0b3720 00000000 00000000 00000000
  06c25c14 00000000 d73115c0 00007fc6 96af1bd8 00000000 8e0af298 00000000
  8e0b0188 00000000 97a84cc8 00000000 00000000 00000009 8e0af918 00000000
  8e0af918 00000000 00000000 00000000 00000000 00000000 8e0af938 00000000
  8e0af938 00000000 8e0af948 00000000 8e0af948 00000000 8e0af958 00000000
  8e0af958 00000000 00000000 00000000 d73115a8 00007fc6 00000000 00000000
  00000000 00000000 8e0af988 00000000 8e0af988 00000000 00000000 00000000
  00000000 00000000 02609720 00007fff 10600160 00000100 00000001 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000001
  00000050 00000000 8e0af6f0 00000000
row cache subordinate object:
address=0x8e0af3f8 cid=16(dc_histogram_data) set=0 parent=0x8e0af628 typ=a
status=VALID/-/-/-/-
data=
00000000 0000001b 00000036 0000003c 00000058 000000ba 000000c2 000000de
000000f9 00000101 00000109 00000111 0000015e 000001ab 000001b1 00000000
00000000 00000000 00000000 00000000 00000000 00000000 c60e3da5 47277c26
5c1f5f69 4728918b 41856db0 473feb89 eaec04a0 47412e16 c77b6033 475a2ae2
09de084f 475ce9dd c43d8095 47610dc8 1d4ee46a 476356fb 32f9d9bb 4764cfcf
39166862 476553c4 bdd0ce20 476a36cf f6c47491 476abfee 7e250b7c 476d246e
526468aa 476d33a6 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000001 00000000













dc_histogram_defs ==> kqdhs   histogram cache entry  是parent row cache ，包括 parent cache header 和 key (obj#, col#)

  0000398d 00000001 000001b1 7801000e 070f0770 00002702 00000000 402c0000
  ^^^^^^^^  ^^^^^^^
  14733           1  即  obj#=14733 的第一个列


SQL> select name from obj$ where obj#=14733;

NAME
------------------------------
MGMT_COLLECTION_METRIC_TASKS



dc_histogram_data是 dc_histogram_defs的 subordinate object，存放一些实际的数据
  

回复 只看该作者 道具 举报

3#
发表于 2013-5-19 20:21:13
FYI:

BUCKET 163:
  row cache parent object: address=0x8e0af628 cid=16(dc_histogram_defs)
  hash=ebb600a2 typ=11 transaction=(nil) flags=00000002
  own=0x8e0af6f0[0x8e0af6f0,0x8e0af6f0] wat=0x8e0af700[0x8e0af700,0x8e0af700] mode=S
  status=VALID/-/-/-/-/-/-/-/-
  request=N release=FALSE flags=8
  instance lock=QQ ebb600a2 6d0a8b4e
  set=0, complete=FALSE
  set=1, complete=FALSE
  data=
  0000398d 00000001 000001b1 7801000e 070f0770 00002702 00000000 402c0000
  a1fed14a 3f52eb4e c60e3da5 47277c26 526468aa 476d33a6 00000000 00000000
  00000000 402c0000 00000000 407b1000 00000011 6313be0b 76d41e07 852d74e3
  4869dbb4 00000000 00000000 00000000 00000000 9de90010 45239332 6153f178
  546e9ca0 0000d465 00000000 00000000 00000000 00100000 00000002 00000000
  00000001 00000000 00000000 ebb600a2 8e0af628 00000000 93834a40 00000000
  93834a40 00000000 00000000 00000000 d73115a0 00007fc6 00000000 00000000
  00000000 00000000 8e0af628 00000000 8e0af810 00000000 8e0af810 00000000
  8e0af838 00000000 00000008 ebb600a2 6d0a8b4e 00000000 00000101 00000000
  0000011d 00000000 96af1bd8 00000000 978a1c90 00000000 8e0b00e8 00000000
  8e0af1f8 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 8e0b36d8 00000000 00000001 00000095
  00030308 08910321 8e0af8a8 00000000 8e0af8a8 00000000 00000000 00000000
  d73115c0 00007fc6 8e0b3720 00000000 8e0b3720 00000000 00000000 00000000
  06c25c14 00000000 d73115c0 00007fc6 96af1bd8 00000000 8e0af298 00000000
  8e0b0188 00000000 97a84cc8 00000000 00000000 00000009 8e0af918 00000000
  8e0af918 00000000 00000000 00000000 00000000 00000000 8e0af938 00000000
  8e0af938 00000000 8e0af948 00000000 8e0af948 00000000 8e0af958 00000000
  8e0af958 00000000 00000000 00000000 d73115a8 00007fc6 00000000 00000000
  00000000 00000000 8e0af988 00000000 8e0af988 00000000 00000000 00000000
  00000000 00000000 02609720 00007fff 10600160 00000100 00000001 00000000
  00030308 08910321 8e0af8a8 00000000 8e0af8a8 00000000 00000000 00000000
  d73115c0 00007fc6 8e0b3720 00000000 8e0b3720 00000000 00000000 00000000
  06c25c14 00000000 d73115c0 00007fc6 96af1bd8 00000000 8e0af298 00000000
  8e0b0188 00000000 97a84cc8 00000000 00000000 00000009 8e0af918 00000000
  8e0af918 00000000 00000000 00000000 00000000 00000000 8e0af938 00000000
  8e0af938 00000000 8e0af948 00000000 8e0af948 00000000 8e0af958 00000000
  8e0af958 00000000 00000000 00000000 d73115a8 00007fc6 00000000 00000000
  00000000 00000000 8e0af988 00000000 8e0af988 00000000 00000000 00000000
  00000000 00000000 02609720 00007fff 10600160 00000100 00000001 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000001
  00000050 00000000 8e0af6f0 00000000
row cache subordinate object:
address=0x8e0af3f8 cid=16(dc_histogram_data) set=0 parent=0x8e0af628 typ=a
status=VALID/-/-/-/-
data=
00000000 0000001b 00000036 0000003c 00000058 000000ba 000000c2 000000de
000000f9 00000101 00000109 00000111 0000015e 000001ab 000001b1 00000000
00000000 00000000 00000000 00000000 00000000 00000000 c60e3da5 47277c26
5c1f5f69 4728918b 41856db0 473feb89 eaec04a0 47412e16 c77b6033 475a2ae2
09de084f 475ce9dd c43d8095 47610dc8 1d4ee46a 476356fb 32f9d9bb 4764cfcf
39166862 476553c4 bdd0ce20 476a36cf f6c47491 476abfee 7e250b7c 476d246e
526468aa 476d33a6 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000001 00000000













dc_histogram_defs ==> kqdhs   histogram cache entry  是parent row cache ，包括 parent cache header 和 key (obj#, col#)

  0000398d 00000001 000001b1 7801000e 070f0770 00002702 00000000 402c0000
  ^^^^^^^^  ^^^^^^^
  14733           1  即  obj#=14733 的第一个列


SQL> select name from obj$ where obj#=14733;

NAME
------------------------------
MGMT_COLLECTION_METRIC_TASKS



dc_histogram_data是 dc_histogram_defs的 subordinate object，存放一些实际的数据
  

回复 只看该作者 道具 举报

4#
发表于 2013-5-19 20:35:30
SQL> create table test_dc_histogram tablespace users as select * from dba_objects;

Table created.

SQL> exec dbms_stats.gather_table_stats(user,'TEST_DC_HISTOGRAM', method_opt=>'FOR ALL COLUMNS SIZE 5');

PL/SQL procedure successfully completed.


SQL> select obj#,name from obj$ where name='TEST_DC_HISTOGRAM';

      OBJ# NAME
---------- ------------------------------
     18000 TEST_DC_HISTOGRAM
         
SQL> select * from test_dc_histogram where object_id=10;

         
         SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump row_cache 10;
Statement processed.

18000 =》4650


BUCKET 63289:
  row cache parent object: address=0x900e3838 cid=16(dc_histogram_defs)
  hash=1fddf738 typ=11 transaction=(nil) flags=00000002
  own=0x900e3900[0x900e3900,0x900e3900] wat=0x900e3910[0x900e3910,0x900e3910] mode=S
  status=VALID/-/-/-/-/-/-/-/-
  request=N release=FALSE flags=8
  instance lock=QQ 1fddf738 acf324a4
  set=0, complete=FALSE
  set=1, complete=FALSE
  data=
  00004650 00000009 00000005 78010006 09130571 00000317 00000000 407cf000
  97c59ee6 3f726e84 9b96981d 47489c9c 99969823 47491818 00000000 00000000
  00000000 407cf000 00000000 40d12d00 00000014 37393931 2d34302d 313a3231
  39353a32 0030303a 00000000 00000000 00000000 30320013 302d3331 39312d35
  3a38303a 333a3132 00000033 00000000 00000000 00130000 00000006 00000000
  00000009 00000000 00000000 1fddf738 900e3838 00000000 9392b3a0 00000000
  9392b3a0 00000000 00000000 00000000 d72e6ac0 00007fc6 00000000 00000000
  00000000 00000000 900e3838 00000000 900e3a20 00000000 900e3a20 00000000
  900e3a48 00000000 00000008 1fddf738 acf324a4 00000000 00000101 00000000
  0000011d 00000000 96af1bd8 00000000 978a1c90 00000000 900e7308 00000000
  900e1090 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 96bd4090 00000000 00000001 00000095
  00030308 08910321 900e3ab8 00000000 900e3ab8 00000000 00000000 00000000
  d72e6ae0 00007fc6 96bd40d8 00000000 96bd40d8 00000000 00000000 00000000
  06c25c14 00000000 d72e6ae0 00007fc6 96af1bd8 00000000 900e1130 00000000
  900e73a8 00000000 97a84cc8 00000000 00000000 00000015 900e3b28 00000000
  900e3b28 00000000 00000000 00000000 00000000 00000000 900e3b48 00000000
  900e3b48 00000000 900e3b58 00000000 900e3b58 00000000 900e3b68 00000000
    900e3b68 00000000 00000000 00000000 d72e6ac8 00007fc6 00000000 00000000
  00000000 00000000 900e3b98 00000000 900e3b98 00000000 00000000 00000000
  00000000 00000000 02609720 00007fff 10600160 00000100 00000001 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000001
  00014c26 00000000 900e3900 00000000
row cache subordinate object:
address=0x8e3ac218 cid=16(dc_histogram_data_values) set=1 parent=0x900e3838 typ=12
status=VALID/-/-/-/-
data=
00000000 00000000 00000001 00000002 00000003 00000004 00000005 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 9b96981d 47489c9c
99169824 47491818 99169824 47491818 99169824 47491818 99169824 47491818
99969823 47491818 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000009 00000000
37393931 2d34302d 313a3231 39353a32 0030303a 00000000 00004000 00000000
32313032 2d35302d 303a3032 36343a37 0038343a 00000000 88250670 00000000
32313032 2d35302d 303a3032 37343a37 ae31343a 00007fff 06c1c985 00000000
32313032 2d35302d 303a3032 39343a37 0b39353a 00000000 00000000 00000000
32313032 2d35302d 303a3032 34353a37 0032303a 00000000 978a7888 00000000
33313032 2d35302d 303a3931 31323a38 0033333a 4051157c 00000000 00000000
0b7c010c 00007fb7 33333333 415819c7 33333333 415819c7 00002000 00000000
00000000 00000000 0b812d00 00007fb7 00000000 00000000 000003f0 00000000
0baf3fa0 00000000 0b7c00c0 00007fb7 00000000 00000000 00000000 00000000
900e3900 00000000 900e3910 00000000 00000001 00000000 047ebd3c 00000000
00000000 00000000 093205df 00000000 93f39be8 00000000 00000000 00000000
00000000 00000000 9392b3a0 00000000 ae478410 00007fff ae478490 00007fff
913af018 00000000 0b812d10 00007fb7 00000178 00000000 00000178 00007fb7
00000001 00007fb7 00000000 00007fff 00000000 00000000 047ebd3c 00000000
00000000 00000000 0932aec8 00000000 0b7c00c0 00007fb7 00000000 00000000
095e85dc 00000000 00000000 00000000 0baf4150 00000000 ae478478 00007fff
0b7c1660 00007fb7 0b7c17e8 00007fb7 93f39be8 00000000 943b8cc8 00000000
00000000 00000000 00000000 00000000 900e3870 00000000 090dc805 00000000
ae478290 00007fff 0208e25f 00000000 00000000 00000000 00000001 00000000
ae478290 00007fff 091d1c82 00000000 8e3ac218 00000000 00000000 00000000
13131313 00b71313 000000d0 718b0000 00000001 00000000
  BUCKET 63289 total object count=1
  
  
  
  
  
  
  
  
  
  
SQL> select * from test_dc_histogram where owner='MLFDSSDF';

no rows selected

使用owner 查询 时出现新的dc_histogram_defs 信息包括col#=0000000e

BUCKET 61491:
  row cache parent object: address=0x900ec1c8 cid=16(dc_histogram_defs)
  hash=66a7f032 typ=11 transaction=(nil) flags=00000002
  own=0x900ec290[0x900ec290,0x900ec290] wat=0x900ec2a0[0x900ec2a0,0x900ec2a0] mode=S
  status=VALID/-/-/-/-/-/-/-/-
  request=N release=FALSE flags=8
  instance lock=QQ 66a7f032 b3da4caa
  set=0, complete=FALSE
  set=1, complete=FALSE
  data=
  00004650 0000000e 00000005 78010003 09130571 00000317 00000000 40300000
  


  SQL> exec dbms_stats.gather_table_stats(user,'TEST_DC_HISTOGRAM', method_opt=>'FOR ALL COLUMNS SIZE 254');

PL/SQL procedure successfully completed.

SQL> alter system flush shared_pool;

System altered.

SQL> /

System altered.

SQL> /

System altered.

select * from test_dc_histogram where object_id=10;
  
  
SQL> select count(*) from dba_tab_histograms where table_name='TEST_DC_HISTOGRAM' and column_name='OBJECT_ID';

  COUNT(*)
----------
       255

  
  SQL> oradebug setmypid
Statement processed.
SQL>
SQL> oradebug dump row_cache 10;
Statement processed.
SQL> oradebug tracefile_name
/s01/diag/rdbms/prod/PROD1/trace/PROD1_ora_4369.trc


BUCKET 63289:
  row cache parent object: address=0x91d90b60 cid=16(dc_histogram_defs)
  hash=1fddf738 typ=11 transaction=(nil) flags=00000002
  own=0x91d90c28[0x91d90c28,0x91d90c28] wat=0x91d90c38[0x91d90c38,0x91d90c38] mode=S
  status=VALID/-/-/-/-/-/-/-/-
  request=N release=FALSE flags=8
  instance lock=QQ 1fddf738 acf324a4
  set=0, complete=FALSE
  set=1, complete=FALSE
  data=
  00004650 00000009 000000fe 780b00d4 09130571 00000d1f 00000000 407cf000
  911d84fb 3f6c114f 9b96981d 47489c9c 99969823 47491818 00000000 00000000


[oracle@maclean1 ~]$ grep "dc_histogram_data_values" /s01/diag/rdbms/prod/PROD1/trace/PROD1_ora_4369.trc|grep parent=0x91d90b60
address=0x91d90730 cid=16(dc_histogram_data_values) set=1 parent=0x91d90b60 typ=12
address=0x91d90300 cid=16(dc_histogram_data_values) set=1 parent=0x91d90b60 typ=12
address=0x91d8fed0 cid=16(dc_histogram_data_values) set=1 parent=0x91d90b60 typ=12
address=0x903808c8 cid=16(dc_histogram_data_values) set=1 parent=0x91d90b60 typ=12
address=0x90380498 cid=16(dc_histogram_data_values) set=1 parent=0x91d90b60 typ=12
address=0x90380068 cid=16(dc_histogram_data_values) set=1 parent=0x91d90b60 typ=12
address=0x9037fc38 cid=16(dc_histogram_data_values) set=1 parent=0x91d90b60 typ=12
address=0x9037f808 cid=16(dc_histogram_data_values) set=1 parent=0x91d90b60 typ=12
address=0x882c53d8 cid=16(dc_histogram_data_values) set=1 parent=0x91d90b60 typ=12
address=0x882c4fa8 cid=16(dc_histogram_data_values) set=1 parent=0x91d90b60 typ=12
address=0x882c4b78 cid=16(dc_histogram_data_values) set=1 parent=0x91d90b60 typ=12

回复 只看该作者 道具 举报

5#
发表于 2013-5-19 22:48:35
谢谢刘大的回复，不过看的不是很懂，比如：

SQL> select * from test_dc_histogram where object_id=10;

BUCKET 63289:
  row cache parent object: address=0x900e3838 cid=16(dc_histogram_defs)
  hash=1fddf738 typ=11 transaction=(nil) flags=00000002
  own=0x900e3900[0x900e3900,0x900e3900] wat=0x900e3910[0x900e3910,0x900e3910] mode=S
  status=VALID/-/-/-/-/-/-/-/-
  request=N release=FALSE flags=8
  instance lock=QQ 1fddf738 acf324a4
  set=0, complete=FALSE
  set=1, complete=FALSE
  data=
  00004650 00000009 00000005 78010006 09130571 00000317 00000000 407cf000

为什么查询object_id会出现第9列的dc_histogram_defs，同理下面的使用owner查询时又为什么会出现14列的dc_histogram_defs，object_id和owner分别是test_dc_histogram的第4列和第1列吧。

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-12-28 12:18 , Processed in 0.045915 second(s), 21 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569