Re: fastest SQL?

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@xxxxxxxxx>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?
>
>
>

Other related posts: