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 Go to next message
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 Go to previous messageGo to next message
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 #521604 is a reply to message #521603] Wed, 31 August 2011 09:12 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/mv/msg/84315/433888/136107/#msg_433888
Re: Query Performance Issue [message #521606 is a reply to message #521604] Wed, 31 August 2011 09:15 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous messageGo to next message
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 #521859 is a reply to message #521858] Fri, 02 September 2011 08:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First things:
1/ Remove the hints
2/ Gather the statistics
3/ Post all information mentionned in http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888.

Regards
Michel
Re: Query Performance Issue [message #521861 is a reply to message #521859] Fri, 02 September 2011 08:08 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do NOT repost the same question in other forums.
Especially after we've already replied to you.
I've now merged the topics.
Re: Query Performance Issue [message #521867 is a reply to message #521861] Fri, 02 September 2011 09:33 Go to previous message
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
Previous Topic: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
Next Topic: performance tuning
Goto Forum:
  


Current Time: Thu Mar 28 16:54:12 CDT 2024