FULL Hint not working
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: FULL Hint not working

Hybrid View

  1. #1
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    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?

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    you have to put the tablespace offline then online again

  6. #6
    Join Date
    May 2001
    Posts
    73
    10G is comming up with the feature to flush buffers from the buffer cache.
    OCP 7.3,8.0,8i,9i

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  8. #8
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    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
  •  



Click Here to Expand Forum to Full Width