快捷搜索:

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

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          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

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

本文源自: AG88环亚

您可能还会对下面的文章感兴趣: