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 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous message
Kevin Meade
Messages: 2101
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
Previous Topic: Performance Due to Buffer Sort
Next Topic: Fine tuning
Goto Forum:
  


Current Time: Sat Dec 03 18:18:02 CST 2016

Total time taken to generate the page: 0.08647 seconds