Home » RDBMS Server » Performance Tuning » SQL tuning (Oracle 9.2)
SQL tuning [message #329866] Thu, 26 June 2008 17:40 Go to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
I am trying to tune the Query but not with much success
All the tables are less 500 records except the view xx_ips_sku_view which has over 300,000 recs. I am attaching the explain plan for reference.

Select
distinct e.id, e.dynamic_seq, h.primary_vendor_id, h.division, h.group_no, h.dept, h.class, h.subclass, h.sku
from xx_ips_basket_dyn_flags e, xx_ips_basket_category ,xx_ips_sku_view h
where g e.id(+) = g.id and e.dynamic_seq = g.dynamic_seq

and (((select count(*) from xx_ips_basket_vendor i where i.id = e.id and i.dynamic_seq = e.dynamic_seq and i.ie_code = 'I') = 0) OR
((h.primary_vendor_id in (select i.vendor from xx_ips_basket_vendor i where i.id = e.id and i.dynamic_seq = e.dynamic_seq and i.ie_code = 'I'))))
and (((select count(*) from xx_ips_basket_category j where j.id = e.id and j.dynamic_seq = e.dynamic_seq and j.ie_code = 'I') = 0) OR
((g.division = '0' or h.division = g.division) and
(g.group_no = '0' or h.group_no = g.group_no) and
(g.dept = '0' or h.dept = g.dept) and
(g.class = '0' or h.class = g.class) and
(g.subclass = '0' or h.subclass = g.subclass)))
and h.sku not in (select k.sku_id from xx_ips_basket_sku_ie k where k.id = e.id and k.dynamic_seq = e.dynamic_seq and k.ie_code = 'E')


Re: SQL tuning [message #329867 is a reply to message #329866] Thu, 26 June 2008 17:45 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

http://www.orafaq.com/forum/t/84315/74940/
which of the suggestions in URL above have you tried & what were the results?

I have doubts about the validity & correctness of the posted SQL.
Where does alias "g" get established in the jumbled & UGLY code?

[Updated on: Thu, 26 June 2008 17:48] by Moderator

Report message to a moderator

Re: SQL tuning [message #330086 is a reply to message #329866] Fri, 27 June 2008 09:57 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
I also find that if we remove distinct the query comes back very quick. Is there a way to get around that. Please help.
Re: SQL tuning [message #330087 is a reply to message #330086] Fri, 27 June 2008 10:00 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
Can one of the sort parameters help?
Re: SQL tuning [message #330094 is a reply to message #330087] Fri, 27 June 2008 10:18 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
chintu00 wrote on Fri, 27 June 2008 08:00
Can one of the sort parameters help?

Since you don't answer my question, I won't answer your question.

You're on Your Own (YOYO)!
Re: SQL tuning [message #330096 is a reply to message #329866] Fri, 27 June 2008 10:19 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
what do you mean wery quickly?
wihout distinct query returns first rows quickly

use create table as statement to insert all record set which returning query with distinct and without distinct. and compare time.

query with distinct must be be slower, bet not dramaticaly slower.
Re: SQL tuning [message #330109 is a reply to message #329867] Fri, 27 June 2008 10:42 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
From clause
xx_ips_basket_category g
xx_ips_basket_dyn_flags e

Where clause
AND e.dynamic_seq = g.dynamic_seq
Re: SQL tuning [message #330122 is a reply to message #330094] Fri, 27 June 2008 11:08 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
The SQL is perfectly correct.
I have formatted it this time.

SELECT DISTINCT e.ID, e.dynamic_seq, h.primary_vendor_id, h.division,
h.group_no, h.dept, h.CLASS, h.subclass, h.sku
FROM xx_ips_basket_dyn_flags e,
xx_ips_basket_category g,
xx_ips_sku_view h,
xx_ips_basket_vendor i
where e.ID(+) = g.ID
AND e.dynamic_seq = g.dynamic_seq
and (((SELECT COUNT (*)
FROM xx_ips_basket_vendor i
WHERE i.ID = e.ID
AND i.dynamic_seq = e.dynamic_seq
AND i.ie_code = 'I') = 0
)
OR ((h.primary_vendor_id = i.vendor
and i.ID = e.ID
AND i.dynamic_seq = e.dynamic_seq
AND i.ie_code = 'I')
)
)

AND ( ((SELECT COUNT (*)
FROM xx_ips_basket_category j
WHERE j.ID = e.ID
AND j.dynamic_seq = e.dynamic_seq
AND j.ie_code = 'I') = 0
)
OR ( (g.division = '0' OR h.division = g.division)
AND (g.group_no = '0' OR h.group_no = g.group_no)
AND (g.dept = '0' OR h.dept = g.dept)
AND (g.CLASS = '0' OR h.CLASS = g.CLASS)
AND (g.subclass = '0' OR h.subclass = g.subclass)
)
)
AND h.sku NOT IN (
SELECT k.sku_id
FROM xx_ips_basket_sku_ie k
WHERE k.ID = e.ID
AND k.dynamic_seq = e.dynamic_seq
AND k.ie_code = 'E')
Re: SQL tuning [message #330139 is a reply to message #329866] Fri, 27 June 2008 11:45 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
It appears we disagree upon what proper formatting is.
SELECT DISTINCT e.Id,
                e.Dynamic_seq,
                h.Primary_Vendor_Id,
                h.Division,
                h.Group_No,
                h.dept,
                h.Class,
                h.SubClass,
                h.sku
FROM   xx_ips_Basket_dyn_Flags e,
       xx_ips_Basket_Category g,
       xx_ips_sku_View h,
       xx_ips_Basket_Vendor i
WHERE  e.Id (+)  = g.Id
       AND e.Dynamic_seq = g.Dynamic_seq
       AND (((SELECT COUNT(* )
              FROM   xx_ips_Basket_Vendor i
              WHERE  i.Id = e.Id
                     AND i.Dynamic_seq = e.Dynamic_seq
                     AND i.ie_Code = 'I') = 0)
             OR ((h.Primary_Vendor_Id = i.Vendor
                  AND i.Id = e.Id
                  AND i.Dynamic_seq = e.Dynamic_seq
                  AND i.ie_Code = 'I')))
       AND (((SELECT COUNT(* )
              FROM   xx_ips_Basket_Category j
              WHERE  j.Id = e.Id
                     AND j.Dynamic_seq = e.Dynamic_seq
                     AND j.ie_Code = 'I') = 0)
             OR ((g.Division = '0'
                   OR h.Division = g.Division)
                 AND (g.Group_No = '0'
                       OR h.Group_No = g.Group_No)
                 AND (g.dept = '0'
                       OR h.dept = g.dept)
                 AND (g.Class = '0'
                       OR h.Class = g.Class)
                 AND (g.SubClass = '0'
                       OR h.SubClass = g.SubClass)))
       AND h.sku NOT IN (SELECT k.sku_Id
                         FROM   xx_ips_Basket_sku_ie k
                         WHERE  k.Id = e.Id
                                AND k.Dynamic_seq = e.Dynamic_seq
                                AND k.ie_Code = 'E')


Some other Senior Members disagree with the following approach
but In My Opinion xx_ips_Basket_Category g & Xx_ips_Basket_Vendor i
should NOT be in the FROM clause because they contribute no data to the SELECT clause.
Subordinating them into the WHERE might improve performance.

[Updated on: Fri, 27 June 2008 11:49] by Moderator

Report message to a moderator

Re: SQL tuning [message #330145 is a reply to message #330139] Fri, 27 June 2008 11:57 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
thanks for your response. I will evaluate it.
Copying from one editor to another often makes the formatting bad.It also appears differently in message window and when it actually gets posted.
Re: SQL tuning [message #330147 is a reply to message #330145] Fri, 27 June 2008 12:01 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
chintu00 wrote on Fri, 27 June 2008 09:57
thanks for your response. I will evaluate it.
Copying from one editor to another often makes the formatting bad.It also appears differently in message window and when it actually gets posted.


Is clicking the "Preview Message" button beyond your capability?

http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: SQL tuning [message #330273 is a reply to message #329866] Sat, 28 June 2008 12:35 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
I tried to rewrite query in ANSI SQL
but I think, that something is wrong with your query algorithm.


query can be tuned by precalculating counts for all cases
like
LEFT JOIN  (SELECT j.Id, j.Dynamic_seq, COUNT(1) cnt
           FROM   xx_ips_Basket_Category j
           WHERE j.ie_Code = 'I'
           group by j.Id, j.Dynamic_seq) v ON     v.id = e.id 
                                              AND v.Dynamic_seq = e.Dynamic_seq

and then use HASH join to join table and inline view, and after that filter result set.

but I think, mots poroblems are in this part of query
LEFT JOIN  xx_ips_sku_View h               ON     (h.Division = g.Division OR g.Division=0 )
                                              AND (h.Group_No = g.Group_No OR g.Group_No=0)
                                              AND (h.dept = g.dept         OR g.dept=0)
                                              AND (h.Class = g.Class       OR g.Class=0)
                                              AND (h.SubClass = g.SubClass OR g.SubClass=0)


Like you wrote, xx_ips_sku_View has 300 000 records.
and every time when g.Division=0 and g.Group_No=0 and g.dept=0 and g.Class=0 and g.SubClass=0 ju join on every record in table h all 300 000 table g records.

so if I think that if you remove DISTINC from select, you wil get hundred times (or more) larger recorset compare to the recordset you geting using DISTINCT.


SELECT DISTINCT e.Id,
                e.Dynamic_seq,
                h.Primary_Vendor_Id,
                h.Division,
                h.Group_No,
                h.dept,
                h.Class,
                h.SubClass,
                h.sku
FROM xx_ips_Basket_Category g

LEFT JOIN  xx_ips_Basket_dyn_Flags e       ON     e.Id = g.Id  
                                              AND e.Dynamic_seq = g.Dynamic_seq
                                              
LEFT JOIN (SELECT i.Id, i.Dynamic_seq, COUNT(1) cnt 
           from xx_ips_Basket_Vendor i
           WHERE  I.ie_Code = 'I' 
           group by i.Id, i.Dynamic_seq) z ON     e.id = z.id 
                                              AND e.Dynamic_seq = z.Dynamic_seq
                                              
LEFT JOIN  (SELECT j.Id, j.Dynamic_seq, COUNT(1) cnt
           FROM   xx_ips_Basket_Category j
           WHERE j.ie_Code = 'I'
           group by j.Id, j.Dynamic_seq) v ON     v.id = e.id 
                                              AND v.Dynamic_seq = e.Dynamic_seq
                                              
LEFT JOIN  xx_ips_sku_View h               ON     (h.Division = g.Division OR g.Division=0 )
                                              AND (h.Group_No = g.Group_No OR g.Group_No)
                                              AND (h.dept = g.dept         OR g.dept=0)
                                              AND (h.Class = g.Class       OR g.Class=0)
                                              AND (h.SubClass = g.SubClass OR g.SubClass=0)
                                              
LEFT JOIN xx_ips_Basket_sku_ie k           ON     h.sku = k.sku_Id 
                                              AND k.Id = e.Id 
                                              AND k.Dynamic_seq = e.Dynamic_seq 
                                              AND k.ie_Code = 'E'        
                                              
LEFT JOIN  xx_ips_Basket_Vendor i          ON     h.Primary_Vendor_Id = i.Vendor 
                                              AND v.id = e.id 
                                              AND v.Dynamic_seq = e.Dynamic_seq         
                                              
WHERE     e.Dynamic_seq IS NOT NULL
      AND k.sku_Id IS NULL
      AND (NVL(z.cnt,0)=0 OR i.Vendor IS NOT NULL)
      AND (NVL(j.cnt,0)=0 OR h.Division IS NOT NULL)



You need to explain what you selecting, from what tables, what you need to get in result, and etc.
You need to rewrite query joins !!
Re: SQL tuning [message #330610 is a reply to message #330273] Mon, 30 June 2008 10:12 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
Yes without distinct I get much larger recordset.
There are 2 counts both of them use tables xx_ips_basket_dyn_flags to compute their counts along with one other table
xx_ips_basket_category is in the outer from clause but with xx_ips_basket_vendor is not. Do you think it is good to
move the table xx_ips_basket_vendor to the outer SQL as it may result in scan larger number of records.

I think I originally did not give the correct SQL I apologize
the join should be

FROM xx_ips_Basket_dyn_Flags e
LEFT JOIN
xx_ips_Basket_Category g ON e.Id = g.Id
AND e.Dynamic_seq = g.Dynamic_seq

I appreciate all your effort and advice.
Re: SQL tuning [message #330634 is a reply to message #329866] Mon, 30 June 2008 12:08 Go to previous message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
I made LEFT JOIN of "count" queries, becouse I want to precalculate counts for all Id and Dynamic_seq combinations.
when use hash join.
if there is no Id and Dynamic_seq combination that exist in table e, with inner join you will eliminate columns from table e.
so I use statament in where clause
AND (NVL(z.cnt,0)=0 OR i.Vendor IS NOT NULL)
AND (NVL(j.cnt,0)=0 OR h.Division IS NOT NULL)


Like I said, please look at this part
LEFT JOIN  xx_ips_sku_View h               ON     (h.Division = g.Division OR g.Division=0 )
                                              AND (h.Group_No = g.Group_No OR g.Group_No)
                                              AND (h.dept = g.dept         OR g.dept=0)
                                              AND (h.Class = g.Class       OR g.Class=0)
                                              AND (h.SubClass = g.SubClass OR g.SubClass=0)



do you really need such join, maybe there is some other posibilities to join. What is purpose of that join in your query ?

in ANSI SQL writing stile you can fast identify problems of output record grow
please use such thing:

run select
select count(1)
FROM xx_ips_Basket_Category g

save somethere number of counts ant execution time
after that run
FROM xx_ips_Basket_Category g

LEFT JOIN  xx_ips_Basket_dyn_Flags e       ON     e.Id = g.Id  
                                              AND e.Dynamic_seq = g.Dynamic_seq

save number of counts and execution time
and grow query like that..
then you can see at what place there is problems
you can examine at each step execution plan, and tune it.
you can identify on what part is huge output recorset count increase, and look at the joins to eliminate that.







Previous Topic: Tuning a query
Next Topic: reduce the wait event for db file sequential read event
Goto Forum:
  


Current Time: Sun Dec 11 07:58:50 CST 2016

Total time taken to generate the page: 0.07292 seconds