Select statement runs slow until flush of shared pool

From: Jorgensen, Finn <Finn.Jorgensen_at_constellation.com>
Date: Tue, 27 Nov 2012 12:34:51 -0500
Message-ID: <9CE162BC5ED2C643956B526A7EDE46FF03594458A415_at_EXM-OMF-04.Ceg.Corp.Net>



List:
10.2.0.3 EE on Solaris. No RAC.

I have a client who run a report which consist of just a select statement. Nothing really special except there's a couple of stored function calls in the select. From time to time while running this report it gets hung up and takes hours to complete. When that happens somebody on the DBA team realized that flushing the shared pool fixes the problem and the report end almost immediately after the flushing.

I was oncall this morning when they needed the shared pool flush. I took a little time to see if I could quickly determine what was locking up the SQL statement. In OEM there was a sea of green meaning it was all registered as CPU time. I could see 9 sessions running the select statement at the same time. I checked the dynamic SGA and got this:

COMPONENT                                                        CURRENT_MB     MIN_MB     MAX_MB
---------------------------------------------------------------- ---------- ---------- ----------
DEFAULT buffer cache                                                    848        768          0
java pool                                                                48         48          0
large pool                                                               48         16          0
shared pool                                                            1024        848          0
streams pool                                                             16          0          0

I checked how much free memory there was in the shared pool and got this:   1 select * from v$sgastat
  2 where pool = 'shared pool'
  3* and name = 'free memory'
SQL> /

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  free memory                 728943624
                                        ----------
sum                                      728943624

I used Tanel Poders sgastatx script to check for subpools and got this (I only have 1 subpool): SQL> _at_sgastatx total

  • All allocations:
SUBPOOL                             BYTES         MB
------------------------------ ---------- ----------
shared pool (0 - Unused):        33554432         32
shared pool (1):               1177308608    1122.77
shared pool (Total):           1210863040    1154.77
                               ----------
sum                            2421726080

I then ran latchprofx and got this:
SQL > _at_latchprofx name,sid,sqlid,sqlchild % % 100000

NAME                                       SID SQLID           SQLCHILD       Held       Gets  Held %     Held ms Avg hold ms
----------------------------------- ---------- ------------- ---------- ---------- ---------- ------- ----------- -----------
cache buffers chains                       220 dy6p54n6ymuhx          3       1938       1936    1.94     159.497        .082
cache buffers chains                       102 dy6p54n6ymuhx          3       1650       1648    1.65     135.795        .082
cache buffers chains                       104 dy6p54n6ymuhx          3       1605       1603    1.61     132.092        .082
cache buffers chains                       356 dy6p54n6ymuhx          3       1401       1392    1.40     115.302        .083
simulator lru latch                        220 dy6p54n6ymuhx          3        401        397     .40      33.002        .083
simulator lru latch                        102 dy6p54n6ymuhx          3        375        375     .38      30.863        .082
simulator lru latch                        104 dy6p54n6ymuhx          3        353        353     .35      29.052        .082
simulator lru latch                        356 dy6p54n6ymuhx          3        336        332     .34      27.653        .083
cache buffers lru chain                    220 dy6p54n6ymuhx          3         80         80     .08       6.584        .082
cache buffers chains                       251 184w65v7z6cpr          1         61         61     .06       5.020        .082
cache buffers lru chain                    102 dy6p54n6ymuhx          3         20         20     .02       1.646        .082
archive process latch                      386                        0         20          3     .02       1.646        .549
cache buffers lru chain                    356 dy6p54n6ymuhx          3         17         16     .02       1.399        .087
multiblock read objects                    220 dy6p54n6ymuhx          3         15         15     .02       1.235        .082
SQL memory manager latch                   386                        0         12          3     .01        .988        .329
cache buffers lru chain                    104 dy6p54n6ymuhx          3          9          8     .01        .741        .093
object queue header operation              220 dy6p54n6ymuhx          3          6          6     .01        .494        .082
cache buffers chains                       247 8dz24ywwkrgn3          0          6          6     .01        .494        .082
redo copy                                  212 6szcgz387bc84          3          6          6     .01        .494        .082
cache buffers chains                       367 6jn4skq8jmbwj          3          6          6     .01        .494        .082

The SQL id dy6p54n6ymuhx is the SQL statement that hangs until the shared pool is flushed.

At this point I'm not sure what to look for. There are no locks and no wait event reported.

Any ideas are welcome.

Thanks,
Finn

>>> This e-mail and any attachments are confidential, may contain legal,
professional or other privileged information, and are intended solely for the addressee. If you are not the intended recipient, do not use the information in this e-mail in any way, delete this e-mail and notify the sender. -IP2

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 27 2012 - 18:34:51 CET

Original text of this message