| Performance Tuning [message #315520] |
Mon, 21 April 2008 17:21  |
ajexpert
Messages: 16
Registered: April 2008
Location: U.S.
|
Junior Member |
|
|
Here is the query which needs to be optimized
SELECT B.TEXT_ID NOTE_PROD_DATA_ID, 'CLM' REFERENCE_TYPE_C, 'CLM' ANCESTOR_TYPE_C, A.WORK_ID
ANCESTOR_ID, 1 SYSTEM_ID, A.WORK_ID REFERENCE_ID, A.CLAIM_NUM REFERENCE_DISPLAY, A.CLAIM_NUM
ANCESTOR_DISPLAY, 4 UPDATED_BY,
mg_getGMTDate(B.INSERT_DT), mg_getGMTDate(B.UPDATE_DT), B.TEXT FROM
CWSUTC.WFM_WORK_ITEM A, CWSUTC.CLM_TEXT B WHERE
A.DIARY_TEXT_ID = B.TEXT_ID
mg_getGMTDate is a user defined function which returns a date depending upon the timezone
Explain plan is attached
Any help would be appreciated
|
|
|
|
| Performance Tuning [message #315521 is a reply to message #315520] |
Mon, 21 April 2008 17:23  |
ajexpert
Messages: 16
Registered: April 2008
Location: U.S.
|
Junior Member |
|
|
Here is the query which needs to be optimized
SELECT B.TEXT_ID NOTE_PROD_DATA_ID, 'CLM' REFERENCE_TYPE_C, 'CLM' ANCESTOR_TYPE_C, A.WORK_ID
ANCESTOR_ID, 1 SYSTEM_ID, A.WORK_ID REFERENCE_ID, A.CLAIM_NUM REFERENCE_DISPLAY, A.CLAIM_NUM
ANCESTOR_DISPLAY, 4 UPDATED_BY,
mg_getGMTDate(B.INSERT_DT), mg_getGMTDate(B.UPDATE_DT), B.TEXT FROM
CWSUTC.WFM_WORK_ITEM A, CWSUTC.CLM_TEXT B WHERE
A.DIARY_TEXT_ID = B.TEXT_ID
mg_getGMTDate is a user defined function which returns a date depending upon the timezone
Explain plan is attached
Any help would be appreciated
-
Attachment: ex-pl.JPG
(Size: 55.86KB, Downloaded 793 times)
|
|
|
|
|
|
| Re: Performance Tuning [message #315524 is a reply to message #315520] |
Mon, 21 April 2008 17:40 |
ajexpert
Messages: 16
Registered: April 2008
Location: U.S.
|
Junior Member |
|
|
Here is the structure and indexes of WFM_WORK_ITEM
SQL> DESC CWSUTC.WFM_WORK_ITEM;
Name Type Nullable Default Com
------------------------- ------------- -------- ------- ---
WORK_ID NUMBER(10) -1
SERVER_LOC_ID NUMBER(10) -1
CLAIM_TYPE_ID NUMBER(10) -1
FOLDER_TYPE_CODE NUMBER(5) -1
PERM_OWNER_ID NUMBER(10) -1
ORIGINAL_LOC_ID NUMBER(10) -1
ASSIGNEDTO_ID NUMBER(10) -1
ASSIGNED_DT DATE sysdate
DOC_DISTRIBUTOR_ID NUMBER(10) -1
CLOSE_APPROVER_ID NUMBER(10) -1
WI_STATUS VARCHAR2(15) 'NEW'
WI_STATUS_DT DATE sysdate
WI_OPEN_DT DATE Y
CLAIM_NUM VARCHAR2(15)
PRIORITY NUMBER(5) 2
CAT_ID NUMBER(10) -1
CAT_USER_ID NUMBER(10) -1
CAT_MANUAL_FLAG NUMBER(5) 0
LOB_ID NUMBER(10) -1
POLICY_ID NUMBER(10) -1
POLICY_NUM VARCHAR2(30) Y
COMPANY_NAME VARCHAR2(90) Y
INSURED_NID NUMBER(10) -1
INSURED_NOTE VARCHAR2(120) Y
LOSS_LOCATION VARCHAR2(40) Y
LOSS_CITY VARCHAR2(40) Y
LOSS_COUNTY VARCHAR2(30) Y
LOSS_STATE VARCHAR2(2) Y
LOSS_COUNTRY VARCHAR2(20) Y 'USA'
DISTRIBUTION_TERRITORY VARCHAR2(30)
DISTRIBUTION_STATE VARCHAR2(2)
DISTRIBUTION_COUNTRY VARCHAR2(20) 'USA'
LOSS_DESC_TEXT_ID NUMBER(10) -1
CLAIM_FLAGS VARCHAR2(48) Y
COMPLEXITY NUMBER(3) 2
DATE_OF_LOSS_DT DATE sysdate
CLAIM_MADE_DT DATE Y
NOTIFICATION_DT DATE
CALLER_NAME VARCHAR2(71) Y
CALLER_TYPE VARCHAR2(24) Y
AGENCY_ID NUMBER(10) -1
DIARY_TEXT_ID NUMBER(10) -1
LOSS_NOTICE_TEXT_ID NUMBER(10) -1
METHOD_OF_REPORT VARCHAR2(24)
CITY_CODE VARCHAR2(5) Y
WEATHER VARCHAR2(30) Y
AT_FAULT_INDICATOR NUMBER(5) 0
CREATOR_ID NUMBER(10) -1
MODIFIER_ID NUMBER(10) -1
TOT_RECOVERY_DED_AMT NUMBER(17,2) Y
EST_CLAIM_FROM_EXT_SYSTEM VARCHAR2(1) Y
PACKAGE_PREFIX VARCHAR2(6) Y
INSERT_DT DATE sysdate
UPDATE_DT DATE sysdate
|
|
|
|
|
|
|
|
| Re: Performance Tuning [message #315527 is a reply to message #315520] |
Mon, 21 April 2008 17:58 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
The STICKY at the top of this sub-forum contains the following text.
These articles may help you understand some key points:
On indexes
Jonathan Lewis Why Isn't Oracle Using My Index?!
Tom Kyte Why isn't my index getting used? (need to register to the site but it is free)
Ross Leishman The index is being ignored
I suggest you actually do some research on YOUR own time & stop wasting other folks time by not posting meaningful facts.
You're On Your Own (YOYOY)!
[Updated on: Mon, 21 April 2008 18:11] by Moderator Report message to a moderator |
|
|
|
| Re: Performance Tuning [message #315528 is a reply to message #315520] |
Mon, 21 April 2008 18:14 |
ajexpert
Messages: 16
Registered: April 2008
Location: U.S.
|
Junior Member |
|
|
What a reply!!!
FYI...i am not a beginner in Oracle.
I have done some research and thought will ask experts opinion to assist me.
Well, if its time waste for you folks, i will stop posting on this site.
|
|
|
|
|
|
| Re: Performance Tuning [message #315558 is a reply to message #315530] |
Mon, 21 April 2008 21:20 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
OK. Break! Neutral corners.
@ajexpert, consider what we might need in order to help here. We don't know how big your tables are. We don't know how many rows will be returned. We don't know what that function does.
You've posted the plan, and you can see for yourself that it is performing a full table scan. Is that the problem? Maybe. I cannot tell, but I doubt it.
You are joining two tables WITHOUT filtering the results. That means EVERY row is a join candidate. The fastest way to read every row is a FULL TABLE SCAN. That's what your query is doing, so I see no problem.
The only exception to this logic is when there will be a predominance of join failures. ie. >90% of rows in one table do not have a matching row in the other table. I'll assume that's not the case here.
In all likelihood, the problem is the user-defined function. This article explains why - see the section on FUNCTIONS.
You can verify this theory by removing the function calls and checking how fast it runs. If I am right, much, much faster.
You need to eliminate the functions and merge their logic into the join clause.
Ross Leishman
|
|
|
|
| Re: Performance Tuning [message #315560 is a reply to message #315520] |
Mon, 21 April 2008 21:31 |
ajexpert
Messages: 16
Registered: April 2008
Location: U.S.
|
Junior Member |
|
|
Thanks Ross,
I just figured out that user defined function is indeed a cause for slow response.
I saw the explain plan and got drifted as it was using full table scans. The number of records in both tables is 0.5 million.
I tried to use USE_NL oracle hints to see if I can avoid full table scan and use index path, but doing this didn't improve either.
I have to find a way to fine tune the user defined functions which are causes of poor performance.
Thanks Again
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
