Using an Index to Shortcut a MIN/MAX Aggregate – Part 2

I’ve previously commented about the Index Scan MIN/MAX optimisation. My description of this operation from that post is below:

the optimiser can take a shortcut in satisfying a MIN or MAX aggregate by plucking the first or last value from the index, thus avoiding scanning the whole segment

I’ve been meaning to revisit and define this a bit more clearly. The target is not necessarily the first or last value in the index, the optimiser can use a similar operation to pluck a value from the middle of a composite index by using an “INDEX RANGE SCAN (MIN/MAX)” rather than “INDEX (FULL SCAN (MIN/MAX))”. An example is below:

Create a test table.

CREATE TABLE tab1 AS
SELECT MOD(ROWNUM,5) group_id
, ROWNUM id
FROM dual
CONNECT BY LEVEL <= 20000;

SELECT MIN(group_id), MAX(group_id) FROM tab1;

MIN(GROUP_ID) MAX(GROUP_ID)
------------- -------------
            0             4

CREATE INDEX tab1_i ON tab1 (group_id,id);

The index is on GROUP and ID so the query below using GROUP_ID “0″ will be doing as I previously described – plucking the first value from the index (note: I executed the queries below previously to first charge the shared pool).

set autotrace on
SELECT MIN(id) FROM tab1 WHERE group_id = 0;

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |    26 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |        |     1 |    26 |            |          |
|   2 |   FIRST ROW                  |        |  4000 |   101K|     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| TAB1_I |  4000 |   101K|     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          2  consistent gets

And now we query using GROUP_ID “3″. This is going to find its target value somewhere near the middle of the index.

set autotrace on
SELECT MIN(id) FROM tab1 WHERE group_id = 3;

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |    26 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |        |     1 |    26 |            |          |
|   2 |   FIRST ROW                  |        |  4000 |   101K|     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| TAB1_I |  4000 |   101K|     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          2  consistent gets

2 consistent gets again. The operation causes us to navigate the index tree to the start of GROUP_ID 3 and at that point we can stop as we have the minimum value of ID right there.

Converting Control Files to be Oracle Managed Files

No bombshells in this post – just a little task I’d not come across before and a complaint at the end.

On a test 10.2.0.4 database at home I have 2 control files, one in the DB_CREATE_FILE_DEST and one in the DB_RECOVERY_FILE_DEST as can be seen below:

NAME
--------------------------------------------------------------------
/u01/oradata/ORCL1/controlfile/o1_mf_5h1bxxg0_.ctl
/u01/app/oracle/flash_recovery_area/ORCL1/controlfile/o1_mf_5h1bxy2m_.ctl

During a bit of testing too mundane to go into I ended up with non-Oracle managed control files:

NAME
--------------------------------------------------------------------
/u01/oradata/orcl1/ORCL1_1.ctl
/u01/oradata/orcl1/ORCL1_2.ctl

After my unproductive testing I then wanted to put the control files back as they were. This was not something I’d considered before and it wasn’t immediately obvious how to go about it. The steps are below for my future reference (and perhaps yours).

Blank out the CONTROL_FILES parameter in the SPFILE and then restart the instance.

SYS @ orcl1 >alter system reset control_files scope=spfile sid='*';

System altered.

SYS @ orcl1 >startup force nomount
ORACLE instance started.

The CONTROL_FILES parameter appears to be set to a location in the Oracle home. This confused me at first but just turn a blind eye to it for now.

SYS @ orcl1 >show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/cntrlorcl1.dbf

If you restore the control files from an RMAN session the resulting files find their way back to being managed by Oracle.

RMAN> RESTORE CONTROLFILE FROM '/u01/oradata/orcl1/ORCL1_2.ctl';

Starting restore at 05-NOV-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=/u01/oradata/ORCL1/controlfile/o1_mf_5h6ml6to_.ctl
output filename=/u01/app/oracle/flash_recovery_area/ORCL1/controlfile/o1_mf_5h6ml74f_.ctl
Finished restore at 05-NOV-09

Nice.

Complaint time:

DB_RECOVERY_FILE_DEST. A little thing I like is the naming of this parameter. Perfect – it accurately describes the sort of files you’ll find in there. A little thing that I don’t like is that it’s Sunday best name is “Flash Recovery Area”. In which meeting did that seem like a good idea! Oh – that will be the marketing one.

CLUSTER_DATABASE=FALSE

I recently encountered a procedure that stated:

Set CLUSTER_DATABASE=FALSE
Set CLUSTER_DATABASE_INSTANCES=1

At that point I stopped for a second to wonder if the second parameter was required… It seems logical that if the database is not in a cluster then you wouldn’t need to tell Oracle how many database are in the cluster…

I didn’t have opportunity to test it at the time, but a SEV 1 a few days later put me in a position of attempting to start a RAC database in single instance mode and give me the perfect chance to find out…

The database in question was running on 9.2.0.8, but showed the same behaviour as the output below (generated on version 11.1.0.7):

19:05:33 SYS@erac1> show parameter cluster

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
cluster_database		     boolean	 TRUE
cluster_database_instances	     integer	 2
cluster_interconnects		     string
19:05:42 SYS@erac1> alter system set cluster_database = false scope = spfile;

System altered.

Elapsed: 00:00:00.12
19:06:03 SYS@erac1> startup force
ORACLE instance started.

Total System Global Area  509411328 bytes
Fixed Size		    2161152 bytes
Variable Size		  322962944 bytes
Database Buffers	  180355072 bytes
Redo Buffers		    3932160 bytes
Database mounted.
Database opened.
19:08:44 SYS@erac1> show parameter cluster

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
cluster_database		     boolean	 FALSE
cluster_database_instances	     integer	 1
cluster_interconnects		     string
19:09:00 SYS@erac1>

I’d learnt something new and as the rest of the team was surprised by the behaviour it seems something worth sharing… I’m all for removing redundant commands.

Oracle OpenWorld – First Keynote

At the end of a busy first day at Oracle OpenWorld 2009 I took myself along to the first keynote presentation “Extreme Innovation”. I wasn’t sure if it would be worth going to what is clearly focused on marketing and customer relations rather than technical education, but it was…

The number of people, size of the screens and general buzz in the room was something worth experiencing and that’s before anyone even started talking. You can watch highlights on the Oracle site. It is hard to not marvel at how Larry Ellison has grown Oracle and for all the chatter to the contrary it does appear that Oracle a full committed to SPARC, Solaris, Java and MySQL.

Spoofing V$SESSION.OSUSER

In order to audit users activity in a database we need the ability to uniquely identify every individual user. The easiest way of course is to create every user and individual user account and use it for auditing purposes. But what if for whatever reason using individual database user accounts is difficult to implement, or impractical ? If we can’t use unique database accounts maybe a combination of a (shared) database account and a unique operating system user ID could be a way to go ?

The information about operating system account can be found in V$SESSION view column OSUSER, and when auditing is enabled it is written to AUD$ table, and can be viewed via related views. It is important to understand that it’s a client side operating system ID and database relies on the client software to provide it.

If we connect using SQLPLUS and database user “HR” from the OS user “pawel”, and query V$SESSION, we can see that OS user “pawel” connected to the database as user “HR”:

SQL> select username,osuser,program from v$session where username='HR';

USERNAME   OSUSER           PROGRAM
---------- ---------------- ----------------------------------------
HR         pawel            sqlplus@arantorga (TNS V1-V3)

SQL>

It looks OK because the client software provided the database with a correct information about OS user ID. It is possible to write a client software that will send false OS user ID to the database instead of a real one, and it isn’t even difficult. It took me less them 5 minutes and Google to find how to do it, and it doesn’t require a lot of programming skills. I used Oracle JDBC driver (11.2.0.1) and Sun JDK6 U16 to write a small piece of code that connects to the database (10.2.0.4) with false OSUSER. The application doesn’t really do anything except connecting to the specified database and waiting for user input before terminating the connection:

pawel:~/workspace/java_sandbox/src$id
uid=1000(pawel) gid=1000(pawel) groups=122(kvm),1000(pawel)
pawel:~/workspace/java_sandbox/src$
pawel:~/workspace/java_sandbox/src$java -cp ../../java_test/lib/ojdbc6.jar:. OraAccess
Your current operating system username is: pawel
Enter database server IP and port number (IP:port):  192.168.122.21:1521
Enter database name:  sample
Enter database username:  hr
Enter password for user "hr":  hr
Enter fake osuser name:  false_osuser
Enter anything to disconnect: 

Once the connection is established we can check V$SESSION view again:

SQL> select username,osuser,program from v$session where username='HR';

USERNAME   OSUSER           PROGRAM
---------- ---------------- ----------------------------------------
HR         pawel            sqlplus@arantorga (TNS V1-V3)
HR         false_osuser     UserSpoofTest

SQL>

I was able to set not only OSUSER column but also PROGRAM and a few other columns from V$SESSION view can be set in exactly the same way.

I can’t give a definite answer if using OSUSER for auditing is acceptable or not because it probably depends on more then one thing. However spoofing OSUSER isn’t exactly difficult, and if security is important to you, individual database accounts should be your first choice, even if it’s not easy it implement.

11gR2 Installed and Looking Good

Just a quick post to express my excitement about having 11gR2 up and running on a 8 disk ASM configuration including an ACFS mount point… Some people play with their Wii, I’m happy with an Oracle database and *nix OS!

Is that so wrong?

I particularly like the new installer, but then again I’ve only just started by 11gR2 experience…

Grid Control with ASM

A colleague recently hit a problem configuring Grid Control with a repository database that uses ASM.

You do not have sufficient privilege to write to the specified patch for tablespaces

He checked out MetaLink or, as I should say, “My Oracle Support” and found note 738445.1

… Now, I don’t know if I’m misunderstanding what it is suggesting, but an alternative solution that I have used previously is to specify a file system location for both the management tablespaces, then move them to ASM using RMAN after they have been created.

In summary:

1. Allocate sufficient temporary storage (referred to as the “staging” area) for the Grid Control specific tablespaces (2GB should be more than enough judging by the size of these tablespaces in my current installation)
2. Install Grid Control (I followed Jeff Hunter’s excellent instructions) specifying a location on the “staging” filesystem for the Grid Control tablespaces
3. Post installation, shutdown the OMS using (assuming ORACLE_HOME set to OMS home):

$ $ORACLE_HOME/opmn/bin/opmnctl stopall

4. Use RMAN to backup “as copy” the relevant tablespaces (MGMT_TABLESPACE and MGMT_ECM_DEPOT_TS) with format ‘+DATA’ (or other disk group as applicable), i.e.:

RMAN> backup as copy tablespace MGMT_TABLESPACE format '+DATA';
RMAN> backup as copy tablespace MGMT_ECM_DEPOT_TS format '+DATA';

5. Take relevant tablespaces offline

RMAN> sql 'alter tablespace MGMT_TABLESPACE offline';
RMAN> sql 'alter tablespace MGMT_ECM_DEPOT_TS offline';

6. Switch tablespaces to copy using RMAN

RMAN> switch tablespace MGMT_TABLESPACE to copy;
RMAN> switch tablespace MGMT_ECM_DEPOT_TS to copy;

7. Recover tablespaces

RMAN> recover tablespace MGMT_TABLESPACE;
RMAN> recover tablespace MGMT_ECM_DEPOT_TS;

8. Bring tablespaces back online

RMAN> sql 'alter tablespace MGMT_TABLESPACE online';
RMAN> sql 'alter tablespace MGMT_ECM_DEPOT_TS online';

9. Start the OMS processes (assuming ORACLE_HOME set to OMS home)

$ $ORACLE_HOME/opmn/bin/opmnctl startall

Now that really has got to be easier than (only edited highlights because repoducing MetaLink/My Oracle Support Documentation is not going to win me friends at Oracle:

Do not install grid console on the local machine on which ASM instance exist.

Then

After successful installation of grid control console, install additional OMS on local machine where the ASM is installed.

Maybe I missed something that is RAC specific, but the note does state:

Same problem occurs for

+ Multiple node RAC Database.
+ Single node RAC Database.
+ Single instance Database.

With RAC the logical option would be to use a clustered filesystem as the tablespace staging area, but I can’t see why dropping to a single node RAC configuration in order to get Grid Control setup and then moving back to multi-node would not work…

Anyone out there with other experiences of this?

Microsoft Shortcuts

I few weeks ago a colleague showed me the shortcut I’ve been hoping to find for many years:

Application: Microsoft Word
Context: Select text
Shortcut: shift + f3

… Watch the selected text cycle through uppercase, lowercase and init caps.

The conversation moved on to other useful shortcuts…

Application: Microsoft Excel
Context: Focus in cell
Shortcut: ctrl + ‘

… Copy the value from the cell above.

The final one that pleased a few colleagues is the fast way to lock your computer:

Windows + L

I hope these prove useful to readers.

When to Gather Fixed Object Optimiser Statistics

Lets start with some advice from Metalink note 272479.1: Gathering Statistics For All fixed Objects In The Data Dictionary.

With Oracle Database 10G it is now recommended that you analyze the data dictionary.
The data dictionary has many fixed tables, such as X$ tables and collecting statistics
for these objects is suggested by Oracle. The GATHER_FIXED_OBJECTS_STATS
procedure gathers statistics for all fixed objects (dynamic performance tables) in the
data dictionary.

So we know we should do it – but when? Advice from the Oracle documentation this time.

Statistics on fixed objects, such as the dynamic performance tables, need to be
manually collected using GATHER_FIXED_OBJECTS_STATS procedure. Fixed objects record
current database activity; statistics gathering should be done when database has
representative activity.

I have an example below of how important the timing of this “representative activity” can be. Here is a simple query – the likes of which are often run by monitoring tools such as Oracle Grid Control – executed on a database that has had statistics for all fixed objects gathered previously.

select count(*) from v$rman_backup_job_details;

  COUNT(*)
----------
        23

Elapsed: 00:01:43.39

Nearly 2 minutes for a 23 row result set. The execution plan is a bit long to post here but below is a snippet that set some gentle alarm bells ringing.

------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Rows  |
------------------------------------------------------------------------------
... snip ...
|* 11 |            HASH JOIN OUTER          |                        |     1 |
|  12 |             MERGE JOIN CARTESIAN    |                        |     1 |
|  13 |              MERGE JOIN CARTESIAN   |                        |     1 |
|  14 |               FIXED TABLE FULL      | X$KCCRSR               |     1 |
... snip ...
|* 36 |           HASH JOIN OUTER           |                        |     1 |
|  37 |            MERGE JOIN CARTESIAN     |                        |     1 |
|  38 |             MERGE JOIN CARTESIAN    |                        |     1 |
|  39 |              FIXED TABLE FULL       | X$KCCRSR               |     1 |
... snip ...
------------------------------------------------------------------------------

A quick glance at the optimiser statistics for the X$KCCRSR table tells us some interesting information.

select rowcnt, blkcnt, analyzetime, samplesize
from
 tab_stats$ where obj#=(select OBJECT_ID from V$FIXED_TABLE where name = 'X$KCCRSR');

    ROWCNT     BLKCNT ANALYZETI SAMPLESIZE
---------- ---------- --------- ----------
         0          0 26-APR-09          0

Zero rows. And after gathering fixed object statistics…

exec dbms_stats.gather_fixed_objects_stats;

    ROWCNT     BLKCNT ANALYZETI SAMPLESIZE
---------- ---------- --------- ----------
       225          0 21-JUL-09        225

select count(*) from v$rman_backup_job_details;

  COUNT(*)
----------
        23

Elapsed: 00:00:00.19

The optimiser now knows there are 225 rows in X$KCCRSR and returns it’s result set in under a second. Now I’m not suggesting anyone regularly schedule a call to GATHER_FIXED_OBJECTS_STATS just consider the timing of this call a bit. Perhaps after your first couple of RMAN backups may do the trick

As an interesting aside I stumbled across the following method to view when statistics have been gathered.

select operation,start_time from DBA_OPTSTAT_OPERATIONS order by start_time;

OPERATION                             START_TIME
------------------------------------- -------------------------------------
gather_database_stats(auto)           17-JUL-09 10.00.01.560328 PM -03:00
gather_database_stats(auto)           18-JUL-09 06.00.03.189531 AM -03:00
gather_database_stats(auto)           20-JUL-09 10.00.01.363156 PM -03:00
gather_fixed_objects_stats            21-JUL-09 09.52.59.646815 AM -03:00

I’ve just noticed there are no system statistics in there – that’s an investigation for another day.

Purging Audit Data

When looking to implement something to purge the audit data after 90 days from some Oracle databases I found a nice blog post from Alex Gorbachev. The post provides a procedure for keeping X days of records in SYS.AUD$ and deleting anything older. It also provides an example for submitting a scheduled job via DBMS_SCHEDULER so that the purging of the data can be set up, and hopefully largely forgotten about.

I wanted to take this a little further and create a user specifically for the purpose of purging the SYS.AUD$ and most importantly I wanted the user to be locked down as much as possible. Here is what I came up with…

As SYSTEM:

create user purge_aud identified by {appropriately complex password};
grant create session, create job, create procedure to purge_aud;


As SYS:

grant delete on sys.aud$ to purge_aud;
grant execute on sys.dbms_system to purge_aud;


I feel that I should explain why I didn’t just run all the above as SYS. Well, the logic is that if I have the parameter AUDIT_SYS_OPERATIONS set to TRUE, I believe is is wise to do this, then every command that is run as SYS will be logged in an audit file in AUDIT_FILE_DEST. I don’t want to create more audit data than I need to, so therefore try to limit the commands that I run as SYS. In this case the creation of a user and the granting of system privileges can be handled by SYSTEM – I can also see value in either having a user specifically to create new users or having individual accounts for the DBAs so that they can create users using an account that should only ever be used by a named individual.

Maybe I don’t need to point this out, but performing the object level grants as a user other than SYS would require me giving the same privilege I want to grant to PURGE_AUD to another user with the “WITH GRANT” option, which to me is not appropriate and doesn’t actually help. I would say that as a general rule I want the object owner to be the user granting the privileges on that object.

As I final note on this section, I have not named my user “PURGE_AUD” in reality on the basis that it would feel a little too obvious what the purpose of the user is and would potentially make it a target for the bad guys/girls!

The next step was to create the PURGE_AUDIT_TRAIL procedure in the schema of the PURGE_AUD user. I won’t repoduce Alex’s code here, but note that I used fully qualified object names, i.e. sys.aud$ and sys.dbms_system. Once the procedure was created I then scheduled the job as specified by Alex, but with the addition of “SYS.” before the schedule_name parameter and using 90 as the parameter value for PURGE_AUDIT_TRAIL. There would also be the option of creating synonyms, but I prefer to keep it simple and to me more objects in increased complexity.

At this point we have a user with pretty tight privileges and a scheduled job to run each day and clear out any audit data older than 90 days. I wanted to lock this user right down and remove any privileges that were no longer needed…

As SYSTEM:

alter user purge_aud account lock;
revoke create session, create job, create procedure from purge_aud;


The above highlights something that I have the impression is often overlooked: A user might need a certain set of privileges when being “setup”, but it is highly likely that the user will not need that same set of privileges for its entire life.

The scheduled job is not impeded by revoking any of the above privileges and the job will continue to run each day in the “maintenance window”. In the unlikely event that anything needs to be changed it will simply be a case of granting the required privilege(s) to PURGE_AUD, making the change and then revoke the privilege(s) previously granted specifically for the change… A little extra work I admit, but I’d say a fair price for improved security.

It has been commented that auditors might not like the granting of execute on DBMS_SYSTEM to the PURGE_AUD user, but if that is the case for you then there is the option of not writing to the alert log and cutting this bit out of the PURGE_AUDIT_TRAIL procedure.

Finally, this maybe obvious to most readers, but the text will only be written to the alert log of the instance running the scheduled job in a RAC environment.