I'm not an expert tuner, but know the basics and where to look. Yesterday someone came to me with a problem - 2 databases, same data, same query. One was taking 2 seconds (that's good), one was taking 15 seconds (that's bad).
I checked the obvious things first and compared the results between the 2 servers:
Hardware
Database Parameters
Amount of data
Explain Plan.
Did a "top"
Ran awrrpt
The hardware on the bad server (memory and CPU) is actually a lot better on than on the good server:
Bad = 48GB of RAM, and 24 x 2.93GHz CPUs
Good = 12GB of RAM, and 16 x 2.93GHz CPUs
(just goes to show that throwing hardware at a problem isn't always the solution).
The database parameters on the bad server were beefed up to take advantage of the hardware.
The "top" command didn't show any runaway processes, or anything untoward.
The explain plan was the same for both servers, and showed the query using an index.
I created a 5 GB file on both - we've had issues with disk I/O on some of these hosts before. The bad server created the file a lot faster, so I ruled out the earlier issues we experienced.
I ran
"analyze index owner.index_name validate structure;"
then a
"select * from index_stats;"
on both servers.
The result (sorry about the formatting):
Bad:
HEIGHT BLOCKS NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
3 19272 DBQ_PROCESS_TIME 190128 18768 6004458 5524 18767 103 601529 8028 184802 5765005 190128 1 104501316 6605987 7 1 4 0 0 2 32
Good:
HEIGHT BLOCKS NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
3 17208 DBQ_PROCESS_TIME 9426 16048 401044 6052 16047 87 544198 8028 9392 399684 9426 1 97820932 945242 1 1 4
The figures from the bad server are a lot higher.
I rebuilt the index on the bad server:
alter index owner.index_name rebuild online parallel 3;
Didn't take long, then re-ran the query - it then ran in 0.01 seconds.
I rebuilt the index the same way on the good server, same result.
I was later told that the application owner did a purge of the table the night before and removed about 10M rows.
If I'd known that before it would have saved me a bit of investigation.
Wednesday, June 5, 2013
Tuesday, March 5, 2013
Quick notes on restoring DB to a new host
Quick Notes on Restoring a DB to another host
Just had to do this, so decided to put it here where I know I'll be able to find it.
I had to copy a V11.2.0.2 database from one linux box to another host, and the new version was 11.2.0.3.
Rman can do this with no issue, but you need to run the upgrade script after the restore.
The databases were test, and were running in no archive log mode.
This is what I did.
Shutdown the source database and restart it in mount mode.
Run rman and connect to the target, set the config and backup the controlfile. These are rough notes, you should be able to fill in the gaps.
shutdown immediate
startup mount
rman
connect target /
show all;
configure controlfile autobackup on;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u04/oraexport/orabackup/nmsmopdv_rman_%F';
configure channel device type disk format '/u04/oraexport/orabackup/nmsmopdv_rman_%U';
backup current controlfile;
backup database;
This will create the backup files in the /u04/oraexport/orabackup/ directory
scp the files to the target host. Preferably the same location, but if it is a different location it doesn't matter, there is a command to fix this.
While they are being scp'd, create the new database directories on the target host.
mkdir /u01/oradata/NMSMOPDV
mkdir /u01/app/oracle/admin/NMSMOPDV
mkdir /u02/oradata/NMSMOPDV
mkdir /u04/oradata/NMSMOPDV
mkdir /u05/oradata/NMSMOPDV
Create an initialisation parameter file in the $ORAC:LE_HOME/dbs directory with the relevant file and directory names (control file etc), and create an entry in the oratab for the new database.
On the target, run RMAN, restore the controlfile and then the database. Make sure you set the new database environment with . oraenv
rman
connect target /
startup nomount
restore controlfile from /u04/oraexport/orabackup/nmsmopdv_rman_c-3406859642-20130306-00';
You should see some messages, including these:
output file name=/u01/oradata/NMSMOPTS/NMSMOPTS_control01.ctl
output file name=/u02/oradata/NMSMOPTS/NMSMOPTS_control02.ctl
Mount the database then restore it
alter database mount
restore database;
If you see this:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/06/2013 10:19:05
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
it means the files haven't been found by rman. You can just do this and reply "yes" at the prompt:
catalog start with '/u04/oraexport';
You should see this:
List of Cataloged Files
=======================
File Name: /u04/oraexport/nmsmopts_rman_c-3406859642-20130306-00
File Name: /u04/oraexport/nmsmopts_rman_c-3406859642-20130306-01
File Name: /u04/oraexport/nmsmopts_rman_03o3r6e3_1_1
and can run the restore again:
RMAN> restore database;
Starting restore at 06-MAR-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u05/oradata/NMSMOPTS/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u05/oradata/NMSMOPTS/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u05/oradata/NMSMOPTS/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u05/oradata/NMSMOPTS/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u05/oradata/NMSMOPTS/NMSMOPTS_sams_op_data01.dbf
channel ORA_DISK_1: reading from backup piece /u04/oraexport/nmsmopts_rman_03o3r6e3_1_1
channel ORA_DISK_1: piece handle=/u04/oraexport/nmsmopts_rman_03o3r6e3_1_1 tag=TAG20130306T093707
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:26
Finished restore at 06-MAR-13
You should now be able to restart the database.
Because I was going from V11.2.0.2 to V11.2.0.3, I needed to do this:
SQL> startup upgrade;
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd
then it restarted normally. Do any tidying like creating an spfile.
Subscribe to:
Posts (Atom)