Home » RDBMS Server » Performance Tuning » IOT secondary indexes SLOW performance (9i, hp-ux)
IOT secondary indexes SLOW performance [message #387037] Tue, 17 February 2009 12:26 Go to next message
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 #387041 is a reply to message #387037] Tue, 17 February 2009 12:40 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Does anyone know what can be done to speed up the performance?
Create table as a heap table.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you

[Updated on: Tue, 17 February 2009 12:41]

Report message to a moderator

Re: IOT secondary indexes SLOW performance [message #387042 is a reply to message #387041] Tue, 17 February 2009 12:54 Go to previous messageGo to next message
sky_lt
Messages: 28
Registered: February 2009
Location: vilnius
Junior Member
I need that table to be IOT-table:
70% of queries use primary key of IOT(performance is perfect here)

but the rest 30% of queries go through secondary indexes
and here i get the bottleneck.

[Updated on: Tue, 17 February 2009 12:57]

Report message to a moderator

Re: IOT secondary indexes SLOW performance [message #387043 is a reply to message #387037] Tue, 17 February 2009 13:04 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You have table. We don't.
You have data. We don't.
You have SQL. We don't.
You have (or can have) EXPLAIN PLAN. We Don't.
You have (or can have) results from SQL_TRACE. We don't.

Would you like some cheese with your whine?

Why do you expect useful answers when you've provided no meaningful substance?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you
Re: IOT secondary indexes SLOW performance [message #387066 is a reply to message #387043] Tue, 17 February 2009 16:51 Go to previous messageGo to next message
pablolee
Messages: 2836
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 #387093 is a reply to message #387066] Tue, 17 February 2009 22:06 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Post the Explain Plan of both queries

Ross Leishman
Re: IOT secondary indexes SLOW performance [message #387183 is a reply to message #387066] Wed, 18 February 2009 03:36 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
[quote title=pablolee wrote on Tue, 17 February 2009 23:51]
Quote:

What exactly is it that drives you to 'talk' to people in the way that you do?


I don't see that too offensive, it's just an ironic statement.

Anyway the OP asked the question without the needed information to have a good answer and probably he's excepting an answer that supports his idea.

But he said he's using an IOT without overflow segment ( very horrible concept usually ). Then he didn't say how many columns the table contains what is the meaning of those columns, what are the kinds of queries executed on table, how is the primary key composed and any other sort of useful information.

So what's so wrong on pointing the OP to be more detailed and to not except at any cost the answer he wants in a pronounced form?


Bye Alessandro
Re: IOT secondary indexes SLOW performance [message #387233 is a reply to message #387183] Wed, 18 February 2009 06:24 Go to previous messageGo to next message
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 #388211 is a reply to message #387093] Mon, 23 February 2009 19:12 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
rleishman wrote on Wed, 18 February 2009 15:06
Post the Explain Plan of both queries

Ross Leishman



Ross Leishman
Re: IOT secondary indexes SLOW performance [message #389254 is a reply to message #388211] Fri, 27 February 2009 17:06 Go to previous messageGo to next message
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 messageGo to next message
rleishman
Messages: 3724
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 messageGo to next message
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 #389655 is a reply to message #387037] Tue, 03 March 2009 00:50 Go to previous messageGo to next message
alexzeng
Messages: 133
Registered: August 2005
Location: alexzeng.wordpress.com
Senior Member
hi,

Another thought, as there are 328 million rows, I think you can partition this table by credit_evt_trns_dt.

Regards,
Alex
Re: IOT secondary indexes SLOW performance [message #389664 is a reply to message #389655] Tue, 03 March 2009 01:13 Go to previous messageGo to next message
sky_lt
Messages: 28
Registered: February 2009
Location: vilnius
Junior Member
I thought about partitioning, but the problem is in the architecture of that table: Confused
It is very heavy indexed table i have 3 secondary indexes.
Partitioning will help only for (IOT_EVENT_DTIDX) index.(the dml in the example i pasted).
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)


Re: IOT secondary indexes SLOW performance [message #389688 is a reply to message #389664] Tue, 03 March 2009 03:47 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 messageGo to next message
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 messageGo to next message
cookiemonster
Messages: 12422
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 messageGo to next message
rleishman
Messages: 3724
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 #389932 is a reply to message #387037] Wed, 04 March 2009 03:43 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Oooops!

Not that it changes the basic point.
Re: IOT secondary indexes SLOW performance [message #390137 is a reply to message #389932] Thu, 05 March 2009 02:28 Go to previous messageGo to next message
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 messageGo to next message
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 #390362 is a reply to message #389664] Fri, 06 March 2009 01:56 Go to previous messageGo to next message
alexzeng
Messages: 133
Registered: August 2005
Location: alexzeng.wordpress.com
Senior Member
sky_lt wrote on Tue, 03 March 2009 01:13
I thought about partitioning, but the problem is in the architecture of that table: Confused
It is very heavy indexed table i have 3 secondary indexes.
Partitioning will help only for (IOT_EVENT_DTIDX) index.(the dml in the example i pasted).
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)




If there are so many secondary indexes, I think you can really think about heap table if you cannot bear the speed of using secondary indexes. Secondary indexes are designed to be inefficient. It is just guess and guess again...even full table scan might be better.
Or you create a materialized view for this iot table, and create nomral indexes on the mv for these kinds of query.

Regards,
Alex
Re: IOT secondary indexes SLOW performance [message #390397 is a reply to message #387037] Fri, 06 March 2009 03:54 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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
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


Previous Topic: Hash Joins and Memory Requirements
Next Topic: PLSQL Query Tuning Suggestion/Feedback needed
Goto Forum:
  


Current Time: Fri Dec 09 13:47:57 CST 2016

Total time taken to generate the page: 0.08145 seconds