Nov 29 2009

Instance Caging

Tag: 11gR2, Resource Manager — Christian Antognini @ 4:06 pm

Instance caging is another small but useful feature of Oracle Database 11g Release 2. Thanks to it the database resource manager is able, for the first time, to limit the number of CPUs that can be used by a given instance. (By the way, note that this limit has no “impact” on the number of CPUs that have to be licensed.)

Using instance caging requires only two simple configurations from our part:

  • Enable the resource manager by assigning a resource plan (e.g. DEFAULT_PLAN)
  • Set the number of CPUs through the CPU_COUNT initialization parameter

To illustrate how it works, let me show you what I observed on my small test server with 4 cores.

To burn all the available CPU resources, I started four sessions executing the following PL/SQL block:

DECLARE
  n NUMBER;
BEGIN
  WHILE (TRUE)
  LOOP
    n:= dbms_random.random();
  END LOOP;
END;

With the four sessions up and running, the output of vmstat(8) looks like the following:

procs ------------memory------------ --swap-- ----io---- --system-- -----cpu-----
 r  b  swpd     free   buff    cache  si   so   bi    bo    in   cs   us sy id wa
 5  0     0  6037164  71568  5476620   0    0   66   198  1033  823  100  0  0  0
 5  0     0  6037164  71572  5476616   0    0    0   158  1021  801  100  0  0  0
 5  0     0  6037164  71572  5476616   0    0    0    48  1016  750  100  0  0  0

Notice that there is no idle time and that the number of processes waiting for run time is 5.
Now, to show what instance caging can do to limit the CPU utilization, I started the following PL/SQL block:

DECLARE
  l_sql VARCHAR2(100) := 'ALTER SYSTEM SET cpu_count = ';
BEGIN
  EXECUTE IMMEDIATE l_sql || '4';
  dbms_lock.sleep(10);
  EXECUTE IMMEDIATE l_sql || '3';
  dbms_lock.sleep(10);
  EXECUTE IMMEDIATE l_sql || '2';
  dbms_lock.sleep(10);
  EXECUTE IMMEDIATE l_sql || '1';
  dbms_lock.sleep(10);
  EXECUTE IMMEDIATE l_sql || '0';
END;

This time the output of vmstat(8) looks like the following:

procs ------------memory------------ --swap-- ----io---- --system-- -----cpu-----
 r  b  swpd     free   buff    cache  si   so   bi    bo    in   cs   us sy id wa
 5  0      0 3695176  80856  7780352   0    0    0    96  1019  783  100  0  0  0
 5  0      0 3695176  80856  7780352   0    0    0   170  1023  795  100  0  0  0
 5  0      0 3695184  80856  7780352   0    0    0   156  1028  795  100  0  0  0
 5  0      0 3695184  80860  7780348   0    0    0   120  1021  795  100  0  0  0
 5  0      0 3694928  80860  7780348   0    0    2   168  1029  810  100  0  0  0
 3  0      0 3694928  80860  7780348   0    0    0   214  1029  939   77  0 23  0
 3  0      0 3694928  80864  7780344   0    0    0   118  1020  961   75  0 25  0
 3  0      0 3694928  80864  7780344   0    0    2   152  1026  961   75  0 25  0
 3  0      0 3694596  80868  7780340   0    0    2   158  1027  975   75  0 25  0
 3  0      0 3694612  80868  7780340   0    0    0   142  1031  979   75  0 25  0
 3  0      0 3694612  80868  7780340   0    0    2   164  1024  963   75  0 25  0
 3  0      0 3694616  80872  7780336   0    0    0   358  1079  961   52  0 49  0
 2  0      0 3694616  80872  7780336   0    0    0   120  1021  909   50  0 50  0
 2  0      0 3697312  80872  7780336   0    0    0   162  1025  952   50  0 50  0
 2  0      0 3694744  80876  7780332   0    0    0   142  1027  948   50  0 50  0
 1  0      0 3694744  80876  7780332   0    0    0   120  1021  954   40  0 60  0
 1  0      0 3694748  80876  7780332   0    0    0   234  1034  953   26  0 74  0
 1  0      0 3694748  80876  7780332   0    0    0   134  1021  921   26  0 74  0
 1  0      0 3696484  80876  7780332   0    0    0   120  1020  954   26  0 74  0
 1  0      0 3696476  80880  7780328   0    0    2   196  1035  996   26  0 74  0
 5  0      0 3696476  80880  7780328   0    0    0   112  1020  643   96  0  4  0
 6  0      0 3696484  80880  7780328   0    0    0   216  1040  778  100  0  0  0
 5  0      0 3696484  80884  7780324   0    0    0   160  1021  763  100  0  0  0
 5  0      0 3696484  80884  7780324   0    0    0   112  1020  775  100  0  0  0
 5  0      0 3696468  80888  7780320   0    0    0   156  1026  785  100  0  0  0

Notice that not only the idle time but also the number of processes waiting for run time dynamically changes according to the value specified for the CPU_COUNT initialization parameter. Great!

The only problem I faced during my tests is that the management does not work as expected when lot of time is spent running kernel code. On my server I am able to reproduce such a problem by running the following PL/SQL block:

BEGIN
  WHILE (TRUE)
  LOOP
    FOR i IN (SELECT * FROM t)
    LOOP
      NULL;
    END LOOP;
  END LOOP;
END;

When four sessions run that code, the output of vmstat(8) looks like the following:

procs ------------memory------------ --swap-- ----io---- --system-- -----cpu-----
 r  b  swpd     free   buff    cache  si   so   bi    bo    in   cs   us sy id wa
 4  0     0  3690112  81032  7780652   0    0    0   138  1025  786   65 35  0  0
 4  0     0  3690112  81036  7780648   0    0    0   182  1024  775   66 34  0  0
 4  0     0  3690112  81036  7780648   0    0    0    84  1018  779   66 34  0  0
 4  0     0  3690112  81036  7780648   0    0    0   138  1024  822   66 34  0  0
 4  0     0  3690112  81040  7780644   0    0    0   204  1032  809   66 34  0  0
 4  0     0  3690112  81044  7780640   0    0    0   120  1027  798   63 36  1  0
 4  0     0  3690112  81044  7780640   0    0    0   146  1021  833   61 37  2  0
 4  0     0  3690112  81044  7780640   0    0    0   160  1023  785   63 35  1  0
 5  0     0  3690112  81044  7780640   0    0    0    98  1021  782   62 36  1  0
 4  0     0  3690112  81044  7780640   0    0    0   146  1021  818   64 36  1  0
 4  0     0  3690048  81044  7780640   0    0    2   180  1031  803   63 36  1  0
 3  0     0  3690048  81044  7780640   0    0    0   126  1027  921   53 29 18  0
 3  0     0  3690048  81044  7780640   0    0    0   146  1022  988   46 25 29  0
 3  0     0  3690044  81048  7780636   0    0    0   180  1032  976   48 25 28  0
 3  0     0  3690044  81048  7780636   0    0    0   212  1058  974   48 25 27  0
 2  0     0  3690052  81048  7780636   0    0    0   144  1028  950   47 23 30  0
 2  0     0  3690052  81048  7780636   0    0    0   204  1035  971   34 14 51  0
 2  0     0  3691404  81048  7780636   0    0    0   106  1020  939   32 13 55  0
 2  0     0  3691404  81048  7780636   0    0    0   124  1021  972   34 14 53  0
 2  0     0  3691404  81048  7780636   0    0    0   202  1032  986   33 14 53  0
 4  0     0  3691404  81048  7780636   0    0    0    80  1024  850   51 29 21  0
 4  0     0  3691404  81048  7780636   0    0    0   172  1028  790   65 35  0  0
 4  0     0  3691404  81048  7780636   0    0    0   220  1033  785   63 37  0  0
 5  0     0  3691404  81048  7780636   0    0    0    62  1016  771   64 36  0  0
 5  0     0  3691404  81048  7780636   0    0    0   170  1022  785   62 38  0  0
 5  0     0  3691404  81048  7780636   0    0    0   18   1026  772   64 36  0  0

Notice that in this case about one third of the time is spent running kernel code. As a result, the CPU utilization (for the values of 3, 2 and 1) is always higher than expected.

Nov 25 2009

Zero-Size Unusable Indexes and the Query Optimizer

Tag: 11gR2, Indexes, Partitioning, Query Optimizer — Christian Antognini @ 8:25 pm

Zero-size unusable indexes and index partions is a small but useful feature of Oracle Database 11g Release 2. Simply put, its aim is to save space in the database by immediately releasing the segment associated to unusable indexes or index partitions. To illustrate this, let’s have a look to an example…

  • Create a partitioned table, insert data, create a local index and gather object statistics:

SQL> CREATE TABLE t (
  2    id NUMBER NOT NULL,
  3    d DATE NOT NULL,
  4    n NUMBER NOT NULL,
  5    pad VARCHAR2(4000) NOT NULL
  6  )
  7  PARTITION BY RANGE (d) (
  8    PARTITION t_jan_2009 VALUES LESS THAN (to_date('2009-02-01','yyyy-mm-dd')),
  9    PARTITION t_feb_2009 VALUES LESS THAN (to_date('2009-03-01','yyyy-mm-dd')),
 10    PARTITION t_mar_2009 VALUES LESS THAN (to_date('2009-04-01','yyyy-mm-dd')),
 11    PARTITION t_apr_2009 VALUES LESS THAN (to_date('2009-05-01','yyyy-mm-dd')),
 12    PARTITION t_may_2009 VALUES LESS THAN (to_date('2009-06-01','yyyy-mm-dd')),
 13    PARTITION t_jun_2009 VALUES LESS THAN (to_date('2009-07-01','yyyy-mm-dd')),
 14    PARTITION t_jul_2009 VALUES LESS THAN (to_date('2009-08-01','yyyy-mm-dd')),
 15    PARTITION t_aug_2009 VALUES LESS THAN (to_date('2009-09-01','yyyy-mm-dd')),
 16    PARTITION t_sep_2009 VALUES LESS THAN (to_date('2009-10-01','yyyy-mm-dd')),
 17    PARTITION t_oct_2009 VALUES LESS THAN (to_date('2009-11-01','yyyy-mm-dd')),
 18    PARTITION t_nov_2009 VALUES LESS THAN (to_date('2009-12-01','yyyy-mm-dd')),
 19    PARTITION t_dec_2009 VALUES LESS THAN (to_date('2010-01-01','yyyy-mm-dd'))
 20  );

SQL> INSERT INTO t
  2  SELECT rownum, to_date('2009-01-01','yyyy-mm-dd')+rownum/274, mod(rownum,11), rpad('*',100,'*')
  3  FROM dual
  4  CONNECT BY level <= 100000;

SQL> CREATE INDEX i ON t (d) LOCAL;

SQL> execute dbms_stats.gather_table_stats(user,'T')

  • Make all partitions but the last one unusable:

SQL> ALTER INDEX i MODIFY PARTITION t_jan_2009 UNUSABLE;

SQL> ALTER INDEX i MODIFY PARTITION t_feb_2009 UNUSABLE;

SQL> ALTER INDEX i MODIFY PARTITION t_mar_2009 UNUSABLE;

SQL> ALTER INDEX i MODIFY PARTITION t_apr_2009 UNUSABLE;

SQL> ALTER INDEX i MODIFY PARTITION t_may_2009 UNUSABLE;

SQL> ALTER INDEX i MODIFY PARTITION t_jun_2009 UNUSABLE;

SQL> ALTER INDEX i MODIFY PARTITION t_jul_2009 UNUSABLE;

SQL> ALTER INDEX i MODIFY PARTITION t_aug_2009 UNUSABLE;

SQL> ALTER INDEX i MODIFY PARTITION t_sep_2009 UNUSABLE;

SQL> ALTER INDEX i MODIFY PARTITION t_oct_2009 UNUSABLE;

SQL> ALTER INDEX i MODIFY PARTITION t_nov_2009 UNUSABLE;

  • Check whether the segments associated to the unusable partitions still exist:

SQL> SELECT partition_name, bytes
  2  FROM user_segments
  3  WHERE segment_name = 'I'
  4  AND segment_type = 'INDEX PARTITION'
  5  ORDER BY partition_name;

PARTITION_NAME       BYTES
--------------- ----------
T_DEC_2009          262144

As you can see from the output of the last query, only the segment associated to the partition T_DEC_2009 exists. All other segments have been freed. (Note that up to Oracle Database 11g Release 1 all segments would still exist.)

This is nice but, in my opinion, there is a more important thing to consider…
What does the query optimizer do when it has to generate the execution plan for a query that reads data stored into a table having unusable index partitions? For example, does the query optimizer take advantage of the usable partitions to apply a restriction? If yes, what happens when both partitions having usable and unusable index partitions have to be accessed? Let’s have a look to some examples…

  • First, let’s check whether the usable index partition can be used to apply a restriction:

SQL> SELECT count(d)
  2  FROM t
  3  WHERE d BETWEEN to_date('2009-12-01 23:00:00','yyyy-mm-dd hh24:mi:ss')
  4              AND to_date('2009-12-02 01:00:00','yyyy-mm-dd hh24:mi:ss');

SQL> SELECT * FROM table(dbms_xplan.display_cursor(format=>'basic +partition'));

--------------------------------------------------------
| Id  | Operation               | Name | Pstart| Pstop |
--------------------------------------------------------
|   0 | SELECT STATEMENT        |      |       |       |
|   1 |  SORT AGGREGATE         |      |       |       |
|   2 |   PARTITION RANGE SINGLE|      |    12 |    12 |
|   3 |    INDEX RANGE SCAN     | I    |    12 |    12 |
--------------------------------------------------------

Nice, an index range scan can be performed.


  • Second, let’s check what happen when an unusable index partition would be accessed:

SQL> SELECT count(d)
  2  FROM t
  3  WHERE d BETWEEN to_date('2009-11-01 23:00:00','yyyy-mm-dd hh24:mi:ss')
  4              AND to_date('2009-11-02 01:00:00','yyyy-mm-dd hh24:mi:ss');

SQL> SELECT * FROM table(dbms_xplan.display_cursor(format=>'basic +partition'));

--------------------------------------------------------
| Id  | Operation               | Name | Pstart| Pstop |
--------------------------------------------------------
|   0 | SELECT STATEMENT        |      |       |       |
|   1 |  SORT AGGREGATE         |      |       |       |
|   2 |   PARTITION RANGE SINGLE|      |    11 |    11 |
|   3 |    TABLE ACCESS FULL    | T    |    11 |    11 |
--------------------------------------------------------

Obviously, a partition scan is performed. Note that this is only true if the initialization parameter SKIP_UNUSABLE_INDEXES is set to TRUE (this is the default). Otherwise an ORA-01502 would be generated.


  • Third, let’s check what happens when both usable and unusable index partitions would be accessed:

SQL> SELECT count(d)
  2  FROM t
  3  WHERE d BETWEEN to_date('2009-11-30 23:00:00','yyyy-mm-dd hh24:mi:ss')
  4              AND to_date('2009-12-01 01:00:00','yyyy-mm-dd hh24:mi:ss');

SQL> SELECT * FROM table(dbms_xplan.display_cursor(format=>'basic +partition'));

-------------------------------------------------------------
| Id  | Operation                 | Name    | Pstart| Pstop |
-------------------------------------------------------------
|   0 | SELECT STATEMENT          |         |       |       |
|   1 |  SORT AGGREGATE           |         |       |       |
|   2 |   VIEW                    | VW_TE_2 |       |       |
|   3 |    UNION-ALL              |         |       |       |
|   4 |     PARTITION RANGE SINGLE|         |    12 |    12 |
|   5 |      INDEX RANGE SCAN     | I       |    12 |    12 |
|   6 |     PARTITION RANGE SINGLE|         |    11 |    11 |
|   7 |      TABLE ACCESS FULL    | T       |    11 |    11 |
-------------------------------------------------------------

Good stuff! The query optimizer generates an execution plan containing a UNION ALL to takes advantage of the usable index partition. This is really interesting because it allows us to selectively remove unnecessary index partitions. For example, in case some indexes are only used for the “current” partition(s), the index partitions of the older ones could be set unusable. As a result, lot of space might be released.

Be careful, however, that the flexibility of the query optimizer has (still?) some limits. For example, in the following query the unusable partition is the one in the “middle”. In such a case, I was not able to let the query optimizer apply the optimization described above.

SQL> ALTER INDEX i REBUILD PARTITION t_oct_2009;

SQL> SELECT count(d)
  2  FROM t
  3  WHERE d BETWEEN to_date('2009-10-30 23:00:00','yyyy-mm-dd hh24:mi:ss')
  4              AND to_date('2009-12-01 01:00:00','yyyy-mm-dd hh24:mi:ss');

SQL> SELECT * FROM table(dbms_xplan.display_cursor(format=>'basic +partition'));

----------------------------------------------------------
| Id  | Operation                 | Name | Pstart| Pstop |
----------------------------------------------------------
|   0 | SELECT STATEMENT          |      |       |       |
|   1 |  SORT AGGREGATE           |      |       |       |
|   2 |   PARTITION RANGE ITERATOR|      |    10 |    12 |
|   3 |    TABLE ACCESS FULL      | T    |    10 |    12 |
----------------------------------------------------------