ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Cursor_sharing,Histogram,Analyze之间的关系

Cursor_sharing,Histogram,Analyze之间的关系

原创 Linux操作系统 作者:sxzhanghl 时间:2009-03-19 12:20:07 0 删除 编辑
次碰到v$sqlareaversion_count异常的事件之间,找了一下相关的资料,对cursor_sharing histogramanalyze三者有了一定的认识。

下在就将对此三者的一些认识做个实验说明一下:

SQL> show parameter cursor_sharing

NAME TYPE

------------------------------------ ----------------------

VALUE

------------------------------

cursor_sharing string

SIMILAR

SQL> col name format a30

SQL> set linesize 100

SQL> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

------------------------------ ----------

parse time cpu 57

parse time elapsed 804

parse count (total) 338

parse count (hard) 157

parse count (failures) 3

SQL> select count(*) from test_hist where object_id = 2000;

COUNT(*)

----------

1

SQL> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

------------------------------ ----------

parse time cpu 57

parse time elapsed 804

parse count (total) 340

parse count (hard) 157

parse count (failures) 3

SQL> alter session set nls_language=american;

Session altered.

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

------------------------------ ----------

parse time cpu 61

parse time elapsed 813

parse count (total) 361

parse count (hard) 163 (硬解析的次数)

parse count (failures) 5

SQL> select count(*) from test_hist where object_id = 2000;

COUNT(*)

----------

1

SQL> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

------------------------------ ----------

parse time cpu 63

parse time elapsed 815

parse count (total) 368

parse count (hard) 168

parse count (failures) 5

SQL> select count(*) from test_hist where object_id = 2000;

COUNT(*)

----------

1

SQL> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

------------------------------ ----------

parse time cpu 63

parse time elapsed 815

parse count (total) 371

parse count (hard) 168 (重新执行,硬解析没有增加)

parse count (failures) 5

SQL> select count(*) from test_hist where object_id = 2001;

---改变object_id的值看看有什么变化

COUNT(*)

----------

1

SQL> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

------------------------------ ----------

parse time cpu 63

parse time elapsed 815

parse count (total) 373

parse count (hard) 168 (看到没,即使object_id不同,也没有硬解析)

parse count (failures) 5

SQL> select count(*) from test_hist where object_id = 2002;

-----再次改变一下看看:

COUNT(*)

----------

1

SQL> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

------------------------------ ----------

parse time cpu 63

parse time elapsed 815

parse count (total) 375

parse count (hard) 168 (还是没有增加)

parse count (failures) 5

SQL> select count(*) from test_hist where object_id = 2001111111;

---改变object_id数量级看看:

COUNT(*)

----------

0

SQL> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

------------------------------ ----------

parse time cpu 63

parse time elapsed 815

parse count (total) 379

parse count (hard) 168 (同样没有改变)

parse count (failures) 5

SQL> analyze table test_hist compute statistics for table for columns object_id;

---分析表和字段看看,注意这种分析方式会产生Histogram信息,下面会说明:

Table analyzed.

SQL> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

------------------------------ ----------

parse time cpu 76

parse time elapsed 841

parse count (total) 408

parse count (hard) 185

parse count (failures) 6

SQL> select count(*) from test_hist where object_id = 2000;

COUNT(*)

----------

1

SQL> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

------------------------------ ----------

parse time cpu 76

parse time elapsed 841

parse count (total) 410

parse count (hard) 186

parse count (failures) 6

SQL> select count(*) from test_hist where object_id = 20001;

COUNT(*)

----------

0

SQL> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

------------------------------ ----------

parse time cpu 76

parse time elapsed 841

parse count (total) 412

parse count (hard) 187 (看到没object_id改变时,产生了硬解析)

parse count (failures) 6

SQL> select count(*) from test_hist where object_id = 20001;

COUNT(*)

----------

0

SQL> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

------------------------------ ----------

parse time cpu 76

parse time elapsed 841

parse count (total) 414

parse count (hard) 187 (重复执行,硬解析次数不变)

parse count (failures) 6

SQL> select count(*) from test_hist where object_id = 20002;

COUNT(*)

----------

0

SQL> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

------------------------------ ----------

parse time cpu 78

parse time elapsed 843

parse count (total) 416

parse count (hard) 188 (看到没object_id改变时,产生了硬解析)

parse count (failures) 6

SQL> select sql_text,child_number from v$sql where sql_text like 'select count(*) from test_hist%';

SQL_TEXT

---------------------------------------------------------------------------------------------------

CHILD_NUMBER

------------

select count(*) from test_hist where object_id = :"SYS_B_0"

0

select count(*) from test_hist where object_id = :"SYS_B_0"

1

select count(*) from test_hist where object_id = :"SYS_B_0"

2

SQL> select sql_text,version_count,hash_value,address from v$sqlarea where sql_text like 'select count(*) from test_hist%';

SQL_TEXT

----------------------------------------------------------------------------------------------------

VERSION_COUNT HASH_VALUE ADDRESS

------------- ---------- --------

select count(*) from test_hist where object_id = :"SYS_B_0"

3 1855362923 663366B0

SQL> select * from v$sql_shared_cursor

2 where KGLHDPAR = '663366B0';

ADDRESS KGLHDPAR UN SQ OP OU ST LI SE EX BU PD IN SL TY AU BI DE LA TR RO IN IN RE LO IN OV SQ MV

-------- -------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

US TY NO FL

-- -- -- --

66359468 663366B0 N N N N N N N N N N N N N N N N N N N N N N N N N N N

N N N N

66302298 663366B0 N N N N N N N N N N N N N N N N N N N N N N N N N N N

N N N N

66047F00 663366B0 N N N N N N N N N N N N N N N N N N N N N N N N N N N

N N N N

---看到没,有意思的事情发生了,这就是我所遇到的问题!!

---也就是说,虽然在v$sql中存在3个版本的sql,虽然它们的hash_valueaddress都是一

---样,但是它们并没有共享,但是你到v$sql_shared_cursor中查找不能共享的原因时,却又---都是N,那么这只能有一种可能Bug

---metalink上的说明:

High library cache latch contention under high parsing rates.

A few SQL statements have hundreds of versions.

V$SQL_SHARED_CURSOR shows N in all columns.

CURSOR_SHARING is SIMILAR. .

DIAGNOSTIC ANALYSIS:

--------------------

If histograms are being used then cursors will not be shared. This is normal behaviour. Will check for existence of histograms.

Bug:5056340 non-sharing with histograms and SIMILAR = not a bug

于是我们得到关于cursor_sharinghistogram之间关系的结论:

--- 也就是说:假如同时存在 Histogram 并且cursor_sharing 置为 similar时,那么cursor将不会共享!! 这就造成了每次都是硬解析,child cursor不停的增加。而且在硬解析之前它还要到library cache当中去寻找可以重用的cursorcursor过多,造成library cache latch过长时间的持有。

--- 虽然这是个bug,但是oracle却没有承认.

那么这个问题该如何解决呢?

答案是: 删除Histogram,但是这会存在一个问题,会将表和索引的分析信息也一起删除,因此删除Histogram之后还要将表和索引再分析一下,以便让执行计划走CBO

下面就来分析一下几种analyze用法,看看哪些会生产Histogram,哪些不会

SQL> analyze table test_hist delete statistics;

Table analyzed.

SQL> analyze table test_hist compute statistics;

Table analyzed.

SQL> select count(*) from dba_tab_histograms where table_name = 'TEST_HIST';

COUNT(*)

----------

26 ---看到没生成了Histogram

SQL> analyze table test_hist delete statistics;

Table analyzed.

SQL> select count(*) from dba_tab_histograms where table_name = 'TEST_HIST';

COUNT(*)

----------

0

SQL> analyze table test_hist compute statistics for table for all indexes;

Table analyzed.

SQL> select count(*) from dba_tab_histograms where table_name = 'TEST_HIST';

COUNT(*)

----------

0 ---Histogram不存在!!

SQL> analyze table test_hist delete statistics;

Table analyzed.

SQL> analyze table test_hist compute statistics for table for columns object_id;

Table analyzed.

SQL> select count(*) from dba_tab_histograms where table_name = 'TEST_HIST';

COUNT(*)

----------

76 ---产生Histogram信息。

---接下来我们来看看关于对索引的分析是怎么样的?

SQL> create index ind_objid on test_hist(object_id);

Index created.

SQL> analyze table test_hist delete statistics;

Table analyzed.

SQL> select count(*) from dba_tab_histograms where table_name = 'TEST_HIST';

COUNT(*)

----------

0

SQL> analyze table test_hist compute statistics for table;

Table analyzed.

SQL> select count(*) from dba_tab_histograms where table_name = 'TEST_HIST';

COUNT(*)

----------

0 ---光对表分析,不会产生Histogram

SQL> analyze table test_hist delete statistics;

Table analyzed.

SQL> analyze table test_hist compute statistics for all indexes;

Table analyzed.

SQL> select count(*) from dba_tab_histograms where table_name = 'TEST_HIST';

COUNT(*)

----------

0 ---对所有索引分析不会产生Histogram

SQL> analyze table test_hist compute statistics for all indexed columns;

Table analyzed.

SQL> select count(*) from dba_tab_histograms where table_name = 'TEST_HIST';

COUNT(*)

----------

76 ---对索引列分析产生Histogram信息。

于是关于AnalyzeHistogram我们得到如下的结论:

  只要对列进行分析的话,就会产生Histogram信息。而光对表,索引进行分析的话,则不会产生Histogram,而且默认的COMPUTE | ESTIMATE STATISTICS; 对表进行分析是会产生Histogram的,因为它默认的会加上for all columns

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

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-03-19

  • 博文量
    31
  • 访问量
    60046