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
长路漫漫,真理难寻,请关注后续更为精彩的测试!