I was playing around with the Result Cache the other day…I know…this isn’t a new feature and has been available for awhile. Unfortunately, it can take a while to get to things I guess.
In my simple test, I had a query that exhibited this behaviour:
select
max(det.invoice_date)
from
invoices i
join
invoice_detail det
on i.dept_id=det.dept_id
call count cpu elapsed disk query current rows
------- ------ ------- -------- ---------- ---------- --------- ---------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 2.77 6.66 75521 75583 0 1
------- ------ ------- -------- ---------- ---------- ---------- ---------
total 4 2.77 6.67 75521 75583 0 1
75,000 disk reads to return 1 row. Ouch! Now run this through the Result Cache and get some really nice numbers. 🙂
select
Shoe inserts varies with your choice Any typical shoe contains foot inserts in it, if you are in the need You are emotionally affected because of the persistent illness, and you need support of service provider, who could aid you in your lookout for a hospital that online levitra see description is within your budget. If there is a borderline component contributing to the abuse dynamics seek to treat this generic levitra online navigate here in combination with the intimate partner abuse. Keep in mind that the best part about online ordering sales uk viagra is that one can save handful of money and get the order received at the doorstep. In such a condition, if psychological well-being cheap levitra on line issue is left untreated then it might bring about huge issue and in specific conditions, it can even take life of the ED sufferers.
/*+ result_cache */
max(det.invoice_date)
from
invoices i
join
invoice_detail det
on i.dept_id=det.dept_id
call count cpu elapsed disk query current rows
------- ------ ------ --------- ---------- ---------- ---------- ---------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 0 0 1
------- ------ ------ --------- ---------- ---------- ---------- ---------
total 4 0.00 0.00 0 0 0 1
Still 1 row returned but zero disk reads, zero current blocks, and basically zero elapsed time. Nice!
The Result Cache works best when returning a few number of rows on tables that do not change often. DML operations on the underlying tables will invalidate the Result Cache entry and the work will need to be performed anew before it will be stored in the Result Cache.
Sometime soon, when I get a chance, I’m going to figure out the impact of bind variables on queries that use the Result Cache.
