Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Pietro.

Asked: May 27, 2016 - 2:11 pm UTC

Last updated: June 05, 2016 - 2:50 am UTC

Version: Oracle ODBC Instant Client Version 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi all,
I'm using Oracle ODBC Instant Client Version 12.1.0.2.0 32 bit on Windows 10 with a remote Oracle 10g database on Centos 5.11. I'm developing a web application using a C++ cgi, everything work fine in my pagination logic (many thanks to Tom Kyte for his 'On ROWNUM and Limiting Results' article) the only strange beaviour is that on last pages the SQLexecdirect slows down to 2 seconds against the 10 milliseconds of the initial pages, why?

Is this a linear degradation but I can't tell you the "delta" because it's a pagination of 2 millions of rows and it's not simple for me to wait while clicking on the "next button" from the begin to the end of rows ;-)

The query of Tom Kyte for pagination works very well but it seems that the cursor underlying the odbc drivers do not reflect the real speed of the query.

Thanks

and Connor said...

As the number of rows you "skip" grows, the amount of work we have to do grows with it. It's based on the principle that it's unlikely for someone to scroll down repeatedly....especially up to 2million rows !

Here's an example

SQL> create table t as
  2  select rownum r, d.* from dba_Objects d,
  3    ( select 1 from dual connect by level <= 20 );

Table created.

SQL> create or replace
  2  procedure searcher(p_offset int) is
  3    x int;
  4    delta int;
  5    s timestamp := systimestamp;
  6  begin
  7    select sum(value) into delta
  8    from v$mystat
  9    where statistic# in (14,   -- session logical reads
 10                         141,  -- physical reads
 11                         144);  -- physical reads direct
 12
 13    for i in (
 14    select *
 15    from (
 16      select rownum r1, x.*
 17      from
 18        ( select * from t order by r ) x
 19      where rownum <= p_offset + 100
 20    )
 21    where r1 > p_offset
 22    )
 23    loop
 24      x := i.object_id;
 25    end loop;
 26
 27    select sum(value)-delta into delta
 28    from v$mystat
 29    where statistic# in (14,   -- session logical reads
 30                         141,  -- physical reads
 31                         144);  -- physical reads direct
 32
 33    dbms_output.put_line('Work: '||delta);
 34    dbms_output.put_line('Time: '||(systimestamp-s));
 35
 36  end;
 37  /

Procedure created.

SQL>
SQL> set serverout on
SQL> exec searcher(1);
Work: 102995
Time: +000000000 00:00:00.669000000

PL/SQL procedure successfully completed.

SQL> exec searcher(1000);
Work: 102995
Time: +000000000 00:00:00.637000000

PL/SQL procedure successfully completed.

SQL> exec searcher(10000);
Work: 102995
Time: +000000000 00:00:00.680000000

PL/SQL procedure successfully completed.

SQL> exec searcher(1000000);
Work: 138879
Time: +000000000 00:00:04.882000000

PL/SQL procedure successfully completed.


So you can see that things were ok until we wanted to offset 1,000,000 rows and then we had a big dip in speed. So I trace the last two executions, and you can see why:

--
--  100000 rows
--

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
       100        100        100  VIEW  (cr=34335 pr=34330 pw=0 time=1203304 us cost=61556 size=771144680 card=1957220)
    100100     100100     100100   COUNT STOPKEY (cr=34335 pr=34330 pw=0 time=1177001 us)
    100100     100100     100100    VIEW  (cr=34335 pr=34330 pw=0 time=1155986 us cost=61556 size=745700820 card=1957220)
    100100     100100     100100     SORT ORDER BY STOPKEY (cr=34335 pr=34330 pw=0 time=1133345 us cost=61556 size=232909180 card=1957220)
   1957220    1957220    1957220      TABLE ACCESS FULL T (cr=34335 pr=34330 pw=0 time=398648 us cost=9396 size=232909180 card=1957220)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        1        0.02          0.02
  direct path read                                9        0.00          0.00
********************************************************************************

--
--  1000000 rows
--


Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
       100        100        100  VIEW  (cr=34335 pr=52271 pw=17941 time=3927721 us cost=61556 size=771144680 card=1957220)
   1000100    1000100    1000100   COUNT STOPKEY (cr=34335 pr=52271 pw=17941 time=3709735 us)
   1000100    1000100    1000100    VIEW  (cr=34335 pr=52271 pw=17941 time=3544977 us cost=61556 size=745700820 card=1957220)
   1000100    1000100    1000100     SORT ORDER BY STOPKEY (cr=34335 pr=52271 pw=17941 time=3361620 us cost=61556 size=232909180 card=1957220)
   1957220    1957220    1957220      TABLE ACCESS FULL T (cr=34335 pr=34330 pw=0 time=921024 us cost=9396 size=232909180 card=1957220)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        2        0.01          0.03
  direct path read                              117        0.04          0.52
  direct path write temp                        393        0.03          0.67
  direct path read temp                         805        0.01          0.36
********************************************************************************


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        2        0.01          0.03
  direct path read                              117        0.04          0.52
  direct path write temp                        393        0.03          0.67
  direct path read temp                         805        0.01          0.36
********************************************************************************



Notice we cross a threshold where we had to start spilling some rows to temporary storage (direct path write temp) because we had to hold on to a big chunk of rows.

If you can index the sorting column in question, then you can avoid this, but of course, if its a "free format" style of query, ie, where uses can nominate all sorts of predicates, then that's probably not going to fly. The question remains - do people really want to scroll down through 2million records ?

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

SqlExecDirect slow on

Pietro Fabrizio, May 30, 2016 - 11:04 am UTC

Hi Connor,
thank you for your deep and clear analisys but I forgot to mention in my first post that my web page has a FIRST and LAST button. The button's sql actions are respectively:

SELECT * FROM
(SELECT
/*+ FIRST_ROWS(50) */
a.*,
ROWNUM rnum
FROM
(SELECT
*
FROM mytable
) a
WHERE ROWNUM <=50
)
WHERE rnum >= 0

and

SELECT * FROM
(SELECT
/*+ FIRST_ROWS(50) */
a.*,
ROWNUM rnum
FROM
(SELECT
*
FROM mytable
) a
WHERE ROWNUM <=1909450
)
WHERE rnum >= 1909400

if I paste them from SQL Developer IDE they took about 20ms to execute each (I have tried on 15 millions of rows and Oracle speed was impressive, as always).

The odbc snippet code wich executes the sql query is the following:

db D;
statement S(D);
S.attribute(SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_STATIC);
S.attribute(SQL_ATTR_CONCURRENCY, (SQLPOINTER)SQL_CONCUR_READ_ONLY);
S.execdirect(query);



Your analisys pointed me to this direction: odbc cursors are implemented in a week manner and convinced me to use oci libraries instead of odbc.




Order by !

Rajeshwaran, Jeyabal, June 04, 2016 - 4:31 pm UTC

SELECT * FROM 
(SELECT 
/*+ FIRST_ROWS(50) */ 
a.*, 
ROWNUM rnum 
FROM (SELECT * FROM mytable ) a 
WHERE ROWNUM <=1909450 
) 
WHERE rnum >= 1909400 


Connor/Chris - correct me if i am wrong.

Without "order by"

a) records come out from the table in random order.
b) There is not first and last rows in Table unless we order them.

(script used to create big_table is here -
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1132417600346069010 )

loaded 10M rows in the table big_table

demo@ORA12C> variable start_id number
demo@ORA12C> variable end_id number
demo@ORA12C> exec :start_id := 1; :end_id := 50;

PL/SQL procedure successfully completed.

demo@ORA12C> select *
  2  from (
  3  select a.*, rownum r
  4  from ( select * from big_table t order by id ) a
  5  where rownum <= :end_id
  6       )
  7  where r >= :start_id;

50 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 5455004

---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |            |    10M|  3757M|   205K  (9)| 00:00:09 |
|*  1 |  VIEW                          |            |    10M|  3757M|   205K  (9)| 00:00:09 |
|*  2 |   COUNT STOPKEY                |            |       |       |            |          |
|   3 |    VIEW                        |            |    10M|  3633M|   205K  (9)| 00:00:09 |
|   4 |     TABLE ACCESS BY INDEX ROWID| BIG_TABLE  |    10M|  1020M|   205K  (9)| 00:00:09 |
|   5 |      INDEX FULL SCAN           | BIG_IDX_01 |    10M|       | 26450  (17)| 00:00:02 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("R">=TO_NUMBER(:START_ID))
   2 - filter(ROWNUM<=TO_NUMBER(:END_ID))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          3  physical reads
          0  redo size
       4594  bytes sent via SQL*Net to client
        551  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         50  rows processed

demo@ORA12C>


When user click the LAST button, sort them in DESC order - that way Oracle reads the index in descending order and get the last 50 rows faster ( rather than reading the rows in ascending order and keep excluding until we reach the last 50 rows)

demo@ORA12C> exec :start_id := 1; :end_id := 50;

PL/SQL procedure successfully completed.

demo@ORA12C>
demo@ORA12C> select *
  2  from (
  3  select a.*, rownum r
  4  from ( select * from big_table order by id DESC ) a
  5  where rownum <= :end_id
  6       )
  7  where r >= :start_id;

50 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1248043345

---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |            |    10M|  3757M|   205K  (9)| 00:00:09 |
|*  1 |  VIEW                          |            |    10M|  3757M|   205K  (9)| 00:00:09 |
|*  2 |   COUNT STOPKEY                |            |       |       |            |          |
|   3 |    VIEW                        |            |    10M|  3633M|   205K  (9)| 00:00:09 |
|   4 |     TABLE ACCESS BY INDEX ROWID| BIG_TABLE  |    10M|  1020M|   205K  (9)| 00:00:09 |
|   5 |      INDEX FULL SCAN DESCENDING| BIG_IDX_01 |    10M|       | 26450  (17)| 00:00:02 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("R">=TO_NUMBER(:START_ID))
   2 - filter(ROWNUM<=TO_NUMBER(:END_ID))


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

Connor McDonald
June 05, 2016 - 2:46 am UTC

Yes nice catch - there's an obvious missing order by there. Not much use having two inline views with rownum with that ! :-)

Row limiting Clause

Rajeshwaran, Jeyabal, June 04, 2016 - 4:42 pm UTC

BTW , since you are 12c, you could take the advantage of Row liming clause introduced in 12c for this.

demo@ORA12C> variable limit number
demo@ORA12C> exec :limit := 50;

PL/SQL procedure successfully completed.

demo@ORA12C> select *
  2  from big_table
  3  order by id
  4  fetch first :limit rows only  ;

50 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4004280397

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |    10M|  3881M|   205K  (9)| 00:00:09 |
|*  1 |  VIEW                         |            |    10M|  3881M|   205K  (9)| 00:00:09 |
|*  2 |   WINDOW NOSORT STOPKEY       |            |    10M|  1020M|   205K  (9)| 00:00:09 |
|   3 |    TABLE ACCESS BY INDEX ROWID| BIG_TABLE  |    10M|  1020M|   205K  (9)| 00:00:09 |
|   4 |     INDEX FULL SCAN           | BIG_IDX_01 |    10M|       | 26450  (17)| 00:00:02 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=TO_NUMBER(:LIMIT))
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "BIG_TABLE"."ID")<=TO_NUMBER(:LIMIT))


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

demo@ORA12C> select *
  2  from big_table
  3  order by id DESC
  4  fetch first :limit rows only ;

50 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 80551959

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |    10M|  3881M|   205K  (9)| 00:00:09 |
|*  1 |  VIEW                         |            |    10M|  3881M|   205K  (9)| 00:00:09 |
|*  2 |   WINDOW NOSORT STOPKEY       |            |    10M|  1020M|   205K  (9)| 00:00:09 |
|   3 |    TABLE ACCESS BY INDEX ROWID| BIG_TABLE  |    10M|  1020M|   205K  (9)| 00:00:09 |
|   4 |     INDEX FULL SCAN DESCENDING| BIG_IDX_01 |    10M|       | 26450  (17)| 00:00:02 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=TO_NUMBER(:LIMIT))
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("BIG_TABLE"."ID") DESC
              )<=TO_NUMBER(:LIMIT))


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

demo@ORA12C>

Connor McDonald
June 05, 2016 - 2:50 am UTC

Yes, but dont forget that ultimately this is a syntactical convenience. You can see from the execution plan that the new syntax is just a wrapper for our standard row_number() plus the associated pagination optimization (pushed rank)

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library