|
Replies:
14
-
Pages:
1
-
Last Post:
Jul 3, 2009 6:35 AM
Last Post By: victoria2122
|
|
|
Posts:
6
Registered:
07/03/09
|
|
|
|
Delete Performance on Index Organised Tables
Posted:
Jul 3, 2009 3:16 AM
|
|
|
|
Hello,
We are having a few performance problems with one of our tables.
We have a table (test) which has 9 columns:
A number(10) not null pk,
B number(10),
C number(10),
D number(10),
E number(10),
F varchar2(30),
F varchar2(2),
G varchar2(2),
H varchar2(250),
The table test is an IOT (Index Organised Table) in default IOT configuration.
All of the columns are frequently required so we cannot overflow any.
The table currently has 8m records, which is roughly 1/2 a years worth of data, so not significant.
Inserts and updates are fine, but deleting a single row is taking 40+ seconds!
(delete from test where a = 3043;)
If I convert this table in to a standard table, deletes are only 0.5 of a second?!
Any idea why the delete statement takes an inordinately long time on the IOT, or what I might be doing wrong?
Thanks
Victoria
Oracle Enterprise version 10.2.0.1.0
Oracle XE version 10.2.0.1.0
|
|
|
Posts:
7,642
Registered:
02/20/01
|
|
|
|
Re: Delete Performance on Index Organised Tables
Posted:
Jul 3, 2009 3:23 AM
in response to: victoria2122
|
|
|
|
Can you post the execution plan of Delete statement against IOT?
|
|
|
Posts:
6
Registered:
07/03/09
|
|
|
|
Re: Delete Performance on Index Organised Tables
Posted:
Jul 3, 2009 3:37 AM
in response to: Satish Kandi
|
|
|
operation object_name options cost
Delete_Statement blank blank 2
delete test blank blank
index pk_test_id unique_scan 2
access_predicates blank blank blank
a=3043 blank blank blank
sorry thats a bit of a mess all bunched together. I tried to take a screen shot, but the image is completely black...
Thanks
Victoria
Edited by: user8339226 on Jul 3, 2009 10:38 AM
|
|
|
Posts:
7,642
Registered:
02/20/01
|
|
|
|
Re: Delete Performance on Index Organised Tables
Posted:
Jul 3, 2009 3:41 AM
in response to: victoria2122
|
|
|
|
Can you post the output of
SQL> set linesize 132
SQL> set pagesize 100
SQL> col plan_plus_exp format 132
SQL> set autotrace on
SQL> delete statement...
and copy-paste the contents of sql*plus screen here.
|
|
|
Posts:
6
Registered:
07/03/09
|
|
|
|
Re: Delete Performance on Index Organised Tables
Posted:
Jul 3, 2009 4:43 AM
in response to: Satish Kandi
|
|
|
Well I'm very confused...
I ran the delete statement from sql*plus as you asked on my table and the cost was not huge, but the statistics were in the thousands. I couldn't copy that run to clipboard as it contained information I cannot disclose. I then renamed the table, renamed the columns, renamed the index and ran the test again and deletes are now almost instantaneous!
SQL> delete from test2 where a = 18000003;
1 row deleted.
Execution Plan
Plan hash value: 3314751404
| Id |
Operation |
Name |
Rows |
Bytes |
Cost (%CPU) |
Time |
| 0 |
DELETE STATEMENT |
|
1 |
6 |
2 (0) |
00:00:01 |
| 1 |
DELETE |
TEST2 |
|
|
|
|
| * 2 |
INDEX UNIQUE SCAN |
PK_TEST2_ID |
1 |
6 |
2 (0) |
00:00:01 |
Predicate Information (identified by operation id):
2 - access("A"=18000003)
Statistics
3 recursive calls
1 db block gets
4 consistent gets
0 physical reads
288 redo size
677 bytes sent via SQL*Net to client
568 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed |
|
|
Posts:
7,642
Registered:
02/20/01
|
|
|
|
Re: Delete Performance on Index Organised Tables
Posted:
Jul 3, 2009 4:48 AM
in response to: victoria2122
|
|
|
Well I'm very confused...
I ran the delete statement from sql*plus as you asked on my table and the cost was not huge, but the statistics were in the thousands. I couldn't copy that run to clipboard as it contained information I cannot disclose.
Well you can change the names while posting here. What matters here for diagnosis is the statistics, not the names.
I then renamed the table, renamed the columns, renamed the index and ran the test again and deletes are now almost instantaneous!
Is the issue still there with Original table? |
|
|
Posts:
6
Registered:
07/03/09
|
|
|
|
Re: Delete Performance on Index Organised Tables
Posted:
Jul 3, 2009 5:09 AM
in response to: Satish Kandi
|
|
|
Hi,
Yes the problem is still there. Here is another explain plan on the original table, names altered.
Thank you
Victoria
SQL> delete from test where a = 5555;
1 row deleted.
Execution Plan
Plan hash value: 3835166932
| Id |
Operation |
Name |
Rows |
Bytes |
Cost (%CPU) |
Time |
| 0 |
DELETE STATEMENT |
|
1 |
72 |
1 (0) |
00:00:01 |
| 1 |
DELETE |
test |
|
|
|
|
| * 2 |
INDEX UNIQUE SCAN |
PK_test_id |
1 |
72 |
1 (0) |
00:00:01 |
Predicate Information (identified by operation id):
2 - access("a"=5555)
Statistics
61 recursive calls
69 db block gets
70365 consistent gets
66776 physical reads
1200 redo size
671 bytes sent via SQL*Net to client
571 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed |
|
|
Posts:
7,642
Registered:
02/20/01
|
|
|
|
Re: Delete Performance on Index Organised Tables
Posted:
Jul 3, 2009 5:11 AM
in response to: victoria2122
|
|
|
Statistics
61 recursive calls
69 db block gets
70365 consistent gets
66776 physical reads
1200 redo size
671 bytes sent via SQL*Net to client
571 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
There seems to be trigger(s) defined on this table for DELETE. Have you checked the trigger code? |
|
|
Posts:
6
Registered:
07/03/09
|
|
|
|
Re: Delete Performance on Index Organised Tables
Posted:
Jul 3, 2009 5:20 AM
in response to: victoria2122
|
|
|
Odd,
I have 5 triggers in the whole database and they are on different tables and are only insert or update triggers. I dropped all 5 triggers and got this:
SQL> delete from test where a = 6655;
1 row deleted.
Execution Plan
Plan hash value: 3835166932
| Id |
Operation |
Name |
Rows |
Bytes |
Cost (%CPU) |
Time |
| 0 |
DELETE STATEMENT |
|
1 |
25 |
2 (0) |
00:00:01 |
| 1 |
DELETE |
test |
|
|
|
|
| * 2 |
INDEX UNIQUE SCAN |
PK_test_id |
1 |
25 |
2 (0) |
00:00:01 |
Predicate Information (identified by operation id):
2 - access("a"=6655)
Statistics
61 recursive calls
69 db block gets
70365 consistent gets
67067 physical reads
1244 redo size
676 bytes sent via SQL*Net to client
575 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed |
|
|
Posts:
482
Registered:
12/13/99
|
|
|
|
Re: Delete Performance on Index Organised Tables
Posted:
Jul 3, 2009 5:36 AM
in response to: victoria2122
|
 |
Correct |
|
|
This appears to be as if the PK on this IOT table is being referenced by a FK on a (somewhat large) child table but the FK does not have an associated index.
By deleting a row from this table, Oracle is forced to perform a FTS on the child table to ensure there are no matching FK.
Find out if you do indeed have a FK that is referencing this table and if the FK is indexed.
Just a guess of course. An extended trace during the delete operation should highlight where the PIOs are coming from just to be sure.
Cheers
Richard Foote
http://richardfoote.wordpress.com/ |
|
|
Posts:
7,642
Registered:
02/20/01
|
|
|
|
Re: Delete Performance on Index Organised Tables
Posted:
Jul 3, 2009 5:39 AM
in response to: victoria2122
|
 |
Helpful |
|
|
|
There is a change in the COST but you have not mentioned the time taken.
Also, are there any child tables for this IOT? Are the constraining columns indexed in child tables?
|
|
|
Posts:
1,781
Registered:
01/23/07
|
|
|
|
Re: Delete Performance on Index Organised Tables
Posted:
Jul 3, 2009 5:59 AM
in response to: Richard Foote
|
|
Helpful |
|
|
This appears to be as if the PK on this IOT table is being referenced by a FK on a (somewhat large) child table but the FK does not have an associated index.
Or possibly even 10 child tables given the number of recursive calls.
This would help to explain why copying just the table "solved" the problem. But I am puzzled by the suggestion that renaming columns also addressed the problem (unless that was on a copy of the IOT) - the FKs would still apply.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk
To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {code} (lowercase, curly brackets, no spaces) so that the text appears in fixed format
.
"Science is more than a body of knowledge; it is a way of thinking"
Carl Sagan |
|
|
Posts:
482
Registered:
12/13/99
|
|
|
|
Re: Delete Performance on Index Organised Tables
Posted:
Jul 3, 2009 6:06 AM
in response to: Jonathan Lewis
|
|
|
Hi Jonathan
Indeed 10 such child tables would do it as well 
Yes, if just renaming the column fixed the problem, then I'm not sure what is really going on.
Cheers
Richard Foote
http://richardfoote.wordpress.com/ |
|
|
Posts:
754
Registered:
01/27/08
|
|
|
|
Re: Delete Performance on Index Organised Tables
Posted:
Jul 3, 2009 6:34 AM
in response to: Jonathan Lewis
|
|
|
|
I am just throwing this out there to see if it provokes any other thoughts. Is it likely that a child table (or maybe 10 child tables) are also index organized tables with indexes on the foreign key columns? Would that possibly lead to the number of consistent gets reported? One of the paraphrases from "Practical Oracle 8i" that I added to my notes is this one:
"It is possible to create secondary indexes on index organized tables. The secondary index points to the UROWID (universal logical) which holds the best guess of the block containing the index organized table data, which gradually becomes less accurate."
A theoretical question: Could it be that the UROWIDs in the secondary indexes have become less accurate over time, and by changing the column name Oracle has re-evaluated the UROWIDs? Does Oracle behave in this manner?
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
|
|
|
Posts:
6
Registered:
07/03/09
|
|
|
|
Re: Delete Performance on Index Organised Tables
Posted:
Jul 3, 2009 6:35 AM
in response to: Richard Foote
|
|
|
Hi Everyone,
thank you so much for all your respective comments. We do indeed have a lot of referencing tables - 12 to be precise. And there are two foreign key relations to the test table itself. One of those foreign keys that links back on itself was not indexed. Indexing that column has improved the speed dramatically from 30 seconds to 0.5 seconds.
Thank you
Victoria
The explain plan after that new index looks like:
SQL> delete from test where a = 3242;
1 row deleted.
Execution Plan
Plan hash value: 3835166932
| Id |
Operation |
Name |
Rows |
Bytes |
Cost (%CPU) |
Time |
| 0 |
DELETE STATEMENT |
|
1 |
25 |
2 (0) |
00:00:01 |
| 1 |
DELETE |
test |
|
|
|
|
| * 2 |
INDEX UNIQUE SCAN |
PK_test_id |
1 |
25 |
2 (0) |
00:00:01 |
Predicate Information (identified by operation id):
2 - access("a"=3242)
Statistics
718 recursive calls
57 db block gets
283 consistent gets
6 physical reads
1676 redo size
681 bytes sent via SQL*Net to client
571 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
16 sorts (memory)
0 sorts (disk)
1 rows processed |
|
|
|
Legend
|
|
Guru : 2500
- 1000000
pts
|
|
Expert : 1000
- 2499
pts
|
|
Pro : 500
- 999
pts
|
|
Journeyman : 200
- 499
pts
|
|
Newbie : 0
- 199
pts
|
|
Oracle ACE Director
|
|
Oracle ACE Member
|
|
Oracle Employee ACE
|
|
|
Helpful Answer
(5 pts)
|
|
Correct Answer
(10 pts)
|
|
