Hi,
This is sure an important interview question asked to me in many interviews
what is the difference between DBMS and RDBMS??
1)Data Base Management System is a process of managing
data for efficient retrivel & storage of data.
Ex: sysbase , Foxpro
The database which is used by relations(tables) to
acquire information retrival are known as RDBMS
EX: SQL, ORACLE,MY-SQLSERVER
2)In DBMS,client server concept is not present.
In RDBMS,client server architecture is present (i.e) the
client sends the request to the server and the server
responds to that particular request.
3)DBMS does not impose any constraints or security with
regard to data manipulation. It is user or the programmer
responsibility to ensure the ACID PROPERTY of the database.
RDBMS defines the integrity constraint for the purpose of
holding ACID PROPERTY.
4)In DBMS Normalization process will not be present.
In RDBMS, normalization process will be present to check
the database table cosistency.
5)In DBMS we treats Data as Files internally.
In RDBMS we treats data as Tables internally.
6)DBMS supports 3 rules of E.F.CODD out off 12 rules.
RDBMS supports minimum 6 rules of E.F.CODD.
7)DBMS does not support distributed databases.
RDBMS support distributed databases.
8)In DBMS we see small organization with small amount of data.
RDBMS designed to take care of large amount of data.
9)DBMS contains only flat data.
RDBMS contains some relation between entities.
10)DBMS supports single user
RDBMS supports multiple user
Best regards,
Rafi.
Wednesday, March 24, 2010
Monday, March 22, 2010
ORACLE DBA GENERAL INTERVIEW QUESTIONS
Hi ,
Below are some general interview questions which can be useful:
Q 1)What is tablespace and Quota,if i create any user how much quota is allocated to it?How to check the user had been granted unlimited quota for a particular tablespace?(TCS INTERVIEW QUESTION)
Ans: TABLESPACE A tablespace is a logical storage unit within an Oracle database. It is logical because a tablespace is not visible in the file system of the machine on which the database resides. A tablespace, in turn, consists of at least one datafile which, in turn, are physically located in the file system of the server. Btw, a datafile belongs to exactly one tablespace. Each table, index and so on that is stored in an Oracle database belongs to a tablespace. The tablespace builds the bridge between the Oracle database and the filesystem in which the table's or index' data is stored. There are three types of tablespaces in Oracle:
•Permanent tablespaces
•Undo tablespaces
•temporary tablespaces
Quotas on tablespaces
Users can have (space) quotas on tablespaces. This is a means to limit how much space a user uses on a tablespace. This quota can be set using alter user quota... Use DBA_TS_QUOTAS/USER_TS_QUOTAS to find out a user's quota on a tablespace as well as how much he has already occupied.
If we just create a user and query dba_ts_quotas it will result in no rows as given below:
http://forums.oracle.com/forums/thread.jspa?threadID=1046926&tstart=0
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3396797633001
If I get the value -1 in maxbytes column of DBA_TS_QUOTAS it will tell you that we had allocated unlimited quota to a particular tablespace.
Refer the above for more details.This is something interesting.
Q 2) What are batch files?(TCS interview question)
Ans:
In DOS, OS/2, and Microsoft Windows, a batch file is a text file containing a series of commands intended to be executed by the command interpreter. When a batch file is run, the shell program (usually COMMAND.COM or cmd.exe) reads the file and executes its commands, normally line-by-line. Batch files are useful for running a sequence of executables automatically and are often used by system administrators to automate tedious processes.[1] Unix-like operating systems (such as Linux) have a similar type of file called a shell script.[2]
DOS batch files have the filename extension .bat. Batch files for other environments may have different extensions, e.g. .cmd or .bat in the Microsoft Windows NT-family of operating systems and OS/2, or .btm in 4DOS and 4NT related shells. The Windows 9x family of operating systems only recognize the .bat extension
Explanation
Batch files are executed by every line being executed in order until the end is reached or something else stops it (such as the key shortcut for terminating batch processing; 'Ctrl' + 'C'). This batch file first turns off the 'echo' with ECHO OFF. This stops the display of input from the batch file and limits the display to output from commands only. Since this command is executed before the input is silenced, the @ symbol is used at the start of the command which stops that command line showing input. Then the ECHO command is used again in the form ECHO Hello World! which outputs the line Hello World!. Then the command ECHO. is used which adds the empty line below Hello World!, using the . so that the command doesn't output the input display's state (ECHO is on. or ECHO is off.) and just outputs an empty line. The . can also be used to prevent the ECHO command from confusing an attempt to output a line beginning with 'ON' or 'OFF' from changing the state of showing input. Then the PAUSE command is used which pauses execution until the user presses a key. The Press any key to continue . . . prompt is output by the command. Lastly, after the user presses a key the command ECHO ONis used which turns the prompt and input on again, so that if the file is executed from the Command Prompt, rather than Windows Explorer then when the execution ends the user can see the prompt again to use normally. After the last line is reached the batch file ends execution automatically. If it was started from the command prompt (by entering the name of the file when in its directory) then the window remains when finished, but when started from Windows Explorer the window automatically closes upon the end of execution
Q 3)How can we upgrade a database from one version to another version with zero or low downtime in 24/7 or RAC environment?
Ans: Rolling upgrade is a zero-downtime method for upgrading the Oracle software.
The term rolling upgrade refers to upgrading different databases or different instances of the same database (in a Real Application Clusters environment) one at a time, without stopping the database.
The advantage of a RAC rolling upgrade is that it enables at least some instances of the RAC installation to be available during the scheduled outage required for patch upgrades. Only the RAC instance that is currently being patched needs to be brought down. The other instances can continue to remain available. This means that the impact on the application downtime required for such scheduled outages is further minimized. Oracle's opatch utility enables the user to apply the patch successively to the different instances of the RAC installation.
Rolling upgrade is available only for patches that have been certified by Oracle to be eligible for rolling upgrades. Typically, patches that can be installed in a rolling upgrade include:
Patches that do not affect the contents of the database such as the data dictionary
Patches not related to RAC internode communication
Patches related to client-side tools such as SQL*PLUS, Oracle utilities, development libraries, and Oracle Net
Patches that do not change shared database resources such as datafile headers, control files, and common header definitions of kernel modules
Rolling upgrade of patches is currently available for one-off patches only. It is not available for patch sets.
Rolling patch upgrades are not available for deployments where the Oracle Database software is shared across the different nodes. This is the case where the Oracle home is on Cluster File System (CFS) or on shared volumes provided by file servers or NFS-mounted drives. The feature is only available where each node has its own copy of the Oracle Database software.
Q 4)What is the difference between delete and truncate(HCL interview question)?
Ans:
Deletes perform normal DML. That is, they take locks on rows, they generate redo (lots of it), and they require segments in the UNDO tablespace. Deletes clear records out of blocks carefully. If a mistake is made a rollback can be issued to restore the records prior to a commit. A delete does not relinquish segment space thus a table in which all records have been deleted retains all of its original blocks.
Truncates are DDL and, in a sense, cheat. A truncate moves the High Water Mark of the table back to zero. No row-level locks are taken, no redo or rollback is generated. All extents bar the initial are de-allocated from the table (if you have MINEXTENTS set to anything other than 1, then that number of extents is retained rather than just the initial). By re-positioning the high water mark, they prevent reading of any table data, so they have the same effect as a delete, but without all the overhead. Just one slight problem: a truncate is a DDL command, so you can't roll it back if you decide you made a mistake. (It's also true that you can't selectively truncate -no "WHERE" clause is permitted, unlike with deletes, of course).
By resetting the High Water Mark, the truncate prevents reading of any table's data, so they it has the same effect as a delete, but without the overhead. There is, however, one aspect of a Truncate that must be kept in mind. Because a Truncate is DDL it issues a COMMIT before it acts and another COMMIT afterward so no rollback of the transaction is possible.
Q 5)When your recovery catalog gets corrupted how you recover your database?(PATNI INTERVIEW QUESTION)
Ans: I answer as per my knowledge that If you have the backup of datafiles and redolog files you can create the control file by using the create control file command and open the database with resetlogs option if required.
To be on the safer side we should backup our recovery catalog also.
The below link might be useful for backing up Recovery catalog:
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmcatdb.htm#CIHFEIIH
Q 6)When your current redolog gets corrupted how you recover your Database?
:(** PATNI INTERVIEW QUESTION **)
Ans:
Recovery From Current Redolog Corruption
1. startup error message
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TEST\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) cannot find file
5.1.2 Current Redolog Corruption
Two case:
A. shutdown normally, no transaction to recovery, recreate log group with 'alter database clear unarchived logfile group n'
B. active transaction exists. database need media recovery. log group need synchronized. there are 2 methods:
a. imcomplete recovery. keep the consistence of database. but this method requires archivelog mode and valid backup.
b. force recovery. but may cause inconsistence.
5.1.2.1 recovery with backup
1. accounting a error when open database
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TEST\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) OS cannot found file
2. check V$log, and notice the current redolog corrupted
SQL> select group#,sequence#,archived,status from v$log;
GROUP# SEQUENCE# ARCHIVED STATUS
---------- ---------- -------- ----------------
1 1 NO CURRENT
2 2 YES INACTIVE
3 3 YES INACTIVE
3. cannot clear the current redolog
SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TEST\REDO01.LOG'
4. copy a full backup and recover database
until scn or until cnacel
recover database until cancel
select auto recover with all the valid archivelogs and redologs, and
recover database until cancel
enter cancel to do a incomplete recovery (that's need recover twice)
Example:
SQL> recover database until cancel;
Auto
...
SQL> recover database until cancel;
Cancel;
5. open database with: alter database open resetlogs
Description:
1. incomplete recovery may cause data lost of current redolog.
2. need archivelog mode and full backup.
3. backup database after recovery.
4. strongly recomment that make log mirror on different disks.
5.1.2.2 force recovery without backup
1. accounting a error
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TEST\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2)
2、notice curruent redolog corrupted
SQL> select group#,sequence#,archived,status from v$log;
GROUP# SEQUENCE# ARCHIVED STATUS
---------- ---------- -------- ----------------
1 1 NO CURRENT
2 2 YES INACTIVE
3 3 YES INACTIVE
3、cannot clear
SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TEST\REDO01.LOG'
4、shutdown database
SQL>shutdown immediate
5、add the following parameter to init.ora
_allow_resetlogs_corruption=TRUE
6、restart database and recover with 'until cancel'
SQL>recover database until cancel;
Cancel
SQL>alter database open resetlogs;
7、full export database after open database successfully
8、shutdown database, remove init parameter
_all_resetlogs_corrupt
9、recreate database
10、import and finish recovery
11、recomment to run
ANALYZE TABLE ...VALIDATE STRUCTURE CASCADE;
Description
1. use this method only when no other method. because it may cause database inconsistent
2. cause data loss, but less than recover with backup. because the data without commit be recovered too.
3. strongly recomment run complete recreation and valication check.
4. should backup database fullly.
5. strongly recomment that make log mirror on different disks, no data loss can be accept on production environment.
Reference http://www.itpub.net/thread-126320-1-1.html
Q 7)What are the parameters responsible for generating Execution Plan? what is db_file_multiblock_read_count?
Ans: The important parameters are:
1)optimizer_mode
2)db_file_multiblock_read_count
DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.
As of Oracle Database 10g release 2, the default value of this parameter is a value that corresponds to the maximum I/O size that can be performed efficiently. This value is platform-dependent and is 1MB for most platforms.Because the parameter is expressed in blocks, it will be set to a value that is equal to the maximum I/O size that can be performed efficiently divided by the standard block size. Note that if the number of sessions is extremely large the multiblock read count value is decreased to avoid the buffer cache getting flooded with too many table scan buffers.
Even though the default value may be a large value, the optimizer will not favor large plans if you do not set this parameter. It would do so only if you explicitly set this parameter to a large value.
Online transaction processing (OLTP) and batch environments typically have values in the range of 4 to 16 for this parameter. DSS and data warehouse environments tend to benefit most from maximizing the value of this parameter. The optimizer is more likely to choose a full table scan over an index if the value of this parameter is high.
The maximum value is the operating system's maximum I/O size expressed as Oracle blocks ((max I/O size)/DB_BLOCK_SIZE). If you set this parameter to a value greater than the maximum, Oracle uses the maximum.
The db_file_multiblock_read_count is Operating system dependent and in turn depends on three things
a)Sequential read time
b)Scattered read speed and
c)CPU consumption
Apart from optimizer_mode & db_file_multiblock_read_count we have parameter like Optimizer_index_cost_adj,Optimizer_index_caching & Parallel_automatic_tuning which helps optimizer to generate execution plan for SQL statements.
Below are some general interview questions which can be useful:
Q 1)What is tablespace and Quota,if i create any user how much quota is allocated to it?How to check the user had been granted unlimited quota for a particular tablespace?(TCS INTERVIEW QUESTION)
Ans: TABLESPACE A tablespace is a logical storage unit within an Oracle database. It is logical because a tablespace is not visible in the file system of the machine on which the database resides. A tablespace, in turn, consists of at least one datafile which, in turn, are physically located in the file system of the server. Btw, a datafile belongs to exactly one tablespace. Each table, index and so on that is stored in an Oracle database belongs to a tablespace. The tablespace builds the bridge between the Oracle database and the filesystem in which the table's or index' data is stored. There are three types of tablespaces in Oracle:
•Permanent tablespaces
•Undo tablespaces
•temporary tablespaces
Quotas on tablespaces
Users can have (space) quotas on tablespaces. This is a means to limit how much space a user uses on a tablespace. This quota can be set using alter user quota... Use DBA_TS_QUOTAS/USER_TS_QUOTAS to find out a user's quota on a tablespace as well as how much he has already occupied.
If we just create a user and query dba_ts_quotas it will result in no rows as given below:
http://forums.oracle.com/forums/thread.jspa?threadID=1046926&tstart=0
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3396797633001
If I get the value -1 in maxbytes column of DBA_TS_QUOTAS it will tell you that we had allocated unlimited quota to a particular tablespace.
Refer the above for more details.This is something interesting.
Q 2) What are batch files?(TCS interview question)
Ans:
In DOS, OS/2, and Microsoft Windows, a batch file is a text file containing a series of commands intended to be executed by the command interpreter. When a batch file is run, the shell program (usually COMMAND.COM or cmd.exe) reads the file and executes its commands, normally line-by-line. Batch files are useful for running a sequence of executables automatically and are often used by system administrators to automate tedious processes.[1] Unix-like operating systems (such as Linux) have a similar type of file called a shell script.[2]
DOS batch files have the filename extension .bat. Batch files for other environments may have different extensions, e.g. .cmd or .bat in the Microsoft Windows NT-family of operating systems and OS/2, or .btm in 4DOS and 4NT related shells. The Windows 9x family of operating systems only recognize the .bat extension
Explanation
Batch files are executed by every line being executed in order until the end is reached or something else stops it (such as the key shortcut for terminating batch processing; 'Ctrl' + 'C'). This batch file first turns off the 'echo' with ECHO OFF. This stops the display of input from the batch file and limits the display to output from commands only. Since this command is executed before the input is silenced, the @ symbol is used at the start of the command which stops that command line showing input. Then the ECHO command is used again in the form ECHO Hello World! which outputs the line Hello World!. Then the command ECHO. is used which adds the empty line below Hello World!, using the . so that the command doesn't output the input display's state (ECHO is on. or ECHO is off.) and just outputs an empty line. The . can also be used to prevent the ECHO command from confusing an attempt to output a line beginning with 'ON' or 'OFF' from changing the state of showing input. Then the PAUSE command is used which pauses execution until the user presses a key. The Press any key to continue . . . prompt is output by the command. Lastly, after the user presses a key the command ECHO ONis used which turns the prompt and input on again, so that if the file is executed from the Command Prompt, rather than Windows Explorer then when the execution ends the user can see the prompt again to use normally. After the last line is reached the batch file ends execution automatically. If it was started from the command prompt (by entering the name of the file when in its directory) then the window remains when finished, but when started from Windows Explorer the window automatically closes upon the end of execution
Q 3)How can we upgrade a database from one version to another version with zero or low downtime in 24/7 or RAC environment?
Ans: Rolling upgrade is a zero-downtime method for upgrading the Oracle software.
The term rolling upgrade refers to upgrading different databases or different instances of the same database (in a Real Application Clusters environment) one at a time, without stopping the database.
The advantage of a RAC rolling upgrade is that it enables at least some instances of the RAC installation to be available during the scheduled outage required for patch upgrades. Only the RAC instance that is currently being patched needs to be brought down. The other instances can continue to remain available. This means that the impact on the application downtime required for such scheduled outages is further minimized. Oracle's opatch utility enables the user to apply the patch successively to the different instances of the RAC installation.
Rolling upgrade is available only for patches that have been certified by Oracle to be eligible for rolling upgrades. Typically, patches that can be installed in a rolling upgrade include:
Patches that do not affect the contents of the database such as the data dictionary
Patches not related to RAC internode communication
Patches related to client-side tools such as SQL*PLUS, Oracle utilities, development libraries, and Oracle Net
Patches that do not change shared database resources such as datafile headers, control files, and common header definitions of kernel modules
Rolling upgrade of patches is currently available for one-off patches only. It is not available for patch sets.
Rolling patch upgrades are not available for deployments where the Oracle Database software is shared across the different nodes. This is the case where the Oracle home is on Cluster File System (CFS) or on shared volumes provided by file servers or NFS-mounted drives. The feature is only available where each node has its own copy of the Oracle Database software.
Q 4)What is the difference between delete and truncate(HCL interview question)?
Ans:
Deletes perform normal DML. That is, they take locks on rows, they generate redo (lots of it), and they require segments in the UNDO tablespace. Deletes clear records out of blocks carefully. If a mistake is made a rollback can be issued to restore the records prior to a commit. A delete does not relinquish segment space thus a table in which all records have been deleted retains all of its original blocks.
Truncates are DDL and, in a sense, cheat. A truncate moves the High Water Mark of the table back to zero. No row-level locks are taken, no redo or rollback is generated. All extents bar the initial are de-allocated from the table (if you have MINEXTENTS set to anything other than 1, then that number of extents is retained rather than just the initial). By re-positioning the high water mark, they prevent reading of any table data, so they have the same effect as a delete, but without all the overhead. Just one slight problem: a truncate is a DDL command, so you can't roll it back if you decide you made a mistake. (It's also true that you can't selectively truncate -no "WHERE" clause is permitted, unlike with deletes, of course).
By resetting the High Water Mark, the truncate prevents reading of any table's data, so they it has the same effect as a delete, but without the overhead. There is, however, one aspect of a Truncate that must be kept in mind. Because a Truncate is DDL it issues a COMMIT before it acts and another COMMIT afterward so no rollback of the transaction is possible.
Q 5)When your recovery catalog gets corrupted how you recover your database?(PATNI INTERVIEW QUESTION)
Ans: I answer as per my knowledge that If you have the backup of datafiles and redolog files you can create the control file by using the create control file command and open the database with resetlogs option if required.
To be on the safer side we should backup our recovery catalog also.
The below link might be useful for backing up Recovery catalog:
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmcatdb.htm#CIHFEIIH
Q 6)When your current redolog gets corrupted how you recover your Database?
:(** PATNI INTERVIEW QUESTION **)
Ans:
Recovery From Current Redolog Corruption
1. startup error message
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TEST\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) cannot find file
5.1.2 Current Redolog Corruption
Two case:
A. shutdown normally, no transaction to recovery, recreate log group with 'alter database clear unarchived logfile group n'
B. active transaction exists. database need media recovery. log group need synchronized. there are 2 methods:
a. imcomplete recovery. keep the consistence of database. but this method requires archivelog mode and valid backup.
b. force recovery. but may cause inconsistence.
5.1.2.1 recovery with backup
1. accounting a error when open database
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TEST\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) OS cannot found file
2. check V$log, and notice the current redolog corrupted
SQL> select group#,sequence#,archived,status from v$log;
GROUP# SEQUENCE# ARCHIVED STATUS
---------- ---------- -------- ----------------
1 1 NO CURRENT
2 2 YES INACTIVE
3 3 YES INACTIVE
3. cannot clear the current redolog
SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TEST\REDO01.LOG'
4. copy a full backup and recover database
until scn or until cnacel
recover database until cancel
select auto recover with all the valid archivelogs and redologs, and
recover database until cancel
enter cancel to do a incomplete recovery (that's need recover twice)
Example:
SQL> recover database until cancel;
Auto
...
SQL> recover database until cancel;
Cancel;
5. open database with: alter database open resetlogs
Description:
1. incomplete recovery may cause data lost of current redolog.
2. need archivelog mode and full backup.
3. backup database after recovery.
4. strongly recomment that make log mirror on different disks.
5.1.2.2 force recovery without backup
1. accounting a error
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TEST\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2)
2、notice curruent redolog corrupted
SQL> select group#,sequence#,archived,status from v$log;
GROUP# SEQUENCE# ARCHIVED STATUS
---------- ---------- -------- ----------------
1 1 NO CURRENT
2 2 YES INACTIVE
3 3 YES INACTIVE
3、cannot clear
SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TEST\REDO01.LOG'
4、shutdown database
SQL>shutdown immediate
5、add the following parameter to init
_allow_resetlogs_corruption=TRUE
6、restart database and recover with 'until cancel'
SQL>recover database until cancel;
Cancel
SQL>alter database open resetlogs;
7、full export database after open database successfully
8、shutdown database, remove init parameter
_all_resetlogs_corrupt
9、recreate database
10、import and finish recovery
11、recomment to run
ANALYZE TABLE ...VALIDATE STRUCTURE CASCADE;
Description
1. use this method only when no other method. because it may cause database inconsistent
2. cause data loss, but less than recover with backup. because the data without commit be recovered too.
3. strongly recomment run complete recreation and valication check.
4. should backup database fullly.
5. strongly recomment that make log mirror on different disks, no data loss can be accept on production environment.
Reference http://www.itpub.net/thread-126320-1-1.html
Q 7)What are the parameters responsible for generating Execution Plan? what is db_file_multiblock_read_count?
Ans: The important parameters are:
1)optimizer_mode
2)db_file_multiblock_read_count
DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.
As of Oracle Database 10g release 2, the default value of this parameter is a value that corresponds to the maximum I/O size that can be performed efficiently. This value is platform-dependent and is 1MB for most platforms.Because the parameter is expressed in blocks, it will be set to a value that is equal to the maximum I/O size that can be performed efficiently divided by the standard block size. Note that if the number of sessions is extremely large the multiblock read count value is decreased to avoid the buffer cache getting flooded with too many table scan buffers.
Even though the default value may be a large value, the optimizer will not favor large plans if you do not set this parameter. It would do so only if you explicitly set this parameter to a large value.
Online transaction processing (OLTP) and batch environments typically have values in the range of 4 to 16 for this parameter. DSS and data warehouse environments tend to benefit most from maximizing the value of this parameter. The optimizer is more likely to choose a full table scan over an index if the value of this parameter is high.
The maximum value is the operating system's maximum I/O size expressed as Oracle blocks ((max I/O size)/DB_BLOCK_SIZE). If you set this parameter to a value greater than the maximum, Oracle uses the maximum.
The db_file_multiblock_read_count is Operating system dependent and in turn depends on three things
a)Sequential read time
b)Scattered read speed and
c)CPU consumption
Apart from optimizer_mode & db_file_multiblock_read_count we have parameter like Optimizer_index_cost_adj,Optimizer_index_caching & Parallel_automatic_tuning which helps optimizer to generate execution plan for SQL statements.
MY RMAN practices
RMAN BACKUP taken on different locations by using 'format' keyword. Here is how we can take rman backup in different locations:
RUN
{
DELETE NOPROMPT OBSOLETE;
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
ALLOCATE CHANNEL C1 DEVICE TYPE DISK MAXPIECESIZE 100M format 'C:\RMANBKP\%U';
ALLOCATE CHANNEL C2 DEVICE TYPE DISK MAXPIECESIZE 100M format 'C:\RMANBKP\%U';
ALLOCATE CHANNEL C3 DEVICE TYPE DISK MAXPIECESIZE 100M format 'C:\RMANBKP\%U';
BACKUP INCREMENTAL LEVEL 0 DATABASE TAG Weekly_Friday_0_Hotpsms_bkp;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
ALLOCATE CHANNEL B1 DEVICE TYPE DISK MAXPIECESIZE 100M format 'C:\RMANBKP\%U';
ALLOCATE CHANNEL B2 DEVICE TYPE DISK MAXPIECESIZE 100M format 'C:\RMANBKP\%U';
BACKUP DATABASE ARCHIVELOG ALL DELETE ALL INPUT TAG Weekly_Friday_0_Hotpsms_bkp;
BACKUP VALIDATE DATABASE;
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
}
Best regards,
Rafi.
RUN
{
DELETE NOPROMPT OBSOLETE;
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
ALLOCATE CHANNEL C1 DEVICE TYPE DISK MAXPIECESIZE 100M format 'C:\RMANBKP\%U';
ALLOCATE CHANNEL C2 DEVICE TYPE DISK MAXPIECESIZE 100M format 'C:\RMANBKP\%U';
ALLOCATE CHANNEL C3 DEVICE TYPE DISK MAXPIECESIZE 100M format 'C:\RMANBKP\%U';
BACKUP INCREMENTAL LEVEL 0 DATABASE TAG Weekly_Friday_0_Hotpsms_bkp;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
ALLOCATE CHANNEL B1 DEVICE TYPE DISK MAXPIECESIZE 100M format 'C:\RMANBKP\%U';
ALLOCATE CHANNEL B2 DEVICE TYPE DISK MAXPIECESIZE 100M format 'C:\RMANBKP\%U';
BACKUP DATABASE ARCHIVELOG ALL DELETE ALL INPUT TAG Weekly_Friday_0_Hotpsms_bkp;
BACKUP VALIDATE DATABASE;
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
}
Best regards,
Rafi.
Friday, March 19, 2010
BUFFER BUSY WAITS AND REMEDIES
UNDERSTANDING BUFFER BUSY WAITS AND REMEDIES
Hi ,
Buffer busy wait comes in top 5 wait events.Below is the detail of dealing with them.
The Buffer Busy Waits Oracle metric occur when an Oracle session needs to access a block in the buffer cache, but cannot because the buffer copy of the data block is locked. This buffer busy wait condition can happen for either of the following reasons:
The block is being read into the buffer by another session, so the waiting session must wait for the block read to complete.
Another session has the buffer block locked in a mode that is incompatible with the waiting session's request.
Because buffer busy waits are due to contention between particular blocks, there's nothing you can do until you know which blocks are in conflict and why the conflicts are occurring. Tuning therefore involves identifying and eliminating the cause of the block contention.
One of the most confounding problems with Oracle is the resolution of buffer busy wait events. Buffer busy waits are common in an I/O-bound Oracle system, as evidenced by any system with read (sequential/scattered) waits in the top-five waits in the Oracle STATSPACK report, like this:
Top 5 Timed Events
% Total
Event Waits Time (s) Ela Time
--------------------------- ------------ ----------- -----------
db file sequential read 2,598 7,146 48.54
db file scattered read 25,519 3,246 22.04
library cache load lock 673 1,363 9.26
CPU time 2,154 934 7.83
log file parallel write 19,157 837 5.68
The main way to reduce buffer busy waits is to reduce the total I/O on the system. This can be done by tuning the SQL to access rows with fewer block reads (i.e., by adding indexes). Even if we have a huge db_cache_size, we may still see buffer busy waits, and increasing the buffer size won't help.
The resolution of a "buffer busy wait" events is one of the most confounding problems with Oracle. In an I/O-bound Oracle system, buffer busy waits are common, as evidenced by any system with read (sequential/scattered) waits in the top-five waits.
Reducing buffer busy waits reduces the total I/O on the system. This can be accomplished by tuning the SQL to access rows with fewer block reads by adding indexes, adjusting the database writer or adding freelists to tables and indexes. Even if there is a huge db_cache_size , the DBA may still see buffer busy waits and, in this case, increasing the buffer size will not help.
The most common remedies for high buffer busy waits include database writer (DBWR) contention tuning, adding freelists (or ASSM), and adding missing indexes
Best regards,
Rafi.
Hi ,
Buffer busy wait comes in top 5 wait events.Below is the detail of dealing with them.
The Buffer Busy Waits Oracle metric occur when an Oracle session needs to access a block in the buffer cache, but cannot because the buffer copy of the data block is locked. This buffer busy wait condition can happen for either of the following reasons:
The block is being read into the buffer by another session, so the waiting session must wait for the block read to complete.
Another session has the buffer block locked in a mode that is incompatible with the waiting session's request.
Because buffer busy waits are due to contention between particular blocks, there's nothing you can do until you know which blocks are in conflict and why the conflicts are occurring. Tuning therefore involves identifying and eliminating the cause of the block contention.
One of the most confounding problems with Oracle is the resolution of buffer busy wait events. Buffer busy waits are common in an I/O-bound Oracle system, as evidenced by any system with read (sequential/scattered) waits in the top-five waits in the Oracle STATSPACK report, like this:
Top 5 Timed Events
% Total
Event Waits Time (s) Ela Time
--------------------------- ------------ ----------- -----------
db file sequential read 2,598 7,146 48.54
db file scattered read 25,519 3,246 22.04
library cache load lock 673 1,363 9.26
CPU time 2,154 934 7.83
log file parallel write 19,157 837 5.68
The main way to reduce buffer busy waits is to reduce the total I/O on the system. This can be done by tuning the SQL to access rows with fewer block reads (i.e., by adding indexes). Even if we have a huge db_cache_size, we may still see buffer busy waits, and increasing the buffer size won't help.
The resolution of a "buffer busy wait" events is one of the most confounding problems with Oracle. In an I/O-bound Oracle system, buffer busy waits are common, as evidenced by any system with read (sequential/scattered) waits in the top-five waits.
Reducing buffer busy waits reduces the total I/O on the system. This can be accomplished by tuning the SQL to access rows with fewer block reads by adding indexes, adjusting the database writer or adding freelists to tables and indexes. Even if there is a huge db_cache_size , the DBA may still see buffer busy waits and, in this case, increasing the buffer size will not help.
The most common remedies for high buffer busy waits include database writer (DBWR) contention tuning, adding freelists (or ASSM), and adding missing indexes
Best regards,
Rafi.
RAC QUESTIONS AND ANSWERS
Q 1)How do we find the location of OCR and voting disks?
Ans: The location of OCR and Voting disks can be found as follows:
OCR location:
[oracle@dbcl1n1 AUCS1 ~]$ cat /etc/oracle/ocr.locO/P:
ocrconfig_loc=/export/ocw/oracle/ocr1
ocrmirrorconfig_loc=/export/ocw/oracle/ocr2
local_only=FALSE
Voting disk location:
[oracle@dbcl1n1 AUCS1 ~]$ cd $ORA_CRS_HOME/bin
[oracle@dbcl1n1 AUCS1 bin]$ ./crsctl query css votedisk
0. 0 /export/ocw/oracle/vote1
1. 0 /export/ocw/oracle/vote2
2. 0 /export/ocw/oracle/vote3
Q 3) What is cache fusion?
Ans :Cache fusion is the block shipping from one instance to another instance in a RAC environment.Due to Cache Fusion and the elimination of disk writes that occur when other instances request blocks for modifications, the performance overhead to manage shared data between instances is greatly diminished. Not only do Cache Fusion's concurrency controls greatly improve performance, but they also reduce the administrative effort for Real Application Clusters environments.
Cache Fusion addresses several types of concurrency as described under the following headings:
Concurrent Reads on Multiple Nodes
Concurrent Reads and Writes on Different Nodes
Concurrent Writes on Different Nodes
Q 4)Which process is responsible for cache fusion mechanism?
Ans:Global cache service(GCS)
A read request from an instance for a block that was modified by another instance and not yet written to disk can be a request for either the current version of the block or for a read-consistent version. In either case, the Global Cache Service Processes (LMSn) transfer the block from the holding instance's cache to the requesting instance's cache over the interconnect.
Q 5)If we perform a DML acivity in a 2 node RAC environment,if that node disconnect due to some reason,What will be the result?
Ans: The DML statement will execute successfully since Database is there on the shared device.
Note: Thanks Junad for noticing the above answer and helping me in clarifying that in actual:
We neeed to write the callout functions using FAST APPLICATION NOTIFICATION (FAN) Notifications FOR RAC.The callout can be written in OCI, for example JAVA for dml failover to work.
http://download.oracle.com/docs/cd/B19306_01/rac.102/b14197/hafeats.htm
If you are using TAF (Transparent Application failover) with RAC Than only: session(alter session is not) failover,select failover,pre-connect and Basic Failover are supported.
http://download.oracle.com/docs/cd/B28359_01/network.111/b28316/advcfg.htm#NETAG338
http://www.scribd.com/doc/19211546/Failover-for-Rac
In Oracle 11g rel2:
http://www.oracle.com/technetwork/database/app-failover-oracle-database-11g-173323.pdf
Q 6)How do we backup OCR and Voting disk?
Ans:VOTING DISK BACKUP:
To make a backup copy of the voting disk, use the Linux dd command. Perform this operation on every voting disk as needed where voting_disk_name is the name of the active voting disk and backup_file_name is the name of the file to which you want to back up the voting disk contents:
$dd if=voting_disk_name of=backup_file_name
If your voting disk is stored on a raw device, use the device name in place of voting_disk_name. For example:
dd if=/dev/sdd1 of=/tmp/voting.dmp
When you use the dd command for making backups of the voting disk, the backup can be performed while the Cluster Ready Services (CRS) process is active; you do not need to stop the crsd.bin process before taking a backup of the voting disk
OCR BACKUP:
Viewing Available OCR Backups
To find the most recent backup of the OCR, on any node in the cluster, use the following command:
$ocrconfig -showbackup
Backing Up the OCR
Because of the importance of OCR information, Oracle recommends that you use the ocrconfig tool to make copies of the automatically created backup files at least once a day.
In addition to using the automatically created OCR backup files, you should also export the OCR contents to a file before and after making significant configuration changes, such as adding or deleting nodes from your environment, modifying Oracle Clusterware resources, or creating a database. Exporting the OCR contents to a file lets you restore the OCR if your configuration changes cause errors. For example, if you have unresolvable configuration problems, or if you are unable to restart your cluster database after such changes, then you can restore your configuration by importing the saved OCR content from the valid configuration.
To export the contents of the OCR to a file, use the following command, where backup_file_name is the name of the OCR backup file you want to create:
$ocrconfig -exportbackup_file_name
Q 7)What is global cache service,global enque service and global resource directory?
Ans: Global Cache Service
GCS is the main controlling process that implements Cache Fusion. GCS tracks the location and the status (mode and role) of the data blocks, as well as the access privileges of various instances. GCS is the mechanism, which guarantees the data integrity by employing global access levels. GCS maintains the block modes for data blocks in the global role. It is also responsible for block transfers between the instances. Upon a request from an Instance GCS organizes the block shipping and appropriate lock mode conversions. The Global Cache Service is implemented by various background processes, such as the Global Cache Service Processes (LMSn) and Global Enqueue Service Daemon (LMD).
Global Enqueue Service
The Global Enqueue Service (GES) manages or tracks the status of all the Oracle enqueuing mechanisms. This involves all non Cache-fusion intra-instance operations. GES performs concurrency control on dictionary cache locks, library cache locks and the transactions. GES does this operation for resources that are accessed by more than one instance.
GES/GCS Areas
GES and GCS have the memory structures associated with global resources. It is distributed across all instances in a cluster. This area is located in the variable or shared pool section of the SGA. The list below shows the additions.
Global Resource Directory Global Resource Directory (GRD) is the internal database that records and stores the current status of the data blocks. Whenever a block is transferred out of a local cache to another instance’s cache, the GRD is updated. The following resources information is available in GRD.
* Data Block Identifiers (DBA)
* Location of most current version
* Modes of the data blocks ( (N)Null, (S)Shared, (X)Exclusive )
*The Roles of the data blocks (local or global) held by each instance
*Buffer caches on multiple nodes in the cluster
Ans: The location of OCR and Voting disks can be found as follows:
OCR location:
[oracle@dbcl1n1 AUCS1 ~]$ cat /etc/oracle/ocr.locO/P:
ocrconfig_loc=/export/ocw/oracle/ocr1
ocrmirrorconfig_loc=/export/ocw/oracle/ocr2
local_only=FALSE
Voting disk location:
[oracle@dbcl1n1 AUCS1 ~]$ cd $ORA_CRS_HOME/bin
[oracle@dbcl1n1 AUCS1 bin]$ ./crsctl query css votedisk
0. 0 /export/ocw/oracle/vote1
1. 0 /export/ocw/oracle/vote2
2. 0 /export/ocw/oracle/vote3
Q 3) What is cache fusion?
Ans :Cache fusion is the block shipping from one instance to another instance in a RAC environment.Due to Cache Fusion and the elimination of disk writes that occur when other instances request blocks for modifications, the performance overhead to manage shared data between instances is greatly diminished. Not only do Cache Fusion's concurrency controls greatly improve performance, but they also reduce the administrative effort for Real Application Clusters environments.
Cache Fusion addresses several types of concurrency as described under the following headings:
Concurrent Reads on Multiple Nodes
Concurrent Reads and Writes on Different Nodes
Concurrent Writes on Different Nodes
Q 4)Which process is responsible for cache fusion mechanism?
Ans:Global cache service(GCS)
A read request from an instance for a block that was modified by another instance and not yet written to disk can be a request for either the current version of the block or for a read-consistent version. In either case, the Global Cache Service Processes (LMSn) transfer the block from the holding instance's cache to the requesting instance's cache over the interconnect.
Q 5)If we perform a DML acivity in a 2 node RAC environment,if that node disconnect due to some reason,What will be the result?
Ans: The DML statement will execute successfully since Database is there on the shared device.
Note: Thanks Junad for noticing the above answer and helping me in clarifying that in actual:
We neeed to write the callout functions using FAST APPLICATION NOTIFICATION (FAN) Notifications FOR RAC.The callout can be written in OCI, for example JAVA for dml failover to work.
http://download.oracle.com/docs/cd/B19306_01/rac.102/b14197/hafeats.htm
If you are using TAF (Transparent Application failover) with RAC Than only: session(alter session is not) failover,select failover,pre-connect and Basic Failover are supported.
http://download.oracle.com/docs/cd/B28359_01/network.111/b28316/advcfg.htm#NETAG338
http://www.scribd.com/doc/19211546/Failover-for-Rac
In Oracle 11g rel2:
http://www.oracle.com/technetwork/database/app-failover-oracle-database-11g-173323.pdf
Q 6)How do we backup OCR and Voting disk?
Ans:VOTING DISK BACKUP:
To make a backup copy of the voting disk, use the Linux dd command. Perform this operation on every voting disk as needed where voting_disk_name is the name of the active voting disk and backup_file_name is the name of the file to which you want to back up the voting disk contents:
$dd if=voting_disk_name of=backup_file_name
If your voting disk is stored on a raw device, use the device name in place of voting_disk_name. For example:
dd if=/dev/sdd1 of=/tmp/voting.dmp
When you use the dd command for making backups of the voting disk, the backup can be performed while the Cluster Ready Services (CRS) process is active; you do not need to stop the crsd.bin process before taking a backup of the voting disk
OCR BACKUP:
Viewing Available OCR Backups
To find the most recent backup of the OCR, on any node in the cluster, use the following command:
$ocrconfig -showbackup
Backing Up the OCR
Because of the importance of OCR information, Oracle recommends that you use the ocrconfig tool to make copies of the automatically created backup files at least once a day.
In addition to using the automatically created OCR backup files, you should also export the OCR contents to a file before and after making significant configuration changes, such as adding or deleting nodes from your environment, modifying Oracle Clusterware resources, or creating a database. Exporting the OCR contents to a file lets you restore the OCR if your configuration changes cause errors. For example, if you have unresolvable configuration problems, or if you are unable to restart your cluster database after such changes, then you can restore your configuration by importing the saved OCR content from the valid configuration.
To export the contents of the OCR to a file, use the following command, where backup_file_name is the name of the OCR backup file you want to create:
$ocrconfig -exportbackup_file_name
Q 7)What is global cache service,global enque service and global resource directory?
Ans: Global Cache Service
GCS is the main controlling process that implements Cache Fusion. GCS tracks the location and the status (mode and role) of the data blocks, as well as the access privileges of various instances. GCS is the mechanism, which guarantees the data integrity by employing global access levels. GCS maintains the block modes for data blocks in the global role. It is also responsible for block transfers between the instances. Upon a request from an Instance GCS organizes the block shipping and appropriate lock mode conversions. The Global Cache Service is implemented by various background processes, such as the Global Cache Service Processes (LMSn) and Global Enqueue Service Daemon (LMD).
Global Enqueue Service
The Global Enqueue Service (GES) manages or tracks the status of all the Oracle enqueuing mechanisms. This involves all non Cache-fusion intra-instance operations. GES performs concurrency control on dictionary cache locks, library cache locks and the transactions. GES does this operation for resources that are accessed by more than one instance.
GES/GCS Areas
GES and GCS have the memory structures associated with global resources. It is distributed across all instances in a cluster. This area is located in the variable or shared pool section of the SGA. The list below shows the additions.
Global Resource Directory Global Resource Directory (GRD) is the internal database that records and stores the current status of the data blocks. Whenever a block is transferred out of a local cache to another instance’s cache, the GRD is updated. The following resources information is available in GRD.
* Data Block Identifiers (DBA)
* Location of most current version
* Modes of the data blocks ( (N)Null, (S)Shared, (X)Exclusive )
*The Roles of the data blocks (local or global) held by each instance
*Buffer caches on multiple nodes in the cluster
Alert Log Scraping with Oracle's ADRCI Utility
Alert Log Scraping with Oracle's ADRCI Utility
By James Koopmann
Oracle’s new ADR with command interface shows promise for database administrators who like to script their own solution for quickly scraping the alert log and automatically looking for errors.
Oracle’s alert log is a chronological log of important messages and errors that are generated using an Oracle database. Often times it is the first place DBAs look to see if there are errors being generated or checking to see if the Oracle database is healthy, or at least not spitting out errors and complaining about something. If you have yet to pry open the hood of Oracle and have never seen an alert log, below is a snippet of some of the errors (ORA-) and messages that Oracle keeps in the alert log. Notice that I’ve included two different snippets. One is what most DBAs are familiar with, a pure text file, and one that is new to many, an XML alert log. Both are generated but the prior is, as suggested by Oracle, not being maintained and not receiving the same messaging as the XML alert log.
From the text alert log:
Wed Dec 09 14:55:16 2009
Errors in file /opt/app/oracle/diag/rdbms/db11/db11/trace/db11_ora_14798.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/oradata/db11/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
From the XML alert log:
module='sqlplus@ludwig (TNS V1-V3)' pid='14798'>
Errors in file /opt/app/oracle/diag/rdbms/db11/db11/trace/db11_ora_14798.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/oradata/db11/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Clearly, they are very similar except for the XML tags. These XML tags cause some interesting problems for those of us that like to scrape the alert log with system utilities. It isn’t as easy as pointing the scripts at a new file out on disk. Thankfully Oracle has provided a utility (ADRCI) that can be used to extract diagnostic information generated from an Oracle database, including the alert log.
When used interactively by issuing the “adrci” command from a system prompt, which is often the way it is used, a user can easily extract alert log detail with very simple commands such as the following:
SHOW ALERT –TAIL; To display the last 10 lines of the alert log.
SHOW ALERT -TAIL 50; To display the last 50 lines of the alert log.
SHOW ALERT -P "MESSAGE_TEXT LIKE '%ORA-%'"; search for alert lines that have ORA- in them.
However, like many other DBA tasks we clearly hate logging into a database server and issuing commands. What is more expected is to have some set of commands that can be executed through a schedule to check and alert us, such as by email. Additionally if we schedule something to check on a regular interval, say 10 minutes, it becomes a better and more reliable monitoring methodology then having a DBA waste time logging into 10s or 100s of database servers every 10 minutes. Therefore, and to not belabor the point, here are the scripts that I’ve started to develop. I hope that you can use them:
This solution makes use of a very simple directory structure under the oracle user’s home directory. Here are the directories/files used:
/home/oracle/alert
This is where the main script and supporting files exist
/home/oracle/alert/CHKALERT
This file contains ORA- errors, or any other errors we are interested in checking for in the alert log
/home/oracle/alert/CHKALERT.sh
This is the script that will need to be executed
/home/oracle/alert/CHKALERT.curr
File that contains information on the last time the alert log was scanned; containing information about the errors found and put into the CHKALERT.yyyy-mm-dd files
/home/oracle/alert/lst
The directory where output is generated
/home/oracle/alert/lst/CHKALERT.adrci
Is dynamically generated during runtime and will be used as a script to the ADRCI utility
/home/oracle/alert/lst/CHKALERT.lst
Is dynamically generated during runtime and will be used as output to a SQL*Plus call to get some variables to be used when calling ADRCI
/home/oracle/alert/lst/CHKALERT.sql
Is the SQL*Plus SQL used to generate the CHKALERT.lst output
/home/oracle/alert/lst/CHKALERT.tmp
Contains header output from the alert log to get a timezone
/home/oracle/alert/lst/CHKALERT.yyyy-mm-dd
will be files generated for each day that contains ORA- errors for that day
Below is the script itself. To use, just execute by typing in at the command prompt:
[oracle@ludwig alert]$ ./CHKALERT.shThe file you will be interested in looking at, if you’ve setup the /home/oracle/alert/CHKALERT file properly to look for specific ORA errors, will be the /home/oracle/alert/lst/CHKALERT.log file. This file contains each of the ORA errors found for the last run and looks like the following:
CHKALERT.log
::::::::::::::
001:2010:03:04:02:32:29:ORA-00312: online log 1 thread 1: '/oradata/db11/redo01.log'
001:2010:03:04:02:32:29:ORA-00312: online log 2 thread 1: '/oradata/db11/redo02.log'
001:2010:03:04:02:32:29:ORA-00312: online log 3 thread 1: '/oradata/db11/redo03.log'
To fully automate the process, not covered in this article, would be to interrogate this file and if it contains something, it should be sent to the appropriate DBAs to figure out what to do with the errors found. Have fun with the script. Change as you see fit and begin to take advantage of Oracle new ADRCI utility for scraping the alert logs.
#!/bin/sh
#--File: CHKALERT.sh
#--setup
PGM="CHKALERT"
ALRT=$HOME/alert
LST=${ALRT}/lst
LOG=${LST}/${PGM}.log
CURR=${ALRT}/${PGM}.curr
#--Unix environment variables
ECHO=echo; export ECHO
CAT=/bin/cat; export CAT
RM=/bin/rm; export RM
TOUCH=/bin/touch; export TOUCH
GREP=/bin/grep; export GREP
AWK=/bin/awk; export AWK
WC=/usr/bin/wc; export WC
TAIL=/usr/bin/tail; export TAIL
HEAD=/usr/bin/head; export HEAD
SUM=/usr/bin/sum; export SUM
#--Oracle environment variables
ORACLE_SID=db11; export ORACLE_SID
ORACLE_HOME=`${GREP} ${ORACLE_SID}: /etc/oratab | ${AWK} -F: '{print $2}'`; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH; export PATH
#--code
start=`date "+%Y:%m:%d:%H:%M:%S"`
${RM} ${LOG}
${TOUCH} ${LOG}
#--execute SQL to get some diagnostic variables
echo "set echo off" > ${LST}/${PGM}.sql
echo "set feedback off" >> ${LST}/${PGM}.sql
echo "set heading off" >> ${LST}/${PGM}.sql
echo "set linesize 40" >> ${LST}/${PGM}.sql
echo "set pagesize 55" >> ${LST}/${PGM}.sql
echo "set verify off" >> ${LST}/${PGM}.sql
echo "set linesize 300" >> ${LST}/${PGM}.sql
echo "SELECT 'homepath:'||replace(homepath.value,adrbase.value||'/','')" >> ${LST}/${PGM}.sql
echo " FROM v\$diag_info homepath, v\$diag_info adrbase" >> ${LST}/${PGM}.sql
echo " WHERE homepath.name = 'ADR Home'" >> ${LST}/${PGM}.sql
echo " AND adrbase.name = 'ADR Base';" >> ${LST}/${PGM}.sql
echo "SELECT 'day:'||to_char(sysdate ,'yyyy-mm-dd') FROM dual;" >> ${LST}/${PGM}.sql
echo "SELECT 'nextday:'||to_char(sysdate+1,'yyyy-mm-dd') FROM dual;" >> ${LST}/${PGM}.sql
echo "SELECT 'prevday:'||to_char(sysdate-1,'yyyy-mm-dd') FROM dual;" >> ${LST}/${PGM}.sql
echo "exit" >> ${LST}/${PGM}.sql
sqlplus -s '/as sysdba' @${LST}/${PGM}.sql > ${LST}/${PGM}.lst
#-- get diag information variables just queried from the database
homepath=`${GREP} homepath ${LST}/${PGM}.lst | ${AWK} -F":" '{print $2}'`
day=`${GREP} "^day" ${LST}/${PGM}.lst | ${AWK} -F":" '{print $2}'`
nextday=`${GREP} nextday ${LST}/${PGM}.lst | ${AWK} -F":" '{print $2}'`
prevday=`${GREP} prevday ${LST}/${PGM}.lst | ${AWK} -F":" '{print $2}'`
#-- get the timezone from the alert log (safest place to get)
#-- the proper timezone is needed to properly filter the alert log for date ranges you
#-- want to look at
echo "set echo off" > ${LST}/${PGM}.adrci
echo "set termout off" >> ${LST}/${PGM}.adrci
echo "set homepath ${homepath}" >> ${LST}/${PGM}.adrci
echo "spool ${LST}/${PGM}.tmp" >> ${LST}/${PGM}.adrci
echo "show alert -tail 1" >> ${LST}/${PGM}.adrci
echo "spool off" >> ${LST}/${PGM}.adrci
adrci script=${LST}/${PGM}.adrci 1>/dev/null 2>/dev/null
timezone=`${HEAD} -1 ${LST}/${PGM}.tmp | ${AWK} -F" " '{print $3}'`
#-- extract alert log errors for the current day (today) and previous day (yesterday)
#-- previous day alerts will be used if the current file has yesterday's day as last day;
#-- meaning that we have had a switch to a new day and might have errors still to
#-- process from the previous day
echo "set echo off" > ${LST}/${PGM}.adrci
echo "set termout off" >> ${LST}/${PGM}.adrci
echo "set homepath ${homepath}" >> ${LST}/${PGM}.adrci
echo "spool ${LST}/${PGM}.${day}" >> ${LST}/${PGM}.adrci
echo "show alert -P \"ORIGINATING_TIMESTAMP BETWEEN '${day} 00:00:00.000000 ${timezone}' AND
'${nextday} 00:00:00.000000 ${timezone}' AND MESSAGE_TEXT LIKE '%ORA-%'\" -term" >>
${LST}/${PGM}.adrci
echo "spool off" >> ${LST}/${PGM}.adrci
echo "spool ${LST}/${PGM}.${prevday}" >> ${LST}/${PGM}.adrci
echo "show alert -P \"ORIGINATING_TIMESTAMP BETWEEN '${prevday} 00:00:00.000000 ${timezone}' AND
'${day} 00:00:00.000000 ${timezone}' AND MESSAGE_TEXT LIKE '%ORA-%'\" -term" >>
${LST}/${PGM}.adrci
echo "spool off" >> ${LST}/${PGM}.adrci
adrci script=${LST}/${PGM}.adrci 1>/dev/null 2>/dev/null
#-- get current contents of the current file
#-- default to current day if no current file
if [ -r "${CURR}" ]
then
#-- if the current exists then get the information it contains
daychecksum=`${GREP} day ${CURR} | ${AWK} -F":" '{print $2}'`
daylastline=`${GREP} day ${CURR} | ${AWK} -F":" '{print $3}'`
daylastday=`${GREP} day ${CURR} | ${AWK} -F":" '{print $4}'`
else
#-- if the current does not exist then default to today
daychecksum=0
daylastline=3
daylastday=${day}
fi
#-- set the days to search through for alerts
#-- if last day in current file was yesterday then include previous day
#-- if last day in current file is not yesterday then just scan today's alerts
if [ "${daylastday}" = "${prevday}" ]
then
alertdays="${prevday} ${day}"
else
alertdays="${day}"
fi
#-- for each of the days to scan for alerts
for theday in ${alertdays}
do
#-- check alert errors for the last day.
if [ -r "${LST}/${PGM}.${theday}" ]
then
#-- If the checksum generated is DIFFERENT we should start reporting from the top.
#--
#-- If the checksum generated is the SAME we should start reporting from end of
#-- the previously generated output.
new_daychecksum=`${HEAD} -4 ${LST}/${PGM}.${theday} | ${SUM} | ${AWK} '{print $1}'`
if [ ${new_daychecksum} -ne ${daychecksum} ]
then
daychecksum=${new_daychecksum}
daylastline=3
fi
#-- get the number of lines in the generated errors so we can report to the
#-- end of the file and we know where to start next time.
new_daylastline=`${WC} -l ${LST}/${PGM}.${theday} | ${AWK} -F" " '{print $1}'`
#-- if the number of lines in the output is 3 then there are no errors found.
if [ ${new_daylastline} -ne 3 ]
then
#-- if number of lines in extracted alerts is the same as last time then no new alerts
if [ ${new_daylastline} -ne ${daylastline} ]
then
#-- find the line to begin reporting new alerts from
fromline=`expr ${new_daylastline} - ${daylastline}`
#-- produce alert lines for alerts defined in file CHKALERT
${TAIL} -${fromline} ${LST}/${PGM}.${theday} |
while read LINE
do
for ORAS in `${CAT} ${ALRT}/CHKALERT`
do
ora=`${ECHO} ${LINE} | ${GREP} ${ORAS}`
if [ $? -eq 0 ]
then
#-- you might want to do something here
#-- that is specific to certain ORA- errors
err="001"
echo "${err}:${start}:${LINE}" >> ${LOG}
fi
done
done
fi
fi
daylastline=${new_daylastline}
#-- update the current file only if the day being processed is current day
if [ "${theday}" = "${day}" ]
then
${ECHO} "day:"${daychecksum}":"${daylastline}":"${day} > ${CURR}
fi
fi
done
By James Koopmann
Oracle’s new ADR with command interface shows promise for database administrators who like to script their own solution for quickly scraping the alert log and automatically looking for errors.
Oracle’s alert log is a chronological log of important messages and errors that are generated using an Oracle database. Often times it is the first place DBAs look to see if there are errors being generated or checking to see if the Oracle database is healthy, or at least not spitting out errors and complaining about something. If you have yet to pry open the hood of Oracle and have never seen an alert log, below is a snippet of some of the errors (ORA-) and messages that Oracle keeps in the alert log. Notice that I’ve included two different snippets. One is what most DBAs are familiar with, a pure text file, and one that is new to many, an XML alert log. Both are generated but the prior is, as suggested by Oracle, not being maintained and not receiving the same messaging as the XML alert log.
From the text alert log:
Wed Dec 09 14:55:16 2009
Errors in file /opt/app/oracle/diag/rdbms/db11/db11/trace/db11_ora_14798.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/oradata/db11/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
From the XML alert log:
module='sqlplus@ludwig (TNS V1-V3)' pid='14798'>
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/oradata/db11/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Clearly, they are very similar except for the XML tags. These XML tags cause some interesting problems for those of us that like to scrape the alert log with system utilities. It isn’t as easy as pointing the scripts at a new file out on disk. Thankfully Oracle has provided a utility (ADRCI) that can be used to extract diagnostic information generated from an Oracle database, including the alert log.
When used interactively by issuing the “adrci” command from a system prompt, which is often the way it is used, a user can easily extract alert log detail with very simple commands such as the following:
SHOW ALERT –TAIL; To display the last 10 lines of the alert log.
SHOW ALERT -TAIL 50; To display the last 50 lines of the alert log.
SHOW ALERT -P "MESSAGE_TEXT LIKE '%ORA-%'"; search for alert lines that have ORA- in them.
However, like many other DBA tasks we clearly hate logging into a database server and issuing commands. What is more expected is to have some set of commands that can be executed through a schedule to check and alert us, such as by email. Additionally if we schedule something to check on a regular interval, say 10 minutes, it becomes a better and more reliable monitoring methodology then having a DBA waste time logging into 10s or 100s of database servers every 10 minutes. Therefore, and to not belabor the point, here are the scripts that I’ve started to develop. I hope that you can use them:
This solution makes use of a very simple directory structure under the oracle user’s home directory. Here are the directories/files used:
/home/oracle/alert
This is where the main script and supporting files exist
/home/oracle/alert/CHKALERT
This file contains ORA- errors, or any other errors we are interested in checking for in the alert log
/home/oracle/alert/CHKALERT.sh
This is the script that will need to be executed
/home/oracle/alert/CHKALERT.curr
File that contains information on the last time the alert log was scanned; containing information about the errors found and put into the CHKALERT.yyyy-mm-dd files
/home/oracle/alert/lst
The directory where output is generated
/home/oracle/alert/lst/CHKALERT.adrci
Is dynamically generated during runtime and will be used as a script to the ADRCI utility
/home/oracle/alert/lst/CHKALERT.lst
Is dynamically generated during runtime and will be used as output to a SQL*Plus call to get some variables to be used when calling ADRCI
/home/oracle/alert/lst/CHKALERT.sql
Is the SQL*Plus SQL used to generate the CHKALERT.lst output
/home/oracle/alert/lst/CHKALERT.tmp
Contains header output from the alert log to get a timezone
/home/oracle/alert/lst/CHKALERT.yyyy-mm-dd
will be files generated for each day that contains ORA- errors for that day
Below is the script itself. To use, just execute by typing in at the command prompt:
[oracle@ludwig alert]$ ./CHKALERT.shThe file you will be interested in looking at, if you’ve setup the /home/oracle/alert/CHKALERT file properly to look for specific ORA errors, will be the /home/oracle/alert/lst/CHKALERT.log file. This file contains each of the ORA errors found for the last run and looks like the following:
CHKALERT.log
::::::::::::::
001:2010:03:04:02:32:29:ORA-00312: online log 1 thread 1: '/oradata/db11/redo01.log'
001:2010:03:04:02:32:29:ORA-00312: online log 2 thread 1: '/oradata/db11/redo02.log'
001:2010:03:04:02:32:29:ORA-00312: online log 3 thread 1: '/oradata/db11/redo03.log'
To fully automate the process, not covered in this article, would be to interrogate this file and if it contains something, it should be sent to the appropriate DBAs to figure out what to do with the errors found. Have fun with the script. Change as you see fit and begin to take advantage of Oracle new ADRCI utility for scraping the alert logs.
#!/bin/sh
#--File: CHKALERT.sh
#--setup
PGM="CHKALERT"
ALRT=$HOME/alert
LST=${ALRT}/lst
LOG=${LST}/${PGM}.log
CURR=${ALRT}/${PGM}.curr
#--Unix environment variables
ECHO=echo; export ECHO
CAT=/bin/cat; export CAT
RM=/bin/rm; export RM
TOUCH=/bin/touch; export TOUCH
GREP=/bin/grep; export GREP
AWK=/bin/awk; export AWK
WC=/usr/bin/wc; export WC
TAIL=/usr/bin/tail; export TAIL
HEAD=/usr/bin/head; export HEAD
SUM=/usr/bin/sum; export SUM
#--Oracle environment variables
ORACLE_SID=db11; export ORACLE_SID
ORACLE_HOME=`${GREP} ${ORACLE_SID}: /etc/oratab | ${AWK} -F: '{print $2}'`; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH; export PATH
#--code
start=`date "+%Y:%m:%d:%H:%M:%S"`
${RM} ${LOG}
${TOUCH} ${LOG}
#--execute SQL to get some diagnostic variables
echo "set echo off" > ${LST}/${PGM}.sql
echo "set feedback off" >> ${LST}/${PGM}.sql
echo "set heading off" >> ${LST}/${PGM}.sql
echo "set linesize 40" >> ${LST}/${PGM}.sql
echo "set pagesize 55" >> ${LST}/${PGM}.sql
echo "set verify off" >> ${LST}/${PGM}.sql
echo "set linesize 300" >> ${LST}/${PGM}.sql
echo "SELECT 'homepath:'||replace(homepath.value,adrbase.value||'/','')" >> ${LST}/${PGM}.sql
echo " FROM v\$diag_info homepath, v\$diag_info adrbase" >> ${LST}/${PGM}.sql
echo " WHERE homepath.name = 'ADR Home'" >> ${LST}/${PGM}.sql
echo " AND adrbase.name = 'ADR Base';" >> ${LST}/${PGM}.sql
echo "SELECT 'day:'||to_char(sysdate ,'yyyy-mm-dd') FROM dual;" >> ${LST}/${PGM}.sql
echo "SELECT 'nextday:'||to_char(sysdate+1,'yyyy-mm-dd') FROM dual;" >> ${LST}/${PGM}.sql
echo "SELECT 'prevday:'||to_char(sysdate-1,'yyyy-mm-dd') FROM dual;" >> ${LST}/${PGM}.sql
echo "exit" >> ${LST}/${PGM}.sql
sqlplus -s '/as sysdba' @${LST}/${PGM}.sql > ${LST}/${PGM}.lst
#-- get diag information variables just queried from the database
homepath=`${GREP} homepath ${LST}/${PGM}.lst | ${AWK} -F":" '{print $2}'`
day=`${GREP} "^day" ${LST}/${PGM}.lst | ${AWK} -F":" '{print $2}'`
nextday=`${GREP} nextday ${LST}/${PGM}.lst | ${AWK} -F":" '{print $2}'`
prevday=`${GREP} prevday ${LST}/${PGM}.lst | ${AWK} -F":" '{print $2}'`
#-- get the timezone from the alert log (safest place to get)
#-- the proper timezone is needed to properly filter the alert log for date ranges you
#-- want to look at
echo "set echo off" > ${LST}/${PGM}.adrci
echo "set termout off" >> ${LST}/${PGM}.adrci
echo "set homepath ${homepath}" >> ${LST}/${PGM}.adrci
echo "spool ${LST}/${PGM}.tmp" >> ${LST}/${PGM}.adrci
echo "show alert -tail 1" >> ${LST}/${PGM}.adrci
echo "spool off" >> ${LST}/${PGM}.adrci
adrci script=${LST}/${PGM}.adrci 1>/dev/null 2>/dev/null
timezone=`${HEAD} -1 ${LST}/${PGM}.tmp | ${AWK} -F" " '{print $3}'`
#-- extract alert log errors for the current day (today) and previous day (yesterday)
#-- previous day alerts will be used if the current file has yesterday's day as last day;
#-- meaning that we have had a switch to a new day and might have errors still to
#-- process from the previous day
echo "set echo off" > ${LST}/${PGM}.adrci
echo "set termout off" >> ${LST}/${PGM}.adrci
echo "set homepath ${homepath}" >> ${LST}/${PGM}.adrci
echo "spool ${LST}/${PGM}.${day}" >> ${LST}/${PGM}.adrci
echo "show alert -P \"ORIGINATING_TIMESTAMP BETWEEN '${day} 00:00:00.000000 ${timezone}' AND
'${nextday} 00:00:00.000000 ${timezone}' AND MESSAGE_TEXT LIKE '%ORA-%'\" -term" >>
${LST}/${PGM}.adrci
echo "spool off" >> ${LST}/${PGM}.adrci
echo "spool ${LST}/${PGM}.${prevday}" >> ${LST}/${PGM}.adrci
echo "show alert -P \"ORIGINATING_TIMESTAMP BETWEEN '${prevday} 00:00:00.000000 ${timezone}' AND
'${day} 00:00:00.000000 ${timezone}' AND MESSAGE_TEXT LIKE '%ORA-%'\" -term" >>
${LST}/${PGM}.adrci
echo "spool off" >> ${LST}/${PGM}.adrci
adrci script=${LST}/${PGM}.adrci 1>/dev/null 2>/dev/null
#-- get current contents of the current file
#-- default to current day if no current file
if [ -r "${CURR}" ]
then
#-- if the current exists then get the information it contains
daychecksum=`${GREP} day ${CURR} | ${AWK} -F":" '{print $2}'`
daylastline=`${GREP} day ${CURR} | ${AWK} -F":" '{print $3}'`
daylastday=`${GREP} day ${CURR} | ${AWK} -F":" '{print $4}'`
else
#-- if the current does not exist then default to today
daychecksum=0
daylastline=3
daylastday=${day}
fi
#-- set the days to search through for alerts
#-- if last day in current file was yesterday then include previous day
#-- if last day in current file is not yesterday then just scan today's alerts
if [ "${daylastday}" = "${prevday}" ]
then
alertdays="${prevday} ${day}"
else
alertdays="${day}"
fi
#-- for each of the days to scan for alerts
for theday in ${alertdays}
do
#-- check alert errors for the last day.
if [ -r "${LST}/${PGM}.${theday}" ]
then
#-- If the checksum generated is DIFFERENT we should start reporting from the top.
#--
#-- If the checksum generated is the SAME we should start reporting from end of
#-- the previously generated output.
new_daychecksum=`${HEAD} -4 ${LST}/${PGM}.${theday} | ${SUM} | ${AWK} '{print $1}'`
if [ ${new_daychecksum} -ne ${daychecksum} ]
then
daychecksum=${new_daychecksum}
daylastline=3
fi
#-- get the number of lines in the generated errors so we can report to the
#-- end of the file and we know where to start next time.
new_daylastline=`${WC} -l ${LST}/${PGM}.${theday} | ${AWK} -F" " '{print $1}'`
#-- if the number of lines in the output is 3 then there are no errors found.
if [ ${new_daylastline} -ne 3 ]
then
#-- if number of lines in extracted alerts is the same as last time then no new alerts
if [ ${new_daylastline} -ne ${daylastline} ]
then
#-- find the line to begin reporting new alerts from
fromline=`expr ${new_daylastline} - ${daylastline}`
#-- produce alert lines for alerts defined in file CHKALERT
${TAIL} -${fromline} ${LST}/${PGM}.${theday} |
while read LINE
do
for ORAS in `${CAT} ${ALRT}/CHKALERT`
do
ora=`${ECHO} ${LINE} | ${GREP} ${ORAS}`
if [ $? -eq 0 ]
then
#-- you might want to do something here
#-- that is specific to certain ORA- errors
err="001"
echo "${err}:${start}:${LINE}" >> ${LOG}
fi
done
done
fi
fi
daylastline=${new_daylastline}
#-- update the current file only if the day being processed is current day
if [ "${theday}" = "${day}" ]
then
${ECHO} "day:"${daychecksum}":"${daylastline}":"${day} > ${CURR}
fi
fi
done
OPTIMIZER HINTS
What are the hints and what are the different types of hints (INTERVIEW Question)
Ans: Hints(Optimizer hints) are used to alter the execution plan of the sql statments.
We should first get the explain plan of our SQL and determine what changes can be done to make the code operate without using hints if possible. However, Oracle hints such as ORDERED, LEADING, INDEX, FULL, and the various AJ and SJ Oracle hints can tame a wild optimizer and give you optimal performance.
With hints one can influence the optimizer. The usage of hints (with exception of the RULE-hint) causes Oracle to use the Cost Based optimizer.
The following syntax is used for hints:
select /*+ HINT */ name
from emp
where id =1;
Where HINT is replaced by the hint text.
When the syntax of the hint text is incorrect, the hint text is ignored and will not be used.
here you can find undocumented hints.
Hints for Optimization Approaches and Goals
ALL_ROWS The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).
FIRST_ROWS The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row). In newer Oracle version you should give a parameter with this hint: FIRST_ROWS(n) means that the optimizer will determine an executionplan to give a fast response for returning the first n rows.
CHOOSE The CHOOSE hint causes the optimizer to choose between the rule-based approach and the cost-based approach for a SQL statement based on the presence of statistics for the tables accessed by the statement
RULE The RULE hint explicitly chooses rule-based optimization for a statement block. This hint also causes the optimizer to ignore any other hints specified for the statement block. The RULE hint does not work any more in Oracle 10g.
Hints for Access Paths
FULL The FULL hint explicitly chooses a full table scan for the specified table. The syntax of the FULL hint is FULL(table) where table specifies the alias of the table (or table name if alias does not exist) on which the full table scan is to be performed.
ROWID The ROWID hint explicitly chooses a table scan by ROWID for the specified table. The syntax of the ROWID hint is ROWID(table) where table specifies the name or alias of the table on which the table access by ROWID is to be performed. (This hint depricated in Oracle 10g)
CLUSTER The CLUSTER hint explicitly chooses a cluster scan to access the specified table. The syntax of the CLUSTER hint is CLUSTER(table) where table specifies the name or alias of the table to be accessed by a cluster scan.
HASH The HASH hint explicitly chooses a hash scan to access the specified table. The syntax of the HASH hint is HASH(table) where table specifies the name or alias of the table to be accessed by a hash scan.
HASH_AJ The HASH_AJ hint transforms a NOT IN subquery into a hash anti-join to access the specified table. The syntax of the HASH_AJ hint is HASH_AJ(table) where table specifies the name or alias of the table to be accessed.(depricated in Oracle 10g)
INDEX The INDEX hint explicitly chooses an index scan for the specified table. The syntax of the INDEX hint is INDEX(table index) where:table specifies the name or alias of the table associated with the index to be scanned and index specifies an index on which an index scan is to be performed. This hint may optionally specify one or more indexes:
NO_INDEX The NO_INDEX hint explicitly disallows a set of indexes for the specified table. The syntax of the NO_INDEX hint is NO_INDEX(table index)
INDEX_ASC The INDEX_ASC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, Oracle scans the index entries in ascending order of their indexed values.
INDEX_COMBINE If no indexes are given as arguments for the INDEX_COMBINE hint, the optimizer will use on the table whatever boolean combination of bitmap indexes has the best cost estimate. If certain indexes are given as arguments, the optimizer will try to use some boolean combination of those particular bitmap indexes. The syntax of INDEX_COMBINE is INDEX_COMBINE(table index).
INDEX_JOIN Explicitly instructs the optimizer to use an index join as an access path. For the hint to have a positive effect, a sufficiently small number of indexes must exist that contain all the columns required to resolve the query.
INDEX_DESC The INDEX_DESC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, Oracle scans the index entries in descending order of their indexed values.
INDEX_FFS This hint causes a fast full index scan to be performed rather than a full table.
NO_INDEX_FFS Do not use fast full index scan (from Oracle 10g)
INDEX_SS Exclude range scan from query plan (from Oracle 10g)
INDEX_SS_ASC Exclude range scan from query plan (from Oracle 10g)
INDEX_SS_DESC Exclude range scan from query plan (from Oracle 10g)
NO_INDEX_SS The NO_INDEX_SS hint causes the optimizer to exclude a skip scan of the specified indexes on the specified table. (from Oracle 10g)
Hints for Query Transformations
NO_QUERY_TRANSFORMATION Prevents the optimizer performing query transformations. (from Oracle 10g)
USE_CONCAT The USE_CONCAT hint forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator. Normally, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them.
NO_EXPAND The NO_EXPAND hint prevents the optimizer from considering OR-expansion for queries having OR conditions or IN-lists in the WHERE clause. Usually, the optimizer considers using OR expansion and uses this method if it decides that the cost is lower than not using it.
REWRITE The REWRITE hint forces the optimizer to rewrite a query in terms of materialized views, when possible, without cost consideration. Use the REWRITE hint with or without a view list. If you use REWRITE with a view list and the list contains an eligible materialized view, then Oracle uses that view regardless of its cost.
NOREWRITE / NO_REWRITE In Oracle 10g renamed to NO_REWRITE. The NOREWRITE/NO_REWRITE hint disables query rewrite for the query block, overriding the setting of the parameter QUERY_REWRITE_ENABLED.
MERGE The MERGE hint lets you merge views in a query.
NO_MERGE The NO_MERGE hint causes Oracle not to merge mergeable views. This hint is most often used to reduce the number of possible permutations for a query and make optimization faster.
FACT The FACT hint indicated that the table should be considered as a fact table. This is used in the context of the star transformation.
NO_FACT The NO_FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should not be considered as a fact table.
STAR_TRANSFORMATION The STAR_TRANSFORMATION hint makes the optimizer use the best plan in which the transformation has been used. Without the hint, the optimizer could make a query optimization decision to use the best plan generated without the transformation, instead of the best plan for the transformed query.
NO_STAR_TRANSFORMATION Do not use star transformation (from Oracle 10g)
UNNEST The UNNEST hint specifies subquery unnesting.
NO_UNNEST Use of the NO_UNNEST hint turns off unnesting for specific subquery blocks.
Hints for Join Orders
LEADING Give this hint to indicate the leading table in a join. This will indicate only 1 table. If you want to specify the whole order of tables, you can use the ORDERED hint. Syntax: LEADING(table)
ORDERED The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause. If you omit the ORDERED hint from a SQL statement performing a join , the optimizer chooses the order in which to join the tables. You may want to use the ORDERED hint to specify a join order if you know something about the number of rows selected from each table that the optimizer does not. Such information would allow you to choose an inner and outer table better than the optimizer could.
Hints for Join Operations
USE_NL The USE_NL hint causes Oracle to join each specified table to another row source with a nested loops join using the specified table as the inner table. The syntax of the USE_NL hint is USE_NL(table table) where table is the name or alias of a table to be used as the inner table of a nested loops join.
NO_USE_NL Do not use nested loop (from Oracle 10g)
USE_NL_WITH_INDEX Specifies a nested loops join. (from Oracle 10g)
USE_MERGE The USE_MERGE hint causes Oracle to join each specified table with another row source with a sort-merge join. The syntax of the USE_MERGE hint is USE_MERGE(table table) where table is a table to be joined to the row source resulting from joining the previous tables in the join order using a sort-merge join.
NO_USE_MERGE Do not use merge (from Oracle 10g)
USE_HASH The USE_HASH hint causes Oracle to join each specified table with another row source with a hash join. The syntax of the USE_HASH hint is USE_HASH(table table) where table is a table to be joined to the row source resulting from joining the previous tables in the join order using a hash join.
NO_USE_HASH Do not use hash (from Oracle 10g)
Hints for Parallel Execution
PARALLEL The PARALLEL hint allows you to specify the desired number of concurrent query servers that can be used for the query. The syntax is PARALLEL(table number number). The PARALLEL hint must use the table alias if an alias is specified in the query. The PARALLEL hint can then take two values separated by commas after the table name. The first value specifies the degree of parallelism for the given table, the second value specifies how the table is to be split among the instances of a parallel server. Specifying DEFAULT or no value signifies the query coordinator should examine the settings of the initialization parameters (described in a later section) to determine the default degree of parallelism.
NOPARALLEL / NO_PARALLEL The NOPARALLEL hint allows you to disable parallel scanning of a table, even if the table was created with a PARALLEL clause. In Oracle 10g this hint was renamed to NO_PARALLEL.
PQ_DISTRIBUTE The PQ_DISTRIBUTE hint improves the performance of parallel join operations. Do this by specifying how rows of joined tables should be distributed among producer and consumer query servers. Using this hint overrides decisions the optimizer would normally make.
NO_PARALLEL_INDEX The NO_PARALLEL_INDEX hint overrides a PARALLEL attribute setting on an index to avoid a parallel index scan operation.
Additional Hints
APPEND When the APPEND hint is used with the INSERT statement, data is appended to the table. Existing free space in the block is not used. If a table or an index is specified with nologging, this hint applied with an insert statement produces a direct path insert which reduces generation of redo.
NOAPPEND Overrides the append mode.
CACHE The CACHE hint specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables. In the following example, the CACHE hint overrides the table default caching specification.
NOCACHE The NOCACHE hint specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the normal behavior of blocks in the buffer cache.
PUSH_PRED The PUSH_PRED hint forces pushing of a join predicate into the view.
NO_PUSH_PRED The NO_PUSH_PRED hint prevents pushing of a join predicate into the view.
PUSH_SUBQ The PUSH_SUBQ hint causes nonmerged subqueries to be evaluated at the earliest possible place in the execution plan.
NO_PUSH_SUBQ The NO_PUSH_SUBQ hint causes non-merged subqueries to be evaluated as the last step in the execution plan.
QB_NAME Specifies a name for a query block. (from Oracle 10g)
CURSOR_SHARING_EXACT Oracle can replace literals in SQL statements with bind variables, if it is safe to do so. This is controlled with the CURSOR_SHARING startup parameter. The CURSOR_SHARING_EXACT hint causes this behavior to be switched off. In other words, Oracle executes the SQL statement without any attempt to replace literals by bind variables.
DRIVING_SITE The DRIVING_SITE hint forces query execution to be done for the table at a different site than that selected by Oracle
DYNAMIC_SAMPLING The DYNAMIC_SAMPLING hint lets you control dynamic sampling to improve server performance by determining more accurate predicate selectivity and statistics for tables and indexes. You can set the value of DYNAMIC_SAMPLING to a value from 0 to 10. The higher the level, the more effort the compiler puts into dynamic sampling and the more broadly it is applied. Sampling defaults to cursor level unless you specify a table.
SPREAD_MIN_ANALYSIS This hint omits some of the compile time optimizations of the rules, mainly detailed dependency graph analysis, on spreadsheets. Some optimizations such as creating filters to selectively populate spreadsheet access structures and limited rule pruning are still used. (from Oracle 10g)
Hints with unknown status
MERGE_AJ The MERGE_AJ hint transforms a NOT IN subquery into a merge anti-join to access the specified table. The syntax of the MERGE_AJ hint is MERGE_AJ(table) where table specifies the name or alias of the table to be accessed.(depricated in Oracle 10g)
AND_EQUAL The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes. The syntax of the AND_EQUAL hint is AND_EQUAL(table index index) where table specifies the name or alias of the table associated with the indexes to be merged. and index specifies an index on which an index scan is to be performed. You must specify at least two indexes. You cannot specify more than five. (depricated in Oracle 10g)
STAR The STAR hint forces the large table to be joined last using a nested loops join on the index. The optimizer will consider different permutations of the small tables. (depricated in Oracle 10g)
BITMAP Usage: BITMAP(table_name index_name) Uses a bitmap index to access the table. (depricated ?)
HASH_SJ
Use a Hash Anti-Join to evaluate a NOT IN sub-query. Use this hint in the sub-query, not in the main query. Use this when your high volume NOT IN sub-query is using a FILTER or NESTED LOOPS join. Try MERGE_AJ if HASH_AJ refuses to work.(depricated in Oracle 10g)
NL_SJ Use a Nested Loop in a sub-query. (depricated in Oracle 10g)
NL_AJ Use an anti-join in a sub-query. (depricated in Oracle 10g)
ORDERED_PREDICATES (depricated in Oracle 10g)
EXPAND_GSET_TO_UNION (depricated in Oracle 10g)
Best regards,
Rafi.
Ans: Hints(Optimizer hints) are used to alter the execution plan of the sql statments.
We should first get the explain plan of our SQL and determine what changes can be done to make the code operate without using hints if possible. However, Oracle hints such as ORDERED, LEADING, INDEX, FULL, and the various AJ and SJ Oracle hints can tame a wild optimizer and give you optimal performance.
With hints one can influence the optimizer. The usage of hints (with exception of the RULE-hint) causes Oracle to use the Cost Based optimizer.
The following syntax is used for hints:
select /*+ HINT */ name
from emp
where id =1;
Where HINT is replaced by the hint text.
When the syntax of the hint text is incorrect, the hint text is ignored and will not be used.
here you can find undocumented hints.
Hints for Optimization Approaches and Goals
ALL_ROWS The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).
FIRST_ROWS The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row). In newer Oracle version you should give a parameter with this hint: FIRST_ROWS(n) means that the optimizer will determine an executionplan to give a fast response for returning the first n rows.
CHOOSE The CHOOSE hint causes the optimizer to choose between the rule-based approach and the cost-based approach for a SQL statement based on the presence of statistics for the tables accessed by the statement
RULE The RULE hint explicitly chooses rule-based optimization for a statement block. This hint also causes the optimizer to ignore any other hints specified for the statement block. The RULE hint does not work any more in Oracle 10g.
Hints for Access Paths
FULL The FULL hint explicitly chooses a full table scan for the specified table. The syntax of the FULL hint is FULL(table) where table specifies the alias of the table (or table name if alias does not exist) on which the full table scan is to be performed.
ROWID The ROWID hint explicitly chooses a table scan by ROWID for the specified table. The syntax of the ROWID hint is ROWID(table) where table specifies the name or alias of the table on which the table access by ROWID is to be performed. (This hint depricated in Oracle 10g)
CLUSTER The CLUSTER hint explicitly chooses a cluster scan to access the specified table. The syntax of the CLUSTER hint is CLUSTER(table) where table specifies the name or alias of the table to be accessed by a cluster scan.
HASH The HASH hint explicitly chooses a hash scan to access the specified table. The syntax of the HASH hint is HASH(table) where table specifies the name or alias of the table to be accessed by a hash scan.
HASH_AJ The HASH_AJ hint transforms a NOT IN subquery into a hash anti-join to access the specified table. The syntax of the HASH_AJ hint is HASH_AJ(table) where table specifies the name or alias of the table to be accessed.(depricated in Oracle 10g)
INDEX The INDEX hint explicitly chooses an index scan for the specified table. The syntax of the INDEX hint is INDEX(table index) where:table specifies the name or alias of the table associated with the index to be scanned and index specifies an index on which an index scan is to be performed. This hint may optionally specify one or more indexes:
NO_INDEX The NO_INDEX hint explicitly disallows a set of indexes for the specified table. The syntax of the NO_INDEX hint is NO_INDEX(table index)
INDEX_ASC The INDEX_ASC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, Oracle scans the index entries in ascending order of their indexed values.
INDEX_COMBINE If no indexes are given as arguments for the INDEX_COMBINE hint, the optimizer will use on the table whatever boolean combination of bitmap indexes has the best cost estimate. If certain indexes are given as arguments, the optimizer will try to use some boolean combination of those particular bitmap indexes. The syntax of INDEX_COMBINE is INDEX_COMBINE(table index).
INDEX_JOIN Explicitly instructs the optimizer to use an index join as an access path. For the hint to have a positive effect, a sufficiently small number of indexes must exist that contain all the columns required to resolve the query.
INDEX_DESC The INDEX_DESC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, Oracle scans the index entries in descending order of their indexed values.
INDEX_FFS This hint causes a fast full index scan to be performed rather than a full table.
NO_INDEX_FFS Do not use fast full index scan (from Oracle 10g)
INDEX_SS Exclude range scan from query plan (from Oracle 10g)
INDEX_SS_ASC Exclude range scan from query plan (from Oracle 10g)
INDEX_SS_DESC Exclude range scan from query plan (from Oracle 10g)
NO_INDEX_SS The NO_INDEX_SS hint causes the optimizer to exclude a skip scan of the specified indexes on the specified table. (from Oracle 10g)
Hints for Query Transformations
NO_QUERY_TRANSFORMATION Prevents the optimizer performing query transformations. (from Oracle 10g)
USE_CONCAT The USE_CONCAT hint forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator. Normally, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them.
NO_EXPAND The NO_EXPAND hint prevents the optimizer from considering OR-expansion for queries having OR conditions or IN-lists in the WHERE clause. Usually, the optimizer considers using OR expansion and uses this method if it decides that the cost is lower than not using it.
REWRITE The REWRITE hint forces the optimizer to rewrite a query in terms of materialized views, when possible, without cost consideration. Use the REWRITE hint with or without a view list. If you use REWRITE with a view list and the list contains an eligible materialized view, then Oracle uses that view regardless of its cost.
NOREWRITE / NO_REWRITE In Oracle 10g renamed to NO_REWRITE. The NOREWRITE/NO_REWRITE hint disables query rewrite for the query block, overriding the setting of the parameter QUERY_REWRITE_ENABLED.
MERGE The MERGE hint lets you merge views in a query.
NO_MERGE The NO_MERGE hint causes Oracle not to merge mergeable views. This hint is most often used to reduce the number of possible permutations for a query and make optimization faster.
FACT The FACT hint indicated that the table should be considered as a fact table. This is used in the context of the star transformation.
NO_FACT The NO_FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should not be considered as a fact table.
STAR_TRANSFORMATION The STAR_TRANSFORMATION hint makes the optimizer use the best plan in which the transformation has been used. Without the hint, the optimizer could make a query optimization decision to use the best plan generated without the transformation, instead of the best plan for the transformed query.
NO_STAR_TRANSFORMATION Do not use star transformation (from Oracle 10g)
UNNEST The UNNEST hint specifies subquery unnesting.
NO_UNNEST Use of the NO_UNNEST hint turns off unnesting for specific subquery blocks.
Hints for Join Orders
LEADING Give this hint to indicate the leading table in a join. This will indicate only 1 table. If you want to specify the whole order of tables, you can use the ORDERED hint. Syntax: LEADING(table)
ORDERED The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause. If you omit the ORDERED hint from a SQL statement performing a join , the optimizer chooses the order in which to join the tables. You may want to use the ORDERED hint to specify a join order if you know something about the number of rows selected from each table that the optimizer does not. Such information would allow you to choose an inner and outer table better than the optimizer could.
Hints for Join Operations
USE_NL The USE_NL hint causes Oracle to join each specified table to another row source with a nested loops join using the specified table as the inner table. The syntax of the USE_NL hint is USE_NL(table table) where table is the name or alias of a table to be used as the inner table of a nested loops join.
NO_USE_NL Do not use nested loop (from Oracle 10g)
USE_NL_WITH_INDEX Specifies a nested loops join. (from Oracle 10g)
USE_MERGE The USE_MERGE hint causes Oracle to join each specified table with another row source with a sort-merge join. The syntax of the USE_MERGE hint is USE_MERGE(table table) where table is a table to be joined to the row source resulting from joining the previous tables in the join order using a sort-merge join.
NO_USE_MERGE Do not use merge (from Oracle 10g)
USE_HASH The USE_HASH hint causes Oracle to join each specified table with another row source with a hash join. The syntax of the USE_HASH hint is USE_HASH(table table) where table is a table to be joined to the row source resulting from joining the previous tables in the join order using a hash join.
NO_USE_HASH Do not use hash (from Oracle 10g)
Hints for Parallel Execution
PARALLEL The PARALLEL hint allows you to specify the desired number of concurrent query servers that can be used for the query. The syntax is PARALLEL(table number number). The PARALLEL hint must use the table alias if an alias is specified in the query. The PARALLEL hint can then take two values separated by commas after the table name. The first value specifies the degree of parallelism for the given table, the second value specifies how the table is to be split among the instances of a parallel server. Specifying DEFAULT or no value signifies the query coordinator should examine the settings of the initialization parameters (described in a later section) to determine the default degree of parallelism.
NOPARALLEL / NO_PARALLEL The NOPARALLEL hint allows you to disable parallel scanning of a table, even if the table was created with a PARALLEL clause. In Oracle 10g this hint was renamed to NO_PARALLEL.
PQ_DISTRIBUTE The PQ_DISTRIBUTE hint improves the performance of parallel join operations. Do this by specifying how rows of joined tables should be distributed among producer and consumer query servers. Using this hint overrides decisions the optimizer would normally make.
NO_PARALLEL_INDEX The NO_PARALLEL_INDEX hint overrides a PARALLEL attribute setting on an index to avoid a parallel index scan operation.
Additional Hints
APPEND When the APPEND hint is used with the INSERT statement, data is appended to the table. Existing free space in the block is not used. If a table or an index is specified with nologging, this hint applied with an insert statement produces a direct path insert which reduces generation of redo.
NOAPPEND Overrides the append mode.
CACHE The CACHE hint specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables. In the following example, the CACHE hint overrides the table default caching specification.
NOCACHE The NOCACHE hint specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the normal behavior of blocks in the buffer cache.
PUSH_PRED The PUSH_PRED hint forces pushing of a join predicate into the view.
NO_PUSH_PRED The NO_PUSH_PRED hint prevents pushing of a join predicate into the view.
PUSH_SUBQ The PUSH_SUBQ hint causes nonmerged subqueries to be evaluated at the earliest possible place in the execution plan.
NO_PUSH_SUBQ The NO_PUSH_SUBQ hint causes non-merged subqueries to be evaluated as the last step in the execution plan.
QB_NAME Specifies a name for a query block. (from Oracle 10g)
CURSOR_SHARING_EXACT Oracle can replace literals in SQL statements with bind variables, if it is safe to do so. This is controlled with the CURSOR_SHARING startup parameter. The CURSOR_SHARING_EXACT hint causes this behavior to be switched off. In other words, Oracle executes the SQL statement without any attempt to replace literals by bind variables.
DRIVING_SITE The DRIVING_SITE hint forces query execution to be done for the table at a different site than that selected by Oracle
DYNAMIC_SAMPLING The DYNAMIC_SAMPLING hint lets you control dynamic sampling to improve server performance by determining more accurate predicate selectivity and statistics for tables and indexes. You can set the value of DYNAMIC_SAMPLING to a value from 0 to 10. The higher the level, the more effort the compiler puts into dynamic sampling and the more broadly it is applied. Sampling defaults to cursor level unless you specify a table.
SPREAD_MIN_ANALYSIS This hint omits some of the compile time optimizations of the rules, mainly detailed dependency graph analysis, on spreadsheets. Some optimizations such as creating filters to selectively populate spreadsheet access structures and limited rule pruning are still used. (from Oracle 10g)
Hints with unknown status
MERGE_AJ The MERGE_AJ hint transforms a NOT IN subquery into a merge anti-join to access the specified table. The syntax of the MERGE_AJ hint is MERGE_AJ(table) where table specifies the name or alias of the table to be accessed.(depricated in Oracle 10g)
AND_EQUAL The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes. The syntax of the AND_EQUAL hint is AND_EQUAL(table index index) where table specifies the name or alias of the table associated with the indexes to be merged. and index specifies an index on which an index scan is to be performed. You must specify at least two indexes. You cannot specify more than five. (depricated in Oracle 10g)
STAR The STAR hint forces the large table to be joined last using a nested loops join on the index. The optimizer will consider different permutations of the small tables. (depricated in Oracle 10g)
BITMAP Usage: BITMAP(table_name index_name) Uses a bitmap index to access the table. (depricated ?)
HASH_SJ
Use a Hash Anti-Join to evaluate a NOT IN sub-query. Use this hint in the sub-query, not in the main query. Use this when your high volume NOT IN sub-query is using a FILTER or NESTED LOOPS join. Try MERGE_AJ if HASH_AJ refuses to work.(depricated in Oracle 10g)
NL_SJ Use a Nested Loop in a sub-query. (depricated in Oracle 10g)
NL_AJ Use an anti-join in a sub-query. (depricated in Oracle 10g)
ORDERED_PREDICATES (depricated in Oracle 10g)
EXPAND_GSET_TO_UNION (depricated in Oracle 10g)
Best regards,
Rafi.
Subscribe to:
Posts (Atom)