OraFAQ Forum: Performance Tuning » Buffer Busy Wait DBMS_ALERT

Home » RDBMS Server » Performance Tuning » Buffer Busy Wait DBMS_ALERT_INFO (Oracle DB 11.2.0.3, Standard 64-bit, Windows 2008 R2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Buffer Busy Wait DBMS_ALERT_INFO [message #597275]

Wed, 02 October 2013 09:57

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?

Report message to a moderator

Re: Buffer Busy Wait DBMS_ALERT_INFO [message #597277 is a reply to message #597275]

Wed, 02 October 2013 10:20

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post results from SQL below

select tablespace_name , extent_management from dba_tablespaces;

Report message to a moderator

Re: Buffer Busy Wait DBMS_ALERT_INFO [message #597278 is a reply to message #597275]

Wed, 02 October 2013 11:01

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Buffer Busy Wait DBMS_ALERT_INFO [message #597279 is a reply to message #597278]

Wed, 02 October 2013 12:31

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

Report message to a moderator

Re: Buffer Busy Wait DBMS_ALERT_INFO [message #597285 is a reply to message #597279]

Wed, 02 October 2013 13:40

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

Re: Buffer Busy Wait DBMS_ALERT_INFO [message #597288 is a reply to message #597285]

Wed, 02 October 2013 13:56

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

Report message to a moderator

Re: Buffer Busy Wait DBMS_ALERT_INFO [message #597295 is a reply to message #597288]

Wed, 02 October 2013 14:32

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Buffer Busy Wait DBMS_ALERT_INFO [message #597323 is a reply to message #597295]

Thu, 03 October 2013 04:19

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 '

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 13:56:25 CST 2025