Re: fastest SQL?
Date: Fri, 12 Mar 2010 08:52:12 +0100
Message-ID: <cd8f74561003112352y4fee5053v2e6372a7ea75775a_at_mail.gmail.com>
I just tested the result cache for any effect (11gR1 on Sun-Blade-T6340, Solaris10)
- no_result_cache
alter system flush shared_pool;
System altered.
select SQL_ID, elapsed_time from v$sql
2 where sql_id = 'dtda63h3189hv';
no rows selected
select /* no_result_cache */ 1 from dual;
1
1
select SQL_ID, elapsed_time from v$sql
2 where sql_id = 'dtda63h3189hv';
SQL_ID ELAPSED_TIME
------------- ------------
dtda63h3189hv 97707
select /* no_result_cache */ 1 from dual;
1
1
select SQL_ID, elapsed_time from v$sql
2 where sql_id = 'dtda63h3189hv';
SQL_ID ELAPSED_TIME
------------- ------------
dtda63h3189hv 97707
- result_cache alter system flush shared_pool;
System altered.
select SQL_ID, elapsed_time from v$sql
2 where sql_id = 'f3cqkqf9272pp';
no rows selected
select /* result_cache */ 1 from dual;
1
1
select SQL_ID, elapsed_time from v$sql
2 where sql_id = 'f3cqkqf9272pp';
SQL_ID ELAPSED_TIME
------------- ------------
f3cqkqf9272pp 32253
select /* berx1 */ 1 from dual;
1
1
select SQL_ID, elapsed_time from v$sql
2 where sql_id = 'f3cqkqf9272pp';
SQL_ID ELAPSED_TIME
------------- ------------
f3cqkqf9272pp 32253
here, with and without result_cache the 2nd run of the statement is faster
than ELAPSED_TIME can show.
Does anyone know a better method to measure here?
On Thu, Mar 11, 2010 at 21:48, Martin Berger <martin.a.berger_at_gmail.com>wrote:
> Toon, Andre,
>
> can you test it, please?
> I'm not sure if the additional code and SGA access in result cache improves
> the query.
>
> (ok, we are now on a measurement issue: how can we measure such a fast sql?
> any good idea?)
>
>
>
> Am 11.03.2010 um 21:41 schrieb Toon Koppelaars:
>
> Any method to make it even faster?
>>
>
> Use the query result cache hint?
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Mar 12 2010 - 01:52:12 CST