Home » RDBMS Server » Performance Tuning » Query execution time is fluctuating lot
| Query execution time is fluctuating lot [message #225556] |
Tue, 20 March 2007 05:53  |
niravshah2705
Messages: 2
Registered: March 2007
|
Junior Member |
|
|
Hi,
I have a Datawarehouse database setup.
While looking to the below query is giving output in 2 to 15 secs. So it flactuates a lot.
I have tried to capture autotrace details, to get better look over the execution plan. Each time the execution plan looks same , but the query shows changes the value in recursive calls each time(some times it scheduled periodically 5 mins interval).
Please find the query along with execution time & trace value for 2 different time.
Kindly provide the probable reason for the query output flucuations.
Query:
SELECT RS.CTC_ID,
RS.CTC_NAME,
NVL(ROUND(RS.CTC_SUM, 2), -999999999),
RS.GE_CCODE,
0,
0,
(SELECT 'Y' FROM DUAL WHERE RS.CTC_SUM / RS.TOT_UNIT < RS.MIN) MIN_VAL, ---- lower limit set is divided by count of vehicles and chked whether it has reached MIN limits
(SELECT 'Y' FROM DUAL WHERE RS.CTC_SUM / RS.TOT_UNIT > RS.MAX) MAX_VAL, ---- upper limit set is divided by count of vehicles and chked whether it has reached MAX limits
NULL,
DS
FROM (SELECT CTC_SUM.CTCID CTC_ID,
CTC_SUM.CTCNAME CTC_NAME,
(CTC_SUM.CTCSUM * 1) CTC_SUM,
MOD(ROWNUM, 2) GE_CCODE,
LSL_VAL * 1 MIN,
USL_VAL * 1 MAX,
CTC_SUM.DISP_SEQ DS,
NVL(CTC_SUM.UNITS, 1) TOT_UNIT
FROM IMANAGE_USER_CTC_SPEC_LIMITS IUCL,
(SELECT /* index(L2 IM_COST_L2_VEH_MONTH_ID)*/
CTC.CTC_ID CTCID,
CTC.CTCNAME CTCNAME,
NVL(SUM(L2.CTC_VAL), 0) CTCSUM,
CTC.DISP_SEQ DISP_SEQ,
DECODE(CTC.TARGET_TYPE,
'AMOUNT BY UNIT',
MAX(TOTAL_VEH_COUNT),
1) UNITS
from (SELECT /*index(ITR IMANAGE_TRANS_REF)*/
IC.CTC_ID,
NVL(ITR.LANG_VAL, IC.CTC_NAME) CTCNAME,
IC.DISPL_SEQ DISP_SEQ,
UCL.LSL_VAL,
UCL.USL_VAL,
UCL.TARGET_TYPE TARGET_TYPE
FROM IMANAGE_CTC IC,
IMANAGE_CTC_DOMAIN ICD,
IMANAGE_TRANS_REF ITR,
IMANAGE_USER_CTC_SPEC_LIMITS UCL
WHERE ICD.CTC_DOMAIN_NAME = 'FLEET COSTS' AND
IC.CTC_DOMAIN_ID = ICD.CTC_DOMAIN_ID AND
IC.CTC_ID = UCL.CTC_ID AND
IC.CTC_NAME = ITR.BASE_VAL AND
ITR.LANG_CD = 'E' AND UCL.USER_ID = 204 AND
UCL.ACTIVE_FLG = 'Y') CTC,
(SELECT *
FROM IMANAGE_F_COST_L2_AGG_VEH
WHERE CUST_HIER_ID IN
(SELECT CHILD_CUST_HIER_ID
FROM IMANAGE_USER_CUST_HIER_L3_MV MV
WHERE MV.USER_ID = 204 AND
MV.CUST_ACC_HIER_AGG_IND = 'Y') AND
MONTH_ID IN
(SELECT /* index(IMANAGE_PERIOD_DEFINITION_MV IM_PRD_DEF_MV_DASH_PRD_CMP)*/
MONTH_ID
FROM IMANAGE_PERIOD_DEFINITION_MV
WHERE DASHB_PERIOD = 'MN' AND PERIOD = 'PR')) L2,
(select /*index(imanage_f_cost)*/
max(ctc_id) IFC_CTC_ID,
lower(COST_COMP_name),
count(distinct VEHICLE_FLT_NUM) TOTAL_VEH_COUNT
from imanage_f_cost, imanage_ctc
where lower(ctc_name) = lower(COST_COMP_name) and
cust_hier_id in
(select /*index(IMANAGE_USER_CUST_HIER_L3_MV)*/
CHILD_CUST_HIER_ID
from IMANAGE_USER_CUST_HIER_L3_MV
where USER_ID = 204 and
CUST_ACC_HIER_AGG_IND = 'Y') and
MONTH_ID =
(SELECT /* index(IMANAGE_PERIOD_DEFINITION_MV IM_PRD_DEF_MV_DASH_PRD_CMP)*/
MONTH_ID
FROM IMANAGE_PERIOD_DEFINITION_MV
WHERE DASHB_PERIOD = 'MN' AND PERIOD = 'PR')
group by (COST_COMP_name)) IFC
where CTC.CTC_ID = L2.CTC_ID and IFC.IFC_CTC_ID = CTC.CTC_ID
GROUP BY CTC.CTC_ID,
CTC.CTCNAME,
CTC.DISP_SEQ,
CTC.TARGET_TYPE) CTC_SUM
WHERE CTC_SUM.CTCID = IUCL.CTC_ID AND IUCL.USER_ID = 204) RS
ORDER BY DS
Execution Plan:
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2834 Card=335 Bytes=
47905)
1 0 SORT (ORDER BY) (Cost=2834 Card=335 Bytes=47905)
2 1 VIEW (Cost=2830 Card=335 Bytes=47905)
3 2 COUNT
4 3 HASH JOIN* (Cost=2830 Card=335 Bytes=38525) :Q682000
9
5 4 TABLE ACCESS* (BY INDEX ROWID) OF 'IMANAGE_USER_CT :Q682000
C_SPEC_LIMITS' (Cost=2 Card=16 Bytes=176) 4
6 5 INDEX (RANGE SCAN) OF 'IMANAGE_USER_CTC_SPEC_LIM
I' (NON-UNIQUE) (Cost=1 Card=16)
7 4 VIEW* (Cost=2828 Card=418 Bytes=43472) :Q682000
9
8 7 SORT* (GROUP BY) (Cost=2828 Card=418 Bytes=73986 :Q682000
) 9
9 8 SORT* (GROUP BY) (Cost=2828 Card=418 Bytes=739 :Q682000
86) 8
10 9 HASH JOIN* (SEMI) (Cost=2824 Card=418 Bytes= :Q682000
73986) 8
11 10 HASH JOIN* (SEMI) (Cost=2747 Card=9623 Byt :Q682000
es=1616664) 8
12 11 HASH JOIN* (Cost=2746 Card=38490 Bytes=6 :Q682000
196890) 8
13 12 TABLE ACCESS* (FULL) OF 'IMANAGE_TRANS :Q682000
_REF' (Cost=4 Card=191 Bytes=5539) 1
14 12 TABLE ACCESS* (BY LOCAL INDEX ROWID) O :Q682000
F 'IMANAGE_F_COST_L2_AGG_VEH' (Cost=6 Card=21522 Bytes=11191 8
44)
15 14 NESTED LOOPS* (Cost=2742 Card=30156 :Q682000
Bytes=3980592) 8
16 15 NESTED LOOPS* (Cost=2740 Card=1 By :Q682000
tes=80) 8
17 16 HASH JOIN* (Cost=2739 Card=4 Byt :Q682000
es=260) 8
18 17 TABLE ACCESS* (BY INDEX ROWID) :Q682000
OF 'IMANAGE_CTC' (Cost=1 Card=1 Bytes=20) 8
19 18 NESTED LOOPS* (Cost=3 Card=5 :Q682000
Bytes=195) 8
20 19 TABLE ACCESS* (BY INDEX RO :Q682000
WID) OF 'IMANAGE_CTC_DOMAIN' (Cost=2 Card=1 Bytes=19) 8
21 20 INDEX* (RANGE SCAN) OF ' :Q682000
IMANAGE_CTC_DOMAIN_IE_01' (NON-UNIQUE) (Cost=1 Card=1) 8
22 19 INDEX* (RANGE SCAN) OF 'IM :Q682000
ANAGE_CTC_DOMAIN' (NON-UNIQUE) 8
23 17 VIEW* (Cost=2736 Card=14 Bytes :Q682000
=364) 8
24 23 SORT* (GROUP BY) (Cost=2736 :Q682000
Card=14 Bytes=672) 8
25 24 SORT* (GROUP BY) (Cost=273 :Q682000
6 Card=14 Bytes=672) 6
26 25 SORT* (GROUP BY) (Cost=2 :Q682000
736 Card=14 Bytes=672) 5
27 26 HASH JOIN* (Cost=2730 :Q682000
Card=10309 Bytes=494832) 5
28 27 TABLE ACCESS* (FULL) :Q682000
OF 'IMANAGE_CTC' (Cost=2 Card=20 Bytes=320) 0
29 27 TABLE ACCESS* (BY LO :Q682000
CAL INDEX ROWID) OF 'IMANAGE_F_COST' (Cost=8 Card=33 Bytes=7 5
59)
30 29 NESTED LOOPS* (Cos :Q682000
t=2728 Card=51546 Bytes=1649472) 5
31 30 TABLE ACCESS* (F :Q682000
ULL) OF 'IMANAGE_USER_CUST_HIER_L3_MV' (Cost=236 Card=1557 B 5
ytes=14013)
32 30 PARTITION RANGE* :Q682000
(SINGLE) 5
33 32 INDEX* (RANGE :Q682000
SCAN) OF 'IMANAGE_F_COST_MTHCOSTCUST_P' (NON-UNIQUE) (Cost=2 5
Card=33)
34 33 TABLE ACCESS :Q682000
* (BY INDEX ROWID) OF 'IMANAGE_PERIOD_DEFINITION_MV' (Cost=2 5
Card=4 Bytes=28)
35 34 INDEX* (RA :Q682000
NGE SCAN) OF 'IM_PRD_DEF_MV_DASH_PRD_CMP' (NON-UNIQUE) (Cost 5
=1 Card=4)
36 16 TABLE ACCESS* (BY INDEX ROWID) O :Q682000
F 'IMANAGE_USER_CTC_SPEC_LIMITS' (Cost=1 Card=1 Bytes=15) 8
37 36 INDEX* (UNIQUE SCAN) OF 'IMANA :Q682000
GE_USER_CTC_SPEC_LIMIT_PK' (UNIQUE) 8
38 15 PARTITION RANGE* (ALL) :Q682000
8
39 38 INDEX* (RANGE SCAN) OF 'IM_COST_ :Q682000
L2_VEH_P_CTC_ID' (NON-UNIQUE) (Cost=5 Card=21522) 8
40 11 TABLE ACCESS* (FULL) OF 'IMANAGE_PERIOD_ :Q682000
DEFINITION_MV' (Cost=1 Card=4 Bytes=28) 7
41 10 TABLE ACCESS* (BY INDEX ROWID) OF 'IMANAGE :Q682000
_USER_CUST_HIER_L3_MV' (Cost=77 Card=1557 Bytes=14013) 3
42 41 INDEX (RANGE SCAN) OF 'IMANAGE_USER_MV_L
3_USER_ID' (NON-UNIQUE) (Cost=2 Card=1557)
4 PARALLEL_TO_SERIAL SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) */
A1.C0,A2.C0,A1.C1,A1.C2,A1.C3,A1.C4
5 PARALLEL_FROM_SERIAL
7 PARALLEL_COMBINED_WITH_PARENT
8 PARALLEL_COMBINED_WITH_PARENT
9 PARALLEL_TO_PARALLEL SELECT /*+ PIV_GB */ A1.C8 C0,NVL(A1.C6,A1.C
5) C1,A1.C14 C2,A1.C21 C3,SYS_OP_MSR
10 PARALLEL_COMBINED_WITH_PARENT
11 PARALLEL_COMBINED_WITH_PARENT
12 PARALLEL_COMBINED_WITH_PARENT
13 PARALLEL_FROM_SERIAL
14 PARALLEL_COMBINED_WITH_CHILD
15 PARALLEL_COMBINED_WITH_PARENT
16 PARALLEL_COMBINED_WITH_PARENT
17 PARALLEL_COMBINED_WITH_PARENT
18 PARALLEL_COMBINED_WITH_PARENT
19 PARALLEL_COMBINED_WITH_PARENT
20 PARALLEL_COMBINED_WITH_PARENT
21 PARALLEL_COMBINED_WITH_PARENT
22 PARALLEL_COMBINED_WITH_PARENT
23 PARALLEL_COMBINED_WITH_PARENT
24 PARALLEL_COMBINED_WITH_PARENT
25 PARALLEL_TO_PARALLEL SELECT /*+ TIV_GB */ A1.C0 C0,SYS_OP_MSR(COU
NT(DISTINCT SYS_OP_CSR(A1.C1,0)),MAX
26 PARALLEL_TO_PARALLEL SELECT /*+ PIV_GB */ A1.C11 C0,SYS_OP_MSR(CO
UNT(DISTINCT A1.C8),MAX(A1.C2)) C1 F
27 PARALLEL_COMBINED_WITH_PARENT
28 PARALLEL_FROM_SERIAL
29 PARALLEL_COMBINED_WITH_CHILD
30 PARALLEL_COMBINED_WITH_PARENT
31 PARALLEL_COMBINED_WITH_PARENT
32 PARALLEL_COMBINED_WITH_PARENT
33 PARALLEL_COMBINED_WITH_PARENT
34 PARALLEL_COMBINED_WITH_PARENT
35 PARALLEL_COMBINED_WITH_PARENT
36 PARALLEL_COMBINED_WITH_PARENT
37 PARALLEL_COMBINED_WITH_PARENT
38 PARALLEL_COMBINED_WITH_PARENT
39 PARALLEL_COMBINED_WITH_PARENT
40 PARALLEL_TO_PARALLEL SELECT /*+ NO_EXPAND ROWID(A1) */ A1."MONTH_
ID" C0,A1."DASHB_PERIOD" C1,A1."PERI
41 PARALLEL_FROM_SERIAL
Statistics:
1) Elapsed time: 14 secs
3852 recursive calls
3 db block gets
43780 consistent gets
9595 physical reads
808 redo size
1439 bytes sent via SQL*Net to client
3344 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
108 sorts (memory)
0 sorts (disk)
10 rows processed
2) Elapsed time: 5 secs
0 recursive calls
0 db block gets
42436 consistent gets
8257 physical reads
0 redo size
1439 bytes sent via SQL*Net to client
3344 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
33 sorts (memory)
0 sorts (disk)
10 rows processed
|
|
|
|
| Re: Query execution time is fluctuating lot [message #225724 is a reply to message #225556] |
Wed, 21 March 2007 02:38  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
The recursive calls are run when Oracle needs to query the data dictionary to parse your query. This will happen each time the SQL is aged out of the shared pool.
So on the first run, you'll get a lot of recursive calls. If you run it again straight away, it will do a soft parse and less recursive sql. If you wait a while (or bounce the database, or flush the shared pool), you will have to re-parse.
Ross Leishman
|
|
|
|
| Re: Query execution time is fluctuating lot [message #226339 is a reply to message #225724] |
Fri, 23 March 2007 05:20 |
niravshah2705
Messages: 2
Registered: March 2007
|
Junior Member |
|
|
Thanks ross for your explanation.
I have a big doubt here...
Is the variation of 40 secs usual for the small queries?
Please guide me for the same. If it is usual what could be said to the client about execution time?
It takes 40 seconds for first run & then it takes hardly 9 seconds for second run & then onwards it reduced to 2 seconds!!!!!!! Also find the explain plan attached with shows no difference at all. Please find the statistics below that.
Similarly other queris are also shows this much variation!!!!
[B]Below Query:[/[/SIZE]B]
SELECT /*+ INDEX(REF) INDEX(IFC)*/
NVL(REF.FLT_LEVEL_02_ID, '-799') FLT_LEVEL_02_ID,
NVL(REF.FLT_LEVEL_02_NAME, 'VALUE NOT AVAILABLE') FLT_LEVEL_02_NAME,
COUNT(DISTINCT IFC.VEHICLE_FLT_NUM) VEH_COUNT
FROM IMANAGE_F_COST IFC,
IMANAGE_USER_ALL_COMB_ACC_MV REF
WHERE IFC.MONTH_ID IN (109) AND
IFC.COST_COMP_NAME =
(SELECT CTC_NAME
FROM IMANAGE_CTC
WHERE CTC_ID = 141) AND
IFC.CUST_HIER_ID = REF.CHILD_CUST_HIER_ID AND
REF.USER_ID = 204 AND
REF.CUST_ACC_HIER_AGG_IND = 'Y' AND
REF.FLT_LEVEL_01_ID = 'EU' AND
REF.FLT_LEVEL_02_ID <> '-'
-- WITHOUT THE BELOW CONDITION IT IS RUNNING FAST
AND
(IFC.COUNTRY_CD, IFC.SRC_PRODUCT_MSTR_ID) IN
(SELECT COUNTRY_CD, SRC_CD
FROM IMANAGE_COMMON_CROSS_REF
WHERE CROSS_REF_TYPE = 'PRODUCT' AND
TARGET_CD = '3' AND EFF_TO_DT >= SYSDATE AND
EFF_FROM_DT <= SYSDATE)
GROUP BY ROLLUP(REF.FLT_LEVEL_02_ID,
REF.FLT_LEVEL_02_NAME)
===========================================================
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=873 Card=1 Bytes=74)
1 0 SORT (GROUP BY ROLLUP) (Cost=873 Card=1 Bytes=74)
2 1 HASH JOIN (Cost=867 Card=1 Bytes=74)
3 2 NESTED LOOPS (Cost=833 Card=10 Bytes=550)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'IMANAGE_COMMON_CRO
SS_REF' (Cost=2 Card=1 Bytes=29)
5 4 INDEX (RANGE SCAN) OF 'IMNG_COMMON_CROSS_REF_TGT_C
OMB' (NON-UNIQUE) (Cost=1 Card=1)
6 3 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'IMANAGE_F_C
OST' (Cost=833 Card=4 Bytes=104)
7 6 AND-EQUAL
8 7 INDEX (RANGE SCAN) OF 'IM_SRC_PRODUCT_MSTR_ID' (
NON-UNIQUE) (Cost=280 Card=193776)
9 7 INDEX (RANGE SCAN) OF 'IDX_IMANAGE_F_COST_P_MONT
H_ID' (NON-UNIQUE) (Cost=280 Card=193776)
10 7 INDEX (RANGE SCAN) OF 'IMANAGE_F_COST_COUNTRY_P'
(NON-UNIQUE) (Cost=280 Card=193776)
11 6 TABLE ACCESS (BY INDEX ROWID) OF 'IMANAGE_CTC' (Co
st=1 Card=1 Bytes=16)
12 11 INDEX (UNIQUE SCAN) OF 'IMANAGE_CTC_PK' (UNIQUE)
13 2 TABLE ACCESS (BY INDEX ROWID) OF 'IMANAGE_USER_ALL_COM
B_ACC_MV' (Cost=33 Card=134 Bytes=2546)
14 13 INDEX (RANGE SCAN) OF 'USER_ALL_MV_USER_ID' (NON-UNI
QUE) (Cost=6 Card=24)
Statistics
----------------------------------------------------------
1) for 40 secs:
0 recursive calls
0 db block gets
38977 consistent gets
5432 physical reads
0 redo size
774 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
5 rows processed
2) for 9 secs:
0 recursive calls
0 db block gets
38977 consistent gets
1674 physical reads
0 redo size
774 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
5 rows processed
|
|
|
|
| Re: Query execution time is fluctuating lot [message #226471 is a reply to message #226339] |
Fri, 23 March 2007 22:10 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
The difference between the two runs you posted this was not in the recursive calls, it was in the disk reads.
Why would the query need to read from disk the FIRST time you run it but not the second time. Come on, take a guess.
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Thu May 16 03:30:57 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
