Hi,
on my 11.2 RAC database on Linux servers,
i've a join on 2 table with this execution plan:
Execution Plan
----------------------------------------------------------
Plan hash value: 3669554776
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 404K| 19M| | 230K (1)| 00:00:06 |
| 1 | SORT ORDER BY | | 404K| 19M| 26M| 230K (1)| 00:00:06 |
|* 2 | HASH JOIN | | 404K| 19M| 2664K| 225K (1)| 00:00:05 |
|* 3 | INDEX RANGE SCAN | PK_TAB1 | 97158 | 1518K| | 356 (1)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TAB2 | 716K| 23M| | 223K (1)| 00:00:05 |
-----------------------------------------------------------------------------------------------
When using orderd hint, hash join order changes in:
Execution Plan
----------------------------------------------------------
Plan hash value: 2999560214
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 404K| 19M| | 230K (1)| 00:00:06 |
| 1 | SORT ORDER BY | | 404K| 19M| 24M| 230K (1)| 00:00:06 |
|* 2 | HASH JOIN | | 404K| 19M| 32M| 225K (1)| 00:00:05 |
|* 3 | TABLE ACCESS FULL| TAB2 | 716K| 23M| | 223K (1)| 00:00:05 |
|* 4 | INDEX RANGE SCAN | PK_TAB1 | 97158 | 1518K| | 356 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Seems more faster than the first.
But ORDER BY clause generate this event "direct path read temp" and it's very slow.
PGA is about 5Gb.
Advisor show me:
| PGA Target Est (MB) | Size Factr | W/A MB Processed | Estd Extra W/A MB Read/ Written to Disk | Estd PGA Cache Hit % | Estd PGA Overalloc Count | Estd Time |
|---|
| 1,024 | 0.13 | 4,438.61 | 4,304.65 | 51.00 | 0 | 3,400,405 |
| 2,048 | 0.25 | 4,438.61 | 4,304.65 | 51.00 | 0 | 3,400,405 |
| 4,096 | 0.50 | 4,438.61 | 4,304.65 | 51.00 | 0 | 3,400,405 |
| 6,144 | 0.75 | 4,438.61 | 4,304.65 | 51.00 | 0 | 3,400,405 |
| 8,192 | 1.00 | 4,438.61 | 4,304.65 | 51.00 | 0 | 3,400,405 |
| 9,830 | 1.20 | 4,438.61 | 4,304.65 | 51.00 | 0 | 3,400,405 |
| 11,469 | 1.40 | 4,438.61 | 4,304.65 | 51.00 | 0 | 3,400,405 |
| 13,107 | 1.60 | 4,438.61 | 4,304.65 | 51.00 | 0 | 3,400,405 |
I've gathered awr during the query execution and this is the result:
| Event | Waits | Total Wait Time (sec) | Wait Avg(ms) | % DB time | Wait Class |
|---|
| DB CPU | | 570.9 | | 83.3 | |
| direct path write temp | 10,556 | 91 | 9 | 13.3 | User I/O |
| direct path read | 1,584 | 14.8 | 9 | 2.2 | User I/O |
Tablespace IO:
| Tablespace | Reads | Av Rds/s | Av Rd(ms) | Av Blks/Rd | 1-bk Rds/s | Av 1-bk Rd(ms) | Writes | Writes avg/s | Buffer Waits | Av Buf Wt(ms) |
|---|
| TEMP | 11,055 | 15 | 0.00 | 15.01 | 20,452 | 0.00 | 0 | 27 | 0 | 0.00 |
What can I check?