Thank you very much, I will put your suggestion into a notes so when I have chance I will give it a try
as your said,
it's very weird do we like for this.
this segments size just only 524288 bytes, as our prior experience, this is a small table,
no matter how so slow is abnormal via delete statement.
my opinion on this case as following:
1) create a new table as select * from old_table
2) alter table table_name shrink cascade;
3) alter table table_name move new_tablespace or rebuild index based on the table;
4) check your datafile(filesystem? raw device? or asm?), diag performance via os tools.
may be I/O performance is the matter mostly.
5) observe the wait event which are not familiar with you,eg:lms flush message acks,
occasionally, probably you encounter an Oracle bug To occur by chance.
6) adjust DB_FILE_MULTIBLOCK_READ_COUNT parameter to 128 temporary at session level before your delete
statement executed by "db file scattered read"
7) analyze dump process state
if issue occur again,you can dump the process state by oradebug as following:
SQL> oradebug setospid PID
SQL> oradebug unlimit
SQL> oradebug dump processstate 10;
Or set 10046 event by ORADEBUG
SQL> oradebug setospid pid
SQL> oradebug unlimit
SQL> oradebug eventb 10046 trace name context forever,level 12;
SQL> oradebug event 10046 trace name context off;
by the trace file,you will get more helpful information.
8) Perform a Healthcheck on the Database by your regulation.
9) analyze the system performance via 10g new feature(ADDM,AWR,ASH,STA...)
...
Hi Thomas,
Thank you for replying my post and I enjoy reading your blog.
As this is customer database and I can only provide as much as information as it is available
1. The database is 10G (10.2.0.1)
2. Database machine is HP-UX Parc 11i
3. This was taken from the replica of this customer DB (two month older, but this table does not change quite often)
OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS
------------------------------ -------------------- ---------- ---------- ----------
Some_user THIS_WERID_TABLE 524288 64 1
4. I monitored v$session_wait while monitoring them to do the delete, I can recall the deleting session would keep waiting for the following
User I/O
db file scattered read
5. Correct, the delete would go on for hours. The strange thing was they exported this table and I loaded into my schema, the same delete would take less than 1 seconds to finish (all the to_be_deleted rows were deleted).
No other sessions holding lock on this table while the delete was going on
6. I could not check the alert log file as the people worked with me had no access to the Oracle host machine
7. Triggers were disabled, no M-Views on this table, some packages and referential constraints depend on this table
8. I believe it would repeat, but as the problem solved, no-one except me want to pursue it any more
I may not get exact cause of this problem, you could just give me some possible causes.
Thank you very much
Hi,New_driver:
first,thanks for your visit to my blog.
As your said, I need you privode more information as following:
1) version
select * from v$version;
2) database
select * from v$database;
3) segment
select owner,segment_name,bytes,blocks,extents from dba_segments where segment_name=upper('this_weird_table');
4) wait event
session A:
select sid from v$mystat where rownum=1;
delele this_weird_table where id=22;
session B:
selelct event from v$session_wait/v$session where sid=&sid
5) just hang by dml on this table?
6) is there any error in alert log file?
7) is there any trigger or materialized view or other objects based on this table?
8) if the same situation can repeat, pls make a 10046 event and send mail to toms_zrp@hotmail.com
Hi Thomas,
Seek your expert advise on one of the problem I faced sometime ago.
1. A production DB's one table had some unwanted data, the table was small, there were four blocks for this table segments
2. delele this_weird_table where id=22;
would do the job of removing these unwanted data
3. The sqlplus session would not return
4. There was no lock holding on this_weird_table by any other sessions
5. The deleting session kep waiting on "User I/O"
6. Using hint to use full table scan would not work either
7. There were totally 1100 rows in this table, the delete would remove 505 rows
8. Eventually I had to rename this to workaround
SQL> rename this_weird_table to this_weird_table_orig;
SQL> create table this_weird_table as select * from this_weird_table_orig where id != 22;
Appreciate if you could provide some suggestions, I suspect blocks were corrupted somehow
|
