OraFAQ Forum: Performance Tuning » Clearing Blocks from Memory

Home » RDBMS Server » Performance Tuning » Clearing Blocks from Memory

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Clearing Blocks from Memory [message #165705]

Fri, 31 March 2006 14:14

scottwmackey
Messages: 515
Registered: March 2005

Senior Member

Let's say I run the following query:

SELECT col1
FROM   t
WHERE  group = 1

Let's say that the table has several million rows and the count for the query returns 20K. The first time I run it, it returns in about 3 seconds. The second time and every time after that, it returns in about 0.5 second. I know that that is because the data blocks have already been read into memory (is it the SGA?) so that subsequent queries do not have to do disk I/O. If I wait a while for other people to perform queries and push these out of memory, my query will take 3 seconds again to retrieve 20K rows. So my question is, Is there are way to clear out the memory to see how long a query would take to perform "fresh"?

ALTER SYSTEM FLUSH SHARED_POOL

doesn't seem to have an effect. We need to verify that the query will be under 3 seconds and the vast majority of the query where clauses will be run once and only once so we will not benefit from the block being stored in memory.

Report message to a moderator

Re: Clearing Blocks from Memory [message #165818 is a reply to message #165705]

Sun, 02 April 2006 06:06

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

What is Oracle version/OS?
Did you already try this?

scott@9i >  select count(*) from emp where job='MANAGER';

Elapsed: 00:00:00.92

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2706  consistent gets
          0  physical reads
          0  redo size
        493  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

scott@9i > alter session set events 'immediate trace name flush_cache';

Session altered.

Elapsed: 00:00:19.58
scott@9i >  select count(*) from emp where job='MANAGER';

Elapsed: 00:00:01.24

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2706  consistent gets
       2701  physical reads
          0  redo size
        493  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Regards

Report message to a moderator

Re: Clearing Blocks from Memory [message #165829 is a reply to message #165818]

Sun, 02 April 2006 22:13

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Superb!

Report message to a moderator

Re: Clearing Blocks from Memory [message #165882 is a reply to message #165705]

Mon, 03 April 2006 03:24

nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...

Senior Member

Perhaps putting your table in the keep portion of the sga would help?
Neil.

Report message to a moderator

Re: Clearing Blocks from Memory [message #165906 is a reply to message #165882]

Mon, 03 April 2006 04:43

madhusunkara
Messages: 59
Registered: March 2006
Location: hyderabad,india

Member

put the table in recycle pool

you can flush the buffer cache in newer verions of oracle with out bouncing but it will effect other queries also

Regards
Madhu

Report message to a moderator

Previous Topic:	Query Speed Slow.
Next Topic:	gather_fixed_objects_stats

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 30 06:52:25 CDT 2024