次碰到v$sqlarea中version_count异常的事件之间，找了一下相关的资料，对cursor_sharing， histogram，analyze三者有了一定的认识。

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

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_value，address都是一

---样，但是它们并没有共享，但是你到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_sharing与histogram之间关系的结论：

--- 也就是说：假如同时存在 Histogram 并且cursor_sharing 置为 similar时，那么cursor将不会共享!! 这就造成了每次都是硬解析，child cursor不停的增加。而且在硬解析之前它还要到library cache当中去寻找可以重用的cursor，cursor过多，造成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信息。

于是关于Analyze和Histogram我们得到如下的结论：

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

ITPub博客

Cursor_sharing，Histogram，Analyze之间的关系

最新文章