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.

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> <output_report.prf>

Decode Vs Case

Everything DECODE can do, CASE can. There is a lot more that you can do with CASE, though, which DECODE cannot.
Differences between DECODE and CASE are listed below:

---

1. DECODE performs an equality check only but CASE can work with logical operators other than "=".
2. DECODE works with expressions that are scalar values only. CASE can work with predicates and subqueries in searchable form.
3. CASE is a statement while DECODE is a function.
4. DECODE and CASE treats NULL differently. Normally, including CASE, NULL = NULL results in NULL, however when DECODE compares NULL with NULL result is TRUE.
5. CASE expects datatype consistency, DECODE does not.
6. CASE complies with ANSI SQL. DECODE is proprietary to Oracle.

Example 1: DECODE and CASE treats NULL differently

SELECT  DECODE(NULL,NULL,-1) decode,

            CASE NULL WHEN NULL THEN -1 END case

 FROM  DUAL;
/

Output:

  DECODE       CASE
---------- ----------

        -1

Example 2: CASE expects datatype consistency, DECODE does not

SELECT  CASE 2 WHEN 1 THEN 1 WHEN to_number('2') THEN to_number('2') END case
FROM  DUAL
/
Output:
      CASE
----------
         2

SELECT  DECODE(2,1,1,'2','2') decode
FROM  DUAL
/

DECODE
-------
     2

SELECT  CASE 2 WHEN 1 THEN 1 WHEN '2' THEN '2'  END case
FROM  DUAL
/
SELECT  CASE 2 WHEN 1 THEN 1 WHEN '2' THEN '2'  END case
                                  *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR