Re: Chache??

Hi Marc,

Don't you just hate it when Oracle is efficient and caches data so you don't need to reload it again and again ;)

Not sure why you want override this behaviour but I'm sure you have your reasons.....

Oracle actually 'stores' two relevant things here.

Firstly, it stores the actual code you're executing along with the execution plan. It stores this in the Library Cache which is a component of the Shared Pool which is a component of the System Global Area (SGA). When Oracle parses the statement and determines the execution plan (which takes time and in the case of complex queries can take a substantial amount of time), this is stored away so that when the same code is subsequently executed, Oracle doesn't have to redo all the work of parsing and redetermining the execution plan. It's already there (note all users have access to this area of memory).

Therefore, the first time you execute the query it probably needs to be (hard) parsed and so initially needs to go through this overhead. Subsequent times you execute the query, Oracle probably finds the SQL in the Library cache and is more efficient as a result as the hard parse is not performed.

To 'get rid' of this, you can issue the ALTER SYSTEM FLUSH SHARED_POOL;

Secondly, Oracle stores the 'blocks' that contain the rows of interest in a Buffer Cache that is also a component of the SGA. The reason it does this is two fold. Firstly, Oracle can only manipulate and extract information out of the blocks once they're loaded into memory. However they are stored in a shared memory structure because having gone to all the trouble of retrieving the data from disk, there might be a possibility that the blocks might need to be accessed again. Note this possibility is greater for the session that has read in the block(s) but depending on the data (eg. block on a freelist) and the data structure (eg. popular index) it is quite likely that another session may want access to the same block. The Buffer Cache is organised in a number of linked lists (eg. Least Recently Used LRU list) to encourage popular blocks to remain cached in memory.

Therefore, the first time you run your query, the blocks need to be read from disk and loaded into the Buffer Cache. However when you subsequently re-execute the query, the blocks are probably still cached in memory, so you avoid the (expensive) disk reads and the query runs faster as a result.

There is no flush Buffer Cache command (as it never makes sense to do this). If you really, really want to perform disk reads each time (?), I guess some things you could consider is to bounce the database or run a query on another large table via an index that hence fills the buffer cache with these 'alien' blocks.

Cheers

Richard
"Marc Zinzen" <mzinzen_at_genese.de> wrote in message news:3D916F19.E011F9B1_at_genese.de...
> Hi.
>
> This is the situation.
> I did:
> Create table
> (
> prKey integer
> Str Varchar2(30)
> bVar Smallint
> );
> I filled this Table with 100.000 unique rows.
> Then I did the following select 10* : select * from t_ind_unt where
> prKey < 1000
> I traced it by settting alter session set sql_trace = true and got my
> report over tkprof. It seems that it after the first time i do this
> select, Oracle stores this information somewhere. I need to do some
> testing though. So here's my question.
> Where does Oracle store what info from my select-query and How can I get
> Oracle to quit doing that. I need clean results, not from cache or from
> any temp table.
>
> Thanks for your help
> Marc
>
Received on Wed Sep 25 2002 - 09:03:09 CDT