[20150724]無法通過sql_id找到sql語句.txt

[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 6ssrk2dqj7jbx
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語句幾乎不能發現問題的情況。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1749265/，如需轉載，請註明出處，否則將追究法律責任。