7 Replies Latest reply on Nov 20, 2012 8:13 AM by Gwydion

    Question about pinning data in the shared pool


      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?

        • 1. Re: Question about pinning data in the shared pool
          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:

          Edited by: Fran on 30-oct-2012 9:05
          • 2. Re: Question about pinning data in the shared pool
            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
              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
                Pinning the package from which the query is run doesn't help, too.
                • 5. Re: Question about pinning data in the shared pool
                  No ideas? Should I post more information?
                  • 6. Re: Question about pinning data in the shared pool
                    Check mos notes :
                    Troubleshooting: High CPU Utilization [ID 164768.1]
                    • 7. Re: Question about pinning data in the shared pool
                      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?