If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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

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