November 2010

Since we published the RAC book, Oracle has released patchset 11.2.0.2. Amongst other things, this improved the RAC One Node option, exactly the way we expected.

How it was

A quick recap on the product as it was in 11.2.0.1: RAC One Node is part of Oracle Enterprise Edition, any other software editions are explicitly not allowed. Another restriction exists for 3^rd party Clusterware: it’s not allowed to use one. RAC One Node is a hybrid between full blown RAC and the active/passive cluster. The option uses Grid Infrastructure for cluster management and storage provisioning via ASM. The RAC One instance starts its life as a RAC database, limited to only one cluster node. It only ever runs on one node only, but that node can change. It is strongly recommended to create a service for that RAC database. Utilities such as raconeinit provide a text based command line interface to transform that database to a “RAC One Node”-instance. In the process, the administrator can elect which nodes should be allowed to run the instance. The “omotion” utilities allowed the DBA to move the RAC One Node instance from the current node to another one. Optionally a time threshold could be set after which all ongoing transactions were to move to the new node. This feature required TAF or FAN to be set up correctly. The raconestatus utility allowed you to view the status of your RAC One Node instances. Conversion to full RAC was made possible by the racone2rac utility.

If you were after a Data Guard setup you’d be disappointed: that wasn’t (and AFAIK still is not) supported.

So all in all, that seemed a little premature. A patch to be downloaded and applied, no Data Guard and a new set of utilities are not really user friendly. Plus, initially this patch was available for Linux only. But at least a MOS note (which I didn’t find until after having finished writing this!) exists, RAC One — Changes in 11.2.0.2 [ID 1232802.1]

Changes

Instead of having to apply patch 9004119 to your environment, RAC One Node is available “out of the box” with 11.2.0.2. Sadly, the Oracle RAC One Node manual has not been updated, and searches on Metalink reveal no new information. One interesting piece of information: the patch for RAC One Node is listed as “undocumented Oracle Server” section.

The creation of a RAC One Node instance has been greatly simplified-dbca has added support for it, both from the command line for silent installations as well as the interactive GUI. Consider these options for dbca.

$ dbca -help
dbca  [-silent | -progressOnly | -customCreate] {  }  |
 { [ [options] ] -responseFile   }
 [-continueOnNonFatalErrors ]
Please refer to the manual for details.
You can enter one of the following command:

Create a database by specifying the following parameters:
-createDatabase
 -templateName 
 [-cloneTemplate]
 -gdbName 
 [-RACOneNode
 -RACOneNodeServiceName  ]
 [-policyManaged | -adminManaged ]
 [-createServerPool ]
 [-force ]
 -serverPoolName 
 -[cardinality ]
 [-sid ]
...

With RAC One Node you will most likely end up with a policy managed database in the end, I can’t see how an admin managed database made sense.

The srvctl command line tool has been improved to deal with the RAC One node. The most important operations are to add, remove, config and status. The nice thing about dbca is that it actually registers the database in the OCR. Immediately after the installation, you see this status information:

$ srvctl status database -d rontest
Instance rontest_1 is running on node node2
Online relocation: INACTIVE

$ srvctl config database -d rontest
Database unique name: rontest
Database name:
Oracle home: /data/oracle/product/11.2.0.2
Oracle user: oracle
Spfile: + DATA/rontest/spfilerontest.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: rontest
Database instances:
Disk Groups: DATA
Mount point paths:
Services: rontestsrv
Type: RACOneNode
Online relocation timeout: 30
Instance name prefix: rontest
Candidate servers: node2,node3
Database is administrator managed

Note that the instance_name, although the instance is administrator managed, changed to $ORACLE_SID_1. Relocating works now with the srvctl relocate database command as in this example:

$ srvctl relocate database -d rontest -n node2

You’ll get feedback about this in the output of the “status” command:

$ srvctl status database -d rontest
Instance rontest_1 is running on node node2
Online relocation: ACTIVE
Source instance: rontest_1 on node2
Destination instance: rontest_2 on node3

After the command completed, check the status again:

srvctl status database -d rontest
Instance rontest_2 is running on node node2
Online relocation: INACTIVE

The important difference between an admin managed database and a policy managed database is that you are responsible for undo tablespaces. If you don’t create and configure undo tablespaces, the relocate command will fail:

$ srvctl relocate database –d rontest -n node3                       <
PRCD-1222 : Online relocation of database rontest failed but database was restored to its original state
PRCD-1129 : Failed to start instance rontest_2 for database rontest
PRCR-1064 : Failed to start resource ora.rontest.db on node node3
CRS-5017: The resource action "ora.rontest.db start" encountered the following error:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
Process ID: 1587
Session ID: 35 Serial number: 1

CRS-2674: Start of 'ora.rontest.db' on 'node3' failed

In this case, the database runs on the same node. Check the ORACLE_SID (rontest_2 in my case) and modify the initialisation parameter.

SQL> select tablespace_name from dba_data_files where tablespace_name like ‘%UNDO%’;

TABLESPACE_NAME
------------------------------
UNDOTBS1
UNDOTBS2

So the tablespace was there, but the initialisation parameter was wrong! Let’s correct this:

SQL> alter system set undo_tablespace='UNDOTBS1' sid='rontest_1';

System altered.

SQL> alter system set undo_tablespace='UNDOTBS2' sid='rontest_2';

System altered.

Now the relocate will succeed.

To wrap this article up, the srvctl convert database command will convert between single instance, RAC One Node and RAC databases.

In the previous note on local indexes I raised a couple of questions about the problems of different partitions holding different volumes of data, and supplied a script to build some sample data that produced the following values for blevel across the partitions of a list-partitioned table.

INDEX_NAME           PARTITION_NAME           BLEVEL LEAF_BLOCKS   NUM_ROWS
-------------------- -------------------- ---------- ----------- ----------
T1_ID                P0                            0           1          9
                     P1                            0           1         90
                     P2                            1           4        900
                     P3                            1          33       9000
                     P4                            1         339      90000
                     P5                            2        3384     900000

T1_N1                P0                            0           1          9
                     P1                            0           1         90
                     P2                            1           4        900
                     P3                            1          32       9000
                     P4                            1         314      90000
                     P5                            2        3136     900000

Q1: What do you think Oracle will record as the blevel at the global level for the two indexes ?
A1: As one of the commentators suggested, it seems to be the highest blevel recorded for any partition – in this case 2. (It’s possible that this assumption is wrong, of course, there may be some subtle weighting calculation involved – but I haven’t yet tested that hypothesis.)

Q2: If you have query with a where clause like “id between 100 and 400 and n1 != 5″ – which is designed very precisely to exclude the last, very big, partition – what value of blevel is Oracle going to use when considering the cost of using the index t1_id to access the data ?
A2: As I pointed out in an earlier note on list partitioned tables, Oracle doesn’t recognise the (obvious to the human eye) option for partition pruning in this predicate, so it uses the global blevel in the calculations.

The second answer is the one that is causing me a problem – because I have a client system where almost all the data is in a “dead” partiiton – it has a status, stored as the partition key in a list-partitioned table, of “COMPLETE”, and lots of their code includes the predicate: status != ‘COMPLETE’, but this can make the optimizer take the wrong execution path because it uses a global blevel that has been dictated by the huge volume of data that we know we don’t want to see.

The client queries are fairly complex, of course, but here’s a very trivial example demonstrating the basic problem (using the data generated by the code in the previous note – running under 11.1.0.6):

set autotrace traceonly explain

select	*
from	t1
where
	id = 99
and	n1 != 5
;

--------------------------------------------------------------------------------------------
| Id  | Operation                          | Name  | Rows  | Bytes | Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |       |     1 |   119 |    14 |       |       |
|   1 |  PARTITION LIST ALL                |       |     1 |   119 |    14 |     1 |     6 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T1    |     1 |   119 |    14 |     1 |     6 |
|*  3 |    INDEX RANGE SCAN                | T1_ID |     1 |       |    13 |     1 |     6 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("N1"<>5)
   3 - access("ID"=99)

From Oracle’s perspective it has to visit all six partitions because it can’t use the most apporpriate index and do partition pruning – and the final cost of this simple query is 14 because the value used (six times, in effect) for the blevel in the calculations is two; but we have inside information that tells us that this is essentially an unreasonable cost.

If Oracle were to believe that a more appropriate blevel for this query was just one then the cost would drop significantly (although in this case the plan wouldn’t change):

--------------------------------------------------------------------------------------------
| Id  | Operation                          | Name  | Rows  | Bytes | Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |       |     1 |   119 |     8 |       |       |
|   1 |  PARTITION LIST ALL                |       |     1 |   119 |     8 |     1 |     6 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T1    |     1 |   119 |     8 |     1 |     6 |
|*  3 |    INDEX RANGE SCAN                | T1_ID |     1 |       |     7 |     1 |     6 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("N1"<>5)
   3 - access("ID"=99)

Of course for a really big system, where the “dead” partition was 200 Million rows, we might have a blevel of three:

--------------------------------------------------------------------------------------------
| Id  | Operation                          | Name  | Rows  | Bytes | Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |       |     1 |   119 |    20 |       |       |
|   1 |  PARTITION LIST ALL                |       |     1 |   119 |    20 |     1 |     6 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T1    |     1 |   119 |    20 |     1 |     6 |
|*  3 |    INDEX RANGE SCAN                | T1_ID |     1 |       |    19 |     1 |     6 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("N1"<>5)
   3 - access("ID"=99)

Note how changing the global blevel by one makes the cost change by six – a consequence of the fact that we have six partitions with no pruning. If you’re having trouble with queries against partitioned table that don’t use the right index, take a close look at the data volumes and values recorded for blevel at the global, partition and subpartition levels – it’s possible that you’re suffering from a bias introduced by one partition being much larger than all the rest.

If you’re wondering how I got these plans (without simply editing them) it was by using dbms_stats.set_index_stats() to change the stored statistics – see “Copy Stats” for an example of the type of code needed. In cases like this, where I have better information about the data and the intent of the code than the optimizer has, I am perfectly happy to give a “more truthful” picture of the data to the optimizer by writing scripts to adjust statistics.

There are three drawbacks to such an approach, of course. First: on the next upgrade the optimizer might get smarter and make my clever little hack a liability rather than a benefit; secondly, there may be examples of application code that I haven’t noticed that might go wrong because of my hack; finally, and more importantly in the short term, I have to make sure that my code runs every time the statistics on the index are modified by any other program (such as the automatic stats collection job).

But the principle is sound – if we understand the system better than the optimizer then it’s positively important to help the optimizer in the most truthful way possible. List partitions (in a way similar to frequency histograms) are an obvious target for this type of treatment.

Exadata storage software uses the cellcli utility as its command line interface. Unfortunately, although the documentation set that comes with Exadata does have many examples of cellcli commands, and even a chapter dedicated to cellcli, it does not include any reference material on the syntax itself (particularly the LIST command). So I thought I would write up a few of the things I’ve learned while picking around at it. But first a little bit of a rant on why they wrote yet another command line interface.

They already had SQL*Plus for crying out loud. Why not just use that. SQL*Plus has all kinds of functionality for using variables, formatting output, etc… And on top of that, they invented a new syntax. Why use LIST instead of SELECT? They used WHERE and LIKE, so why not SELECT? I find it more than a little annoying (in case you couldn’t tell). I’m told that storage admins don’t like SQL and that’s one of the reasons for not using straight SQL syntax. That seems pretty silly since the storage is designed specifically for use with Oracle databases.

So anyway, here’s my quick top ten list of things you should know:

1. cellcli does have a handful of SQL*Plus commands (START (@), SET ECHO ON, SPOOL, DESCRIBE, HELP)
2. SELECT is replaced by LIST and it must be the first key word on the command line
3. There is no FROM keyword (the LIST keyword must be immediately followed by the ObjectType which is equivalent to a table name)
4. There is a DESCRIBE command which displays the attributes (columns) that make up an ObjectType (table)
5. Column names are specified with the ATTRIBUTES keyword followed by the columns you wish to be displayed
6. There is a default set of columns for each Object that will be returned if the ATTRIBUTES keyword is not specified
7. There is a WHERE clause that can be applied to any attribute and multiple conditions can be ANDed together (no OR though)
8. There is no ORDER BY equivalent
9. The DETAIL key word can be appended to any LIST command to change the output from column oriented to row oriented
10. The LIKE operator works but instead of the standard SQL wildcard, %, cellcli uses regex – so ‘%’ = ‘.*’

So here are a few examples:

CellCLI> help 
 
 HELP [topic]
   Available Topics:
        ALTER
        ALTER ALERTHISTORY
        ALTER CELL
        ALTER CELLDISK
        ALTER GRIDDISK
        ALTER IORMPLAN
        ALTER LUN
        ALTER THRESHOLD
        ASSIGN KEY
        CALIBRATE
        CREATE
        CREATE CELL
        CREATE CELLDISK
        CREATE FLASHCACHE
        CREATE GRIDDISK
        CREATE KEY
        CREATE THRESHOLD
        DESCRIBE
        DROP
        DROP ALERTHISTORY
        DROP CELL
        DROP CELLDISK
        DROP FLASHCACHE
        DROP GRIDDISK
        DROP THRESHOLD
        EXPORT CELLDISK
        IMPORT CELLDISK
        LIST
        LIST ACTIVEREQUEST
        LIST ALERTDEFINITION
        LIST ALERTHISTORY
        LIST CELL
        LIST CELLDISK
        LIST FLASHCACHE
        LIST FLASHCACHECONTENT
        LIST GRIDDISK
        LIST IORMPLAN
        LIST KEY
        LIST LUN
        LIST METRICCURRENT
        LIST METRICDEFINITION
        LIST METRICHISTORY
        LIST PHYSICALDISK
        LIST THRESHOLD
        SET
        SPOOL
        START
 
CellCLI> help set
 
  Usage: SET  
 
  Purpose: Sets a variable to alter the CELLCLI environment settings for your
           current session.
 
  Arguments:
    variable and value represent one of the following clauses:
    DATEFORMAT { STANDARD | LOCAL }
    ECHO { ON | OFF }
 
  Examples:
    set dateformat local
    set echo on 
 
 
CellCLI> help list
 
  Enter HELP LIST  for specific help syntax.
    :  {ACTIVEREQUEST | ALERTHISTORY | ALERTDEFINITION | CELL 
                     | CELLDISK | FLASHCACHE | FLASHCACHECONTENT | GRIDDISK
                     | IORMPLAN | KEY | LUN 
                     | METRICCURRENT | METRICDEFINITION | METRICHISTORY 
                     | PHYSICALDISK | THRESHOLD }
 
CellCLI> help list FLASHCACHECONTENT
 
  Usage: LIST FLASHCACHECONTENT [] [] [DETAIL] 
 
  Purpose: Displays specified attributes for flash cache entries.
 
  Arguments:
    :  An expression which determines the entries to be displayed.
    : The attributes that are to be displayed.
                      ATTRIBUTES {ALL | attr1 [, attr2]... }
 
  Options:
    [DETAIL]: Formats the display as an attribute on each line, with
              an attribute descriptor preceding each value.
 
  Examples:
    LIST FLASHCACHECONTENT DETAIL

So as you can see, the help system allows you to see a bit of the syntax for each command. You may also have noticed a couple of SQL*Plus carry-overs. SET, SPOOL, and START work pretty much as expected. Note the @ is equivalent to START and that the only things you can SET are ECHO and DATEFORMAT. Now for a couple of queries (er LIST commands):

CellCLI> desc flashcachecontent
         ^
CELL-01504: Invalid command syntax.
 
CellCLI> describe flashcachecontent
        cachedKeepSize
        cachedSize
        dbID
        dbUniqueName
        hitCount
        hoursToExpiration
        missCount
        objectNumber
        tableSpaceNumber
 
CellCLI> set echo on
 
CellCLI> @fc_content
 
> CellCLI> list flashcachecontent where dbUniqueName like 'EXDB' and hitcount > 100 attributes dbUniqueName, objectNumber, cachedKeepSize, cachedSize, hitCount, missCount
         EXDB    2       0       4194304         600     208
         EXDB    40      0       2424832         376     60
         EXDB    224     0       1802240         115     80
         EXDB    267     0       458752          128     9
         EXDB    383     0       2547712         157     27
         EXDB    423     0       1867776         180     41
         EXDB    471     0       4071424         552     85
         EXDB    472     0       1277952         114     22
         EXDB    474     0       13246464        286     326
         EXDB    475     0       5914624         519     124
         EXDB    503     0       5308416         669     455
         EXDB    5710    0       3735552         363     90
         EXDB    6207    0       393216          112     9
         EXDB    6213    0       3842048         359     147
         EXDB    6216    0       1245184         184     29
         EXDB    6373    0       3481600         222     61
         EXDB    56085   0       4194304         822     129
         EXDB    66849   0       438763520       1221    3322
         EXDB    71493   0       5636096         302     127
         EXDB    71497   0       1351680         320     22
         EXDB    71573   0       2760704         101     37
         EXDB    71775   0       1801412608      34994   46315
 
CellCLI> list flashcachecontent where dbUniqueName like 'EX.?.?' and hitcount > 100 -
 
> attributes dbUniqueName, objectNumber, cachedKeepSize, cachedSize 
         EXDB    2       0       4194304
         EXDB    18      0       1179648
         EXDB    37      0       622592
         EXDB    40      0       2424832
         EXDB    63      0       524288
         EXDB    104     0       688128
         EXDB    224     0       3407872
         EXDB    267     0       458752
         EXDB    383     0       2670592
         EXDB    420     0       1507328
         EXDB    423     0       1867776
         EXDB    424     0       720896
         EXDB    471     0       4071424
         EXDB    472     0       1277952
         EXDB    473     0       2351104
         EXDB    474     0       13574144
         EXDB    475     0       5521408
         EXDB    503     0       5308416
         EXDB    5702    0       262144
         EXDB    5709    0       2416640
         EXDB    5710    0       3735552
         EXDB    6207    0       393216
         EXDB    6210    0       131072
         EXDB    6213    0       4227072
         EXDB    6216    0       1245184
         EXDB    6373    0       3579904
         EXDB    56085   0       4194304
         EXDB    66849   0       438763520
         EXDB    71493   0       5636096
         EXDB    71497   0       1351680
         EXDB    71573   0       2801664
         EXDB    71775   0       1801412608
 
CellCLI> list flashcachecontent where dbUniqueName like 'EX.?.?' and hitcount > 100 and objectNumber like '.*775'
         2356637742      6       71775
 
CellCLI> list flashcachecontent where dbUniqueName like '.*X.?.?' and objectNumber like '.*775' detail                                      
         cachedKeepSize:         0
         cachedSize:             1801412608
         dbID:                   2356637742
         dbUniqueName:           EXDB
         hitCount:               34994
         missCount:              46315
         objectNumber:           71775
         tableSpaceNumber:       6
 
CellCLI> list flashcachecontent where dbUniqueName like 'EX.?.?' and hitcount > 100 and objectNumber like '.*775'
         2356637742      6       71775
 
CellCLI> list flashcachecontent attributes objectNumber, hitCount, missCount where dbUniqueName like 'EX.?.?' and hitcount > 100 and objectNumber like '.*775'
         71775   34994   46315

So DESC doesn’t work as an abbreviation of DESCRIBE. Notice that there are no headings for column oriented output. As you can see, you can run “scripts” and SET ECHO ON to display the commands in any scripts that you execute. One of the LIST commands was strung across two lines by using the continuation operator (-). The LIST commands look a lot like SQL except for LIST being used instead of SELECT and the regex expressions for matching when using the LIKE key word. Also notice that in the last command a number was matched with a regex expression implying a data type conversion, although all data may be treated at text. You can see that the ATTRIBUTES and WHERE key words can be anywhere on the command line after the “LIST objectName” keywords. In other words, these two key words are not positional, either one can be first. Finally, the DETAIL keyword turns the output sideways. Or as the help says, “Formats the display as an attribute on each line, with
an attribute descriptor preceding each value.”

So the cellcli interface is really not that bad, I just happen to like SQL*Plus better. ;) I do think it would have been a simple matter to reuse SQL*Plus since they already have all the functionality built into it, but maybe there were other concerns that I’m not aware of. But cellcli works. And by the way, cellcli has the ability to scroll though previous commands and edit them via the arrow keys which is quite handy. The editing capability is definitely a step forward from SQL*Plus on unix like platforms (although you can use rlwrap to accomplish this – see this post for more details on that: Using rlwrap on Windows) And regex also provides a very powerful pattern matching capability although it’s still a little confusing to have SQL like syntax mixed with regex to my way of thinking. Maybe if they just added the ability to use the % wildcard in addition to the regex I would feel better about it.

… (or “Fall” for speakers of American) has arrived in the UK – and once again I am reminded how gardening and trouble-shooting are just two aspects of the same problem.

I have several trees in and around my garden, including two rather large Oak trees, and at this time of year it takes a couple of hours at the weekend to rake up the fallen leaves. The comparison with solving performance problems is obvious:

Every Saturday, I look at the leaves on the ground and the leaves still on the trees and quite often manage to persuade myself that there’s no point in doing anything just yet.

On the Saturdays when I decide that I really do have to rake up the leaves I aim to clear about 99% of the problem – there’s no point in clearing to 100% because if I go for perfection it’s only going to last a couple of minutes before more leaves start coming down or blowing in. Of course, after I’ve spent ages clearing 99% of the mess, my wife (the end user) is quite likely to say: “you haven’t finished yet”

After I’ve done a really good job raking up enough leaves I look up at the trees and know that all those leaves are going to be heading my way and I’m probably going to have to do it all over again next week, and there’s nothing appropriate that I can do to stop it happening.