1，ITPUB壇友提出的問題

http://www.itpub.net/thread-1876900-1-1.html

2，開始測試，資料庫版本資訊

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

3,手工生成一個取樣的快照

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

4，模擬一個查詢的sql

SQL> select 1 from dual connect by level<=8;

1

----------

1

8 rows selected.

5,上述查詢sql的的共享池中的sql_id

SQL> set linesize 300

SQL> col sql_text for a100

SQL> select sql_text,sql_id from v$sql where lower(sql_text) like '%select 1 from dual connect by level<=8%';

SQL_TEXT SQL_ID

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

select 1 from dual connect by level<=8 cjx0zkqa1j18d

6,手工生成一個取樣的快照

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

7,在dba_hist_sqltext沒有查詢到上述2次手工取樣快照之間執行的sql

SQL> desc dba_hist_sqltext;

Name Null? Type

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

DBID NOT NULL NUMBER

SQL_ID NOT NULL VARCHAR2(13)

SQL_TEXT CLOB

COMMAND_TYPE NUMBER

SQL> select dbid,sql_id,sql_text from dba_hist_sqltext where lower(sql_text) like '%select 1 from dual connect by level<=8%';

no rows selected

8,同上，在dba_hist_sql_plan也沒有查詢到上述2次手工取樣快照之間執行的sql

SQL> select dbid,sql_id,plan_hash_value from dba_hist_sql_plan where sql_id='cjx0zkqa1j18d';

no rows selected

9,同理，在dba_hist_sqlstat也沒有結果

SQL> select snap_id,dbid,sql_id,plan_hash_value from dba_hist_sqlstat where sql_id='cjx0zkqa1j18d';

no rows selected

10，這是什麼原因呢，我猜想可能是這個sql只在執行了1次，ORACLE共享池只要執行3次以上的SQL才會在共享池記錄下來

所以我們手工把上述的查詢SQL手工執行4次

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> select 1 from dual connect by level<=8;

1

----------

1

8 rows selected.

SQL> select 1 from dual connect by level<=8;

1

----------

1

8 rows selected.

SQL> select 1 from dual connect by level<=8;

1

----------

1

8 rows selected.

SQL> select 1 from dual connect by level<=8;

1

----------

1

8 rows selected.

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL>

11，再次在dba_hist_sqltext，dba_hist_sqlplan,dba_hist_sqlstat查詢，上述的查詢SQL已經被抓獲取了

SQL> col sql_text for a150

SQL> set linesize 300

SQL> select dbid,sql_id,sql_text from dba_hist_sqltext where lower(sql_text) like '%select 1 from dual connect by level<=8%';

DBID SQL_ID SQL_TEXT

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

3209836149 4958xjs57njkg select dbid,sql_id,sql_text from dba_hist_sqltext where lower(sql_text) like '%select 1 from dual connect by level<=8%'

3209836149 64wbk44zw5pna select sql_text,sql_id from v$sql where lower(sql_text) like '%select 1 from dual connect by level<=8%'

3209836149 cjx0zkqa1j18d select 1 from dual connect by level<=8

SQL> select dbid,sql_id,plan_hash_value from dba_hist_sql_plan where sql_id='cjx0zkqa1j18d';

DBID SQL_ID PLAN_HASH_VALUE

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

3209836149 cjx0zkqa1j18d 1236776825

SQL> select snap_id,dbid,sql_id,plan_hash_value from dba_hist_sqlstat where sql_id='cjx0zkqa1j18d';

SNAP_ID DBID SQL_ID PLAN_HASH_VALUE

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

125 3209836149 cjx0zkqa1j18d 1236776825

11，我們再多想一些，如果某個sql剛好只執行3次，會不會被記錄下來呢,為了測試，我們重寫另一個查詢sql

SQL> select snap_id,dbid,sql_id,plan_hash_value from dba_hist_sqlstat where sql_id='cjx0zkqa1j18d';

SNAP_ID DBID SQL_ID PLAN_HASH_VALUE

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

125 3209836149 cjx0zkqa1j18d 1236776825

SQL>

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> select 111 from dual connect by level<=9;

111

----------

111

9 rows selected.

SQL> select 111 from dual connect by level<=9;

111

----------

111

9 rows selected.

SQL> select 111 from dual connect by level<=9;

111

----------

111

9 rows selected.

SQL>

SQL> select dbid,sql_id,sql_text from dba_hist_sqltext where lower(sql_text) like '%select 111 from dual connect by level<=9%';

DBID SQL_ID SQL_TEXT

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

3209836149 7tck2yyzrcbn8 select 111 from dual connect by level<=9

SQL>

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

12，如果某個sql執行剛好sql,也會從v$sql檢視中抓取到dba_hist字首

SQL> select dbid,sql_id,sql_text from dba_hist_sqltext where lower(sql_text) like '%select 111 from dual connect by level<=9%';

DBID SQL_ID SQL_TEXT

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

3209836149 7tck2yyzrcbn8 select 111 from dual connect by level<=9

SQL>

SQL> select dbid,sql_id,plan_hash_value from dba_hist_sql_plan where sql_id='7tck2yyzrcbn8';

DBID SQL_ID PLAN_HASH_VALUE

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

3209836149 7tck2yyzrcbn8 1236776825

SQL> select snap_id,dbid,sql_id,plan_hash_value from dba_hist_sqlstat where sql_id='7tck2yyzrcbn8';

SNAP_ID DBID SQL_ID PLAN_HASH_VALUE

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

127 3209836149 7tck2yyzrcbn8 1236776825

13，我們再試下如果某SQL只執行2次，會如何呢，不再列舉測試結果，只給出測試結論

結論：sql執行2次，也會從v$sql抓取到dba_hist字首相關的表中

14，小結

1，sql執行2次以上，會被從v$sql抓取到dba_hist字首相關的表中

2, sql如果只執行1次，且執行時間非常快就結束，是不是被從v$sql抓取到dba_hist字首相關的表中

15，這裡有個問題，如果某sql只執行1次，且在2個快照結束之間仍未執行完畢，會如何呢？

16，繼續測試，先構建測試表

SQL> create table t_long_sql(a int);

Table created.

SQL> insert into t_long_sql values(1);

1 row created.

SQL> commit;

Commit complete.

17,另啟一新會話，更新測試表不提交

SQL> update t_long_sql set a=3;

1 row updated.

SQL>

18，回到原會話，手工生成2個快照，且在2個快照之間執行一個查詢sql

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> select * from t_long_sql;

A

----------

1

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

19, 如果對一個表update且不提交，之後執行只執行1次對此表的查詢sql,會從v$sql抓取到dba_hist字首的表中

SQL> select sql_text,sql_id from v$sql where lower(sql_text) like '%select * from t_long_sql%';

SQL_TEXT SQL_ID

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

select * from t_long_sql dshkhu754j01y

select sql_text,sql_id from v$sql where lower(sql_text) like '%select * from t_long_sql%' b0rp11038k795

SQL> select dbid,sql_id,sql_text from dba_hist_sqltext where sql_id='dshkhu754j01y';

DBID SQL_ID SQL_TEXT

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

3209836149 dshkhu754j01y select * from t_long_sql

SQL> select dbid,sql_id,plan_hash_value from dba_hist_sql_plan where sql_id='dshkhu754j01y';

DBID SQL_ID PLAN_HASH_VALUE

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

3209836149 dshkhu754j01y 557260049

SQL> select snap_id,dbid,sql_id,plan_hash_value from dba_hist_sqlstat where sql_id='dshkhu754j01y';

SNAP_ID DBID SQL_ID PLAN_HASH_VALUE

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

131 3209836149 dshkhu754j01y 557260049

SQL>

小結：

1，是不是說明明如果對某表dml不提交，然後執行1次針對此表的查詢sql,就會記錄下來

20，如下測試，又顛覆了我們的前面的測試，某查詢就是執行1次，仍會記錄在dba_hist字首的表中

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> select sql_text,sql_id from v$sql where lower(sql_text) like '%select 1 from dual connect by level<=6%';

SQL_TEXT SQL_ID

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

select sql_text,sql_id from v$sql where lower(sql_text) like '%select 1 from dual connect by level<=6%' 9zhs4vdr5475m

select 1 from dual connect by level<=6 9v2mg07uyq8b9

SQL> select dbid,sql_id,sql_text from dba_hist_sqltext where sql_id='9v2mg07uyq8b9';

DBID SQL_ID SQL_TEXT

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

3209836149 9v2mg07uyq8b9 select 1 from dual connect by level<=6

SQL> select dbid,sql_id,plan_hash_value from dba_hist_sql_plan where sql_id='9v2mg07uyq8b9';

DBID SQL_ID PLAN_HASH_VALUE

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

3209836149 9v2mg07uyq8b9 1236776825

SQL> select snap_id,dbid,sql_id,plan_hash_value from dba_hist_sqlstat where sql_id='9v2mg07uyq8b9';

SNAP_ID DBID SQL_ID PLAN_HASH_VALUE

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

133 3209836149 9v2mg07uyq8b9 1236776825

長路漫漫，真理難尋，請關注後續更為精彩的測試！

oracle手工快照會不會把所有的v$sql中的sql寫入到dba_hist字首的表呢？

相關文章