Hi,
below is an excerpt from a rather long execution plan of a statement on a 10.2.0.5 database.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
...
| 7 | WINDOW SORT | | 1 | 1561 | 8424K|00:05:56.29 | 197K| 170K| 98440 | 1730M| 12M| 90M (1)| 1539K|
|* 8 | HASH JOIN | | 1 | 1561 | 8424K|00:00:33.76 | 197K| 2692 | 0 | 1634K| 872K| 2415K (0)| |
|* 9 | TABLE ACCESS BY INDEX ROWID| TABLE1 | 1 | 4062 | 8791 |00:00:00.03 | 221 | 0 | 0 | | | | |
|* 10 | INDEX RANGE SCAN | IDX_TABLE1 | 1 | 12117 | 8791 |00:00:00.01 | 22 | 0 | 0 | | | | |
|* 11 | TABLE ACCESS FULL | TABLE2 | 1 | 126K| 16M|00:00:49.27 | 197K| 2692 | 0 | | | | |
...
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Of course, there are huge discrepancies between actual and estimated cardinalities (126k vs 16M) which are due to the bad statistics. However, updating the stats won't change the plan (I tried the cardinality hint already), and plus, it's not possible to do that right now for a number of reasons. My questions are:
1) am I correct assuming that WINDOW SORT is slow because poor cardinality estimate results in undersized temp area to carry out the window sorting operation?
2) if the answer to 1) is yes, is there a hint or a session-level parameter which could correct this problem?
some parameters that may be relevant to the issue:
hash_area_size integer 2097152
sort_area_retained_size integer 512000
sort_area_size integer 1048576
workarea_size_policy string AUTO
Best regards,
Nikolay