Hello,
We got a server with 18c databases (5 of them) where the same query (a select on a view) takes twice as long than on any other database on same server.
Underlying tables of this view have very similar volumes (even a little more in one where it's quicker), statistics are up-to-date; execution plan *is the same* everywhere too. All parameters are the same too (except CPU_COUNT=12 where it's "slow", it's 24 everywhere else).
If I run this select on view in a loop (I loop 5000 times) with a 10046 trace, grep'ing waits in resulting trace files shows the following (I did a "grep ^WAIT <trc file>|cut -d'=' -f2|sort|uniq -c")
for DB where it's slow:
1750857 'ASM IO for non-blocking poll' ela
1 'db file parallel read' ela
2 'db file scattered read' ela
1046 'db file sequential read' ela
652968 'direct path read temp' ela
106466 'direct path write temp' ela
10 'Disk file operations I/O' ela
1 'latch free' ela
16 'PGA memory operation' ela
4 'SQL*Net message from client' ela
4 'SQL*Net message to client' ela
for DBs where it's quicker, it always shows something similar to:
2 'db file parallel read' ela
1 'db file scattered read' ela
1430 'db file sequential read' ela
9 'Disk file operations I/O' ela
7 'PGA memory operation' ela
4 'SQL*Net message from client' ela
4 'SQL*Net message to client' ela
My question: where should I try to find the reason why that many direct path temp operations take place on "slow" database ?
Any suggestion ?
Thanks a lot...
Regards,
Sebino