Home » RDBMS Server » Performance Tuning » Please help me how to improve the performance of this query further. (Oracle 11g)
| Please help me how to improve the performance of this query further. [message #600494] |
Thu, 07 November 2013 06:44  |
 |
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
|
Senior Member |
|
|
Hi All,
Please help me how to improve the performance of this query further.
SELECT TAM.MANF AS MANF,
TAM.MAJOR_LOCATION AS OPTION_LOCATION,
(SELECT LOCATION_DESCRIPTION
FROM BLUEB_TAM_DESCRIPTION
WHERE NT_NUM = TAM.MAJOR_LOCATION AND ROWNUM = 1)
AS LOCATION_DESCRIPTION,
(SELECT LOCATION_DESCRIPTION
FROM BLUEB_TAM_DESCRIPTION
WHERE NT_NUM = TAM.MANF AND ROWNUM = 1)
AS MANF_DESCRIPTION,
TAM.NT_NUM AS SKU,
TAM.REBEAT_GROUP,
TAM_DESCRIPTION.SHORT_DESC AS DESCRIPTION,
(SELECT SHORT_DESC
FROM BLUEB_TAM_DESCRIPTION
WHERE LOCATION_CODE = 'EN'
AND NT_NUM = TAM.NT_NUM
AND LOCATION = TAM.LOCATION
AND ROWNUM = 1)
AS EN_DESCRIPTION,
TAM.DESCRIPTION AS LONG_DESCRIPTION,
PRP.LIST_PRICE,
PRP.COST_PRICE,
TCS.TOT_RANGE AS TOT_RANGE,
TCS.AVG_RANGE AS AVG_RANGE,
REBEATS.DISCOUNTABLE AS DISCOUNTABLE,
TCCC.TCCCIBUTE_KEYWORD AS TCCCIBUTE_KEYWORD,
TCCC.TCCCIBUTE_TERM AS TCCCIBUTE_TERM,
TCCC.QTY AS QTY,
TAM.TAX_CODE,
TAM.MAT_LOCATION,
TAM.OPTICAL_NAME,
TAM.MAT_TYPE,
TAM.MAT_SOURCE
FROM BLUEB_TAM TAM
INNER JOIN
BLUEB_TAM_DESCRIPTION TAM_DESCRIPTION
ON TAM.NT_NUM = TAM_DESCRIPTION.NT_NUM
AND TAM.LOCATION = TAM_DESCRIPTION.LOCATION
AND TAM_DESCRIPTION.LOCATION_CODE = :LOCATION_CODE
INNER JOIN
PRICE_LIST PRP
ON TAM.NT_NUM = PRP.NT_NUM
AND PRP.cates = :cates
AND PRP.MONEY_UNIT = :MONEY_UNIT
INNER JOIN
TAM_REBEAT REBEATS
ON TAM.REBEAT_GROUP = REBEATS.REBEAT_GROUP
AND REBEATS.REBEAT_NAME = :REBEAT_NAME
INNER JOIN
TAM_CATEGORY_STATS TCS
ON TAM.LOCATION = TCS.LOCATION
AND TAM.MANF = TCS.MANF
AND PRP.MONEY_UNIT = TCS.MONEY_UNIT
AND TCS.cates = PRP.cates
LEFT JOIN
TAM_CLASSIFICATIONS TCCC
ON TAM.NT_NUM = TCCC.NT_NUM
WHERE NVL (TAM.DISABLED, 'N') = 'N'
AND SYSDATE BETWEEN NVL (TAM.STRT_DATE, DATE '0001-01-01')
AND NVL (TAM.END_DATE, DATE '3999-12-31')
AND TAM.MANF = :MANF
AND TAM.NT_NUM IN(:NT_NUM)
ORDER BY TAM.NT_NUM;
Plan hash value: 4137741654
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 450 | 18 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | BLUEB_TAM_DESCRIPTION | 2 | 134 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | BLUEB_TAM_DESCRIPTION_N2 | 7 | | 3 (0)| 00:00:01 |
|* 4 | COUNT STOPKEY | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID | BLUEB_TAM_DESCRIPTION | 2 | 134 | 4 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | BLUEB_TAM_DESCRIPTION_N2 | 7 | | 3 (0)| 00:00:01 |
|* 7 | COUNT STOPKEY | | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | BLUEB_TAM_DESCRIPTION | 1 | 82 | 4 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | BLUEB_TAM_DESCRIPTION_U1_MARKT | 1 | | 3 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | | | | |
| 11 | NESTED LOOPS | | 1 | 450 | 18 (0)| 00:00:01 |
| 12 | NESTED LOOPS OUTER | | 1 | 433 | 16 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 1 | 407 | 15 (0)| 00:00:01 |
| 14 | MERGE JOIN OUTER | | 1 | 375 | 12 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | | | | |
| 16 | NESTED LOOPS | | 1 | 343 | 10 (0)| 00:00:01 |
| 17 | MERGE JOIN CARTESIAN | | 1 | 261 | 7 (0)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID | BLUEB_TAM | 1 | 234 | 4 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | BLUEB_TAM_N4 | 1 | | 3 (0)| 00:00:01 |
| 20 | BUFFER SORT | | 1 | 27 | 3 (0)| 00:00:01 |
| 21 | TABLE ACCESS BY INDEX ROWID| PRICE_LIST | 1 | 27 | 3 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | PRICE_LIST | 1 | | 2 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN | BLUEB_TAM_DESCRIPTION_U1_MARKT | 1 | | 2 (0)| 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID | BLUEB_TAM_DESCRIPTION | 1 | 82 | 3 (0)| 00:00:01 |
| 25 | BUFFER SORT | | 8 | 256 | 9 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | TAM_CLASSIFICATIONS_N5 | 8 | 256 | 2 (0)| 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID | TAM_CATEGORY_STATS_B | 1 | 32 | 3 (0)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | TAM_CATEGORY_STATS_N1 | 1 | | 2 (0)| 00:00:01 |
|* 29 | TABLE ACCESS BY INDEX ROWID | TAM_MAIN_CLASSIFICATIONS | 1 | 26 | 1 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | TAM_MAIN_CLASSIFICATIONS_U1 | 1 | | 0 (0)| 00:00:01 |
|* 31 | INDEX RANGE SCAN | TAM_REBEAT_N1 | 1 | | 1 (0)| 00:00:01 |
| 32 | TABLE ACCESS BY INDEX ROWID | TAM_REBEAT | 1 | 17 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
3 - access("NT_NUM"=:B1)
4 - filter(ROWNUM=1)
6 - access("NT_NUM"=:B1)
7 - filter(ROWNUM=1)
9 - access("NT_NUM"=:B1 AND "LOCATION"=:B2 AND "LOCATION_CODE"='EN')
18 - filter(NVL("TAM"."DISABLED",'N')='N')
19 - access("TAM"."MANF"=:MANF AND "TAM"."NT_NUM"=:NT_NUM)
filter(NVL("STRT_DATE",TO_DATE(' 0001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<=SYSDATE@!
AND NVL("END_DATE",TO_DATE(' 3999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))>=SYSDATE@!)
22 - access("PRP"."cates"=:cates AND "PRP"."MONEY_UNIT"=:MONEY_UNIT AND
"PRP"."NT_NUM"=:NT_NUM)
23 - access("TAM_DESCRIPTION"."NT_NUM"=:NT_NUM AND "TAM"."LOCATION"="TAM_DESCRIPTION"."LOCATION" AND
"TAM_DESCRIPTION"."LOCATION_CODE"=:LOCATION_CODE)
26 - access("TCCC"."NT_NUM"(+)=:NT_NUM)
28 - access("CSB"."MANF"=:MANF AND "TAM"."LOCATION"="CSB"."LOCATION" AND "CSB"."MONEY_UNIT"=:MONEY_UNIT AND
"CSB"."cates"=:cates)
29 - filter("TMC"."VALUE"(+)='M')
30 - access("TMC"."LOCATION"(+)=SUBSTR("CSB"."LOCATION",1,INSTR("CSB"."LOCATION",'-')-1) AND
"TMC"."CLASSIFICATION"(+)='SECOND_CLASS')
31 - access("REBEATS"."REBEAT_NAME"=:REBEAT_NAME AND "TAM"."REBEAT_GROUP"="REBEATS"."REBEAT_GROUP")
TAM_CATEGORY_STATS is a view.
Script for this view is
SELECT CSB.CATES,
CSB.MONEY_UNIT,
CSB.MANF,
CSB.LOCATION,
DECODE (TMC.LOCATION, NULL, CSB.TOT_RANGE, 0) TOT_RANGE,
DECODE (TMC.LOCATION, NULL, CSB.AVG_RANGE, 0) AVG_RANGE
FROM TAM_CATEGORY_STATS_B CSB, TAM_MAIN_CLASSIFICATIONS TMC
WHERE SUBSTR (CSB.LOCATION, 1, INSTR (CSB.LOCATION, '-') - 1) = TMC.LOCATION(+)
AND TMC.CLASSIFICATION(+) = 'SECOND_CLASS'
AND TMC.VALUE(+) = 'M';
Indexes created
CREATE INDEX BLUEB_TAM_N4 ON BLUEB_TAM(MANF, NT_NUM, STRT_DATE, END_DATE);
CREATE INDEX BLUEB_TAM_DESCRIPTION_N2 ON BLUEB_TAM_DESCRIPTION(NT_NUM);
CREATE INDEX BLUEB_TAM_DESCRIPTION_U1_MARKT ON BLUEB_TAM_DESCRIPTION(NT_NUM, LOCATION, LOCATION_CODE);
CREATE INDEX PRICE_LIST ON PRICE_LIST(cates, MONEY_UNIT, NT_NUM);
CREATE INDEX TAM_REBEAT_N1 ON TAM_REBEAT(REBEAT_NAME, REBEAT_GROUP);
CREATE INDEX TAM_CLASSIFICATIONS_N5 ON TAM_CLASSIFICATIONS(NT_NUM, CLASSIFICATION_TERM, CLASSIFICATION_KEYWORD, QTY);
CREATE INDEX TAM_CATEGORY_STATS_N1 ON TAM_CATEGORY_STATS_B(MANF, LOCATION, MONEY_UNIT, cates);
CREATE UNIQUE INDEX TAM_MAIN_CLASSIFICATIONS_U1 ON TAM_MAIN_CLASSIFICATIONS(LOCATION, CLASSIFICATION);
Thanks.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Please help me how to improve the performance of this query further. [message #600659 is a reply to message #600657] |
Mon, 11 November 2013 03:04  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
How about first you let us know the trace event results. Since you have bind variables, enable the 10046 trace with level 12.
Follow these steps :
1.
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
2. Give a tracefile identifier to find the file easily :
alter session set tracefile_identifier = 'test_plan1';
3. Execute your SQL.
4. Turn the trace event off :
alter session set events '10046 trace name context off';
5. Find the trace file, follow the VALUE:
SHOW PARAMETER user_dump_dest;
6. Get the tkprof output.
And post the output.
|
|
|
|
| Re: Please help me how to improve the performance of this query further. [message #600669 is a reply to message #600659] |
Mon, 11 November 2013 03:40 |
|
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
|
Senior Member |
|
|
Hi,
I am sending the plan with values.
SELECT TAM.MANF AS MANF,
TAM.MAJOR_LOCATION AS OPTION_LOCATION,
(SELECT LOCATION_DESCRIPTION
FROM BLUEB_TAM_DESCRIPTION
WHERE NT_NUM = TAM.MAJOR_LOCATION AND ROWNUM = 1)
AS LOCATION_DESCRIPTION,
(SELECT LOCATION_DESCRIPTION
FROM BLUEB_TAM_DESCRIPTION
WHERE NT_NUM = TAM.MANF AND ROWNUM = 1)
AS MANF_DESCRIPTION,
TAM.NT_NUM AS SKU,
TAM.REBEAT_GROUP,
TAM_DESCRIPTION.SHORT_DESC AS DESCRIPTION,
(SELECT SHORT_DESC
FROM BLUEB_TAM_DESCRIPTION
WHERE LOCATION_CODE = 'AB'
AND NT_NUM = TAM.NT_NUM
AND LOCATION = TAM.LOCATION
AND ROWNUM = 1)
AS EN_DESCRIPTION,
TAM.DESCRIPTION AS LONG_DESCRIPTION,
PRP.LIST_PRICE,
PRP.COST_PRICE,
TCS.TOT_RANGE AS TOT_RANGE,
TCS.AVG_RANGE AS AVG_RANGE,
REBEATS.DISCOUNTABLE AS DISCOUNTABLE,
TCCC.TCCCIBUTE_KEYWORD AS TCCCIBUTE_KEYWORD,
TCCC.TCCCIBUTE_TERM AS TCCCIBUTE_TERM,
TCCC.QTY AS QTY,
TAM.TAX_CODE,
TAM.MAT_LOCATION,
TAM.OPTICAL_NAME,
TAM.MAT_TYPE,
TAM.MAT_SOURCE
FROM BLUEB_TAM TAM
INNER JOIN
BLUEB_TAM_DESCRIPTION TAM_DESCRIPTION
ON TAM.NT_NUM = TAM_DESCRIPTION.NT_NUM
AND TAM.LOCATION = TAM_DESCRIPTION.LOCATION
AND TAM_DESCRIPTION.LOCATION_CODE = 'AB'
INNER JOIN
PRICE_LIST PRP
ON TAM.NT_NUM = PRP.NT_NUM
AND PRP.cates = 'MTR'
AND PRP.MONEY_UNIT = 'INR'
INNER JOIN
TAM_REBEAT REBEATS
ON TAM.REBEAT_GROUP = REBEATS.REBEAT_GROUP
AND REBEATS.REBEAT_NAME = '567'
INNER JOIN
TAM_CATEGORY_STATS TCS
ON TAM.LOCATION = TCS.LOCATION
AND TAM.MANF = TCS.MANF
AND PRP.MONEY_UNIT = TCS.MONEY_UNIT
AND TCS.cates = PRP.cates
LEFT JOIN
TAM_CLASSIFICATIONS TCCC
ON TAM.NT_NUM = TCCC.NT_NUM
WHERE NVL (TAM.DISABLED, 'N') = 'N'
AND SYSDATE BETWEEN NVL (TAM.STRT_DATE, DATE '0001-01-01')
AND NVL (TAM.END_DATE, DATE '3999-12-31')
AND TAM.MANF = 'U125'
AND TAM.NT_NUM IN((SELECT /*+ CARDINALITY(t, 1) */
COLUMN_VALUE
FROM table (Split('9000,8000,7000,6000', ',')) t))
ORDER BY TAM.NT_NUM;
Plan hash value: 3517061744
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 3 | | | |
|* 1 | COUNT STOPKEY | | 0 | | 0 |00:00:00.01 | 0 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | BLUEB_TAM_DESCRIPTION | 0 | 2 | 0 |00:00:00.01 | 0 | | | |
|* 3 | INDEX RANGE SCAN | BLUEB_TAM_DESCRIPTION_N2 | 0 | 2 | 0 |00:00:00.01 | 0 | | | |
|* 4 | COUNT STOPKEY | | 0 | | 0 |00:00:00.01 | 0 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID | BLUEB_TAM_DESCRIPTION | 0 | 2 | 0 |00:00:00.01 | 0 | | | |
|* 6 | INDEX RANGE SCAN | BLUEB_TAM_DESCRIPTION_N2 | 0 | 2 | 0 |00:00:00.01 | 0 | | | |
|* 7 | COUNT STOPKEY | | 0 | | 0 |00:00:00.01 | 0 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | BLUEB_TAM_DESCRIPTION | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 9 | INDEX RANGE SCAN | BLUEB_TAM_DESCRIPTION_U1_MARKT | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 10 | SORT ORDER BY | | 1 | 1 | 0 |00:00:00.01 | 3 | 1024 | 1024 | |
|* 11 | HASH JOIN SEMI | | 1 | 1 | 0 |00:00:00.01 | 3 | 694K| 694K| 182K (0)|
| 12 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 3 | | | |
| 13 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 3 | | | |
| 14 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 3 | | | |
| 15 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 3 | | | |
| 16 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 3 | | | |
| 17 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 3 | | | |
| 18 | TABLE ACCESS BY INDEX ROWID| PRICE_LIST | 1 | 1 | 0 |00:00:00.01 | 3 | | | |
|* 19 | INDEX RANGE SCAN | PRICE_LIST | 1 | 1 | 0 |00:00:00.01 | 3 | | | |
|* 20 | TABLE ACCESS BY INDEX ROWID| BLUEB_TAM | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 21 | INDEX RANGE SCAN | BLUEB_TAM_N4 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 22 | TABLE ACCESS BY INDEX ROWID | TAM_CATEGORY_STATS_B | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 23 | INDEX RANGE SCAN | TAM_CATEGORY_STATS_N1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 24 | TABLE ACCESS BY INDEX ROWID | TAM_MAIN_CLASSIFICATIONS | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 25 | INDEX UNIQUE SCAN | TAM_MAIN_CLASSIFICATIONS_U1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 26 | TABLE ACCESS BY INDEX ROWID | TAM_REBEAT | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 27 | INDEX RANGE SCAN | TAM_REBEAT_N1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 28 | TABLE ACCESS BY INDEX ROWID | BLUEB_TAM_DESCRIPTION | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 29 | INDEX RANGE SCAN | BLUEB_TAM_DESCRIPTION_U1_MARKT | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 30 | INDEX RANGE SCAN | TAM_CLASSIFICATIONS_N5 | 0 | 3 | 0 |00:00:00.01 | 0 | | | |
| 31 | COLLECTION ITERATOR PICKLER FETCH| SPLIT | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
3 - access("NT_NUM"=:B1)
4 - filter(ROWNUM=1)
6 - access("NT_NUM"=:B1)
7 - filter(ROWNUM=1)
9 - access("NT_NUM"=:B1 AND "LOCATION"=:B2 AND "LOCATION_CODE"='AB')
11 - access("TAM"."NT_NUM"=VALUE(KOKBF$))
19 - access("PRP"."cates"='MTR' AND "PRP"."MONEY_UNIT"='INR')
20 - filter(NVL("TAM"."DISABLED",'N')='N')
21 - access("TAM"."MANF"='U125' AND "TAM"."NT_NUM"="PRP"."NT_NUM")
filter((NVL("STRT_DATE",TO_DATE(' 0001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<=SYSDATE@! AND NVL("END_DATE",TO_DATE('
3999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))>=SYSDATE@!))
23 - access("CSB"."MANF"='U125' AND "TAM"."LOCATION"="CSB"."LOCATION" AND"CSB"."MONEY_UNIT"='INR' AND "CSB"."cates"='MTR')
24 - filter("TMC"."VALUE"='Y')
25 - access("TMC"."CLASS"=SUBSTR("CSB"."LOCATION",1,INSTR("CSB"."LOCATION",'-')-1) AND "TMC"."CLASSIFICATION"(+)='SECOND_CLASS')
27 - access("REBEATS"."REBEAT_NAME"='567' AND "TAM"."REBEAT_GROUP"="REBEATS"."REBEAT_GROUP")
29 - access("TAM"."NT_NUM"="TAM_DESCRIPTION"."NT_NUM" AND "TAM"."LOCATION"="TAM_DESCRIPTION"."LOCATION" AND "TAM_DESCRIPTION"."LOCATION_CODE"='AB')
30 - access("TAM"."NT_NUM"="TCCC"."NT_NUM")
1 - filter(ROWNUM=1)
3 - access("NT_NUM"=:B1)
4 - filter(ROWNUM=1)
6 - access("NT_NUM"=:B1)
7 - filter(ROWNUM=1)
9 - access("NT_NUM"=:B1 AND "LOCATION"=:B2 AND "LOCATION_CODE"='EN')
18 - filter(NVL("TAM"."DISABLED",'N')='N')
19 - access("TAM"."MANF"=:MANF AND "TAM"."NT_NUM"=:NT_NUM)
filter(NVL("STRT_DATE",TO_DATE(' 0001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<=SYSDATE@!
AND NVL("END_DATE",TO_DATE(' 3999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))>=SYSDATE@!)
22 - access("PRP"."cates"=:cates AND "PRP"."MONEY_UNIT"=:MONEY_UNIT AND
"PRP"."NT_NUM"=:NT_NUM)
23 - access("TAM_DESCRIPTION"."NT_NUM"=:NT_NUM AND "TAM"."LOCATION"="TAM_DESCRIPTION"."LOCATION" AND
"TAM_DESCRIPTION"."LOCATION_CODE"=:LOCATION_CODE)
26 - access("TCCC"."NT_NUM"(+)=:NT_NUM)
28 - access("CSB"."MANF"=:MANF AND "TAM"."LOCATION"="CSB"."LOCATION" AND "CSB"."MONEY_UNIT"=:MONEY_UNIT AND
"CSB"."cates"=:cates)
29 - filter("TMC"."VALUE"(+)='M')
30 - access("TMC"."LOCATION"(+)=SUBSTR("CSB"."LOCATION",1,INSTR("CSB"."LOCATION",'-')-1) AND
"TMC"."CLASSIFICATION"(+)='SECOND_CLASS')
31 - access("REBEATS"."REBEAT_NAME"=:REBEAT_NAME AND "TAM"."REBEAT_GROUP"="REBEATS"."REBEAT_GROUP")
Please help me.
Thanks.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Please help me how to improve the performance of this query further. [message #600705 is a reply to message #600687] |
Mon, 11 November 2013 06:31 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
ramya_162 wrote on Mon, 11 November 2013 16:01We don't have any data for these values becoz of that it's coming in .01 minutes.
Are you struggling to get the values for the bind variables? And then trying to generate the execution plan by using those bind values in the query?
If yes, then no need to do that. just follow the steps I have mentioned to generate the trace and with level 12 it will include the bind variables as well.
|
|
|
|
|
|
|
|
| Re: Please help me how to improve the performance of this query further. [message #600785 is a reply to message #600774] |
Tue, 12 November 2013 08:52 |
|
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
|
Senior Member |
|
|
Trace file: wedb1_ora_8123_test_plan1.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
SELECT TAM.MANF AS MANF,
TAM.MAJOR_LOCATION AS OPTION_LOCATION,
(SELECT LOCATION_DESCRIPTION
FROM BLUEB_TAM_DESCRIPTION
WHERE NT_NUM = TAM.MAJOR_LOCATION AND ROWNUM = 1)
AS LOCATION_DESCRIPTION,
(SELECT LOCATION_DESCRIPTION
FROM BLUEB_TAM_DESCRIPTION
WHERE NT_NUM = TAM.MANF AND ROWNUM = 1)
AS MANF_DESCRIPTION,
TAM.NT_NUM AS SKU,
TAM.REBEAT_GROUP,
TAM_DESCRIPTION.SHORT_DESC AS DESCRIPTION,
(SELECT SHORT_DESC
FROM BLUEB_TAM_DESCRIPTION
WHERE LOCATION_CODE = 'AB'
AND NT_NUM = TAM.NT_NUM
AND LOCATION = TAM.LOCATION
AND ROWNUM = 1)
AS EN_DESCRIPTION,
TAM.DESCRIPTION AS LONG_DESCRIPTION,
PRP.LIST_PRICE,
PRP.COST_PRICE,
TCS.TOT_RANGE AS TOT_RANGE,
TCS.AVG_RANGE AS AVG_RANGE,
REBEATS.DISCOUNTABLE AS DISCOUNTABLE,
TCCC.TCCCIBUTE_KEYWORD AS TCCCIBUTE_KEYWORD,
TCCC.TCCCIBUTE_TERM AS TCCCIBUTE_TERM,
TCCC.QTY AS QTY,
TAM.TAX_CODE,
TAM.MAT_LOCATION,
TAM.OPTICAL_NAME,
TAM.MAT_TYPE,
TAM.MAT_SOURCE
FROM BLUEB_TAM TAM
INNER JOIN
BLUEB_TAM_DESCRIPTION TAM_DESCRIPTION
ON TAM.NT_NUM = TAM_DESCRIPTION.NT_NUM
AND TAM.LOCATION = TAM_DESCRIPTION.LOCATION
AND TAM_DESCRIPTION.LOCATION_CODE = :LOCATION_CODE
INNER JOIN
PRICE_LIST PRP
ON TAM.NT_NUM = PRP.NT_NUM
AND PRP.cates = :cates
AND PRP.MONEY_UNIT = :MONEY_UNIT
INNER JOIN
TAM_REBEAT REBEATS
ON TAM.REBEAT_GROUP = REBEATS.REBEAT_GROUP
AND REBEATS.REBEAT_NAME = :REBEAT_NAME
INNER JOIN
TAM_CATEGORY_STATS TCS
ON TAM.LOCATION = TCS.LOCATION
AND TAM.MANF = TCS.MANF
AND PRP.MONEY_UNIT = TCS.MONEY_UNIT
AND TCS.cates = PRP.cates
LEFT JOIN
TAM_CLASSIFICATIONS TCCC
ON TAM.NT_NUM = TCCC.NT_NUM
WHERE NVL (TAM.DISABLED, 'N') = 'N'
AND SYSDATE BETWEEN NVL (TAM.STRT_DATE, DATE '0001-01-01')
AND NVL (TAM.END_DATE, DATE '3999-12-31')
AND TAM.MANF = :MANF
AND TAM.NT_NUM IN((SELECT /*+ CARDINALITY(t, 1) */
COLUMN_VALUE
FROM table (Split(:NTNUM_LIST,:NTNUM_LIST_delim)) t)
ORDER BY TAM.NT_NUM
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.32 0.32 0 48 0 0
Fetch 1 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.32 0.32 0 48 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 COUNT STOPKEY (cr=0 pr=0 pw=0 time=0 us)
0 0 0 TABLE ACCESS BY INDEX ROWID BLUEB_TAM_DESCRIPTION (cr=0 pr=0 pw=0 time=0 us cost=5 size=134 card=2)
0 0 0 INDEX RANGE SCAN BLUEB_TAM_DESCRIPTION_N2 (cr=0 pr=0 pw=0 time=0 us cost=3 size=0 card=2)(object id 112788)
0 0 0 COUNT STOPKEY (cr=0 pr=0 pw=0 time=0 us)
0 0 0 TABLE ACCESS BY INDEX ROWID BLUEB_TAM_DESCRIPTION (cr=0 pr=0 pw=0 time=0 us cost=5 size=134 card=2)
0 0 0 INDEX RANGE SCAN BLUEB_TAM_DESCRIPTION_N2 (cr=0 pr=0 pw=0 time=0 us cost=3 size=0 card=2)(object id 112788)
0 0 0 COUNT STOPKEY (cr=0 pr=0 pw=0 time=0 us)
0 0 0 TABLE ACCESS BY INDEX ROWID BLUEB_TAM_DESCRIPTION (cr=0 pr=0 pw=0 time=0 us cost=4 size=81 card=1)
0 0 0 INDEX RANGE SCAN BLUEB_TAM_DESCRIPTION_U1_MARKT (cr=0 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(object id 112789)
0 0 0 SORT ORDER BY (cr=0 pr=0 pw=0 time=584 us cost=48 size=2432 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=562 us)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=559 us cost=47 size=2432 card=1)
0 0 0 NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=555 us cost=44 size=2351 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=550 us cost=42 size=2320 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=546 us cost=39 size=2292 card=1)
0 0 0 NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=545 us cost=37 size=2276 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=541 us cost=36 size=2250 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=538 us cost=33 size=2218 card=1)
0 0 0 VIEW VW_NSO_1 (cr=0 pr=0 pw=0 time=533 us cost=29 size=2002 card=1)
0 0 0 HASH UNIQUE (cr=0 pr=0 pw=0 time=526 us)
0 0 0 COLLECTION ITERATOR PICKLER FETCH SPLIT (cr=0 pr=0 pw=0 time=401 us cost=29 size=2 card=1)
0 0 0 TABLE ACCESS BY INDEX ROWID BLUEB_TAM (cr=0 pr=0 pw=0 time=0 us cost=3 size=216 card=1)
0 0 0 INDEX RANGE SCAN BLUEB_TAM_N4 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 112794)
0 0 0 TABLE ACCESS BY INDEX ROWID TAM_CATEGORY_STATS_B (cr=0 pr=0 pw=0 time=0 us cost=3 size=32 card=1)
0 0 0 INDEX RANGE SCAN TAM_CATEGORY_STATS_N1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 112800)
0 0 0 TABLE ACCESS BY INDEX ROWID TAM_MAIN_CLASSIFICATIONS (cr=0 pr=0 pw=0 time=0 us cost=1 size=26 card=1)
0 0 0 INDEX UNIQUE SCAN GEDIS_TAM_MAIN_CLASSIFICATIONS_U1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 112799)
0 0 0 TABLE ACCESS BY INDEX ROWID TAM_REBEAT (cr=0 pr=0 pw=0 time=0 us cost=2 size=16 card=1)
0 0 0 INDEX RANGE SCAN TAM_REBEAT_N1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 112817)
0 0 0 TABLE ACCESS BY INDEX ROWID PRICE_LIST (cr=0 pr=0 pw=0 time=0 us cost=3 size=28 card=1)
0 0 0 INDEX RANGE SCAN PRICE_LIST_U1_MARKT (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 112841)
0 0 0 INDEX RANGE SCAN TAM_CLASSIFICATIONS_N5 (cr=0 pr=0 pw=0 time=0 us cost=2 size=93 card=3)(object id 112785)
0 0 0 INDEX RANGE SCAN BLUEB_TAM_DESCRIPTION_U1_MARKT (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 112789)
0 0 0 TABLE ACCESS BY INDEX ROWID BLUEB_TAM_DESCRIPTION (cr=0 pr=0 pw=0 time=0 us cost=3 size=81 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache lock 2 0.00 0.00
library cache pin 3 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 13.52 13.52
******************************************************************************** |
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Jan 30 12:00:32 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
