This discussion is archived
1 Reply Latest reply: Mar 27, 2013 10:09 AM by damorgan RSS

pagination query ?

961833 Newbie
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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.

Legend

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