Oracle Tips and Tricks — David Fitzjarrell

April 1, 2014

Let’s All Join In

Filed under: General — dfitzjarrell @ 10:33

While investigating a question on returning unmatched rows between two tables I set up an example where both tables had indexes which could be used to speed up the query and hopefully return the result set in less time than required for a full table scan. To my surprise when the index was used on the table with the missing record the query returned no rows. Not understanding this behavior (as I KNEW there was at least one row in the data range that wasn’t in the smaller table) I checked the execution plans for queries that worked (meaning they returned the correct ‘missing’ row) and the ones that didn’t. We’ll go through my investigation and reveal the reason for this unexpected result.

First we’ll provide the problem we need to solve, namely how to return unmatched rows from two tables. Setting up the example is simple and straightforward — create two tables and load them with data, ensuring, in this example, that table B is missing rows that are present in table A:


SQL>
SQL> create table a (
  2  	     id number,
  3  	     dt      date
  4  );

Table created.

SQL>
SQL> create table b (
  2  	     id number,
  3  	     dt      date
  4  );

Table created.

SQL>
SQL> begin
  2  	     for i in 1..1000000 loop
  3  		     insert into a
  4  		     values(i, trunc(sysdate)+i);
  5  		     if mod(i,9317)  0 then
  6  			     insert into b
  7  			     values(i, trunc(sysdate)+i);
  8  		     end if;
  9  	     end loop;
 10
 11  	     commit;
 12
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL>

Compute statistics on the schema to prevent dynamic sampling:


SQL>
SQL> exec dbms_stats.gather_schema_stats('GRIBNAUT')

PL/SQL procedure successfully completed.

SQL>

Create the indexes we want to make the select statements more efficient:


SQL>
SQL> create index a_id_idx on a(dt);

Index created.

SQL> create index b_id_idx on b(dt);

Index created.

SQL>

Now the fun begins. Queries are executed that use both indexes to speed up processing and, as a result, return the ‘wrong’ result set:


SQL>
SQL> set autotrace on timing on
SQL>
SQL> select a.id
  2  from a left join b
  3  on b.id = a.id
  4  where a.dt between trunc(sysdate)+1000 and trunc(sysdate)+10000
  5  and b.dt between trunc(sysdate)+1000 and trunc(sysdate)+10000
  6  and b.id is null;

no rows selected

Elapsed: 00:00:02.22

Execution Plan
----------------------------------------------------------
Plan hash value: 991751610

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |     1 |    26 |    97   (0)| 00:00:01 |
|*  1 |  FILTER                        |          |       |       |            |          |
|   2 |   NESTED LOOPS                 |          |       |       |            |          |
|   3 |    NESTED LOOPS                |          |     1 |    26 |    97   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| B        |     1 |    13 |    49   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | B_ID_IDX |  9001 |       |    26   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN           | A_ID_IDX |  9002 |       |    25   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS BY INDEX ROWID | A        |     1 |    13 |    48   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   1 - filter(TRUNC(SYSDATE@!)+1000=TRUNC(SYSDATE@!)+1000 AND "B"."DT"=TRUNC(SYSDATE@!)+1000 AND "A"."DT"<=TRUNC(SYSDATE@!)+10000)
   7 - filter("B"."ID"="A"."ID")

Note
-----
   - SQL plan baseline "SQL_PLAN_827rna5a37fsc89d874e3" used for this statement


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         49  consistent gets
         28  physical reads
          0  redo size
        330  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>
SQL> select /*+ leading(b a) */ a.id
  2  from a left join b
  3  on b.id = a.id
  4  where a.dt between trunc(sysdate)+1000 and trunc(sysdate)+10000
  5  and b.dt between trunc(sysdate)+1000 and trunc(sysdate)+10000
  6  and b.id is null;

no rows selected

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 991751610

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |     1 |    26 |    97   (0)| 00:00:01 |
|*  1 |  FILTER                        |          |       |       |            |          |
|   2 |   NESTED LOOPS                 |          |       |       |            |          |
|   3 |    NESTED LOOPS                |          |     1 |    26 |    97   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| B        |     1 |    13 |    49   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | B_ID_IDX |  9001 |       |    26   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN           | A_ID_IDX |  9002 |       |    25   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS BY INDEX ROWID | A        |     1 |    13 |    48   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   1 - filter(TRUNC(SYSDATE@!)+1000=TRUNC(SYSDATE@!)+1000 AND "B"."DT"=TRUNC(SYSDATE@!)+1000 AND "A"."DT"<=TRUNC(SYSDATE@!)+10000)
   7 - filter("B"."ID"="A"."ID")

Note
-----
   - SQL plan baseline "SQL_PLAN_6p83dmsh1ur9589d874e3" used for this statement


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

SQL>
SQL> select /*+ leading(b a) use_nl(b a) */ a.id
  2  from a left join b
  3  on b.id = a.id
  4  where a.dt between trunc(sysdate)+1000 and trunc(sysdate)+10000
  5  and b.dt between trunc(sysdate)+1000 and trunc(sysdate)+10000
  6  and b.id is null;

no rows selected

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 991751610

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |     1 |    26 |    97   (0)| 00:00:01 |
|*  1 |  FILTER                        |          |       |       |            |          |
|   2 |   NESTED LOOPS                 |          |       |       |            |          |
|   3 |    NESTED LOOPS                |          |     1 |    26 |    97   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| B        |     1 |    13 |    49   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | B_ID_IDX |  9001 |       |    26   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN           | A_ID_IDX |  9002 |       |    25   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS BY INDEX ROWID | A        |     1 |    13 |    48   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   1 - filter(TRUNC(SYSDATE@!)+1000=TRUNC(SYSDATE@!)+1000 AND "B"."DT"=TRUNC(SYSDATE@!)+1000 AND "A"."DT"<=TRUNC(SYSDATE@!)+10000)
   7 - filter("B"."ID"="A"."ID")

Note
-----
   - SQL plan baseline "SQL_PLAN_7up4d2184h90h89d874e3" used for this statement


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

SQL>

Notice the order of the tables in the join; this is due to the Optimizer converting the outer join to an inner join:


  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$64C6BB79")
      OUTER_JOIN_TO_INNER(@"SEL$9E43CB6E" "B"@"SEL$1")
      OUTLINE(@"SEL$9E43CB6E")
      MERGE(@"SEL$58A6D7F6")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$58A6D7F6")
      MERGE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      INDEX_RS_ASC(@"SEL$64C6BB79" "B"@"SEL$1" ("B"."DT"))
      INDEX(@"SEL$64C6BB79" "A"@"SEL$2" ("A"."DT"))
      LEADING(@"SEL$64C6BB79" "B"@"SEL$1" "A"@"SEL$2")
      USE_NL(@"SEL$64C6BB79" "A"@"SEL$2")
      NLJ_BATCHING(@"SEL$64C6BB79" "A"@"SEL$2")
    END_OUTLINE_DATA
  */

These examples generated several comments which caused me to examine what I did and what I may have done incorrectly. Re-writing the first ‘non-working’ query by moving the index access for table B to the join condition caused Oracle to produce the correct result set:


SQL> select a.id
  2  from a left join b
  3  on b.id = a.id
  4  and b.dt between trunc(sysdate)+1000 and trunc(sysdate)+10000
  5  where a.dt between trunc(sysdate)+1000 and trunc(sysdate)+10000
  6  and b.id is null;

        ID
----------
      9317

Elapsed: 00:00:00.70

Execution Plan
----------------------------------------------------------
Plan hash value: 4150175149

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |    90 |  2340 |    99   (2)| 00:00:01 |
|*  1 |  FILTER                        |          |       |       |            |          |
|*  2 |   FILTER                       |          |       |       |            |          |
|*  3 |    HASH JOIN OUTER             |          |    90 |  2340 |    99   (2)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| A        |  9002 |   114K|    49   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | A_ID_IDX |  9002 |       |    26   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID| B        |  9001 |   114K|    49   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN          | B_ID_IDX |  9001 |       |    26   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   1 - filter(TRUNC(SYSDATE@!)+1000=TRUNC(SYSDATE@!)+1000 AND "A"."DT"=TRUNC(SYSDATE@!)+1000 AND
              "B"."DT"(+)

Obviously, after seeing this example, putting the date restriction for table B in the WHERE clause was incorrect thinking on my part.

Now look at a series of ‘working’ queries:


SQL>
SQL> select a.id
  2  from a, b
  3  where a.dt between trunc(sysdate)+1000 and trunc(sysdate)+10000
  4  and b.id (+) = a.id
  5  and b.id is null;

        ID
----------
      9317

Elapsed: 00:00:00.14

Execution Plan
----------------------------------------------------------
Plan hash value: 764351325

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |    90 |  1620 |   740   (2)| 00:00:01 |
|*  1 |  FILTER                       |          |       |       |            |          |
|*  2 |   HASH JOIN ANTI              |          |    90 |  1620 |   740   (2)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| A        |  9002 |   114K|    49   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | A_ID_IDX |  9002 |       |    26   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL          | B        |   999K|  4882K|   687   (1)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - filter(TRUNC(SYSDATE@!)+1000=TRUNC(SYSDATE@!)+1000 AND
              "A"."DT"<=TRUNC(SYSDATE@!)+10000)

Note
-----
   - SQL plan baseline "SQL_PLAN_a6prrdg4ukbttd488787e" used for this statement


Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
       2606  consistent gets
          0  physical reads
          0  redo size
        521  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>
SQL> select id
  2  from
  3  (select a.id, a.dt
  4   from a left join b
  5   on b.id = a.id
  6   where b.id is null)
  7  where dt between trunc(sysdate)+1000 and trunc(sysdate)+10000;

        ID
----------
      9317

Elapsed: 00:00:00.14

Execution Plan
----------------------------------------------------------
Plan hash value: 764351325

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |    90 |  1620 |   740   (2)| 00:00:01 |
|*  1 |  FILTER                       |          |       |       |            |          |
|*  2 |   HASH JOIN ANTI              |          |    90 |  1620 |   740   (2)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| A        |  9002 |   114K|    49   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | A_ID_IDX |  9002 |       |    26   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL          | B        |   999K|  4882K|   687   (1)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - filter(TRUNC(SYSDATE@!)+1000=TRUNC(SYSDATE@!)+1000 AND
              "A"."DT"<=TRUNC(SYSDATE@!)+10000)

Note
-----
   - SQL plan baseline "SQL_PLAN_42g6vdgv7hfm3d488787e" used for this statement


Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
       2606  consistent gets
          0  physical reads
          0  redo size
        521  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>
SQL> select id
  2  from a
  3  where id not in (select id from b where dt between trunc(sysdate)+1000 and trunc(sysdate)+10000)
  4  and a.dt between trunc(sysdate)+1000 and trunc(sysdate)+10000;

        ID
----------
      9317

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2827420310

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |    90 |  2340 |    99   (2)| 00:00:01 |
|*  1 |  FILTER                       |          |       |       |            |          |
|*  2 |   HASH JOIN ANTI NA           |          |    90 |  2340 |    99   (2)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| A        |  9002 |   114K|    49   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | A_ID_IDX |  9002 |       |    26   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| B        |  9001 |   114K|    49   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | B_ID_IDX |  9001 |       |    26   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - filter(TRUNC(SYSDATE@!)+1000=TRUNC(SYSDATE@!)+1000 AND
              "A"."DT"=TRUNC(SYSDATE@!)+1000 AND "DT"<=TRUNC(SYSDATE@!)+10000)

Note
-----
   - SQL plan baseline "SQL_PLAN_792mbp1nk9zf69c0ae570" used for this statement


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         98  consistent gets
          0  physical reads
          0  redo size
        521  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>

Another comment stated the first working query was not the same as the first ‘nonworking’ query; they are not supposed to be. My goal was to determine why one set of conditions using the index on table B produced no rows and another set of conditions, in general absent that same restriction, produced the correct results. As stated in earlier in this article my thought process was a bit flawed as my presumptions with respect to including a date restriction on table B in the WHERE clause may not have been correct.

In the above examples the outer join is preserved, resulting in the ‘correct’ answer. All of this because of an index. Notice, too, that the only table using an index is table A. To be fair and honest it is possible to use an index on table B and get the proper result, as the examples below illustrate:


SQL> select a_id
  2  from
  3  (select id a_id
  4  from a
  5  where dt between trunc(sysdate)+1000 and trunc(sysdate)+10000) a,
  6  (select id b_id
  7  from b
  8  where dt between trunc(sysdate)+1000 and trunc(sysdate)+10000) b
  9  where b_id (+) = a_id
 10  and b_id is null;

      A_ID
----------
      9317

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 4150175149

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |    90 |  2340 |    99   (2)| 00:00:01 |
|*  1 |  FILTER                        |          |       |       |            |          |
|*  2 |   FILTER                       |          |       |       |            |          |
|*  3 |    HASH JOIN OUTER             |          |    90 |  2340 |    99   (2)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| A        |  9002 |   114K|    49   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | A_ID_IDX |  9002 |       |    26   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID| B        |  9001 |   114K|    49   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN          | B_ID_IDX |  9001 |       |    26   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   1 - filter(TRUNC(SYSDATE@!)+1000=TRUNC(SYSDATE@!)+1000 AND "DT"=TRUNC(SYSDATE@!)+1000 AND "DT"(+)<=TRUNC(SYSDATE@!)+10000)

Note
-----
   - SQL plan baseline "SQL_PLAN_0awgfy98n1g1rdca7cba2" used for this statement


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         98  consistent gets
          0  physical reads
          0  redo size
        523  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>
SQL> select id
  2  from a
  3  where dt between trunc(sysdate)+1000 and trunc(sysdate)+10000
  4  minus
  5  select id
  6  from b
  7  where dt between trunc(sysdate)+1000 and trunc(sysdate)+10000;

        ID
----------
      9317

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3815546394

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |  9002 |   228K|   101  (52)| 00:00:01 |
|   1 |  MINUS                         |          |       |       |            |          |
|   2 |   SORT UNIQUE                  |          |  9002 |   114K|    50   (2)| 00:00:01 |
|*  3 |    FILTER                      |          |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| A        |  9002 |   114K|    49   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | A_ID_IDX |  9002 |       |    26   (0)| 00:00:01 |
|   6 |   SORT UNIQUE                  |          |  9001 |   114K|    50   (2)| 00:00:01 |
|*  7 |    FILTER                      |          |       |       |            |          |
|   8 |     TABLE ACCESS BY INDEX ROWID| B        |  9001 |   114K|    49   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN          | B_ID_IDX |  9001 |       |    26   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   3 - filter(TRUNC(SYSDATE@!)+1000=TRUNC(SYSDATE@!)+1000 AND "DT"<=TRUNC(SYSDATE@!)+10000)
   7 - filter(TRUNC(SYSDATE@!)+1000=TRUNC(SYSDATE@!)+1000 AND "DT"<=TRUNC(SYSDATE@!)+10000)

Note
-----
   - SQL plan baseline "SQL_PLAN_6h13amvqrnwdx3cdf639d" used for this statement


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

SQL>

In the first example both tables are used to create in-line views of the desired data range; those individual result sets are then joined to return the ‘correct’ result. In the second example the two select statements use the MINUS operator to again return the ‘correct’ answer.

Please refer back to the beginning of this article to see yet another query that uses the index on table B and produces the correct results

Is this a bug in Oracle 11.2? I suspected that it was; it certainly seemed strange that the outer join was converted to an inner join when an index on the ‘smaller’ table, the one with the missing data, was used. It turns out that a misstep in my logic caused at least one of the empty result sets. Correcting my logic and query produced the correct answer.

With a product as complex as Oracle some bugs are bound to be present; of course sometimes the ‘bug’ is in the logic behind an apparently misbehaving query which does teach you to pay careful attention to the results a query delivers. Even though the queries which produced the incorrect results are syntatically correct you can’t be certain that Oracle won’t do something ‘behind the scenes’ that can produce the wrong answer and reveal an error in your logic. My gratitude goes out to those who paid enough attention to make comments on this article and point out places where I went wrong so I could update it with corrected information.

Now, let’s join the party.

March 7, 2014

Hide And Seek (After A Fashion)

Filed under: General — dfitzjarrell @ 12:12

Indexes can be very useful tools to improve query performance, but it can be difficult to test indexes in an environment that exactly mirrors production. Although not the preferred method, Oracle offers in 11.2 and later releases the option of invisible indexes. What are invisible indexes? An invisible index can’t be ‘seen’ by any session by default, so it can be created and remain unavailable until testing is scheduled. This can be confusing since Oracle has never offered such a feature. Looking at an example might clear up any confusion.

Creating an invisible index is fairly straightforward:


SQL> create index emp_empno_idx
  2  on emp(empno)
  3  invisible;

Index created.

SQL>

We now have an invisible index on the EMPNO column of the ubiquitous EMP table. Will it be used? No, since the required parameter, optimizer_use_invisible_indexes, has not been altered from its default of FALSE. We prove this by executing the following query and examining the plan:


SQL>
SQL> select
  2  	     *
  3  from
  4  	     emp
  5  where
  6  	     empno < 7400;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    87 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("EMPNO"<7400)

Note
-----
   - dynamic sampling used for this statement (level=2)


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

SQL>

Notice the full table scan for a single row result set; even though we know the index exists the INVISIBLE keyword prevents Oracle from using it. Let’s fix that with the next attempt:


SQL> alter session set optimizer_use_invisible_indexes=true;

Session altered.

SQL>

SQL> select
  2             *
  3  from
  4             emp
  5  where
  6             empno < 7400;



     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20



Execution Plan
----------------------------------------------------------
Plan hash value: 576302360
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1|    87 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP           |     1|    87 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_EMPNO_IDX |     1|       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   2 - access("EMPNO"<7400)

Note
-----
   - dynamic sampling used for this statement (level=2)

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

SQL>

The current session can now see and use this invisible index even though no one else can. Since no DDL was issued the cursor is not invalidated. The parameter optimizer_use_invisible_indexes was set to TRUE at the session level, which now allows Oracle to know this index exists. If you’ve created more than one invisible index then all invisible indexes are visible from the session where this parameter is set to TRUE. This could make testing difficult if several invisible indexes are created using a common column especially if it’s the leading column. In the following example the intent was to test EMO_EMPNO_IDX but two similar invisible indexes were created:


SQL> create index emp_empno_idx
  2  on emp(empno,sal)
  3  invisible;

Index created.

SQL>
SQL> create index emp_empdept_idx
  2  on emp(empno,deptno)
  3  invisible;

Index created.

SQL>
SQL> set autotrace on
SQL>
SQL> select
  2             *
  3  from
  4             emp
  5  where
  6             empno < 7400;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost(%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    87 |     3  (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |     3  (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("EMPNO"<7400)

Note
-----
   - dynamic sampling used for this statement (level=2)

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

SQL>
SQL> alter session set optimizer_use_invisible_indexes=true;

Session altered.

SQL>
SQL> select
  2             *
  3  from
  4             emp
  5  where
  6             empno < 7400;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

Execution Plan
----------------------------------------------------------
Plan hash value: 1632405565
-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            |Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |    1 |    87 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP             |    1 |    87 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_EMPDEPT_IDX |    1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   2 - access("EMPNO"<7400)

Note
-----
   - dynamic sampling used for this statement (level=2)

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

SQL>

Notice that the intended index was not selected; the second of the two invisible indexes was favored for the query. It’s best if only one invisible index per table is created to avoid such occurrences. Of course it’s not hard to drop an invisible index so if the first attempt doesn’t function as intended the index can be dropped and a revised invisible index can replace it.

When should you use invisible indexes? Normally in a development or load test environment when performance issues need to be addressed. An invisible index can be created and then tested using the commands shown previously; the beauty of such a system is that only one session needs to see the invisible index so it can be tested. This prevents a new index from affecting existing processes so that other sessions on a heavily used test server won’t be disrupted by its presence. Test suite results can be invalidated by creating visible indexes without first verifying prior testing is complete (I’ve seen situations where indexes were created on load test systems because the person creating the indexes thought the system was ‘free’ and did not check that information first).

So you have an invisible index created and it provides the desired results; how does it become visible to all? The following command establishes its visibility:


SQL> alter index emp_empno_idx visible;

Index altered.

SQL>

A quick query against EMP from another session proves the index can be seen by all:


SQL> select
  2             *
  3  from
  4             emp
  5  where
  6             empno < 7400;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

Execution Plan
----------------------------------------------------------
Plan hash value: 576302360
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1|    87 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP           |     1|    87 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_EMPNO_IDX |     1|       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   2 - access("EMPNO"<7400)

Note
-----
   - dynamic sampling used for this statement (level=2)

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

SQL> 

Once you make an index visible the DDL invalidates the current cursor for queries against the table where the once-invisible index is built. Since the plan changes this should not be a surprising event. This does result in a hard parse, so you will likely see the hard to soft parse ratio increase after invisible indexes are made visible.

Invisible indexes are a very useful addition to an already world-class database, allowing performance tuning in a relatively isolated environment. Of course such tools must be used with discretion, forethought and care as the example with two similar invisible indexes illustrates. Keeping that in mind I find that invisible indexes can greatly improve the tuning process since the indexes can be tested and refined with a minimal impact on other sessions. Implementation then becomes less of a ‘hit-or-miss’ proposition in the finalized version of a product release or in a firefighting session geared toward immediate performance improvement since various forms of an index can be created and the most beneficial version can be selected. Remember, too, that there are no ‘silver bullets’ when it comes to Oracle performance.

January 30, 2014

You Take Too Long

Filed under: General — dfitzjarrell @ 12:09

Sometimes statements take longer than you’d like to complete. This can happen for a number of reasons including data changes, table growth, stale statistics and the occasional optimizer bug you haven’t encountered before. When it’s a SQL query that is misbehaving it’s pretty easy to find the cause and, in many cases, fix it. When it’s PL/SQL it isn’t as easy to do. There are views available from Oracle to assist in this endeavor and, provided you’re licensed, these views can make the troubleshooting process much easier.

Finding long-running statements used to be a task for the V$SESSION_LONGOPS view but it doesn’t record every long running operation so some problem statements could be missed because their actions aren’t recorded there. With Oracle 10.2 and later releases AWR provides an extended window into performance statistics, and in this particular case the views you want are DBA_HIST_SQLSTAT and DBA_HIST_SQLTEXT. The following statements can be used to generate listings of long-running statements, both historically and from current sessions; put them into a single script and you can generate troubleshooting data to assist you in your efforts:


set linesize 200 trimspool on pagesize 60 verify off
column begin_interval_time format a35
column end_interval_time format a35

break on sql_id skip 1 on instance_number

column sdate new_value sdt noprint

select to_char(sysdate, 'YYYYMMDDHHMI') sdate from dual;

spool &sdt._elapsed_time_report.log

prompt 
prompt  Historic
prompt 
prompt  Elapsed by exec
prompt 

select distinct x.instance_number, x.sql_id, x.time_per_exec, x.elapsed_time_total, s.begin_interval_time, s.end_interval_time
from
(select instance_number, sql_id, snap_id,
       round((elapsed_time_total/1000000)/(case when executions_total = 0 then 1 else executions_total end),4) time_per_exec,
       round(elapsed_time_total/1000000, 4) elapsed_time_total
from dba_hist_sqlstat) x, (select snap_id, 
                                  max(begin_interval_time) begin_interval_time, 
                                  max(end_interval_time) end_interval_time
                           from dba_hist_snapshot 
                           group by snap_id) s
where s.snap_id = x.snap_id 
and x.time_per_exec > &&1
and x.time_per_exec  x.elapsed_time_total
order by 2 asc, 3 desc, 6 desc
/

clear breaks

prompt 
prompt  Elapsed time total, current statements
prompt 

select inst_id,
       sql_id,
       executions,
       round(elapsed_time/1000000, 6) elapsed_sec,
       round((elapsed_time/1000000)/(case when executions = 0 then 1 else executions end), 6) elapsed_per_exec,
       last_active_time
from gv$sqlstats
where elapsed_time/1000000 > &&1
order by 4 desc
/

prompt 
prompt  Elapsed per exec, current statements
prompt 

select inst_id,
       sql_id,
       executions,
       round(elapsed_time/1000000, 6) elapsed_sec,
       round((elapsed_time/1000000)/(case when executions = 0 then 1 else executions end), 6) elapsed_per_exec,
       last_active_time
from gv$sqlstats
where elapsed_time/1000000 > &&1
order by 5 desc
/

prompt
prompt SQL text for current statements
prompt

set long 1000000 linesize 200 trimspool on
column sql_text format a132

select sql_id, sql_text
from dba_hist_sqltext
where sql_id in
(
select sql_id
from gv$sqlstats
where elapsed_time/1000000 > &&1
);

set linesize 80 trimspool off

spool off

Calling such a script is straightforward, simply provide the minimum length of time, in seconds, the script should consider as a starting point for the report; notice that there is a historic section and a current statement section so don’t expect the results from the first (historic) query to match those of the remaining queries. As an example let’s look at statements running for 15 seconds or longer:


SQL> @query_elapsed_per_exec 15





Historic

Elapsed by exec


INSTANCE_NUMBER SQL_ID        TIME_PER_EXEC ELAPSED_TIME_TOTAL BEGIN_INTERVAL_TIME                 END_INTERVAL_TIME
--------------- ------------- ------------- ------------------ ----------------------------------- -----------------------------------
              1 0nazp4jx2k26t       33.9633            67.9265 30-JAN-14 02.00.05.958 AM           30-JAN-14 03.00.43.716 AM

              1 6ajkhukk78nsr       20.0365            40.0729 28-JAN-14 12.00.01.801 PM           28-JAN-14 01.00.06.031 PM

              1 6kk1p64v5qhvx       43.0353           258.2121 22-JAN-14 11.00.07.315 AM           22-JAN-14 12.00.14.834 PM

              1 b6usrg82hwsa3       63.9262           127.8525 26-JAN-14 05.00.29.524 PM           26-JAN-14 06.00.29.893 PM
                                    44.3428           133.0283 26-JAN-14 09.00.42.077 PM           26-JAN-14 10.00.46.559 PM
                                    35.0675           140.2702 27-JAN-14 01.00.53.979 AM           27-JAN-14 02.01.00.542 AM


6 rows selected.


Elapsed time total, current statements


   INST_ID SQL_ID        EXECUTIONS ELAPSED_SEC ELAPSED_PER_EXEC LAST_ACTI
---------- ------------- ---------- ----------- ---------------- ---------
         1 f6cz4n8y72xdc          1   17.983987        17.983987 30-JAN-14
         1 02577v815yp77          1   17.408648        17.408648 30-JAN-14


Elapsed per exec, current statements


   INST_ID SQL_ID        EXECUTIONS ELAPSED_SEC ELAPSED_PER_EXEC LAST_ACTI
---------- ------------- ---------- ----------- ---------------- ---------
         1 f6cz4n8y72xdc          1   17.983987        17.983987 30-JAN-14
         1 02577v815yp77          1   17.408648        17.408648 30-JAN-14


SQL text for current statements


SQL_ID        SQL_TEXT
------------- ------------------------------------------------------------------------------------------------------------------------------------
f6cz4n8y72xdc SELECT space_usage_kbytes  FROM  v$sysaux_occupants  WHERE occupant_name = 'SQL_MANAGEMENT_BASE'
02577v815yp77 BEGIN :success := dbms_ha_alerts_prvt.post_instance_up; END;

SQL>

The ‘offending’ SQL text is now available so you can take that and, using UNIX/Linux utilities like grep, find source code that may need to be tuned. If you don’t have grep (you’re on Windows and haven’t installed any of the Windows-ported UNIX/Linux utilities) you can query either the DBA_SOURCE or USER_SOURCE view to find the package or procedure that contains the errant code. Presumably your enterprise is using some form of change control to manage code modifications which will make the source easy to modify. However, if for some strange reason code is simply written and deployed (a ghastly occurrence) using either DBA_SOURCE or USER_SOURCE should allow you to retrieve the problem code and get it corrected. As you can see the 15 second window doesn’t supply much data from my personal database so let’s use a shorter length of time and get more output:


SQL> query_elapsed_per_exec 4





Historic

Elapsed by exec


INSTANCE_NUMBER SQL_ID        TIME_PER_EXEC ELAPSED_TIME_TOTAL BEGIN_INTERVAL_TIME                 END_INTERVAL_TIME
--------------- ------------- ------------- ------------------ ----------------------------------- -----------------------------------
              1 0nazp4jx2k26t       33.9633            67.9265 30-JAN-14 02.00.05.958 AM           30-JAN-14 03.00.43.716 AM

              1 1cd2krbdzrhvq        7.9052             79.052 30-JAN-14 02.00.05.958 AM           30-JAN-14 03.00.43.716 AM

              1 1cq3qr774cu45        6.6394            66.3944 30-JAN-14 02.00.05.958 AM           30-JAN-14 03.00.43.716 AM

              1 2nszajb0qbyvp        4.2614             8.5229 29-JAN-14 08.20.57.000 AM           29-JAN-14 08.32.46.414 AM
                                     4.1071             8.2142 28-JAN-14 11.36.50.000 PM           28-JAN-14 11.48.05.036 PM

              1 350myuyx0t1d6        4.6085            46.0851 30-JAN-14 02.00.05.958 AM           30-JAN-14 03.00.43.716 AM

              1 35y2u24f4rxqm        6.9261            34.6305 29-JAN-14 12.00.00.587 PM           29-JAN-14 01.00.07.187 PM
                                     5.7718             34.631 29-JAN-14 01.00.07.187 PM           29-JAN-14 02.00.11.450 PM
                                     4.9473            34.6314 29-JAN-14 02.00.11.450 PM           29-JAN-14 03.00.16.164 PM

              1 63n9pwutt8yzw        8.2877            16.5754 26-JAN-14 05.00.29.524 PM           26-JAN-14 06.00.29.893 PM
                                     6.4604            19.3812 26-JAN-14 09.00.42.077 PM           26-JAN-14 10.00.46.559 PM
                                     5.0272            20.1089 27-JAN-14 01.00.53.979 AM           27-JAN-14 02.01.00.542 AM

              1 6ajkhukk78nsr       20.0365            40.0729 28-JAN-14 12.00.01.801 PM           28-JAN-14 01.00.06.031 PM
                                     13.464            40.3919 28-JAN-14 01.00.06.031 PM           28-JAN-14 02.00.09.913 PM
                                    10.1444            40.5776 28-JAN-14 02.00.09.913 PM           28-JAN-14 03.00.14.264 PM
                                     9.9596            29.8787 23-JAN-14 11.00.11.607 AM           23-JAN-14 12.00.17.236 PM
                                     9.1878            36.7513 23-JAN-14 12.00.17.236 PM           23-JAN-14 01.00.23.702 PM
                                     8.3653            41.8264 28-JAN-14 03.00.14.264 PM           28-JAN-14 04.00.18.883 PM
                                     8.2677            16.5355 23-JAN-14 10.00.06.894 AM           23-JAN-14 11.00.11.607 AM
                                     8.0056            16.0111 24-JAN-14 09.00.15.036 AM           24-JAN-14 10.00.21.294 AM
                                     7.4062            37.0311 23-JAN-14 01.00.23.702 PM           23-JAN-14 02.00.27.111 PM
                                     6.8399            13.6798 22-JAN-14 09.00.56.596 AM           22-JAN-14 10.00.02.354 AM
                                     6.3572             31.786 22-JAN-14 12.00.14.834 PM           22-JAN-14 01.00.20.445 PM
                                      6.198            37.1877 23-JAN-14 02.00.27.111 PM           23-JAN-14 03.00.31.177 PM
                                     5.9719            35.8313 22-JAN-14 01.00.20.445 PM           22-JAN-14 02.00.27.213 PM
                                     5.7639            17.2917 24-JAN-14 10.00.21.294 AM           24-JAN-14 11.00.26.060 AM
                                     5.5205            38.6435 22-JAN-14 02.00.27.213 PM           22-JAN-14 03.00.32.344 PM
                                     4.7033            14.1099 22-JAN-14 10.00.02.354 AM           22-JAN-14 11.00.07.315 AM
                                     4.5685            18.2742 24-JAN-14 11.00.26.060 AM           24-JAN-14 12.00.31.071 PM
                                     4.0313            20.1563 24-JAN-14 12.00.31.071 PM           24-JAN-14 01.00.36.467 PM

              1 6c9u8s2zwmthf        5.8052            17.4155 27-JAN-14 10.00.16.082 AM           27-JAN-14 11.00.23.152 AM

              1 6kk1p64v5qhvx       43.0353           258.2121 22-JAN-14 11.00.07.315 AM           22-JAN-14 12.00.14.834 PM

              1 6mcpb06rctk0x        7.2119            14.4237 26-JAN-14 05.00.29.524 PM           26-JAN-14 06.00.29.893 PM
                                     4.9693            14.9079 26-JAN-14 09.00.42.077 PM           26-JAN-14 10.00.46.559 PM

              1 7ydcybv1szhrj        5.1018            10.2037 29-JAN-14 08.32.46.414 AM           29-JAN-14 10.00.52.345 AM

              1 9ctt1scmwbmbg        6.2727            12.5454 29-JAN-14 08.32.46.414 AM           29-JAN-14 10.00.52.345 AM
                                     5.4683            10.9365 28-JAN-14 12.00.01.801 PM           28-JAN-14 01.00.06.031 PM
                                     4.4863             8.9726 30-JAN-14 08.02.30.500 AM           30-JAN-14 09.00.33.806 AM
                                     4.3223            12.9669 29-JAN-14 10.00.52.345 AM           29-JAN-14 11.00.56.416 AM

              1 b3abwkm67yg8r        4.0078             8.0156 29-JAN-14 06.08.45.886 PM           29-JAN-14 07.00.30.792 PM

              1 b6usrg82hwsa3       63.9262           127.8525 26-JAN-14 05.00.29.524 PM           26-JAN-14 06.00.29.893 PM
                                    44.3428           133.0283 26-JAN-14 09.00.42.077 PM           26-JAN-14 10.00.46.559 PM
                                    35.0675           140.2702 27-JAN-14 01.00.53.979 AM           27-JAN-14 02.01.00.542 AM

43 rows selected.


Elapsed time total, current statements


   INST_ID SQL_ID        EXECUTIONS ELAPSED_SEC ELAPSED_PER_EXEC LAST_ACTI
---------- ------------- ---------- ----------- ---------------- ---------
         1 f6cz4n8y72xdc          1   17.983987        17.983987 30-JAN-14
         1 02577v815yp77          1   17.408648        17.408648 30-JAN-14
         1 12a2xbmwn5v6z          1   13.154622        13.154622 30-JAN-14
         1 8mz0wa11tacp0          1   10.168939        10.168939 30-JAN-14
         1 3ktacv9r56b51        163    9.548086          .058577 30-JAN-14
         1 c2p32r5mzv8hb          1    9.054776         9.054776 30-JAN-14
         1 9ctt1scmwbmbg          3    9.015966         3.005322 30-JAN-14
         1 6ajkhukk78nsr          3    8.864179         2.954726 30-JAN-14
         1 db78fxqxwxt7r        807    8.675157           .01075 30-JAN-14
         1 01uy9sb7w8a9g          1    8.557564         8.557564 30-JAN-14
         1 c0agatqzq2jzr          3    7.937203         2.645734 30-JAN-14
         1 cvn54b7yz0s8u         99    7.722906          .078009 30-JAN-14
         1 g0jvz8csyrtcf          1    6.608542         6.608542 30-JAN-14
         1 ga9j9xk5cy9s0         99    5.745133          .058032 30-JAN-14
         1 b3abwkm67yg8r          3    5.286708         1.762236 30-JAN-14
         1 cnq31548hb8un          1    5.123489         5.123489 30-JAN-14
         1 96g93hntrzjtr       3002    4.909722          .001635 30-JAN-14
         1 3nkd3g3ju5ph1        910    4.892754          .005377 30-JAN-14
         1 2nszajb0qbyvp         14    4.570901          .326493 30-JAN-14
         1 832kkz790r75k          1     4.17935          4.17935 30-JAN-14

20 rows selected.


Elapsed per exec, current statements


   INST_ID SQL_ID        EXECUTIONS ELAPSED_SEC ELAPSED_PER_EXEC LAST_ACTI
---------- ------------- ---------- ----------- ---------------- ---------
         1 f6cz4n8y72xdc          1   17.983987        17.983987 30-JAN-14
         1 02577v815yp77          1   17.408648        17.408648 30-JAN-14
         1 12a2xbmwn5v6z          1   13.154622        13.154622 30-JAN-14
         1 8mz0wa11tacp0          1   10.168939        10.168939 30-JAN-14
         1 c2p32r5mzv8hb          1    9.054776         9.054776 30-JAN-14
         1 01uy9sb7w8a9g          1    8.557564         8.557564 30-JAN-14
         1 g0jvz8csyrtcf          1    6.608542         6.608542 30-JAN-14
         1 cnq31548hb8un          1    5.123489         5.123489 30-JAN-14
         1 832kkz790r75k          1     4.17935          4.17935 30-JAN-14
         1 9ctt1scmwbmbg          3    9.015966         3.005322 30-JAN-14
         1 6ajkhukk78nsr          3    8.864179         2.954726 30-JAN-14
         1 c0agatqzq2jzr          3    7.937203         2.645734 30-JAN-14
         1 b3abwkm67yg8r          3    5.286708         1.762236 30-JAN-14
         1 2nszajb0qbyvp         14    4.570901          .326493 30-JAN-14
         1 cvn54b7yz0s8u         99    7.722906          .078009 30-JAN-14
         1 3ktacv9r56b51        163    9.548086          .058577 30-JAN-14
         1 ga9j9xk5cy9s0         99    5.745133          .058032 30-JAN-14
         1 db78fxqxwxt7r        807    8.675157           .01075 30-JAN-14
         1 3nkd3g3ju5ph1        910    4.892754          .005377 30-JAN-14
         1 96g93hntrzjtr       3002    4.909722          .001635 30-JAN-14

20 rows selected.


SQL text for current statements


SQL_ID        SQL_TEXT
------------- ------------------------------------------------------------------------------------------------------------------------------------
12a2xbmwn5v6z select owner, segment_name, blocks from dba_segments where tablespace_name = :tsname
cnq31548hb8un BEGIN    :c := dbms_spm_internal.auto_purge_sql_plan_baseline;  END;
2nszajb0qbyvp DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN := FALSE; BEGIN wwv_flow_mail.push_queue(wwv_flow_pla
              tform.get_preference('SMTP_HOST_ADDRESS'),wwv_flow_platform.get_preference('SMTP_HOST_PORT')); :mydate := next_date; IF broken THEN
              :b := 1; ELSE :b := 0; END IF; END;

f6cz4n8y72xdc SELECT space_usage_kbytes  FROM  v$sysaux_occupants  WHERE occupant_name = 'SQL_MANAGEMENT_BASE'
db78fxqxwxt7r select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
9ctt1scmwbmbg begin dbsnmp.bsln_internal.maintain_thresholds; end;
3ktacv9r56b51 select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,type#,d_attrs from dependency$ d, obj$
               o where d_obj#=:1 and p_obj#=obj#(+) order by order#

6ajkhukk78nsr begin prvt_hdm.auto_execute( :dbid, :inst_num , :end_snap_id ); end;
96g93hntrzjtr select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, dens
              ity, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2

3nkd3g3ju5ph1 select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespa
              ce=:3 and remoteowner is null and linkname is null and subname is null

cvn54b7yz0s8u select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece#
02577v815yp77 BEGIN :success := dbms_ha_alerts_prvt.post_instance_up; END;
ga9j9xk5cy9s0 select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece from idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece#
832kkz790r75k SELECT c.capture#, c.capture_name, c.flags FROM sys.streams$_capture_process c, dba_queues q, dba_queue_tables qt  ,v$instance i WHE
              RE c.queue_owner  = q.owner   and c.queue_name   = q.name   and qt.queue_table = q.queue_table   and qt.owner       = q.owner   and
              ((qt.owner_instance = i.instance_number and  (bitand(c.flags, 1572864) = 0 or   bitand(c.flags, 8388608) > 0))  or (bitand(c.flags,
              1572864) > 0 and  bitand(c.flags, 8388608) = 0))   and bitand(c.flags, 512) != 512   and c.status       = :1 ORDER BY c.capture#

b3abwkm67yg8r DECLARE job BINARY_INTEGER := :job;  next_date TIMESTAMP WITH TIME ZONE := :mydate;  broken BOOLEAN := FALSE;  job_name VARCHAR2(30)
               := :job_name;  job_subname VARCHAR2(30) := :job_subname;  job_owner VARCHAR2(30) := :job_owner;  job_start TIMESTAMP WITH TIME ZONE
               := :job_start;  job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start;  window_start TIMESTAMP WITH TIME ZONE := :wi
              ndow_start;  window_end TIMESTAMP WITH TIME ZONE := :window_end;  chain_id VARCHAR2(14) :=  :chainid;  credential_owner varchar2(30)
               := :credown;  credential_name  varchar2(30) := :crednam;  destination_owner varchar2(30) := :destown;  destination_name varchar2(30
              ) := :destnam;  job_dest_id varchar2(14) := :jdestid;  log_id number := :log_id;  BEGIN  begin dbms_rlmgr_dr.execschdactions('RLM$SC
              HDNEGACTION'); end;  :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

8mz0wa11tacp0 select table_objno, primary_instance, secondary_instance, owner_instance from sys.aq$_queue_table_affinities a  where  a.owner_insta
              nce  :1 and dbms_aqadm_syscalls.get_owner_instance(a.primary_instance,       a.secondary_instance,       a.owner_instance) = :2  o
              rder by table_objno

c2p32r5mzv8hb BEGIN    prvt_advisor.delete_expired_tasks;  END;
01uy9sb7w8a9g  begin      dbms_aqadm_sys.remove_all_nondurablesub(:1, :2);   end;
c0agatqzq2jzr insert into "SYS"."ALERT_QT"  (q_name, msgid, corrid, priority, state, delay, expiration,   time_manager_info, local_order_no, chain
              _no, enq_time, step_no, enq_uid,   enq_tid, retry_count, exception_qschema, exception_queue, recipient_key,   dequeue_msgid, user_da
              ta, sender_name, sender_address, sender_protocol,   user_prop, cscn, dscn)   values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :
              12, :13, :14, 0, :15,         :16, :17, :18, :19, :20, :21, :22, :23, :24, :25)

g0jvz8csyrtcf SELECT T.TASK_ID FROM SYS.DBA_ADVISOR_TASKS T, WRI$_ADV_PARAMETERS P WHERE T.TASK_ID = P.TASK_ID AND T.STATUS#  :B2 AND T.READ_ONL
              Y = 'FALSE' AND P.NAME = 'DAYS_TO_EXPIRE' AND PRVT_ADVISOR.CHECK_EXPIRATION_COMP(:B1 , T.LAST_MODIFIED, P.VALUE) = 1


20 rows selected.

SQL>

It’s likely that longer minimum execution times will produce a much longer report in an active production database; it’s also likely that full blocks of PL/SQL will be reported to aid you in your search for the misbehaving code. Once you get the code and the sql_id values it’s a simple task to return plan data for the sql_ids of interest:


SQL> @plan_query_hist 12a2xbmwn5v6z

I   Operation                                Object Name                        # Rows      BYTES    COST PStart PStop
--- ---------------------------------------- ------------------------------ ---------- ---------- ---------- ------ ------
1   SELECT STATEMENT                                                                                 1117
2   VIEW                                     SYS_DBA_SEGS                          474      85320    1117
3     UNION-ALL
4       NESTED LOOPS (OUTER)                                                         1        121    1082
5         NESTED LOOPS                                                               1        103    1081
6           HASH JOIN                                                               33       2112    1015
7             NESTED LOO                                                          1736      72912      20
8               NESTED L                                                             6         96       2
9                 TABLE  (BY INDEX ROWID)    TS$                                     1         10       1
10                  INDE (UNIQUE SCAN)       I_TS1                                   1                  0
11                INDEX (FULL SCAN)          I_FILE2                                 6         36       1
12              TABLE AC (CLUSTER)           SEG$                                  289       7514       8
13                INDEX (RANGE SCAN)         I_FILE#_BLOCK#                          1                  2
14            VIEW                           SYS_OBJECTS                          5842     128524     995
15              UNION-AL
16                TABLE  (FULL)              TAB$                                 1826      40172     296
17                TABLE  (FULL)              TABPART$                              110       1760       2
18                TABLE  (FULL)              CLU$                                   10        140     296
19                TABLE  (FULL)              IND$                                 3713      66834     296
20                TABLE  (FULL)              INDPART$                              127       2032       3
21                TABLE  (FULL)              LOB$                                   22        440     296
22                TABLE  (FULL)              TABSUBPART$                            32        448       2
23                TABLE  (FULL)              INDSUBPART$                             1         52       2
24                TABLE  (FULL)              LOBFRAG$                                1         17       2
25          TABLE ACCESS (BY INDEX ROWID)    OBJ$                                    1         39       2
26            INDEX (RANGE SCAN)             I_OBJ1                                  1                  1
27        TABLE ACCESS (CLUSTER)             USER$                                   1         18       1
28          INDEX (UNIQUE SCAN)              I_USER#                                 1                  0
29      NESTED LOOPS (OUTER)                                                         2        198      10
30        NESTED LOOPS                                                               2        162       8
31          NESTED LOOPS                                                             2        150       8
32            NESTED LOO                                                            11        506       3
33              TABLE AC (BY INDEX ROWID)    TS$                                     1         10       1
34                INDEX (UNIQUE SCAN)        I_TS1                                   1                  0
35              TABLE AC (FULL)              UNDO$                                  11        396       2
36            TABLE ACCE (CLUSTER)           SEG$                                    1         29       1
37              INDEX (UNIQUE SCAN)          I_FILE#_BLOCK#                          1                  0
38          INDEX (UNIQUE SCAN)              I_FILE2                                 1          6       0
39        TABLE ACCESS (CLUSTER)             USER$                                   1         18       1
40          INDEX (UNIQUE SCAN)              I_USER#                                 1                  0
41      HASH JOIN (RIGHT OUTER)                                                    472      33512      25
42        TABLE ACCESS (FULL)                USER$                                  63       1134       3
43        NESTED LOOPS                                                             472      25016      21
44          NESTED LOOPS                                                             6        114       3
45            TABLE ACCE (BY INDEX ROWID)    TS$                                     1         10       1
46              INDEX (UNIQUE SCAN)          I_TS1                                   1                  0
47            TABLE ACCE (FULL)              FILE$                                   6         54       2
48          TABLE ACCESS (CLUSTER)           SEG$                                   79       2686       8
49            INDEX (RANGE SCAN)             I_FILE#_BLOCK#                          1                  2

49 rows selected.


Access Predicates .....


no rows selected


Filter predicates .....


no rows selected

SQL>

The statement for that plan runs for a little over 13 seconds per execution; it’s using data dictionary tables and views that can’t be modified or indexed any further so other means will need to be used to tune this statement. One option might be to gather data dictionary statistics to ensure they are current, however I have rarely found such a case to be true. Since these examples were run on a Windows system, in a fairly small database with a single disk the most likely culprit is the file configuration. This can also happen on enterprise systems, depending upon how LUNs are created and presented, so it would be a good idea to examine file placement to see if redo logs or control files are located on the same spindles as data and index files. Examining a current AWR report should shed some light on how I/O is behaving and which wait events are consuming the most resources.

Although not quite as straightforward as SQL statement tuning it is possible to find ‘offending’ PL/SQL code and work to improve its performance. The DBMS_PROFILER package, available since Oracle 10.2 was released, can be used to provide performance information for PL/SQL packages and procedures. To install the dbms_profiler package and dependent objects the ?/rdbms/admin/proftab.sql script needs to be run. Once these objects are created it’s simply a matter of starting the profiler, running the problem code and then stopping the profiler run. Using the profdemo.sql script from Oracle you can see the following calls before and after the PL/SQL is executed:


SQL> Rem  Start profiling.
SQL>
SQL> declare
  2    status number;
  3  begin
  4    status := dbms_profiler.start_profiler('PROFILER DEMO');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> execute profdemo_pack.profdemo_p1;

PL/SQL procedure successfully completed.

SQL>
SQL> Rem  Stop profiling data.
SQL>
SQL> declare
  2    status number;
  3  begin
  4    status := dbms_profiler.stop_profiler;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>

Reports were generated using the profrep.sql script, again from Oracle:


GRAND_TOTA
----------
       .00


     RUNID RUN_COMMENT                       SECONDS
---------- ------------------------------ ----------
         1 PROFILER DEMO                        .037
         2 PROFILER DEMO                        .003
         3 PROFILER DEMO                        .003
         4 PROFILER DEMO                        .051


     RUNID RUN_COMMENT          UNIT_OWNER                       UNIT_NAME            SECONDS   PERCEN
---------- -------------------- -------------------------------- -------------------- --------- ------
         1 PROFILER DEMO        SYS                              PROFDEMO_PACK              .00    1.4
         2 PROFILER DEMO        SYS                              PROFDEMO_PACK              .00    9.6
         2 PROFILER DEMO                                              .00    1.5
         2 PROFILER DEMO                                              .00    1.2
         3 PROFILER DEMO        SYS                              PROFDEMO_PACK              .00   10.7
         3 PROFILER DEMO                                              .00    2.1
         3 PROFILER DEMO                                              .00    1.4
         3 PROFILER DEMO        SYS                              DBMS_OUTPUT                .00    1.1

8 rows selected.


UNIT_OWNER                       UNIT_NAME                 SECONDS   PERCENTAG
-------------------------------- ------------------------- --------- ---------
SYS                              PROFDEMO_PACK                   .00     66.44
                                           .00     23.00
SYS                              DBMS_OUTPUT                     .00      5.35
SYS                              DBMS_PROFILER                   .00      4.38
SYS                              PROFDEMO_TYPE                   .00       .83

    to_char(p1.total_time/(p1.total_occur*p1.min_time),'99999.99')as "Ave/min",
                         *
ERROR at line 10:
ORA-01476: divisor is equal to zero



     RUNID SECONDS  OWNER                UNIT_NAME                 LINE# TEXT
---------- -------- -------------------- -------------------- ---------- -------------------------------------------------------------
         1       .0 SYS                  PROFDEMO_PACK                 9        insert into profdemo_tab_1 values (d3);
         4       .0 SYS                  PROFDEMO_PACK                 9        insert into profdemo_tab_1 values (d3);
         3       .0 SYS                  PROFDEMO_PACK                 9        insert into profdemo_tab_1 values (d3);
         2       .0 SYS                  PROFDEMO_PACK                 9        insert into profdemo_tab_1 values (d3);
         1       .0 SYS                  PROFDEMO_PACK                15        insert into profdemo_tab_1 values (d2.atr);
         4       .0 SYS                  PROFDEMO_PACK                15        insert into profdemo_tab_1 values (d2.atr);
         3       .0 SYS                  PROFDEMO_PACK                15        insert into profdemo_tab_1 values (d2.atr);
         2       .0 SYS                  PROFDEMO_PACK                15        insert into profdemo_tab_1 values (d2.atr);

8 rows selected.


SECONDS  UNIT_OWNER           UNIT_NAME                 LINE# TEXT
-------- -------------------- -------------------- ---------- -------------------------------------------------------------------------
      .0 SYS                  PROFDEMO_PACK                 9        insert into profdemo_tab_1 values (d3);
      .0 SYS                  PROFDEMO_PACK                15        insert into profdemo_tab_1 values (d2.atr);
      .0 SYS                  PROFDEMO_PACK                 8        d3 := d1.profdemo_type_regular_method()+i;
      .0 SYS                  PROFDEMO_PACK                18      raise value_error;
      .0 SYS                  PROFDEMO_PACK                 2    procedure profdemo_p1 is


PL/SQL procedure successfully completed.


UNIT_OWNER                       UNIT_NAME                        LINES_EXECUTED
-------------------------------- -------------------------------- --------------
SYS                              PROFDEMO_PACK                                11
SYS                              PROFDEMO_TYPE                                 4
SYS                              DBMS_OUTPUT                                  17
SYS                              DBMS_PROFILER                                 7


UNIT_OWNER                       UNIT_NAME                        LINES_PRESENT
-------------------------------- -------------------------------- -------------
SYS                              PROFDEMO_PACK                               13
SYS                              PROFDEMO_TYPE                                9
SYS                              DBMS_OUTPUT                                106
SYS                              DBMS_PROFILER                              112


LINES_EXECUTED
--------------
            39


LINES_PRESENT
-------------
          240

=================================trace info=================================
===========================Results for run #1 made on 30-JAN-14 11:25:14 =========================
(PROFILER DEMO) Run total time:       .04 seconds
Unit #1: SYS.DBMS_PROFILER - Total time:       .00 seconds
Unit #2: . - Total time:       .00 seconds
Unit #3: . - Total time:       .00 seconds
Unit #4: SYS.DBMS_OUTPUT - Total time:       .00 seconds
Unit #5: . - Total time:       .00 seconds
Unit #6: SYS.PROFDEMO_PACK - Total time:       .00 seconds
1                                                package body profdemo_pack is
2           0   .00000795                           procedure profdemo_p1 is
3           1   .00000198  .00000198                  d1 profdemo_type := profdemo_type(earliest_date);
4           1   .00000497  .00000497                  d2 profdemo_type := profdemo_type(earliest_date+1);
5                                                     d3 date;
6                                                   begin
7           6   .00000099  .00000016                  for i in 1..5 loop
8           5   .00001789  .00000357                    d3 := d1.profdemo_type_regular_method()+i;
9           5   .00041947  .00008389                    insert into profdemo_tab_1 values (d3);
10                                                     end loop;
11
12           1   .00000099  .00000099                  if d1 > d2 then
13           0   0                                       insert into profdemo_tab_1 values (d1.atr);
14                                                     else
15           1   .00004274  .00004274                    insert into profdemo_tab_1 values (d2.atr);
16                                                     end if;
17
18           1   .00001093  .00001093                  raise value_error;
19
20           1   0  0                                  exception when value_error then
21           1   .00000099  .00000099                          NULL;
22           1   .00000099  .00000099                end;
23                                                end profdemo_pack
Unit #7: SYS.PROFDEMO_TYPE - Total time:       .00 seconds
1                                                type body profdemo_type is
2           0   0                                    static function profdemo_type_static_method  return date is
3                                                    begin
4           0   0                                         return (sysdate);
5           0   0                                    end;
6
7           0   .00000198                            member function profdemo_type_regular_method return date is
8                                                    begin
9           5   .00000099  .00000019                      return (atr);
10           5   .00000099  .00000019                 end;
11
12           0   0                                    map member function profdemo_type_map_method return date is
13                                                    begin
14           2   .00000099  .00000049                      return (atr);
15           2   0  0                                 end;
16                                                end
Unit #8: . - Total time:       .00 seconds
Unit #9: . - Total time:       .00 seconds
===========================Results for run #2 made on 30-JAN-14 11:27:07 =========================
(PROFILER DEMO) Run total time:       .00 seconds
Unit #1: SYS.DBMS_PROFILER - Total time:       .00 seconds
Unit #2: . - Total time:       .00 seconds
Unit #3: . - Total time:       .00 seconds
Unit #4: SYS.DBMS_OUTPUT - Total time:       .00 seconds
Unit #5: . - Total time:       .00 seconds
Unit #6: SYS.PROFDEMO_PACK - Total time:       .00 seconds
1                                                package body profdemo_pack is
2           0   .00000537                           procedure profdemo_p1 is
3           1   .00000179  .00000179                  d1 profdemo_type := profdemo_type(earliest_date);
4           1   .00000447  .00000447                  d2 profdemo_type := profdemo_type(earliest_date+1);
5                                                     d3 date;
6                                                   begin
7           6   0  0                                  for i in 1..5 loop
8           5   .00001432  .00000286                    d3 := d1.profdemo_type_regular_method()+i;
9           5   .00021223  .00004244                    insert into profdemo_tab_1 values (d3);
10                                                     end loop;
11
12           1   .00000268  .00000268                  if d1 > d2 then
13           0   0                                       insert into profdemo_tab_1 values (d1.atr);
14                                                     else
15           1   .00003671  .00003671                    insert into profdemo_tab_1 values (d2.atr);
16                                                     end if;
17
18           1   .00000985  .00000985                  raise value_error;
19
20           1   0  0                                  exception when value_error then
21           1   .00000089  .00000089                          NULL;
22           1   .00000089  .00000089                end;
23                                                end profdemo_pack
Unit #7: SYS.PROFDEMO_TYPE - Total time:       .00 seconds
1                                                type body profdemo_type is
2           0   0                                    static function profdemo_type_static_method  return date is
3                                                    begin
4           0   0                                         return (sysdate);
5           0   0                                    end;
6
7           0   .00000089                            member function profdemo_type_regular_method return date is
8                                                    begin
9           5   .00000179  .00000035                      return (atr);
10           5   .00000179  .00000035                 end;
11
12           0   0                                    map member function profdemo_type_map_method return date is
13                                                    begin
14           2   .00000179  .00000089                      return (atr);
15           2   0  0                                 end;
16                                                end
Unit #8: . - Total time:       .00 seconds
Unit #9: . - Total time:       .00 seconds
===========================Results for run #3 made on 30-JAN-14 11:29:21 =========================
(PROFILER DEMO) Run total time:       .00 seconds
Unit #1: SYS.DBMS_PROFILER - Total time:       .00 seconds
Unit #2: . - Total time:       .00 seconds
Unit #3: . - Total time:       .00 seconds
Unit #4: SYS.DBMS_OUTPUT - Total time:       .00 seconds
Unit #5: . - Total time:       .00 seconds
Unit #6: SYS.PROFDEMO_PACK - Total time:       .00 seconds
1                                                package body profdemo_pack is
2           0   .00000625                           procedure profdemo_p1 is
3           1   .00000267  .00000267                  d1 profdemo_type := profdemo_type(earliest_date);
4           1   .00000535  .00000535                  d2 profdemo_type := profdemo_type(earliest_date+1);
5                                                     d3 date;
6                                                   begin
7           6   .00000267  .00000044                  for i in 1..5 loop
8           5   .00001339  .00000267                    d3 := d1.profdemo_type_regular_method()+i;
9           5   .00023757  .00004751                    insert into profdemo_tab_1 values (d3);
10                                                     end loop;
11
12           1   .00000357  .00000357                  if d1 > d2 then
13           0   0                                       insert into profdemo_tab_1 values (d1.atr);
14                                                     else
15           1   .00003840  .00003840                    insert into profdemo_tab_1 values (d2.atr);
16                                                     end if;
17
18           1   .00000982  .00000982                  raise value_error;
19
20           1   0  0                                  exception when value_error then
21           1   0  0                                          NULL;
22           1   .00000089  .00000089                end;
23                                                end profdemo_pack
Unit #7: SYS.PROFDEMO_TYPE - Total time:       .00 seconds
1                                                type body profdemo_type is
2           0   0                                    static function profdemo_type_static_method  return date is
3                                                    begin
4           0   0                                         return (sysdate);
5           0   0                                    end;
6
7           0   .00000178                            member function profdemo_type_regular_method return date is
8                                                    begin
9           5   .00000089  .00000017                      return (atr);
10           5   .00000178  .00000035                 end;
11
12           0   0                                    map member function profdemo_type_map_method return date is
13                                                    begin
14           2   .00000089  .00000044                      return (atr);
15           2   0  0                                 end;
16                                                end
Unit #8: . - Total time:       .00 seconds
Unit #9: . - Total time:       .00 seconds
===========================Results for run #4 made on 30-JAN-14 11:30:41 =========================
(PROFILER DEMO) Run total time:       .05 seconds
Unit #1: SYS.DBMS_PROFILER - Total time:       .00 seconds
Unit #2: . - Total time:       .00 seconds
Unit #3: . - Total time:       .00 seconds
Unit #4: SYS.DBMS_OUTPUT - Total time:       .00 seconds
Unit #5: . - Total time:       .00 seconds
Unit #6: SYS.PROFDEMO_PACK - Total time:       .00 seconds
1                                                package body profdemo_pack is
2           0   .00000393                           procedure profdemo_p1 is
3           1   .00000295  .00000295                  d1 profdemo_type := profdemo_type(earliest_date);
4           1   .00000295  .00000295                  d2 profdemo_type := profdemo_type(earliest_date+1);
5                                                     d3 date;
6                                                   begin
7           6   .00000098  .00000016                  for i in 1..5 loop
8           5   .00001968  .00000393                    d3 := d1.profdemo_type_regular_method()+i;
9           5   .00027756  .00005551                    insert into profdemo_tab_1 values (d3);
10                                                     end loop;
11
12           1   .00000098  .00000098                  if d1 > d2 then
13           0   0                                       insert into profdemo_tab_1 values (d1.atr);
14                                                     else
15           1   .00004232  .00004232                    insert into profdemo_tab_1 values (d2.atr);
16                                                     end if;
17
18           1   .00001082  .00001082                  raise value_error;
19
20           1   0  0                                  exception when value_error then
21           1   .00000098  .00000098                          NULL;
22           1   .00000098  .00000098                end;
23                                                end profdemo_pack
Unit #7: SYS.PROFDEMO_TYPE - Total time:       .00 seconds
1                                                type body profdemo_type is
2           0   0                                    static function profdemo_type_static_method  return date is
3                                                    begin
4           0   0                                         return (sysdate);
5           0   0                                    end;
6
7           0   0                                    member function profdemo_type_regular_method return date is
8                                                    begin
9           5   .00000098  .00000019                      return (atr);
10           5   0  0                                 end;
11
12           0   0                                    map member function profdemo_type_map_method return date is
13                                                    begin
14           2   .00000098  .00000049                      return (atr);
15           2   0  0                                 end;
16                                                end
Unit #8: . - Total time:       .00 seconds
Unit #9: . - Total time:       .00 seconds
============================================================================

PL/SQL procedure successfully completed.

================== Profiler report - all runs rolled up ===================
Unit .:
Unit SYS.DBMS_OUTPUT:
Unit SYS.DBMS_PROFILER:
Unit SYS.PROFDEMO_PACK:
1                                                package body profdemo_pack is
2           0   .00002351                           procedure profdemo_p1 is
3           4   .00000941  .00000235                  d1 profdemo_type := profdemo_type(earliest_date);
4           4   .00001775  .00000443                  d2 profdemo_type := profdemo_type(earliest_date+1);
5                                                     d3 date;
6                                                   begin
7          24   .00000465  .00000019                  for i in 1..5 loop
8          20   .00006530  .00000326                    d3 := d1.profdemo_type_regular_method()+i;
9          20   .00114684  .00005734                    insert into profdemo_tab_1 values (d3);
10                                                     end loop;
11
12           4   .00000823  .00000205                  if d1 > d2 then
13           0   0                                       insert into profdemo_tab_1 values (d1.atr);
14                                                     else
15           4   .00016018  .00004004                    insert into profdemo_tab_1 values (d2.atr);
16                                                     end if;
17
18           4   .00004143  .00001035                  raise value_error;
19
20           4   0  0                                  exception when value_error then
21           4   .00000287  .00000071                          NULL;
22           4   .00000376  .00000094                end;
23                                                end profdemo_pack
Unit SYS.PROFDEMO_TYPE:
1                                                type body profdemo_type is
2           0   0                                    static function profdemo_type_static_method  return date is
3                                                    begin
4           0   0                                         return (sysdate);
5           0   0                                    end;
6
7           0   .00000466                            member function profdemo_type_regular_method return date is
8                                                    begin
9          20   .00000466  .00000023                      return (atr);
10          20   .00000457  .00000022                 end;
11
12           0   0                                    map member function profdemo_type_map_method return date is
13                                                    begin
14           8   .00000466  .00000058                      return (atr);
15           8   0  0                                 end;
16                                                end
============================================================================

PL/SQL procedure successfully completed.

A wealth of performance data is provided from the profiler; tuning the PL/SQL should then be a simple matter of working through the long-running sections and addressing any issues causing the slow performance. And all this was made available by running the set of queries provided at the start of this post so problem statements could be found.

When things start running slower, and it’s PL/SQL performance that’s impeding progress, it’s good to know how to find the problem statements so you can run the profiler (on 10.2 and later releases) to find the root causes and address them. It may take some time to correct the issues you find but it’s definitely worth the effort.

Hurry up, I haven’t got all day.

January 22, 2014

Space Exploration

Filed under: General — dfitzjarrell @ 12:58

Managing free space in a tablespace seems to be an easy task, what with the views DBA_DATA_FILES, DBA_TEMP_FILES and DBA_FREE_SPACE avaliable. Those views can provide accurate information for datafiles not set to autoextend, however for autoextensible datafiles they can paint a bleaker picture because they’re based on the current size of the file, not the autoextend limit that file could reach. Let’s look at how those views, without taking autoextend into consideration, can report a ‘problem’ that doesn’t actually exist.

For datafiles not set to autoextend the following queries return basically the same information:


SQL>
SQL> select tablespace_name, ttl_alloc, ttl_free, round((ttl_free/ttl_alloc)*100,2) pct_free
  2  from
  3  (select fs.tablespace_name, sum(df.bytes) ttl_alloc, sum(fs.bytes) ttl_free
  4  from dba_free_space fs join dba_data_files df on (df.tablespace_name = fs.tablespace_name)
  5  group by fs.tablespace_name);

TABLESPACE_NAME                 TTL_ALLOC   TTL_FREE   PCT_FREE
------------------------------ ---------- ---------- ----------
SYSAUX                         2.5166E+10  136904704        .54
UNDOTBS1                       4383047680   38797312        .89
USERS                           104857600  103809024         99
USERS2                          209715200  208666624       99.5
SYSTEM                         1468006400   93388800       6.36
INDX                             52428800   51380224         98

6 rows selected.

SQL>
SQL> @tsalloc
SQL> set linesize 132 tab off trimspool on
SQL> set pagesize 105
SQL> set pause off
SQL> -- set echo off
SQL> set feedb on
SQL>
SQL> column "TOTAL ALLOC (MB)" format 9,999,990.00
SQL> column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
SQL> column "USED (MB)" format  9,999,990.00
SQL> column "FREE (MB)" format 9,999,990.00
SQL> column "% USED" format 990.00
SQL>
SQL> select a.tablespace_name,
  2         a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
  3         a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
  4         nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
  5         (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
  6  from ( select tablespace_name,
  7         sum(bytes) physical_bytes,
  8         sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
  9         from dba_data_files
 10         group by tablespace_name ) a,
 11       ( select tablespace_name, sum(bytes) tot_used
 12         from dba_segments
 13         group by tablespace_name ) b
 14  where a.tablespace_name = b.tablespace_name (+)
 15  --and   (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
 16  and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
 17  and   a.tablespace_name not like 'UNDO%'
 18  --order by 1
 19  order by 5
 20  /

TABLESPACE_NAME                TOTAL ALLOC (MB) TOTAL PHYS ALLOC (MB)     USED (MB)  % USED
------------------------------ ---------------- --------------------- ------------- -------
USERS                                 32,767.98                100.00          0.00    0.00
USERS2                                32,767.98                200.00          0.00    0.00
INDX                                  32,767.98                 50.00          0.00    0.00
SYSAUX                                32,767.98                600.00        468.44    1.43
SYSTEM                                32,767.98                700.00        609.94    1.86

5 rows selected.

It’s when autoextend is enabled that the first query can report a ‘problem’ that doesn’t really exist. Let’s create a table and populate it with data to see how the USERS tablespace behaves:


SQL>
SQL>
SQL> create table spacetst(
  2          spaceid number,
  3          spacenm varchar2(20),
  4          spacedt date);

Table created.

SQL>
SQL> begin
  2          for i in 0..1000000 loop
  3                  insert into spacetst
  4                  values(i, 'Space '||i, sysdate+i);
  5          end loop;
  6
  7          commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> select tablespace_name, ttl_alloc, ttl_free, round((ttl_free/ttl_alloc)*100,2) pct_free
  2  from
  3  (select fs.tablespace_name, sum(df.bytes) ttl_alloc, sum(fs.bytes) ttl_free
  4  from dba_free_space fs join dba_data_files df on (df.tablespace_name = fs.tablespace_name)
  5  group by fs.tablespace_name);

TABLESPACE_NAME                 TTL_ALLOC   TTL_FREE   PCT_FREE
------------------------------ ---------- ---------- ----------
SYSAUX                         2.5166E+10  136904704        .54
UNDOTBS1                       3287285760   30408704        .93
USERS                           104857600   68157440         65
USERS2                          209715200  208666624       99.5
SYSTEM                         1468006400   93388800       6.36
INDX                             52428800   51380224         98

6 rows selected.

SQL>
SQL> @tsalloc
SQL> set linesize 132 tab off trimspool on
SQL> set pagesize 105
SQL> set pause off
SQL> -- set echo off
SQL> set feedb on
SQL>
SQL> column "TOTAL ALLOC (MB)" format 9,999,990.00
SQL> column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
SQL> column "USED (MB)" format  9,999,990.00
SQL> column "FREE (MB)" format 9,999,990.00
SQL> column "% USED" format 990.00
SQL>
SQL> select a.tablespace_name,
  2         a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
  3         a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
  4         nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
  5         (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
  6  from ( select tablespace_name,
  7         sum(bytes) physical_bytes,
  8         sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
  9         from dba_data_files
 10         group by tablespace_name ) a,
 11       ( select tablespace_name, sum(bytes) tot_used
 12         from dba_segments
 13         group by tablespace_name ) b
 14  where a.tablespace_name = b.tablespace_name (+)
 15  --and   (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
 16  and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
 17  and   a.tablespace_name not like 'UNDO%'
 18  --order by 1
 19  order by 5
 20  /

TABLESPACE_NAME                TOTAL ALLOC (MB) TOTAL PHYS ALLOC (MB)     USED (MB)  % USED
------------------------------ ---------------- --------------------- ------------- -------
INDX                                  32,767.98                 50.00          0.00    0.00
USERS2                                32,767.98                200.00          0.00    0.00
USERS                                 32,767.98                100.00         34.00    0.10
SYSAUX                                32,767.98                600.00        468.44    1.43
SYSTEM                                32,767.98                700.00        609.94    1.86

5 rows selected.

So far, so good as both queries indicate we have plenty of available space. Let’s add more data and see where that leads:


SQL>
SQL>
SQL> begin
  2          for i in 0..1000000 loop
  3                  insert into spacetst
  4                  values(i, 'Space '||i, sysdate+i);
  5          end loop;
  6
  7          commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> select tablespace_name, ttl_alloc, ttl_free, round((ttl_free/ttl_alloc)*100,2) pct_free
  2  from
  3  (select fs.tablespace_name, sum(df.bytes) ttl_alloc, sum(fs.bytes) ttl_free
  4  from dba_free_space fs join dba_data_files df on (df.tablespace_name = fs.tablespace_name)
  5  group by fs.tablespace_name);

TABLESPACE_NAME                 TTL_ALLOC   TTL_FREE   PCT_FREE
------------------------------ ---------- ---------- ----------
SYSAUX                         2.5166E+10  136904704        .54
UNDOTBS1                       3287285760   13697024        .42
USERS                           104857600   28311552         27
USERS2                          209715200  208666624       99.5
SYSTEM                         1468006400   93388800       6.36
INDX                             52428800   51380224         98

6 rows selected.

SQL>
SQL> @tsalloc
SQL> set linesize 132 tab off trimspool on
SQL> set pagesize 105
SQL> set pause off
SQL> -- set echo off
SQL> set feedb on
SQL>
SQL> column "TOTAL ALLOC (MB)" format 9,999,990.00
SQL> column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
SQL> column "USED (MB)" format  9,999,990.00
SQL> column "FREE (MB)" format 9,999,990.00
SQL> column "% USED" format 990.00
SQL>
SQL> select a.tablespace_name,
  2         a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
  3         a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
  4         nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
  5         (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
  6  from ( select tablespace_name,
  7         sum(bytes) physical_bytes,
  8         sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
  9         from dba_data_files
 10         group by tablespace_name ) a,
 11       ( select tablespace_name, sum(bytes) tot_used
 12         from dba_segments
 13         group by tablespace_name ) b
 14  where a.tablespace_name = b.tablespace_name (+)
 15  --and   (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
 16  and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
 17  and   a.tablespace_name not like 'UNDO%'
 18  --order by 1
 19  order by 5
 20  /

TABLESPACE_NAME                TOTAL ALLOC (MB) TOTAL PHYS ALLOC (MB)     USED (MB)  % USED
------------------------------ ---------------- --------------------- ------------- -------
INDX                                  32,767.98                 50.00          0.00    0.00
USERS2                                32,767.98                200.00          0.00    0.00
USERS                                 32,767.98                100.00         72.00    0.22
SYSAUX                                32,767.98                600.00        468.44    1.43
SYSTEM                                32,767.98                700.00        609.94    1.86

5 rows selected.

SQL>
SQL>
SQL> begin
  2          for i in 0..1000000 loop
  3                  insert into spacetst
  4                  values(i, 'Space '||i, sysdate+i);
  5          end loop;
  6
  7          commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>

Again the two queries don’t show any issues with the USERS tablespace. Let’s keep adding rows to the table to attempt to fill up the USERS tablespace and see what happens:


SQL> select tablespace_name, ttl_alloc, ttl_free, round((ttl_free/ttl_alloc)*100,2) pct_free
  2  from
  3  (select fs.tablespace_name, sum(df.bytes) ttl_alloc, sum(fs.bytes) ttl_free
  4  from dba_free_space fs join dba_data_files df on (df.tablespace_name = fs.tablespace_name)
  5  group by fs.tablespace_name);

TABLESPACE_NAME                 TTL_ALLOC   TTL_FREE   PCT_FREE
------------------------------ ---------- ---------- ----------
SYSAUX                         2.5166E+10  136904704        .54
UNDOTBS1                       1095761920    1048576         .1
USERS                           116654080    6553600       5.62
USERS2                          209715200  208666624       99.5
SYSTEM                         1468006400   93388800       6.36
INDX                             52428800   51380224         98

6 rows selected.

SQL>
SQL> @tsalloc
SQL> set linesize 132 tab off trimspool on
SQL> set pagesize 105
SQL> set pause off
SQL> -- set echo off
SQL> set feedb on
SQL>
SQL> column "TOTAL ALLOC (MB)" format 9,999,990.00
SQL> column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
SQL> column "USED (MB)" format  9,999,990.00
SQL> column "FREE (MB)" format 9,999,990.00
SQL> column "% USED" format 990.00
SQL>
SQL> select a.tablespace_name,
  2         a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
  3         a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
  4         nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
  5         (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
  6  from ( select tablespace_name,
  7         sum(bytes) physical_bytes,
  8         sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
  9         from dba_data_files
 10         group by tablespace_name ) a,
 11       ( select tablespace_name, sum(bytes) tot_used
 12         from dba_segments
 13         group by tablespace_name ) b
 14  where a.tablespace_name = b.tablespace_name (+)
 15  --and   (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
 16  and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
 17  and   a.tablespace_name not like 'UNDO%'
 18  --order by 1
 19  order by 5
 20  /

TABLESPACE_NAME                TOTAL ALLOC (MB) TOTAL PHYS ALLOC (MB)     USED (MB)  % USED
------------------------------ ---------------- --------------------- ------------- -------
INDX                                  32,767.98                 50.00          0.00    0.00
USERS2                                32,767.98                200.00          0.00    0.00
USERS                                 32,767.98                111.25        104.00    0.32
SYSAUX                                32,767.98                600.00        468.44    1.43
SYSTEM                                32,767.98                700.00        609.94    1.86

5 rows selected.

SQL>
SQL>
SQL> begin
  2          for i in 0..1000000 loop
  3                  insert into spacetst
  4                  values(i, 'Space '||i, sysdate+i);
  5          end loop;
  6
  7          commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> select tablespace_name, ttl_alloc, ttl_free, round((ttl_free/ttl_alloc)*100,2) pct_free
  2  from
  3  (select fs.tablespace_name, sum(df.bytes) ttl_alloc, sum(fs.bytes) ttl_free
  4  from dba_free_space fs join dba_data_files df on (df.tablespace_name = fs.tablespace_name)
  5  group by fs.tablespace_name);

TABLESPACE_NAME                 TTL_ALLOC   TTL_FREE   PCT_FREE
------------------------------ ---------- ---------- ----------
SYSAUX                         2.5166E+10  136904704        .54
UNDOTBS1                       1095761920    2097152        .19
USERS                           144179200     524288        .36
USERS2                          209715200  208666624       99.5
SYSTEM                         1468006400   93388800       6.36
INDX                             52428800   51380224         98

6 rows selected.

SQL>
SQL> @tsalloc
SQL> set linesize 132 tab off trimspool on
SQL> set pagesize 105
SQL> set pause off
SQL> -- set echo off
SQL> set feedb on
SQL>
SQL> column "TOTAL ALLOC (MB)" format 9,999,990.00
SQL> column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
SQL> column "USED (MB)" format  9,999,990.00
SQL> column "FREE (MB)" format 9,999,990.00
SQL> column "% USED" format 990.00
SQL>
SQL> select a.tablespace_name,
  2         a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
  3         a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
  4         nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
  5         (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
  6  from ( select tablespace_name,
  7         sum(bytes) physical_bytes,
  8         sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
  9         from dba_data_files
 10         group by tablespace_name ) a,
 11       ( select tablespace_name, sum(bytes) tot_used
 12         from dba_segments
 13         group by tablespace_name ) b
 14  where a.tablespace_name = b.tablespace_name (+)
 15  --and   (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
 16  and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
 17  and   a.tablespace_name not like 'UNDO%'
 18  --order by 1
 19  order by 5
 20  /

TABLESPACE_NAME                TOTAL ALLOC (MB) TOTAL PHYS ALLOC (MB)     USED (MB)  % USED
------------------------------ ---------------- --------------------- ------------- -------
INDX                                  32,767.98                 50.00          0.00    0.00
USERS2                                32,767.98                200.00          0.00    0.00
USERS                                 32,767.98                137.50        136.00    0.42
SYSAUX                                32,767.98                600.00        468.44    1.43
SYSTEM                                32,767.98                700.00        609.94    1.86

5 rows selected.

SQL>

Now we’re getting to the point where the first query shows a space problem, a space problem that will resolve itself when the datafile autoextends. This is misleading as there is plenty of space which can be made available in the USERS tablespace, we just have to wait for Oracle to extend the datafile. This is where the second query provides a much clearer picture of the total available and potential space. Another insert to cause Oracle to extend the datafile doesn’t affect the output of the second query but alters the output of the first:


SQL>
SQL> begin
  2          for i in 0..1000000 loop
  3                  insert into spacetst
  4                  values(i, 'Space '||i, sysdate+i);
  5          end loop;
  6
  7          commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> select tablespace_name, ttl_alloc, ttl_free, round((ttl_free/ttl_alloc)*100,2) pct_free
  2  from
  3  (select fs.tablespace_name, sum(df.bytes) ttl_alloc, sum(fs.bytes) ttl_free
  4  from dba_free_space fs join dba_data_files df on (df.tablespace_name = fs.tablespace_name)
  5  group by fs.tablespace_name);

TABLESPACE_NAME                 TTL_ALLOC   TTL_FREE   PCT_FREE
------------------------------ ---------- ---------- ----------
SYSAUX                         2.5166E+10  136904704        .54
UNDOTBS1                       1.6436E+10   63963136        .39
USERS                           186122240    8912896       4.79
USERS2                          209715200  208666624       99.5
SYSTEM                         1468006400   93388800       6.36
INDX                             52428800   51380224         98

6 rows selected.

SQL>
SQL> @tsalloc
SQL> set linesize 132 tab off trimspool on
SQL> set pagesize 105
SQL> set pause off
SQL> -- set echo off
SQL> set feedb on
SQL>
SQL> column "TOTAL ALLOC (MB)" format 9,999,990.00
SQL> column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
SQL> column "USED (MB)" format  9,999,990.00
SQL> column "FREE (MB)" format 9,999,990.00
SQL> column "% USED" format 990.00
SQL>
SQL> select a.tablespace_name,
  2         a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
  3         a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
  4         nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
  5         (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
  6  from ( select tablespace_name,
  7         sum(bytes) physical_bytes,
  8         sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
  9         from dba_data_files
 10         group by tablespace_name ) a,
 11       ( select tablespace_name, sum(bytes) tot_used
 12         from dba_segments
 13         group by tablespace_name ) b
 14  where a.tablespace_name = b.tablespace_name (+)
 15  --and   (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
 16  and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
 17  and   a.tablespace_name not like 'UNDO%'
 18  --order by 1
 19  order by 5
 20  /

TABLESPACE_NAME                TOTAL ALLOC (MB) TOTAL PHYS ALLOC (MB)     USED (MB)  % USED
------------------------------ ---------------- --------------------- ------------- -------
INDX                                  32,767.98                 50.00          0.00    0.00
USERS2                                32,767.98                200.00          0.00    0.00
USERS                                 32,767.98                177.50        168.00    0.51
SYSAUX                                32,767.98                600.00        468.44    1.43
SYSTEM                                32,767.98                700.00        609.94    1.86

5 rows selected.

SQL>

And another insert creates the same ‘picture’ from the first query; the second query keeps up with the allocations and reports the available space more accurately:


SQL>
SQL> begin
  2          for i in 0..1000000 loop
  3                  insert into spacetst
  4                  values(i, 'Space '||i, sysdate+i);
  5          end loop;
  6
  7          commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> select tablespace_name, ttl_alloc, ttl_free, round((ttl_free/ttl_alloc)*100,2) pct_free
  2  from
  3  (select fs.tablespace_name, sum(df.bytes) ttl_alloc, sum(fs.bytes) ttl_free
  4  from dba_free_space fs join dba_data_files df on (df.tablespace_name = fs.tablespace_name)
  5  group by fs.tablespace_name);

TABLESPACE_NAME                 TTL_ALLOC   TTL_FREE   PCT_FREE
------------------------------ ---------- ---------- ----------
SYSAUX                         2.5166E+10  136904704        .54
USERS                           221511680    2359296       1.07
USERS2                          209715200  208666624       99.5
SYSTEM                         1468006400   93388800       6.36
INDX                             52428800   51380224         98

5 rows selected.

SQL>
SQL> @tsalloc
SQL> set linesize 132 tab off trimspool on
SQL> set pagesize 105
SQL> set pause off
SQL> -- set echo off
SQL> set feedb on
SQL>
SQL> column "TOTAL ALLOC (MB)" format 9,999,990.00
SQL> column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
SQL> column "USED (MB)" format  9,999,990.00
SQL> column "FREE (MB)" format 9,999,990.00
SQL> column "% USED" format 990.00
SQL>
SQL> select a.tablespace_name,
  2         a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
  3         a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
  4         nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
  5         (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
  6  from ( select tablespace_name,
  7         sum(bytes) physical_bytes,
  8         sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
  9         from dba_data_files
 10         group by tablespace_name ) a,
 11       ( select tablespace_name, sum(bytes) tot_used
 12         from dba_segments
 13         group by tablespace_name ) b
 14  where a.tablespace_name = b.tablespace_name (+)
 15  --and   (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
 16  and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
 17  and   a.tablespace_name not like 'UNDO%'
 18  --order by 1
 19  order by 5
 20  /

TABLESPACE_NAME                TOTAL ALLOC (MB) TOTAL PHYS ALLOC (MB)     USED (MB)  % USED
------------------------------ ---------------- --------------------- ------------- -------
INDX                                  32,767.98                 50.00          0.00    0.00
USERS2                                32,767.98                200.00          0.00    0.00
USERS                                 32,767.98                211.25        208.00    0.63
SYSAUX                                32,767.98                600.00        468.44    1.43
SYSTEM                                32,767.98                700.00        609.94    1.86

5 rows selected.

SQL>

On it goes, insert after insert, with the first query reporting only free space available from the current allocation and ignoring the fact that the datafile has a number of extensions left to go:


SQL>
SQL> begin
  2          for i in 0..1000000 loop
  3                  insert into spacetst
  4                  values(i, 'Space '||i, sysdate+i);
  5          end loop;
  6
  7          commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> select tablespace_name, ttl_alloc, ttl_free, round((ttl_free/ttl_alloc)*100,2) pct_free
  2  from
  3  (select fs.tablespace_name, sum(df.bytes) ttl_alloc, sum(fs.bytes) ttl_free
  4  from dba_free_space fs join dba_data_files df on (df.tablespace_name = fs.tablespace_name)
  5  group by fs.tablespace_name);

TABLESPACE_NAME                 TTL_ALLOC   TTL_FREE   PCT_FREE
------------------------------ ---------- ---------- ----------
SYSAUX                         2.5166E+10  136904704        .54
UNDOTBS1                       1095761920    8388608        .77
USERS                           256901120    4194304       1.63
USERS2                          209715200  208666624       99.5
SYSTEM                         1468006400   93388800       6.36
INDX                             52428800   51380224         98

6 rows selected.

SQL>
SQL> @tsalloc
SQL> set linesize 132 tab off trimspool on
SQL> set pagesize 105
SQL> set pause off
SQL> -- set echo off
SQL> set feedb on
SQL>
SQL> column "TOTAL ALLOC (MB)" format 9,999,990.00
SQL> column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
SQL> column "USED (MB)" format  9,999,990.00
SQL> column "FREE (MB)" format 9,999,990.00
SQL> column "% USED" format 990.00
SQL>
SQL> select a.tablespace_name,
  2         a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
  3         a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
  4         nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
  5         (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
  6  from ( select tablespace_name,
  7         sum(bytes) physical_bytes,
  8         sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
  9         from dba_data_files
 10         group by tablespace_name ) a,
 11       ( select tablespace_name, sum(bytes) tot_used
 12         from dba_segments
 13         group by tablespace_name ) b
 14  where a.tablespace_name = b.tablespace_name (+)
 15  --and   (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
 16  and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
 17  and   a.tablespace_name not like 'UNDO%'
 18  --order by 1
 19  order by 5
 20  /

TABLESPACE_NAME                TOTAL ALLOC (MB) TOTAL PHYS ALLOC (MB)     USED (MB)  % USED
------------------------------ ---------------- --------------------- ------------- -------
INDX                                  32,767.98                 50.00          0.00    0.00
USERS2                                32,767.98                200.00          0.00    0.00
USERS                                 32,767.98                245.00        240.00    0.73
SYSAUX                                32,767.98                600.00        468.44    1.43
SYSTEM                                32,767.98                700.00        609.94    1.86

5 rows selected.

SQL>
SQL>

When autoextend is in use it’s not the best idea to rely solely upon the sum of the BYTES column in the DBA_FREE_SPACE view as it’s defined without autoextend in mind. You can fix that by using the second, more robust, query to report space usage and allocations for tablespaces, and the query automatically takes into consideration the autoextend size limit. Using the second query may significantly reduce your stress levels when a file is nowhere near it’s autoextend size limit as you won’t be scrambling to add space only to find that Oracle will do that for you automatically.

And I’ll just bet you thought we were going to Mars.

January 14, 2014

You’re Not In The Group

Filed under: General — dfitzjarrell @ 15:59

In yet another Oracle forum (yes, I’m in a number of them) the following question was posed:


I'm reading oracle 9i sql and there is an example of a subquery which I'm trying to write as a group by query.

 select empno, ename, sal
 from emp
 where sal =
 (select min(sal) from emp);

 This works but I don't understand why I can't write this as a group by expression

 select empno, ename, min(sal)
 from emp
 group by empno, ename
 having min(sal) = sal;

 I'd like to understand what is going on inside of oracle that makes the subquery work but the group by fail.

 Thanks.

It is an interesting question, with, I believe, an interesting answer, so let’s dive in and see what makes these two queries ‘tick’.

The first query uses a subquery to return the absolute minimum sal from the emp table:


SQL> select min(sal) from emp;

  MIN(SAL)
----------
       800
SQL>

There is only one reference value for the driving query to use:


SQL> select empno, ename, sal
  2  from emp
  3  where sal =
  4  (select min(sal) from emp);

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800

SQL>

This is not the case with the initial group by attempt, as it returns every sal value as a minimum due to the group by criteria:


SQL> select empno, ename, min(sal)
  2  from emp
  3  group by empno, ename;

     EMPNO ENAME        MIN(SAL)
---------- ---------- ----------
      7521 WARD             1250
      7959 QUEEN            5000
      7869 JACK             5000
      7566 JONES            2975
      7844 TURNER           1500
      7876 ADAMS            1100
      7499 ALLEN            1600
      7369 SMITH             800
      7782 CLARK            2450
      7839 KING             5000
      7698 BLAKE            2850

     EMPNO ENAME        MIN(SAL)
---------- ---------- ----------
      7900 JAMES             950
      7902 FORD             3000
      7788 SCOTT            3000
      7939 DUKE             5000
      7949 PRINCE           5000
      7654 MARTIN           1250
      7934 MILLER           1300

18 rows selected.

SQL>

[I've added a few rows to the table to help explain another example; for the standard EMP build you should return 14 rows.] Since there is no absolute minimum sal from the group by query, and since a non-grouped column can’t be used as a reference in a HAVING clause:


SQL> select empno, ename, min(sal)
  2  from emp
  3  group by empno, ename
  4  having min(sal) = sal;
having min(sal) = sal
                  *
ERROR at line 4:
ORA-00979: not a GROUP BY expression


SQL>

you will need to re-write the group by query slightly to return the same answer the original query returned:


SQL> select empno, ename, min_sal
  2  from
  3  (select empno, ename, min(sal) min_sal
  4  from emp
  5  group by empno, ename
  6  order by 3)
  7  where rownum = 1;

     EMPNO ENAME         MIN_SAL
---------- ---------- ----------
      7369 SMITH             800

SQL>

Now the group by query works, and, through some ROWNUM magic, returns the same answer as the original, non-group-by query. The key to understanding this is the fact that the group by query did NOT return a single reference value, as min(sal) was evaluated for each of the empno/ename pairs in the table, and all of those pairs are unique. (There would be a real problem in HR if that wasn’t the case for an employee table. True, employee names may not be unique but their employee identifier must be, which makes each pair of values unique, thus the min(sal) returns the sal value for that employee.) The following two queries are equivalent:


SQL> select empno, ename, min(sal)
  2  from emp
  3  group by empno, ename;

     EMPNO ENAME        MIN(SAL)
---------- ---------- ----------
      7521 WARD             1250
      7959 QUEEN            5000
      7869 JACK             5000
      7566 JONES            2975
      7844 TURNER           1500
      7876 ADAMS            1100
      7499 ALLEN            1600
      7369 SMITH             800
      7782 CLARK            2450
      7839 KING             5000
      7698 BLAKE            2850

     EMPNO ENAME        MIN(SAL)
---------- ---------- ----------
      7900 JAMES             950
      7902 FORD             3000
      7788 SCOTT            3000
      7939 DUKE             5000
      7949 PRINCE           5000
      7654 MARTIN           1250
      7934 MILLER           1300

18 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4067220884

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    18 |   594 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |    18 |   594 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    18 |   594 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
       1195  bytes sent via SQL*Net to client
        531  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         18  rows processed

SQL>
SQL> select empno, ename, sal
  2  from emp;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975
      7654 MARTIN           1250
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7844 TURNER           1500
      7876 ADAMS            1100

     EMPNO ENAME             SAL
---------- ---------- ----------
      7900 JAMES             950
      7902 FORD             3000
      7934 MILLER           1300
      7939 DUKE             5000
      7949 PRINCE           5000
      7959 QUEEN            5000
      7869 JACK             5000

18 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    18 |   594 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    18 |   594 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         17  consistent gets
          0  physical reads
          0  redo size
       1196  bytes sent via SQL*Net to client
        531  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         18  rows processed

SQL>

Notice that the second, non-group-by query is slightly more efficient (as evidenced by the COST column in both plan outputs). Had there been more than one record with the same empno/ename combination, each with a different value for sal, the group by query would, indeed, return a smaller result set. It still wouldn’t return the single row the original query reports, at least not without using order by in an in-line view and ROWNUM like the ‘fixed’ example illustrates.

Understanding the data in a table is key to understanding the queries that return data from it. It wasn’t the query, but the data, that caused a correctly written (meaning no syntax or logic errors) group by query to, well, fail. And understanding what the group by query DOES return allows you to make adjustments in coding and logic which, in turn, results in the correct result being displayed.

Now, let’s rejoin the group.

January 13, 2014

“Skip” That

Filed under: General — dfitzjarrell @ 15:09

In a forum I frequent a question was raised regarding an index not being used. The poster is using Oracle 11.1.0.7, which limits the index access paths Oracle can use; a concatenated index is created on the table (which has columns x,y,z,t,q and w, for lack of better names) on columns x, y, and z. Let’s look at what 11.2.0.3 will do with such a situation then explain what may be going on with 11.1.0.7 and, as a result, why the index isn’t being used.

The original problem states that the table is new, meaning it has been created and has no data in it (possibly a daily occurrence) and at least one index is created on this table during the create table process. Of course having no data in the table means that even if you do generate statistics they will be essentially useless. Let’s set this up with 11.2.0.3, using a single-column index (since at the time this example was created the existence of the concatenated index wasn’t known) and see what happens:


SQL>
SQL> --
SQL> -- Create an empty table with no indexes
SQL> --
SQL> create table emp2 as select * from emp where 0=1;

Table created.

SQL>
SQL> --
SQL> -- Create an index on the empty table
SQL> --
SQL> create index empno_idx on emp2(empno);

Index created.

SQL>
SQL> --
SQL> -- Populate the table (will insert 1,800,000 rows)
SQL> --
SQL>
SQL> begin
  2  	     for i in 1..100000 loop
  3  		     insert into emp2
  4  		     select * from emp;
  5
  6  		     update emp2
  7  		     set empno = empno+i, mgr=mgr+i, updated='YES'
  8  		     where updated is null;
  9
 10  		     commit;
 11  	     end loop;
 12
 13  	     commit;
 14  end;
 15  /

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Select from emp2 and see if any indexes are used
SQL> --
SQL> set autotrace on
SQL>
SQL> select *
  2  from emp2
  3  where empno = (select min(empno)+1 from emp);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO UPD
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---
      7370 SMITH      CLERK           7903 17-DEC-80        800                    20 YES


Execution Plan
----------------------------------------------------------
Plan hash value: 1300931333

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    90 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP2      |     1 |    90 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMPNO_IDX | 82203 |       |     1   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE           |           |     1 |    13 |            |          |
|   4 |     TABLE ACCESS FULL       | EMP       |    18 |   234 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - access("EMPNO"= (SELECT MIN("EMPNO")+1 FROM "EMP" "EMP"))

Note
-----
   - dynamic sampling used for this statement (level=2)


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

SQL>
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Generate statistics
SQL> --
SQL> exec dbms_stats.gather_table_stats('GRIBNAUT', 'EMP2', estimate_percent=>100, cascade=>true)

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Try the query again
SQL> --
SQL> set autotrace on
SQL>
SQL> select *
  2  from emp2
  3  where empno = (select min(empno)+1 from emp);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO UPD
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---
      7370 SMITH      CLERK           7903 17-DEC-80        800                    20 YES


Execution Plan
----------------------------------------------------------
Plan hash value: 1300931333

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |    18 |   738 |    22   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP2      |    18 |   738 |    19   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMPNO_IDX |    18 |       |     3   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE           |           |     1 |    13 |            |          |
|   4 |     TABLE ACCESS FULL       | EMP       |    18 |   234 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - access("EMPNO"= (SELECT MIN("EMPNO")+1 FROM "EMP" "EMP"))

Note
-----
   - dynamic sampling used for this statement (level=2)


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

SQL>
SQL> set autotrace off
SQL>

Even with no ‘formal’ statistics Oracle 11.2.0.3 can use the CBO to generate index access paths to use the index because of dynamic sampling. Knowing that a concatenated index was created we drop the original single-column index and replace it with a concatenated index:


SQL> --
SQL> -- Drop the empno index and replace it
SQL> --
SQL> drop index empno_idx;

Index dropped.

SQL> create index ename_empno_idx on emp2(ename, empno);

Index created.

SQL>
SQL> --
SQL> -- Try the query again
SQL> --
SQL> set autotrace on
SQL>
SQL> select *
  2  from emp2
  3  where empno = (select min(empno)+1 from emp);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO UPD
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---
      7370 SMITH      CLERK           7903 17-DEC-80        800                    20 YES


Execution Plan
----------------------------------------------------------
Plan hash value: 2131058526

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |    18 |   738 |    26   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP2            |    18 |   738 |    23   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | ENAME_EMPNO_IDX |    18 |       |    20   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE           |                 |     1 |    13 |            |          |
|   4 |     TABLE ACCESS FULL       | EMP             |    18 |   234 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   2 - access("EMPNO"= (SELECT MIN("EMPNO")+1 FROM "EMP" "EMP"))
       filter("EMPNO"= (SELECT MIN("EMPNO")+1 FROM "EMP" "EMP"))

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         52  consistent gets
         32  physical reads
          0  redo size
       1094  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> set autotrace off
SQL>

Yes, we should have truncated the table, created the new index and re-loaded the data but the results, in 11.2.0.3, would be the same. Notice that this release of Oracle provides an INDEX SKIP SCAN access path; 11.1.0.7 doesn’t provide that index access pathway. As such it’s very likely that the column in the WHERE clause is referencing the second or third column in the index key and if that is the case then Oracle 11.1.0.7 will shift to a full table scan. To circumvent that it will be necessary to create another index, a concatenated index where the leading column is the column of interest as indicated by the WHERE clause. If we recreate the index yet again, swapping the columns so that the leading column is the column referenced in the WHERE clause both 11.1.0.7 and 11.2.0.3 will show the same plan:


SQL> -- Drop the empno index and replace it
SQL> --
SQL> drop index ename_empno_idx;

Index dropped.

SQL> create index empno_ename_idx on emp2(empno, ename);

Index created.

SQL>SQL> --
SQL> -- Try the queries again
SQL> --
SQL> set autotrace on
SQL>
SQL> select *
  2  from emp2
  3  where empno = (select min(empno)+1 from emp);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO UPD
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---
      7370 SMITH      CLERK           7903 17-DEC-80        800                    20 YES


Execution Plan
----------------------------------------------------------
Plan hash value: 3006901234

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |    18 |   738 |    24   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP2            |    18 |   738 |    21   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMPNO_ENAME_IDX |    18 |       |     3   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE           |                 |     1 |    13 |            |          |
|   4 |     TABLE ACCESS FULL       | EMP             |    18 |   234 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   2 - access("EMPNO"= (SELECT MIN("EMPNO")+1 FROM "EMP" "EMP"))

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         20  consistent gets
          2  physical reads
          0  redo size
       1094  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>

So, we see that computing statistics on the index in question doesn’t help matters in 11.1.0.7 since, apparently, the problem query is looking for a table column that is not the leading column of that index. And we also see that adding an index, where the leading column is the column of interest, causes that index to be used.

Sometimes it’s good to skip things.

December 15, 2013

To DMA, Or Not To DMA

Filed under: Exadata — dfitzjarrell @ 12:35

Exadata is a different system for a DBA to administer. Some tasks in this environment, such as running the exachk script, require root O/S privileges. This script can be run by the system administrator, and this will be the case if you are managing Exadata as a DBA. However, a new role has emerged relative to Exadata, that of the Database Machine Administrator, or DMA. Let’s look at what being a DMA really means.

In addition to the usual DBA skillset, the DMA must also be familiar with, and be able to understand, the following management and monitoring commands on the specified systems.

On the compute nodes (database nodes):

Linux: top , mpstat , vmstat , iostat , fdisk , ustat , sar , sysinfo
Exadata: dcli
ASM: asmcmd , asmca
Clusterware: crsctl , srvctl

On the storage servers/cells:

Linux: top , mpstat , vmstat , iostat , fdisk , ustat , sar , sysinfo
Cell management: cellcli , cellsrvstat

Being a DMA also includes other areas of responsibility not associated with being a DBA. The following table summarizes the areas of responsibility for a DMA:

*******DMA Responsibilities *******
Skill Percent
System Administrator 15
Storage Administrator 0
Network Administrator 5
Database Administrator 60
Cell Administrator 20

The ‘Percent’ column indicates the percentage of the overall Exadata system requiring this knowledge, and as you can see if you’ve been an 11g RAC administrator, you have 60 percent of the skillset required to be a DMA. Notice that storage administration knowledge is not necessary; this is because Exadata uses a different storage model than normal, a model where the database servers (or nodes) have no direct access to the storage and must access it through the storage servers. Also the number of available disks is dependent upon the number of available storage servers, with each storage server managing 12 physical disks. For many Exadata installations a separate server is configured to communicate with Exadata and with Oracle to provide information on hardware and firmware problems and/or failures; this system notifies Oracle Customer Support of the issue, generates any parts orders and notifies the Oracle technician when to pick up the delivered parts and dispatches the techician to the site to effect the repairs/replacements. Unless there is an external SAN or NAS device connected to Exadata to store database backups and exports there isn’t much need for a Storage Administrator. The remaining skills necessary to be a DMA are not difficult to learn and master. The Cell Administrator commands you will need ( cellcli , dcli ) will increase your knowledge to 80 percent of the DMA skillset. CellCLI is the command-line interface to monitor and manage the storage cells. There are three supplied logins to each storage cell and these are ‘root’, ‘cellmonitor’ and ‘celladmin’. As you can probably guess ‘celladmin’ is the most powerful login that isn’t ‘root’ (the superuser in Linux and Unix). You can do most anything to the storage cells, including startup and shutdown, with ‘celladmin’. The ‘cellmonitor’ user can generate reports and list attributes from the storage cells but has no authority to perform management tasks. The full list of available cellcli commands is shown below:

CellCLI> help

 HELP [topic]
   Available Topics:
        ALTER
        ALTER ALERTHISTORY
        ALTER CELL
        ALTER CELLDISK
        ALTER GRIDDISK
        ALTER IBPORT
        ALTER IORMPLAN
        ALTER LUN
        ALTER PHYSICALDISK
        ALTER QUARANTINE
        ALTER THRESHOLD
        ASSIGN KEY
        CALIBRATE
        CREATE
        CREATE CELL
        CREATE CELLDISK
        CREATE FLASHCACHE
        CREATE FLASHLOG
        CREATE GRIDDISK
        CREATE KEY
        CREATE QUARANTINE
        CREATE THRESHOLD
        DESCRIBE
        DROP
        DROP ALERTHISTORY
        DROP CELL
        DROP CELLDISK
        DROP FLASHCACHE
        DROP FLASHLOG
        DROP GRIDDISK
        DROP QUARANTINE
        DROP THRESHOLD
        EXPORT CELLDISK
        IMPORT CELLDISK
        LIST
        LIST ACTIVEREQUEST
        LIST ALERTDEFINITION
        LIST ALERTHISTORY
        LIST CELL
        LIST CELLDISK
        LIST FLASHCACHE
        LIST FLASHCACHECONTENT
        LIST FLASHLOG
        LIST GRIDDISK
        LIST IBPORT
        LIST IORMPLAN
        LIST KEY
        LIST LUN
        LIST METRICCURRENT
        LIST METRICDEFINITION
        LIST METRICHISTORY
        LIST PHYSICALDISK
        LIST QUARANTINE
        LIST THRESHOLD
        SET
        SPOOL
        START

CellCLI>

All of the above commands are available to ‘celladmin’; only the LIST, DESCRIBE, SET and SPOOL commands are available to ‘cellmonitor’.

Networking commands that you may need are ifconfig , iwconfig , netstat , ping , traceroute , and tracepath . You may, at some time, also need ifup and ifdown , to bring up or bring down network interfaces, although using these commands will not be a regular occurrence. The following example shows how to bring up the eth0 interface.

# ifup eth0

It seems like a daunting task, to become a DMA, but it really isn’t that difficult. It does require a slightly different mindset, as you are now looking at, and managing, the entire system, rather than just the database. There will still be a need for a dedicated System Administrator and Network Administrator for your Exadata system, because, as a DMA, you won’t be responsible for configuration of these resources, nor will you be responsible for patching and firmware upgrades. The DMA is, essentially, assisting these dedicated administrators by assuming the day-to-day tasks these resources would provide. Being a DMA is also more useful to you and to the enterprise as the regular tasks for these areas can be performed by the person or persons who do most of the interaction with Exadata on a daily basis. Enterprises vary, however, and it may not be possible to assume the role of DMA as the division of duties is strictly outlined and enforced. It is good to know, though, that such a role exists and may be made available to you at some time in the future.

November 9, 2013

“Compressing” Matters

Filed under: General — dfitzjarrell @ 14:55

Compression is often mentioned in reverent tones as the cure for database space problems, and properly used it can be just that. However simply throwing compression at a table without careful consideration of what can be accomplished or what behavior to expect afterwards can leave you scratching your head in confusion. Let’s look at the compression options available in Oracle 11.2, noting what happens after the initial compression is completed and data is modified.

Oracle 11.2.0.x provides, out of the box, two types of table compression, BASIC and OLTP. BASIC is exactly what its name says, compressed with a basic compression algorithm. Let’s see what space savings BASIC can provide:

SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                   713031680
SALGRADE                                  65536

SQL> alter table emp move compress;

Table altered.

SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                   184549376
SALGRADE                                  65536

SQL>

So far, so good. Let’s now update some data in the EMP table and see what happens to the consumed space:

SQL> update emp set sal=5001 where sal=5000;

1048576 rows updated.

SQL> commit;

Commit complete.

SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                   234881024
SALGRADE                                  65536

SQL> 

The update caused Oracle to uncompress the rows being updated and, thus, increase the space consumed by the table. You can re-compress the table after updates but that could get to be a daunting task, requiring a stored procedure for inserts, updates and deletes to the affected table or tables. A second option is a scheduled job run late at night to re-compress the compressed tables.

OLTP compression works a bit differently, as it compresses the data but will, when affected data blocks are marked as full, re-compress the block automatically. Let’s look at the same table using OLTP compression. First let’s uncompress the table:

SQL> alter table emp move nocompress;

Table altered.

SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                   645922816
SALGRADE                                  65536

SQL> 

It’s interesting to note that the uncompressed table size has decreased slightly from its original value, possibly because unnecessary NULL bytes have been removed (this is a guess). Compressing for OLTP and updating the same rows produces these results:

SQL> alter table emp move compress for oltp;

Table altered.

SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                   184549376
SALGRADE                                  65536

SQL> update emp set sal=5000 where sal=5001;

1048576 rows updated.

SQL> commit;

Commit complete.

SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                   201326592
SALGRADE                                  65536

SQL>

Notice that the compressed size of the OLTP table is initially the same as it was for BASIC compression; the difference is in the compressed size after the update. It’s the OLTP compression mechanism that does this, as some of the updated blocks are now full and triggered the automatic re-compression of the data. Of course these are not the only two compression options, but the next set of compression types, using the Hybrid Columnar Compression (HCC) algorithm, require licensing the Advanced Compression feature.

HCC works differently than Basic or OLTP compression as it re-organizes data into Compression Units (CU). Note that there are two types of HCC compression, QUERY and ARCHIVE, with two levels of LOW and HIGH for each. Using the same examples from a previous article let’s look at these compression types in action. First in line is QUERY:

SQL> 
SQL> --
SQL> -- Current storage for the EMP table
SQL> -- (this is simply a test table for this example)
SQL> --
SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                   713031680
EMP_IDX                               478150656
SALGRADE                                  65536

Elapsed: 00:00:00.82
SQL> 
SQL> --
SQL> -- Compress the table for query high (use HCC)
SQL> --
SQL> -- Note elapsed time to compress
SQL> --
SQL> alter table emp move compress for query high;

Table altered.

Elapsed: 00:00:35.65
SQL> 
SQL> --
SQL> -- Index is now invalid
SQL> --
SQL> -- Must rebuild to make it usable
SQL> --
SQL> -- Note elapsed time
SQL> --
SQL> alter index emp_idx rebuild;

Index altered.

Elapsed: 00:01:13.70
SQL> 
SQL> --
SQL> -- Current compression type, storage for table/index
SQL> -- initially after compression is enabled
SQL> --
SQL> select table_name, compression, compress_for
  2  from user_tables;

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
DEPT                           DISABLED
BONUS                          DISABLED
SALGRADE                       DISABLED
DUMMY                          DISABLED
EMP                            ENABLED  QUERY HIGH

Elapsed: 00:00:00.20
SQL> 
SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                     8388608
EMP_IDX                               260046848
SALGRADE                                  65536

Elapsed: 00:00:00.03
SQL>

The resulting size is much smaller than either of the Basic or OLTP compression options. Even at the Query Low compression rate the size is still less than OLTP compression can provide:

SQL> 
SQL> --
SQL> -- Initial storage
SQL> --
SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                   713031680
EMP_IDX                               478150656
SALGRADE                                  65536

Elapsed: 00:00:00.25
SQL> 
SQL> --
SQL> -- Compress for QUERY LOW
SQL> --
SQL> -- Note elapsed time
SQL> --
SQL> alter table emp move compress for query low;

Table altered.

Elapsed: 00:00:16.16
SQL> 
SQL> alter index emp_idx rebuild;

Index altered.

Elapsed: 00:00:43.08
SQL> 
SQL> --
SQL> -- These figures are the same as those generated
SQL> -- AFTER the HCC compressed data was updated the first time
SQL> --
SQL> select table_name, compression, compress_for
  2  from user_tables;

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
DEPT                           DISABLED
BONUS                          DISABLED
SALGRADE                       DISABLED
DUMMY                          DISABLED
EMP                            ENABLED  QUERY LOW

Elapsed: 00:00:00.02
SQL> 
SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                    14680064
EMP_IDX                               260046848
SALGRADE                                  65536

Elapsed: 00:00:00.02
SQL>

The QUERY compression type is definitely a space saver, but note that after updates to a table compressed with either QUERY LOW or QUERY HIGH the compression behavior reverts to the OLTP re-compression mechanism.

The HCC compression type ARCHIVE is definitely the most aggressive in terms of space savings, but it’s also intended for data that is designated read-only and is or will be archived for occasional use:

SQL> --
SQL> -- Current storage for the EMP table
SQL> -- (this is simply a test table for this example)
SQL> --
SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                   713031680
EMP_IDX                               478150656
SALGRADE                                  65536

Elapsed: 00:00:00.02
SQL> 
SQL> --
SQL> -- Compress the table for archive high
SQL> --
SQL> -- Note elapsed time to compress
SQL> --
SQL> alter table emp move compress for archive high;

Table altered.

Elapsed: 00:00:38.55
SQL> 
SQL> --
SQL> -- Index is now invalid
SQL> --
SQL> -- Must rebuild to make it usable
SQL> --
SQL> -- Note elapsed time
SQL> --
SQL> alter index emp_idx rebuild;

Index altered.

Elapsed: 00:00:39.45
SQL> 
SQL> --
SQL> -- Current compression type, storage for table/index
SQL> -- initially after compression is enabled
SQL> --
SQL> select table_name, compression, compress_for
  2  from user_tables;



TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
DEPT                           DISABLED
BONUS                          DISABLED
SALGRADE                       DISABLED
DUMMY                          DISABLED
EMP                            ENABLED  ARCHIVE HIGH

Elapsed: 00:00:00.02
SQL> 
SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                     4194304
EMP_IDX                               260046848
SALGRADE                                  65536

Elapsed: 00:00:00.01
SQL>

The space savings are substantial, taking the table from its original size of 680 megabytes down to 4 megabytes, a savings of 99.41 percent. Using ARCHIVE LOW instead of ARCHIVE HIGH still produces impressive results:

SQL> --
SQL> -- Initial storage
SQL> --
SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                   713031680
EMP_IDX                               478150656
SALGRADE                                  65536

Elapsed: 00:00:01.31
SQL> 
SQL> --
SQL> -- Compress for ARCHIVE LOW
SQL> --
SQL> -- Note elapsed time
SQL> --
SQL> alter table emp move compress for archive low;

Table altered.

Elapsed: 00:00:34.16
SQL> 
SQL> alter index emp_idx rebuild;

Index altered.

Elapsed: 00:00:48.44
SQL> 
SQL> --
SQL> -- These figures are the same as those generated
SQL> -- AFTER the HCC compressed data was updated the first time
SQL> --
SQL> select table_name, compression, compress_for
  2  from user_tables;

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
DEPT                           DISABLED
BONUS                          DISABLED
SALGRADE                       DISABLED
DUMMY                          DISABLED
EMP                            ENABLED  ARCHIVE LOW

Elapsed: 00:00:00.03
SQL> 
SQL> select segment_name, sum(bytes) total_space
  2  from user_segments
  3  group by segment_name;

SEGMENT_NAME                        TOTAL_SPACE
----------------------------------- -----------
DEPT                                      65536
DUMMY                                     65536
EMP                                     8388608
EMP_IDX                               260046848
SALGRADE                                  65536

Elapsed: 00:00:00.02
SQL>

Using ARCHIVE LOW the table ends up twice as large as it did using ARCHIVE HIGH but the table is 98.82 percent smaller than it was before compression. This is the same level of compression afforded by QUERY HIGH. The same caveat for QUERY level compression also applies to ARCHIVE; updates to a table compressed either ARCHIVE LOW or ARCHIVE HIGH cause Oracle to revert to the OLTP re-compression mechanism. Also note that the reported compression type in the data dictionary does NOT change when this occurs.

Compression can be an excellent tool to save database storage, but you need to be aware that the compression levels can change for updated tables when running Exadata and using any of the HCC compression types. Also it’s good to be aware that BASIC compression won’t automatically re-compress updated data; maintaing a reasonable compression ratio will require some sort of manual or scheduled intervention, so using OLTP compression on active tables is likely the best choice if you are planning on using table compression.

Compression: saving space, one byte at a time.

June 12, 2013

Sorting Out Constraints

Filed under: General — dfitzjarrell @ 13:24

An interesting question was posed on the Oracle-L list a while back; a member was having difficulty getting a constraint created using an existing non-unique index as Oracle would consistently consume all of the TEMP space and cause the process to terminate for lack of resources. The conventional wisdom was that since that index was already in existence no sorting would be required and it should be a relatively small task to validate the uniqueness of the data and create the constraint. Unfortunately that isn’t the case; I’ve tried to replicate the situation in my personal database but could not create the 1.8 billion rows reported in the original question (yes, that’s 1.8 BILLION rows). The data volume doesn’t really matter as far as the plans and execution path Oracle takes are concerned so I set up an example using the EMP table and set off to trace the session and see what Oracle was doing using guidance from a similar example posted by Jonathan Lewis. First let’s do some setup:


alter table emp modify ename varchar2(12);

Table altered.

alter table emp modify empno number;

Table altered.

SQL> 
SQL> begin
  2  	     for i in 1..6000 loop
  3  		     insert into emp
  4  		     values(i, 'RAMBO'||i, 'FRUMP', 7000+mod(i,29), add_months(sysdate, (-1*i)), mod(1000*i, 43), 0, 40);
  5  	     end loop;
  6  
  7  	     for i in 8000..1000000 loop
  8  		     insert into emp
  9  		     values(i, 'RAM'||i, 'FRUMP', 7000+mod(i,29), add_months(sysdate, -1), mod(i, 43), 0, 40);
 10  	     end loop;
 11  
 12  	     commit;
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL> 

I’ve modified the table to increase the length of the ename column and increase the size of the empno column, then loaded 999,000 rows into the table. Now it’s time to create the non-unique index:


SQL> create index emp_idx on emp(empno);

Index created.

SQL> 

Verifying the existence of the index:


SQL> select * from user_segments where segment_name = 'EMP_IDX';

SEGMENT_NAME                                                                      PARTITION_NAME                 SEGMENT_TYPE       SEGMENT_SU TABLESPACE_NAME                     BYTES     BLOCKS
--------------------------------------------------------------------------------- ------------------------------ ------------------ ---------- ------------------------------ ---------- ----------
   EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS   MAX_SIZE RETENTI MINRETENTION PCT_INCREASE  FREELISTS FREELIST_GROUPS BUFFER_ FLASH_C CELL_FL
---------- -------------- ----------- ----------- ----------- ---------- ------- ------------ ------------ ---------- --------------- ------- ------- -------
EMP_IDX                                                                                                          INDEX              ASSM       USERS                            18874368       2304
        33          65536     1048576           1  2147483645 2147483645                                                              DEFAULT DEFAULT DEFAULT


SQL> 

So I know the index exists (yes, I knew that from the ‘Index created.’ statement but I like to see that Oracle has all of its ducks in a row). I will now add the primary key constraint using the non-unique index, this on Oracle version 11.2.0.3:


SQL> alter table emp add constraint emp_pk primary key(empno) using index emp_idx;

Table altered.

SQL> 

In this case the constraint created successfully without running out of memory or temp space, but I had far fewer rows in the table. Looking at the trace file generated for that session a curious query rears its ugly head:


********************************************************************************

SQL ID: 2u8n8uzz763hd Plan Hash: 0

alter table emp add constraint emp_pk primary key(empno) using index emp_idx
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.06          0          2          0           0
Execute      1      0.00       0.02          0       4805          4           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.08          0       4807          4           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 611  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************

SQL ID: bwy3tr44u2071 Plan Hash: 1273047431

select /*+ all_rows ordered */ A.rowid, :1, :2, :3 
from
 "BING"."EMP" A, (select /*+ all_rows */ "EMPNO" from "BING"."EMP" A where( 
  "EMPNO" is not null) group by  "EMPNO" having count(1) > 1) B where( 
  "A"."EMPNO" = "B"."EMPNO")


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          4          0           0
Fetch        1      9.43       9.94       2227       4448          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      9.43       9.95       2227       4452          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  HASH JOIN  (cr=4448 pr=2227 pw=0 time=9948046 us cost=3476 size=33098532 card=871014)
    998015     998015     998015   INDEX FULL SCAN EMP_IDX (cr=2224 pr=2227 pw=0 time=1530974 us cost=456 size=21775350 card=871014)(object id 83641)
         0          0          0   VIEW  (cr=2224 pr=0 pw=0 time=5014325 us cost=456 size=11323182 card=871014)
         0          0          0    FILTER  (cr=2224 pr=0 pw=0 time=5014317 us)
    998015     998015     998015     SORT GROUP BY (cr=2224 pr=0 pw=0 time=3919827 us cost=456 size=11323182 card=871014)
    998015     998015     998015      INDEX FULL SCAN EMP_IDX (cr=2224 pr=0 pw=0 time=1251086 us cost=456 size=11323182 card=871014)(object id 83641)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                       260        0.02          0.15
  db file scattered read                        284        0.01          0.16
********************************************************************************

(The full query Oracle is executing is this:


select /*+ all_rows ordered */
	A.rowid, :1, :2, :3
from
	BING.EMP A,
	(
	select /*+ all_rows */
		EMPNO
	from
		BING.EMP A
	where	(EMPNO is not null)
	group by
		EMPNO
	having
		count(1) > 1
	) B
where
	(A.EMPNO = B.EMPNO)
union all
select
	/*+ all_rows ordered */
	A.rowid, :1, :2, :3
from
	BING.EMP A
where
	(EMPNO is null)
;

and the formatted plan from dbms_xplan is:


Plan hash value: 628507593

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |  1048K|    25M|   487   (7)| 00:00:06 |
|   1 |  UNION-ALL         |         |       |       |            |          |
|*  2 |   FILTER           |         |       |       |            |          |
|   3 |    SORT GROUP BY   |         |  1048K|    25M|   487   (7)| 00:00:06 |
|   4 |     INDEX FULL SCAN| EMP_IDX |  1048K|    25M|   456   (1)| 00:00:06 |
|*  5 |   FILTER           |         |       |       |            |          |
|   6 |    INDEX FULL SCAN | EMP_IDX |  1048K|    25M|   456   (1)| 00:00:06 |
------------------------------------------------------------------------------

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

   2 - filter(COUNT(*)>1)
   5 - filter(NULL IS NOT NULL)

which differs from the plan shown in the tkprof output as the UNION-ALL is replaced by a HASH JOIN. I will use the actual plan from the tkprof report to continue this discussion.)

Notice that Oracle is not only using index full scans but is also sorting the results and creating a hash join to validate no key has more than one entry in the table. Since an index is already in place the query above seems to be rather inefficient; a better query is shown below which uses the index values in order (it walks the index keys) to eliminate the sort operation. Running the query and checking the plan it’s obvious this method of attack saves time and work — notice the SORT GROUP BY NOSORT step:


SQL> 
SQL> set autotrace on
SQL> 
SQL> select *
  2  from
  3  (select /*+ index(emp(empno)) */ empno
  4  	     from emp
  5  	     where empno is not null
  6  	     group by empno
  7  	     having count(*) > 1)
  8  /

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2890940416

---------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |  1048K|    13M|   456   (1)| 00:00:06 |
|*  1 |  FILTER               |         |       |       |            |          |
|   2 |   SORT GROUP BY NOSORT|         |  1048K|    13M|   456   (1)| 00:00:06 |
|   3 |    INDEX FULL SCAN    | EMP_IDX |  1048K|    13M|   456   (1)| 00:00:06 |
---------------------------------------------------------------------------------

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

   1 - filter(COUNT(*)>1)

SQL> 

But, if that query is used as a subquery the plan isn’t what would be expected:


SQL> 
SQL> select /*+ leading(y x) use_nl(x) index(emp(empno)) no_merge(y) */
  2  	     x.rowid
  3  from
  4  (select *
  5  from
  6  (select /*+ index(emp(empno)) */ empno
  7  	     from emp
  8  	     where empno is not null
  9  	     group by empno
 10  	     having count(*) > 1)) y, emp x
 11  where x.empno = y.empno
 12  /

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1374118939

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |  1048K|    38M|   464   (2)| 00:00:06 |
|   1 |  NESTED LOOPS       |         |  1048K|    38M|   464   (2)| 00:00:06 |
|   2 |   VIEW              |         |  1048K|    13M|   456   (1)| 00:00:06 |
|*  3 |    FILTER           |         |       |       |            |          |
|   4 |     HASH GROUP BY   |         |  1048K|    13M|   456   (1)| 00:00:06 |
|   5 |      INDEX FULL SCAN| EMP_IDX |  1048K|    13M|   456   (1)| 00:00:06 |
|*  6 |   INDEX RANGE SCAN  | EMP_IDX |     1 |    25 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   3 - filter(COUNT(*)>1)
   6 - access("X"."EMPNO"="Y"."EMPNO")

SQL> 

A HASH GROUP BY replaces the SORT GROUP BY NOSORT and the sorting returns (as part of the HASH GROUP BY a sort is executed on the hash keys). That can be ‘turned off’ by setting _gby_hash_aggregation_enabled to FALSE which would produce the following plan:


SQL> select /*+ leading(y x) use_nl(x) index(emp(empno)) no_merge(y) */
  2          x.rowid
  3  from
  4  (select *
  5  from
  6  (select /*+ index(emp(empno)) */ empno
  7          from emp
  8          where empno is not null
  9          group by empno
 10          having count(*) > 1)) y, emp x
 11  where x.empno = y.empno
 12  /

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1759981554

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |  1048K|    38M|   464   (2)| 00:00:06 |
|   1 |  NESTED LOOPS       |         |  1048K|    38M|   464   (2)| 00:00:06 |
|   2 |   VIEW              |         |  1048K|    13M|   456   (1)| 00:00:06 |
|*  3 |    FILTER           |         |       |       |            |          |
|   4 |     SORT GROUP BY   |         |  1048K|    13M|   456   (1)| 00:00:06 |
|   5 |      INDEX FULL SCAN| EMP_IDX |  1048K|    13M|   456   (1)| 00:00:06 |
|*  6 |   INDEX RANGE SCAN  | EMP_IDX |     1 |    25 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   3 - filter(COUNT(*)>1)
   6 - access("X"."EMPNO"="Y"."EMPNO")

SQL>

which more clearly shows the sort in action. The query has been hinted to provide a nested loops path rather than the overall hash join and the INDEX FULL SCAN driving the orignal hash join has been replaced with an INDEX RANGE SCAN which returns a smaller set of data. But it’s still sorting the data, unnecessarily, and try as we might it doesn’t appear it can be fixed, at least not simply.

Oracle offers a package named DBMS_ADVANCED_REWRITE that can be used to ‘divert’ execution from the submitted query to a similar, better performing, query. Unfortunately it doesn’t set hidden parameters so the query submitted to DBMS_ADVANCED_REWRITE needs to generate a better plan without the use of such parameters. As Jonathan Lewis pointed out to rewrite the above query to get the desired plan requires subquery factoring (the WITH clause) and DBMS_ADVANCED_REWRITE doesn’t accept queries using subquery factoring. Drat, drat and double drat.

It appears that using an existing nonunique index to enforce a primary key constraint may not be the path to take with extremely large tables (with millions or billions of rows). Dropping the existing index and constraint and creating a new primary key constraint provides this execution path:


********************************************************************************

SQL ID: 336zud14mr3ma Plan Hash: 2730705978

CREATE UNIQUE INDEX "BING"."EMP_PK" on "BING"."EMP"("EMPNO") NOPARALLEL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          1          0           0
Execute      1      5.48       6.28          0       6543       3180           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      5.48       6.30          0       6544       3180           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 721     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  INDEX BUILD UNIQUE EMP_PK (cr=6646 pr=0 pw=2086 time=6277350 us)(object id 0)
    998015     998015     998015   SORT CREATE INDEX (cr=6464 pr=0 pw=0 time=4524100 us)
    998015     998015     998015    TABLE ACCESS FULL EMP (cr=6464 pr=0 pw=0 time=1430193 us cost=1744 size=6814899 card=524223)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  direct path write                               1        0.00          0.00
  reliable message                                1        0.00          0.00
********************************************************************************

A single sort on the table data, using a full table scan, seems to be a more efficient path to the primary key, especially on large tables as no HASH JOIN is necessary. That’s not what I would expect given the existence of an index, unique or not, for the key but that’s currently how Oracle is behaving. Since there is no obvious way around this it’s just a matter of time until Oracle modifies the code to make this process more efficient.

Hopefully such occurrences (primary key constraints using existing nonunique indexes failing to create) are few and far between; it is nice to know, though, what Oracle is doing to create/enable such constraints to better prepare for the possiblity the constraint won’t create as expected.

Boy, I’m glad that’s sorted.

May 30, 2013

We Have No Constraints

Filed under: General — dfitzjarrell @ 12:53

A question was recently asked by a colleague regarding unique indexes and constraints. He was complaining that the unique index did not create the associated constraint by default. Unlike the situation when a unique constraint is created (the associated unique index is created if no other index is specified) there really isn’t any reason for Oracle to create a unique constraint for a given unique index. Also, it’s possible to create a unique constraint which uses a non-unique index. Let’s explore this further.

Whether you have a unique index or a unique constraint the following error message will appear if you violate the uniqueness:

ORA-00001: unique constraint … violated

The confusing part is probably the report by Oracle that a unique constraint has been violated. This message can report the constraint name (for unique and primary key constraints) or the index name (for unique indexes in the absence of a constraint). The first act a DBA would likely perform is a check of the DBA_CONSTRAINTS view to search for the reported constraint; since the index name is reported rather than the constraint name no such constraint exists. Hopefully the DBA would then go to DBA_INDEXES to search for a unique index that may have been created to enforce uniqueness, which is what this colleague did. Let’s go through three scenarios that may help explain why, when a unique index is created, the associated unique constraint is not.

Let’s start with the warhorse of the Oracle demo tables, EMP. We create the demo tables then add a unique index to EMP:


SQL> --
SQL> -- Make the empno column unique
SQL> --
SQL> -- Use an index
SQL> --
SQL> -- The expected constraint does not
SQL> -- get created by default
SQL> --
SQL>
SQL> create unique index emp_uq_empno on emp(empno);

Index created.

SQL>
SQL> select index_name, uniqueness
  2  from user_indexes;

INDEX_NAME                     UNIQUENES
------------------------------ ---------
EMP_UQ_EMPNO                   UNIQUE

SQL>
SQL> select owner, table_name, constraint_name, index_name
  2  from user_constraints;

OWNER                          TABLE_NAME                     CONSTRAINT_NAME                INDEX_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
BING                           EMP                            SYS_C007995

SQL>

The constraint reported is a NOT NULL constraint for the EMPNO column, it has nothing to do with the unique index. Notice that no UNIQUE constraint exists. So let’s now violate that unique index and see what Oracle reports:


SQL> --
SQL> -- Violate the uniqueness
SQL> --
SQL> -- Oracle reports a constraint violation
SQL> -- even though no actual constraint
SQL> -- exists
SQL> --
SQL>
SQL> insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
  2  values (7369, 'BORPO','FLERB',7844,sysdate-121, 900, 0, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
*
ERROR at line 1:
ORA-00001: unique constraint (BING.EMP_UQ_EMPNO) violated


SQL>

As was noted earlier Oracle reports that a constraint was violated yet reports the name for the unique index created against the EMP table. Let’s change this around a bit and create an actual UNIQUE constraint and see what happens:


SQL> --
SQL> -- Drop the index
SQL> --
SQL> -- Create a unique constraint now
SQL> --
SQL> -- Notice that the index IS created
SQL> --
SQL>
SQL> drop index emp_uq_empno;

Index dropped.

SQL>
SQL> alter table emp
  2  add constraint emp_uq unique(empno);

Table altered.

SQL>
SQL> select index_name, uniqueness
  2  from user_indexes;

INDEX_NAME                     UNIQUENES
------------------------------ ---------
EMP_UQ                         UNIQUE

SQL>
SQL> select owner, table_name, constraint_name, index_name
  2  from user_constraints;

OWNER                          TABLE_NAME                     CONSTRAINT_NAME                INDEX_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
BING                           EMP                            EMP_UQ                         EMP_UQ
BING                           EMP                            SYS_C007995

SQL>

Now we have both a UNIQUE constraint and a unique index. Violating this reports what we would expect to see from Oracle:


QL> --
SQL> -- Violate the uniqueness
SQL> --
SQL> -- Oracle reports a constraint violation
SQL> --
SQL>
SQL> insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
  2  values (7369, 'BORPO','FLERB',7844,sysdate-121, 900, 0, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
*
ERROR at line 1:
ORA-00001: unique constraint (BING.EMP_UQ) violated


SQL>

The error message reports the constraint name that was violated and, as shown, we do have a UNIQUE constraint by that name AND an associated UNIQUE index to go along with it. Oracle correctly assumes that since you want a UNIQUE constraint you also want a UNIQUE index. But it can’t assume that if you create a UNIQUE index you also want a UNIQUE constraint because you can build a UNIQUE constraint with a non-unique index. We drop the existing constraint and start again:


SQL> --
SQL> -- NOW create a unique constraint
SQL> -- that uses a non-unique index
SQL> --
SQL>
SQL> alter table emp drop constraint emp_uq;

Table altered.

SQL>
SQL> create index emp_empno on emp(empno);

Index created.

SQL>
SQL> alter table emp add constraint emp_uq
  2  unique (empno)
  3  using index emp_empno;

Table altered.

SQL>
SQL> select index_name, uniqueness
  2  from user_indexes;

INDEX_NAME                     UNIQUENES
------------------------------ ---------
EMP_EMPNO                      NONUNIQUE

SQL>
SQL> select owner, table_name, constraint_name, index_name
  2  from user_constraints;

OWNER                          TABLE_NAME                     CONSTRAINT_NAME                INDEX_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
BING                           EMP                            EMP_UQ                         EMP_EMPNO
BING                           EMP                            SYS_C007995

SQL>

Once we dropped the UNIQUE constraint the UNIQUE index created as part of that process was also dropped. Now the only index we have on EMP is the non-unique index on EMPNO we created before creating the constraint. Take note that we used the USING INDEX clause of the ADD CONSTRAINT action from ALTER TABLE, to tell Oracle that we have an index we want to use so don’t create the default index. Again let’s violate the uniqueness:


SQL> --
SQL> -- Violate the uniqueness
SQL> --
SQL> -- Oracle reports a constraint violation
SQL> --
SQL>
SQL> insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
  2  values (7369, 'BORPO','FLERB',7844,sysdate-121, 900, 0, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
*
ERROR at line 1:
ORA-00001: unique constraint (BING.EMP_UQ) violated


SQL>

Here is the interesting part of using a non-unique index to enforce a UNIQUE/PRIMARY KEY constraint: if you drop the constraint the index remains:


SQL> --
SQL> -- Drop the constraint
SQL> --
SQL> -- The index remains
SQL> --
SQL>
SQL> alter table emp drop constraint emp_uq;

Table altered.

SQL>
SQL> select index_name, uniqueness
  2  from user_indexes;

INDEX_NAME                     UNIQUENES
------------------------------ ---------
EMP_EMPNO                      NONUNIQUE

SQL>
SQL> select owner, table_name, constraint_name, index_name
  2  from user_constraints;

OWNER                          TABLE_NAME                     CONSTRAINT_NAME                INDEX_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
BING                           EMP                            SYS_C007998

SQL>

Since there are several ways to implement uniqueness in a column or set of columns in a table, one being to simply create a unique index on the key column or columns, it isn’t necessary to have an associated UNIQUE constraint in force. It may be confusing to not have such a constraint (and I prefer to have such a constraint in place to enforce uniqueness) but it’s not required. As to why Oracle doesn’t automatically create such a constraint when a unique index is created, well, I think it has to do with how that constraint can be created. We can allow Oracle to create the unique index, we can use a non-unique index that already exists, or we could use a separate, existing unique index to keep the number of indexes manageable.

Yes, Oracle could modify the CREATE INDEX statement yet again to include a CONSTRAINT clause but I think that would be one more thing to forget or misuse — not every index needs a constraint connected to it. And how would you police how that parameter is used? UNIQUE and PRIMARY KEY constraints would be logical to create for UNIQUE indexes; maybe the CONSTRAINT option would only be available for CREATE UNIQUE INDEX statements. Again, just because you have a UNIQUE index doesn’t mean you need to have a corresponding UNIQUE constraint.

That’s my story and I’m sticking with it.

Next Page »

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 675 other followers