1 Reply Latest reply: Mar 27, 2013 12:09 PM by damorgan RSS

    pagination query ?

    961833
      select * from v$version;
      
      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
      PL/SQL Release 11.2.0.2.0 - Production
      CORE    11.2.0.2.0      Production
      TNS for Linux: Version 11.2.0.2.0 - Production
      NLSRTL Version 11.2.0.2.0 - Production
      SQL
      SELECT * FROM (SELECT * FROM TAB where ID=:1 and TIME >= :2 and TIME < :3 order by TIME  desc ) where rownum <= 500;
      Plan hash value: 725504092
      
      -------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                             | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
      -------------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                      |                 |     1 |  7155 |     6  (17)| 00:00:01 |       |       |
      |*  1 |  COUNT STOPKEY                        |                 |       |       |            |          |       |       |
      |   2 |   VIEW                                |                 |     1 |  7155 |     6  (17)| 00:00:01 |       |       |
      |*  3 |    FILTER                             |                 |       |       |            |          |       |       |
      |   4 |     PARTITION HASH SINGLE             |                 |     1 |   828 |     5   (0)| 00:00:01 |   KEY |   KEY |
      |   5 |      TABLE ACCESS BY LOCAL INDEX ROWID| TAB             |     1 |   828 |     5   (0)| 00:00:01 |   KEY |   KEY |
      |*  6 |       INDEX RANGE SCAN                | IDX             |     1 |       |     4   (0)| 00:00:01 |   KEY |   KEY |
      -------------------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         1 - filter(ROWNUM<=500)
         3 - filter(TO_TIMESTAMP(:2)<TO_TIMESTAMP(:3))
         6 - access("ID"=:1 AND SYS_OP_DESCEND("TIME")>SYS_OP_DESCEND(TO_TIMESTAMP(:3)) AND
                    SYS_OP_DESCEND("TIME")<=SYS_OP_DESCEND(TO_TIMESTAMP(:2)))
             filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("TIME"))>=TO_TIMESTAMP(:2) AND
                    SYS_OP_UNDESCEND(SYS_OP_DESCEND("TIME"))<TO_TIMESTAMP(:3))
      Index DDL
      CREATE INDEX IDX ON TAB (ID, TIME DESC) TABLESPACE IDX LOCAL;
      
      Table TAB is HASH partitioned by ID (16 hash partitions)
      If this is a pagination query, why is it having different statistics (buffer_gets and elapsed_time) for different executions ?
      set  pagesize 90
      col time for a20
      select a.sql_id,TO_DATE(TO_CHAR(b.BEGIN_INTERVAL_TIME,'DD-MON-YY HH24:MI'),'DD-MON-YY HH24:MI') as sdate,a.PLAN_HASH_VALUE,a.EXECUTIONS_TOTAL EXE_T,a.EXECUTIONS_DELTA EXE_D,
      case (executions_delta) when 0 then 0 else round(a.ROWS_PROCESSED_DELTA/a.EXECUTIONS_DELTA,1) end as RD,
      case (executions_delta) when 0 then 0 else round(a.ELAPSED_TIME_TOTAL/a.EXECUTIONS_DELTA/1000000/60/60,2) end as  ELA_HR_D,
      case (executions_delta) when 0 then 0 else round(a.BUFFER_GETS_DELTA/a.EXECUTIONS_DELTA) end as BG_D
      from dba_hist_sqlstat a ,dba_hist_snapshot b where a.snap_id=b.snap_id and a.sql_id = '&sql_id'
      and b.BEGIN_INTERVAL_TIME>=sysdate-1 and b.BEGIN_INTERVAL_TIME <=sysdate order by 1,2;
      
      Enter value for sql_id: 08tb4pz84jtus
      
      SQL_ID         SDATE              PLAN_HASH_VALUE      EXE_T      EXE_D         RD   ELA_HR_D       BG_D
      -------------- ------------------ --------------- ---------- ---------- ---------- ---------- ----------
      08tb4pz84jtus  26.Mar.13/16:50:00       725504092      24716         26      181.2        .89     114390
      08tb4pz84jtus  26.Mar.13/17:00:00       725504092      24720          4      109.5       5.87     640252
      08tb4pz84jtus  26.Mar.13/17:10:00       725504092      24722          2         71      11.78     109788
      08tb4pz84jtus  26.Mar.13/17:30:00       725504092      24762         18      103.6       1.31        876
      08tb4pz84jtus  26.Mar.13/17:40:00       725504092      24790         28       52.5        .84       2924
      08tb4pz84jtus  26.Mar.13/18:20:00       725504092      24895         25      130.5        .94        366
      08tb4pz84jtus  27.Mar.13/09:20:00       725504092         29         15       71.3          0        152
      08tb4pz84jtus  27.Mar.13/12:00:00       725504092         82         24       23.1          0        811
      08tb4pz84jtus  27.Mar.13/15:30:00       725504092        313         14      191.3        .02     514276
      08tb4pz84jtus  27.Mar.13/15:40:00       725504092        323         10       37.1        .08    1204950
      08tb4pz84jtus  27.Mar.13/15:50:00       725504092        331          8         36        .16    1481798
      08tb4pz84jtus  27.Mar.13/16:00:00       725504092        337          6       51.3         .3    1925338
      08tb4pz84jtus  27.Mar.13/16:10:00       725504092        361         24         96         .1     497692
      08tb4pz84jtus  27.Mar.13/16:20:00       725504092        375         14       17.3         .2     815690
      08tb4pz84jtus  27.Mar.13/16:30:00       725504092        405         30       18.5        .11     336556
      
      Table has couple of ID which has millions of records. However, to limit large index scan for such id, i tried to form pagination type query so as to stop index scan at 500 rowids and return rows to service page.
      This sql got couple of concurrent executions and thus most of them are waiting for 'read by other sessions' wait event.
        • 1. Re: pagination query ?
          damorgan
          I would be surprised if they were the same every time. Consider just something as simple as:
          set timing on
          
          SELECT COUNT(*)
          FROM dba_source;
          I can run it repeatedly with nothing more than "/" and get substantial deltas.