Home » RDBMS Server » Performance Tuning » HOW to tune this query
HOW to tune this query [message #382333] Wed, 21 January 2009 23:47 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
We have a query like this.results are coming perfectly fine.but response time is more.i have created index on COUNTRY_REF_NO in EMPLOYEE_ITEMS table but still it is taking more time.how to tune this query.without using INLINE query can i rewrite this query.


Please note that i can't send explain plan cos i have duplicated original query with dummy tables.


SELECT   ROWNUM LINE_NO,
         T."COUNTRY_TYPE",
         T."COUNTRY",
         T."COUNTRYREFNO",
         T."TOTALSUITES",
         T.COUNT,
	 T."TDNO",
FROM     (SELECT   MASTR.COUNTRY_TYPE COUNTRY_TYPE,
                   MASTR.COUNTRY_NO COUNTRY,
                   OUTITEMS.COUNTRY_REF_NO COUNTRYREFNO,
                   SUM (OUTITEMS.NO_OF_SUITES) TOTALSUITES,
                   DECODE ((SELECT COUNT (INVITEMS.ID)
                            FROM   INVOICE INV,
                                   INVOICE_ITEMS INVITEMS
                            WHERE  INVITEMS.INV_ID = INV.ID
                            AND    INVITEMS.COUNTRY_REF_NO = OUTITEMS.COUNTRY_REF_NO),
                           (SELECT COUNT (ITEMS1.ID)
                            FROM   EMPLOYEE EMP,
                                   EMPLOYEE_ITEMS INNERITEMS
                            WHERE  INNERITEMS.COUNTRY_REF_NO = OUTITEMS.COUNTRY_REF_NO
                            AND    EMP.ID = INNERITEMS.EMP_ID), '1',
                           '0') COUNT,
        DECODE ((SELECT COUNT (DISTINCT TD.ID)
                            FROM        DOCUMENT TD,
                                        EMPLOYEE EMP,
                                        EMPLOYEE_ITEMS EMPITEMS
                                 WHERE  EMPITEMS.COUNTRY_REF_NO = OUTITEMS.COUNTRY_REF_NO
                                 AND    EMP.ID = EMPITEMS.EMP_ID
                                 AND    TD.ID = EMP.REF_ID_1),
                           '1', (SELECT TD.TD_NO
                                 FROM   DOCUMENT TD,
                                        EMPLOYEE EMP,
                                        EMPLOYEE_ITEMS EMPITEMS
                                 WHERE  EMPITEMS.COUNTRY_REF_NO =OUTITEMS.COUNTRY_REF_NO
                                 AND    EMP.ID = EMPITEMS.EMP_ID
                                 AND    TD.ID = EMP.REF_ID_1
                                 AND    ROWNUM = 1),
                           '') TDNO,
          FROM     EMPLOYEE_ITEMS OUTITEMS,
                   MSTR_COUNTRIES MASTR,
                   EMPLOYEE OUTEMP
          WHERE    OUTITEMS.COUNTRY_REF_NO = MASTR.ID
          AND      OUTITEMS.EMP_ID = OUTEMP.ID
          AND      OUTEMP.STATUS != 'CANCEL'
          AND      OUTITEMS.REF_1 in ('OPEN')
          GROUP BY MASTR.COUNTRY_TYPE,
                   MASTR.COUNTRY_NO,
                   MASTR.COUNTRY_REF_NO) T
WHERE    T.COUNT <> '0'

Re: HOW to tune this query [message #382334 is a reply to message #382333] Wed, 21 January 2009 23:55 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You have tables & provide us no DDL.
You have data & provide use no DML.
You have actual EXPLAIN PLAN & obscure reality with dummy tables.
This post is waste of time for anyone to try to guess what is real & how to make it better.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for real tables.
Post DML for actual test data.

You do NOT tune SQL simply by looking at the query; otherwise you could/would have done so already.

Otherwise You're On Your Own (YOYO)!

[Updated on: Wed, 21 January 2009 23:59]

Report message to a moderator

Re: HOW to tune this query [message #382352 is a reply to message #382333] Thu, 22 January 2009 00:35 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
find the tables and attached explain plan gif.

SELECT   ROWNUM LINE_NO,
         T."COUNTRY_TYPE",
         T."COUNTRY",
         T."COUNTRYREFNO",
         T."TOTALSUITES",
         T.COUNT,
	 T."TDNO"
FROM     (SELECT   MASTR.COUNTRY_TYPE COUNTRY_TYPE,
                   MASTR.COUNTRY_NO COUNTRY,
                   OUTITEMS.COUNTRY_REF_NO COUNTRYREFNO,
                   SUM (OUTITEMS.NO_OF_SUITES) TOTALSUITES,
                   DECODE ((SELECT COUNT (INVITEMS.ID)
                            FROM   INVOICE INV,
                                   INVOICE_ITEMS INVITEMS
                            WHERE  INVITEMS.INV_ID = INV.ID
                            AND    INVITEMS.COUNTRY_REF_NO = OUTITEMS.COUNTRY_REF_NO),
                           (SELECT COUNT (INNERITEMS.ID)
                            FROM   EMPLOYEE EMP,
                                   EMPLOYEE_ITEMS INNERITEMS
                            WHERE  INNERITEMS.COUNTRY_REF_NO = OUTITEMS.COUNTRY_REF_NO
                            AND    EMP.ID = INNERITEMS.EMP_ID), '1',
                           '0') COUNT,
        DECODE ((SELECT COUNT (DISTINCT TD.ID)
                            FROM        DOCUMENT TD,
                                        EMPLOYEE EMP,
                                        EMPLOYEE_ITEMS EMPITEMS
                                 WHERE  EMPITEMS.COUNTRY_REF_NO = OUTITEMS.COUNTRY_REF_NO
                                 AND    EMP.ID = EMPITEMS.EMP_ID
                                 AND    TD.ID = EMP.REF_ID_1),
                           '1', (SELECT TD.TD_NO
                                 FROM   DOCUMENT TD,
                                        EMPLOYEE EMP,
                                        EMPLOYEE_ITEMS EMPITEMS
                                 WHERE  EMPITEMS.COUNTRY_REF_NO =OUTITEMS.COUNTRY_REF_NO
                                 AND    EMP.ID = EMPITEMS.EMP_ID
                                 AND    TD.ID = EMP.REF_ID_1
                                 AND    ROWNUM = 1),
                           '') TDNO
          FROM     EMPLOYEE_ITEMS OUTITEMS,
                   MSTR_COUNTRIES MASTR,
                   EMPLOYEE OUTEMP
          WHERE    OUTITEMS.COUNTRY_REF_NO = MASTR.ID
          AND      OUTITEMS.EMP_ID = OUTEMP.ID
          AND      OUTEMP.STATUS != 'CANCEL'
          AND      OUTITEMS.REF_1 in ('OPEN')
          GROUP BY MASTR.COUNTRY_TYPE,
                   MASTR.COUNTRY_NO,
                   OUTITEMS.COUNTRY_REF_NO) T
WHERE    T.COUNT <> '0';

tables 
*****

create table INVOICE
(
  ID         NUMBER,
  INVOICE_NO VARCHAR2(15)
)
create table INVOICE_ITEMS
(
  ID             NUMBER,
  INV_ID         NUMBER,
  COUNTRY_REF_NO VARCHAR2(15)
)

create table EMPLOYEE
(
  ID       NUMBER,
  EMPNO    VARCHAR2(15),
  STATUS   VARCHAR2(15),
  REF_ID_1 NUMBER
)

create table EMPLOYEE_ITEMS
(
  ID             NUMBER,
  EMP_ID         NUMBER,
  COUNTRY_REF_NO VARCHAR2(15),
  NO_OF_SUITES   NUMBER,
  REF_1          VARCHAR2(15)
)

create table DOCUMENT
(
  ID    NUMBER,
  TD_NO VARCHAR2(18)
)

create table MSTR_COUNTRIES
(
  ID           VARCHAR2(15),
  COUNTRY_TYPE VARCHAR2(15),
  COUNTRY_NO   VARCHAR2(15)
)
  • Attachment: 1.GIF
    (Size: 45.25KB, Downloaded 131 times)
Re: HOW to tune this query [message #382355 is a reply to message #382333] Thu, 22 January 2009 00:39 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Rewrite the query to avoide the SQL query in DECODE. More over you are using the Table EMPLOYEE_ITEMS thrice.

Get the Plan for the table and look what is taking more time.

Re: HOW to tune this query [message #382373 is a reply to message #382355] Thu, 22 January 2009 01:10 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3258944768

--------------------------------------------------------------------------------
------------

| Id  | Operation                 | Name           | Rows  | Bytes | Cost (%CPU)
| Time     |

--------------------------------------------------------------------------------
------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                |     1 |    53 |   235   (6)
| 00:00:03 |

|   1 |  COUNT                    |                |       |       |
|          |

|   2 |   VIEW                    |                |     1 |    53 |   235   (6)
| 00:00:03 |

|   3 |    HASH GROUP BY          |                |     1 |    93 |   235   (6)
| 00:00:03 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|*  4 |     FILTER                |                |       |       |
|          |

|*  5 |      HASH JOIN            |                |   300 | 27900 |   163   (5)
| 00:00:02 |

|*  6 |       HASH JOIN           |                |   300 | 21300 |   112   (5)
| 00:00:02 |

|   7 |        TABLE ACCESS FULL  | MSTR_COUNTRIES |   197 |  5319 |     3   (0)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 00:00:01 |

|*  8 |        TABLE ACCESS FULL  | EMPLOYEE_ITEMS |  5844 |   251K|   109   (5)
| 00:00:02 |

|*  9 |       TABLE ACCESS FULL   | EMPLOYEE       | 44503 |   956K|    50   (4)
| 00:00:01 |

|  10 |      SORT AGGREGATE       |                |     1 |    48 |
|          |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 11 |       HASH JOIN           |                |  1002 | 48096 |    71   (8)
| 00:00:01 |

|* 12 |        TABLE ACCESS FULL  | INVOICE_ITEMS  |  1002 | 35070 |    57   (8)
| 00:00:01 |

|  13 |        TABLE ACCESS FULL  | INVOICE        | 13880 |   176K|    13   (0)
| 00:00:01 |

|  14 |        SORT AGGREGATE     |                |     1 |    48 |
|          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|* 15 |         HASH JOIN         |                |  1225 | 58800 |   159   (5)
| 00:00:02 |

|* 16 |          TABLE ACCESS FULL| EMPLOYEE_ITEMS |  1225 | 42875 |   109   (5)
| 00:00:02 |

|  17 |          TABLE ACCESS FULL| EMPLOYEE       | 44827 |   569K|    49   (3)
| 00:00:01 |

--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------


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

   4 - filter(DECODE( (SELECT /*+ */ COUNT("INVITEMS"."ID") FROM "INVOICE_ITEMS"


              "INVITEMS","INVOICE" "INV" WHERE "INVITEMS"."INV_ID"="INV"."ID" AN
D

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

              "INVITEMS"."COUNTRY_REF_NO"=:B1), (SELECT /*+ */ COUNT("INNERITEMS
"."ID") FROM

              "EMPLOYEE_ITEMS" "INNERITEMS","EMPLOYEE" "EMP" WHERE
              "EMP"."ID"="INNERITEMS"."EMP_ID" AND "INNERITEMS"."COUNTRY_REF_NO"
=:B2),'1','0')<>'0

              ')
   5 - access("OUTITEMS"."EMP_ID"="OUTEMP"."ID")
   6 - access("OUTITEMS"."COUNTRY_REF_NO"="MASTR"."ID")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   8 - filter("OUTITEMS"."COUNTRY_REF_NO" IS NOT NULL AND "OUTITEMS"."REF_1"='OP
EN')

   9 - filter("OUTEMP"."STATUS"<>'CANCEL')
  11 - access("INVITEMS"."INV_ID"="INV"."ID")
  12 - filter("INVITEMS"."COUNTRY_REF_NO"=:B1)
  15 - access("EMP"."ID"="INNERITEMS"."EMP_ID")
  16 - filter("INNERITEMS"."COUNTRY_REF_NO"=:B1)

Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - dynamic sampling used for this statement

46 rows selected.

SQL> 
Re: HOW to tune this query [message #382645 is a reply to message #382333] Fri, 23 January 2009 06:55 Go to previous message
chaituu
Messages: 115
Registered: June 2008
Senior Member
i have concerns abot this post.
can anybody tell me is anything wrong with the query i have written above?i have created index on COUNTRY_REF_NO in EMPLOYEE_ITEMS table.i have analyzed all the tables i have been using in this query.other than these things can't i rewrite query to improve the performance?stll any information require please ask me?
Previous Topic: Io cost too high
Next Topic: high water mark (merged)
Goto Forum:
  


Current Time: Thu Dec 08 14:42:41 CST 2016

Total time taken to generate the page: 0.21518 seconds