[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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-1749265/