-
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
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?
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
you have to put the tablespace offline then online again
-
10G is comming up with the feature to flush buffers from the buffer cache.
OCP 7.3,8.0,8i,9i
-
Another point worth noting: you must use NORMAL option when offlining the tablespace!
ALTER TABLESPACE ts_name OFFLINE NORMAL;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Great idea! Not sure why I didn't think of that... I must need some caffeine!
Thanks for all your help!
Jodie
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|