-
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
-
ell you need to work out hy the plans are different
is the data the same, are the stats the same
-
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!
-
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.
-
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
-
as soon as the stats have been collected it invalidates the cursor.
So the answer is right away
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|