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
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:
sys/mypass as sysdba;
create view x_$dual as select * from x$dual ;
select on x_$dual to public;
create synonym DUAL for sys.x_$dual ;
1. Allocate a small keep pool if it
2. From the sys user issue “alter table dual cache
3. This will drop the consistent gets from accessing dual from 3 to
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.