11gr2 sql issue
Date: Tue, 8 Nov 2011 19:05:05 -0800
Message-ID: <CAHDOOG432YqQ15sBsoUeBiPVZVxz764EL3_uWGZSETuYY_jn0w_at_mail.gmail.com>
Hi
As part of regression testing in our db upgrade from 11.1.0.7 to 11.2.0.2, one of the developers reported a slow sql (attached slow_sql.sql) We did a 10053 trace and noticed one of the things was wip_entities was not doing a Index scan in 11gR2 vs 11gR1.
But what is most useful till this point is Tanel's se.sql script which reports this
11.2.0.2 (bad)
SQL> _at_se 4294
SID EVENT TIME_WAITED TOTAL_WAITS
TOTAL_TIMEOUTS AVERAGE_WAIT MAX_WAIT
------- ---------------------------------------- ------------ -----------
- ------------ ---------- 4294* SQL*Net message from dblink* * 806.67 585136 * 0 .0014 .6 CPU Time 73.31 SQL*Net message from client 51.29 226 0 .227 32.87 latch: shared pool .69 84 0 .0082 .26 single-task message .65 1 0 .6453 .65 SQL*Net message to dblink .61 585135 0 0 0 db file sequential read .10 17 0 .0059 .01 Disk file operations I/O .03 19 0 .0017 0 library cache pin .01 17 0 .0003 0 SQL*Net message to client .00 227 0 0 0 library cache lock .00 14 0 .0003 0 events in waitclass Other .00 11 3 .0001 0 gc cr grant 2-way .00 9 0 .0003 0 control file sequential read .00 7 0 .0003 0 SQL*Net break/reset to client .00 3 0 .0001 0 gc current block 2-way .00 2 0 .0004 0 SQL*Net break/reset to dblink .00 2 0 .0002 0
11.1.0.7 (good)
------- ---------------------------------------- ------------ -----------
- ------------ ----------
1737 SQL*Net message from client 264.16
12789 0 .0207 184.73
CPU Time 13.00
- SQL*Net message from dblink 1.11 651 0 .0017 .19* single-task message .90 1 0 .8977 .9 control file sequential read .17 7 0 .0238 .16 SQL*Net more data from dblink .12 742 0 .0002 0 SQL*Net message to client .01 12790 0 0 0 log file sync .01 1 0 .0086 .01 SQL*Net message to dblink .00 651 0 0 0 events in waitclass Other .00 3 0 .0001 0
I ran se.sql at different intervals and in 11.1.07 SQL*Net message from dblink is constant (numbers for that event) but it is constantly increating for 11.2.0.2*.
*If in the 11.2.0.2 database, I use the ordered hint for the sql, then the explain plan is much better and it starts to use hash joins instead of NL. plus I dont see huge waits for the SQL*Net message from db link.
SQL> _at_se 6774
SID EVENT TIME_WAITED TOTAL_WAITS
TOTAL_TIMEOUTS AVERAGE_WAIT MAX_WAIT
------- ---------------------------------------- ------------ -----------
- ------------ ---------- 6774 SQL*Net message from client 134.56 4525 0 .0297 49.42 CPU Time 82.45 db file scattered read 14.30 3015 0 .0047 .28 SQL*Net more data from dblink 8.04 328 0 .0245 1.8 SQL*Net message from dblink 6.50 30 0 .2167 6.18 gc cr multi block request 1.64 2907 0 .0006 0 db file sequential read 1.33 568 0 .0023 .04 single-task message .47 1 0 .4732 .47 gc cr grant 2-way .13 541 0 .0002 0 Disk file operations I/O .06 76 0 .0007 0 SQL*Net message to client .00 4526 0 0 0 SQL*Net message to dblink .00 29 0 0 0 events in waitclass Other .00 15 13 0 0 control file sequential read .00 7 0 .0004 0 SQL*Net break/reset to client .00 3 0 .0001 0 library cache lock .00 1 0 .0008 0
The sql uses a db link but both the databases are using the same db link (meaning target db is the same in both cases).
Any pointers on why this can happen.
Thank you
Kumar
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 08 2011 - 21:05:05 CST