Home » RDBMS Server » Performance Tuning » Performance Tuning
Performance Tuning [message #315520] Mon, 21 April 2008 17:21 Go to next message
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 Go to previous messageGo to next message
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 145 times)
Re: Performance Tuning [message #315523 is a reply to message #315521] Mon, 21 April 2008 17:26 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
add indexes on the columns in the WHERE clause
Re: Performance Tuning [message #315524 is a reply to message #315520] Mon, 21 April 2008 17:40 Go to previous messageGo to next message
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 #315525 is a reply to message #315520] Mon, 21 April 2008 17:42 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Read & follow posting guidelines including the use of <code tags>
Re: Performance Tuning [message #315526 is a reply to message #315523] Mon, 21 April 2008 17:50 Go to previous messageGo to next message
ajexpert
Messages: 16
Registered: April 2008
Location: U.S.
Junior Member
Indexes already present.

Here is structure and index of CLM_TEXT

Name      Type       Nullable Default 
--------- ---------- -------- ------- 
TEXT_ID   NUMBER(10)          -1      
TEXT      BLOB       Y                
INSERT_DT DATE                SYSDATE 
UPDATE_DT DATE                SYSDATE 
  • Attachment: CLM_TEXT.JPG
    (Size: 30.13KB, Downloaded 140 times)
Re: Performance Tuning [message #315527 is a reply to message #315520] Mon, 21 April 2008 17:58 Go to previous messageGo to next message
BlackSwan
Messages: 25036
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 Go to previous messageGo to next message
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 #315530 is a reply to message #315520] Mon, 21 April 2008 18:47 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
> i will stop posting on this site.
It is your choice.
It will be your loss & our gain.

Enjoy solving your mystery.
Re: Performance Tuning [message #315558 is a reply to message #315530] Mon, 21 April 2008 21:20 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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
Re: Performance Tuning [message #315562 is a reply to message #315560] Mon, 21 April 2008 21:36 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You can't fine-tune them. It's impossible. Re-read my post above and the article to find out why.

Ross Leishman
Previous Topic: Performance isssue
Next Topic: Sql query tuning
Goto Forum:
  


Current Time: Sun Dec 04 12:54:58 CST 2016

Total time taken to generate the page: 0.16018 seconds