Query tunning [message #422560] |
Thu, 17 September 2009 07:29  |
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 #422666 is a reply to message #422560] |
Fri, 18 September 2009 03:39   |
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 callfn_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   |
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   |
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 #422705 is a reply to message #422699] |
Fri, 18 September 2009 07:52   |
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   |
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   |
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 #423125 is a reply to message #423088] |
Tue, 22 September 2009 04:46   |
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 #423166 is a reply to message #423159] |
Tue, 22 September 2009 07:03   |
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   |
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   |
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   |
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   |
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   |
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   |
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   |
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   |
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  |
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
|
|
|