Home » RDBMS Server » Performance Tuning » HOW to tune this query
| HOW to tune this query [message #382333] |
Wed, 21 January 2009 23:47  |
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  |
 |
BlackSwan
Messages: 26766
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 |
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 664 times)
|
|
|
|
| Re: HOW to tune this query [message #382355 is a reply to message #382333] |
Thu, 22 January 2009 00:39 |
trivendra
Messages: 211
Registered: October 2007
Location: Phoenix
|
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 |
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 |
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?
|
|
|
|
Goto Forum:
Current Time: Sat Jun 01 22:29:56 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
