IOT secondary indexes SLOW performance [message #387037] |
Tue, 17 February 2009 12:26 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sky_lt
Messages: 28 Registered: February 2009 Location: vilnius
|
Junior Member |
|
|
Hi all,
I created not compressed IOT-table of 250 million rows with no OWERFLOW segment
I created secondary index, altered it updating its block references
Now i have that it takes about 12 minutes to get rows using that index on IOT-table
And it takes about 14 seconds in regular heap-table to get same rows using the same index
In documnetation Oracle states: that secondary indexes on IOT table
are similar to regular indexes on performance.
Does anyone know what can be done to speed up the performance?
Thanks for answers.
Sky_lt
|
|
|
|
|
|
Re: IOT secondary indexes SLOW performance [message #387066 is a reply to message #387043] |
Tue, 17 February 2009 16:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Quote: |
Would you like some cheese with your whine?
|
At no point in what the OP has said, does it come across that they are whining. Totally uncalled for. What exactly is it that drives you to 'talk' to people in the way that you do? I remember your posts prior to you becoming BlackSwan and even though most of them were quite brusque, at least a high percentage of them were helpful (I know that I learned a lot from you). It seems that all of your posts are now pretty much you being obnoxious. Why?
|
|
|
|
|
Re: IOT secondary indexes SLOW performance [message #387233 is a reply to message #387183] |
Wed, 18 February 2009 06:24 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sky_lt
Messages: 28 Registered: February 2009 Location: vilnius
|
Junior Member |
|
|
Hi all,
Sorry for unclear post.
The IOT i created:
CREATE TABLE logs2(
credit_evt_credit_code, --NUMBER
credit_evt_code, --NUMBER
credit_evt_serv_mnemo, --VARCHAR2(30)
credit_evt_meth_mnemo, --VARCHAR2(30)
credit_evt_base_evt_code, --NUMBER
credit_evt_pl_code, --NUMBER
credit_evt_pl_inc_number, --NUMBER(4)
credit_evt_trns_dt, --DATE
credit_evt_trns_code, --NUMBER
credit_evt_trns_amount, --NUMBER13,2)
credit_evt_amount, --NUMBER(19,6)
credit_evt_param, --VARCHAR2(1)
credit_evt_dt, --DATE
credit_evt_user, --VARCHAR2(30)
credit_evt_sys_dt, --DATE
CONSTRAINT pk_logs PRIMARY KEY (credit_evt_credit_code, credit_evt_serv_mnemo, credit_evt_code))
ORGANIZATION INDEX PARALLEL (DEGREE 4)
NOLOGGING
TABLESPACE X1
PCTTHRESHOLD 10
INCLUDING credit_evt_param
OVERFLOW TABLESPACE X2
AS SELECT * FROM logs2
I takes about 22 hours to create it for Oracle (328 million rows). ORACLE 9.2.0.7.0 - 64bit Production (HPUX)
Tablespace Datablock size 8k, average row length 85 bytes
About Owerflow i mentioned that no data is in it - as columns
are not lengthy in data.
Currently i am building this table again only with compressed option. As soon as it finishes i will post the rezult and execution plan for queries using secondary indexes.
Thanks for comments
|
|
|
|
Re: IOT secondary indexes SLOW performance [message #389254 is a reply to message #388211] |
Fri, 27 February 2009 17:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sky_lt
Messages: 28 Registered: February 2009 Location: vilnius
|
Junior Member |
|
|
Hi all,
Can i determine the problem of secondary index slow performance from TKPROF output:
1. Selecting using secondary index from compressed(degree 2) IOT:
********************************************************************************
SELECT *
FROM logs2
WHERE credit_evt_trns_dt = '2008.12.12'
AND credit_evt_serv_mnemo = 'ACCUM_INTR'
ORDER BY credit_evt_code
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 11.74 413.40 296578 295914 43 501
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 11.74 413.42 296578 295914 43 501
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 8 (TESTER)
Rows Row Source Operation
------- ---------------------------------------------------
501 SORT ORDER BY
147585 INDEX UNIQUE SCAN PK_S_CREDIT_EVENTS2 (object id 951975)
147585 INDEX RANGE SCAN IOT_EVENT_DTIDX2 (object id 952430)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: RULE
501 SORT (ORDER BY)
147585 INDEX (UNIQUE SCAN) OF 'PK_S_CREDIT_EVENTS2' (UNIQUE)
147585 INDEX (RANGE SCAN) OF 'IOT_EVENT_DTIDX2' (NON-UNIQUE)
********************************************************************************
2. Selecting using secondary index from non compressed IOT:
********************************************************************************
SELECT*
FROM logs3
WHERE credit_evt_trns_dt = '2008.12.12'
AND credit_evt_serv_mnemo = 'ACCUM_INTR'
ORDER BY credit_evt_code
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 5.72 73.01 296705 295908 43 501
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 5.72 73.02 296705 295908 43 501
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 8 (TESTER)
Rows Row Source Operation
------- ---------------------------------------------------
501 SORT ORDER BY
147585 INDEX UNIQUE SCAN PK_S_CREDIT_EVENTS3 (object id 952002)
147585 INDEX RANGE SCAN IOT_EVENT_DTIDX3 (object id 952431)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: RULE
501 SORT (ORDER BY)
147585 INDEX (UNIQUE SCAN) OF 'PK_S_CREDIT_EVENTS3' (UNIQUE)
147585 INDEX (RANGE SCAN) OF 'IOT_EVENT_DTIDX3' (NON-UNIQUE)
********************************************************************************
3. Selecting using index from HEAP TABLE:
********************************************************************************
SELECT *
FROM logs22
WHERE credit_evt_trns_dt = '2008.12.12'
AND credit_evt_serv_mnemo = 'ACCUM_INTR'
ORDER BY credit_evt_code
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 3 0 12 0
Fetch 2 0.70 3.97 3904 35958 43 501
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.70 3.98 3907 35958 55 501
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 8 (TESTER)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: RULE
0 SORT (ORDER BY)
0 FILTER
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'S_CREDIT_EVENTS'
0 INDEX (RANGE SCAN) OF 'EVENT_DTIDX' (NON-UNIQUE)
********************************************************************************
I see the disk usage is 10 times greater when getting rows from secondary index of IOT table comparing to HEAP table query.
What are the possible reasons for that?
[Updated on: Sat, 28 February 2009 04:05] Report message to a moderator
|
|
|
Re: IOT secondary indexes SLOW performance [message #389345 is a reply to message #389254] |
Sun, 01 March 2009 00:23 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
300,000 blocks and 300,000 buffers read for the benefit of 150,000 rows. That's 4 reads per row!!!! That's huge.
OK, I'm guessing here, but I read that secondary indexes on IOTs store APPROXIMATE rowids, not physical rowids. When it looks up the approximate rowid, it then works out whether the row is forward or backward in the IOT, then goes to look for it.
With long and varying row lengths, the ROWID guesses will become much less accurate and take more IO to find the real row.
If this is your problem, the best way to deal with it would be to create an overflow segment containing most of the columns - especially those with varying length. This will make the guesses more accurate and hopefully reduce IO.
Ross Leishman
|
|
|
Re: IOT secondary indexes SLOW performance [message #389577 is a reply to message #389345] |
Mon, 02 March 2009 13:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sky_lt
Messages: 28 Registered: February 2009 Location: vilnius
|
Junior Member |
|
|
rleishman,
thanks for suggestion i will try to move columns to overflow and check the performance.
It is strange for me those "missed gueses" as before querying the IOT by secondary index i updated secondary index block references.
But in this case i think i will lose performance in querying table by primary key as the columns will be moved to overflow and will be referenced by ROWID. So getting the row by using primary key index will require on more I/O (to get data from overflow tablespace).
Sky_lt
[Updated on: Mon, 02 March 2009 13:11] Report message to a moderator
|
|
|
|
|
Re: IOT secondary indexes SLOW performance [message #389688 is a reply to message #389664] |
Tue, 03 March 2009 03:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
sky_lt wrote on Tue, 03 March 2009 07:13 | Besides our DB is running using RBO and as my DBA said: "partitioning works poor when using RBO" (currently we do not have any posilbility to migrate to CBO)
|
That probably has something to do with the problem.
RBO doesn't work with IOT's, it doesn't know what to do with them. If you've got a query that accesses one oracle uses the CBO.
So you need to either start using the CBO properly (gather stats in other words) or stop using IOT's.
|
|
|
Re: IOT secondary indexes SLOW performance [message #389734 is a reply to message #389688] |
Tue, 03 March 2009 07:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sky_lt
Messages: 28 Registered: February 2009 Location: vilnius
|
Junior Member |
|
|
cookiemonster,
IOt's were introduced in Oracle 8.0 i doubt that then anyone was using CBO - i guess oracle did not have CBO then at all.
Of course i can gather IOT statistics and try to query it using CBO in session, but personally i do not think that RBO is is the reason for slow secondary indexes. (In the query i pasted oracle goes through indexes)
|
|
|
Re: IOT secondary indexes SLOW performance [message #389764 is a reply to message #389734] |
Tue, 03 March 2009 08:40 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
sky_lt wrote on Tue, 03 March 2009 13:26 |
IOt's were introduced in Oracle 8.0 i doubt that then anyone was using CBO - i guess oracle did not have CBO then at all.
|
Wrong - CBO was introduced in Oracle 8.
Quote: |
Of course i can gather IOT statistics and try to query it using CBO in session
|
It's not a case of trying to use the CBO - you are!
Here's an example from an 8i DB:
Connected to:
Oracle8i Release 8.1.7.4.0 - Production
JServer Release 8.1.7.4.0 - Production
SQL> CREATE TABLE iot_test
2 (a NUMBER NOT NULL,
3 b NUMBER NOT NULL,
4 CONSTRAINT iot_test_PK PRIMARY KEY (a)
5 )
6 ORGANIZATION INDEX;
Table created.
SQL> BEGIN
2
3 FOR n IN 1..100000 LOOP
4
5 INSERT INTO iot_test(a,b)
6 VALUES (n , n +1);
7
8 END LOOP;
9
10 COMMIT;
11
12 END;
13 /
PL/SQL procedure successfully completed.
SQL> CREATE TABLE non_iot_test AS SELECT * FROM iot_test;
Table created.
SQL> ALTER TABLE non_iot_test ADD CONSTRAINT non_iot_test_pk PRIMARY KEY (a);
Table altered.
SQL> set autotrace traceonly explain
SQL> alter session set optimizer_goal=rule;
Session altered.
SQL> SELECT b FROM iot_test WHERE a=12;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=1 Card=1 Bytes=26)
1 0 INDEX (UNIQUE SCAN) OF 'IOT_TEST_PK' (UNIQUE) (Cost=1 Card=1 Bytes=26)
SQL> SELECT b FROM non_iot_test WHERE a=12;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'NON_IOT_TEST'
2 1 INDEX (UNIQUE SCAN) OF 'NON_IOT_TEST_PK' (UNIQUE)
SQL>
Notice how the explain plan for the IOT has card/cost?
You only get those from the CBO.
The second plan is from the RBO.
Quote: |
but personally i do not think that RBO is is the reason for slow secondary indexes. (In the query i pasted oracle goes through indexes)
|
You might be right here, but you've still got a big issue if your using IOTs and the RBO.
I assume your tables have no statistics.
So what happens when you write a query that links an IOT to a normal table? - it uses the CBO and makes a complete mess of it because it doesn't sufficient information to calculate a decent plan.
|
|
|
Re: IOT secondary indexes SLOW performance [message #389872 is a reply to message #389764] |
Tue, 03 March 2009 20:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
cookiemonster wrote on Wed, 04 March 2009 01:40 | Wrong - CBO was introduced in Oracle 8.
|
Wrong. CBO was introduced in Oracle 7 and was pretty stable by 7.3. Oracle 8 introduced partitioning, which set the CBO back some because of some bad plans on partitioned tables.
The point is, if you are a DBA advocating RBO on a post 8i database, you should be sacked.
Ross Leishman
|
|
|
|
Re: IOT secondary indexes SLOW performance [message #390137 is a reply to message #389932] |
Thu, 05 March 2009 02:28 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sky_lt
Messages: 28 Registered: February 2009 Location: vilnius
|
Junior Member |
|
|
Hi all,
another try to speed up...
ANALYZE TABLE logs2 COMPUTE STATISTICS;
ALTER SESSION set OPTIMIZER_GOAL=ALL_ROWS;
CREATE TABLE iot_test_speed NOLOGGING AS
SELECT /*+ALL_ROWS*/*
FROM logs2
WHERE credit_evt_trns_dt = '2008.12.12'
AND credit_evt_serv_mnemo = 'ACCUM_INTR'
ORDER BY credit_evt_code
Execution time: 11 minutes.
Autotrace:
Description VALUE
recursive calls 2008
db BLOCK gets 1418
CONSISTENT gets 887088
PHYSICAL READS 324277
redo SIZE 143912
bytes sent via SQL*Net TO client 52
bytes received via SQL*Net FROM client 256
SQL*Net roundtrips TO/FROM client 1
sorts (MEMORY) 0
sorts (DISK) 1
With respect to Oracle IOT's - it seems that secondary indexes suck. Oracle is slow with using them.
As the issue is on IOT secondary indexes performance - here is no difference if you are using RBO or CBO fro that single query.
|
|
|
Re: IOT secondary indexes SLOW performance [message #390141 is a reply to message #390137] |
Thu, 05 March 2009 02:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/129340.jpg) |
Alessandro Rossi
Messages: 166 Registered: September 2008 Location: Rome
|
Senior Member |
|
|
Quote: |
With respect to Oracle IOT's - it seems that secondary indexes suck. Oracle is slow with using them.
As the issue is on IOT secondary indexes performance - here is no difference if you are using RBO or CBO fro that single query.
|
From the beginning of the thread you just supported your own position and you didn't want to say what are using it for to look for alternatives.
Quote: | IOTs suck and that's all!!
|
In cases like this there are many more things to consider before saying the indexes on IOTs are not performing fine, like an application design that may suck more than anything on this earth.
I don't know what are you doing with it and what are you doing with the table but my guess is that it can be done (you can't even imagine how many times) better with a good process design!
Just a question now, did you check the clustering factor of that index?
Bye Alessandro
[Updated on: Thu, 05 March 2009 02:45] Report message to a moderator
|
|
|
|
Re: IOT secondary indexes SLOW performance [message #390397 is a reply to message #387037] |
Fri, 06 March 2009 03:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Or go with your original suggestion of partioning the table.
After all the DBA's stated reason for not using partitioning - that it doesn't work with the RBO - is equally true of IOT's and so is rather redundant.
|
|
|
Re: IOT secondary indexes SLOW performance [message #390424 is a reply to message #390362] |
Fri, 06 March 2009 05:19 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
sky_lt
Messages: 28 Registered: February 2009 Location: vilnius
|
Junior Member |
|
|
Hi all,
Our system is using this table very widely. It is included in reports, forms, jobs, queries ... i agree that the architecture is poor, but i can not easily change it.
Of course the problem with secondary indexes on IOT's is the clustering factor:
Clustering factors:
INDEX_____________________________IOT_________HEAP
_IOT_EVENT_DTIDX2(secondary)____248027292___10000467
_PK_S_CREDIT_EVENTS2(primary)___2786571______290365300
It is pity that Oracle states: that secondary indexes perform similar to regular indexes.
Thanks everyone for answers i will consider partitioning and materialized view creation.
Sky_lt
|
|
|