need help tuning this query [message #461642] |
Sun, 20 June 2010 05:04 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ultgnp
Messages: 106 Registered: May 2010 Location: Germany
|
Senior Member |
|
|
Hi,
I am trying to tune this query. I created required indexes. Tha table A_PT_ORDREG is about 2.5GB. I tried removing TRUNC on dates, but of no use. The data is not matching with original report output. Please suggest any alternative. Even with indexes,this query is taking lott of time. If i use FULL table scan hint, it might take much longer.
SELECT b.bus_unit,
b.reg_no,
b.pt_code,
b.md_code,
a.pt_reg_date
FROM a_pt_bill_master a,
a_pt_ordreg b
WHERE ( a.bus_unit = b.bus_unit
--AND ORD_DATE >= :P_DATE_FROM
AND a.reg_no = b.reg_no
AND a.pt_code = b.pt_code
AND b.ord_status <> 'V'
AND ( ( b.md_code BETWEEN :P_MD_CODE_FROM AND :P_MD_CODE_TO )
OR ( :P_MD_CODE_FROM IS NULL
AND :P_MD_CODE_TO IS NULL ) )
AND ( ( Trunc(b.ord_end_date) BETWEEN :P_DATE_FROM AND :P_DATE_TO
AND Nvl(b.dntl_xray_lab_flag, 'A') <> 'L' )
OR ( b.dntl_xray_lab_flag = 'L'
AND Trunc(b.ord_date) BETWEEN :P_DATE_FROM AND :P_DATE_TO )
OR ( :P_DATE_FROM IS NULL
AND :P_DATE_TO IS NULL ) )
--AND B.AS_APPLICATION <> 'INPT'
AND Nvl(b.ord_bill_amount, 0) >= 0 )
AND ( :BUS_UNIT = b.bus_unit )
AND ( :MD_CODE2 = b.md_code )
GROUP BY b.bus_unit,
b.reg_no,
b.pt_code,
b.md_code,
a.pt_reg_date
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.42 0 0 0 0
Execute 111 0.00 0.14 0 0 0 0
Fetch 111 20.34 1402.81 646924 1294948 0 281
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 223 20.36 1403.37 646924 1294948 0 281
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 675 (ULTGNP)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (GROUP BY)
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'A_PT_ORDREG'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'TEMP1_A_PT_ORDRDEG' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'A_PT_BILL_MASTER'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_A_PT_BILL_MASTER' (UNIQUE)
Index on A_PT_ORDREG
INDEX_NAME COLUMN_NAME COLUMN_POSITION
A_PT_ORDREG_NDX5 PT_CODE 2
A_PT_ORDREG_NDX5 REG_NO 3
A_PT_ORDREG_NDX5 BUS_UNIT 1
TEMP1_A_PT_ORDRDEG BUS_UNIT 1
TEMP1_A_PT_ORDRDEG MD_CODE 2
IDX9_DONE_SRVCS BUS_UNIT 1
IDX9_DONE_SRVCS ORD_END_DATE 2
IDX9_DONE_SRVCS CASH_FLAG 3
IDX9_DONE_SRVCS BILL_FLAG 4
A_PT_ORDREG PT_CODE 1
A_PT_ORDREG ORDER_NO 2
A_PT_ORDREG SERVICE_TYPE_CODE 3
A_PT_ORDREG SERVICE_CODE 4
A_PT_ORDREG_NDX4 BUS_UNIT 1
A_PT_ORDREG_NDX4 REG_NO 2
A_PT_ORDREG_NDX4 ORDER_NO 3
IDX6_A_PT_ORDREG BUS_UNIT 1
IDX6_A_PT_ORDREG REG_NO 2
A_PT_ORDREG_NEW BUS_UNIT 1
A_PT_ORDREG_NEW ORD_DATE 2
A_PT_ORDREG_NEW ORD_STATUS 3
A_PT_ORDREG_NEW MIS_FLAG 4
A_PT_ORDREG_NEW PRIOR_APPROVAL_FLAG 5
A_PT_ORDREG_NEW CASH_FLAG 6
TEMP_A_PT_ORDREG BUS_UNIT 1
TEMP_A_PT_ORDREG ORD_DATE 2
IDX8_A_PT_ORDREG BUS_UNIT 1
IDX8_A_PT_ORDREG DNTL_PROCESS_FLAG 2
IDX8_A_PT_ORDREG ORD_DATE 3
IDX8_A_PT_ORDREG ORD_STATUS 4
PK_A_PT_ORDREG BUS_UNIT 1
PK_A_PT_ORDREG ORDER_NO 2
PK_A_PT_ORDREG SEQ_NO 3
WASI_A_PT_ORDREG BUS_UNIT 1
WASI_A_PT_ORDREG ORD_DATE 2
WASI_A_PT_ORDREG HOSP_DEPT_CODE 3
WASI_A_PT_ORDREG BILL_FLAG 4
WASI_A_PT_ORDREG CASH_FLAG 5
WASI_A_PT_ORDREG ORD_STATUS 6
DNT_LAB_A_PT_ORDREG BUS_UNIT 1
DNT_LAB_A_PT_ORDREG ORD_DATE 2
DNT_LAB_A_PT_ORDREG DNTL_XRAY_LAB_FLAG 3
DNT_LAB_A_PT_ORDREG DNTL_LAB_CHRGS 4
DNT_LAB_A_PT_ORDREG MIS_FLAG 5
DNT_LAB_A_PT_ORDREG DNTL_PROCESS_FLAG 6
DNT_LAB_A_PT_ORDREG CASH_FLAG 7
APT_DEDUCTIBLE BUS_UNIT 1
APT_DEDUCTIBLE MD_CODE 2
APT_DEDUCTIBLE PT_CODE 3
BlackSwan formatted the posted SQL
[Updated on: Sun, 20 June 2010 09:25] by Moderator Report message to a moderator
|
|
|
|
|
Re: need help tuning this query [message #461706 is a reply to message #461642] |
Mon, 21 June 2010 01:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
1. Why do you need
AND ( ( b.md_code BETWEEN :P_MD_CODE_FROM AND :P_MD_CODE_TO )
OR ( :P_MD_CODE_FROM IS NULL
AND :P_MD_CODE_TO IS NULL ) )
when you already have
AND ( :MD_CODE2 = b.md_code )
? Can you remove it from the query?
2. IMHO the index TEMP1_A_PT_ORDRDEG can be dropped (it's already
included in index APT_DEDUCTIBLE ).
3. You didn't perform EXIT from the traced session, so your trace was not closed properly and no row counts were printed.
4. How many rows are in a_pt_bill_master table and what are it's indexes?
5. Do you supply values for :P_DATE_FROM and :P_DATE_TO parameters or are these values NULLs?
6. If these values are NOT nulls can you try following:
A. CREATE INDEX ... ON A_PT_ORDREG ( BUS_UNIT, MD_CODE,
DECODE(dntl_xray_lab_flag, 'L', ord_date, ord_end_date) )
NOLOGGING COMPUTE STATISTICS ...
B. Rewrite query as:
...DECODE(dntl_xray_lab_flag, 'L', ord_date, ord_end_date)
BETWEEN :P_DATE_FROM and :P_DATE_TO
HTH.
|
|
|
Re: need help tuning this query [message #461727 is a reply to message #461706] |
Mon, 21 June 2010 03:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ultgnp
Messages: 106 Registered: May 2010 Location: Germany
|
Senior Member |
|
|
Hi,
How do i exit after tracing a session. I did stop the tracing and closed the Application. are the cursors still open?
Please guide me in exiting the trace session
|
|
|
|
|
Re: need help tuning this query [message #461743 is a reply to message #461706] |
Mon, 21 June 2010 05:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
michael_bialik wrote on Mon, 21 June 2010 07:58
B. Rewrite query as:
...DECODE(dntl_xray_lab_flag, 'L', ord_date, ord_end_date)
BETWEEN :P_DATE_FROM and :P_DATE_TO
Assuming p_date_from and p_date_to can't have time components that should probably be:
...DECODE(dntl_xray_lab_flag, 'L', ord_date, ord_end_date)
BETWEEN :P_DATE_FROM and :P_DATE_TO + 1
|
|
|
|
|
Re: need help tuning this query [message #461954 is a reply to message #461905] |
Tue, 22 June 2010 04:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
ultgnp wrote on Tue, 22 June 2010 07:54Hi, i tried using decode function, but came to know that it's supported from 9i onwards.
Where on earth did you get that idea from?
Case didn't exist till 9i but decode exists in 8 and earlier versions.
|
|
|
|
|
Re: need help tuning this query [message #462196 is a reply to message #462164] |
Wed, 23 June 2010 03:00 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ultgnp
Messages: 106 Registered: May 2010 Location: Germany
|
Senior Member |
|
|
I tried after removing the trunc function. Still it takes time to execute. The output now matches the original report output.
Also could someone please tell me which index is it using? I will also try using the decode function and let you know the result.
SELECT B.BUS_UNIT , B.REG_NO , B.PT_CODE, B.MD_CODE, A.PT_REG_DATE
FROM A_PT_BILL_MASTER A , A_PT_ORDREG B
WHERE ( ( A.BUS_UNIT = B.BUS_UNIT
AND A.REG_NO = B.REG_NO
AND A.PT_CODE = B.PT_CODE
AND B.ORD_STATUS <> 'V'
AND ((B.MD_CODE BETWEEN :P_MD_CODE_FROM AND :P_MD_CODE_TO) OR(:P_MD_CODE_FROM IS NULL AND :P_MD_CODE_TO IS NULL))
AND
(
(B.ORD_END_DATE >= :P_DATE_FROM AND B.ORD_END_DATE < (:P_DATE_TO+1) AND NVL(B.DNTL_XRAY_LAB_FLAG,'A') <> 'L')
OR (B.DNTL_XRAY_LAB_FLAG = 'L' AND
B.ORD_DATE >= :P_DATE_FROM AND B.ORD_DATE < :P_DATE_TO+1)
)
AND NVL(B.ORD_BILL_AMOUNT,0) >= 0 ) ) AND ( :BUS_UNIT = B.BUS_UNIT) AND ( :MD_CODE2 = B.MD_CODE) GROUP BY B.BUS_UNIT , B.REG_NO, B.PT_CODE,B.MD_CODE,A.PT_REG_DATE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.25 0 0 0 0
Execute 111 0.00 0.00 0 0 0 0
Fetch 111 16.47 849.57 551975 1297181 0 246
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 223 16.47 849.82 551975 1297181 0 246
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 675 (ULTGNP)
Rows Row Source Operation
------- ---------------------------------------------------
601 SORT GROUP BY
601 NESTED LOOPS
1234982 TABLE ACCESS BY INDEX ROWID A_PT_ORDREG
1235093 INDEX RANGE SCAN (object id 5302)
601 TABLE ACCESS BY INDEX ROWID A_PT_BILL_MASTER
601 INDEX UNIQUE SCAN (object id 5249)
[Updated on: Wed, 23 June 2010 03:04] Report message to a moderator
|
|
|
|
|
|
Re: need help tuning this query [message #462315 is a reply to message #462219] |
Wed, 23 June 2010 08:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
ultgnp
Messages: 106 Registered: May 2010 Location: Germany
|
Senior Member |
|
|
A_PT_ORDREG. By the way i created an index on A_PT_ORDREG(Bus_unit,dntl_xray_lab_flag,ord_date, ord_end_date)and the report got executed in less than 10 sec. Earlier it took over an hour to run. I would like to thank everyone who helped me with my queries. I have learnt few things about indexes,writing statements correctly. Thank You all once again. Appreciate your help.
|
|
|