THE Q U A D R O BLOG: Consistent gets from cache (fastpath)
In cases I've seen the number of consistent gets between 10g and 11g are the same. This equates to a comment by Roderick in the above post.
In my scenario, there were two identical plans and an identical number of consistent gets. In 11g it ran more than twice as fast.FYI. The reduction in consistent gets is actually due to a separate internal optimization. The fastpath gets allows those fewer consistent gets to use a shorter code path.
10g:
Plan hash value: 4121601496 ------------------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ |* 1 | FILTER | | 142 | | | | | 100K|00:02:32.29 | 20M| 57 | | 2 | TABLE ACCESS BY INDEX ROWID| CUSTOMER_NODE_HISTORY | 142 | 2 | 98 | 5 (0)| 00:00:01 | 2840K|00:00:11.74 | 2777K| 0 | |* 3 | INDEX RANGE SCAN | P_CUSTOMER_NODE_HISTORY | 142 | 2 | | 2 (0)| 00:00:01 | 2840K|00:00:02.84 | 10542 | 0 | |* 4 | TABLE ACCESS BY INDEX ROWID| PRODUCT_INSTANCE_HISTORY | 2840K| 1 | 30 | 199 (3)| 00:00:01 | 100K|00:02:07.75 | 17M| 57 | | 5 | AND-EQUAL | | 2840K| | | | | 100K|00:01:59.76 | 17M| 54 | |* 6 | INDEX RANGE SCAN | I_PRODUCT_INSTANCE_HISTORY_CN | 2840K| 6 | | 3 (0)| 00:00:01 | 3118K|00:00:26.55 | 8998K| 0 | |* 7 | INDEX RANGE SCAN | I_PRODUCT_INSTANCE_HISTORY_PRD | 2840K| 72000 | | 195 (3)| 00:00:01 | 347K|00:01:24.34 | 8844K| 54 | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ consistent gets from cache 23,100,14611g:
Plan hash value: 4121601496 --------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 142 | | | 228 (100)| | 100K|00:01:08.66 | 20M| |* 1 | FILTER | | 142 | | | | | 100K|00:01:08.66 | 20M| | 2 | TABLE ACCESS BY INDEX ROWID| CUSTOMER_NODE_HISTORY | 142 | 2 | 98 | 5 (0)| 00:00:01 | 2840K|00:00:09.16 | 2796K| |* 3 | INDEX RANGE SCAN | P_CUSTOMER_NODE_HISTORY | 142 | 2 | | 2 (0)| 00:00:01 | 2840K|00:00:01.22 | 10684 | |* 4 | TABLE ACCESS BY INDEX ROWID| PRODUCT_INSTANCE_HISTORY | 2840K| 1 | 30 | 199 (3)| 00:00:02 | 100K|00:00:47.42 | 17M| | 5 | AND-EQUAL | | 2840K| | | | | 100K|00:00:45.24 | 17M| |* 6 | INDEX RANGE SCAN | I_PRODUCT_INSTANCE_HISTORY_CN | 2840K| 6 | | 3 (0)| 00:00:01 | 3118K|00:00:20.43 | 8998K| |* 7 | INDEX RANGE SCAN | I_PRODUCT_INSTANCE_HISTORY_PRD | 2840K| 72000 | | 195 (3)| 00:00:01 | 347K|00:00:21.17 | 8854K| --------------------------------------------------------------------------------------------------------------------------------------------------------- consistent gets from cache 23,012,794 consistent gets from cache (fastp 10,022,007
