Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    87

    Unanswered: Full Table Scans

    Hi,
    In an effort to improve performance I was wondering if the following approach could help me.
    1.Enable Monitoring for FTS going on in my database(That will be looking for wait event 'db file scattered Read' say every 10 mins
    2.Identifying database tables for which this event takes place
    3.Finding size of such tables
    4. Configure then in Recycle Buffer Pool if they are big and in Keep Buffer Pool if they are small (as compared to the current buffer cache size)
    5.Making parallel degree two for them
    Suggestions welcome!
    Thanks

  2. #2
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: Full Table Scans

    Originally posted by MePreeti
    Hi,
    In an effort to improve performance I was wondering if the following approach could help me.
    1.Enable Monitoring for FTS going on in my database(That will be looking for wait event 'db file scattered Read' say every 10 mins
    2.Identifying database tables for which this event takes place
    3.Finding size of such tables
    4. Configure then in Recycle Buffer Pool if they are big and in Keep Buffer Pool if they are small (as compared to the current buffer cache size)
    5.Making parallel degree two for them
    Suggestions welcome!
    Thanks
    http://download-west.oracle.com/docs...a96533/toc.htm

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    what version are you running?

    just trace all your sessions and then tkprof the trace files, then
    search for FULL TABLE SCAN

    simple, effective, fun
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Here's a little script I use that queries v$sqlarea to look for "FULL"...
    It may help out ...

    Gregg
    Attached Files Attached Files

  5. #5
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    The_Duck,
    Mine is a datawarehousing database where in tracing all sessions querying the reports all teh time may not be possible. And dusring night times when the batches run I will not be available to trace sessions!
    That was why I enabled monitoring for the detection of the wait even 'db file scattered read' through cronjob which will help me locate such tables which underwent FTS.

  6. #6
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    This is giving me
    ORA-01000: maximum open cursors exceeded
    while executing the procedure although there is no row in the plan_table.
    Open_cursors set for my db is 700.

  7. #7
    Join Date
    Jul 2003
    Posts
    38
    Originally posted by MePreeti
    This is giving me
    ORA-01000: maximum open cursors exceeded
    while executing the procedure although there is no row in the plan_table.
    Open_cursors set for my db is 700.
    Hi,
    The open cursor count of 700 is very low actually. We are running with 1000 and we faced the same kind of issue. Then we increased the same to 5000. Now it is ok. 'se i think oracle will consider even a select from a table also an open cursor. So better increase the open cursor count.

    regards
    avr

  8. #8
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    Suggestions??

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •