Skip navigation.

XTended Oracle SQL

Syndicate content
XTended Oracle SQL
Updated: 11 hours 33 sec ago

select * from table where rownum=1

Sun, 2015-02-08 17:40

I never thought I would have to optimize so simple query as

select col1, col2, col4, col7 from table where rownum=1

(even though I read recently “SELECT * FROM TABLE” Runs Out Of TEMP Space)
But a few days ago frequent executions of this query caused big problems on the one of our databases(11.2.0.3) because of adaptive serial direct path reads.

I don’t know why, but I felt intuitively that full table scan with “First K rows” optimization (“_optimizer_rownum_pred_based_fkr“=true) should turn off adaptive serial direct path reads. It seems quite logical to me.

PS. Unfortunately I had a little time, so I didn’t investigate what process and why it was doing that, I just created profile with “index full scan” access, and it completely solved the problem.

Categories: Development

INDEX FULL SCAN (MIN/MAX) with two identical MIN()

Wed, 2015-02-04 09:39

I’ve just noticed an interesting thing:

Assume, that we have a simple query with “MIN(ID)” that works through “Index full scan(MIN/MAX)”:

SQL> explain plan for
  2  select
  3     min(ID)      as x
  4  from tab1
  5  where ID is not null;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 4170136576

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |     4 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |         |     1 |     4 |            |          |
|   2 |   FIRST ROW                 |         |     1 |     4 |     3   (0)| 00:00:01 |
|*  3 |    INDEX FULL SCAN (MIN/MAX)| IX_TAB1 |     1 |     4 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   3 - filter("ID" IS NOT NULL)

Spoiler:: Test tables SelectShow
create table tab1(id, x, padding)
as 
   with gen as (select level n from dual connect by level<=1000)
   select g1.n, g2.n, rpad(rownum,10,'x')
   from gen g1,gen g2;
create index ix_tab1 on tab1(id, x);
exec dbms_stats.gather_table_stats('','TAB1');


But look what will happen if we add one more “MIN(ID)”:

SQL> explain plan for
  2  select
  3     min(ID)      as x
  4   , min(ID)+1000 as x1000
  5  from tab1
  6  where ID is not null;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 3397888171

---------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |     4 |  3075  (17)| 00:00:02 |
|   1 |  SORT AGGREGATE       |         |     1 |     4 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IX_TAB1 |   999K|  3906K|  3075  (17)| 00:00:02 |
---------------------------------------------------------------------------------

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

   2 - filter("ID" IS NOT NULL)

At first I thought it was the old limitation from “MIN/MAX index access is not used if query has multiple MIN/MAX functions (Doc ID 316467.1)”, but we can get same plan by using hint:

SQL> explain plan for
  2  select/*+ index(tab1) */
  3     min(ID)      as x
  4   , min(ID)+1000 as x1000
  5  from tab1
  6  where ID is not null;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 4170136576

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |     4 |  3433  (22)| 00:00:02 |
|   1 |  SORT AGGREGATE             |         |     1 |     4 |            |          |
|   2 |   FIRST ROW                 |         |   999K|  3906K|  3433  (22)| 00:00:02 |
|*  3 |    INDEX FULL SCAN (MIN/MAX)| IX_TAB1 |   999K|  3906K|  3433  (22)| 00:00:02 |
---------------------------------------------------------------------------------------

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

   3 - filter("ID" IS NOT NULL)

So, we can see that the real problem lies in the wrong cardinality calculation.

Of course, we can avoid this problem using the old method – with simple inner view:

SQL> explain plan for
  2  select x, x+1000
  3  from (
  4        select
  5           min(ID)      as x
  6        from tab1
  7        where ID is not null
  8       );

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 2347179087

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    13 |     3   (0)| 00:00:01 |
|   1 |  VIEW                        |         |     1 |    13 |     3   (0)| 00:00:01 |
|   2 |   SORT AGGREGATE             |         |     1 |     4 |            |          |
|   3 |    FIRST ROW                 |         |     1 |     4 |     3   (0)| 00:00:01 |
|*  4 |     INDEX FULL SCAN (MIN/MAX)| IX_TAB1 |     1 |     4 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - filter("ID" IS NOT NULL)
Categories: Development

Simple Android Oracle client

Mon, 2014-12-29 18:54

I am happy to announce, that I’ve just published my first android app – Simple oracle client for android!
Since this is only the first version, I’m sure that it contains various UI bugs, so I’ll wait for reviews and bug reports!

Several screenshots:


Screenshot_qe_activity

Screenshot_fullscreen_results

Screenshot_db_list_phone

Screenshot_phone2


Get it on Google Play

Categories: Development