[20150724]无法通过sql_id找到sql语句.txt

--前一阵子遇到一个奇怪的问题，要优化一个项目：
http://www.itpub.net/thread-1930339-1-1.html

10.2.0.4 遇到一个问题？

> select count(*) from V$ACTIVE_SESSION_HISTORY where sql_id= '7pucjfm8w1vy6';
  COUNT(*)
----------
      3213

> select * from DBA_HIST_SQLTEXT where sql_id='7pucjfm8w1vy6';
no rows selected

--这个sql大约是3：30执行。
我查询x$kglob也没有。

--我检查发现共享池设置太小，增大后第2天观察，一切正常。
SQL> show sga
Total System Global Area  599785472 bytes
Fixed Size                  2085776 bytes
Variable Size             109055088 bytes
Database Buffers          482344960 bytes
Redo Buffers                6299648 bytes

SQL> alter system set shared_pool_size=188M ;
System altered.

SQL> show sga
Total System Global Area  599785472 bytes
Fixed Size                  2085776 bytes
Variable Size             209718384 bytes
Database Buffers          381681664 bytes
Redo Buffers                6299648 bytes

--可以通过一个例子来演示这种情况：

SCOTT@test> @ &r/ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

SCOTT@test> set timing on ;
SCOTT@test> select count(*) from emp,emp,emp,emp,emp,emp,emp,dept ;
    COUNT(*)
------------
   421654016
Elapsed: 00:00:19.63

--以上语句在我的测试环境执行要20秒。避开整点的AWR采样,执行如下:

SCOTT@test> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.62
SCOTT@test> Select count(*) from emp,emp,emp,emp,emp,emp,emp,dept ;
    COUNT(*)
------------
   421654016

Elapsed: 00:00:19.66
SCOTT@test> Select count(*) from emp,emp,emp,emp,emp,emp,emp,dept ;
    COUNT(*)
------------
   421654016
Elapsed: 00:00:19.54
--注意我这里修改Select的开头大写，与前面执行的不同,这样保证这个sql_id仅仅执行2次。

SCOTT@test> alter system flush shared_pool;
System altered.
--清除共享池。

Elapsed: 00:00:00.09
SCOTT@test> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.08

SCOTT@test> select sql_id,count(*) from V$ACTIVE_SESSION_HISTORY where sample_time >=sysdate - 15/1440 group by sql_id having count(*)>=35;
SQL_ID            COUNT(*)
------------- ------------
3gtp56ub7yqqn           39

--因为执行时间接近20秒(2次),这样查询count(*)>=35，对应的sql_id='3gtp56ub7yqqn'.通过如下也可以确定sql_id是正确的。

SCOTT@test> select sys.dbms_sqltune_util0.sqltext_to_sqlid('Select count(*) from emp,emp,emp,emp,emp,emp,emp,dept '||chr(0)) c20 from dual;
C20
--------------------
3gtp56ub7yqqn

SCOTT@test> select * from DBA_HIST_SQLTEXT where sql_id='3gtp56ub7yqqn';
no rows selected

--可以发现视图DBA_HIST_SQLTEXT没有发现。
--观察awr报表：
Time Model Statistics                    DB/Inst: TEST/test  Snaps: 4219-4220
-> Total time in database user-calls (DB Time): 39s
-> Statistics including the word "background" measure background process
   time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name

Statistic Name                                       Time (s) % of DB Time
------------------------------------------ ------------------ ------------
DB CPU                                                   39.0        100.0
sql execute elapsed time                                 38.9         99.7
parse time elapsed                                        0.6          1.6
hard parse elapsed time                                   0.6          1.6
PL/SQL compilation elapsed time                           0.0           .1
PL/SQL execution elapsed time                             0.0           .1
hard parse (sharing criteria) elapsed time                0.0           .0
repeated bind elapsed time                                0.0           .0
DB time                                                  39.0          N/A
background cpu time                                       0.1          N/A
background elapsed time                                   0.1          N/A
          -------------------------------------------------------------      

--DB CPU=39秒。

SQL ordered by Elapsed Time              DB/Inst: TEST/test  Snaps: 4219-4220
-> Resources reported for PL/SQL code includes the resources used by all SQL
   statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
   into the Total Database Time multiplied by 100

  Elapsed      CPU                  Elap per  % Total             
  Time (s)   Time (s)  Executions   Exec (s)  DB Time    SQL Id   
---------- ---------- ------------ ---------- ------- -------------
         0          0            1        0.2     0.5 bunssq950snhf
insert into wrh$_sga_target_advice (snap_id, dbid, instance_number, SGA_SIZ
E, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS) select :snap_id, :dbi
d, :instance_number, SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_R
EADS from v$sga_target_advice

         0          0            2        0.0     0.1 6s-s-rk2dqj7jbx
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (n
ext_date <= :2)) or ((last_date is null) and (next_date < :3))) and (field1
= :4 or (field1 = 0 and 'Y' = :5)) and (this_date is null) order by next_date, j
ob

--可以发现SQL ordered by Elapsed Time根本没有。

--这个不知道算不算oracle的bug，如果sql语句执行很少，在共享池设置很小的情况下，没到awr取样，语句已经被清除共享池，这样再
--生成awr报表时会看到一个非常奇特的情况,看不到有问题的sql语句。而且如果大家看我上传的awr报表可以发现,db time接近40分钟，
--而看下面的sql语句几乎不能发现问题的情况。