Home » RDBMS Server » Performance Tuning » Buffer Busy Wait DBMS_ALERT_INFO (Oracle DB 11.2.0.3, Standard 64-bit, Windows 2008 R2)
Buffer Busy Wait DBMS_ALERT_INFO [message #597275] Wed, 02 October 2013 09:57 Go to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
Hello,

I've have a system where the most buffer waits are spent on system tables like
SYS.DBMS_ALERT_INFO
and SYS.DBMS_LOCK_ALLOCATED

Would it help to convert the system tablespace from dictionary managed to locally managed?


Re: Buffer Busy Wait DBMS_ALERT_INFO [message #597277 is a reply to message #597275] Wed, 02 October 2013 10:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post results from SQL below

select tablespace_name , extent_management from dba_tablespaces;
icon13.gif  Re: Buffer Busy Wait DBMS_ALERT_INFO [message #597278 is a reply to message #597275] Wed, 02 October 2013 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Would it help to convert the system tablespace from dictionary managed to locally managed?


There should be NO dictionary managed tablespaces in these days.

Re: Buffer Busy Wait DBMS_ALERT_INFO [message #597279 is a reply to message #597278] Wed, 02 October 2013 12:31 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
Thanks for your comment Michel. Added on the to-do list.

select tablespace_name , extent_management from dba_tablespaces;
TABLESPACE_NAME                EXTENT_MAN
------------------------------ ----------
PERFSTAT                       LOCAL
SYSAUX                         LOCAL
SYSTEM                         DICTIONARY
TBS_DGZDBINFO                  LOCAL
TBS_SCOM                       LOCAL
TBS_SPOTLIGHT                  LOCAL
TRCA                           LOCAL
UNI_BO                         LOCAL
UNI_INDEX                      LOCAL
UNI_INDEXC                     LOCAL
UNILABC                        LOCAL
UNILABO                        LOCAL
UNI_LOB                        LOCAL
UNI_TEMP                       LOCAL
UNI_UNDO                       LOCAL
XDB                            LOCAL
icon2.gif  Re: Buffer Busy Wait DBMS_ALERT_INFO [message #597285 is a reply to message #597279] Wed, 02 October 2013 13:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The only solution is then to recreate the database with a LMT SYSTEM tablespace.

Re: Buffer Busy Wait DBMS_ALERT_INFO [message #597288 is a reply to message #597285] Wed, 02 October 2013 13:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>The only solution is then to recreate the database with a LMT SYSTEM tablespace.

ONLY?

http://docs.oracle.com/cd/E16655_01/server.121/e17636/tspaces.htm#ADMIN11393
icon11.gif  Re: Buffer Busy Wait DBMS_ALERT_INFO [message #597295 is a reply to message #597288] Wed, 02 October 2013 14:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Ah! There are some improvements in the package I didn't see... maybe because DBCA still creates a database with a DMT SYSTEM tablespace and many clients have to fix it...

Note there is one more condition in 11.2 than in 10.2: "The SYSAUX tablespace is offline"... I won't be very confident on this procedure. Wink

Re: Buffer Busy Wait DBMS_ALERT_INFO [message #597323 is a reply to message #597295] Thu, 03 October 2013 04:19 Go to previous message
8939513598$
Messages: 103
Registered: July 2013
Location: chennai
Senior Member
sysaux tablespace should be offline so that we can migrate the system tablespace from DMT to LMT using procedure ' DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL '

Previous Topic: Big table joining
Next Topic: Need help to tune this query
Goto Forum:
  


Current Time: Thu Mar 28 17:10:03 CDT 2024