Hi,
We encountered a Bug 8671349 -
Automatic degree of parallelism may be too high ( Doc ID
8671349.8 ) on our Core Oracle Production database
11.2.0.1 , the two options were either
to run the system in no parallel mode taking a hit on performance or to upgrade
.
This update from oracle came late Thursday night so early
Friday an executive decision was taken by our higher management to upgrade and
migrate our production from existing server to new server(with better specs).
I successfully upgraded and migrated the database from 11.2.0.1,
Win 2008 R2 to 11.2.0.4 Win 2012, but
observed that the database was extremely slow.
I ran the below query and observed that the maximum time spent
by database was on direct path reads! Awr also confirmed the same
select * from( select
dba_objects.object_name, dba_objects.object_type,active_session_history.event,
sum(active_session_history.wait_time + active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history, dba_objects
where
active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.current_obj# =
dba_objects.object_id group by dba_objects.object_name,
dba_objects.object_type, active_session_history.event order by 4 desc);
dba_objects.object_type, active_session_history.event order by 4 desc);
The query to check this hidden parameter is
select a.ksppinm name,b.ksppstvl
value,b.ksppstdf deflt,decode
(a.ksppity, 1,'boolean', 2,'string',
3,'number', 4,'file', a.ksppity) type,
a.ksppdesc description from sys.x$ksppi
a,sys.x$ksppcv b where
a.indx = b.indx and a.ksppinm like
'\_serial_direct_read%' escape '\'
To disable this
alter system
set "_serial_direct_read"=never scope=both;
The above change worked like magic and the
performance improved.
The next day was Sunday which is a working day in gulf, observed that
all sessions were hanging with event resmgr:cpu
quantum.
Since a production system stabilizes over a period
of time we had many such tweaking s to be done on the new database.
After the database installation the database by default took the default maintenance window
which should be Friday and Saturday in gulf and not Sunday which is a working
day.
Checked resource_manager_plan setting
SQL> show parameter resource_manager_plan
NAME_COL_PLUS_SHOW_PARAM
------------------------------------------------------------------------------
TYPE
------------------------------------------------------------------------------
VALUE_COL_PLUS_SHOW_PARAM
------------------------------------------------------------------------------
resource_manager_plan
string
SCHEDULER[0x32DF]:DEFAULT_MAINTENANCE_PLAN
alter system set resource_manager_plan=''
scope=both;
Quantum cpu gone after this :)
You can create a plan as per your organization maintenance
windows and set it as a default plan.
