Today we got a complain from our development team that a query is running slow. i ran it in sql plus to have a look at its execution plan and check time it is taking.
I ran it with select count(*) and turned on timing and autotrace to get execution plan.
select count(*)
from
(Select A.Claim_Number, B.Req_Serial, C.Payments_Request_Num, C.Expenses_Claim_Num, C.Sent_Claim_Num
From Edatt_Insurance_Data_Img A, Fin_Edatt_Std_Payment_Req_Dtl B, Fin_Edatt_Std_Payment_Req_Hdr C
Where A.Cmp_Num = To_Char(B.Cmp_Num)
And A.Student_Id_Num = To_Char(B.Student_Id_Num)
And A.Patient_Other_Id = B.Patient_Other_Id
And A.Treatment_Code = B.Treatment_Code
And A.Claim_Reference_Number = B.Claim_Reference_Number
And A.Serial = B.Serial
And B.Cmp_Num = C.Cmp_Num
And B.Account_Year = C.Account_Year
And B.Req_Serial = C.Req_Serial
And A.Guid = C.Guid
and A.Claim_Number between 151211 And 160112
And A.Supplier_Number In (Select Supplier_Number
From (select rownum,total, Supplier_Number, Supplier_Name, Cmp_num , Account_Year
from(Select sum(nvl(B.Base_Amount,0)) total , A.Supplier_Number Supplier_Number,A.Supplier_Name Supplier_Name , A.Cmp_num Cmp_num , B.Account_Year Account_Year
From Edatt_Insurance_Data_img A,Fin_Edatt_Std_Payment_Req_Dtl B
Where A.Cmp_Num = To_char(B.Cmp_Num)
and A.Student_Id_Num = To_char(B.Student_Id_Num)
and A.Patient_Other_Id = B.Patient_Other_Id
And A.Treatment_Code = B.Treatment_Code
And A.Claim_Reference_Number = B.Claim_Reference_Number
And A.Serial = B.Serial
and A.Cmp_num = 45
and B.Account_Year = 2016
and A.Claim_Number between 151211 And 160112
group by A.Cmp_num, B.Account_Year , A.Supplier_Number, A.Supplier_Name)
where rownum <= 5)) )
I got 65 rows in more than 20 seconds. Upon analyzing execution plan of query i found that there are 2 full table scans.
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 317 | 2637 (1)| 00:00:32 |
| 1 | SORT AGGREGATE | | 1 | 317 | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 317 | 2637 (1)| 00:00:32 |
| 4 | NESTED LOOPS | | 1 | 253 | 2635 (1)| 00:00:32 |
|* 5 | HASH JOIN RIGHT SEMI | | 1 | 219 | 2631 (1)| 00:00:32 |
| 6 | VIEW | VW_NSO_1 | 1 | 102 | 330 (1)| 00:00:04 |
| 7 | VIEW | | 1 | 13 | 330 (1)| 00:00:04 |
|* 8 | COUNT STOPKEY | | | | | |
| 9 | VIEW | | 1 | 13 | 330 (1)| 00:00:04 |
|* 10 | SORT GROUP BY STOPKEY | | 1 | 175 | 330 (1)| 00:00:04 |
| 11 | NESTED LOOPS | | | | | |
| 12 | NESTED LOOPS | | 1 | 175 | 329 (1)| 00:00:04 |
|* 13 | TABLE ACCESS BY INDEX ROWID| EDATT_INSURANCE_DATA_IMG | 4 | 576 | 315 (0)| 00:00:04 |
|* 14 | INDEX RANGE SCAN | IDX_TO_NUM_CMP_NUM | 570 | | 281 (0)| 00:00:04 |
|* 15 | INDEX RANGE SCAN | STD_PAYMENT_REQ_DTL_IDX4 | 2 | | 1 (0)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID | FIN_EDATT_STD_PAYMENT_REQ_DTL | 1 | 31 | 4 (0)| 00:00:01 |
|* 17 | TABLE ACCESS FULL | EDATT_INSURANCE_DATA_IMG | 357 | 41769 | 2301 (1)| 00:00:28 |
|* 18 | TABLE ACCESS BY INDEX ROWID | FIN_EDATT_STD_PAYMENT_REQ_DTL | 1 | 34 | 4 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | STD_PAYMENT_REQ_DTL_IDX4 | 2 | | 1 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | PK_FIN_EDATT_STD_PAY_REQ_HDR | 1 | | 1 (0)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID | FIN_EDATT_STD_PAYMENT_REQ_HDR | 1 | 64 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("A"."SUPPLIER_NUMBER"="SUPPLIER_NUMBER")
8 - filter(ROWNUM<=5)
10 - filter(ROWNUM<=5)
13 - filter(TO_NUMBER("A"."CLAIM_NUMBER")>=151211 AND TO_NUMBER("A"."CLAIM_NUMBER")<=160112)
14 - access(TO_NUMBER("CMP_NUM")=45)
15 - access("A"."CLAIM_REFERENCE_NUMBER"="B"."CLAIM_REFERENCE_NUMBER")
filter("B"."CLAIM_REFERENCE_NUMBER" IS NOT NULL)
16 - filter("B"."PATIENT_OTHER_ID" IS NOT NULL AND "B"."TREATMENT_CODE" IS NOT NULL AND "B"."SERIAL" IS NOT
NULL AND "B"."ACCOUNT_YEAR"=2016 AND "A"."CMP_NUM"=TO_CHAR("CMP_NUM") AND
"A"."STUDENT_ID_NUM"=TO_CHAR("STUDENT_ID_NUM") AND "A"."PATIENT_OTHER_ID"="B"."PATIENT_OTHER_ID" AND
"A"."TREATMENT_CODE"="B"."TREATMENT_CODE" AND "A"."SERIAL"="B"."SERIAL")
17 - filter(TO_NUMBER("A"."CLAIM_NUMBER")>=151211 AND TO_NUMBER("A"."CLAIM_NUMBER")<=160112)
18 - filter("B"."PATIENT_OTHER_ID" IS NOT NULL AND "B"."TREATMENT_CODE" IS NOT NULL AND "B"."SERIAL" IS NOT
NULL AND "A"."CMP_NUM"=TO_CHAR("CMP_NUM") AND "A"."STUDENT_ID_NUM"=TO_CHAR("STUDENT_ID_NUM") AND
"A"."PATIENT_OTHER_ID"="B"."PATIENT_OTHER_ID" AND "A"."TREATMENT_CODE"="B"."TREATMENT_CODE" AND
"A"."SERIAL"="B"."SERIAL")
19 - access("A"."CLAIM_REFERENCE_NUMBER"="B"."CLAIM_REFERENCE_NUMBER")
filter("B"."CLAIM_REFERENCE_NUMBER" IS NOT NULL)
20 - access("B"."CMP_NUM"="C"."CMP_NUM" AND "B"."ACCOUNT_YEAR"="C"."ACCOUNT_YEAR" AND
"B"."REQ_SERIAL"="C"."REQ_SERIAL")
21 - filter("C"."GUID" IS NOT NULL AND "A"."GUID"="C"."GUID")
Statistics
----------------------------------------------------------
2 recursive calls
0 db block gets
72223 consistent gets
4556 physical reads
0 redo size
526 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
I found that there is a data type conversion from varchar to number on 2 columns (CMP_NUM) and (CLAIM_NUMBER) which was causing a full table scan because due to conversion index was not being used, so i created a function based index on both columns.
create index idx_to_num_cmp_num on EDATT_INSURANCE_DATA_IMG(to_number(CMP_NUM));
create index idx_to_num_claim_ref_num on EDATT_INSURANCE_DATA_IMG(to_number(CLAIM_NUMBER));
SQL> /
COUNT(*)
----------
65
Elapsed: 00:00:00.56
Execution Plan
----------------------------------------------------------
Plan hash value: 3175557350
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 323 | 120 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 323 | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 323 | 120 (2)| 00:00:02 |
| 4 | NESTED LOOPS | | 1 | 259 | 118 (2)| 00:00:02 |
| 5 | NESTED LOOPS | | 1 | 225 | 114 (2)| 00:00:02 |
| 6 | VIEW | VW_NSO_1 | 1 | 102 | 64 (2)| 00:00:01 |
| 7 | HASH UNIQUE | | 1 | 13 | | |
| 8 | VIEW | | 1 | 13 | 64 (2)| 00:00:01 |
|* 9 | COUNT STOPKEY | | | | | |
| 10 | VIEW | | 1 | 13 | 64 (2)| 00:00:01 |
|* 11 | SORT GROUP BY STOPKEY | | 1 | 181 | 64 (2)| 00:00:01 |
| 12 | NESTED LOOPS | | | | | |
| 13 | NESTED LOOPS | | 1 | 181 | 63 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID| EDATT_INSURANCE_DATA_IMG | 4 | 600 | 50 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | IDX_TO_NUM_CLAIM_REF_NUM | 647 | | 3 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | STD_PAYMENT_REQ_DTL_IDX4 | 2 | | 1 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID | FIN_EDATT_STD_PAYMENT_REQ_DTL | 1 | 31 | 4 (0)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID | EDATT_INSURANCE_DATA_IMG | 1 | 123 | 49 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | IDX_TO_NUM_CLAIM_REF_NUM | 647 | | 2 (0)| 00:00:01 |
|* 20 | TABLE ACCESS BY INDEX ROWID | FIN_EDATT_STD_PAYMENT_REQ_DTL | 1 | 34 | 4 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | STD_PAYMENT_REQ_DTL_IDX4 | 2 | | 1 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | PK_FIN_EDATT_STD_PAY_REQ_HDR | 1 | | 1 (0)| 00:00:01 |
|* 23 | TABLE ACCESS BY INDEX ROWID | FIN_EDATT_STD_PAYMENT_REQ_HDR | 1 | 64 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - filter(ROWNUM<=5)
11 - filter(ROWNUM<=5)
14 - filter(TO_NUMBER("CMP_NUM")=45)
15 - access(TO_NUMBER("CLAIM_NUMBER")>=151211 AND TO_NUMBER("CLAIM_NUMBER")<=160112)
16 - access("A"."CLAIM_REFERENCE_NUMBER"="B"."CLAIM_REFERENCE_NUMBER")
filter("B"."CLAIM_REFERENCE_NUMBER" IS NOT NULL)
17 - filter("B"."PATIENT_OTHER_ID" IS NOT NULL AND "B"."TREATMENT_CODE" IS NOT NULL AND "B"."SERIAL" IS NOT
NULL AND "B"."ACCOUNT_YEAR"=2016 AND "A"."CMP_NUM"=TO_CHAR("CMP_NUM") AND
"A"."STUDENT_ID_NUM"=TO_CHAR("STUDENT_ID_NUM") AND "A"."PATIENT_OTHER_ID"="B"."PATIENT_OTHER_ID" AND
"A"."TREATMENT_CODE"="B"."TREATMENT_CODE" AND "A"."SERIAL"="B"."SERIAL")
18 - filter("A"."SUPPLIER_NUMBER"="SUPPLIER_NUMBER")
19 - access(TO_NUMBER("CLAIM_NUMBER")>=151211 AND TO_NUMBER("CLAIM_NUMBER")<=160112)
20 - filter("B"."PATIENT_OTHER_ID" IS NOT NULL AND "B"."TREATMENT_CODE" IS NOT NULL AND "B"."SERIAL" IS NOT
NULL AND "A"."CMP_NUM"=TO_CHAR("CMP_NUM") AND "A"."STUDENT_ID_NUM"=TO_CHAR("STUDENT_ID_NUM") AND
"A"."PATIENT_OTHER_ID"="B"."PATIENT_OTHER_ID" AND "A"."TREATMENT_CODE"="B"."TREATMENT_CODE" AND
"A"."SERIAL"="B"."SERIAL")
21 - access("A"."CLAIM_REFERENCE_NUMBER"="B"."CLAIM_REFERENCE_NUMBER")
filter("B"."CLAIM_REFERENCE_NUMBER" IS NOT NULL)
22 - access("B"."CMP_NUM"="C"."CMP_NUM" AND "B"."ACCOUNT_YEAR"="C"."ACCOUNT_YEAR" AND
"B"."REQ_SERIAL"="C"."REQ_SERIAL")
23 - filter("C"."GUID" IS NOT NULL AND "A"."GUID"="C"."GUID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
74884 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
No comments:
Post a Comment