| Sql tuning [message #504177] |
Thu, 21 April 2011 14:19  |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
I need your advise to tune the below sql. When the input value is 'O' the cost is too high for the sql.
When I executed the sql for input 'O' it went for a toss.
SQL> EXPLAIN PLAN FOR
2 SELECT
3 td.TD_ACC_TRN_DTL_ID,
4 ac.AC_GR_CODE, , ac_gr_desc
5 FROM LIC_P_TBL p,---Count 550000 (approx)
6 ACC_TBL ac, ---Count 200000(approx)
7 T_ACC_TRN at,---35700000(approx)
8 T_ACC_TRN_DTL td,--65700000(approx)
9 POL_ATTRIB_TBL pattr,--25000000(approx)
10 RISK_ATTRIB_TBL rattr--9800000(approx)
11 WHERE p.LIC_PROD_CODE = 'AGIL'
12 AND at.ACC_TRN_JREF_NBR=p.LIC_POL_NBR
13 AND at.ACC_TRN_TYPE = 'R'
14 AND at.ACC_TRN_DT BETWEEN to_date('01/01/2011', date_format)
15 AND to_date('02/28/2011', date_format)
16 AND at.ACC_TRN_STATUS = 'TR'
17 AND td.TD_ACC_TRN_ID =at.ACC_TRN_ID
18 AND td.TD_ACC_ID =ac.AC_ACC_ID
19 AND p.LIC_POL_ID = rattr.RATTR_LIC_POL_ID
20 AND rattr.RATTR_INPUT_NAME = 'PFLAG'
21 AND NVL(rattr.RATTR_POL_FIELD_1,rattr.RATTR_POL_FIELD_2) = 'I'
22 AND RATTR_EFF_END_DT IS NULL
23 AND p.LIC_POL_ID = pattr.PATTR_LIC_POL_ID
24 and rattr.RATTR_LIC_POL_ID=pattr.PATTR_LIC_POL_ID
25 AND at.TRN_TRN_LIC_POL_ID=p.LIC_POL_ID
26 AND pattr.PATTR_EFF_END_DT IS NULL
27 AND pattr.PATTR_INPUT_NAME = 'CATEGORY'
28 AND nvl(pattr.PATTR_POL_FIELD_2,pattr.PATTR_POL_FIELD_1)= 'AU';---20min
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 240 | 545 (1)|
| 1 | NESTED LOOPS | | 1 | 240 | 545 (1)|
| 2 | NESTED LOOPS | | 1 | 197 | 544 (1)|
| 3 | NESTED LOOPS | | 1 | 165 | 541 (1)|
| 4 | NESTED LOOPS | | 1 | 116 | 537 (1)|
| 5 | NESTED LOOPS | | 108 | 8100 | 220 (0)|
| 6 | INDEX RANGE SCAN | RATTR_IDX_1 | 108 | 4104 | 4 (0)|
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
| 7 | TABLE ACCESS BY INDEX ROWID| LIC_P_TBL | 1 | 37 | 2 (0)|
| 8 | INDEX UNIQUE SCAN | PK_LIC | 1 | | 1 (0)|
| 9 | TABLE ACCESS BY INDEX ROWID | POL_ATTRIB_TBL | 1 | 41 | 3 (0)|
| 10 | INDEX RANGE SCAN | PATTR_INDX_U1 | 1 | | 2 (0)|
| 11 | TABLE ACCESS BY INDEX ROWID | T_ACC_TRN | 1 | 49 | 4 (0)|
| 12 | INDEX RANGE SCAN | TRN_IDX_01 | 1 | | 3 (0)|
| 13 | INDEX RANGE SCAN | PK_AD | 2 | 64 | 3 (0)|
| 14 | TABLE ACCESS BY INDEX ROWID | ACC_TBL | 1 | 43 | 1 (0)|
| 15 | INDEX UNIQUE SCAN | ACC_PK | 1 | | 0 (0)|
--------------------------------------------------------------------------------------------------
25 rows selected.
SQL> EXPLAIN PLAN FOR
2 SELECT
3 td.TD_ACC_TRN_DTL_ID,
4 ac.AC_GR_CODE, ac_gr_desc
5 FROM LIC_P_TBL p,---Count 550000 (approx)
6 ACC_TBL ac, ---Count 200000(approx)
7 T_ACC_TRN at,---35700000(approx)
8 T_ACC_TRN_DTL td,--65700000(approx)
9 POL_ATTRIB_TBL pattr,--25000000(approx)
10 RISK_ATTRIB_TBL rattr--9800000(approx)
12 WHERE p.LIC_PROD_CODE = 'AGIL' AND at.ACC_TRN_JREF_NBR=p.LIC_POL_NBR
13 AND at.ACC_TRN_TYPE = 'C'
14 AND at.ACC_TRN_DT BETWEEN to_date('01/01/2011', date_format)
15 AND to_date('02/28/2011', date_format)
16 AND at.ACC_TRN_STATUS = 'TR'
17 AND td.TD_ACC_TRN_ID =at.ACC_TRN_ID
18 AND td.TD_ACC_ID =ac.AC_ACC_ID
19 AND p.LIC_POL_ID = rattr.RATTR_LIC_POL_ID
20 AND rattr.RATTR_INPUT_NAME = 'PFLAG'
21 AND NVL(rattr.RATTR_POL_FIELD_1,rattr.RATTR_POL_FIELD_2) = 'O'
22 AND rattr.RATTR_EFF_END_DT IS NULL
23 AND p.LIC_POL_ID = pattr.PATTR_LIC_POL_ID
24 and rattr.RATTR_LIC_POL_ID=pattr.PATTR_LIC_POL_ID
25 AND at.TRN_TRN_LIC_POL_ID=p.LIC_POL_ID
26 AND pattr.PATTR_EFF_END_DT IS NULL
27 AND pattr.PATTR_INPUT_NAME = 'CATEGORY'
28 AND nvl(pattr.PATTR_POL_FIELD_2,pattr.PATTR_POL_FIELD_1)= 'AU'; ---25+ min or more
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 240 | 7631 (1)|
| 1 | NESTED LOOPS | | 1 | 240 | 7631 (1)|
| 2 | NESTED LOOPS | | 1 | 197 | 7630 (1)|
| 3 | NESTED LOOPS | | 1 | 165 | 7627 (1)|
| 4 | HASH JOIN | | 7 | 868 | 7606 (1)|
| 5 | HASH JOIN | | 81 | 6966 | 7501 (1)|
| 6 | TABLE ACCESS BY INDEX ROWID| T_ACC_TRN | 15372 | 735K| 3822 (1)|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
| 7 | INDEX RANGE SCAN | TRN_IDX_03 | 73488 | | 227 (1)|
| 8 | TABLE ACCESS FULL | LIC_P_TBL | 172K| 6222K| 3676 (2)|
| 9 | INDEX RANGE SCAN | RATTR_IDX_1 | 15828 | 587K| 104 (0)|
| 10 | TABLE ACCESS BY INDEX ROWID | POL_ATTRIB_TBL | 1 | 41 | 3 (0)|
| 11 | INDEX RANGE SCAN | PATTR_INDX_U1 | 1 | | 2 (0)|
| 12 | INDEX RANGE SCAN | PK_AD | 2 | 64 | 3 (0)|
| 13 | TABLE ACCESS BY INDEX ROWID | ACC_TBL | 1 | 43 | 1 (0)|
| 14 | INDEX UNIQUE SCAN | ACC_PK | 1 | | 0 (0)|
--------------------------------------------------------------------------------------------------
Total records in LIC_P_TBL for LIC_PROD_CODE = 'AGIL' is around 490000
INDEX LIST:
ACC_TBL TABLE
==================
INDEX_NAME UNIQUENES COLUMN_NAME
------------------------------ --------- ------------------------------
ACC_PK UNIQUE AC_ACC_ID
ACC_UK_1 UNIQUE AC_GR_CODE
T_ACC_TRN TABLE:
INDEX_NAME UNIQUENES COLUMN_NAME
------------------------------ --------- ------------------------------
ATN_LIC_POL_ID NONUNIQUE ATN_LIC_POL_ID
ATN_PK UNIQUE ACC_TRN_ID
TRN_IDX_03 NONUNIQUE ACC_TRN_STATUS
TRN_IDX_02 NONUNIQUE ACC_TRN_DT
TRN_IDX_01 NONUNIQUE ACC_TRN_JREF_NBR
NONUNIQUE ACC_TRN_TYPE
NONUNIQUE ACC_TRN_DT
T_ACC_TRN_DTL TABLE:
INDEX_NAME UNIQUENES COLUMN_NAME
------------------------------ --------- ------------------------------
TD_IDX_002 NONUNIQUE TD_ACC_ID
NONUNIQUE ATD_MATCH_IND
PK_AD UNIQUE TD_ACC_TRN_ID
UNIQUE TD_ACC_ID
UNIQUE TD_ACC_TRN_DTL_ID
TD_IDX_001 NONUNIQUE TD_ACC_TRN_ID
LIC_P_TBL TABLE:
================
INDEX_NAME UNIQUENES COLUMN_NAME
------------------------------ --------- ------------------------------
IDX_LIC_01 NONUNIQUE LIC_PROD_CODE
NONUNIQUE POL_STATUS
NONUNIQUE POL_CREATED_DATE
PK_LIC UNIQUE LIC_POL_ID
LIC_IDX_U1 UNIQUE LIC_POL_NBR
POL_ATTRIB_TBL TABLE:
=======================
INDEX_NAME UNIQUENES COLUMN_NAME
------------------------------ --------- ------------------------------
PATTR_INDX_1 NONUNIQUE PATTR_INPUT_NAME
NONUNIQUE PATTR_LIC_POL_ID
PK_PATTR UNIQUE PATTR_LIC_POL_ID
UNIQUE P_POL_ATTR_ID
PATTR_INDX_2 NONUNIQUE PATTR_INPUT_NAME
NONUNIQUE PATTR_POL_FIELD_2
NONUNIQUE PATTR_POL_FIELD_1
RISK_ATTRIB_TBL TABLE:
========================
INDEX_NAME UNIQUENES COLUMN_NAME
------------------------------ --------- ------------------------------
IDX_1 NONUNIQUE RATTR_LIC_POL_ID
NONUNIQUE RATTR_POL_FIELD_1
PK_RATTR UNIQUE RATTR_LIC_POL_ID
UNIQUE RATTR_POL_RSK_ID
UNIQUE ATTR_POL_RSK_ATTRIB_ID
RATTR_IDX_U1 UNIQUE RATTR_LIC_POL_ID
UNIQUE RATTR_POL_RSK_ID
UNIQUE RATTR_INPUT_PARAMETER_ID
UNIQUE RATTR_EFF_BEGIN_DT
RATTR_IDX_U2 NONUNIQUE RATTR_POL_FIELD_2
NONUNIQUE RATTR_INPUT_NAME
RATTR_IDX_1 NONUNIQUE RATTR_INPUT_NAME
NONUNIQUE RATTR_POL_FIELD_1
NONUNIQUE RATTR_POL_FIELD_2
NONUNIQUE RATTR_EFF_END_DT
NONUNIQUE RATTR_LIC_POL_ID
Regards
Ved
|
|
|
|
|
|
| Re: Sql tuning [message #504181 is a reply to message #504179] |
Thu, 21 April 2011 15:03  |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
Is the first one really taking 20 mins? Cause with that plan I'd be expecting it to take seconds. Are you sure the statistics are up to date?
|
|
|
|
| Re: Sql tuning [message #504192 is a reply to message #504181] |
Thu, 21 April 2011 18:59 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
You could probably force it to use the index with a hint like /*+ FIRST_ROWS */, but if the statistics are correct then you may find that indexed access for the second query is slower overall. It will be fast to return the first row, but by the time you get through the thousands of matching rows, the current HASH JOIN plan may well be faster.
Ross Leishman
|
|
|
|
| Re: Sql tuning [message #504226 is a reply to message #504192] |
Fri, 22 April 2011 08:06 |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
Thanks for the reply.
The two sql is actually a single sql cursor with different input. The second one was to demonstrate how the plan got changes for input 'O'.
Quote:
Is the first one really taking 20 mins? Cause with that plan I'd be expecting it to take seconds. Are you sure the statistics are up to date?
My apologies I put the incorrect time. Its taking around 3-8 min for input 'I'.And for input 'O' its around 15+ min or more.
The RATTR_POL_FIELD_1 attribute is the attribute associated with the poiciy with value either I or O.For a particular policy, there may be multiple risk associated with it.
We used Bulk collect to insert the data returned from the above sql to a table containing 3000000+ rows. The plan looks bad for input 'O'.
I can see the last analysed date for all the table associated with this sql is 14 Apr 2011
Regards
Ved
|
|
|
|
| Re: Sql tuning [message #504234 is a reply to message #504226] |
Fri, 22 April 2011 12:16 |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
I want to add few more things here...
http://www.orafaq.com/scripts/plsql/bulkbind.txt
I am using bulk collect(like example in above link) to insert into a table (with no index and constraint on it but is growing every time I executed the sql). For input I the sql count is around 300000 and the table where the records are inserted is growing rapidly.
FETCH emp_cur BULK COLLECT INTO emp_tab LIMIT rows;
I have used LIMIT 1000;
I renamed the existing table to take back up of data and create the table with the same name without any data. The performance is quite good for input 'I' now..its taking around 2-3 min to insert 3 months data.
Is there any workaround to improve the performance while inserting data to a table that is growing with millions of data each time I ran the procedure.I used /*+ append */ hint.
Also, would like to add that use of /*+ first_rows */ hint increase cost by higher margin.
SELECT RATTR_POL_FIELD_1, COUNT(*) FROM rattr WHERE RATTR_POL_FIELD_1='I' GROUP BY RATTR_POL_FIELD_1 ORDER BY 1;
38717
SELECT RATTR_POL_FIELD_1, COUNT(*) FROM rattr WHERE RATTR_POL_FIELD_1='O' GROUP BY RATTR_POL_FIELD_1 ORDER BY 1;
1149000
For 3 months,
For I , rows returned : 300000
For O , rows returned : 1050000
Also, I noticed that when I ran the sql1 to get the plan now the plan is similar to the second sql. Is there anything I need to look at? For input I the cost is no more 545 !!
Last month DBA suggested to change the CURSOR_SHARING from EXACT to SIMILAR. Can it be the reason for getting the same plan for SQL1 as that of SQL2? While performing the test today again the cost for sql1 was 545 but later the cost of sql1 and sql2 become the same.
Regards
Ved
[Updated on: Fri, 22 April 2011 17:11] Report message to a moderator |
|
|
|
|
|
| Re: Sql tuning [message #504648 is a reply to message #504308] |
Wed, 27 April 2011 01:27 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
1. For code 'O' you have about 3 times more data to insert, so it takes about 3 times more time as well.
2. Can you use INSERT /*+ APPEND */ ... SELECT ... instead of cursor and BULK COLLECT?
3. The change of CURSOR_SHARING to SIMILAR may cause (and probably did) the execution plan changes.
4. Can you post TKPROF?
5. Do you have ACCESS_PREDICATES and FILTER_PREDICATES columns in your PLAN_TABLE?
Michael
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
