LEADING hint

If the query is more complex and it becomes more difficult to figure out the order of all the tables using in ORDER hint. We can figure out the which table access first (driving table), but may not know which table to access after that. The LEADING hint allow us to  specify one table to drive the query while optimizer figure out the rest table.

Syntax:

select /*+ LEADING (table1) */ column1, …

Example1:

select /*+ LEADING(DEPT) */ emp.empno, ename, dept.deptno, itemno

from emp, dept, orders

where emp.deptno = dept.deptno

and emp.empno = orders.empno

and dept.deptno = 1

and emp.empno = 7747

and orders.ordno = 45;

PARALLEL hint

The PARALLEL hint causes the optimizer to break a query into pieces (the degree of parallelism) and process each piece with a different process. The PARALLEL hint will enable the use of parallel operations. If the degree is not specified with the hint, the default degree specifiedduring the table creation will be used.

Syntax:

select /*+ PARALLEL (table, DEGREE) */ ...

The degree is the number of pieces into which the query is broken.

Example1:

select /*+ PARALLEL (order_line_items) */ invoice_number, invoice_date

from order_line_items

order by invoice_date;

Example2:

select /*+ PARALLEL (order_line_items, 4) */ invoice_number, invoice_date

from order_line_items

order by invoice_date;

This statement does not specify a degree of parallelism. The default degree of parallelism is dictated by the table definition when the table was created.

Example3:

select /*+ PARALLEL (oli, 4) */ invoice_number, invoice_date

from order_line_items oli

order by invoice_date;

ORDERED hint

The ORDERED hint is one of the most powerful hints. It process the table of query in the sequential order listed in the FORM clause.
When the ORDERED hint is not used, Oracle may internally switch the driving table when compared to how tables are listed in the FROM clause (EXPLAIN PLAN can show how tables are accessed).

Syntax:

select /*+ ORDERED */ column1, …

Example1:

select /*+ ORDERED */ empno, ename, dept.deptno

from emp, dept

where emp.deptno = dept.deptno

and dept.deptno = 1

and emp.empno = 7747;

Emp and dept have been analyzed (using the cost-based optimizer) and there are no indexes on both the table, the emp table is accessed first and the dept table is accessed second.

Example2:

select /*+ ORDERED */ emp.empno, ename, dept.deptno, itemno

from emp, dept, orders

where emp.deptno = dept.deptno

and emp.empno = orders.empno

and dept.deptno = 1

and emp.empno = 7747

and orders.ordno = 40;;

INDEX Hint

The INDEX hint is frequently used to force one or more indexes to be executed for a given query. Oracle generally chooses the correct index or indexes with the optimizer, but when the optimizer chooses the wrong index or no index at all, this hint is excellent. You may also use multiple indexes with this hint and Oracle will choose one or more of the indexes specified based on the best plan. If you only specify one index, the optimizer considers only one index.

Syntax:

select /*+ INDEX (table index1, index2…) */ column1, …

from Table_Name

where conditions;

Example1:

select /*+ INDEX (emp deptno_idx) */ empno, ename, deptno
from emp
where deptno = 10;

Example2:

Multiple indexes for a single table can be specified, but it is usually better to specify only the most restrictive index on a given query (avoiding the merging of the result of each index). If multiple indexes are specified, Oracle chooses which (one or more) to use, so be careful or your hint could potentially be overridden.

select /*+ INDEX (emp deptno_idx, empno_idx) */ empno, ename, deptno
from emp
where deptno = 10
and empno = 7750;

Example3:

select /*+ INDEX */ empno, ename, deptno
from emp
where deptno = 1
and empno = 7750;

In this example, no index is specified. Oracle now weighs all of the possible indexes that are available and chooses one or more to be used. Since we have not specified a particular index, but we have specified the INDEX hint, the optimizer will not do a full table scan.

Top Hints Used

• A hint is an instruction to the optimizer which can influence the optimizer to choose a difference plan for a sql statement. The optimizer uses these hints to choose an execution plan for the statement. 
• Hints should be used only as LAST RESORT if statistics were gathered properly and query is still following a sub-optimal execution plan.

DBAs and developers use below hints in their day-to-day tuning
1. INDEX
2. ORDERED
3. PARALLEL
4. FIRST_ROWS
5. FULL
6. LEADING
7. USE_NL
8. APPEND
9. USE_HASH

what to look for to help performance

Identify the solution for what problem by looking the TKPROF.

If parsing numbers are high: The SHARED_POOL_SIZE may need to be increased.

If Disk reads are very high: Indexes are not being used or may not exist

If the QUERY or CURRENT memory reads are very high: Indexes may be on columns with low cardinality (columns where an individual value generally makes up a large percentage of the table; like a y/n field). Removing/suppressing the index or using histograms or a bitmap index may increase performance. A poor join order of tables or bad order in a concatenated index may also cause this.

If parse elapse time is high: There may be a problem with the number of open cursors.

If number of rows processed by a row in the EXPLAIN PLAN is high compared to the other rows: This could be a sign of an index with a poor distribution of distinct keys (unique values for a column). This could also be a sign of a poorly written statement.

If the number of rows processed by a row in the EXPLAIN PLAN is high compared to the other rows: This indicates that the statement had to be reloaded. You may need to increase the SHARED_POOL_SIZE in the init.ora file or do a better job of sharing SQL.

Details Of TRACE and TKPROF Output

SQL TRACE has multiple sections including SQL Statements, Statistics, information and EXPLAIN PLAN.

1. SQL Statements: The first part of a TKPROF statement is the SQL  Statement . This statement will be exactly the same as the statement that was executed. If there were any hints or comments in the statement, they would be retained in this output.

2. Statistics:  It has all the Statistics for this SQL Statements. It has eight columns 

• call Statistics for each cursor's activity are divided in to 3 areas: Parse,Execute and Fetch. A total is also calculated.

      Parse: statistics from parsing the cursor. This includes information for plan generation etc.

Execute: statistics for the execution phase of a cursor

Fetch: statistics for actually fetching the rows

• Count number of times each individual activity has been performed on this particular CALL.
• CPU time used by this CALL.
• ELAPSED time for this CALL(includes the CPU time).
• DISK this indicates the number of blocks read from disk. Generally it would be preferable for blocks to be read from the buffer cache rather than disk.
• QUERY the total number of data buffers retrieved from memory for this type of call. SELECT statements usually retrieve buffers in this mode. This is the number of consistent gets.
• CURRENT the total number of data buffers retrieved from memory for this type of call. UPDATE, INSERT, or DELETE usually access buffers in this mode, although SELECT statements may use a small number of buffers in this mode also. This is the number of db block gets.
• ROWS the total number of rows processed by this statement. The rows processed for SELECT statements will appear in the row of Fetch statistics. Inserts, updates, and deletes will appear in the Execute row.

3. Information: It contains information about the number of library cache misses from parse and execute calls. If the number of misses is high, there may be a problem with the size of the shared pool. It also contains the username of  the last user to parse this statement.   

4. EXPLAIN PLAN : This most useful section of  the TKPROF. The first column of this section is the number of rows processed by each line of the execution plan. We can identify how slow a statement is. If the total number of rows in the fetch statistics is low compared to the number of rows being processed by each line of the EXPLAIN PLAN.

DBMS_XPLAN.DISPLAY_CURSOR

DBMS_XPLAN.DISPLAY_CURSOR show actual execution plan from dynamic performance views.

DBMS_XPLAN:  The DBMS_XPLAN package provides an easy way to display the output of
the EXPLAIN PLAN command in several, predefined formats.You can also use the DBMS_XPLAN
package to display the plan of a statement stored in the Automatic Workload Repository (AWR) or
stored in a SQL tuning set. It further provides a way to display the SQL execution plan and SQL execution run time statistics for cached SQL cursors based on the information stored in the V$SQL_PLAN and SQL_PLAN_STATISTICS_ALL fixed views.

DISPLAY_CURSOR: To format and display the contents of the execution plan of any loaded cursor.

Parameter for DBMS_XPLAN.DISPLAY_CURSOR:
SQL_ID: sql_id we will get from V$SQL OR V$SQLAREA. Default this value as NULL.

STEPS:
1. Execute the query

select e.ename , e.sal, d.dname/*sql_execution2*/ 
 from emp e ,dept d where d.deptno = e.deptno; 

2. Then search SQL ID for the query that is executed

select sql_id,sql_text from v$sqlarea where sql_text like '%sql_execution2%';

OUTPUT:
SQL_ID                           SQL_TEXT
-----------------       ---------------------------
bt7bfjp0525vu      select e.ename , e.sal, d.dname/*sql_execution2*/ from emp e ,dept d where                   
3. Chose the correct SQL_ID write to your query, then execute the below query to get actual plan

select * from table(dbms_xplan.display_cursor('bt7bfjp0525vu'));

Oracle SQL TRACE & TKPROF utility

Oracle SQL TRACE utility to measure timing statistics for a given query.
SQL TRACE records many vital information regarding actual query execution into a trace file.
SQL TRACE helps developers analyze every section of a query.
It writes query execution statistics like number of logical I/O, physical I/O, the  CPU and elapsed timings, number of rows processed, query plans with row counts at each levels,information in wait events etc.
Its  bit difficult  to read the trace file .Using TKPROF we can generate readable report from trace file.

Steps for SQL TRACE 

---

1. We need to set the below parameters.

ALTER SESSION SET TIMED_STATISTICS=TRUE;

This enables and disables the collection of timed statistics , such as CPU  and elapsed time etc. The value  can be TRUE or FALSE.

Below is the default destination of trace file.

USER_DUMP_DEST = \oracle\product\10.2.0\admin\tsm\udump

2. Enable the SQL TRACE for a session.

ALTER SESSION SET SQL_TRACE=TRUE;

3. Run the query(run your SQL).

SELECT ENAME,EMPNO,DEPTNO,SAL FROM EMP WHERE EMPNO=10;

4. Disable the SQL TRACE .

ALTER SESSION SET SQL_TRACE=FALSE;

Steps for TKPROF

---

Once your trace file is ready then we need to run the TKPROF at the command line to generate the report.TKPROF accepts input as a trace file and  it produces a formatted output file(report).

Syntax:

tkprof tracefile output_file [sort = parameters] [print=number]
[explain=username/password] [waits=yes|no] [aggregate=yes|no] [insert=filename]
[sys=yes|no] [table=schema.table] [record=filename]

Details of important parameters:
Tracefile: This is the name of the SQL TRACE file containing the statistics by SQL_TRACE.
Output_file: This is the name of the file where TKPROF writes its output.
sort = parameters: A multiple number of sorting options are available.

• FCHCPU (CPU time of fetch); 
• FCHDSK (disk reads for fetch); 
• FCHCU and FCHQRY (memory reads forfetch); 
• FCHROW (number of rows fetched); 
• EXEDSK (disk reads during execute); 
• EXECU and EXEQRY (memory reads during execute); 
• EXEROW (rows processed during execute); 
• EXECPU (execute CPU time); PRSCPU (parse CPU); and 
• PRSCNT (times parsed).

print = number: This is the number of statements to include in the output. If this statement is not included, TKPROF will list all statements in the output.

Explain = username/password: Run the EXPLAIN PLAN on the user’s SQL statements in the TRACE file. This option creates a plan_table of its own,so the user will need to have privileges to create the table and space in which to create it. When TKPROF is finished,this table is dropped. Ensure that you use the username/password of the user that parsed the cursor (ran the query) to ensure the explain is by the correct user.

waits=yes/no: Record summary for any wait events.

aggregate=yes|no: If no, then tkprof does not combine multiple users of the same SQL text.

insert=filename: This option creates a script to create a table and store the TRACE file statistics for each SQL statement traced.

table=schema.table: The table in which tkprof temporarily put execution plans before writing them to the output file.

Once your trace file is ready then we need to run the below command:

5. Go by command line to (cmd) .

oracle\product\10.2.0\admin\oracle\udump

find your trace file with your SPID

6. Run TKPROF command .

TKPROF  [trace_file_name] [ output_report_file.prf]

EXPLAIN PLAN

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer  to execute a SQL statement.
EXPLAIN PLAN takes less than a minute to EXPLAIN a query that takes four hours to run because it does not actually execute the SQL statement, it only outlines the plan to use and inserts this execution plan in an Oracle table (PLAN_TABLE).

Why we will use EXPLAIN PLAN without TRACE?
The statement is not executed; it only shows what will happen if the statement is executed. 
When do you use EXPLAIN without TRACE?
When the query will take exceptionally long to run.

How to use EXPLAIN PLAN?

1. Create PLAN TABLE: Execute the script "utlxplan.sql". File location is below

oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxplan.sql    

2. EXPLAIN Query: Run the EXPLAIN PLAN for the query to be optimized 

EXPLAIN PLAN FOR
select ename,sal,empno,deptno
from emp
where deptno=10;    

Using Tag:

EXPLAIN  PLAN FOR

SET STATEMENT_ID='SQL1'

select ename,sal,empno,deptno
from emp
where deptno=10;

3. PLAN Table is populated: Select the output from PLAN TABLE

select operation, options, object_name, id, parent_id
from plan_table
where statement_id = 'SQL1'   

OR we can use below query to see the output in proper format 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);    

It shows the following information: 

• The row source tree is the core of the execution plan. 
• An ordering of the tables referenced by the statement
• An access method for each table mentioned in the statement
• A join method for tables affected by join operations in the statement
• Data operations like filter, sort, or aggregation.

In addition to the row source tree, the plan table contains information about the following: 

• Optimization, such as the cost and cardinality of each operation
• Partitioning, such as the set of accessed partitions
• Parallel execution, such as the distribution method of join inputs

The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query. 

Query processing can be divided into 7 phases :

• Syntactic          : Checks the syntax of the query
• Semantic          : Checks that all objects exist and are accessible
• View Merging  : Rewrites query as join on base tables as opposed to using views
• Statement Transformation : Rewrites query transforming some complex constructs into simpler ones where appropriate (e.g. subquery merging, in/or transformation)
• Optimization  : Determines the optimal access path for the query to take. With the Rule Based Optimizer (RBO) it uses a set of heuristics to determine access path.  With the Cost Based  Optimizer (CBO) we use statistics to analyze the relative costs of accessing objects.
• QEP Generation    : QEP = Query Evaluation Plan.
• QEP Execution      : QEP = Query Evaluation Plan.

In Toad how we will work on Explain plan :

Using toad , this can be achieved by following the steps below.

• Connect to the Oracle SID
• Open a SQL editor, and write the SQL query for which the explain plan is required.
• CTRL+E will produce the explain plan for the query - which basically means , this is the most likely path oracle will chose while executing the SQL. 
• Analyze the cost of the query , and identify the areas which are causing the cost to grow high.Mostly this happens when full table access is performed, or hashed joins are used , instead of full index scans and nested loops.
• This is the fastest way to identify if a query you have written has some tuning gaps and can be rewritten to perform better in distributed and scalable high volume environments.