OraFAQ Forum: Server Administration » performace issue with indexing

Home » RDBMS Server » Server Administration » performace issue with indexing

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

performace issue with indexing [message #207441]

Tue, 05 December 2006 10:11

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

Our users are experiencing the performance problem using Oracle 8.1.7 database. We are using Business object to display the data as required.

This is the below script using BO.

Can you please suggest me technically how to get resolved this issue?

Please let me know if you need any more information.

Thanks

This is an example of the typical type of query I use, it works o.k. but I think some indexing could make it run much quicker.

I think the TEST.SH_TYS____COMPL.CLOSED_TIME & TEST.SH_TYS____COMPL.CANCELLED_TIME are key fields that could be indexed to speed up the report.

Also I tend to do a lot of reports using the TEST.TYS__COMPL_CONFIGURATION.OWNING_CENTRE as a filter, this isn't that large a table but
uses a table join on TEST.TYS____COMPL.CID = TEST.TYS__COMPL_CONFIGURATION.CID so indexing the TEST.TYS____COMPL.CID field could
provide an improvement.

The TEST.TYS____COMPL.CREATE_DATE is another key field when we want to identify tickets raised during a period.

The TEST.TYS____COMPL.SUBCASE_OF is another key field we filter by.

The TEST.TYS____COMPL.CLEAR_CODE_OBJECT_FAMILY,TEST.TYS____COMPL.CLEAR_CODE_OBJECT & TEST.TYS____COMPL.CLEAR_CODE_ACTION fields may also be useful.

Attachment: indexperfomance.sql
(Size: 4.72KB, Downloaded 954 times)

Report message to a moderator

Re: performace issue with indexing [message #207499 is a reply to message #207441]

Tue, 05 December 2006 16:07

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Duplicate post from Newbies. Notice how no one answers when you put an attachment in your question?

Report message to a moderator

Re: performace issue with indexing [message #207593 is a reply to message #207441]

Wed, 06 December 2006 02:50

bwetkstr
Messages: 114
Registered: August 2005

Senior Member

There are a few things you can do.

First run the query in for example TOAD, check how long it takes to execute. (Best to count the rows)

select count(*) from (
--here comes your own query
)

Let's say it takes 500ms. Then the problem is not your query but BO. The reason why I mention this is because I've had similar problems.

If it is indeed the query that is slow. Then you can indeed add indexes. Don't forget an index can help you but also slow you down.
Oracle says that the best place the create an index is on the column that has the most distinct values.

But it all depends, it is not that easy. It depends weather your database is using the rule or cost based optimizer. When in cost based, are your statistics up to date, and so on.

So my advice is first check if your reports are slow due to your sql statement and not an other factor.

If so check if your database is in Rule based or cost based, and slowly add some indexes. Starting with the column that has the most distinct values.

It could be that your query is still slow, but at least you will have a better view of what is going on.

Kr
Karel.

Report message to a moderator

Re: performace issue with indexing [message #207665 is a reply to message #207593]

Wed, 06 December 2006 09:06

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

As suggested by you, I tested the given query and could find out that it returned 13892 rows in 10.03 secs.

How can I check my database is Rule based CBO?

Any further suggestion?

Your help would be highly appreciated.

Thanks in advance.

Report message to a moderator

Re: performace issue with indexing [message #207679 is a reply to message #207665]

Wed, 06 December 2006 09:51

bwetkstr
Messages: 114
Registered: August 2005

Senior Member

jaydba wrote on Wed, 06 December 2006 16:06

As suggested by you, I tested the given query and could find out that it returned 13892 rows in 10.03 secs.

How can I check my database is Rule based CBO?

Any further suggestion?

Your help would be highly appreciated.

Thanks in advance.

Seen that it is a Oracle 8i database it will probably be rule based. YOu can see that in your explain plan. But also by quering the v$parameter view

select name, value from v$parameter where upper(name) like '%OPTIMIZER%'

output

NAME                                               VALUE
-------------------------------------------------- --------------------------------------------------
optimizer_features_enable                          8.1.7
optimizer_mode                                     RULE
optimizer_max_permutations                         80000
optimizer_index_cost_adj                           100
optimizer_index_caching                            0
optimizer_percent_parallel                         0

None the less in what mode you're in I would start by adding an index to ...

These 2
TEST.TYS____COMPL.OLD_COMPL_ID
TEST.SH_TYS____COMPL.OLD_COMPL_ID

And maybe these 2
TEST.TYS____COMPL.CID
TEST.TYS__COMPL_CONFIGURATION.CID

Column that store id values are most of the time good choises because they have unique values.

But take it slowy one by one and check everytime what the impact is.

Keep me posted Wink

Wink

Report message to a moderator

Re: performace issue with indexing [message #207688 is a reply to message #207679]

Wed, 06 December 2006 11:24

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

The optimizer mode is CHOOSE(I think CBO) I will check your suggestion for creating index. Can you please tell me how to create explain plan through toad? How it would be useful here?

Thanks in advance.

Report message to a moderator

Re: performace issue with indexing [message #207811 is a reply to message #207688]

Thu, 07 December 2006 02:15

bwetkstr
Messages: 114
Registered: August 2005

Senior Member

jaydba wrote on Wed, 06 December 2006 18:24

The optimizer mode is CHOOSE(I think CBO) I will check your suggestion for creating index. Can you please tell me how to create explain plan through toad? How it would be useful here?

Thanks in advance.

Creating an explain plan is very easy : http://www.oracle-base.com/articles/8i/ExplainPlanUsage.php

Here you can find how to install it and how to use it.

An explain plan will tell you if your indexes are beeing used, if so, witch ones and so on. You see how your database is processing your query.

Kr
Karel.

Report message to a moderator

Re: performace issue with indexing [message #207815 is a reply to message #207688]

Thu, 07 December 2006 02:24

bwetkstr
Messages: 114
Registered: August 2005

Senior Member

jaydba wrote on Wed, 06 December 2006 18:24

The optimizer mode is CHOOSE(I think CBO)

CBO = Cost Based Optimizer
Not choose based.

Your database is in choose witch means that if you have statistics you will run in CBO, if not it will be RBO.

So do you run statistics on your objects?

Report message to a moderator

Re: performace issue with indexing [message #208194 is a reply to message #207593]

Fri, 08 December 2006 09:28

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

I executed the same rport today and it takes 01:10 sec. Do you beieve that it is a problem with BO.

Report message to a moderator

Re: performace issue with indexing [message #208198 is a reply to message #208194]

Fri, 08 December 2006 09:52

bwetkstr
Messages: 114
Registered: August 2005

Senior Member

jaydba wrote on Fri, 08 December 2006 16:28

I executed the same rport today and it takes 01:10 sec. Do you beieve that it is a problem with BO.

Did you add an index?
And by saying you ran the report .. is it the select count(*) .. query of that report ? Or the realy report?

[Updated on: Fri, 08 December 2006 09:52]

Report message to a moderator

Re: performace issue with indexing [message #208206 is a reply to message #208194]

Fri, 08 December 2006 10:28

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

I am sending herewith the execution plan for the current issue.

Can you please suggest me further?

Thanks

COUNT(*)
----------
13892

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=18936 Card=1 Bytes=3
9)

1 0 SORT (AGGREGATE)
2 1 CONCATENATION
3 2 HASH JOIN (Cost=10834 Card=3792 Bytes=147888)
4 3 NESTED LOOPS (Cost=10822 Card=3787 Bytes=128758)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'H35' (Cost=3248
Card=3787 Bytes=75740)

6 5 INDEX (RANGE SCAN) OF 'STF_CANT' (NON-UNIQUE) (C
ost=48 Card=3787)

7 4 TABLE ACCESS (BY INDEX ROWID) OF 'T35' (Cost=2 Car
d=1518461 Bytes=21258454)

8 7 INDEX (UNIQUE SCAN) OF 'IT35' (UNIQUE) (Cost=1 C
ard=1518461)

9 3 INDEX (FAST FULL SCAN) OF 'TFC_CID' (NON-UNIQUE) (Co
st=8 Card=11019 Bytes=55095)

10 2 HASH JOIN (Cost=10834 Card=3792 Bytes=147888)
11 10 NESTED LOOPS (Cost=10822 Card=3787 Bytes=128758)
12 11 TABLE ACCESS (BY INDEX ROWID) OF 'H35' (Cost=3248
Card=3787 Bytes=75740)

13 12 INDEX (RANGE SCAN) OF 'STF_CLOT' (NON-UNIQUE) (C
ost=48 Card=3787)

14 11 TABLE ACCESS (BY INDEX ROWID) OF 'T35' (Cost=2 Car
d=1518461 Bytes=21258454)

15 14 INDEX (UNIQUE SCAN) OF 'IT35' (UNIQUE) (Cost=1 C
ard=1518461)

16 10 INDEX (FAST FULL SCAN) OF 'TFC_CID' (NON-UNIQUE) (Co
st=8 Card=11019 Bytes=55095)

Report message to a moderator

Re: performace issue with indexing [message #208209 is a reply to message #207441]

Fri, 08 December 2006 10:41

bwetkstr
Messages: 114
Registered: August 2005

Senior Member

I'm not good at tuning Smile

Smile

still learning myself...

first is this the explain plan for the following query :

"select count(*) from select ....."

or for

"select ....."

Maybe you should post this in the sql or the tuning section, there they can help you furter

Report message to a moderator

Re: performace issue with indexing [message #208534 is a reply to message #208209]

Mon, 11 December 2006 04:15

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

I've not yet created any index still. I sent the explain plan for query select original statment means without select count(*)

Hope it may bes useful to you.

Report message to a moderator

Re: performace issue with indexing [message #208542 is a reply to message #207441]

Mon, 11 December 2006 04:48

bwetkstr
Messages: 114
Registered: August 2005

Senior Member

Well try to add an index and see how your explain plan changes, and recheck the time to execute the query.

And don't forget don't add like 5 indexs at once, take your time Wink

Wink

Report message to a moderator

Re: performace issue with indexing [message #208570 is a reply to message #208534]

Mon, 11 December 2006 06:31

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

Thanks for your suggestion. I'll try it. However, anyother suggestions/feedback/comment are welcome.

Report message to a moderator

Re: performace issue with indexing [message #208604 is a reply to message #207441]

Mon, 11 December 2006 09:01

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

I'm sending herewith the explain plan from toad as below for further invetigation

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 7 K 19360
CONCATENATION
HASH JOIN 3 K 2 M 11048
TABLE ACCESS FULL ARADMIN.T60 11 K 473 K 200
NESTED LOOPS 3 K 1 M 10837
TABLE ACCESS BY INDEX ROWID ARADMIN.H35 3 K 111 K 3255
INDEX RANGE SCAN ARADMIN.STF_CANT 3 K 48
TABLE ACCESS BY INDEX ROWID ARADMIN.T35 1 M 698 M 2
INDEX UNIQUE SCAN ARADMIN.IT35 1 M 1
HASH JOIN 3 K 2 M 11048
TABLE ACCESS FULL ARADMIN.T60 11 K 473 K 200
NESTED LOOPS 3 K 1 M 10837
TABLE ACCESS BY INDEX ROWID ARADMIN.H35 3 K 111 K 3255
INDEX RANGE SCAN ARADMIN.STF_CLOT 3 K 48
TABLE ACCESS BY INDEX ROWID ARADMIN.T35 1 M 698 M 2
INDEX UNIQUE SCAN ARADMIN.IT35 1 M 1

Report message to a moderator

Re: performace issue with indexing [message #208611 is a reply to message #208604]

Mon, 11 December 2006 09:22

bwetkstr
Messages: 114
Registered: August 2005

Senior Member

jaydba wrote on Mon, 11 December 2006 16:01

I'm sending herewith the explain plan from toad as below for further invetigation

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 7 K 19360
CONCATENATION
HASH JOIN 3 K 2 M 11048
TABLE ACCESS FULL ARADMIN.T60 11 K 473 K 200
NESTED LOOPS 3 K 1 M 10837
TABLE ACCESS BY INDEX ROWID ARADMIN.H35 3 K 111 K 3255
INDEX RANGE SCAN ARADMIN.STF_CANT 3 K 48
TABLE ACCESS BY INDEX ROWID ARADMIN.T35 1 M 698 M 2
INDEX UNIQUE SCAN ARADMIN.IT35 1 M 1
HASH JOIN 3 K 2 M 11048
TABLE ACCESS FULL ARADMIN.T60 11 K 473 K 200
NESTED LOOPS 3 K 1 M 10837
TABLE ACCESS BY INDEX ROWID ARADMIN.H35 3 K 111 K 3255
INDEX RANGE SCAN ARADMIN.STF_CLOT 3 K 48
TABLE ACCESS BY INDEX ROWID ARADMIN.T35 1 M 698 M 2
INDEX UNIQUE SCAN ARADMIN.IT35 1 M 1

What did you do? Add an index? Where ? What kind? How long did the query take?

Report message to a moderator

Previous Topic:	Information about dump in the Oracle, please help me to find...
Next Topic:	ORA-01691: unable to extend lob segment

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 25 15:15:49 CST 2025