Output from 10053 trace and dbms_xplan.display_cursor

Oracle 10.1.0.4 on RedHat Linux 3.0

*************************************
PARAMETERS WITH ALTERED VALUES
******************************
parallel_threads_per_cpu = 4
db_file_multiblock_read_count = 32
optimizer_mode = first_rows_10
cursor_sharing = similar
optimizer_index_caching = 25
query_rewrite_enabled = false
query_rewrite_integrity = stale_tolerated
optimizer_dynamic_sampling = 0
*************************************
PARAMETERS WITH DEFAULT VALUES
******************************
optimizer_features_enable = 10.1.0.4

***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats Table: PO_ITEM Alias: PO_ITEM
TOTAL :: CDN: 81294 NBLKS: 3257 AVG_ROW_LEN: 132
Index stats
Index: PO_ITEM_POID COL#: 2
TOTAL :: LVLS: 2 #LB: 365 #DK: 80100 LB/K: 1 DB/K: 1 CLUF: 3191
Index: PO_ITEM_STATUS_RANDOM_UPDDT COL#: 4 6
TOTAL :: LVLS: 2 #LB: 507 #DK: 80100 LB/K: 1 DB/K: 1 CLUF: 45647
Index: PO_ITEM_STATUS_UPDDT COL#: 3 6
TOTAL :: LVLS: 2 #LB: 507 #DK: 80100 LB/K: 1 DB/K: 1 CLUF: 80100
***************************************
SINGLE TABLE ACCESS PATH
COLUMN: POID(NUMBER) Col#: 2 Table: PO_ITEM Alias: PO_ITEM
Size: 5 NDV: 80100 Nulls: 0 Density: 1.2484e-05 Min: 40 Max: 88988
COLUMN: STATUS_RAN(CHARACTER) Col#: 4 Table: PO_ITEM Alias: PO_ITEM
Size: 3 NDV: 20 Nulls: 0 Density: 6.1505e-06
Histogram: Freq #Bkts: 20 UncompBkts: 3985 EndPtVals: 20
TABLE: PO_ITEM Alias: PO_ITEM
Original Card: 81294 Rounded: 1 Computed: 0.00 Non Adjusted: 0.00
Access Path: table-scan Resc: 393 Resp: 393
Access Path: index (equal)
Index: PO_ITEM_POID
rsc_cpu: 24314 rsc_io: 4
ix_sel: 1.2670e-05 ix_sel_with_filters: 1.2670e-05
Access Path: index (scan)
Index: PO_ITEM_STATUS_RANDOM_UPDDT
rsc_cpu: 23778 rsc_io: 4
ix_sel: 6.2422e-06 ix_sel_with_filters: 6.2422e-06
Access Path: index (equal)
Index: PO_ITEM_POID
rsc_cpu: 17832 rsc_io: 3
ix_sel: 1.2670e-05 ix_sel_with_filters: 1.2670e-05
Access Path: index (index-only)
Index: PO_ITEM_STATUS_RANDOM_UPDDT
rsc_cpu: 17632 rsc_io: 3
ix_sel: 6.2422e-06 ix_sel_with_filters: 6.2422e-06
SORT resource Sort statistics
Sort width: 148 Area size: 524288 Max Area size: 26214400
Degree: 1
Blocks to Sort: 1 Row size: 21 Total Rows: 1
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 15225730
Total Temp space used: 0
BEST_CST: 4.00 PATH: 4 Degree: 1
***************************************
GENERAL PLANS
***********************
Join order[1]: PO_ITEM[PO_ITEM]#0
ORDER BY sort
SORT resource Sort statistics
Sort width: 148 Area size: 524288 Max Area size: 26214400
Degree: 1
Blocks to Sort: 1 Row size: 156 Total Rows: 1
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 15225730
Total Temp space used: 0
Best so far: TABLE#: 0 CST: 5 CDN: 1 BYTES: 132
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
SORT resource Sort statistics
Sort width: 148 Area size: 524288 Max Area size: 26214400
Degree: 1
Blocks to Sort: 1 Row size: 156 Total Rows: 1
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 15225730
Total Temp space used: 0
Final - All Rows Plan:
JOIN ORDER: 1
CST: 5 CDN: 1 RSC: 5 RSP: 5 BYTES: 132
IO-RSC: 4 IO-RSP: 4 CPU-RSC: 15249507 CPU-RSP: 15249507


SQL> SELECT /*+ gather_plan_statistics */ * FROM po_item WHERE poid = :a AND STATUS_random = :b ORDER BY poitemid;

SQL> SELECT * FROM table (Cast(dbms_xplan.display_cursor(NULL,NULL, 'runstats_last') as SYS.DBMS_XPLAN_TYPE_TABLE));

PLAN_TABLE_OUTPUT
-----------------

SQL_ID cp44zcmqma34f, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * FROM po_item WHERE poid = :a AND STATUS_random = :b ORDER BY poitemid

Plan hash value: 3970202189

------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows | Buffers | Reads | Writes | A-Time |
------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT ORDER BY | | 1 | 0 | 2377 | 0 | 0 |00:00:00.01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| PO_ITEM | 1 | 0 | 2377 | 0 | 0 |00:00:00.01 |
|* 3 | INDEX RANGE SCAN | PO_ITEM_STATUS_RANDOM_UPDDT | 1 | 4153 | 29 | 0 | 0 |00:00:00.02 |
------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / PO_ITEM@SEL$1
3 - SEL$1 / PO_ITEM@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("POID"=:A)
3 - access("STATUS_RANDOM"=:B)


27 rows selected.

SQL> set autot on
SQL>
SELECT /* */ * FROM po_item WHERE poid = :a AND STATUS_random = :b ORDER BY poitemid;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3115544074

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 132 | 5 (20)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 132 | 5 (20)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| PO_ITEM | 1 | 132 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | PO_ITEM_POID | 1 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("STATUS_RANDOM"=:B)
3 - access("POID"=TO_NUMBER(:A))