OraFAQ Forum: Performance Tuning » Perfomace issues on complex query

Home » RDBMS Server » Performance Tuning » Perfomace issues on complex query

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

Perfomace issues on complex query [message #200788]

Wed, 01 November 2006 05:55

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

Hi,

I have the major perfomance problem while runnig the following query. It takes a lot of time to execute
and perfomnace is degraded due ti this reason. I need to resolve this issue on an urgent basis.

Can I have the recommendation on this issue?

How it can be achieved the better perfomance?

I have attached the query, explan plan and index list to serve this purpose.

thanks in advance.

Jayesh

Attachment: BOQuery.txt
(Size: 12.50KB, Downloaded 1218 times)

Report message to a moderator

Re: Perfomace issues on complex query [message #200798 is a reply to message #200788]

Wed, 01 November 2006 06:38

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Hi.

A number of questions:

1. How many rows do you have in FMIS_OWNER.FMIS_BFG_CUSTOMERS table ?
2. How many of them having
FMIS_OWNER.FMIS_BFG_CUSTOMERS.CUS_BOR_ID = 2 ?
3. Do you have an index on CUS_BOR_ID column (or an index having that column as FIRST index column)?
4. How many rows the query actually selects?

Report message to a moderator

Re: Perfomace issues on complex query [message #200824 is a reply to message #200788]

Wed, 01 November 2006 08:29

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

Please find the below answers as required to resolve this issue.

1. How many rows do you have in FMIS_OWNER.FMIS_BFG_CUSTOMERS table ?

10855

2. How many of them having
FMIS_OWNER.FMIS_BFG_CUSTOMERS.CUS_BOR_ID = 2 ?

384 records

3. Do you have an index on CUS_BOR_ID column (or an index having that column as FIRST index column)?

No

4. How many rows the query actually selects?
I have not understood this question. Can you please specify the table name or query?

Report message to a moderator

Re: Perfomace issues on complex query [message #200830 is a reply to message #200788]

Wed, 01 November 2006 08:55

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

1. About my last questions:
Do you jave an idea how many rows your original query is going to select?

2. I would recommend using an index:

CREATE INDEX ... ON FMIS_OWNER.FMIS_BFG_CUSTOMERS (CUS_BOR_ID).

Can you try such an index and post EXPLAIN plan?

I expect optimizer to utilize it and to see the FMIS_BFG_CUSTOMERS as a driving table of your query.

3. Can you specify what are the relations between following tables ( 1:1 or 1:many):
FMIS_INVENTORY_LISTING - FMIS_SITE_SERVICES (based on ssv_id)
FMIS_INVENTORY_LISTING - FMIS_NTN_NTNS (based on element_id)
FMIS_INVENTORY_LISTING - INVENTORY_SUPPLIERS (sup_id)
FMIS_INVENTORY_LISTING - FMIS_PACKAGE_INSTANCES (pi_id)
FMIS_INVENTORY_LISTING - FMIS_INV_STATUS4 (sta_id)
FMIS_SITE_SERVICES - FMIS_CONTRACT_SERVICES (csv_id)
FMIS_CONTRACT_SERVICES - CONTRACTS_CUS (con_id)

Report message to a moderator

Re: Perfomace issues on complex query [message #200852 is a reply to message #200830]

Wed, 01 November 2006 11:41

artmt
Messages: 32
Registered: October 2006
Location: Boston

Member

I doubt the index would make a difference. The table is fairly small and the index returning 3.6% of the rows may not be selective enough.

Looking at the optimizer cardinality estimates I suspect many to many joins.

Art

Report message to a moderator

Re: Perfomace issues on complex query [message #200855 is a reply to message #200852]

Wed, 01 November 2006 12:02

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

I am providing more details to sort out this issue. I hope that it would be useful to you.

I would have an apprxomately 1000 rows for my original query.

CREATE INDEX ... ON FMIS_OWNER.FMIS_BFG_CUSTOMERS (CUS_BOR_ID).

I could find out that i have non-unique index on cus_bor_id column.

I expect optimizer to utilize it and to see the FMIS_BFG_CUSTOMERS as a driving table of your query.

The FMIS_BFG_CUSTOMERS table is in the left side of where clause so that I belive it is in driving side.

It (FMIS_BFG_CUSTOMERS) is in driving position.

3. Can you specify what are the relations between following tables ( 1:1 or 1:many):

FMIS_INVENTORY_LISTING - FMIS_SITE_SERVICES (based on ssv_id)
FMIS_INVENTORY_LISTING - FMIS_NTN_NTNS (based on element_id)
FMIS_INVENTORY_LISTING - INVENTORY_SUPPLIERS (sup_id)
FMIS_INVENTORY_LISTING - FMIS_PACKAGE_INSTANCES (pi_id)
FMIS_INVENTORY_LISTING - FMIS_INV_STATUS4 (sta_id)
FMIS_SITE_SERVICES - FMIS_CONTRACT_SERVICES (csv_id)
FMIS_CONTRACT_SERVICES - CONTRACTS_CUS (con_id)

All the relations between above tables are many to one.

Can you please provide your valuable input?

How can I utilize the optimizer?

Moreover, I do not have any index on supplier_id,element_pi_id and sta_id used in the where clause.

There are only 14 different values of sta_id column. do I need to use or crate bitmap index on it?

I have also specified total number of rows in following the table.

FMIS_OWNER.FMIS_SITE_LISTING 60250
FMIS_OWNER.FMIS_BFG_CUSTOMERS 10855
FMIS_OWNER.FMIS_CONTRACTS FMIS_OWNER_CONTRACTS_CUS 12788
FMIS_OWNER.FMIS_INVENTORY_LISTING 236993
FMIS_OWNER.FMIS_NTN_NTNS 1720
FMIS_OWNER.FMIS_INV_STATUS FMIS_OWNER_FMIS_INV_STATUS4 17
FMIS_OWNER.FMIS_PACKAGE_INSTANCES, --FMIS_OWNER.FMIS_NETWORK_NODES 94744
FMIS_OWNER.FMIS_SUPPLIERS INVENTORY_SUPPLIERS 656
FMIS_OWNER.FMIS_SITE_SERVICES, --FMIS_OWNER.FMIS_CUSTOMER_LISTING 173187
FMIS_OWNER.FMIS_CONTRACT_SERVICES 3977

Can you please also suggest about the position of tables placed at driving side or not?

Your help would be highly appreciated.

Thanks in advance.

Report message to a moderator

Re: Perfomace issues on complex query [message #200875 is a reply to message #200788]

Wed, 01 November 2006 14:14

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Can you run following queries and post the results:
1. SELECT COUNT(*) CNTR, COUNT(DISTINCT SIT_ID )
FROM FMIS_OWNER.FMIS_INVENTORY_LISTING

2. SELECT COUNT(*) CNTR, COUNT(DISTINCT SSV_ID)
FROM FMIS_OWNER.FMIS_SITE_SERVICES

3. SELECT COUNT(*) CNTR, COUNT(DISTINCT CSV_ID)
FROM FMIS_OWNER.FMIS_CONTRACT_SERVICES

4. SELECT COUNT(*) CNTR, COUNT(DISTINCT CON_ID)
FROM FMIS_OWNER.FMIS_OWNER_CONTRACTS_CUS

5. SELECT COUNT(*) CNTR, COUNT(DISTINCT NTN_NTN_ID2)
FROM FMIS_OWNER.FMIS_NTN_NTNS

6. SELECT COUNT(*) CNTR, COUNT(DISTINCT SUP_ID)
FROM INVENTORY_SUPPLIERS

7. SELECT COUNT(*) CNTR, COUNT(DISTINCT PI_ID)
FROM FMIS_OWNER.FMIS_PACKAGE_INSTANCES

8. SELECT COUNT(*) CNTR, COUNT(DISTINCT STA_ID)
FROM FMIS_OWNER_FMIS_INV_STATUS4

Report message to a moderator

Re: Perfomace issues on complex query [message #202185 is a reply to message #200875]

Wed, 08 November 2006 11:33

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

Sorry for sending the required information late.

Please see the below output for mentioned queries.

1) 238405 53802

2) 173209 173209

3) 4134 4134

4)12800 12800

5) 1741 1343

6) 658 658

7) 94837 94837

Thanks in advance. Please let me know if you need any more information.

Report message to a moderator

Re: Perfomace issues on complex query [message #202358 is a reply to message #202185]

Thu, 09 November 2006 05:39

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Hi.

Try attached query, however in order for it to work verify following:

1. There is an index on FMIS_BFG_CUSTOMERS having CUS_BOR_ID as FIRST column ( If such index does NOT exists - create it).
2. Following conditions exist:
a. Index on FMIS_SITE_LISTING with CUS_ID as FIRST column
b. Index on FMIS_INVENTORY_LISTING with SIT_ID as FIRST column.
c. Index on FMIS_SITE_SERVICES with SSV_ID as FIRST column.
d. Index on FMIS_CONTRACT_SERVICES with CSV_ID as FIRST column.
e. Index on FMIS_CONTRACTS with CON_ID as FIRST column.
f. Index on FMIS_NTN_NTNS with NTN_NTN_ID2 as FIRST column.
g. Index on FMIS_PACKAGE_INSTANCES with PI_ID as FIRST column.
h. Index on FMIS_SUPPLIERS with SUP_ID as FIRST column.
i. Index on FMIS_INV_STATUS with STA_ID as FIRST column.

Please post the EXPLAIN plan and exec times of the query after the changes ( I added a number of hints and changed the order of table in FROM clause ).

HTH.

Attachment: A.SQL
(Size: 4.51KB, Downloaded 1101 times)

Report message to a moderator

Re: Perfomace issues on complex query [message #202407 is a reply to message #202358]

Thu, 09 November 2006 11:06

jaydba
Messages: 86
Registered: September 2006
Location: UK

Member

I'll do it and post you as soon as possible.

Thanks

Report message to a moderator

Previous Topic:	Creating PK in Each table
Next Topic:	PB statistic CBO

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 29 15:28:04 CDT 2024