This content has been marked as final. Show 7 replies
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
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.
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:
Edited by: Gwydion on Oct 31, 2012 9:28 AM
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
Pinning the package from which the query is run doesn't help, too.
No ideas? Should I post more information?
Check mos notes :
Troubleshooting: High CPU Utilization [ID 164768.1]
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?