Hi Sir,
Actually I am doing performance tuning to my database as it was
complained slow. So I am doing all possible operations.
The following are wait events after deleting some records from one
table.
select SUBSTR(event, 0, 30) , wait_time, seconds_in_wait,state from
V$SESSION_WA
SUBSTR(EVENT,0,30) WAIT_TIME SECONDS_IN_WAIT STATE
log buffer space | 0 | 0 | WAITING
log buffer space | 0 | 19 | WAITING
log file switch completion | 0 | 0 | WAITING
log buffer space | 0 | 1 | WAITING
log buffer space | 0 | 1 | WAITING
log buffer space | 0 | 0 | WAITING
db file sequential read | 1 | 0 | WAITED KNOWN TIME
log file switch completion | 0 | 0 | WAITING
log buffer space | 0 | 0 | WAITING
log buffer space | 59 | 8 | WAITED KNOWN TIME
db file scattered read | 2 | 0 | WAITED KNOWN TIME
log buffer space | 0 | 0 | WAITING
log buffer space | 0 | 8 | WAITING
log file switch completion | 0 | 0 | WAITING
log file switch completion | 29 | 9 | WAITED KNOWN TIME
SQL*Net message from client | 0 |1 | WAITING
SQL> delete from kill_ load;
273848 rows deleted.
Elapsed: 00:01:57.01
Can some body tell what's the problem in wait events, What should I do
to bring elapsed time to less than one minute.
Regs,
Sree
-----Original Message-----
From: henry Wollman
[mailto:oracledba-ezmlmshield-x64234956.[Email address protected]
Sent: Thursday, December 29, 2005 11:36 PM
To: LazyDBA Discussion
Subject: RE: archive log stop: rollback:
There is a difference between disabling autoarchiving and running in
noarchivelog mode.
automatic archiving is active if the init.ora parameter
log_archive_start is
true. This is not dynamic and can only be changed by bouncing the DB.
In 9i, at least, this can be set to true with the database in noarchive
mode, and everything is OK. If the database is in archivelog mode, then
redo
logs are automatically archived.
If this is set to false and the db is in archivelog mode, then the
command:
alter system archive log
must be manually run whenever a redo log is filled up, or eventually the
db
will come to a stop when it can no longer switch logs.
In mount mode, alter database archivelog; will enable archive log mode,
alter database noarchivelog; will disable archive log mode.
Enabling archive log mode with log_archive_start=false, is not a good
idea.
I recently had a database on which autoachiving was accidently disabled,
without my knowledge. The redo logs filled up, and I wasn't able to get
to
it right away, and actually thought the problem was just the archive log
space was filled up, which I have a process that monitors this and
removes
them as needed. At any rate, by the time I realized the problem, I could
not
start a manual archive log process. There wasn't enough room left in the
redo log to start it. I temporarily added another redo log and then
archived
everything and got auto archiving reenabled.
| Henry A.L. Wollman Rejoice and be glad!
|_____________________________________________________
| Moody Bible Institute, Database Administrator, [Email address
protected]
| 820 N. LaSalle Blvd, Chicago, IL 60610 312-329-2291
|_____________________________________________________
-----Original Message-----
From: Shergill Gurmohan
[mailto:oracledba-ezmlmshield-x45722519.[Email address protected]
Sent: Thursday, December 29, 2005 11:33 AM
To: LazyDBA Discussion
Subject: RE: archive log stop: rollback:
Theoretically when archiving is disabled the archives shouln't need to
be
archived but be able to write over themselve. However, I have observed
in 8i
that even after you have disabled archiving by issuing a alter system
call
yet the database remain in a confused state about whether to archive or
not
until you also issue the command alter database noarchive [in mount
stage].
If you are stuck after doing the first step all you need is to issue a
manual archive all statement and get out of the hung mode. Then go issue
the
second command. Please note you will have to log in as
svrmgrl
SVRMGRL>connect internal
or
sqlplus /nolog
SQLPLUS>Connect sys as sysdba
depending upon if you are in 8i or 9i
Gurmohan
www.onlymath.com - Place to learn and teach math.
-----Original Message-----
From: henry Wollman
[mailto:oracledba-ezmlmshield-x65397234.[Email address protected]
Sent: Thursday, December 29, 2005 12:11 PM
To: LazyDBA Discussion
Subject: RE: archive log stop: rollback:
If automatic archiving is disabled, then when all your redo logs fill
up,
the database will stop because it cannot switch the log until an archive
is
complete (which must be manually started since the auto archiving was
disabled).
I believe this is what happened. The problem is not with rollback, the
problem is the database has no more redo logs and can't archive.
| Henry A.L. Wollman Rejoice and be glad!
|_____________________________________________________
| Moody Bible Institute, Database Administrator, [Email address
protected]
| 820 N. LaSalle Blvd, Chicago, IL 60610 312-329-2291
|_____________________________________________________
-----Original Message-----
From: Sreedhar_Bhaskararaju
[mailto:oracledba-ezmlmshield-x34403964.[Email address protected]
Sent: Thursday, December 29, 2005 10:01 AM
To: LazyDBA Discussion
Subject: archive log stop: rollback:
I disabled the automatic archive.
Delete data from table
SQL> delete from kill_load;
273848 rows deleted.
Elapsed: 00:02:04.01
SQL>
SQL > rollback
Which gave the following wait events
log file switch (archiving nee |0| 3| WAITING
log file switch (archiving nee |0| 9| WAITING
...
I tried to kill the sql process. Then database has hanged. I found the
following error in alert_log
"PMON failed to delete process, see PMON trace file",
Can some body tell what is the problem? Is there any problem in rollback
while automatic archive is disabled.
Regards,
Sree
DISCLAIMER:
This email (including any attachments) is intended for the sole use of
the
intended recipient/s and may contain material that is CONFIDENTIAL AND
PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying
or
distribution or forwarding of any or all of the contents in this message
is
STRICTLY PROHIBITED. If you are not the intended recipient, please
contact
the sender by email and delete all copies; your cooperation in this
regard
is appreciated.
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
DISCLAIMER:
This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated.
Oracle LazyDBA home page