Home » RDBMS Server » Performance Tuning » need assistance to optimize the query (oracle 10.2.0.3)
| need assistance to optimize the query [message #351737] |
Thu, 02 October 2008 15:19  |
kumar.joy
Messages: 69
Registered: August 2007
Location: morrisville
|
Member |
|
|
Experts,
i am facing problem with query. could you please assist me to optimize the query.
stats are up to date.
it is using right indexes.
SELECT DISTINCT pkg_dev_extract.fb_event2part (a.mfg_partno,
d.mfr_seq_nbr,
'NON-INVOICE',
'Verizon',
'200808'
) ,
a.mfg_partno, '',
pkg_dev_extract.fb_part2invoice ('NON-INVOICE',
'Verizon',
'200808',
a.mfr_name
),
0, 0, 0, 0, 'NON-INVOICE', a.pc_name, a.mfr_name, '200808'
FROM billing.devx_event a, billing.manufacturer_bb d
WHERE a.bds_date_created >= (SELECT TO_DATE ('200808', 'YYYYMM') FROM DUAL)
AND a.bds_date_created <=
(SELECT LAST_DAY (TO_DATE ('200808', 'YYYYMM')) FROM DUAL)
AND a.stage_type = 'NON-INVOICE'
AND a.pc_name = 'Verizon'
AND a.mfr_name = d.mfr_name
AND NOT EXISTS (
SELECT objid
FROM devx_part p
WHERE p.ar_inv_id = 'NON-INVOICE'
AND p.year_month = '200808'
AND p.pc_name = 'Verizon'
AND p.mfg_partno = a.mfg_partno
AND p.mfr_name = a.mfr_name
)
execution plan is :
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 697510110
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 75477 | 16M| | 135K (1)| 00:27:01 | | |
| 1 | HASH UNIQUE | | 75477 | 16M| 160M| 135K (1)| 00:27:01 | | |
|* 2 | HASH JOIN RIGHT ANTI | | 624K| 133M| | 121K (1)| 00:24:23 | | |
| 3 | TABLE ACCESS BY INDEX ROWID | DEVX_PART | 1 | 149 | | 4 (0)| 00:00:01 | | |
|* 4 | INDEX RANGE SCAN | DEVX_PART_NU3 | 1 | | | 3 (0)| 00:00:01 | | |
|* 5 | HASH JOIN | | 624K| 45M| | 121K (1)| 00:24:23 | | |
| 6 | TABLE ACCESS FULL | MANUFACTURER_BB | 4523 | 106K| | 29 (0)| 00:00:01 | | |
| 7 | TABLE ACCESS BY GLOBAL INDEX ROWID| DEVX_EVENT | 626K| 31M| | 121K (1)| 00:24:23 | ROWID | ROWID |
|* 8 | INDEX RANGE SCAN | DEVX_EVENT_IDX | 1877K| | | 19373 (1)| 00:03:53 | | |
| 9 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
| 10 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$6590669A
3 - SEL$6590669A / P@SEL$4
4 - SEL$6590669A / P@SEL$4
6 - SEL$6590669A / D@SEL$1
7 - SEL$6590669A / A@SEL$1
8 - SEL$6590669A / A@SEL$1
9 - SEL$2 / DUAL@SEL$2
10 - SEL$3 / DUAL@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."MFG_PARTNO"="A"."MFG_PARTNO" AND "P"."MFR_NAME"="A"."MFR_NAME")
4 - access("P"."YEAR_MONTH"='200808' AND "P"."PC_NAME"='Verizon' AND "P"."AR_INV_ID"='NON-INVOICE')
5 - access("A"."MFR_NAME"="D"."MFR_NAME")
8 - access("A"."BDS_DATE_CREATED">= (SELECT TO_DATE('2008-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') FROM "SYS"."DUAL"
"DUAL") AND "A"."STAGE_TYPE"='NON-INVOICE' AND "A"."PC_NAME"='Verizon' AND "A"."BDS_DATE_CREATED"<= (SELECT
TO_DATE('2008-08-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') FROM "SYS"."DUAL" "DUAL"))
filter("A"."STAGE_TYPE"='NON-INVOICE' AND "A"."PC_NAME"='Verizon')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=12) "PKG_DEV_EXTRACT"."FB_EVENT2PART"("A"."MFG_PARTNO","D"."MFR_SEQ_NBR",'NON-INVOICE','Verizon','200808')[
4000], "A"."MFG_PARTNO"[VARCHAR2,90], ''[0], "PKG_DEV_EXTRACT"."FB_PART2INVOICE"('NON-INVOICE','Verizon','200808',"A"."MF
R_NAME")[4000], 0[1], 0[1], 0[1], 0[1], 'NON-INVOICE'[11], "A"."PC_NAME"[VARCHAR2,720], "A"."MFR_NAME"[VARCHAR2,720],
'200808'[6]
2 - (#keys=2) "A"."MFG_PARTNO"[VARCHAR2,90], "A"."MFR_NAME"[VARCHAR2,720], "D"."MFR_SEQ_NBR"[NUMBER,22],
"A"."PC_NAME"[VARCHAR2,720]
3 - "P"."MFG_PARTNO"[VARCHAR2,90], "P"."MFR_NAME"[VARCHAR2,720]
4 - "P".ROWID[ROWID,10]
5 - (#keys=1) "A"."MFR_NAME"[VARCHAR2,720], "D"."MFR_SEQ_NBR"[NUMBER,22], "A"."PC_NAME"[VARCHAR2,720],
"A"."MFG_PARTNO"[VARCHAR2,90]
6 - "D"."MFR_NAME"[VARCHAR2,720], "D"."MFR_SEQ_NBR"[NUMBER,22]
7 - "A"."PC_NAME"[VARCHAR2,720], "A"."MFR_NAME"[VARCHAR2,720], "A"."MFG_PARTNO"[VARCHAR2,90]
8 - "A".ROWID[ROWID,10], "A"."PC_NAME"[VARCHAR2,720]
57 rows selected.
|
|
|
|
| Re: need assistance to optimize the query [message #351760 is a reply to message #351737] |
Thu, 02 October 2008 19:57  |
kumar.joy
Messages: 69
Registered: August 2007
Location: morrisville
|
Member |
|
|
one more information i have,
we drop some old partition in the database by using
alter table <table_name>
drop partition <partition_name>
update global index parallel 10
will it cause the performance hurt of the select query. my query is not looking data in that old partitions.
|
|
|
|
| Re: need assistance to optimize the query [message #351806 is a reply to message #351760] |
Fri, 03 October 2008 04:56 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
What are the columns of index DEVX_EVENT_IDX (list them in order)?
How many rows are in DEVX_PART?
How many of them have ar_inv_id = 'NON-INVOICE' and year_month <= '2008-08-31' and p.pc_name = 'Verizon'?
How does it perform if you do not select the user-defined functions?
Ross Leishman
[Updated on: Fri, 03 October 2008 04:59] Report message to a moderator |
|
|
|
| Re: need assistance to optimize the query [message #352009 is a reply to message #351737] |
Sat, 04 October 2008 21:09 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Ross is throwing you an excellent hint here. Let me be more direct at the expense of stealing Ross's glory. When you are faced with a query that is giving you performance problems, one of the tuning techniques you should be employing is incremental costing. Here is an example of this technique using your query:
--
-- when faced with a performance problem query, it is often effective to check the performance of the query in steps
-- begin with a starter query that is only a very small portion of the original query
-- then add in pieces one at a time in order to see the cost of each piece as it is added in
--
-- with a little luck you will pinpoint those portions of the query that are most expensive
-- once you know which pieces of your query are costing you the most, you can focus your tuning effort in the right place
--
-- You should run all these queries back to back
-- then throw out the results and run them all again right away, it is this second set of timings/resource stats you want to start with
--
--
-- a starter query
--
SELECT a.mfg_PartNo,
a.pc_Name,
a.mfr_Name,
FROM BillIng.devx_Event a,
WHERE a.bds_Date_Created >= (SELECT To_date('200808','YYYYMM') FROM Dual)
AND a.bds_Date_Created <= (SELECT Last_day(To_date('200808','YYYYMM')) FROM Dual)
AND a.Stage_Type = 'NON-INVOICE'
AND a.pc_Name = 'Verizon'
/
--
-- add in the table join
--
SELECT a.mfg_PartNo,
a.pc_Name,
a.mfr_Name,
FROM BillIng.devx_Event a,
BillIng.Manufacturer_bb d
WHERE a.bds_Date_Created >= (SELECT To_date('200808','YYYYMM') FROM Dual)
AND a.bds_Date_Created <= (SELECT Last_day(To_date('200808','YYYYMM')) FROM Dual)
AND a.Stage_Type = 'NON-INVOICE'
AND a.pc_Name = 'Verizon'
AND a.mfr_Name = d.mfr_Name
/
--
-- add in the correlated subquery
--
SELECT a.mfg_PartNo,
a.pc_Name,
a.mfr_Name,
FROM BillIng.devx_Event a,
BillIng.Manufacturer_bb d
WHERE a.bds_Date_Created >= (SELECT To_date('200808','YYYYMM') FROM Dual)
AND a.bds_Date_Created <= (SELECT Last_day(To_date('200808','YYYYMM')) FROM Dual)
AND a.Stage_Type = 'NON-INVOICE'
AND a.pc_Name = 'Verizon'
AND a.mfr_Name = d.mfr_Name
AND NOT EXISTS (SELECT p.objId
FROM devx_Part p
WHERE p.ar_Inv_Id = 'NON-INVOICE'
AND p.Year_Month = '200808'
AND p.pc_Name = 'Verizon'
AND p.mfg_PartNo = a.mfg_PartNo
AND p.mfr_Name = a.mfr_Name)
/
--
-- add in one of the function calls in isolation (that means by itself)
--
SELECT a.mfg_PartNo,
'',
pkg_dev_Extract.Fb_part2invoice('NON-INVOICE','Verizon','200808',a.mfr_Name),
0,
0,
0,
0,
'NON-INVOICE',
a.pc_Name,
a.mfr_Name,
'200808'
FROM BillIng.devx_Event a,
BillIng.Manufacturer_bb d
WHERE a.bds_Date_Created >= (SELECT To_date('200808','YYYYMM') FROM Dual)
AND a.bds_Date_Created <= (SELECT Last_day(To_date('200808','YYYYMM')) FROM Dual)
AND a.Stage_Type = 'NON-INVOICE'
AND a.pc_Name = 'Verizon'
AND a.mfr_Name = d.mfr_Name
AND NOT EXISTS (SELECT p.objId
FROM devx_Part p
WHERE p.ar_Inv_Id = 'NON-INVOICE'
AND p.Year_Month = '200808'
AND p.pc_Name = 'Verizon'
AND p.mfg_PartNo = a.mfg_PartNo
AND p.mfr_Name = a.mfr_Name)
/
--
-- take a look at the other function call in isolation
--
SELECT pkg_dev_Extract.Fb_event2part(a.mfg_PartNo,d.mfr_seq_nbr,'NON-INVOICE','Verizon','200808'),
a.mfg_PartNo,
'',
0,
0,
0,
0,
'NON-INVOICE',
a.pc_Name,
a.mfr_Name,
'200808'
FROM BillIng.devx_Event a,
BillIng.Manufacturer_bb d
WHERE a.bds_Date_Created >= (SELECT To_date('200808','YYYYMM') FROM Dual)
AND a.bds_Date_Created <= (SELECT Last_day(To_date('200808','YYYYMM')) FROM Dual)
AND a.Stage_Type = 'NON-INVOICE'
AND a.pc_Name = 'Verizon'
AND a.mfr_Name = d.mfr_Name
AND NOT EXISTS (SELECT p.objId
FROM devx_Part p
WHERE p.ar_Inv_Id = 'NON-INVOICE'
AND p.Year_Month = '200808'
AND p.pc_Name = 'Verizon'
AND p.mfg_PartNo = a.mfg_PartNo
AND p.mfr_Name = a.mfr_Name)
/
--
-- add in both function calls together
--
SELECT pkg_dev_Extract.Fb_event2part(a.mfg_PartNo,d.mfr_seq_nbr,'NON-INVOICE','Verizon','200808'),
a.mfg_PartNo,
'',
pkg_dev_Extract.Fb_part2invoice('NON-INVOICE','Verizon','200808',a.mfr_Name),
0,
0,
0,
0,
'NON-INVOICE',
a.pc_Name,
a.mfr_Name,
'200808'
FROM BillIng.devx_Event a,
BillIng.Manufacturer_bb d
WHERE a.bds_Date_Created >= (SELECT To_date('200808','YYYYMM') FROM Dual)
AND a.bds_Date_Created <= (SELECT Last_day(To_date('200808','YYYYMM')) FROM Dual)
AND a.Stage_Type = 'NON-INVOICE'
AND a.pc_Name = 'Verizon'
AND a.mfr_Name = d.mfr_Name
AND NOT EXISTS (SELECT p.objId
FROM devx_Part p
WHERE p.ar_Inv_Id = 'NON-INVOICE'
AND p.Year_Month = '200808'
AND p.pc_Name = 'Verizon'
AND p.mfg_PartNo = a.mfg_PartNo
AND p.mfr_Name = a.mfr_Name)
/
--
-- add in the distinct
--
SELECT DISTINCT pkg_dev_Extract.Fb_event2part(a.mfg_PartNo,d.mfr_seq_nbr,'NON-INVOICE','Verizon','200808'),
a.mfg_PartNo,
'',
pkg_dev_Extract.Fb_part2invoice('NON-INVOICE','Verizon','200808',a.mfr_Name),
0,
0,
0,
0,
'NON-INVOICE',
a.pc_Name,
a.mfr_Name,
'200808'
FROM BillIng.devx_Event a,
BillIng.Manufacturer_bb d
WHERE a.bds_Date_Created >= (SELECT To_date('200808','YYYYMM') FROM Dual)
AND a.bds_Date_Created <= (SELECT Last_day(To_date('200808','YYYYMM')) FROM Dual)
AND a.Stage_Type = 'NON-INVOICE'
AND a.pc_Name = 'Verizon'
AND a.mfr_Name = d.mfr_Name
AND NOT EXISTS (SELECT p.objId
FROM devx_Part p
WHERE p.ar_Inv_Id = 'NON-INVOICE'
AND p.Year_Month = '200808'
AND p.pc_Name = 'Verizon'
AND p.mfg_PartNo = a.mfg_PartNo
AND p.mfr_Name = a.mfr_Name)
/
Give this a go. Try setting autotrace on in sqlplus and running all these queries. Remember to run them all twice and use the second set of numbers as your first look at their cost.
I was hasty in putting this sql together (quick cut/paste) so if there are syntax errors etc. you will need to work them out. Then let us know what you find.
Good luck, Kevin
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 09:30:56 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
