Re: fastest SQL?

From: Martin Berger <martin.a.berger_at_gmail.com>
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-l
Received on Fri Mar 12 2010 - 01:52:12 CST

Original text of this message