Home » RDBMS Server » Performance Tuning » Full table scan. Indexes are available. (10.2.0.1, window7)
| Full table scan. Indexes are available. [message #580160] |
Wed, 20 March 2013 23:05  |
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
|
Senior Member |
|
|
Dear All,
Below is my query -
SELECT /*+parallel(r)+*/
I.TXT_MASTER_CLAIM_NO,
I.NUM_SERIAL_NO SERIAL_NO,
P.PRODUCTCODE,
P.PRODUCTNAME,
D.DEPARTMENTCODE,
D.DEPARTMENTNAME,
TO_CHAR (
TRUNC (
( ( (86400
* (TO_DATE (TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS'),
'DD/MM/YYYY HH24:MI:SS')
- TO_DATE (
TO_CHAR (I.DAT_REGISTRATION_DATE,
'DD/MM/YYYY HH24:MI:SS'),
'DD/MM/YYYY HH24:MI:SS')))
/ 60)
/ 60)
/ 24),
'9990')
|| 'D '
|| TO_CHAR (
TRUNC (
( (86400
* (TO_DATE (
TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS'),
'DD/MM/YYYY HH24:MI:SS')
- TO_DATE (
TO_CHAR (I.DAT_REGISTRATION_DATE,
'DD/MM/YYYY HH24:MI:SS'),
'DD/MM/YYYY HH24:MI:SS')))
/ 60)
/ 60)
- 24
* (TRUNC (
( ( (86400
* (TO_DATE (
TO_CHAR (SYSDATE,
'DD/MM/YYYY HH24:MI:SS'),
'DD/MM/YYYY HH24:MI:SS')
- TO_DATE (
TO_CHAR (I.DAT_REGISTRATION_DATE,
'DD/MM/YYYY HH24:MI:SS'),
'DD/MM/YYYY HH24:MI:SS')))
/ 60)
/ 60)
/ 24)),
'09')
|| 'HR '
|| TO_CHAR (
TRUNC (
(86400
* (TO_DATE (TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS'),
'DD/MM/YYYY HH24:MI:SS')
- TO_DATE (
TO_CHAR (I.DAT_REGISTRATION_DATE,
'DD/MM/YYYY HH24:MI:SS'),
'DD/MM/YYYY HH24:MI:SS'))
/ 60)
- 60
* (TRUNC (
( (86400
* (TO_DATE (
TO_CHAR (SYSDATE,
'DD/MM/YYYY HH24:MI:SS'),
'DD/MM/YYYY HH24:MI:SS')
- TO_DATE (
TO_CHAR (I.DAT_REGISTRATION_DATE,
'DD/MM/YYYY HH24:MI:SS'),
'DD/MM/YYYY HH24:MI:SS')))
/ 60)
/ 60))),
'09')
|| 'MIN '
TAT,
I.TXT_POLICY_NO_CHAR,
I.NUM_NATURE_OF_LOSS NATUREOFLOSS,
--G.TXT_CUSTOMER_NAME TXT_NAME_OF_INSURED,
I.TXT_NAME_OF_INSURED TXT_NAME_OF_INSURED,
PD.TXT_PAYEE_CUSTOMER_VENDOR_NAME PAYEE,
RT.TXT_RESOURCE_TYPE PAYEETYPE,
DECODE (RT.TXT_LOSS_EXP,
'L', PD.CUR_AMOUNT,
'E', PD.CUR_BILL_AMOUNT,
PD.CUR_AMOUNT)
AMOUNT,
R.PRODUCT_NAME APPROVALTYPE,
C.TXT_OFFICE_CD,
C.YN_IS_INCHARGE,
R.GRPID,
DECODE (RT.TXT_LOSS_EXP, 'L', 'LP', 'E', 'EP') TXT_APPROVAL_TYPE,
I.Num_Claim_No,
PD.NUM_TYPE_OF_PARTY,
PD.TXT_PAYEE_CUSTOMER_VENDOR_CD,
PD.NUM_SERIAL_NO,
--G.TXT_CUSTOMER_ID,
I.TXT_INSURED_ID TXT_CUSTOMER_ID,
C.TXT_INFO2 FROMOFFICETYPE
FROM GC_CLM_PAYMENT_DETAILS PD,
GC_CLMMST_RESOURCE_TYPE RT,
CNFGTR_REQUEST R,
GC_CLM_REQUEST C,
GC_CLM_GEN_INFO I,
UW_DEPARTMENT_MASTER D,
UW_PRODUCT_MASTER P /*,
GEN_PROP_INFORMATION_TAB G*/
WHERE PD.TXT_STATUS = 'APPROVAL DUE'
AND PD.NUM_CLAIM_NO = I.NUM_CLAIM_NO
AND PD.NUM_TYPE_OF_PARTY = RT.NUM_RESOURCE_TYPE_CD
AND R.REQUESTID = C.REQUESTID
AND R.REMARKS =
DECODE (RT.TXT_LOSS_EXP,
'L', 'CLAIM LOSS APPROVAL',
'E', 'CLAIM EXPENSE APPROVAL')
AND R.TXT_DEAL_ID = C.TXT_DEAL_ID
AND C.TXT_DEAL_ID = TO_CHAR (I.NUM_CLAIM_NO)
AND I.NUM_UPDATE_NO = (SELECT MAX (II.NUM_UPDATE_NO)
FROM GC_CLM_GEN_INFO II
WHERE II.NUM_CLAIM_NO = I.NUM_CLAIM_NO)
AND I.NUM_DEPARTMENT_CODE = D.DEPARTMENTCODE
AND I.NUM_PRODUCT_CODE = P.PRODUCTCODE
/* AND G.NUM_REFERENCE_NUMBER=I.NUM_REFERENCE_NO
AND G.DAT_REFERENCE_DATE=I.DAT_REFERENCE_DATE*/
AND R.REQUEST_STATUS = 'I'
AND R.PRODUCT_NAME = 'APPROVAL'
AND R.MODULEINDX = 'C000002';
Below are my indexes on table
CREATE UNIQUE INDEX INS.CNFGTR_REQUEST_PK ON INS.CNFGTR_REQUEST
(REQUESTID)
NOLOGGING
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 704K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX INS.IDX_CNFGTR_REQUEST ON INS.CNFGTR_REQUEST
(TRANS_NO, APPROVEDUSERID)
NOLOGGING
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE BITMAP INDEX INS.IDX_CNFG_BMP ON INS.CNFGTR_REQUEST
(REQUEST_STATUS)
LOGGING
TABLESPACE INS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX INS.IDX_CNFG_REQ ON INS.CNFGTR_REQUEST
(REQUEST_STATUS, TXT_WORKFLOW_APPR_GUID)
LOGGING
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 768K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX INS.IDX_CNFG_TEMP ON INS.CNFGTR_REQUEST
(REQUESTID, REMARKS, PRODUCT_NAME, TXT_DEAL_ID, MODULEINDX)
LOGGING
TABLESPACE INS
PCTFREE 10
INITRANS 2
MAXTRANS 161
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX SYSTEM.IDX_REQUESTID ON INS.CNFGTR_REQUEST
(REMARKS, TXT_DEAL_ID, MODULEINDX)
NOLOGGING
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 166
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
ALTER TABLE INS.CNFGTR_REQUEST ADD (
CONSTRAINT CNFGTR_REQUEST_PK
PRIMARY KEY
(REQUESTID)
USING INDEX INS.CNFGTR_REQUEST_PK);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 818788616
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 513 | 31 (4)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 513 | 31 (4)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
|* 3 | FILTER | | | | | | Q1,01 | PCWC | |
| 4 | HASH GROUP BY | | 1 | 513 | 31 (4)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 1 | 513 | 31 (4)| 00:00:01 | Q1,01 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 1 | 513 | 31 (4)| 00:00:01 | Q1,00 | P->P | HASH |
| 7 | HASH GROUP BY | | 1 | 513 | 31 (4)| 00:00:01 | Q1,00 | PCWP | |
| 8 | NESTED LOOPS | | 1 | 513 | 30 (0)| 00:00:01 | Q1,00 | PCWP | |
| 9 | NESTED LOOPS | | 1 | 498 | 30 (0)| 00:00:01 | Q1,00 | PCWP | |
| 10 | NESTED LOOPS | | 1 | 471 | 30 (0)| 00:00:01 | Q1,00 | PCWP | |
| 11 | NESTED LOOPS | | 1 | 433 | 30 (0)| 00:00:01 | Q1,00 | PCWP | |
| 12 | NESTED LOOPS | | 1 | 408 | 30 (0)| 00:00:01 | Q1,00 | PCWP | |
| 13 | NESTED LOOPS | | 184 | 61640 | 11 (10)| 00:00:01 | Q1,00 | PCWP | |
| 14 | NESTED LOOPS | | 16 | 3328 | 8 (0)| 00:00:01 | Q1,00 | PCWP | |
| 15 | PX BLOCK ITERATOR | | | | | | Q1,00 | PCWC | |
|* 16 | TABLE ACCESS FULL | CNFGTR_REQUEST | 70 | 9590 | 8 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 17 | TABLE ACCESS BY INDEX ROWID| GC_CLM_REQUEST | 1 | 71 | 1 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 18 | INDEX UNIQUE SCAN | PK_GC_CLM_REQUEST | 1 | | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 19 | TABLE ACCESS BY INDEX ROWID | GC_CLM_GEN_INFO | 12 | 1524 | 4 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 20 | INDEX RANGE SCAN | INDX_GC_CLM_GEN_INFO | 12 | | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 21 | TABLE ACCESS BY INDEX ROWID | GC_CLM_PAYMENT_DETAILS | 1 | 73 | 3 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 22 | INDEX RANGE SCAN | IDX_GC_CLM_PAYMENT_DETAILS | 1 | | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 23 | INDEX RANGE SCAN | IDX_GC_CLMMST_RESOURCE_TYPE1 | 1 | 25 | 1 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 24 | INDEX RANGE SCAN | IDX_UW_PRODUCT_MASTER | 1 | 38 | 1 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 25 | INDEX RANGE SCAN | IDX_UW_DEPARTMENT_MASTER | 1 | 27 | 1 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 26 | INDEX RANGE SCAN | PK_GC_CLM_GEN_INFO | 15 | 225 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("I"."NUM_UPDATE_NO"=MAX(SYS_OP_CSR(SYS_OP_MSR(MAX("II"."NUM_UPDATE_NO")),0)))
16 - filter("R"."PRODUCT_NAME"='APPROVAL' AND "R"."REQUEST_STATUS"='I' AND "R"."MODULEINDX"='C000002' AND "R"."TXT_DEAL_ID" IS NOT NULL)
17 - filter("R"."TXT_DEAL_ID"="C"."TXT_DEAL_ID")
18 - access("R"."REQUESTID"="C"."REQUESTID")
20 - access("C"."TXT_DEAL_ID"=TO_CHAR("NUM_CLAIM_NO"))
22 - access("PD"."TXT_STATUS"='APPROVAL DUE' AND "PD"."NUM_CLAIM_NO"="I"."NUM_CLAIM_NO")
23 - access("PD"."NUM_TYPE_OF_PARTY"="RT"."NUM_RESOURCE_TYPE_CD")
filter("R"."REMARKS"=DECODE("RT"."TXT_LOSS_EXP",'L','CLAIM LOSS APPROVAL','E','CLAIM EXPENSE APPROVAL'))
24 - access("I"."NUM_PRODUCT_CODE"="P"."PRODUCTCODE")
25 - access("I"."NUM_DEPARTMENT_CODE"="D"."DEPARTMENTCODE")
26 - access("II"."NUM_CLAIM_NO"="I"."NUM_CLAIM_NO")
48 rows selected.
Now, i really don't understand, even after the indexes created on the columns of the table, why explain plan shows FTS on table CNFGTR_REQUEST?
Waiting for your reply...
Regards,
Ishika
|
|
|
|
|
|
|
|
|
|
| Re: Full table scan. Indexes are available. [message #580190 is a reply to message #580160] |
Thu, 21 March 2013 03:45  |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
The first question is whether you actually have a problem. If the estimates are correct,
that query will take one second. If it is not taking one second, you have a huge problem
with your statistics.
But let us assume that there is a problem, and the stats are correct, and that the FTS
is the cause of the problem.
Look at the filter for operation ID 16, that returns 70 rows. Drop all the indexes on
CNFGTR_REQUEST except for the PK index (or make them invisible, if you want to test first),
add a compound B*Tree index on those columns, and remove the parallel hint.
|
|
|
|
|
|
|
|
|
|
| Re: Full table scan. Indexes are available. [message #580249 is a reply to message #580192] |
Thu, 21 March 2013 23:31 |
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
|
Senior Member |
|
|
Dear Cookiemonster,
I ran it on my Dev Database. On live, stats are upto date. Cost for this query is more over there as compared to Our dev environment.
They are performance problem. And due to this, we have to optimized the query.
Regards,
Ishika
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Dec 18 00:56:00 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
