DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: statspack report

  1. #1
    Join Date
    Sep 2002
    Posts
    411

    statspack report

    Could someone please give me some advises with this report??? I think, the report reflect that the database had I/O issues but I am not sure how to attack the right problem.

    Instance Efficiency Percentages (Target 100%)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Buffer Nowait %: 99.94 Redo NoWait %: 100.00
    Buffer Hit %: 84.25 In-memory Sort %: 99.98
    Library Hit %: 90.59 Soft Parse %: 63.25
    Execute to Parse %: 63.75 Latch Hit %: 99.83
    Parse CPU to Parse Elapsd %: 91.82 % Non-Parse CPU: 99.98

    Shared Pool Statistics Begin End
    ------ ------
    Memory Usage %: 72.26 90.46
    % SQL with executions>1: 59.26 48.01
    % Memory for SQL w/exec>1: 42.20 35.04

    Top 5 Wait Events
    ~~~~~~~~~~~~~~~~~ Wait % Total
    Event Waits Time (cs) Wt Time
    -------------------------------------------- ------------ ------------ -------
    db file scattered read 2,419,171 178,492 76.70
    db file sequential read 656,065 25,652 11.02
    log file parallel write 70,378 16,616 7.14
    db file parallel write 1,983 3,639 1.56
    buffer busy waits 108,393 2,169 .93
    Last edited by mike2000; 03-25-2003 at 11:32 PM.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    When 76% of your wait time is on scattered reads, I would guess you have a lot of full table scans in your system. Start tuning your queries to use indexes.
    Jeff Hunter

  3. #3
    Join Date
    Sep 2002
    Posts
    411
    thanks Jeff,

    as far as I/O and memory, you think it's ok ???

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    If I told you no, would you by more hardware or fix the SQL?
    Jeff Hunter

  5. #5
    Join Date
    Sep 2002
    Posts
    411
    thanks for the values advises.
    one more question. the db_file_multiblock_read_count =32.

    is it too high, should it be set to 16????

  6. #6
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Originally posted by mike2000
    thanks for the values advises.
    one more question. the db_file_multiblock_read_count =32.

    is it too high, should it be set to 16????
    Too many FTS (full table scans) are happening in your database. Tune your SQLs. Change db_file_multiblock_read_count to 16 and check for performance.
    -nagarjuna

  7. #7
    Join Date
    Sep 2002
    Posts
    411
    Jeff,

    thanks for all of your response.

    talking FTS, I just found out that it's true the problem is FTS and what I don't understand is all of the neccessary indexes are there and they are not being used. the client anlyzed objects on this database every night and I don't think it's a good thing to do. So what I did was to add a hint into one query and it's used indexes.

    The main question is why Oracle sometimes picked up the indexes and sometimes it's not???
    rebuilt indexes is going to help ????


    could you please tell me why and how to fix this problems???

    thanks
    Last edited by mike2000; 03-26-2003 at 06:54 PM.

  8. #8
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Your db_file_multiblock_read_count is set to higher value. It makes optimizer to calculate the FTS as cheaper. Try changing db_file_multiblock_read_count to 16 and see. You will see that "db file scattered read" wait reducing to a noticeable value.

    Abt optimizer not choosing indexes... It depends on many factors.. The kind of join you use in your query (nested loop, hash, sort merge), cardinality of the indexed columns, and number of rows fetched by each of the sub queries.
    -nagarjuna

  9. #9
    Join Date
    Sep 2002
    Posts
    411
    thanks for your advises.

    so you are saying that the db_file_multiblock_read_count is too high so that Oracle think FTS is cheaper so that it do full table scan instead of using index. By changing the db_file_multiblock_read_count =16, do you think Oracle will try to use indexes somehow. The thing is that before the changes I made today, sometime Oracle use index and sometime it do FTS and it's very inconsistent.

  10. #10
    Join Date
    Jan 2001
    Posts
    2,828
    Originally posted by mike2000
    thanks for your advises.

    so you are saying that the db_file_multiblock_read_count is too high so that Oracle think FTS is cheaper so that it do full table scan instead of using index. By changing the db_file_multiblock_read_count =16, do you think Oracle will try to use indexes somehow. The thing is that before the changes I made today, sometime Oracle use index and sometime it do FTS and it's very inconsistent.

    Nope by db_file_multiblock_read_count would cause the optimizer to think that the cost of a full table scan is cheaper thats it..if you lower it from 32 to 16 then the cost based optimizer would now start thinking that the full table scan are costlier then before..your indexes would be used or not is another question all together..you will have to alter the paramter and take another statspack report and see..and post it here..

    regards
    Hrishy

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