[Oracle] Consistent gets from cache (fastpath)

Looking for information on "consistent gets from cache (fastpath)", google found this great blog entry.

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.

Roderick said...

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.

In my scenario, there were two identical plans and an identical number of consistent gets. In 11g it ran more than twice as fast.

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,146

11g:

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

Squirrels

Silver Tide (Woodstock Saga)

Many years ago I read a book series called The Woodstock Saga. It was a novel about fictional squirrels in a fictional squirrel world.

There were red and grey squirrels. Reds lived on "Ourland", an island. The greys lived on the mainland. Red "teacher" squirrels taught the greys about the "Sun". Many Christian parallels. The reds have "kernels" or sayings. Here are some of my favorites.

Punctuality
Is vital. Others time wasted,
Is stolen by you
And can never be returned.
Lost minutes sink forever.

If you could know all
Then you could understand all
Then you'd forgive all.

Squirrels who don't check,
May "assume" a fox's mouth
To be a safe den.

Curiosity
Drives dicsovery. Beware -
Daring fools may die.

Squirrels have the right
To explain their own actions,
Fully - in silence.

Squirrels do not live
By nuts alone. Take time off
To seek out beauty.

People puzzle us
With their strange actions. But then
They're only human.

If you think you can
Or if you think you cannot
Either way it's true.

In any crisis
A leaders first duty, is -
To keep hope alive.

Each squirrel is free
To choose its own root through life
Guided by Kernels.

A delayed action -
Stultifies. Find the root cause,
Grub it out and Act.

Unknown danger near
Lie high, wait, watch and look out.
Trust in the Sun's light.

Squirrels protect trees,
They have enough enemies,
Treat them as your friends.

In times of great stress
Rest is a sound investment -
Restoring one's strength.

Oh great loving Sun
We are in need of guidance
Please enlighten us.

In a strange country,
Be careful. Time spent looking
Is seldom wasted.

Indecision kills.
Act positively and lead.
Action is the Key.

A submissive stance
And a request, presumes help -
Give it if you can.

Your prayers alone
Will not do. The Sun will help
Those who help themselves.

Trust in the Sun.
His ways are mysterious.
Faith can fell fir trees.

When quite exhausted
Keep on going while you must,
Never, ever quit.

To ensure a Peace
A wise squirrel will always
Prepare for a War.

Let justice be done,
In every squirrels case,
Though the sky may fall.

My First Blog

This is my first blog and I don't intend to update it very often (if at all). What have I done recently? I spent the Easter School Holiday week at Tangalooma with my family. It was a nice relaxing holiday sitting on the beach, swimming in the ocean, reading a couple of books, feeding dolphins, watching dolphins, watching dolphins (a second time on request of our youngest son - and he picked the best night as the dolphins treated us to a lot of antics), walking to the wrecks, kayaking to the wrecks, frizbie'ing starfish back into the ocean, quad bikes, playing cards.