Re: select/delete on IOT becomes slower as more rows get deleted
Date: Wed, 4 Feb 2009 10:07:24 +0000
Message-ID: <OF45D43FA2.43CA8DBD-ON80257553.003633C6-80257553.00379CBC_at_ons.gsi.gov.uk>
Surely the slow down is due to the fast-full scan of the index. If you ignore caching each row you return will need to scan the disk space occupied by all of the previously deleted ones before idenfitying that it is actually the first row remaining in the table effectively causing the time taken to slightly increase for each row accessed. Obviously you will have some caching in place so it likely that you will only notice a slow down for each block of data accessed (probably = db_file_multiblock_read_count * block_size)
Just as an aside I'm not convinced your table isideally suited to be a IOT. I would normally expect the majority of columns in an IOT to form the primary key rather than just 2 out of six as in your case.
Cheers,
Ian
|---------+----------------------------->
| | martin.a.berger_at_gm|
| | ail.com |
| | Sent by: |
| | oracle-l-bounce_at_fr|
| | eelists.org |
| | |
| | |
| | 04/02/2009 08:47 |
| | Please respond to |
| | martin.a.berger |
| | |
|---------+-----------------------------> >--------------------------------------------------------------------------------------------------------------| | | | To: oracle-l_at_freelists.org | | cc: | | Subject: select/delete on IOT becomes slower as more rows get deleted | >--------------------------------------------------------------------------------------------------------------|
Hi List,
again I'm coming with a behaviour I cannot explain to myselve. Maybe
someone can hint my either to an systematic error in my test, my thoughts
or how to explain the behaviour:
I have an index organized table (100000 rows in my testcase)
CREATE TABLE "TEST"."TEST_IOT"
( "INS_DATE" DATE,
"C1" VARCHAR2(40 CHAR),
"C2" VARCHAR2(30 CHAR),
"C3" VARCHAR2(32 CHAR),
"C4" NUMBER(6,0),
"C5" NUMBER(3,0),
CONSTRAINT "PK_TEST_IOT" PRIMARY KEY ("INS_DATE", "C1") ENABLE
) ORGANIZATION INDEX
...
and run
LOOP
...
execute immediate 'SELECT /*+ first_rows(1) */ C1, C2 ,C3, ROWID FROM
TEST_IOT WHERE ROWNUM=1 FOR UPDATE' into IP, freed, net, rid;
execute immediate 'DELETE from TEST_IOT where rowid = :rid' using rid; commit;
- ...
the execution plan looks quite simple for the sql:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 58 | 2 (0)| 00:00:01 | | 1 | FOR UPDATE | | | | | | |* 2 | COUNT STOPKEY | | | | | | | 3 | INDEX FAST FULL SCAN| PK_TEST_IOT | 1 | 58 | 2 (0)|00:00:01 |
the goal of these statements is to grab any row out of the table and delete it (and do something else with the data - not part of hthis striped down testcase). In the future the developer is hoping for some kind of FIFO (based on a column INS_DATE which reflects the insert date), which is a every 1000 rows I grab a timestamp and reportthe difference.
for any reason the performance drops from 00.734 sec per 1000 select/deletes at the beginning to 40.771 sec per 1000 runs at the end.
The test where made on a test-system, no other load there (neither RDBMS,
nor OS)
System: HP BL860C HP-UX 11.31 - 2 CPUs
Load averages: 0.67, 0.65, 0.59
254 processes: 198 sleeping, 55 running, 1 zombie
Cpu states:
CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS
0 1.01 65.2% 0.0% 0.0% 34.8% 0.0% 0.0% 0.0% 0.0%
2 0.32 36.6% 0.0% 1.0% 62.4% 0.0% 0.0% 0.0% 0.0%
--- ---- ----- ----- ----- ----- ----- ----- ----- -----
avg 0.67 51.0% 0.0% 0.4% 48.6% 0.0% 0.0% 0.0% 0.0%
System Page Size: 4Kbytes
Memory: 4108172K (3120300K) real, 8973412K (7622616K) virtual, 36320K free
Page# 1/10
CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU
COMMAND
0 ? 24646 oracle 178 20 1907M 19212K run 61:28 100.62 100.44
oracleAAAT01
RDBMS:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
Can anyone explain why the performance drops that much as the # of rows in
the IOT decreases?
I would expect quite stable performance as every SELECT has to go from the
root-block down to the first leaf (quite same length every time?) and the
DELETE has the same maintanence-work to do everty time? What is the
component I'm blind to see which increases the response time?
the full test-case is available here:
http://berx.at/files/iot_speed_decreases.sql
(to keep the mail shorter) - any further questions welcome.
thank you all in advance,
Martin
-- Martin Berger http://berxblog.blogspot.com http://www.freelists.org/list/oracle-l This email was received from the INTERNET and scanned by the Government Secure Intranet anti-virus service supplied by Cable&Wireless in partnership with MessageLabs. (CCTM Certificate Number 2007/11/0032.) In case of problems, please call your organisation’s IT Helpdesk. Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes. For the latest data on the economy and society consult National Statistics at http://www.statistics.gov.uk ********************************************************************************* Please Note: Incoming and outgoing email messages are routinely monitored for compliance with our policy on the use of electronic communications ********************************************************************************* Legal Disclaimer : Any views expressed by the sender of this message are not necessarily those of the Office for National Statistics ********************************************************************************* The original of this email was scanned for viruses by the Government Secure Intranet virus scanning service supplied by Cable&Wireless in partnership with MessageLabs. (CCTM Certificate Number 2007/11/0032.) On leaving the GSi this email was certified virus free. Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes. †Ûiÿü0ÁúÞzX¬¶Ê+ƒü n– {ú+iÉ^Received on Wed Feb 04 2009 - 04:07:24 CST