#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    12
    Rep Power
    0

    Query Performance


    Hi Everyone,

    I have a query that take 2mins to run, apparently because of the WHERE clause.
    If I remove one of the joins then it takes 1 sec and result is the same and correct in both cases.

    The Query is this:
    select ARTDIM.PC as c0, ARTDIM.ARHG as c3, TIME_FK.YEAR as c6, TIME_FK.QUARTN as c7, TIME_FK.MONTHNL as c8, TIME_FK.PERIODE as c9,
    SALESFACTS_F.TYPE as c10,
    FIRM.COMPANY as c11, FIRM.FIRMCODE as c12, FIRM.WAREHOUSECODE as c13, FIRM.FRMWHS as c14,
    ARTDIM.ARTSRT as c15, ARTDIM.ARTSRT_DESC as c16, ARTDIM.ARTSRT as c17
    from PENTAHO_DW.ARTDIM ARTDIM, PENTAHO_DW.SALESFACTS_F SALESFACTS_F, PENTAHO_DW.DW_DATE_A TIME_FK, PENTAHO_DW.KLADIM FIRM
    where SALESFACTS_F.ARNR41 = ARTDIM.ARNR30 and SALESFACTS_F.FKDT41 = TIME_FK.DATENUM
    and SALESFACTS_F.KLNRADNR = FIRM.KLRNADNR and (TIME_FK.PERIODE = 201001 and
    TIME_FK.MONTHNL = 'Januari'
    and
    TIME_FK.QUARTN = 'Q1' and TIME_FK.YEAR = 2010)


    No need to read it all, I've identified the problem but just don't know why it slows it down that much and how to fix it.

    The prob is related to this part of the WHERE clause:
    TIME_FK.PERIODE = 201001 and
    TIME_FK.MONTHNL = 'Januari'

    If I remove one of these 2 conditions then it works in 1 second and the result is the same as the same data is actually retrieved. Problem is that this is a SQL generated by a thirdparty application so I can't amend the SQL manually.

    For info the TIME_FK table is setup like this:
    YEAR | QUARTN | MONTHNL | PERIODE | DATE (dd/mm/yyyy)
    2010 | Q1 | Januari | 201101 | 1/1/2010
    2010 | Q1 | Januari | 201101 | 2/1/2010
    2010 | Q1 | Januari | 201101 | 3/1/2010
    2010 | Q1 | Januari | 201101 | 4/1/2010
    2010 | Q1 | Januari | 201101 | etc...
    2010 | Q3 | July | 201107 | 1/7/2010
    2010 | Q3 | July | 201107 | 2/7/2010
    2010 | Q3 | July | 201107 | 3/7/2010
    etc..

    Any idea why having either PERIODE or MONTHNL in the statement makes the query extremely slow whilst it's actually retrieving the same thing?
    Thanks for any input!
    Cheers
    Larry
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    58
    Rep Power
    5
    Hi,

    obviously these additional predicates change the execution plan of the query (verify that) and result in a full table scan of the PENTAHO_DW.DW_DATE_A - table instead of an index range scan.

    try the following (as user PENTAHO_DW) :

    Code:
    create index IND_DW_DATE_A_CONCAT on DW_DATE_A(PERIODE,MONTHNL,QUARTN,YEAR);
    
    exec dbms_stats.gather_index_stats('PENTAHO_DW','IND_DW_DATE_A_CONCAT');
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    12
    Rep Power
    0
    I ran your code but doesn't change anything.
    I also checked via the schema browser (I use toad), and I can clearly see the index has been created properly.
    Still, it takes the same amount of time, so it doesn't help.
    There are indexes on pretty much all single columns of that data table, could that cause this prob?

    Thanks again for your help!
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    58
    Rep Power
    5
    O.K., so you can drop the index again.

    Regarding your question about single column indexes: Hard to tell without seeing the execution plan, but per se single column indexes are not bad.

    Can you post the execution plans for both, the "fast" and the "slow" query ?

    BTW: how many rows are in the involved tables and are the statistics (for both the tables and the indexes) up to date ?
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    12
    Rep Power
    0
    I wanted to post the explain plan but it doesn't let me run it, I guess it's a matter of privileges, the PENTAHO_DW user doesn't have DBA rights.
    I'll try to find out a way to get that.

    As for the nb of records, the fact (salesfacts_F) is 2.000.000 records big, kladim is 70.000 (customers), the date table is 8700 records big, and ARTDIM contain 650.000 unique articles.
    I doubt this is too big though, since it works perfectly unless I use that extra condition on DATES (the smallest table of all) in the query.

    Thanks for your help!
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    58
    Rep Power
    5
    You don't need the DBA role to have a look at the Execution plan:

    Use the "set autotrace on" statement like in the example below (and PLEASE use Code-Tags, so it stays readable):
    Code:
    SQL> connect test/test
    Connected.
    SQL> create table tsttbl (a number, b varchar2(128));
    
    Table created.
    
    SQL> insert into tsttbl (select rownum, 'Line # ' || rownum from all_objects where rownum < 1001);
    
    1000 rows created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> set autotrace on
    SQL> select * from tsttbl where a = 111;
    
             A B
    ---------- --------------------------------------------------------------------------------------------------------------------------------
           111 Line # 111
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1259039243
    
    ----------------------------------------------------------------------------
    | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |        |     1 |    79 |     3   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| TSTTBL |     1 |    79 |     3   (0)| 00:00:01 |
    ----------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("A"=111)
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    
    Statistics
    ----------------------------------------------------------
              5  recursive calls
              0  db block gets
             16  consistent gets
              0  physical reads
              0  redo size
            594  bytes sent via SQL*Net to client
            519  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> alter table tsttbl add constraint pk_tsttbl primary key (a);
    
    Table altered.
    
    SQL> execute dbms_stats.gather_schema_stats(ownname=>'test',estimate_percent=>null,cascade=>true);
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from tsttbl where a = 111;
    
             A B
    ---------- --------------------------------------------------------------------------------------------------------------------------------
           111 Line # 111
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1036174725
    
    -----------------------------------------------------------------------------------------
    | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |           |     1 |    15 |     2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| TSTTBL    |     1 |    15 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX UNIQUE SCAN         | PK_TSTTBL |     1 |       |     1   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("A"=111)
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
              3  consistent gets
              0  physical reads
              0  redo size
            462  bytes sent via SQL*Net to client
            508  bytes received via SQL*Net from client
              1  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> set autotrace off
    SQL>
    Last edited by magicwand; December 15th, 2011 at 02:24 AM.
  12. #7
  13. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    834
    Rep Power
    387
    Originally Posted by larryleisure
    I wanted to post the explain plan but it doesn't let me run it, I guess it's a matter of privileges, the PENTAHO_DW user doesn't have DBA rights.
    I'll try to find out a way to get that.

    As for the nb of records, the fact (salesfacts_F) is 2.000.000 records big, kladim is 70.000 (customers), the date table is 8700 records big, and ARTDIM contain 650.000 unique articles.
    I doubt this is too big though, since it works perfectly unless I use that extra condition on DATES (the smallest table of all) in the query.

    Thanks for your help!
    You can use TOAD to get the execution plan:
    1) copy the query to editor window and click on the "ambulance" icon
    2) or go to session browser, execute the query and look at the execution plan for your session.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    12
    Rep Power
    0
    oh ok, thanks for the tip about the ambulance
    Sorry couldn't reply earlier, but I'm now back at this and strangely enough, now that query runs fine, doesn't matter if I leave or remove the PERIOD in the where clause, it takes less than 3 secs to complete... so it's good but I don't know why it now works and not last time :/

    Can it be that it takes time for indexes to take effect?
    Any idea why this is?

    For info, the explain plan for the above query is


    I have another one that's slow now, and thanks to the explain plan I can see there's a full table scan somewhere.
    I assume all I have to do is make sure there's an index on the column that creates a full table scan, right? Anything else?

    Thanks again everyone for the help and for pointing me in the right directions

IMN logo majestic logo threadwatch logo seochat tools logo