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';
SQL> exec dbms_workload_repository.create_snapshot;
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>
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;
SQL> select sql_text,sql_id from v$sql where lower(sql_text) like '%select * from t_long_sql%';
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_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
长路漫漫，真理难寻，请关注后续更为精彩的测试！
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1244486/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-1244486/
扫一扫
1937
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
