Hi,
I have a very slow query on my 12c database (EE 12.1.0.2). It queries a view based on many tables with many outer joins, these tables are all partitioned and have millions of rows.
I'm not asking for advice on the query plan but I would appreciate some guidance on the AWR SQL Report. Consider this:
| Stat Name | Statement Total | Per Execution | % Snap Total |
|---|
| Elapsed Time (ms) | 61,780,599 | 61,780,599.37 | 43.45 |
| CPU Time (ms) | 28,567,146 | 28,567,146.08 | 56.87 |
| Executions | 1 | | |
| Buffer Gets | 776,323 | 776,323.00 | 0.16 |
| Disk Reads | 720,828 | 720,828.00 | 0.42 |
| Parse Calls | 1 | 1.00 | 0.00 |
| Rows | 0 | 0.00 | |
| User I/O Wait Time (ms) | 88,533 | | |
| Cluster Wait Time (ms) | 0 | | |
| Application Wait Time (ms) | 1,154 | | |
| Concurrency Wait Time (ms) | 33 | | |
| Invalidations | 0 | | |
| Version Count | 18 | | |
| Sharable Mem(KB) | 6,547 | | |
My question here is: where is most of elapse time going to? A big part is going to CPU. I would have expected that IO wait time would take up the rest of the wait time. Because during query runtime, I see a lot of waits on "direct path read temp" and "direct path read". Is there something missing? Is perhaps the network taking a lot of time because I know that this query is supposed to output 4M rows (the report shows 0; have no idea why).
On what part do I need to focus if I want this query to run faster? on cpu? on IO? on network throughput?
Relevant reports are attached.
Kind regards,
Benny Derous.