Many Oracle databases
have high consistent gets (and hence, high CPU consumption) from
excessive "select 1 from dual; calls.
This issue is fixed in
Oracle10g with the new
"fast
dual" execution plan, but you can do a trick in earlier releases of Oracle to
reduce consistent gets (logical I/O) from three to zero.
There are two ways to cache the dual fixed table in Oracle; this,
take your pic:
Fix approach
One:
connect
sys/mypass as sysdba;
create view x_$dual as select * from x$dual ;
grant
select on x_$dual to public;
connect
my_schema_owner/mypassword;
create synonym DUAL for sys.x_$dual ;
Fix
approach Two:
1. Allocate a small keep pool if it
doesn’t exist.
2. From the sys user issue “alter table dual cache
storage(buffer_pool keep);
3. This will drop the consistent gets from accessing dual from 3 to
0.
4. The problem is dual is always an FTS so it is placed at the end
of the LRU and is aged out fast. By marking it as cached and placing
it in the Keep pool this is avoided. In order to get to 0 CR I
had to use both cache and the keep option to make this work.
This caching of the dual fixed table can greatly reduce CPU
consumptions of certain applications.
