Oracle World: 12/28/08

Friday, January 2, 2009

Do not invoke SQL*Plus with a password On UNIX and Linux platforms.

Most of us sometimes start SQL * Plus with a password on UNIX and Linux platforms without knowing security threat.

For example, an application user connects SQL * Plus by passing username and password on Unix/Linux Server.

$ sqlplus apps/apps@proddb

Here the sqlplus command parameters are very much available for viewing by all operating system users on the same host computer; as a result, password entered on the command line could be exposed to other users, as below.

$ ps -efgrep sqlplus
oracle 14490 2190 0 16:31:53 pts/5 0:00 sqlplus apps/apps@proddb
oracle 14493 14491 0 16:32:01 pts/5 0:00 grep sqlplus

So, there might be a chance for an intruder to know the user id and password, and can connect to the database using that credentials.

Then, following is the secure and best way of connecting SQL * Plus where the password is not exposed on the command line.

$ sqlplus apps@proddb
Enter password: ****

Or, even not to expose the username and connecting string.

$ sqlplus
Enter user-name: apps@proddb
Enter password: ****

Or

$ sqlplus /nolog
SQL> connect apps@proddb
Enter password: ****

And also, do not use the password while invoking Export/Import Utility using exp/imp command line, and for any other command line utilities which you think the password will be exposed to others.

On Microsoft Windows, the command recall feature (the Up arrow) remembers user input across command invocations.

For example, if you use the CONNECT APPS/password notation in SQL*Plus, exit, and then press the Up arrow to repeat the CONNECT command, the command recall feature discloses the connect string and shows the password. So, it is advice *NOT* to pass the password while connecting to SQL * Plus on windows as well.

Do not invoke SQL*Plus with a password On UNIX and Linux platforms.

Cloning and Refreshing an Oracle Database

The information about Cloning and Refreshing a Database process available over web widely or has already been discussed. Here, in this post, I would like to explain and provide the information on the following Questions about Cloning and Refreshing of a Database with my simple terms.

Terms used in this post:

Source System - the system to be cloned - Production
Target System - the newly created (or cloned) system – Non Production
Production Database – PROD
Test Database – TEST
Development Database - DEV

What is a Database Clone?

* A database clone is an activity/procedure which is performed by every DBA on regular basis or when there is a requirement or request to do so from the different departments i.e. Test/Development teams.

* Cloning is nothing but creating a copy of production system in to a test or development environment. i.e. Having an exact image of production database in test area.

* Cloning is a procedure for preparing and creating a test or development servers with the copy of Oracle production database for testing upgrades, migrating an existing system to new hardware.

* A cloning process includes a copy of Oracle Home (Directories and Binaries) backup and Database (Database related files) backup to prepare the instance on another server.

* Though, it is possible to clone a database on the same server, Oracle doesn’t suggest to clone a database on the same server, where the production database is running.

What is a Database Refresh?

* A Database Refresh is also referred to as a database clone. However, we don’t clone Oracle Home rather we clone the Database as refresh.

* Refreshing a database is something like applying the changes or updates of production database to the database where the database is already cloned. i.e. let’s say you have cloned a database a month back, and now you are asked for doing refresh of a database, then you will perform the backup of database and prepare the clone the instance again on test server. This is nothing but refreshing.

* Refreshing of a particular table, group of tables, schema, or tablespace will be done using traditional export/import, transportable Tablespaces, or data pump methods.

* When an Oracle patch is applied on Production System, or in doubt, you have to prepare and clone the database again with the copy of Oracle Home (Directories and Binaries) Backup and Database (Database related files) Backup to prepare the instance.

* The difference between Cloning and Refreshing is that cloning process includes Oracle Home and database Clone; where as Refreshing process only includes database clone.

* If seen, the words, Clone and Refresh are used interchangeably for the sake of convenient.

When and why we Clone a Database?

* Generally production (PROD) database is cloned for various reasons and needs i.e. for something to be tested or something to be developed later those to be moved to production.

* It’s normal and quite common thing is that whenever there is any change or update to be performed and do not know the impact or effect after applying it on production (PROD), it’s required to be applied and tested on *NON* production database first (TEST or DEV), after the confirmation of change success, given by the users, then the changes will be moved to production.

* A Cloned test instance (TEST) for testing team/environment is exclusively used for testing the changes or issues which will be come severe on Production. Oracle Support gives the solution as fix when there is an issue in the database, so this fix needs to perform or apply on test/development databases.

* A Cloned development instance (DEV) for development team/environment is used for developing the new changes and then deploying the same on Production.

* A Cloned patch instance is used for patching to know the impact and the time required to apply the same on Production.

How to clone an Oracle Database and different ways of cloning.

There are many possible methods available for cloning a database, but each of them has pros and cons, and significance. Following are the methods.

Using Cold (Offline) Backup:

This is an easy and simple method to perform a clone of a database. This method requires your production database (PROD) needs to be shutdown gracefully, and take the backup of the database related files i.e. Data files, Control files, Redo Log files, using Operating System commands i.e. cp or copy. This is not possible where your PROD database is running 24/7 and should be available continuously for users.

For syntax and the series of steps to perform the clone using cold backup, refer the following URLs from the reference.

References:
http://www.samoratech.com/TopicOfInterest/swCloneDB.htm
http://www.pgts.com.au/pgtsj/pgtsj0211b.html
http://www.jlcomp.demon.co.uk/faq/clone_db.html

Using Hot (Online) Backup:

In this method, backup of the database will be done online i.e. without shutting down the database.

For this, your Production Database is must be in Archive log mode. For syntax and the series of steps to perform the clone using hot backup, refer the following URLs from the reference.

Reference:
http://www.quest-pipelines.com/newsletter/cloning.htm
http://www.oralnx.com/index.php/2007/03/22/cloning-an-oracle-database/
http://www.shutdownabort.com/quickguides/clone_hot.php

Using RMAN Commands:

Cloning can also be performed using RMAN Backups and RMAN commands and it’s also an easy method to perform so. The RMAN DUPLICATE command is used to perform the clone. Until Oracle 9i, to clone the database, it is required to be the Source and Target systems should have the same OS i.e. it is not possible to clone across the platform. But as workaround, using export/import can be cloning the database across the platforms. But starting from Oracle 10g the RMAN capabilities have improved immensely. Cross platform cloning/duplicating a database can be done using RMAN CONVERT commands.

For syntax and the series of steps to perform the clone using RMAN Commands, refer the following URLs from the reference.

References:

Creating and Updating Duplicate Databases with RMAN

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmdupdb.htm#i1008564

Cross-Platform Transportable Database: RMAN CONVERT DATABASE

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/dbxptrn002.htm#CHDCFFDI

Creating a Duplicate Database on a Local or Remote Host

http://sabdarsyed.blogspot.com/2007/06/clone-of-database-on-same-host-ie.html

http://download-east.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmdupdb005.htm

Pre & Post Cloning Steps/Changes:

* Do *NOT* set the clone database name as good as production database Name.

* It’s *NOT* mandatory to have the initialization parameter values of cloned instance similar to Production Instance.

* It is *NOT* mandatory to have the cloned instance in Archive log mode. Because unnecessarily archive log files are generated, which consume the hard disk space? If at all, the cloned instance crashed and need to be recovered, it can easily be again cloned from the production.

* After the clone, change the system users passwords i.e. SYS & SYSTEM, and for any critical users passwords.

* Disable the jobs which are not required to be run in the cloned instance.

* Change any application users tables from the cloned database which are still referring the Production Database i.e. Server IP, Port Details, Printer Details etc,

Other Useful Links:

OTN Forums on Cloning:
http://forums.oracle.com/forums/search.jspa?threadID=&q=clone+a+database&objID=f61&dateRange=all&userID=&numResults=30&rankBy=10001

Ask Tom Forums:

DB cloning -- what is it and why
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:575623107841

Creating test environment from production box
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:526422273445

Cloning and Refreshing an Oracle Database

* Cloning is nothing but creating a copy of production system in to a test or development environment. i.e. Having an exact image of production database in test area.

* Cloning is a procedure for preparing and creating a test or development servers with the copy of Oracle production database for testing upgrades, migrating an existing system to new hardware.

* A cloning process includes a copy of Oracle Home (Directories and Binaries) backup and Database (Database related files) backup to prepare the instance on another server.

* Though, it is possible to clone a database on the same server, Oracle doesn’t suggest to clone a database on the same server, where the production database is running.

What is a Database Refresh?

* A Database Refresh is also referred to as a database clone. However, we don’t clone Oracle Home rather we clone the Database as refresh.

* Refreshing of a particular table, group of tables, schema, or tablespace will be done using traditional export/import, transportable Tablespaces, or data pump methods.

* The difference between Cloning and Refreshing is that cloning process includes Oracle Home and database Clone; where as Refreshing process only includes database clone.

* If seen, the words, Clone and Refresh are used interchangeably for the sake of convenient.

When and why we Clone a Database?

* Generally production (PROD) database is cloned for various reasons and needs i.e. for something to be tested or something to be developed later those to be moved to production.

* A Cloned development instance (DEV) for development team/environment is used for developing the new changes and then deploying the same on Production.

* A Cloned patch instance is used for patching to know the impact and the time required to apply the same on Production.

How to clone an Oracle Database and different ways of cloning.

Pre & Post Cloning Steps/Changes:

* Do *NOT* set the clone database name as good as production database Name.

* It’s *NOT* mandatory to have the initialization parameter values of cloned instance similar to Production Instance.

* After the clone, change the system users passwords i.e. SYS & SYSTEM, and for any critical users passwords.

* Disable the jobs which are not required to be run in the cloned instance.

* Change any application users tables from the cloned database which are still referring the Production Database i.e. Server IP, Port Details, Printer Details etc,

Other Useful Links:

OTN Forums on Cloning:
http://forums.oracle.com/forums/search.jspa?threadID=&q=clone+a+database&objID=f61&dateRange=all&userID=&numResults=30&rankBy=10001

Ask Tom Forums:

Setting an Oracle Environment variable – ORACLE_HOME

What is ORACLE_HOME used for?

* The ORACLE_HOME is an environment variable which is used to set and define the path of Oracle Home (server) Directory.
* The ORACLE_HOME directory will have the sub directories, binaries, executables, programs, scripts, etc. for the Oracle Database.
* This directory can be used by any user who wants to use the particular database.
* If the ORACLE_HOME variable is defined as an environment variable, then during the installation process, the Oracle Home Path will be set to the directory defined as default. If the variable is not defined, then the Oracle will take its own default location. i.e. The ORACLE_HOME variable does not have to be preset as an environment variable, it can be set during the installation process.
* Basically The ORACLE_HOME variable is in the following ORACLE_BASE directory:
ORACLE_HOME=$ORACLE_BASE/product/10.2.0.

What is ORACLE_BASE used for?

* The ORACLE_BASE is also an environment variable to define the base/root level directory where you will have the Oracle Database directory tree - ORACLE_HOME defined under the ORACLE_BASE directory.
* Basically, The ORACLE_BASE directory is a higher-level directory, than ORACLE_HOME, that you can use to install the various Oracle Software Products and the same Oracle base directory can be used for more than one installation.

Note: If you did not set the ORACLE_BASE environment variable before starting OUI, the Oracle home directory is created in an app/username/directory on the first existing and writable directory from /u01 through /u09 for UNIX and Linux systems, or on the disk drive with the most available space for Windows systems. If /u01 through /u09 does not exist on the UNIX or Linux system, then the default location is user_home_directory/app/username.

How to check if ORACLE_HOME is set already?

On Unix/Linux Systems:

Basically, before or after the Oracle Database is installed, the oracle user profile, the environment variable file, is prepared where all the required environment variables for Oracle are set. i.e. ORACLE_BASE, ORACLE_HOME, ORACLE_SID,PATH, LD_LIBRARY_PATH, NLS_LANG, etc.

The user profile file can be
.bash_profile – Bash Shell
.profile – Bourne Shell or Korn shell
.login – C Shell

Note: This user profile file will be under user’s home directory i.e. $HOME/.bash_profile

To check specific environment variable set:

$ echo $ORACLE_HOME

To check all the environment variables set:

$ env

On Windows Systems:

To check specific environment variable set:

C:\> set ORACLE_HOME

OR

C:\echo %ORACLE_HOME%

To check all the environment variables set:

C:\> set

Or

C:\> env

Other way, to check the ORACLE_HOME, is as follows.

Start -> Run -> Regedit (enter) -> HKEY_LOCAL_MACHINE -> SOFTWARE –> ORACLE

i.e. My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

How to check using sqlplus command:

To Find the ORACLE_HOME path in Oracle Database
In 9i:

SELECT substr(file_spec,1,instr(file_spec,'lib')-2) ORACLE_HOME FROM dba_libraries
WHERE library_name='DBMS_SUMADV_LIB';

In 10g:

SQL > var OHM varchar2(100);
SQL > EXEC dbms_system.get_env('ORACLE_HOME', :OHM) ;
SQL > PRINT OHM

Linux/Unix:

echo $ORACLE_HOME

Windows:

start - run - regedit (enter) - HKEY_LOCAL_MACHINE - SOFTWARE - ORACLE

Type the below command at prompt if the environment variable is defined for ORACLE_HOME

c:\> set oracle_home (ENTER)

How to set the ORACLE_HOME environment variable?

On Unix/Linux Systems:

Define the ORACLE_HOME value in the user profile file i.e. .bash_profile or .profile

ORACLE_HOME=$ORACLE_BASE/product/10.2.0
export ORACLE_HOME

Source the user profile as follows:

Bash shell:

$ . ./.bash_profile

Bourne shell or Korn shell:

$ . ./.profile

C shell:

% source ./.login

If no profile file is set with environment variables, then physically also be set as follows:

Bourne, Bash, or Korn shell:

$ ORACLE_BASE=/oracle/app
$ export ORACLE_BASE
$ ORACLE_HOME=$ORACLE_BASE/product/10.2.0
$ export ORACLE_HOME

C Shell:

% setenv ORACLE_BASE /oracle/app
% setenv ORACLE_HOME /oracle/app/product/10.2.0

On Windows Systems:

My Computer -> Properties -> Advanced -> Environment Variables -> System Variables -> New/Edit/Delete (to set the variables)

After setting the environment variables as above, open a fresh CMD tool and check whether they set properly or not. Do not try on already opened CMD tool to make sure the variables set or not.

Another way to physically set the variables as follow at the DOS prompt:

C:\> set ORACLE_HOME=C:\oracle\app\product\10.2.0
C:\> echo %ORACLE_HOME%

Note: I would suggest to refer the Oracle Documentation on Installation where these environment variables are discussed and explained with the enough information.

Setting an Oracle Environment variable – ORACLE_HOME

ORA-1555 and UNDO_RETENTION

When you see ORA-1555s do you just go ahead and increase UNDO_RETENTION ?
Recently, I had a string of ORA-1555s occurring frequently through the day.
One quick suggestion was to increase UNDO_RETENTION. Some DBAs might do that.
However, that would have been jumping to a conclusion.
This was a database that had been running 9.2 for more than 2 years. I had one other
9.2 database with the same schema, usage and comparable size {for another business
unit} and wasn't seeing ORA-1555s there.
The trace files showed me that these ORA-1555s were always for the same SQL.
I knew that this SQL was a Refresh Query being executed to refresh a Materialized View
in another database.
Apparently, it was only this query that was reporting ORA-1555s at about 1 out of every 4
refreshes (the refresh being hourly) . However, later, a developer also reported a
similar query on the same tables taking a long time (but not yet erroring out on ORA-1555s}.
So, the fix was not to increase UNDO_RETENTION but to tune/optimize the tables
and specific queries. The rest of the database schema had no issues.

ORA-1555 and UNDO_RETENTION

Configuring Shared Server

We have recently configured shared server in some of our databases. Steps to follow to enable shared server are below.

The following configuration is on Enterprise Edition 9.2.0.5. The numbers used in the settings shown are only examples. We are running an OLTP system with about 2000 concurrent users with these parameters without any problem.

The parameters you need to set for the shared server and their default values are:

NAME VALUE
------------------------------ ----------
circuits 0
dispatchers
max_dispatchers 5
max_shared_servers 20
mts_circuits 0
mts_dispatchers
mts_max_dispatchers 5
shared_server_sessions 0
shared_servers 0

The ones starting with mts are for backward compatibility, setting circuits, dispatchers and max_dispatchers or mts_circuits, mts_dispatchers and mts_max_dispatchers is the same and is up to you.

If we go over these parameters one by one:

Circuits: This is a static parameter and it defaults to the sessions parameter when the shared server is used. You can leave it to the defalt value if your users are not using database links extensively. Because every session connected through the shared server adds to the circuits count for each distinct database link it opens. In our case we had sessions=3500 and circuits=3500 derived from that sessions parameter. But each user queried from two or three database links, so when the session count was about 1500 we exhausted circuits and began to get “error 18 creating virtual circuit” errors. So, you may need to set this parameter explicitly to a higher value if you are using database links.

Dispatchers: This parameter is said to be dynamic in Database Reference but if you have it as null in your database like the above settings you can not set it dynamically.

SQL> alter system set dispatchers='(protocol=tcp)(dispatchers=2)';
alter system set dispatchers='(protocol=tcp)(dispatchers=2)'
*
ERROR at line 1:
ORA-00105: dispatching mechanism not configured for network protocol
(ADDRESS=(PARTIAL=YES)(PROTOCOL=tcp))

You can not even set it with scope=spfile.

SQL> alter system set dispatchers='(protocol=tcp)(dispatchers=2)' scope=spfile;
alter system set dispatchers='(protocol=tcp)(dispatchers=2)' scope=spfile
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

If you have it null on your system, the only way to set this is to create a pfile from the spfile and put it in there and then shutdown the database, create a new spfile and startup.

Shared_servers: This is a dynamic parameter and defaults to 1 when shared server is used.

Max_shared_servers: This is a static parameter and defaults to 20 or 2*shared_servers.

The steps we followed were:

SQL> alter system set circuits = 10000 scope=spfile;

System altered.

SQL> alter system set max_shared_servers=200 scope=spfile;

System altered.

SQL> alter system set shared_servers=50 scope=spfile;

System altered.

SQL> create pfile from spfile;

File created.

We have inserted the following line to the pfile created.

dispatchers='(protocol=tcp)(dispatchers=5)'

Then shutdown, create spfile from pfile and startup the instance.

Configuring Shared Server

We have recently configured shared server in some of our databases. Steps to follow to enable shared server are below.

ORA-04043 in mount mode

There was a question at the OTN Database-General forum today about a problem when trying to describe the view dba_tablespaces.

The poster was getting an ORA-04043 error.


SQL> desc dba_tablespaces
ERROR:
ORA-04043: object dba_tablespaces does not exist

The first thing I thought about this was that the instance might have been in mount mode. I tried it on a database in mount stage and I got the error.


SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  404723928 bytes
Fixed Size                   735448 bytes
Variable Size             234881024 bytes
Database Buffers          167772160 bytes
Redo Buffers                1335296 bytes
Database mounted.
SQL> desc dba_tablespaces
ERROR:
ORA-04043: object dba_tablespaces does not exist

I was expecting to be able to see the view after opening the database, but...


SQL> alter database open;
Database altered.

SQL> desc dba_tablespaces
ERROR:
ORA-04043: object dba_tablespaces does not exist

I could not query it either.


SQL> select * from dba_tablespaces;
select * from dba_tablespaces
              *
ERROR at line 1:
ORA-00942: table or view does not exist

A quick search in Metalink returned note 296235.1 which refers to the bug 2365821 and says that if you describe any dba_* view in mount mode you cannot describe the same view even after opening the database. The only solution is to restart the database.


SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  404723928 bytes
Fixed Size                   735448 bytes
Variable Size             234881024 bytes
Database Buffers          167772160 bytes
Redo Buffers                1335296 bytes
Database mounted.
SQL> alter database open;

Database altered.

SQL> desc dba_tablespaces
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
 BLOCK_SIZE                                NOT NULL NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                               NOT NULL NUMBER
 MAX_EXTENTS                                        NUMBER
 PCT_INCREASE                                       NUMBER
 MIN_EXTLEN                                         NUMBER
 STATUS                                             VARCHAR2(9)
 CONTENTS                                           VARCHAR2(9)
 LOGGING                                            VARCHAR2(9)
 FORCE_LOGGING                                      VARCHAR2(3)
 EXTENT_MANAGEMENT                                  VARCHAR2(10)
 ALLOCATION_TYPE                                    VARCHAR2(9)
 PLUGGED_IN                                         VARCHAR2(3)
 SEGMENT_SPACE_MANAGEMENT                           VARCHAR2(6)
 DEF_TAB_COMPRESSION                                VARCHAR2(8)

I was not aware of this behaviour till now. I tested this on 9.2 but the bug seems not fixed in any version.

ORA-04043 in mount mode

There was a question at the OTN Database-General forum today about a problem when trying to describe the view dba_tablespaces.

The poster was getting an ORA-04043 error.


SQL> desc dba_tablespaces
ERROR:
ORA-04043: object dba_tablespaces does not exist

The first thing I thought about this was that the instance might have been in mount mode. I tried it on a database in mount stage and I got the error.


SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  404723928 bytes
Fixed Size                   735448 bytes
Variable Size             234881024 bytes
Database Buffers          167772160 bytes
Redo Buffers                1335296 bytes
Database mounted.
SQL> desc dba_tablespaces
ERROR:
ORA-04043: object dba_tablespaces does not exist

I was expecting to be able to see the view after opening the database, but...


SQL> alter database open;
Database altered.

SQL> desc dba_tablespaces
ERROR:
ORA-04043: object dba_tablespaces does not exist

I could not query it either.


SQL> select * from dba_tablespaces;
select * from dba_tablespaces
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  404723928 bytes
Fixed Size                   735448 bytes
Variable Size             234881024 bytes
Database Buffers          167772160 bytes
Redo Buffers                1335296 bytes
Database mounted.
SQL> alter database open;

Database altered.

SQL> desc dba_tablespaces
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
 BLOCK_SIZE                                NOT NULL NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                               NOT NULL NUMBER
 MAX_EXTENTS                                        NUMBER
 PCT_INCREASE                                       NUMBER
 MIN_EXTLEN                                         NUMBER
 STATUS                                             VARCHAR2(9)
 CONTENTS                                           VARCHAR2(9)
 LOGGING                                            VARCHAR2(9)
 FORCE_LOGGING                                      VARCHAR2(3)
 EXTENT_MANAGEMENT                                  VARCHAR2(10)
 ALLOCATION_TYPE                                    VARCHAR2(9)
 PLUGGED_IN                                         VARCHAR2(3)
 SEGMENT_SPACE_MANAGEMENT                           VARCHAR2(6)
 DEF_TAB_COMPRESSION                                VARCHAR2(8)

I was not aware of this behaviour till now. I tested this on 9.2 but the bug seems not fixed in any version.

Index block split bug in 9i

A bug in 9i about index block splits when rows are inserted in the order of the index columns. Depending on when you commit your inserts the index size changes dramatically.

While I was trying to find out why a 3-column primary key index takes more space than its table I recalled that bug and it turned out that was the reason of the space issue. The related bug is 3196414 and it is fixed in 10G.

Here is the test case Richard presents in his paper.


SQL> create table t(id number,value varchar2(10));

Table created.

SQL> create index t_ind on t(id);

Index created.

SQL> @mystat split

NAME                                VALUE
------------------------------ ----------
leaf node splits                        0
leaf node 90-10 splits                  0
branch node splits                      0

SQL> ed
Wrote file afiedt.buf

  1  begin
  2  for i in 1..10000 loop
  3     insert into t values(i,'test');
  4     commit;
  5  end loop;
  6* end;
SQL> r
  1  begin
  2  for i in 1..10000 loop
  3     insert into t values(i,'test');
  4     commit;
  5  end loop;
  6* end;

PL/SQL procedure successfully completed.

SQL> @mystat2 split

NAME                                VALUE       DIFF
------------------------------ ---------- ----------
leaf node splits                       35         35
leaf node 90-10 splits                  0          0
branch node splits                      0          0

SQL> analyze index t_ind validate structure;

Index analyzed.

SQL> select lf_blks, pct_used from index_stats;

   LF_BLKS   PCT_USED
---------- ----------
        36         51

SQL> drop table t;

Table dropped.

I am trying to insert the rows in the order of the primary key column, so what I expect to see is that when an index block fills there will be a 90-10 split and the index will grow in size. But as the number of leaf block splits show there are 35 block splits and none of them are 90-10 splits meaning all are 50-50 block splits. I have 36 leaf blocks but half of each one is empty.

If we try the same inserts but commit after the loop the result changes.


SQL> create table t(id number,value varchar2(10));

Table created.

SQL> create index t_ind on t(id);

Index created.

SQL> @mystat split

NAME                                VALUE
------------------------------ ----------
leaf node splits                       35
leaf node 90-10 splits                  0
branch node splits                      0

SQL> ed
Wrote file afiedt.buf

  1  begin
  2  for i in 1..10000 loop
  3     insert into t values(i,'test');
  4  end loop;
  5  commit;
  6* end;
SQL> r
  1  begin
  2  for i in 1..10000 loop
  3     insert into t values(i,'test');
  4  end loop;
  5  commit;
  6* end;

PL/SQL procedure successfully completed.

SQL> @mystat2 split

NAME                                VALUE       DIFF
------------------------------ ---------- ----------
leaf node splits                       53         53
leaf node 90-10 splits                 18         18
branch node splits                      0          0

SQL> analyze index t_ind validate structure;

Index analyzed.

SQL> select lf_blks, pct_used from index_stats;

   LF_BLKS   PCT_USED
---------- ----------
        19         94

In this case we see that there have been 18 block splits and all were 90-10 splits as expected. We have 19 leaf blocks and all are nearly full. Depending on where the commit is we can get an index twice the size it has to be. When I ran the same test in 10G it did not matter where the commit was. I got 19 leaf blocks in both cases.

I did not test if this problem happens when several sessions insert a single row and commit just like in an OLTP system but I think it is likely because we have indexes showing this behavior in OLTP systems.

Index block split bug in 9i

A bug in 9i about index block splits when rows are inserted in the order of the index columns. Depending on when you commit your inserts the index size changes dramatically.


SQL> create table t(id number,value varchar2(10));

Table created.

SQL> create index t_ind on t(id);

Index created.

SQL> @mystat split

NAME                                VALUE
------------------------------ ----------
leaf node splits                        0
leaf node 90-10 splits                  0
branch node splits                      0

SQL> ed
Wrote file afiedt.buf

  1  begin
  2  for i in 1..10000 loop
  3     insert into t values(i,'test');
  4     commit;
  5  end loop;
  6* end;
SQL> r
  1  begin
  2  for i in 1..10000 loop
  3     insert into t values(i,'test');
  4     commit;
  5  end loop;
  6* end;

PL/SQL procedure successfully completed.

SQL> @mystat2 split

NAME                                VALUE       DIFF
------------------------------ ---------- ----------
leaf node splits                       35         35
leaf node 90-10 splits                  0          0
branch node splits                      0          0

SQL> analyze index t_ind validate structure;

Index analyzed.

SQL> select lf_blks, pct_used from index_stats;

   LF_BLKS   PCT_USED
---------- ----------
        36         51

SQL> drop table t;

Table dropped.


SQL> create table t(id number,value varchar2(10));

Table created.

SQL> create index t_ind on t(id);

Index created.

SQL> @mystat split

NAME                                VALUE
------------------------------ ----------
leaf node splits                       35
leaf node 90-10 splits                  0
branch node splits                      0

SQL> ed
Wrote file afiedt.buf

  1  begin
  2  for i in 1..10000 loop
  3     insert into t values(i,'test');
  4  end loop;
  5  commit;
  6* end;
SQL> r
  1  begin
  2  for i in 1..10000 loop
  3     insert into t values(i,'test');
  4  end loop;
  5  commit;
  6* end;

PL/SQL procedure successfully completed.

SQL> @mystat2 split

NAME                                VALUE       DIFF
------------------------------ ---------- ----------
leaf node splits                       53         53
leaf node 90-10 splits                 18         18
branch node splits                      0          0

SQL> analyze index t_ind validate structure;

Index analyzed.

SQL> select lf_blks, pct_used from index_stats;

   LF_BLKS   PCT_USED
---------- ----------
        19         94

Thursday, January 1, 2009

How to locate unindexed foreign keys

Two issues can be avoided if you index your foreign key columns:

Slow joins (Full Table Scans) between parent/child tables
Deadlocks when updating or deleting a parent key.

The following script is very helpfull in locating unindexed foreign keys:

column columns format a20 word_wrapped
column table_name format a30 word_wrapped

select decode( b.table_name, NULL, '****', 'ok' ) Status,
 a.table_name, a.columns, b.columns
from
( select substr(a.table_name,1,30) table_name,
substr(a.constraint_name,1,30) constraint_name,
   max(decode(position, 1,     substr(column_name,1,30),NULL)) ||
   max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
 from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
  and b.constraint_type = 'R'
group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,
   max(decode(column_position, 1,     substr(column_name,1,30),NULL)) ||
   max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
 from user_ind_columns
group by substr(table_name,1,30), substr(index_name,1,30) ) b
where a.table_name = b.table_name (+)
and b.columns (+) like a.columns || '%'
/

How to locate unindexed foreign keys

Two issues can be avoided if you index your foreign key columns:

Slow joins (Full Table Scans) between parent/child tables
Deadlocks when updating or deleting a parent key.

The following script is very helpfull in locating unindexed foreign keys:

column columns format a20 word_wrapped
column table_name format a30 word_wrapped

select decode( b.table_name, NULL, '****', 'ok' ) Status,
 a.table_name, a.columns, b.columns
from
( select substr(a.table_name,1,30) table_name,
substr(a.constraint_name,1,30) constraint_name,
   max(decode(position, 1,     substr(column_name,1,30),NULL)) ||
   max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
   max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
 from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
  and b.constraint_type = 'R'
group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,
   max(decode(column_position, 1,     substr(column_name,1,30),NULL)) ||
   max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
   max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
 from user_ind_columns
group by substr(table_name,1,30), substr(index_name,1,30) ) b
where a.table_name = b.table_name (+)
and b.columns (+) like a.columns || '%'
/

What is the difference between V$ and GV$, also V$ and V_$?

These “$” views are called dynamic performance views. They are continuously updated while a database is open and in use, and their contents relate primarily to performance.

The actual dynamic performance views are identified by the prefix V_$. Public synonyms for these views have the prefix V$. You should access only the V$ objects, not the V_$ objects.

For almost every V$ view, Oracle has a corresponding GV$ (global V$) view. In addition to the V$ information, each GV$ view contains an extra column named INST_ID of datatype NUMBER. The INST_ID column displays the instance number from which the associated V$ view information was obtained.

What is the difference between V$ and GV$, also V$ and V_$?

These “$” views are called dynamic performance views. They are continuously updated while a database is open and in use, and their contents relate primarily to performance.

The actual dynamic performance views are identified by the prefix V_$. Public synonyms for these views have the prefix V$. You should access only the V$ objects, not the V_$ objects.

How to find what patches are installed in your $ORACLE_HOME using OPatch (Unix/Linux)?

$ cd $ORACLE_HOME/OPatch

$ ./opatch lsinventory

How to find what patches are installed in your $ORACLE_HOME using OPatch (Unix/Linux)?

$ cd $ORACLE_HOME/OPatch

$ ./opatch lsinventory

How to find the most frequently executed SQL?

Select executions, buffer_gets, sql_text
from v$sqlarea
where executions > 10000
order by executions desc;

How to find the most frequently executed SQL?

Select executions, buffer_gets, sql_text
from v$sqlarea
where executions > 10000
order by executions desc;

What happens when a session is killed?

When a session is killed by an ALTER SYSTEM KILL SESSION 'nnnn,nnnn', it won’t necessarily vanish immediately. If the session has made changes to the database, they have to be undone just as if you had coded a ROLLBACK. Drastic action, such as a forced reboot of the database, may make the killed session vanish, but the rollback still has to be done.

Generally it is best to leave the client program, such as TOAD or SQL*Plus, running until the rollback is complete. That way, the client will receive the ‘Your session has been killed’ error and the database session can exit cleanly.

You can monitor how much work a session has still to rollback using an SQL like this :

SELECT
   vt.used_ublk ,
   vs.sid,
   vs.serial#,
   vs.username,
   vs.status,
   vs.schemaname,
   vs.osuser,
   vs.machine,
   vs.terminal,
   vs.program,
   vs.prev_hash_value,
   vs.sql_hash_value,
   vt.start_ubablk,
   used_urec
FROM v$session vs, v$transaction vt, v$sqlarea a
WHERE vs.taddr = vt.addr
AND bitand(vt.flag,POWER(2,7))> 0
AND a.hash_value(+) =
   DECODE(vs.sql_hash_value,
           0,vs.prev_hash_value,
           vs.sql_hash_value)

The USED_UREC should steadily decrease until the rollback is complete.

What happens when a session is killed?

You can monitor how much work a session has still to rollback using an SQL like this :

SELECT
   vt.used_ublk ,
   vs.sid,
   vs.serial#,
   vs.username,
   vs.status,
   vs.schemaname,
   vs.osuser,
   vs.machine,
   vs.terminal,
   vs.program,
   vs.prev_hash_value,
   vs.sql_hash_value,
   vt.start_ubablk,
   used_urec
FROM v$session vs, v$transaction vt, v$sqlarea a
WHERE vs.taddr = vt.addr
AND bitand(vt.flag,POWER(2,7))> 0
AND a.hash_value(+) =
   DECODE(vs.sql_hash_value,
           0,vs.prev_hash_value,
           vs.sql_hash_value)

The USED_UREC should steadily decrease until the rollback is complete.

How to check free space in tablespaces

Query shows tablespace usage and lists tablespace names including the database name:

select d.status,
 db.name dbname,
 d.tablespace_name tsname,
 d.extent_management,
 d.allocation_type,
 to_char(nvl(d.min_extlen / 1024, 0),
   '99G999G990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')
   "ALLOC_SIZE (K)",
 d.contents "Type",
case
when(d.contents = 'TEMPORARY') then
 to_char(nvl(a.bytes / 1024 / 1024, 0),
 '99G999G990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')
else
 to_char(nvl(t.bytes / 1024 / 1024, 0),
 '99G999G990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')
end as "Size (M)",
 to_char(nvl((a.bytes - nvl(f.bytes, 0)) / 1024 / 1024,   0),
 '99G999G990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')
 "Used (M)",
 to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100,   0),
 '990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')
 "Used (%)"
from sys.dba_tablespaces d,
   (select tablespace_name,
    sum(bytes) bytes
  from dba_data_files
  group by tablespace_name)
a,
   (select tablespace_name,
    sum(bytes) bytes
  from dba_temp_files
  group by tablespace_name)
t,
   (select tablespace_name,
    sum(bytes) bytes
  from dba_free_space
  group by tablespace_name)
f,
 v$database db
where d.tablespace_name = a.tablespace_name(+)
and d.tablespace_name = f.tablespace_name(+)
and d.tablespace_name = t.tablespace_name(+)
order by 10 desc;

Friday, January 2, 2009

Thursday, January 1, 2009

Blog Archive