Buffer gets are more in RAC compared to single instance database
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Buffer gets are more in RAC compared to single instance database

  1. #1
    Join Date
    Jun 2006
    Posts
    4

    Buffer gets are more in RAC compared to single instance database

    Hi,

    Recently, we configured Oracle 10g Release2 RAC with two nodes on Red hat Linux (64 bit). Then we created users and imported data from our production (single instance, Oracle 10g Release2 on Red hat Linux 64 bit) database server. When the reports are opening from new RAC server, we observed that it is taking more time when compared to single instance database. For one particular report, the report is taking 1 minute 40 sec in single instance database server, but the same report taking 5 min 30 sec in RAC environment.

    When I checked in v$sqlarea, in RAC environment, the sql statement is taking more CPU time (101 sec) and Buffer gets(77220000) as compared to 5 sec of CPU time and 4700000 of buffer_gets in single instance database. I checked the execution plan. RAC is using a different execution plan, but the "COST" of RAC execution plan is 336 as compared to 1600 of single instance database

    I am not able to understand, when the execution plan of RAC is better than that of single instance database server, why RAC is taking more CPU time and why RAC is fetching more buffer.

    Any help is greatly appreciated.


    Ramesh

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    ell you need to work out hy the plans are different

    is the data the same, are the stats the same

  3. #3
    Join Date
    Nov 2001
    Posts
    335
    Cost is not absolute measurement of the execution plan, I would not use it for comparison of the execution plans in 2 different databases.
    One, who thinks that the other one who thinks that know and does not know, does not know either!

  4. #4
    Join Date
    Apr 2007
    Location
    USA
    Posts
    110
    Quote Originally Posted by BV1963
    Cost is not absolute measurement of the execution plan, I would not use it for comparison of the execution plans in 2 different databases.
    Agreed. Cost is not a reliable stat for performance...
    You can check
    -same no# CPU?
    -Same SGA/RAM?
    -same stats?
    -Did you bring over your indexes? High buffer read/fetches are indicative of poor/no index
    Looking for the greatest evil in the world? Look in the mirror.

  5. #5
    Join Date
    Jun 2006
    Posts
    4

    Smile

    Hi

    After importing data, I collected statistics using DBMS_STATS. After that I immediately opened the reports and compared the time with the single instance database. At that time, RAC taken more time and execution plan of RAC is different from the single instance database. ... But what happened in the night, I don't know, in the morning when I opened the reports, they take almost same time and execution plans of RAC is exactly same as in single instance database.

    ..... My problem gets solved

    Any body has any idea about how much time Oracle will take to consider new statistics when constructing exection plans .. .. .. and what are the steps to discard the execution plans already there in cache .. so that Oracle will construct new execution plans using latest statistics

    thanks for all

    Ramesh

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    as soon as the stats have been collected it invalidates the cursor.

    So the answer is right away

  7. #7
    Join Date
    Nov 2001
    Posts
    335
    It is so trivial, and has nothing to do with RAC
    One, who thinks that the other one who thinks that know and does not know, does not know either!

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