Home » RDBMS Server » Performance Tuning » Optimizing the query containing 7 table joins
Optimizing the query containing 7 table joins [message #426926] Tue, 20 October 2009 06:19 Go to next message
manzoor14
Messages: 5
Registered: October 2009
Junior Member

hi,
I have a query which is taking almost 20 minutes to retrieve the data from DB. let me know how can i further optimize the query.. the tables contains huge amount of data

Table1 a -> 1040131 rows
Table2 b -> 1040131 rows
Table3 c -> 2080262 rows
Table4 d -> 2749 rows
Table5 e -> 1040131 rows,
Table6 f -> 93819 rows
Table7 g -> 99203 rows

My query is

SELECT a.lid, g.image, f.product , d.manufacturer, b.desc, c.price, c.abbr, c.currency, c.class
FROM
Table1 a,
Table2 b,
Table3 c,
Table4 d,
Table5 e,
Table6 f,
Table7 g

WHERE (UPPER(b.desc) like '%TEST%' OR UPPER(b.desc) like '%BEST%')
and a.line = b.line
AND a.line = c.line
AND c.subset = 576
AND a.manufacturer = d.manufacturer
AND a.line = e.line
and a.product = f.product
and e.image = g.image


Please tell me how can i optimize this query further to work faster
Re: Optimizing the query containing 7 table joins [message #426929 is a reply to message #426926] Tue, 20 October 2009 06:27 Go to previous messageGo to next message
balakrishnay
Messages: 54
Registered: September 2009
Location: Pune
Member

Hi,

set autotrace traceonly explain
your query here .

Give me the execution plan for your query for analysis.

I hope you are aware of this , Any function you use in query will suppress in using indexes.

Regards

Bala
Re: Optimizing the query containing 7 table joins [message #426932 is a reply to message #426926] Tue, 20 October 2009 06:34 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
UPPER(b.desc) like '%TEST%' OR UPPER(b.desc) like '%BEST%'

goes for full table scan. You can avoid this by creating INDEX TYPE

Search here by the keyword " CONTAINS"...



Oracle sometimes can not find the best path when there is more joins between tables. You can have a look at the Performance Tuning sticky in this forum


Thanks
Ved

[Updated on: Tue, 20 October 2009 06:37]

Report message to a moderator

Re: Optimizing the query containing 7 table joins [message #426936 is a reply to message #426932] Tue, 20 October 2009 06:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't think the function causing avoidance if an index is very likely to be the problem here, as there's no standard or function based index I know of that will work for a
LIKE '%TEST%'
condition.

Oracle Text can do it though.

Re: Optimizing the query containing 7 table joins [message #426937 is a reply to message #426936] Tue, 20 October 2009 06:39 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Thats what I meant Sir.

Asked the OP to search by "CONTAINS" keyword here..
Re: Optimizing the query containing 7 table joins [message #426948 is a reply to message #426926] Tue, 20 October 2009 06:49 Go to previous messageGo to next message
balakrishnay
Messages: 54
Registered: September 2009
Location: Pune
Member

Hi All,

Index can still be used in this case ..

LIKE '%TEST%'

But a small change to the predicate , Actually this should be like this .

LIKE 'TEST%'

Regards

Bala



Re: Optimizing the query containing 7 table joins [message #426953 is a reply to message #426948] Tue, 20 October 2009 07:01 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

Index can still be used in this case ..

LIKE '%TEST%'

But a small change to the predicate , Actually this should be like this .

LIKE 'TEST%'



@Bala: Can you expect the same result by changing it to
LIKE 'TEST%'


You can not.

Well, it could be a small change as you said but can bring a BIG trouble for you Smile


Ved
Re: Optimizing the query containing 7 table joins [message #426956 is a reply to message #426926] Tue, 20 October 2009 07:08 Go to previous messageGo to next message
balakrishnay
Messages: 54
Registered: September 2009
Location: Pune
Member

Hi Ved,

Agreed !! what you said is exactly right , it makes a big sense interms of output.

I was taking interms of indexing prospective.

Regards

Bala


Re: Optimizing the query containing 7 table joins [message #426958 is a reply to message #426953] Tue, 20 October 2009 07:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@Ved:

I didn't see your post when I hit Reply.

@Bala:Quote:
Index can still be used in this case ..

LIKE '%TEST%'

I don't think that's the case. I don't think a normal index can be used in this case at all.
If you believe otherwise, I'd love to see a test case.
Re: Optimizing the query containing 7 table joins [message #426961 is a reply to message #426926] Tue, 20 October 2009 07:15 Go to previous messageGo to next message
balakrishnay
Messages: 54
Registered: September 2009
Location: Pune
Member
Ved,


Requesting to pls go through what i have replied . i said LIKE 'TEST%' index will be used in this case . It means we should suffix % at the end of the word then index can be used.

I hope i am clear if not we will walk through a test case.

Regards

Bala
Re: Optimizing the query containing 7 table joins [message #426963 is a reply to message #426926] Tue, 20 October 2009 07:19 Go to previous messageGo to next message
balakrishnay
Messages: 54
Registered: September 2009
Location: Pune
Member
Ved,

SQL> drop table emp_tmp purge;

Table dropped.

SQL> create table emp_tmp as select * from emp;

Table created.

SQL> create index emp_tmp_idx on emp_tmp(ename);

Index created.

SQL> exec dbms_Stats.gather_Table_Stats('','EMP_TMP',cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select * from emp_tmp where ename like 'CLA%';

Execution Plan
----------------------------------------------------------
Plan hash value: 4259130087

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    37 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP_TMP     |     1 |    37 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_TMP_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ENAME" LIKE 'CLA%')
       filter("ENAME" LIKE 'CLA%')

SQL> select * from emp_tmp where ename like '%CLA%';

Execution Plan
----------------------------------------------------------
Plan hash value: 2242264577

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |    37 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP_TMP |     1 |    37 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ENAME" LIKE '%CLA%')

Re: Optimizing the query containing 7 table joins [message #426965 is a reply to message #426961] Tue, 20 October 2009 07:31 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

I was taking interms of indexing prospective.

@Bala,
But your statement is no more useful in this context. Is not it?


Thanks
Ved



Re: Optimizing the query containing 7 table joins [message #426969 is a reply to message #426926] Tue, 20 October 2009 07:41 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Let's get this thread back to the point shall we? See if we can speed it up without resorting oracle text or changing the results.

@manzoor14 - you need to give us an explain plan for this query. Details of any indexes on the tables would also be usefull along with the total number of records the query should return.
Re: Optimizing the query containing 7 table joins [message #426981 is a reply to message #426969] Tue, 20 October 2009 08:13 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
cookiemonster wrote on Tue, 20 October 2009 07:41

@manzoor14 - you need to give us an explain plan for this query. Details of any indexes on the tables would also be usefull along with the total number of records the query should return.



Also, it would be helpful to have the below information:

 
select count(*) from Table2 b where UPPER(b.desc) like '%TEST%';
select count(*) from Table2 b where UPPER(b.desc) like '%BEST%';




Make sure that stats are upto data.Verify LAST_ANALYZED column of USER_TABLES/USER_INDEXES

Thanks


[Updated on: Tue, 20 October 2009 08:15]

Report message to a moderator

Re: Optimizing the query containing 7 table joins [message #427071 is a reply to message #426926] Tue, 20 October 2009 22:03 Go to previous messageGo to next message
balakrishnay
Messages: 54
Registered: September 2009
Location: Pune
Member
Hi ved,


I know that its not useful in this context but you have insisted me in writing and we started a debate on usage of index when using like operator.

Any way lets forget for now about our discussion in this thread. Smile

Regards

Bala

[Updated on: Tue, 20 October 2009 22:05]

Report message to a moderator

Re: Optimizing the query containing 7 table joins [message #427080 is a reply to message #427071] Wed, 21 October 2009 00:30 Go to previous messageGo to next message
manzoor14
Messages: 5
Registered: October 2009
Junior Member
Thank you all for the response till now.. Here is the exact query thats taking time..

SELECT a.line_item_id, g.image_file_location, f.product_name , d.manufacturer_name, b.short_desc, c.price_1, c.uom_abbr, c.currency_abbr, c.class_code
FROM
line_item a,
loc_line_item b,
line_item_price c,
loc_manufacturer d,
line_item_image e,
loc_product f,
image g
WHERE (UPPER(b.short_desc) like '%BLADES%' OR UPPER(b.short_desc) like '%DIAMOND%')
and a.line_item_id = b.line_item_id
AND a.line_item_id = c.line_item_id
AND c.catalog_subset_id = 576
AND a.manufacturer_id = d.manufacturer_id
AND a.line_item_id = e.line_item_id
and a.product_id = f.product_id
and e.image_id = g.image_id

Also.. the following is the data some of u guys needed..

line_item a -> 1040131 records,
loc_line_item b -> 1040131 records ,
line_item_price c -> 2080262 records ,
loc_manufacturer d -> 2749 records,
line_item_image e -> 1040131 records,
loc_product f -> 93819 records,
image g -> 99203 records

select count(*) from loc_line_item b where (UPPER(b.short_desc) like '%BLADES%' OR UPPER(b.short_desc) like '%DIAMOND%')

COUNT(*)
----------------------
1768

1 rows selected

select count(*) from line_item_price c where c.catalog_subset_id = 576

COUNT(*)
----------------------
1040131

1 rows selected

also , the tables have the following indexes..

1. CREATE INDEX "IX_LOC_LINE_ITEM_ID" ON "LOC_LINE_ITEM" ("LINE_ITEM_ID")

2. CREATE INDEX "IX_MANUFACTURER_ID" ON "LINE_ITEM" ("MANUFACTURER_ID")

3. CREATE INDEX "IX_PRODUCT_ID_1" ON "LINE_ITEM" ("PRODUCT_ID")

4. CREATE INDEX "IX_LINE_ITEM_ID_STATUS" ON "LINE_ITEM" ("LINE_ITEM_ID", "STATUS")

This query is for a "keyword" search functionality.. so the number of records returned depends on the keyword given in the Front end.

below is the execution plan


Execution Plan
----------------------------------------------------------
   0       SELECT STATEMENT Optimizer Mode=CHOOSE (Cost=2410 Card=55 Bytes=13 K)
   1    0    HASH JOIN (Cost=2410 Card=55 Bytes=13)
   2    1      NESTED LOOPS (Cost=2406 Card=55 Bytes=12 K)
   3    2        NESTED  (Cost=2351 Card=55 Bytes=9 K)
   4    3          NESTED  (Cost=2296 Card=55 Bytes=8 K)
   5    4            HASH JOIN (Cost=1172 Card=562 Bytes=31 K)
   6    5              NESTED LOOPS (Cost=1126 Card=562 Bytes=19 K)
   7    6                TABLE ACCESS BY INDEX ROWID CIM.LINE_ITEM_PRICE (Cost=2 Card=562 Bytes=10 K)
   8    7                  INDEX RANGE SCAN CIM.IX_CATALOG_SUBSET (Cost=1 Card=225)
   9    6                TABLE ACCESS BY INDEX ROWID CIM.LINE_ITEM (Cost=2 Card=378 Bytes=5 K)
  10    9                  INDEX UNIQUE SCAN CIM.PK_LINE_ITEM (Cost=1 Card=1)
  11    5              TABLE ACCESS FULL CIM.LOC_PRODUCT (Cost=40 Card=93 K Bytes=1 M)
  12    4            TABLE ACCESS BY INDEX ROWID CIM.LOC_LINE_ITEM (Cost=2 Card=1 Bytes=100)
  13   12              INDEX RANGE SCAN CIM.IX_LOC_LINE_ITEM_ID (Cost=1 Card=1)
  14    3          INDEX RAN CIM.PK_LINE_ITEM_IMAGE (Cost=1 Card=1 Bytes=10)
  15    2        TABLE ACCESS BY INDEX ROWID CIM.IMAGE (Cost=1 Card=1 Bytes=57)
  16   15          INDEX UNIQUE SCAN CIM.PK_IMAGE (Card=1)
  17    1      TABLE ACCESS FULL CIM.LOC_MANUFACTURER (Cost=3 Card=2 K Bytes=64 K)


please let me know if any further details are required.

Thanks

[addrd code tags to display explain plan in a readable format]

[Updated on: Wed, 21 October 2009 09:23] by Moderator

Report message to a moderator

Re: Optimizing the query containing 7 table joins [message #427089 is a reply to message #427080] Wed, 21 October 2009 01:24 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
@manzoor14,
Are the stats upto date ?

11 5 TABLE ACCESS FULL CIM.LOC_PRODUCT (Cost=40 Card=93 K Bytes=1 M)


Is there any index created LOC_PRODUCT ?



Also can you provide the plan for the sql with the hint.Just want
to see how the plan is when Oracle use RBO (Would suggest not to use this hint but to tune the sql with other alternatives )


SELECT /*+ rule */ a.line_item_id, g.image_file_location, f.product_name , d.manufacturer_name, b.short_desc, c.price_1, c.uom_abbr, c.currency_abbr, c.class_code 
FROM 
line_item a, 
loc_line_item b, 
line_item_price c, 
loc_manufacturer d, 
line_item_image e, 
loc_product f, 
image g 
WHERE (UPPER(b.short_desc) like '%BLADES%' OR UPPER(b.short_desc) like '%DIAMOND%')
and a.line_item_id = b.line_item_id 
AND a.line_item_id = c.line_item_id 
AND c.catalog_subset_id = 576 
AND a.manufacturer_id = d.manufacturer_id 
AND a.line_item_id = e.line_item_id 
and a.product_id = f.product_id 
and e.image_id = g.image_id





@Bala
Quote:

I know that its not useful in this context but you have insisted me in writing and we started a debate on usage of index when using like operator.
huh???

I am not here to debate but to help others with little knowledge I have and just to learn from people here (also from you Smile).
I know that I may provide wrong answers at times but whats bad in trying? I belive that some people with immense knowledge are here in this forum to correct me
and to learn this way.I love when someone criticize at the same time should have the reason for it Smile


Thanks

[Updated on: Wed, 21 October 2009 02:07]

Report message to a moderator

Re: Optimizing the query containing 7 table joins [message #427120 is a reply to message #426926] Wed, 21 October 2009 04:29 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
@manzoor14

When posting code and explain plans can you please use code tags - see the orafaq forum guide if you're not sure how - it makes code a lot eaiser to read.

Can you post the results of the following queries:
SELECT count(distinct(catalog_subset_id)) FROM line_item_price;

SELECT count(*)
FROM 
line_item a, 
loc_line_item b, 
line_item_price c, 
loc_manufacturer d, 
line_item_image e, 
loc_product f, 
image g 
WHERE (UPPER(b.short_desc) like '%BLADES%' OR UPPER(b.short_desc) like '%DIAMOND%')
and a.line_item_id = b.line_item_id 
AND a.line_item_id = c.line_item_id 
AND c.catalog_subset_id = 576 
AND a.manufacturer_id = d.manufacturer_id 
AND a.line_item_id = e.line_item_id 
and a.product_id = f.product_id 
and e.image_id = g.image_id;


Can you also post the rest of the index defintions, you've missed PK_IMAGE, PK_LINE_ITEM_IMAGE, PK_LINE_ITEM and IX_CATALOG_SUBSET.

Can you also re-run the explain plan using the following:
EXPLAIN PLAN FOR <your select statement>;

SELECT * FROM table(dbms_xplan.display);

It gives the explain plan in a nicer format.
Re: Optimizing the query containing 7 table joins [message #427199 is a reply to message #427089] Wed, 21 October 2009 09:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@manzoor14

Are the statistics up to date on these tables? Your initial post idicates that the tables all have in the region of 100,000 - 1,000,000 rows, and yet your cardinality values and costs suggest much smaller tables than that.

as @Ved points out, you're probably missing an index on Loc_Product.Product_id, plus the list of indexes you've provided doesn't include indexes like IX_CATALOG_SUBSET on line_item_price - can we have an accurate list of indexes please.


@bala:Quote:

I know that its not useful in this context but you have insisted me in writing and we started a debate on usage of index when using like operator.
No - you brougt up an irrelevant point about indexes, demonstrating that if we were looking at a different query , then there might be a problem. Sadly, we weren't looking at a different query, we're looking at this one.

Re: Optimizing the query containing 7 table joins [message #427311 is a reply to message #426926] Wed, 21 October 2009 23:39 Go to previous messageGo to next message
manzoor14
Messages: 5
Registered: October 2009
Junior Member
Hi,

Thanks for the responses till now.. here is the list of the missing indexes on the tables used in the query.


CREATE UNIQUE INDEX "PK_IMAGE" ON "IMAGE" ("IMAGE_ID") 
CREATE UNIQUE INDEX "PK_LINE_ITEM_IMAGE" ON "LINE_ITEM_IMAGE" ("LINE_ITEM_ID", "IMAGE_ID") 
CREATE UNIQUE INDEX "PK_LINE_ITEM" ON "LINE_ITEM" ("LINE_ITEM_ID") 
CREATE INDEX "IX_CATALOG_SUBSET" ON "LINE_ITEM_PRICE" ("CATALOG_SUBSET_ID" DESC)
CREATE INDEX "IX_PRODUCT_NAME" ON "LOC_PRODUCT" ("PRODUCT_NAME")
CREATE INDEX "IX_PRODUCT_ID_1" ON "LINE_ITEM" ("PRODUCT_ID") 



Following is the query results that some of the people required



SELECT count(distinct(catalog_subset_id)) FROM line_item_price;

COUNT(DISTINCT(CATALOG_SUBSET_ID)) 
---------------------------------- 
37                                 

1 rows selected

SELECT count(*)
FROM 
line_item a, 
loc_line_item b, 
line_item_price c, 
loc_manufacturer d, 
line_item_image e, 
loc_product f, 
image g 
WHERE (UPPER(b.short_desc) like '%BLADES%' OR UPPER(b.short_desc) like '%DIAMOND%')
and a.line_item_id = b.line_item_id 
AND a.line_item_id = c.line_item_id 
AND c.catalog_subset_id = 576 
AND a.manufacturer_id = d.manufacturer_id 
AND a.line_item_id = e.line_item_id 
and a.product_id = f.product_id 
and e.image_id = g.image_id;

COUNT(*)               
---------------------- 
1768                   

1 rows selected

ps: the above query took 184 seconds to execute :(

below is the result after running the explain plan query

PLAN_TABLE_OUTPUT                                                                                                                                                                                        
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
                                                                                                                                                                                                         
-----------------------------------------------------------------------------------------------                                                                                                          
| Id  | Operation                          |  Name                    | Rows  | Bytes | Cost  |                                                                                                          
-----------------------------------------------------------------------------------------------                                                                                                          
|   0 | SELECT STATEMENT                   |                          |     1 |   249 |  1695 |                                                                                                          
|   1 |  TABLE ACCESS BY INDEX ROWID       | LOC_MANUFACTURER         |     1 |    24 |     2 |                                                                                                          
|   2 |   NESTED LOOPS                     |                          |     1 |   249 |  1695 |                                                                                                          
|   3 |    NESTED LOOPS                    |                          |     1 |   225 |  1693 |                                                                                                          
|   4 |     NESTED LOOPS                   |                          |     1 |   125 |  1691 |                                                                                                          
|   5 |      NESTED LOOPS                  |                          |     1 |   103 |  1689 |                                                                                                          
|   6 |       NESTED LOOPS                 |                          |     1 |    46 |  1688 |                                                                                                          
|   7 |        NESTED LOOPS                |                          |   562 | 20232 |  1126 |                                                                                                          
|   8 |         TABLE ACCESS BY INDEX ROWID| LINE_ITEM_PRICE          |   562 | 11240 |     2 |                                                                                                          
|*  9 |          INDEX RANGE SCAN          | IX_CATALOG_SUBSET        |   225 |       |     1 |                                                                                                          
|  10 |         TABLE ACCESS BY INDEX ROWID| LINE_ITEM                |   378 |  6048 |     2 |                                                                                                          
|* 11 |          INDEX UNIQUE SCAN         | PK_LINE_ITEM             |     1 |       |     1 |                                                                                                          
|* 12 |        INDEX RANGE SCAN            | PK_LINE_ITEM_IMAGE       |     1 |    10 |     1 |                                                                                                          
|  13 |       TABLE ACCESS BY INDEX ROWID  | IMAGE                    |     1 |    57 |     1 |                                                                                                          
|* 14 |        INDEX UNIQUE SCAN           | PK_IMAGE                 |     1 |       |       |                                                                                                          
|  15 |      TABLE ACCESS BY INDEX ROWID   | LOC_PRODUCT              |     1 |    22 |     2 |                                                                                                          
|* 16 |       INDEX RANGE SCAN             | IX_LOC_PRODUCTID_STATUS  |     1 |       |     1 |                                                                                                          
|* 17 |     TABLE ACCESS BY INDEX ROWID    | LOC_LINE_ITEM            |     1 |   100 |     2 |                                                                                                          
|* 18 |      INDEX RANGE SCAN              | IX_LOC_LINE_ITEM_ID      |     1 |       |     1 |                                                                                                          
|* 19 |    INDEX RANGE SCAN                | PK_LOC_MANUFACTURER      |     1 |       |     1 |                                                                                                          
-----------------------------------------------------------------------------------------------                                                                                                          
                                                                                                                                                                                                         
Predicate Information (identified by operation id):                                                                                                                                                      
---------------------------------------------------                                                                                                                                                      
                                                                                                                                                                                                         
   9 - access(SYS_OP_DESCEND("C"."CATALOG_SUBSET_ID")=HEXTORAW('3DF9B2FF') )                                                                                                                             
       filter("C"."CATALOG_SUBSET_ID"=576)                                                                                                                                                               
  11 - access("A"."LINE_ITEM_ID"="C"."LINE_ITEM_ID")                                                                                                                                                     
  12 - access("A"."LINE_ITEM_ID"="E"."LINE_ITEM_ID")                                                                                                                                                     
       filter("A"."LINE_ITEM_ID"="E"."LINE_ITEM_ID")                                                                                                                                                     
  14 - access("E"."IMAGE_ID"="G"."IMAGE_ID")                                                                                                                                                             
  16 - access("A"."PRODUCT_ID"="F"."PRODUCT_ID")                                                                                                                                                         
  17 - filter(UPPER("B"."SHORT_DESC") LIKE '%BLADES%' OR UPPER("B"."SHORT_DESC") LIKE '%DIAM                                                                                                             
              OND%')                                                                                                                                                                                     
  18 - access("A"."LINE_ITEM_ID"="B"."LINE_ITEM_ID")                                                                                                                                                     
  19 - access("A"."MANUFACTURER_ID"="D"."MANUFACTURER_ID")                                                                                                                                               
                                                                                                                                                                                                         
Note: cpu costing is off                                                                                                                                                                                 

42 rows selected



I appreciate your effort on helping me optimize the query.

Thanks
Manzoor

Re: Optimizing the query containing 7 table joins [message #427336 is a reply to message #427311] Thu, 22 October 2009 02:11 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Thanks for providing the information.
What was the response time when u ran the aboove 2nd sql ?


Also can you provide the plan for the sql with the hint.Just want
to see how the plan is when Oracle use RBO (Would suggest not to use this hint but to tune the sql with other alternatives )


SELECT /*+ rule */ a.line_item_id, g.image_file_location, f.product_name , d.manufacturer_name, b.short_desc, c.price_1, c.uom_abbr, c.currency_abbr, c.class_code 
FROM 
line_item a, 
loc_line_item b, 
line_item_price c, 
loc_manufacturer d, 
line_item_image e, 
loc_product f, 
image g 
WHERE (UPPER(b.short_desc) like '%BLADES%' OR UPPER(b.short_desc) like '%DIAMOND%')
and a.line_item_id = b.line_item_id 
AND a.line_item_id = c.line_item_id 
AND c.catalog_subset_id = 576 
AND a.manufacturer_id = d.manufacturer_id 
AND a.line_item_id = e.line_item_id 
and a.product_id = f.product_id 
and e.image_id = g.image_id





Or can you provide the show plan for the sql below with index hint?



SELECT /*+ INDEX(c IX_CATALOG_SUBSET)*/ a.line_item_id, g.image_file_location, f.product_name , d.manufacturer_name, b.short_desc, c.price_1, c.uom_abbr, c.currency_abbr, c.class_code 
FROM 
line_item a, 
loc_line_item b, 
line_item_price c, 
loc_manufacturer d, 
line_item_image e, 
loc_product f, 
image g 
WHERE (UPPER(b.short_desc) like '%BLADES%' OR UPPER(b.short_desc) like '%DIAMOND%')
and a.line_item_id = b.line_item_id 
AND a.line_item_id = c.line_item_id 
AND c.catalog_subset_id = 576 
AND a.manufacturer_id = d.manufacturer_id 
AND a.line_item_id = e.line_item_id 
and a.product_id = f.product_id 
and e.image_id = g.image_id












Another concern here may be


   9 - access(SYS_OP_DESCEND("C"."CATALOG_SUBSET_ID")=HEXTORAW('3DF9B2FF') )                                                                                                                             
       filter("C"."CATALOG_SUBSET_ID"=576)  

sys_op_descend
--------------
An internal function that takes a value and
returns the form that would be stored for
that value in a descending index. Essentially
doing a one's complement on the bytes and
appending an 0xFF byte

CREATE INDEX "IX_CATALOG_SUBSET" ON "LINE_ITEM_PRICE" ("CATALOG_SUBSET_ID" DESC)


Why this index created as DESC? Can be the reason for the sql being slow.


My concern here is that stats are not upto date. Can you provide the plan for the rule hint and index hint I have mentioned?

I want to know how the plan is when Oracle uses RBO.
Are statistics upto date?

Thanks


[Updated on: Thu, 22 October 2009 04:31]

Report message to a moderator

Re: Optimizing the query containing 7 table joins [message #427368 is a reply to message #427336] Thu, 22 October 2009 04:32 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
I definitely think the stats are off.

Considering that there are 2080262 records in line_item_price, of which 1040131 have CATALOG_SUBSET_ID you're looking for -
so half the table - the index on that column should not be used as it's really unselective.

Oracle thinks it is selective though:
|*  9 |          INDEX RANGE SCAN          | IX_CATALOG_SUBSET        |   225 |       |     1 | 

Oracle thinks the index range scan will find approx 225 rows, which is way out.

Also given this:
SELECT count(distinct(catalog_subset_id)) FROM line_item_price;

COUNT(DISTINCT(CATALOG_SUBSET_ID)) 
---------------------------------- 
37                       

I'm not sure why you've got an index on that column at all.

Make sure your stats are up to date and if the problem persists try dropping the index on CATALOG_SUBSET_ID it doesn't appear to be helpful.

Its_me_ved wrote on Thu, 22 October 2009 08:11

Another concern here may be


   9 - access(SYS_OP_DESCEND("C"."CATALOG_SUBSET_ID")=HEXTORAW('3DF9B2FF') )                                                                                                                             
       filter("C"."CATALOG_SUBSET_ID"=576)  

sys_op_descend
--------------
An internal function that takes a value and
returns the form that would be stored for
that value in a descending index. Essentially
doing a one's complement on the bytes and
appending an 0xFF byte


That's there because IX_CATALOG_SUBSET is a descending index.


Re: Optimizing the query containing 7 table joins [message #427372 is a reply to message #427368] Thu, 22 October 2009 04:57 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Thanks for the update.Valid points..Smile


That's there because IX_CATALOG_SUBSET is a descending index.

I meant can the Index as DESC be a reason of casuing a sql slower.Can it be the reason for making cost high. I think this
way.Not sure!

Because by default it is storing as ASC.


Re: Optimizing the query containing 7 table joins [message #427374 is a reply to message #426926] Thu, 22 October 2009 05:02 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
I don't think the fact that it's DESC is the problem. I think the fact that it's being used at all is the problem.

Indexes are usefull when selecting a small subset of rows.
When you're selecting half a large table as the OP is you want a full table scan.

The only way the DESC bit might be an issue is if this fact is what is fooling the optimiser into using the index in the first place - but I suspect the real reason is the stats are out of date.
Re: Optimizing the query containing 7 table joins [message #427390 is a reply to message #427374] Thu, 22 October 2009 06:19 Go to previous messageGo to next message
manzoor14
Messages: 5
Registered: October 2009
Junior Member
Hi,

The stats are up to date. Just to confirm i ran the count queries on the tables used in the Main query and obtained the following result.
select count(*) from line_item;

COUNT(*)               
---------------------- 
1040131                

1 rows selected

select count(*) from loc_line_item ;
COUNT(*)               
---------------------- 
1040131                

1 rows selected

select count(*) from line_item_price;

COUNT(*)               
---------------------- 
2080262                

1 rows selected

select count(*) from loc_manufacturer;

COUNT(*)               
---------------------- 
2749                   

1 rows selected

select count(*) from line_item_image;

COUNT(*)               
---------------------- 
1040131                

1 rows selected

select count(*) from loc_product ;

COUNT(*)               
---------------------- 
93819                  

1 rows selected

select count(*) from image;

COUNT(*)               
---------------------- 
99203                  

1 rows selected


Also,

PFB the explain plan when i ran the query using Index Hint

EXPLAIN PLAN succeeded.
PLAN_TABLE_OUTPUT                                                                                                                                                                                        
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
                                                                                                                                                                                                         
------------------------------------------------------------------------------------------                                                                                                               
| Id  | Operation                         |  Name                | Rows  | Bytes | Cost  |                                                                                                               
------------------------------------------------------------------------------------------                                                                                                               
|   0 | SELECT STATEMENT                  |                      |    55 | 13695 |  2410 |                                                                                                               
|*  1 |  HASH JOIN                        |                      |    55 | 13695 |  2410 |                                                                                                               
|   2 |   NESTED LOOPS                    |                      |    55 | 12375 |  2406 |                                                                                                               
|   3 |    NESTED LOOPS                   |                      |    55 |  9240 |  2351 |                                                                                                               
|   4 |     NESTED LOOPS                  |                      |    55 |  8690 |  2296 |                                                                                                               
|*  5 |      HASH JOIN                    |                      |   562 | 32596 |  1172 |                                                                                                               
|   6 |       NESTED LOOPS                |                      |   562 | 20232 |  1126 |                                                                                                               
|   7 |        TABLE ACCESS BY INDEX ROWID| LINE_ITEM_PRICE      |   562 | 11240 |     2 |                                                                                                               
|*  8 |         INDEX RANGE SCAN          | IX_CATALOG_SUBSET    |   225 |       |     1 |                                                                                                               
|   9 |        TABLE ACCESS BY INDEX ROWID| LINE_ITEM            |   378 |  6048 |     2 |                                                                                                               
|* 10 |         INDEX UNIQUE SCAN         | PK_LINE_ITEM         |     1 |       |     1 |                                                                                                               
|  11 |       TABLE ACCESS FULL           | LOC_PRODUCT          | 93815 |  2015K|    40 |                                                                                                               
|* 12 |      TABLE ACCESS BY INDEX ROWID  | LOC_LINE_ITEM        |     1 |   100 |     2 |                                                                                                               
|* 13 |       INDEX RANGE SCAN            | IX_LOC_LINE_ITEM_ID  |     1 |       |     1 |                                                                                                               
|* 14 |     INDEX RANGE SCAN              | PK_LINE_ITEM_IMAGE   |     1 |    10 |     1 |                                                                                                               
|  15 |    TABLE ACCESS BY INDEX ROWID    | IMAGE                |     1 |    57 |     1 |                                                                                                               
|* 16 |     INDEX UNIQUE SCAN             | PK_IMAGE             |     1 |       |       |                                                                                                               
|  17 |   TABLE ACCESS FULL               | LOC_MANUFACTURER     |  2749 | 65976 |     3 |                                                                                                               
------------------------------------------------------------------------------------------                                                                                                               
                                                                                                                                                                                                         
Predicate Information (identified by operation id):                                                                                                                                                      
---------------------------------------------------                                                                                                                                                      
                                                                                                                                                                                                         
   1 - access("A"."MANUFACTURER_ID"="D"."MANUFACTURER_ID")                                                                                                                                               
   5 - access("A"."PRODUCT_ID"="F"."PRODUCT_ID")                                                                                                                                                         
   8 - access(SYS_OP_DESCEND("C"."CATALOG_SUBSET_ID")=HEXTORAW('3DF9B2FF') )                                                                                                                             
       filter("C"."CATALOG_SUBSET_ID"=576)                                                                                                                                                               
  10 - access("A"."LINE_ITEM_ID"="C"."LINE_ITEM_ID")                                                                                                                                                     
  12 - filter(UPPER("B"."SHORT_DESC") LIKE '%BLADES%' OR UPPER("B"."SHORT_DESC") LIKE '                                                                                                                  
              %DIAMOND%')                                                                                                                                                                                
  13 - access("A"."LINE_ITEM_ID"="B"."LINE_ITEM_ID")                                                                                                                                                     
  14 - access("A"."LINE_ITEM_ID"="E"."LINE_ITEM_ID")                                                                                                                                                     
  16 - access("E"."IMAGE_ID"="G"."IMAGE_ID")                                                                                                                                                             
                                                                                                                                                                                                         
Note: cpu costing is off                                                                                                                                                                                 

39 rows selected




The Explain plan when using the rule hint is.

PLAN_TABLE_OUTPUT                                                                                                                                                                                        
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
                                                                                                                                                                                                         
-------------------------------------------------------------------------------------------                                                                                                              
| Id  | Operation                          |  Name                | Rows  | Bytes | Cost  |                                                                                                              
-------------------------------------------------------------------------------------------                                                                                                              
|   0 | SELECT STATEMENT                   |                      |       |       |       |                                                                                                              
|   1 |  TABLE ACCESS BY INDEX ROWID       | LOC_MANUFACTURER     |       |       |       |                                                                                                              
|   2 |   NESTED LOOPS                     |                      |       |       |       |                                                                                                              
|   3 |    NESTED LOOPS                    |                      |       |       |       |                                                                                                              
|   4 |     NESTED LOOPS                   |                      |       |       |       |                                                                                                              
|   5 |      NESTED LOOPS                  |                      |       |       |       |                                                                                                              
|   6 |       NESTED LOOPS                 |                      |       |       |       |                                                                                                              
|   7 |        NESTED LOOPS                |                      |       |       |       |                                                                                                              
|   8 |         TABLE ACCESS FULL          | LOC_PRODUCT          |       |       |       |                                                                                                              
|   9 |         TABLE ACCESS BY INDEX ROWID| LINE_ITEM            |       |       |       |                                                                                                              
|* 10 |          INDEX RANGE SCAN          | IX_PRODUCT_ID_1      |       |       |       |                                                                                                              
|  11 |        TABLE ACCESS BY INDEX ROWID | LINE_ITEM_PRICE      |       |       |       |                                                                                                              
|* 12 |         INDEX UNIQUE SCAN          | PK_LINE_ITEM_PRICE   |       |       |       |                                                                                                              
|* 13 |       TABLE ACCESS BY INDEX ROWID  | LOC_LINE_ITEM        |       |       |       |                                                                                                              
|* 14 |        INDEX RANGE SCAN            | IX_LOC_LINE_ITEM_ID  |       |       |       |                                                                                                              
|* 15 |      INDEX RANGE SCAN              | PK_LINE_ITEM_IMAGE   |       |       |       |                                                                                                              
|  16 |     TABLE ACCESS BY INDEX ROWID    | IMAGE                |       |       |       |                                                                                                              
|* 17 |      INDEX UNIQUE SCAN             | PK_IMAGE             |       |       |       |                                                                                                              
|* 18 |    INDEX RANGE SCAN                | PK_LOC_MANUFACTURER  |       |       |       |                                                                                                              
-------------------------------------------------------------------------------------------                                                                                                              
                                                                                                                                                                                                         
Predicate Information (identified by operation id):                                                                                                                                                      
---------------------------------------------------                                                                                                                                                      
                                                                                                                                                                                                         
  10 - access("A"."PRODUCT_ID"="F"."PRODUCT_ID")                                                                                                                                                         
  12 - access("A"."LINE_ITEM_ID"="C"."LINE_ITEM_ID" AND "C"."CATALOG_SUBSET_ID"=576)                                                                                                                     
  13 - filter(UPPER("B"."SHORT_DESC") LIKE '%BLADES%' OR UPPER("B"."SHORT_DESC") LIKE '%                                                                                                                 
              DIAMOND%')                                                                                                                                                                                 
  14 - access("A"."LINE_ITEM_ID"="B"."LINE_ITEM_ID")                                                                                                                                                     
  15 - access("A"."LINE_ITEM_ID"="E"."LINE_ITEM_ID")                                                                                                                                                     
  17 - access("E"."IMAGE_ID"="G"."IMAGE_ID")                                                                                                                                                             
  18 - access("A"."MANUFACTURER_ID"="D"."MANUFACTURER_ID")                                                                                                                                               
                                                                                                                                                                                                         
Note: rule based optimization                                                                                                                                                                            

38 rows selected


I am not quite sure about the index IX_CATALOG_SUBSET is in desc mode as , the schema and the structure is in existence from long back.

Thanks.
Re: Optimizing the query containing 7 table joins [message #427396 is a reply to message #427390] Thu, 22 October 2009 06:49 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

The stats are up to date. Just to confirm i ran the count queries on the tables used in the Main query and obtained the following result.


No statistics are not upto date.

You can verify by selecting the LAST_ANALYZED from the USER_TABLES for the tables being used
also, you can look at USER_INDEXES for Indexes.

Verify if the plan looks good/and also the response time


If not, Drop the index suggested in the previous reply.
Re: Optimizing the query containing 7 table joins [message #427500 is a reply to message #427396] Thu, 22 October 2009 23:41 Go to previous messageGo to next message
manzoor14
Messages: 5
Registered: October 2009
Junior Member
the following are the results for the last analyzed query

20-OCT-09 LINE_ITEM
20-OCT-09 LINE_ITEM_DOCUMENT
17-OCT-09 LINE_ITEM_IMAGE
20-OCT-09 LINE_ITEM_PRICE

so i guess the stats are up to date. Please let me know if i need to do anything more.

Thanks,
Manzoor Elahi
Re: Optimizing the query containing 7 table joins [message #427539 is a reply to message #426926] Fri, 23 October 2009 03:17 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Try dropping IX_CATALOG_SUBSET.
Re: Optimizing the query containing 7 table joins [message #427548 is a reply to message #427500] Fri, 23 October 2009 04:01 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're having a bit of a problem supplying complete sets of data, aren't you.

The tables that your query uses are:
line_item
loc_line_item
line_item_price
loc_manufacturer
line_item_image
loc_product 
image

You've confirmed that over half of them have recent statistics - what about the rest?

As I said before, you're missing an index on LOC_PRODUCT(PRODUCT_ID) - that should help you avoid the FTS on Loc_Product
Previous Topic: regarding session time out
Next Topic: Performance on single query
Goto Forum:
  


Current Time: Sun Sep 25 09:27:28 CDT 2016

Total time taken to generate the page: 0.14757 seconds