This discussion is archived
7 Replies Latest reply: Nov 20, 2012 12:13 AM by Gwydion RSS

Question about pinning data in the shared pool

Gwydion Newbie
Currently Being Moderated
Hi,

we have a very complex view that generates XML (vie SQL/XML functions) from dozens of tables. The view is called from within a PL/SQl package, something like
SELECT xml
  INTO v_xml
  FROM xml_view xv
 WHERE xv.key = p_key;
 
where p_key is passed as a parameter to the function, which returns the xml. The real situation is a bit more complex, since the function performs some additional work. Since I use PL/SQL variables, the query shows up as a prepared statement in the v$sqlarea something like "SELECT xml FROM xml_view xv WHERE xv.key = :A".

When I call the function the first time, it is very slow (it takes more than 12 seconds). However, subsequent calls with different keys are much faster (usually less than 2 seconds). First I thought that much of the fetched data is probably already in the buffer cache, but clearing the buffer cache between calls of the function didn't have much impact (call takes 2-3 seconds). But clearing the shared pool makes the execution time go up to the original value of more than 12 seconds.
So in order to keep the execution time low I tried to pin the statement (or to be more exact, the shared cursor) in the v$sqlarea. Unfortunately, if I wait long enough (e.g. waiting until the next day in our development db or much less in production) the statement is slow again, even though it is still visible in v$sqlarea.
So it seems to me that some information in the shared pool is generated the first time the statement is executed (a trace of that execution showed that more than 90% of the time is CPU time, not parsing or fetching) and is purged after some time, even if the cursor is pinned.
What can I do to ensure the statement is fast every time I execute it?

Regards,
Pat
  • 1. Re: Question about pinning data in the shared pool
    Fran Guru
    Currently Being Moderated
    Buffer cache contain data, but it hasn't information about the sql, library cache has it. The library cache (that form part of Shared pool) include shared and private sql areas where the sql information resides.

    You have all the information:
    http://docs.oracle.com/cd/B28359_01/server.111/b28318/memory.htm#i10221

    Edited by: Fran on 30-oct-2012 9:05
  • 2. Re: Question about pinning data in the shared pool
    jgarry Guru
    Currently Being Moderated
    Maybe the problem is the metadata that describes the tables. That's data too, perhaps it is getting aged out. Try doing a describe in a manually constructed script on all the tables before running the procedure and see if that changes anything.

    Show us the trace.
  • 3. Re: Question about pinning data in the shared pool
    Gwydion Newbie
    Currently Being Moderated
    I tried running a DESC on all tables and views that are used by the xml view, but it didn't change anything. Which part of the trace should I include? The trace itself is huge, so I first include the resoure usage profile which shows that almost all time is consumed by CPU operation:
    Resource Usage Profile  overall current
    Component                  Total Duration [s]               %    Number of Events     Duration per Event [s]
    CPU                                          10.240           92.008                  n/a           n/a
    unaccounted-for                                  0.659          5.925          n/a           n/a
    db file sequential read                          0.177          1.594          96          0.002
    SQL*Net message from client                  0.052          0.468          3          0.017
    Disk file operations I/O                  0.000          0.003          5          0.000
    SQL*Net more data to client                  0.000          0.001          1          0.000
    SQL*Net message to client                   0.000          0.000          3          0.000
    Total                                          11.129               100.000
    Edited by: Gwydion on Oct 31, 2012 9:28 AM
  • 4. Re: Question about pinning data in the shared pool
    Gwydion Newbie
    Currently Being Moderated
    Pinning the package from which the query is run doesn't help, too.
  • 5. Re: Question about pinning data in the shared pool
    Gwydion Newbie
    Currently Being Moderated
    No ideas? Should I post more information?
  • 6. Re: Question about pinning data in the shared pool
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Check mos notes :
    Troubleshooting: High CPU Utilization [ID 164768.1]
  • 7. Re: Question about pinning data in the shared pool
    Gwydion Newbie
    Currently Being Moderated
    I don't see how this can help me. Even if I could identify the OS process which consumes the CPU time, I still have no idea what is actually going on. What kind of processing is done when I issue the query the first time? And which data is stored in the shared pool the can be reused for subsequernt queries but gets kicked out after some time?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points