Cursor_sharing,Histogram,Analyze之間的關係

sxzhanghl發表於2009-03-19
次碰到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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章