This discussion is archived
1 Reply Latest reply: Sep 18, 2013 10:03 PM by Ajai E RSS

Performance Issue

Ajai E Newbie
Currently Being Moderated

Hi,

 

Recently we loaded data in CertDB-ONDEMAND-DB for data profiling using EDQ. We found that there a simple count query returns very long (15 min - on ONDEMAND-DB) compared to DB installed in windows desktop (1 min - on win7, 12GB ram).
An example query: SELECT COUNT(*) FROM LUMENPROF.CARD;

We compare explain plan from ONDEMAND-DB vs local machine (attached to SR), they look very similar.
We need to tune/figure out why ONDEMAND-DB is taking 15 mins.

 

Thanks

Ajai

  • 1. Re: Performance Issue
    user11240176 Newbie
    Currently Being Moderated

    Below example should help you understand:

     

    SQL> create table demo1 as select * from all_objects;

    Table created.

    SQL> create table demo2 as select * from all_objects;

    Table created.

    SQL> insert into demo2 select * from demo2;

    21282 rows created.

    SQL> /

    42564 rows created.

    SQL> /

    85128 rows created.

    SQL> /

    170256 rows created.

    SQL> /

    340512 rows created.

    SQL> /

    681024 rows created.

    SQL> rollback;

    Rollback complete.

    SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'DEMO1')

    PL/SQL procedure successfully completed.

    SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'DEMO2')

    PL/SQL procedure successfully completed.

    SQL> set autotrace traceonly exp stat
    SQL> set timi on
    SQL> select count(*) from demo1;

    Elapsed: 00:00:00.01

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3972059614

    --------------------------------------------------------------------
    | Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
    --------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |       |     1 |    89   (2)| 00:00:02 |
    |   1 |  SORT AGGREGATE    |       |     1 |            |          |
    |   2 |   TABLE ACCESS FULL| DEMO1 | 21281 |    89   (2)| 00:00:02 |
    --------------------------------------------------------------------


    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
            309  consistent gets
              0  physical reads
              0  redo size
            524  bytes sent via SQL*Net to client
            520  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed

    SQL> select count(*) from demo2;

    Elapsed: 00:00:00.04

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1256647672

    --------------------------------------------------------------------
    | Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
    --------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |       |     1 |  5528   (1)| 00:01:07 |
    |   1 |  SORT AGGREGATE    |       |     1 |            |          |
    |   2 |   TABLE ACCESS FULL| DEMO2 | 21282 |  5528   (1)| 00:01:07 |
    --------------------------------------------------------------------


    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
          19550  consistent gets
              0  physical reads
              0  redo size
            524  bytes sent via SQL*Net to client
            520  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed

    SQL> set autotrace off

     

    SQL> select table_name,blocks from dba_tables where table_name in ('DEMO1','DEMO2');

    TABLE_NAME                         BLOCKS
    ------------------------------ ----------
    DEMO1                                 319
    DEMO2                               20311

     

    Oracle needs to scan all the blocks under high water mark. Though plan looks same, one table has more blocks compared to the other.

     

    Thanks,

    Asif Haliyal

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points