| Help - ToTune This Query.... [message #149218] |
Wed, 30 November 2005 10:32  |
sameer9448
Messages: 6
Registered: November 2005
|
Junior Member |
|
|
Hi Exeperts,
Can anyone help me to TUNE the below query. The cost of query is 55000+. When i add the condition pnd.packing_notes_id = 16985549 , the cost is very less i.e 48. Proper index exist in Primary Key & Foreing Keys.
SELECT count(1) A
FROM
logistics.arrival_groups ag,
dispatch_labels dl,
dispatch_note_details dd,
styles s,
style_colls sc,
sysmodule.companies comp,
sysmodule.departments d,
orders o,
customer_groups cg2,
order_details od,
logistics.packing_note_details pnd,
customer_groups cg,
temp_distributions td,
logistics.transport_advis ta,
arrivals ar
WHERE
ag.arrival_groups_id = ar.arrival_groups_id
---------------------------------------------------------------------------------
AND (dl.status = 'PRINTED' OR dd.purchase_no IS NULL)
AND dl.dispatch_notes_id(+) = dd.dispatch_notes_id
AND dd.purchase_no(+) = o.order_number
---------------------------------------------------------------------------------
AND s.deleted = 'N'
AND s.styles_id = sc.styles_id
AND sc.style_colls_id = o.style_colls_id
AND comp.deleted = 'N'
AND comp.companies_id = o.companies_id_2
---------------------------------------------------------------------------------
AND d.deleted = 'N'
AND d.departments_id = o.departments_id
---------------------------------------------------------------------------------
AND o.deleted = 'N'
AND o.orders_id = od.orders_id
---------------------------------------------------------------------------------
AND cg2.deleted = 'N'
AND cg2.customer_groups_id = od.customer_groups_id
AND od.order_details_id = pnd.order_details_id
---------------------------------------------------------------------------------
--AND pnd.packing_notes_id = 16985549
AND pnd.packing_note_details_id = ta.packing_note_details_id
---------------------------------------------------------------------------------
--AND cg.group_name = 'NORWAY'
--AND ((:P_CUSTOMERGROUPS_ID is null) or (upper(:P_CUSTOMERGROUPS_ID)
-- = upper(cg.GROUP_NAME) ) )
AND cg.deleted = 'N'
AND ( cg.customer_groups_id = td.customer_groups_id
OR cg.customer_groups_id = od.customer_groups_id )
---------------------------------------------------------------------------------
AND td.packing_note_details_id(+) = ta.packing_note_details_id
---------------------------------------------------------------------------------
AND ta.deleted = 'N'
AND ta.transport_advis_id = ar.transport_advis_id
---------------------------------------------------------------------------------
AND ar.arrival_date BETWEEN '01-JUN-2005' AND '31-DEC-2005'
---------------------------------------------------------------------------------
GROUP BY
cg.group_name,
ar.arrival_date,
o.order_number,
d.department_name,
s.style_name,
s.style_number,
od.customer_groups_id,
cg2.group_name,
ta.order_number,
od.order_details_id,
od.line_number,
cg.customer_groups_id,
dl.TO_NUMBER,
dl.from_number,
pnd.packing_notes_id
Following tables are accessed full by the query
transport_advis
order_details
packing_note_details
departments
orders
companies
Reegards
Sameer Kulkarni
|
|
|
|
| Re: Help - ToTune This Query.... [message #149254 is a reply to message #149218] |
Wed, 30 November 2005 20:17  |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
Wow, that is quite a query! Try using the plan table to see what the optimizer is trying to do.
Try indexing each table with the columns in the same order that you used in the 'where' part of the query. (i.e. if you said in your where clause 'where ag.arrival_groups_id = ar.arrival_groups_id' then put an index on ag and ar that has arrival_groups_id in it as the first segment of that index).
Avoid full table scans if the cardinality of the table is large.
Some where clauses have functions in them that increase the likelyhood of full table scans, like the 'nval' function.
Put the data and the indexes in tablespaces that live on seperate disks. (i.e. data_01 tablespace using a datafile on /u01 and indx_02 tablespace with a datafile on /u02 - or windows, data on D drive and the indexes on E drive)
You have a lot of group by (sort) in your query. See if you can do that in memory rather than on disk. If you have the physical memory. I think you increase the parameter sort_area_size in some versions.
Check to see that you have enough physical memory to support your sga, pga and processes (no swapping).
Start with a less restritive (not so many) where clause and add to the where clause until you find the one(s) that make it slow.
If all else fails, try breaking the query into pieces by using pl/sql or 3rd party software with api's. You would create a collection using a query and fetch though the collection elminitating rows based on your where clause.
Make sure your query is correct first (returns the correct result set), then worry about it running faster.
Good luck!
|
|
|
|
| Re: Help - ToTune This Query.... [message #149400 is a reply to message #149218] |
Thu, 01 December 2005 08:37 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I only skimmed the above answer, but would like to disagree with the notion of separating index data and table data into separate tablespaces for purely performance reasons. I still have seen no satisfactory reasoning or proof of why this is beneficial.
|
|
|
|
| Re: Help - ToTune This Query.... [message #149492 is a reply to message #149400] |
Fri, 02 December 2005 01:01 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| Quote: |
The cost of query is 55000+
|
Don't tune on cost. Cost is a measure used by the optimizer to compare different plans for a certain query. You cannot use cost to compare different queries.
How many rows does each table contain?
What is the execution plan you get?
Is the query normally executed for a given pnd.packing_notes_id, then add it as a bind
(and pnd.packing_notes_id = :b1)
hth
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
