oracle dba_hist tablepsace,oracle手工快照会不会把所有的v$sql中的sql写入到dba_hist前缀的表呢？...

1，ITPUB坛友提出的问题

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

1

1

1

1

1

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

1

1

1

1

1

1

1

8 rows selected.

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

1

----------

1

1

1

1

1

1

1

1

8 rows selected.

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

1

----------

1

1

1

1

1

1

1

1

8 rows selected.

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

1

----------

1

1

1

1

1

1

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

3209836149 cjx0zkqa1j18d      1236776825

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

111

111

111

111

111

111

111

111

9 rows selected.

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

111

----------

111

111

111

111

111

111

111

111

111

9 rows selected.

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

111

----------

111

111

111

111

111

111

111

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

3209836149 7tck2yyzrcbn8      1236776825

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

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

3209836149 9v2mg07uyq8b9      1236776825

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

长路漫漫，真理难寻，请关注后续更为精彩的测试！