Home » RDBMS Server » Performance Tuning » Query Performance Issue (4 Merged) (Oracle 10 G)
| Query Performance Issue (4 Merged) [message #521599] |
Wed, 31 August 2011 09:01  |
 |
mani_rbs
Messages: 11
Registered: January 2011
Location: Chennai
|
Junior Member |
|
|
Hi All,
I am executing the below code with the joins on 3 tables, but the records fetched takes more than 90 mins even after I forcefully made the necessary index to be used.
SELECT /*+ index(bs NK_BASPA_1 vba pk_vbacc cba IX_FK_CLBAC_VBACC) */
bs.f1 ||'|'||
bs.f2 ||'|'||
bs.f3 ||'|'||
bs.f4 ||'|'||
TO_CHAR(bs.d1,'YYYYMMDD') ||'|'||
bs.f5 ||'|'||
TO_CHAR(bs.d2,'YYYYMMDD') ||'|'||
bs.f6 ||'|'||
bs.f7 ||'|'||
bs.f8 ||'|'||
bs.f9 ||'|'||
bs.f10 ||'|'||
bs.f11 ||'|'||
bs.f11 output_rec,
bs.f12 pk_value
FROM table1 bs,
table2 cba,
table3 vba
WHERE vba.f1=cba.f2
AND cba.f1 = bs.f2
AND bs.d2 >= to_date('01012010','DDMMYYYY');
Table1 returns 3,318,265 records
Table 2 & 3 approximately returns 20,000 records respectively.
Below is the explain plan for your reference.
STATEMENT_ID|PLAN_ID|TIMESTAMP|REMARKS|OPERATION|OPTIONS|OBJECT_NODE|OBJECT_OWNER|OBJECT_NAME|OBJECT_ALIAS|OBJECT_INSTANCE|OBJECT_TYPE|OPTIMIZER|SEARCH_COLUMNS|ID|PARENT_ID|DEPTH|POSITION|COST|CARDINALITY|BYTES|OTHER_TAG|PARTITION_START|PARTITION_STOP|PARTITION_ID|OTHER|OTHER_XML|DISTRIBUTION|CPU_COST|IO_COST|TEMP_SPACE|ACCESS_PREDICATES|FILTER_PREDICATES|PROJECTION|TIME|QBLOCK_NAME
|492|31/08/2011 14:42:07||SELECT STATEMENT||||||||RULE||0||0|59527270|59527270|3345728|324535616|||||||||59527270||||||
|492|31/08/2011 14:42:07||NESTED LOOPS||||||||||1|0|1|1|59527270|3345728|324535616||||||<other_xml><info type="db_version">10.2.0.2</info><info type="parse_schema"><![CDATA["VBANK"]]></info><info type="plan_hash">3830922959</info><outline_data><hint><![CDATA[USE_NL(@"SEL$1" "VBA"@"SEL$1")]]></hint><hint><![CDATA[USE_NL(@"SEL$1" "CBA"@"SEL$1")]]></hint><hint><![CDATA[LEADING(@"SEL$1" "BS"@"SEL$1" "CBA"@"SEL$1" "VBA"@"SEL$1")]]></hint><hint><![CDATA[INDEX(@"SEL$1" "VBA"@"SEL$1" ("table3"."f1"))]]></hint><hint><![CDATA[INDEX(@"SEL$1" "CBA"@"SEL$1" ("table2"."CLIENT_REF"))]]></hint><hint><![CDATA[INDEX(@"SEL$1" "BS"@"SEL$1" ("table1"."CLIENT_REF"))]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('8.1.7')]]></hint><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint></outline_data></other_xml>|||59527270||||(#keys=0) "BS"."SCRATCHPAD_SEQ"[NUMBER,22], "BS"."SCREEN_NAME"[VARCHAR2,8], "BS"."CLIENT_REF"[NUMBER,22], "BS"."APPLICATION_REF"[NUMBER,22], "BS"."CREATED_DATE"[DATE,7], "BS"."CREATED_USER"[VARCHAR2,10], "BS"."CHANGED_DATE"[DATE,7], "BS"."CHANGED_USER"[VARCHAR2,10], "BS"."CATEGORY"[VARCHAR2,3], "BS"."INFORMATION"[VARCHAR2,2000], "BS"."STATUS"[VARCHAR2,3], "BS"."CL_WF_PROCESS_SEQ"[NUMBER,22], "BS"."BKLIA_SEQ"[NUMBER,22], "BS"."CHECKMATE_SEQ"[NUMBER,22]||SEL$1
|492|31/08/2011 14:42:07||NESTED LOOPS||||||||||2|1|2|1|56181542|3345728|304461248|||||||||56181542||||(#keys=0) "BS"."f1"[NUMBER,22], "BS"."F2"[VARCHAR2,8], "BS"."f3"[NUMBER,22], "BS"."f4"[NUMBER,22], "BS"."f5"[DATE,7], "BS"."f6"[VARCHAR2,10], "BS"."d1"[DATE,7], "BS"."f7"[VARCHAR2,10], "BS"."f8"[VARCHAR2,3], "BS"."f9"[VARCHAR2,2000], "BS"."f10"[VARCHAR2,3], "BS"."f11"[NUMBER,22], "BS"."f12"[NUMBER,22], "BS"."f13"[NUMBER,22], "CBA"."f1"[NUMBER,22]||
|492|31/08/2011 14:42:07||TABLE ACCESS|BY INDEX ROWID||VBANK|table1|BS@SEL$1|1|TABLE|ANALYZED||3|2|3|1|46199222|3327440|262867760|||||||||46199222|||"BS"."d2">=TO_DATE('2010-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')|"BS"."f1"[NUMBER,22], "BS"."f2"[VARCHAR2,8], "BS"."f3"[NUMBER,22], "BS"."f4"[NUMBER,22], "BS"."d1"[DATE,7], "BS"."f5"[VARCHAR2,10], "BS"."d2"[DATE,7], "BS"."f6"[VARCHAR2,10], "BS"."f7"[VARCHAR2,3], "BS"."f8"[VARCHAR2,2000], "BS"."f9"[VARCHAR2,3], "BS"."f10"[NUMBER,22], "BS"."f11"[NUMBER,22], "BS"."f12"[NUMBER,22]||SEL$1
|492|31/08/2011 14:42:07||INDEX|FULL SCAN||VBANK|NK_BASPA_1|BS@SEL$1||INDEX|ANALYZED||4|3|4|1|163931|50042057||||||||||163931||||"BS".ROWID[ROWID,10], "BS"."f3"[NUMBER,22]||SEL$1
|492|31/08/2011 14:42:07||TABLE ACCESS|BY INDEX ROWID||VBANK|table2|CBA@SEL$1|2|TABLE|ANALYZED||5|2|3|2|3|1|12|||||||||3||||"CBA"."f2"[NUMBER,22]||SEL$1
|492|31/08/2011 14:42:07||INDEX|RANGE SCAN||VBANK|IX_CLBAC_CLIREF|CBA@SEL$1||INDEX|ANALYZED|1|6|5|4|1|2|1||||||||||2||"CBA"."f1"="BS"."f3"||"CBA".ROWID[ROWID,10]||SEL$1
|492|31/08/2011 14:42:07||INDEX|UNIQUE SCAN||VBANK|PK_VBACC|VBA@SEL$1||INDEX (UNIQUE)|ANALYZED|1|7|1|2|2|1|1|6|||||||||1||"VBA"."f1"="CBA"."f2"||||SEL$1
|
|
|
|
| Query Performance Issue [message #521603 is a reply to message #521599] |
Wed, 31 August 2011 09:08  |
|
mani_rbs
Messages: 11
Registered: January 2011
Location: Chennai
|
Junior Member |
|
|
Hi All,
I am executing the below code with the joins on 3 tables, but the records fetched takes more than 90 mins even after I forcefully made the necessary index to be used.
SELECT /*+ index(bs NK_BASPA_1 vba pk_vbacc cba IX_FK_CLBAC_VBACC) */
bs.f1 ||'|'||
bs.f2 ||'|'||
bs.f3 ||'|'||
bs.f4 ||'|'||
TO_CHAR(bs.d1,'YYYYMMDD') ||'|'||
bs.f5 ||'|'||
TO_CHAR(bs.d2,'YYYYMMDD') ||'|'||
bs.f6 ||'|'||
bs.f7 ||'|'||
bs.f8 ||'|'||
bs.f9 ||'|'||
bs.f10 ||'|'||
bs.f11 ||'|'||
bs.f11 output_rec,
bs.f12 pk_value
FROM table1 bs,
table2 cba,
table3 vba
WHERE vba.f1=cba.f2
AND cba.f1 = bs.f2
AND bs.d2 >= to_date('01012010','DDMMYYYY');
Table1 returns 3,318,265 records
Table 2 & 3 approximately returns 20,000 records respectively.
Below is the explain plan for your reference.
STATEMENT_ID|PLAN_ID|TIMESTAMP|REMARKS|OPERATION|OPTIONS|OBJECT_NODE|OBJECT_OWNER|OBJECT_NAME|OBJECT_ALIAS|OBJECT_INSTANCE|OBJECT_TYPE|OPTIMIZER|SEARCH_COLUMNS|ID|PARENT_ID|DEPTH|POSITION|COST|CARDINALITY|BYTES|OTHER_TAG|PARTITION_START|PARTITION_STOP|PARTITION_ID|OTHER|OTHER_XML|DISTRIBUTION|CPU_COST|IO_COST|TEMP_SPACE|ACCESS_PREDICATES|FILTER_PREDICATES|PROJECTION|TIME|QBLOCK_NAME
|492|31/08/2011 14:42:07||SELECT STATEMENT||||||||RULE||0||0|59527270|59527270|3345728|324535616|||||||||59527270||||||
|492|31/08/2011 14:42:07||NESTED LOOPS||||||||||1|0|1|1|59527270|3345728|324535616||||||<other_xml><info type="db_version">10.2.0.2</info><info type="parse_schema"><![CDATA["VBANK"]]></info><info type="plan_hash">3830922959</info><outline_data><hint><![CDATA[USE_NL(@"SEL$1" "VBA"@"SEL$1")]]></hint><hint><![CDATA[USE_NL(@"SEL$1" "CBA"@"SEL$1")]]></hint><hint><![CDATA[LEADING(@"SEL$1" "BS"@"SEL$1" "CBA"@"SEL$1" "VBA"@"SEL$1")]]></hint><hint><![CDATA[INDEX(@"SEL$1" "VBA"@"SEL$1" ("table3"."f1"))]]></hint><hint><![CDATA[INDEX(@"SEL$1" "CBA"@"SEL$1" ("table2"."CLIENT_REF"))]]></hint><hint><![CDATA[INDEX(@"SEL$1" "BS"@"SEL$1" ("table1"."CLIENT_REF"))]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('8.1.7')]]></hint><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint></outline_data></other_xml>|||59527270||||(#keys=0) "BS"."SCRATCHPAD_SEQ"[NUMBER,22], "BS"."SCREEN_NAME"[VARCHAR2,8], "BS"."CLIENT_REF"[NUMBER,22], "BS"."APPLICATION_REF"[NUMBER,22], "BS"."CREATED_DATE"[DATE,7], "BS"."CREATED_USER"[VARCHAR2,10], "BS"."CHANGED_DATE"[DATE,7], "BS"."CHANGED_USER"[VARCHAR2,10], "BS"."CATEGORY"[VARCHAR2,3], "BS"."INFORMATION"[VARCHAR2,2000], "BS"."STATUS"[VARCHAR2,3], "BS"."CL_WF_PROCESS_SEQ"[NUMBER,22], "BS"."BKLIA_SEQ"[NUMBER,22], "BS"."CHECKMATE_SEQ"[NUMBER,22]||SEL$1
|492|31/08/2011 14:42:07||NESTED LOOPS||||||||||2|1|2|1|56181542|3345728|304461248|||||||||56181542||||(#keys=0) "BS"."f1"[NUMBER,22], "BS"."F2"[VARCHAR2,8], "BS"."f3"[NUMBER,22], "BS"."f4"[NUMBER,22], "BS"."f5"[DATE,7], "BS"."f6"[VARCHAR2,10], "BS"."d1"[DATE,7], "BS"."f7"[VARCHAR2,10], "BS"."f8"[VARCHAR2,3], "BS"."f9"[VARCHAR2,2000], "BS"."f10"[VARCHAR2,3], "BS"."f11"[NUMBER,22], "BS"."f12"[NUMBER,22], "BS"."f13"[NUMBER,22], "CBA"."f1"[NUMBER,22]||
|492|31/08/2011 14:42:07||TABLE ACCESS|BY INDEX ROWID||VBANK|table1|BS@SEL$1|1|TABLE|ANALYZED||3|2|3|1|46199222|3327440|262867760|||||||||46199222|||"BS"."d2">=TO_DATE('2010-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')|"BS"."f1"[NUMBER,22], "BS"."f2"[VARCHAR2,8], "BS"."f3"[NUMBER,22], "BS"."f4"[NUMBER,22], "BS"."d1"[DATE,7], "BS"."f5"[VARCHAR2,10], "BS"."d2"[DATE,7], "BS"."f6"[VARCHAR2,10], "BS"."f7"[VARCHAR2,3], "BS"."f8"[VARCHAR2,2000], "BS"."f9"[VARCHAR2,3], "BS"."f10"[NUMBER,22], "BS"."f11"[NUMBER,22], "BS"."f12"[NUMBER,22]||SEL$1
|492|31/08/2011 14:42:07||INDEX|FULL SCAN||VBANK|NK_BASPA_1|BS@SEL$1||INDEX|ANALYZED||4|3|4|1|163931|50042057||||||||||163931||||"BS".ROWID[ROWID,10], "BS"."f3"[NUMBER,22]||SEL$1
|492|31/08/2011 14:42:07||TABLE ACCESS|BY INDEX ROWID||VBANK|table2|CBA@SEL$1|2|TABLE|ANALYZED||5|2|3|2|3|1|12|||||||||3||||"CBA"."f2"[NUMBER,22]||SEL$1
|492|31/08/2011 14:42:07||INDEX|RANGE SCAN||VBANK|IX_CLBAC_CLIREF|CBA@SEL$1||INDEX|ANALYZED|1|6|5|4|1|2|1||||||||||2||"CBA"."f1"="BS"."f3"||"CBA".ROWID[ROWID,10]||SEL$1
|492|31/08/2011 14:42:07||INDEX|UNIQUE SCAN||VBANK|PK_VBACC|VBA@SEL$1||INDEX (UNIQUE)|ANALYZED|1|7|1|2|2|1|1|6|||||||||1||"VBA"."f1"="CBA"."f2"||||SEL$1
|
|
|
|
|
|
| Re: Query Performance Issue [message #521606 is a reply to message #521604] |
Wed, 31 August 2011 09:15 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
This is the correct way to get an explain plan:
SQL> explain plan for select * from dual;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3543395131
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
SQL>
What you've posted is completely unreadable.
|
|
|
|
| Re: Query Performance Issue [message #521779 is a reply to message #521606] |
Fri, 02 September 2011 01:18 |
|
mani_rbs
Messages: 11
Registered: January 2011
Location: Chennai
|
Junior Member |
|
|
Hi,
Sorry for the confusion, below is the plan table I have pasted .
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3830922959
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2515K| 232M| 56M|
| 1 | NESTED LOOPS | | 2515K| 232M| 56M|
| 2 | NESTED LOOPS | | 2515K| 218M| 53M|
|* 3 | TABLE ACCESS BY INDEX ROWID| table1 | 2502K| 188M| 46M|
| 4 | INDEX FULL SCAN | NK_BASPA_1 | 50M| | 163K|
| 5 | TABLE ACCESS BY INDEX ROWID| table2 | 1 | 12 | 3 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 6 | INDEX RANGE SCAN | IX_CLBAC_CLIREF | 1 | | 2 |
|* 7 | INDEX UNIQUE SCAN | PK_VBACC | 1 | 6 | 1 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(NVL("BS"."d2","BS"."d1")>=TO_DATE('2010-01-0
1 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
6 - access("CBA"."f1"="BS"."f3")
7 - access("VBA"."f1"="CBA"."f2")
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Note
-----
- cpu costing is off (consider enabling it)
26 rows selected.
CM: fixed the formatting
[Updated on: Fri, 02 September 2011 03:53] by Moderator Report message to a moderator |
|
|
|
| Re: Query Performance Issue [message #521801 is a reply to message #521779] |
Fri, 02 September 2011 03:55 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
If you're going to modify the explain plan to hide tables, fix the formatting afterwards, I've done it this time.
I doubt you want to be using the index NK_BASPA_1 - looks like a full table scan of table1 is required.
|
|
|
|
| Query Performance Issue [message #521858 is a reply to message #521599] |
Fri, 02 September 2011 07:56 |
|
mani_rbs
Messages: 11
Registered: January 2011
Location: Chennai
|
Junior Member |
|
|
Hi All,
I am running the following query
SELECT /*+ index(vba pk_vbacc bs NK_BASPA_1 cba IX_CLBAC_CLIREF ) */
bs.f1 ||'|'||
bs.f2 ||'|'||
bs.client_ref ||'|'||
bs.f3 ||'|'||
TO_CHAR(bs.d1,'YYYYMMDD') ||'|'||
bs.f4 ||'|'||
TO_CHAR(bs.changed_date,'YYYYMMDD') ||'|'||
bs.f5 ||'|'||
bs.f6 ||'|'||
bs.f7 ||'|'||
bs.f8 ||'|'||
bs.f9 ||'|'||
bs.f10 ||'|'||
bs.f11 output_rec,
bs.f12 pk_value
FROM scratchpads bs,
client_accounts cba,
virgin_accounts vba
WHERE bs.changed_date >= to_date('0101201o','DDMMYYYY')
AND bs.client_ref=cba.client_ref
AND cba.vb_account_Seq=vba.vb_account_seq
AND vba.vb_sort_code=166053;
Scratch Pad returns approximately 125000 records
Cleint_accounts,virgin_accounts returns approximately 20000 recs.
When I execute this, it takes approximately 80-90 mins. to get all the 125000 recs., I have to reduce the speed atleast half of it. Request your valuable inputs on fine tuning this.
Please find Plan below
SQL> select * from table(dbms_xplan.display);
Plan hash value: 3337920922
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 102 | 270K|
| 1 | NESTED LOOPS | | 1 | 102 | 270K|
| 2 | NESTED LOOPS | | 2 | 182 | 270K|
|* 3 | TABLE ACCESS FULL | SCRATCHPADS | 2 | 158 | 270K|
| 4 | TABLE ACCESS BY INDEX ROWID| CLIENT_ACCOUNTS | 1 | 12 | 3 |
|* 5 | INDEX RANGE SCAN | IX_CLBAC_CLIREF | 1 | | 2 |
|* 6 | TABLE ACCESS BY INDEX ROWID | VIRGIN_ACCOUNTS | 1 | 11 | 2 |
|* 7 | INDEX UNIQUE SCAN | PK_VBACC | 1 | | 1 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("BS"."CHANGED_DATE">=TO_DATE('2011-01-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss'))
5 - access("BS"."CLIENT_REF"="CBA"."CLIENT_REF")
6 - filter("VBA"."VB_SORT_CODE"=166053)
7 - access("CBA"."VB_ACCOUNT_SEQ"="VBA"."VB_ACCOUNT_SEQ")
Note
-----
- cpu costing is off (consider enabling it)
27 rows selected.
|
|
|
|
|
|
|
|
| Re: Query Performance Issue [message #521867 is a reply to message #521861] |
Fri, 02 September 2011 09:33 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>I am running the following query
Forgive me but I do NOT believe you.
>WHERE bs.changed_date >= to_date('0101201o','DDMMYYYY')
NOTE lower case letter "o" in string above.
posted EXPLAIN PLAN is NOT from system with poor performance based upon row counts.
Since you post bogus details, you do not deserve any assistance
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 01:29:16 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
