1 Reply Latest reply: Sep 19, 2013 12:03 AM by Ajai E-Oracle RSS

    Performance Issue

    Ajai E-Oracle

      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

          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