Home » SQL & PL/SQL » SQL & PL/SQL » Query tunning
Query tunning [message #422560] Thu, 17 September 2009 07:29 Go to next message
rumarani
Messages: 13
Registered: September 2009
Location: Bangalore
Junior Member
Hi,

I need to tune this query which is taking several minutes to complete in production, please suggest any idea for tunning.

INSERT INTO TMP_RISK_WEIGHT_MAP(
class_id
,ctry_inc_cd
,derivative_ind
,maturity_band)
SELECT DISTINCT
CCDB.class_id
,CCDB.ctry_inc_cd
,'N'
,fn_fac_maturity_band(FAC.last_review_dt,
FAC.renewal_dt,
FAC.maturity_dt,
FAC.approval_dt)
FROM SRC_FAC_DET FAC
,TMP_CCDB_MAP CCDB
WHERE FAC.cob_dt = pi_cob_dt
AND FAC.run_id = pi_run_id
AND EXISTS (SELECT row_id
FROM TMP_MAP_ROWIDS
WHERE row_id = fac.ROWID
AND cob_dt = fac.cob_dt
AND run_id = fac.run_id
AND gcds_type_cd = 'F')
AND NVL(FAC.cpty_type_cd, g_default_string_key) = CCDB.id_type_cd
AND NVL(FAC.cpty_cd, g_default_string_key) = CCDB.id_value_cd
AND NVL(FAC.cpty_ccdb_id, g_default_number_key) = CCDB.pushed_ccdb_id
AND CCDB.ccdb_id IS NOT NULL;
Re: Query tunning [message #422563 is a reply to message #422560] Thu, 17 September 2009 07:40 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please carefully read OraFAQ Forum Guide and post accordingly.

Regards
Michel
Re: Query tunning [message #422666 is a reply to message #422560] Fri, 18 September 2009 03:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Based on the almost total lack of information that you've provided, I'd guess that the culprit is this function call
fn_fac_maturity_band(FAC.last_review_dt,
FAC.renewal_dt,
FAC.maturity_dt,
FAC.approval_dt)


Re: Query tunning [message #422679 is a reply to message #422666] Fri, 18 September 2009 06:15 Go to previous messageGo to next message
rumarani
Messages: 13
Registered: September 2009
Location: Bangalore
Junior Member

Hi,

These two columns are indexed in the query.

WHERE FAC.cob_dt = pi_cob_dt
AND FAC.run_id = pi_run_id

and these four colums are indexed

WHERE row_id = fac.ROWID
AND cob_dt = fac.cob_dt
AND run_id = fac.run_id
AND gcds_type_cd = 'F')

And the query is not frequently taking more time to execute.
It happens oneach 2 or 3 months in production.

Can you suggest any hint will be useful in tuning this query.



Re: Query tunning [message #422699 is a reply to message #422679] Fri, 18 September 2009 07:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Could you post an explain plan, and the code from that function that is called in the select list.
Re: Query tunning [message #422703 is a reply to message #422699] Fri, 18 September 2009 07:48 Go to previous messageGo to next message
rumarani
Messages: 13
Registered: September 2009
Location: Bangalore
Junior Member
Hi,

Please find below the plain path attached.

Thanks!!

Re: Query tunning [message #422705 is a reply to message #422699] Fri, 18 September 2009 07:52 Go to previous messageGo to next message
rumarani
Messages: 13
Registered: September 2009
Location: Bangalore
Junior Member

Hi,

Please find the function below.

FUNCTION fn_fac_maturity_band(pi_last_review_dt IN SRC_FAC_DET.last_review_dt%TYPE,
pi_renewal_dt IN SRC_FAC_DET.renewal_dt%TYPE,
pi_maturity_dt IN SRC_FAC_DET.maturity_dt%TYPE,
pi_approval_dt IN SRC_FAC_DET.approval_dt%TYPE)
RETURN MAP_CPTY_RISK_WEIGHTING.maturity_band_upper_limit%TYPE
AS
v_method_name VARCHAR2(50) := 'fn_fac_maturity_band()';
v_message VARCHAR2(2000);
v_maturity_band NUMBER;
BEGIN
/*
** Determine the maturity band.
*/
IF (pi_last_review_dt IS NOT NULL) THEN
IF (pi_renewal_dt IS NOT NULL) THEN
v_maturity_band := pi_renewal_dt - pi_last_review_dt;
ELSE
/*
** It's assumed that maturity date is populated in this case.
*/
v_maturity_band := pi_maturity_dt - pi_last_review_dt;
END IF;
ELSE
/*
** It's assumed that maturity and approval dates are populated in this case.
*/
v_maturity_band := pi_maturity_dt - pi_approval_dt;
END IF;
IF (v_maturity_band >= 366) THEN
RETURN(366);
ELSE
RETURN(0);
END IF;
EXCEPTION
WHEN OTHERS THEN
v_message := v_method_name || ': ' || SQLERRM;
RAISE_APPLICATION_ERROR(-20202, v_message);
END fn_fac_maturity_band;
Re: Query tunning [message #422707 is a reply to message #422703] Fri, 18 September 2009 08:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Please find below the plain path attached.


I'd love to, but sadly, reality has intervened, and it's not there.
Re: Query tunning [message #422708 is a reply to message #422705] Fri, 18 September 2009 08:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ok, it's probably not that function, unless you're populating a lot of rows.

As a matter of principle, you should get rid of that exception handler. It does nothing except hide the actual line at which the exception occurred.
Re: Query tunning [message #423088 is a reply to message #422707] Tue, 22 September 2009 01:28 Go to previous messageGo to next message
rumarani
Messages: 13
Registered: September 2009
Location: Bangalore
Junior Member
Hi,

Please find attached the plam path.

Re: Query tunning [message #423125 is a reply to message #423088] Tue, 22 September 2009 04:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Is there any chance of having something formatted?

Do:
EXPLAIN PLAN FOR <statement>;

SELECT * FROM table(dbms_xplan.display());
Re: Query tunning [message #423159 is a reply to message #423125] Tue, 22 September 2009 06:37 Go to previous messageGo to next message
rumarani
Messages: 13
Registered: September 2009
Location: Bangalore
Junior Member
Hi,

Please find attached the formatted one.

Thanks.
Re: Query tunning [message #423166 is a reply to message #423159] Tue, 22 September 2009 07:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Just post it in Code tags.
PLAN_TABLE_OUTPUT
 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost  | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                    |                     |     1 |   168 |  1042 |       |       |
|   1 |  HASH UNIQUE                        |                     |     1 |   168 |  1042 |       |       |
|   2 |   FILTER                            |                     |       |       |       |       |       |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SRC_FAC_DET         | 32120 |  2415K|  1038 |       |       |
|   4 |     NESTED LOOPS                    |                     |     1 |   168 |  1040 |       |       |
|   5 |      TABLE ACCESS FULL              | TMP_CCDB_MAP        |     1 |    91 |     2 |       |       |
|   6 |      PARTITION RANGE SINGLE         |                     | 16060 |       |    53 |   KEY |   KEY |
|   7 |       INDEX RANGE SCAN              | SRC_FAC_DET_IDX6    | 16060 |       |    53 |   KEY |   KEY |
|   8 |    INDEX UNIQUE SCAN                | TMP_MAP_ROWIDS_UK01 |     1 |    38 |     1 |       |       |
-----------------------------------------------------------------------------------------------------------
 
Note
-----
   - 'PLAN_TABLE' is old ve
Re: Query tunning [message #423167 is a reply to message #423166] Tue, 22 September 2009 07:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming those stats are up to date, this doesn't look like a several minute query - is this from the production server?

I notice one of your tables is called TMP_....

Is it a bona fide Temporary table, and are the statistics on it current?

How many rows does the query return?
Re: Query tunning [message #423174 is a reply to message #423167] Tue, 22 September 2009 07:27 Go to previous messageGo to next message
rumarani
Messages: 13
Registered: September 2009
Location: Bangalore
Junior Member
Hi ,

The plan path is from Dev Server.We can't run on Production server.
These two tables TMP_RISK_WEIGHT_MAP and TMP_MAP_ROWIDS are global temporary tables and there is no statistic for these tables.

Inserting more than lakhs of records during transaction and afterwards all the records are deleted from the table it is an global temp table.

Thanks.
Re: Query tunning [message #423190 is a reply to message #423174] Tue, 22 September 2009 09:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I have to ask - why have you posted a plan for a query running on the Dev server when your problem is caused by a query running on the Production server?

Have you analyzed the temporary tables?

as far as I can tell, you should either gather stats on the temporary tables every time you populate them, or (which is easier) leave them completely unanalyzed. That way, Oracle 10 will use dynamic sampling to gather data on the temp. tables and get some realistic estimates to work with.

(Look for Query Plans with Dynamic Sampling [url=http://www.oracle.com/technology/oramag/oracle/04-sep/o54asktom.html]here[/url
])
Re: Query tunning [message #423253 is a reply to message #423190] Wed, 23 September 2009 02:27 Go to previous messageGo to next message
rumarani
Messages: 13
Registered: September 2009
Location: Bangalore
Junior Member
Hi,

This the plan path attached for the query from the production server.

On the otherhand we never gather stats on the temporary tables
on production server.




Re: Query tunning [message #423291 is a reply to message #423253] Wed, 23 September 2009 04:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
1) Do you need the DISTINCT? That's taking about a minute of the total time.

2) At a rough estimate, having that function in the select list will be adding anywhere up to 1/3 to the time taken to do the select

3) What columns does SRC_FAC_DET_IDX05 index, and how selective is it?

Re: Query tunning [message #423294 is a reply to message #423291] Wed, 23 September 2009 04:22 Go to previous messageGo to next message
rumarani
Messages: 13
Registered: September 2009
Location: Bangalore
Junior Member
Hi,

Yes DISTINCT is required.

SRC_FAC_DET_IDX05 index is having the columns
(COB_DT, RUN_ID, EXTRACT_IND)and used mostly.

Re: Query tunning [message #423334 is a reply to message #423294] Wed, 23 September 2009 08:06 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
The plan path is from Dev Server.We can't run on Production server.


Can you please get the explain plan for the select part on the production server as there can be drastic differences between both instances execution plans depending on data.you will need execute rights on that procedure and read only access to prod db
.you can use set autotrace option to get execution plan
Re: Query tunning [message #423348 is a reply to message #423334] Wed, 23 September 2009 09:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@ayush - do try to read to the bottom of the thread before replying.
The OP has posted the plan for the Prod server.
Re: Query tunning [message #423351 is a reply to message #423294] Wed, 23 September 2009 09:29 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
When I say 'do you need the Distinct' I mean 'Does the data set definitely return duplicate rows if you don't have it.
Is there an extra clause or two you could add to the query to remove these duplicates?

What happens to the performance if you replace that call to the function with
floor(case when fac.last_review_dt is not null and fac.renewal_dt is not null 
             then fac.renewal_dt - fac.last_review_dt
           when fac.last_review_dt is not null and fac.renewal_dt is null     
             then fac.maturity_dt - fac.last_review_dt
           else
             then fac.maturity_dt - fac.approval_dt
           end /366) * 366
Previous Topic: Strange experince with Oracle 10g !
Next Topic: deleting row in one table using another table
Goto Forum:
  


Current Time: Mon Sep 26 04:19:59 CDT 2016

Total time taken to generate the page: 0.09312 seconds