Usefull link

http://www.serkey.com/tag/analyzing-awr-report/

CTET Detail

You can find your admission card in Below given link .
Provisional Admit Card Information 
http://www.cbseonline.ernet.in/ctet/adminfo.aspx

General Information
http://hyd.tcs-itontap.com/dotcom/CBSE/

What is the best backup strategy for RMAN backup on production database?

The question of strategy really depends on your environment, if your database is small,
I recommend using full backup every day. if you have a larger bank to use incremental backup and
full week on the weekend, if you have a DataGuard and want a bit more secure you can also perform
backup of your DataGuard instead of a backup of your database production more for this you need a
little more attention..

Unlocking the locked table

Some times we get an error while running a DDL statement on a table. something like below

SQL> drop table aa;
drop table aa
*
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already
in use
This happens because some other session is using this table or having a lock on this table.
Following is the simple procedure to kill the session holding the lock on this table and drop the table.
Note that this should be done only if you are sure that this table is no more required and all sessions holding lock on this table can be deleted
1. Get the object ID of the table to be dropped
SQL> select object_id from dba_objects where object_name = 'AA';
 OBJECT_ID
----------
   3735492
2. Get the session ID which is holding lock on this object from v$locked_object view
SQL> select OBJECT_ID, SESSION_ID, ORACLE_USERNAME, PROCESS from v$locked_object where OBJECT_ID = 3735492;
 OBJECT_ID SESSION_ID ORACLE_USERNAME                PROCESS
---------- ---------- ------------------------------ ------------------------
   3735492       1124 MSC                            4092@AKPRADH-LAP

3. Get the serial# of the SID using v$session
SQL> select sid, serial# from v$session where sid = 1124;
       SID    SERIAL#
---------- ----------
      1124      51189

Kill the session by connecting as sysdba and try dropping the table
SQL> alter system kill session '1124, 51189';
System altered.
SQL> drop table aa;
Table dropped.

Once the locks are removed, you should be able to drop the table.

Transparent Application Failover TAF

1. BASIC METHOD
ADD THE ENTRIES TO THE RAC DATABASE NODE1 tnsnames.ora file
CDBS_TAF =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip.localdomain)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip.localdomain)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdbs)
      (FAILOVER_MODE=
      (TYPE=SELECT)
      (METHOD=BASIC)
     )
    )
  )
Make sure all the resources are up and running.
root@node1 bin]# ./crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....s1.inst application    ONLINE    ONLINE    node1
ora....s2.inst application    ONLINE    ONLINE   node2
ora....bs1.srv application    ONLINE    ONLINE    node1
ora....bs2.srv application    ONLINE    ONLINE   node2
ora...._srv.cs application    ONLINE    ONLINE   node2
ora.cdbs.db    application    ONLINE    ONLINE   node2
ora....SM1.asm application    ONLINE    ONLINE    node1
ora....E1.lsnr application    ONLINE    ONLINE    node1
ora....de1.gsd application    ONLINE    ONLINE    node1
ora....de1.ons application    ONLINE    ONLINE    node1
ora....de1.vip application    ONLINE    ONLINE    node1
ora....SM2.asm application    ONLINE    ONLINE   node2
ora....E2.lsnr application    ONLINE    ONLINE   node2
ora....de2.gsd application    ONLINE    ONLINE   node2
ora....de2.ons application    ONLINE    ONLINE   node2
ora....de2.vip application    ONLINE    ONLINE   node2
[oracle@node1 admin]$ sqlplus system/oracle@CDBS_TAF
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 26 17:36:45 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> show user;
USER is "SYSTEM"
SQL> col osuser format a10
SQL> col username format a10
SQL> col failover_type format a15
SQL> col failover_method format a18
SQL> select username,osuser,failover_method,failover_type from v$session
  2  where username='SYSTEM';
USERNAME   OSUSER     FAILOVER_METHOD    FAILOVER_TYPE
---------- ---------- ------------------ ---------------
SYSTEM     oracle     BASIC              SELECT

SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
cdbs1
SQL> select count(*) from demotb;
  COUNT(*)
----------
       954
SQL> select * from demotb;
run the long running query

simultaneously open another terminal and give shutdown abort instance cdbs1
SQL> shu abort
ORACLE instance shut down.
but the query runs smoothly without any interruption after the completion of the query, verify the instance name
SQL>select instance_name from v$instance;
INSTANCE_NAME
----------------
cdbs2
automatically it switch over to the instance cdbs2 , because of this user can access the database and no failure in queries. users can access without interruption.
USER will not receive any error message while running query.
2. PRECONNECT METHOD
Add the entries to the tnsnames.ora file on both nodes.
CDBS1_TAF =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdbs)
      (INSTANCE_NAME = cdbs1)
      (FAILOVER_MODE=
      (BACKUP=CDBS2_TAF)
      (TYPE=SELECT)
      (METHOD=PRECONNECT)
     )
    )
  )

CDBS2_TAF =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdbs)
      (INSTANCE_NAME = cdbs2)
      (FAILOVER_MODE=
      (BACKUP=CDBS1_TAF)
      (TYPE=SELECT)
      (METHOD=PRECONNECT)
     )
    )
  )
connect to the database on node1
[oracle@node1 ~]$ sqlplus system/oracle@CDBS1_TAF
SQL*Plus: Release 10.2.0.1.0 - Production on Fri June 16 12:23:11 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
----------------
cdbs1
SQL> COL USERNAME FOR a10
SQL> col osuser for a10
SQL> col failover_type format a15
SQL> col failover_method for a18
SQL> select username,failover_type,failover_method from v$session
  2  where username='SYSTEM';
USERNAME   FAILOVER_TYPE   FAILOVER_METHOD
---------- --------------- ------------------
SYSTEM     SELECT          PRECONNECT
SQL> select count(*) from demo;
  COUNT(*)
----------
     49783
run the long running query.
SQL> select * from demo;
simultaneously issue shutdown abort in new terminal
SQL> shu abort;
ORACLE instance shut down.
SQL>
the query runs smoothly, user will not receive any kind of error messages.
then check the instance name in node1 machine.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
cdbs2
SQL> select username,osuser,failover_type,failover_method from v$session
  2  where username='SYSTEM';
USERNAME   OSUSER     FAILOVER_TYPE   FAILOVER_METHOD
---------- ---------- --------------- ------------------
SYSTEM     oracle     SELECT          PRECONNECT
SQL>
checking node2 machine before and after TAF.
Before TAF
SQL> select username,osuser,failover_type,failover_method from v$session
  2  where username='SYSTEM';
USERNAME                       OSUSER                         FAILOVER_TYPE
------------------------------ ------------------------------ -------------
FAILOVER_M
----------
SYSTEM                         oracle                         NONE
NONE

SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
cdbs2
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
cdbs2
After TAF.
SQL> select username,osuser,failover_type,failover_method from v$session
  2  where username='SYSTEM';
USERNAME                       OSUSER                         FAILOVER_TYPE
------------------------------ ------------------------------ -------------
FAILOVER_M
----------
SYSTEM                         oracle                         SELECT
PRECONNECT

Connecting to RAC using SQLDeveloper

The following are two definition in tnsnames.ora pointing to the same RAC database. The first one uses name (racora1),
the second (racora) uses actual IP address:

racora1 = (DESCRIPTION =  (ADDRESS_LIST =    (ADDRESS = (PROTOCOL = TCP)(HOST = db1.testoracle.COM)(PORT = 1521))    (ADDRESS = (PROTOCOL = TCP)(HOST = db2.testoracle.COM)(PORT = 1521))    (LOAD_BALANCE = yes)  )  (CONNECT_DATA =    (SERVER = DEDICATED)    (SERVICE_NAME = ORCL.testoracle.COM)    (FAILOVER_MODE =      (TYPE = SELECT)      (METHOD = BASIC)      (RETRIES = 180)      (DELAY = 5)    )  ))

racora2 = (DESCRIPTION =  (ADDRESS_LIST =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.155.1.1)(PORT = 1521))    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.155.1.2)(PORT = 1521))    (LOAD_BALANCE = yes)  )  (CONNECT_DATA =    (SERVER = DEDICATED)    (SERVICE_NAME = ORCL.testoracle.COM)    (FAILOVER_MODE =      (TYPE = SELECT)      (METHOD = BASIC)      (RETRIES = 180)      (DELAY = 5)    )  ))

If you use SQL Developer to connect
to racora1 using the TNS, you will encounter the following error:
Status: Failure - Test failed: (null).

It works fine if you connect to racora. Apparently, it is not able to perform a nslookup to obtain the IP address when using TNS. It works fine if you use advance and use thin client to connect as in:
Strangely, if you use Advanced and put the following string, it works:
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS = (PROTOCOL = TCP)(HOST = db1.testoracle.COM)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = db2.testoracle.COM)(PORT = 1521))(LOAD_BALANCE = yes))(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME = ORCL.testoracle.COM)(FAILOVER_MODE=(TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))))

How to find what is locking a table.

- How to find what is locking a table.
       1. Query from DBA_OBJECTS to find the object_name of the table getting locked.
       2. Query from V$LOCK where id1 = 'table_name', get sid.
       3. Query from v$PROCESS where pid = sid. THis has info on what is locking the table.

Select Randomly from Table
This feature allows you to randomly "sample" from a table. This feature has many great uses. The syntax is as follows:
SELECT COUNT(*) * 100
FROM EMP SAMPLE (1);
This will randomly sample 1% of the rows, multiple the count of them x 100 to get a rough estimate of the amount of rows in the table.
You can also randomly sample by blocks for better performance but possibly less random:
SELECT *
FROM EMP SAMPLE BLOCK (1);
Again, this samples roughly 1% of the table by blocks, which may not be 1% of the actual rows. But this will cause fewer blocks to be visited and decrease the elapsed time, but if the data is grouped in the table, it may not be very random.
This tool can be used to get a rough idea of the data in the table, or give good estimates when using group functions. For example, a great use of this would be on a 40 million row table:
SELECT AVG(number_of children) * 20
FROM dependants sample (5);
This will give you an average of the number of dependants by only sampling 5% of the table by only visiting 2 million rows and not 40 million.

Rebuilding Indexes

You can use the ALTER INDEX REBUILD command to change the storage and tablespace parameters for an existing index without having to drop it.
The following is an example of an index rebuild via this command. It's storage parameters are changed to use an initial extent size of 3MB and a next extent size of 500K. It is also being moved from the USR7 tablespace to the IDX7 tablespace.

ALTER INDEX fuzzy_pink_slippers REBUILD
(  STORAGE(INITIAL 3M NEXT 500K
           PCTINCREASE 0)
TABLESPACE IDX7;  )

alter index <index_name> REBUILD TABLESPACE <new_tablespace>;

Rebuild? There is an alternative
You may wish to consider.
ALTER INDEX vmoore COALESCE;
Coalescing an index, de-fragments the leaf blocks for an index as opposed to a full rebuild cycle. In many instances, this may be sufficient remedial action.
Positives
- Less resource used - in fact no additional space is required
- Faster (typically)
- Good for databases with large block sizes (see the negative point below on index height)
Negatives
- You aren't recreating anything, so obviously you cannot move the index or adjust its storage parameters during a coalesce
- Since only the leaf blocks are coalesced, the height of the index tree will not be changed. For databases with larger block sizes, this should be less significant since the indexes will be "flatter" anyway.
  

Deleting Files from Windows

Delete files more than X days old in Windows "forfiles" is part of the resource kit, but ships with Windows 2003 Server and later.

forfiles -p d:\mssql_backup -s -m *.bak -d -2 -c "cmd /C del @Path"

1. -p: Start in the directory d:\mssql_backup
2. -s: process subdirectories
3. -m: match files using *.bak
4. -d: Find files more than 2 days old
5. -c: Execute the del command to delete the file. @Path has double-quotes around it already.

OS Authentication

OS authentication allows Oracle to pass control of user authentication to the operating system. Non-priviliged OS authentication connections take the following form.

sqlplus /
sqlplus /@service

When a connection is attempted from the local database server, the OS username is passed to the Oracle server. If the username is recognized, the Oracle the connection is accepted, otherwise the connection is rejected.
First, create an OS user, in this case the user is called "diego". Once you created that, if you try to login as sqlplus "/ as sysdba"  it will fail:
The connections failed because we have not told Oracle the users are OS authenticated. To do this, we must create an Oracle user, but first we must check the value of the Oracle OS_AUTHENT_PREFIXinitialization parameter.

SQL> SHOW PARAMETER os_authent_prefix

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$
SQL>

As you can see, the default value is "ops$". If this is not appropriate it can be changed using the ALTER SYSTEM command, but for now we will use this default value.
Now we know the OS authentication prefix, we can create a database user to allow an OS authenticated connection. To do this, we create an Oracle user in the normal way, but the username must be the prefix value concatenated to the OS username. So for the OS user "tim_hall", we would expect an Oracle username of "ops$tim_hall" on a UNIX or Linux platform.

-- UNIX
CREATE USER ops$diego IDENTIFIED EXTERNALLY;
GRANT CONNECT TO ops$diego;

The situation is complicated slightly on Windows platforms as the domain or machine name forms part of the username presented to Oracle. On Windows platforms you would expect an Oracle username of "OPS$DOMAIN\TIM_HALL" for the Windows user "tim_hall".

-- Windows
CREATE USER "OPS$ORACLE-BASE.COM\DIEGO" IDENTIFIED EXTERNALLY;
GRANT CONNECT TO "OPS$ORACLE-BASE.COM\DIEGO";

When using a Windows server, there is an additional consideration. The following option must be set in the "%ORACLE_HOME%\network\admin\sqlnet.ora" file.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

With the configuration complete, now you can connect as that "diego" user.

TWO_TASK In oracle

   1. Find Values from system you are connecting to...
             echo $ORACLE_SID,  echo $FNDNAM,   echo $GWYUID
             from the system you will be joining to.
         2. Set Environment Variables
             TWO_TASK=[net_alias]; export TWO_TASK
             FNDNAM=value from $FNDNAM; export FNDNAM
             GWYUID=value from $GWYUID; export GWYUID
Using two task we can connect remote database with using connection string (@xxx).
which value we will set for two task default database will try to connect same database,
no matter weather that instance is in same node or different node .

Start Oracle DB, Listener at Boot Time

To make the database and listeners start up automatically when the server reboots and shut down automatically when the server shuts down, you’ll need to create a dbora file in /etc/init.d and link it to /etc/rc2.d and /etc/rc0.d. You’ll need to do this as the root user. First create a file called dbora in /etc/init.d as follows as root:
vi /etc/init.d/dbora

#!/bin/sh
# description: Starts and stops Oracle processes 
#
ORA_BASE=/u01/app/oracle
ORA_HOME=/u01/app/oracle/OraHome_1
ORA_OWNER=oracle
if [ ! -f $ORA_HOME/bin/dbstart ]
then
  echo "Oracle startup: cannot start"
  exit
fi
case "$1" in
     'start')
          # Start the Oracle database, listener and Web Control
          su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart"
          su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
          su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start dbconsole"
          ;;
     'stop')
          # Stop the Oracle database, listener and Web Control
          su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
          su - $ORA_OWNER -c "$ORA_HOME/bin/emctl stop dbconsole"
          su - $ORA_OWNER -c $ORA_HOME/bin/dbshut
          ;;
esac

Link the script:
ln -s /etc/init.d/dbora /etc/rc2.d/S99dbora      
ln -s /etc/init.d/dbora /etc/rc0.d/K10dbora

Test the script created. To test the script created above, without rebooting do the following:
su - root
/etc/init.d/dbora start     (for startup)
/etc/init.d/dbora stop      (for shutdown)

Quick Unixs Tips

Deleting Files by 'n' Dates
There are three times associated with a file
    atime  - last access time
    ctime  - last status change time
    mtime  - last modify time
To remove all files from directory /home/dpafumi that have ctime greater than 5 days, enter
find /home/dpafumi -type f -ctime +5 -exec rm {} \;
To test this, first use something like the above command to print out what files have a ctime greater than 5 days with
find /home/dpafumi -type f -ctime +5 -print
Both commands will go down recursively through subdirectories of /home/dpafumi.
To only go search /home/dpafumi, you have to use the GNU version of find
/usr/local/bin/find /home/dpafumi -type f -ctime +5 -maxdepth 1 -print


- How to find O/S VERSION or system name
        uname -ap

- Finding process id for trace or other process
         ps -fu username
- Zipping up a directory for backup
         1. zip -r file [Directory], or
         2. zip -r file *fresh* (This would zip up all files with fresh in the name, plus any directories, and all dirs underneath with fresh in the name
- How to tar a directory
         tar -cvf /tmp/filename *
         do this from the top direct ory that you want to bundle into the tar'd file
 - using 'tar' to list contents of tar file, without extracting
         tar -tvf [filename]
- Checking space on a disk
        df -tk
 Useful grep options
         grep -c  This counts the number of lines that contain the pattern
         grep -i  Ignore upper/lower case
         grep -l  Print only the name of the files that contain the pattern. Does not repeat file names.
- Count the number of files in a directory
         ls | wc -l
- How to send a message to everyone on a UNIX machine
         1. Create a file that contains the message
         2. $ wall < [filename] ( Ex: wall < message )
         3. You may need to specify the directory 'wall' is in. /usr/sbin/wall < message

 Removing some files to free up space
         1. Go to the destination directory
         2. Run 'find . -name "*.*O" -print'. Make sure this returns files you want to delete
         3. Run 'find . -name "*.*O" -exec rm -f {} \;
         WARNING! Make sure you do this right or you can remove more files that you want.
Find Memory in CPU
/usr/sbin/prtconf
or
dmesg | more

- How to do a search and replace in vi
          :%s,[string to replace], [replacing string], g
             example: :%s,oracle,stuck,gc {would replace oracle with stuck}
          The above command does this for the whole document without confirming, if
          you would like to confirm each change use the command:
             :%s,[string to replace], [replacing string], gc
          This will stop after each search, type a y to confirm the change

- Cleaning up memory and semaphores when a db process is killed, rather than being shut down via svrmgrl
          If a database process id is killed, rather than being shutdown properly via svrmgrl, then it will leave memonry and semaphores,
          which may prevent the db from being recreated. Do the following to clean it up.
          1. Run the UNIX command, 'ipcs -a'. You may want to stretch your screen as wide as it will go to get all the data returned to be on one line.
          2. Look for the line where the value for the column NATTCH is 0. This is the Memory for your killed process that you need to delete.
          3. Get the id# for that line from the ID column (first column)
          4. Run the command 'ipcrm -m id#' to delete this memory
          5. Figure out which line in the Semaphores zone is for your database.
          If there are multiple lines, you may be able to determine which one by comparing the value in the NSEMS column to value of 'processes=XX' in your init.ora.
          If only one line matches, then that is the line you need to delete.
          If you delete the wrong line you will crash someone else's database.
          If you cannot find a distinct line, you may want to try shutting down databases on the system until you can get one distinct line.
          Once the line is identified, you need to remove it also.
          6. Again, get the id# for that line from the first column.
          7. Run 'ipcrm -s id#

- To lock archive file for updating
         co -l tele.html


- Finding the IP address for different systems
         grep -i finsun /etc/hosts
- How to find the tcpip address for a machine
         nslookup [machine name]

Change the IP Address
METHOD 1
Open network configuration file. In this example, it’ll configure on interface eth0. Type
vi /etc/sysconfig/network-scripts/ifcfg-eth0

As an example, I'm showing that the current configuration is DHCP:
DEVICE=eth0
BOOTPROTO=dhcp
HWADDR=00:D0:B7:08:09:BB
ONBOOT=yes

Modify the file and perform the following modifications/additions (Change BOOTPROTO to static and add IP Address and Netmask as new lines if they’re not existed yet)
DEVICE=eth0
IPADDR=192.168.1.100
NETMASK=255.255.255.0
BOOTPROTO=static
HWADDR=00:D0:B7:08:09:BB
ONBOOT=yes
# The following settings are optional
BROADCAST=192.168.1.255   #Always Finish with 255
NETWORK=192.168.1.0       #Always Finish with 0
IPV6INIT=yes
IPV6_AUTOCONF=yes

Save and close the file. Define default gateway (router IP) and hostname in /etc/sysconfig//network file:
vi /etc/sysconfig/network

Append/modify configuration as follows:
NETWORKING=yes
HOSTNAME=machinename.domain.com
TYPE=Ethernet
GATEWAY=10.10.29.65

Save and close the file. Restart networking::
# /etc/init.d/network restart
or
# service network restart

Make sure you have correct DNS server defined in /etc/resolv.conf file:
# vi /etc/resolv.conf
Setup DNS Server as follows:
nameserver 10.0.80.11
nameserver 10.0.80.12
nameserver 202.67.222.222

Save and close the file. Now you can ping the gateway/other hosts:
$ ping 10.0.80.12
You can also check for Internet connectivity with nslookup or host command:
$ nslookup yahoo.com
Review the configuration. Type
ifconfig

METHOD 2You can also execute the following command from the X Window and it will show you the GUI Network Tool:
$ system-config-network &
METHOD 3If you don’t have X windows GUI installed type the following command at shell prompt:
# system-config-network-tui &



Change the HOST NAME
Make sure you are logged in as root and move to /etc/sysconfig and open the network file in vi.
cd /etc/sysconfig
vi network
Look for the HOSTNAME line and replace it with the new hostname you want to use. In this example I want to replace localhost with redhat9.
HOSTNAME=redhat9
When you are done, save your changes and exit vi. Next we will edit the /etc/hosts file and set the new hostname.
vi /etc/hosts


Finally we will restart the network to apply the changes we made to /etc/hosts and /etc/sysconfig/network.
service network restart

Oracle Hints

Introduction

Performance is one of the primary objectives of a database. Fine tuned data retrieval and database operations can save lot of time for users and avoid troubleshoot nightmares of database developers.

The root cause of database performance degradation lies with the working of optimizer. Optimizer speed lowers and hence the query speed. There are multiple approaches and many ways to achieve database process performance. Thorough analysis of the data, explain plan is required to adopt the most feasible method to tune a SQL query. 

Out of these many approaches, Oracle SQL hints are one of the tricks to tune a query. SQL hints are optimizer directives which were introduced in Oracle 8.1.0 release. They instruct optimizer to follow an alternative path of query execution. At times, a developer can analyze a complex data better than an Optimizer. Therefore, he may choose a better execution plan than the optimizer by enforcing hints in the queries.  Like other approaches, it is not a full proof method of tuning but it is suggestible in case of complex queries.

SQL Hints Usage  Below are some situations when Hints are required to be used when the Optimizer fails:- 

• Bugs
• Use of Bind Variables which disable Histograms
• Poorly written SQLs
• High frequency of Change of Data
• Faulty (incomplete) Configuration Settings
• Dynamic vs. Static SQL 

Other circumstances where the use of Hints may be required:- 

• Hints can be used to join external tables.
• Hints can be used to force the optimizer to choose different join paths which will fetch different results in the query execution.
• Hints are used to tell the optimizer which data access method to use (as for example in a flashback query). The method may be a full table scan or a different index. 

Note: – Sometimes, the optimizer can ‘lock’ the statistics when they look ideal. In such a case ‘Hints’ lose their importance.

Syntax:-  Hints appear as comments to the SELECT and DML statements. Without these keywords, they hold no meeting and would only appear as a comment. Hints can be used in any of the following ways: 

• /*+ hint */
• /*+ hint (argument) */
• /*+ hint (argument-1 argument-2) */

Notes 

• Hints can be used in SELECT, INSERT, UPDATE or DELETE statements
• It can be a part of subquery too
• It can appear in any of the participating queries of a compound query using SET operator
• There should be no schema names in hints
• All hints except the /*+ rule */ cause the Cost Based Optimizer (CBO) to be used
• Hints operate on a simple view but not on a complex view 

Illustration 

1. The query below uses ALL_ROWS hint to select employee details from EMPLOYEE table 

SELECT /*+ ALL_ROWS */ EMPNO, ENAME, SALARY, DETPNO
FROM EMPLOYEE WHERE EMPNO=100

2. The SQL query below uses RULE hint to change the optimizer mode from COST based to RULE based

SELECT /*+ RULE */ EMPNO, ENAME, SALARY, DETPNO 

FROM EMPLOYEE WHERE EMPNO=100

3. The FULL hint used in the below SQL query enforces optimizer to do FULL TABLE SCAN

SELECT /*+ FULL(E) */ EMPNO, ENAME, SALARY

FROM EMPLOYEE E WHERE DEPTNO IN (SELECT DEPTNO 

                                 FROM DEPARTMENT

                                 WHERE LOCATION_ID=1009)

Types of Hints

Based on their usage area, SQL hints can be classified as below. 

• Hints for Optimization Approaches and Goals
• Hints for Access Paths, Hints for Query Transformations
• Hints for Join Orders
• Hints for Join Operations
• Hints for Parallel Execution
• Additional Hints 

Hints for Optimization Approaches and Goals
Hint	Function	Use
ALL_ROWS	Invokes the CBO	Used for batch processing, data warehousing systems
FIRST_ROWS	Invokes the CBO	Used for On Line Transfer Protocol (OLTP) systems
CHOOSE	Invokes the CBO	It lets the server choose between ALL_ROWS and FIRST_ROWS, based on the statistics gathered by the CBO

 Hints for Access Paths 

CLUSTER	INDEX	INDEX_FFS
FULL	INDEX_ASC	INDEX_JOIN
HASH	INDEX_COMBINE	NO_INDEX
ROWID	INDEX_DESC	AND_EQUAL

Hints for Query Transformations 

FACT	NO_FACT
MERGE	NO_MERGE
NO_EXPAND	NOREWRITE
NO_EXPAND_GSET_TO_UNION	REWRITE
USE_CONCAT	STAR_TRANSFORMATION

Hints for Join Operations 

DRIVING_SITE	MERGE_AJ	USE_HASH
HASH_AJ	MERGE_SJ	USE_MERGE
HASH_SJ	NL_AJ	USE_NL
LEADING	NL_AJ

 Hints for Parallel Execution 

NO PARALLEL

PARALLEL

NOPARALLEL_INDEX

PARALLEL_INDEX

PQ_DISTRIBUTE

Miscellaneous Hints 

ANTIJOIN	DYNAMIC_SAMPLING
APPEND	INLINE
BITMAP	MATERIALIZE
BUFFER	NO_ACCESS
CACHE	NO_BUFFER
CARDINALITY	NO_MONITORING
CPU_COSTING	NO_PUSH_PRED
NO_PUSH_SUBQ	ORDERED_PREDICATES
NO_QKN_BUFF	PUSH_PRED
NO_SEMIJOIN	PUSH_SUBQ
NOAPPEND	NOCACHE
STAR	SEMIJOIN
SWAP_JOIN_INPUTS	SEMIJOIN_DRIVER
USE_ANTI	OR_EXPAND
USE_SEMI	ORDERED
QB_NAME	SELECTIVITY

Hint introduced in Oracle 11g 

RESULT_CACHE – The hint was introduced to cache the result of the SELECT query for the same set of inputs and values. It results in better performance if the same query has to be executed for multiple numbers of times.

Undocumented Hints 

BYPASS_RECURSIVE_CHECK	CURSOR_SHARING_EXACT
BYPASS_UJVC	DEREF_NO_REWRITE
CACHE_CB	DML_UPDATE
CACHE_TEMP_TABLE	DOMAIN_INDEX_NO_SORT
CIV_GB	DOMAIN_INDEX_SORT
COLLECTIONS_GET_REFS	DYNAMIC_SAMPLING
CUBE_GB	DYNAMIC_SAMPLING_EST_CDN
SYS_PARALLEL_TXN	SYS_RID_ORDER
REMOTE_MAPPED	RESTORE_AS_INTERVALS
SYS_DL_CURSOR	NO_UNNEST
SQLLDR	USE_TTT_FOR_GSETS
NESTED_TABLE_GET_REFS	NESTED_TABLE_SET_SETID
NESTED_TABLE_SET_REFS	NO_FILTERING
EXPAND_GSET_TO_UNION	PIV_GB
FORCE_SAMPLE_BLOCK	TIV_GB
GBY_CONC_ROLLUP	SAVE_AS_INTERVALS
GLOBAL_TABLE_HINTS	NOCPU_COSTING
HWM_BROKERED	PQ_NOMAP
NO_PRUNE_GSETS	NO_ORDER_ROLLUPS
INDEX_RRS	SCN_ASCENDING
INDEX_SS	OVERFLOW_NOMOVE
INDEX_SS_ASC	PQ_MAP
INDEX_SS_DESC	NO_STATS_GSETS
LIKE_EXPAND	UNNEST
LOCAL_INDEXES	SKIP_EXT_OPTIMIZER
MV_MERGE	PIV_SSF
TIV_SSF	IGNORE_ON_CLAUSE
IGNORE_WHERE_CLAUSE

Obsolete Hints RULE – Its usage has been deprecated by Oracle after 9i release. Earlier, it used to work similar to the current COST hint i.e. to toggle over the optimizers.