FULL Hint not working

Hi!
I'm trying to analyze my io rates on one of my machines, and am finding that the FULL hint doesn't appear to work.

Oracle 8.1.7.4, AIX 5.2
The instance is not being used by anyone else.
The table I'm testing on has 2+ Million rows and is about 77 Meg.
My buffer cache is 336 Meg.

Code:

SQL> alter session set db_file_multiblock_read_count=24; Session altered. SQL> alter session set events '10046 trace name context forever, level 8'; Session altered. SQL> select /*+ FULL(a) */ count(*) from oradba.merstrqtr a; COUNT (*) ---------- 2007672 SQL> exit

Here is the output from my trc:

Code:

*** 2004-01-14 15:20:16.600 *** SESSION ID:(21.6) 2004-01-14 15:20:16.599 APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240 ===================== PARSING IN CURSOR #3 len=69 dep=0 uid=21 oct=42 lid=21 tim=36979260 hv=589283212 ad='48090628' alter session set events '10046 trace name context forever, level 8' END OF STMT EXEC #3:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=36979260 WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0 *** 2004-01-14 15:21:53.063 WAIT #3: nam='SQL*Net message from client' ela= 9646 p1=1650815232 p2=1 p3=0 ===================== PARSING IN CURSOR #3 len=55 dep=0 uid=21 oct=3 lid=21 tim=36988906 hv=3650852591 ad='4772385c' select /*+ FULL(a) */ count(*) from oradba.merstrqtr a END OF STMT PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=36988906 EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=36988906 WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0 FETCH #3:c=56,e=57,p=0,cr=7707,cu=24,mis=0,r=1,dep=0,og=1,tim=36988963 WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1650815232 p2=1 p3=0 FETCH #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=36988963 WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 806 p1=1650815232 p2=1 p3=0 STAT #3 id=1 cnt=1 pid=0 pos=0 obj=0 op='SORT AGGREGATE ' STAT #3 id=2 cnt=2007672 pid=1 pos=1 obj=12146 op='TABLE ACCESS FULL MERSTRQTR ' XCTEND rlbk=0, rd_only=1 ===================== PARSING IN CURSOR #3 len=183 dep=1 uid=0 oct=6 lid=0 tim=36989769 hv=1963542461 ad='4805fdb0' update sys.aud$ set action#=:2, returncode=:3, logoff$time=sysdate, logoff$pread= :4, logoff$lread=:5, logoff$lwrite=:6, logoff$dead=:7 where sessionid=:1 and entryid=1 and action#=100 END OF STMT PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=36989769 EXEC #3:c=0,e=0,p=0,cr=3,cu=3,mis=0,r=1,dep=1,og=4,tim=36989769 STAT #3 id=1 cnt=1 pid=0 pos=0 obj=0 op='UPDATE AUD$ ' STAT #3 id=2 cnt=2 pid=1 pos=1 obj=90 op='TABLE ACCESS BY INDEX ROWID AUD$ ' STAT #3 id=3 cnt=2 pid=2 pos=1 obj=104 op='INDEX RANGE SCAN ' WAIT #0: nam='log file sync' ela= 0 p1=3590 p2=0 p3=0

As you can see, the only waits are on SQL*Net.. no scattered read. It appears to be getting the data from the cache. Why is it going to the cache if I'm telling to do a full table scan? Is the table I'm using so small that it's storing it in the cache? I thought full table scans didn't keep the data in the cache, just enough from one Multi-block-read count.

Any help would be appreciated.

Thanks!
Jodie

Re: FULL Hint not working

Quote:

Originally posted by jodie
I thought full table scans didn't keep the data in the cache, just enough from one Multi-block-read count.

Ah, not so ... the blocks get placed at the Least Recently Used end of the LRU list in the cache, but they still get cached.

This behaviour can be changed with the CACHE hint, although that's obviously not relevant to your case.

Thanks, Dave.. good to know.

So, any good way to force it to go to disk, since nobody else is pushing data through the cache - besides restarting the instance?? I suppose I could issue a whole bunch of queries myself to force the data off the list, but - yuk.

Any brilliant ideas?

Quote:

Originally posted by jodie
So, any good way to force it to go to disk, since nobody else is pushing data through the cache - besides restarting the instance??

If you put the tablespace where your table oradba.merstrqtr resides OFFLINE and then back again ONLINE, all the buffers that belongs to that tablespace will be flushed out of the buffer cache.

That is, if you can afford to put that tablespace oflline, of course.

you have to put the tablespace offline then online again

10G is comming up with the feature to flush buffers from the buffer cache.

Another point worth noting: you must use NORMAL option when offlining the tablespace!
ALTER TABLESPACE ts_name OFFLINE NORMAL;

Great idea! Not sure why I didn't think of that... I must need some caffeine!

Thanks for all your help!
Jodie