Cursor_sharing，Histogram，Analyze之間的關係之一

上次碰到v$sqlarea中version_count異常的事件之間，找了一下相關的資料，對cursor_sharing， histogram，analyze三者有了一定的認識。

[@more@]

下在就將對此三者的一些認識做個實驗說明一下：

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。

Cursor_sharing，Histogram，Analyze之間的關係（轉）

相關文章