SPM - SQL PLAN MANAGER

As a DBA some I feel that Oracle is living thing ! for example query performance. Out of no where a gentle looking SQL statement may pop up your top time consuming query list. A sql query which gives efficient performance in 3 months ago (Or even week ago), now giving horrible performance that threatening you cool database performance :)

Yes, we all DBA know that a gentle statement can screwed up due to
- Re-guthering optimizer statistics
- Change in optimizes parameters
- Change in Schema? Object structure (i.e, metadata definition)


Though Optimizer always try to choose list expensive plan for each SQL statement execution, Some time It (optimizer) choose expensive plans and raise annoying situations for peace loving DBAs :)

Well Oracle 11g come up with a new feature that can handle such weird situation. The simplest solution we can think is a mechanism that only allow trusted SQL plans to be executed and reject untrusted plans. O Yes Oracle 11g gives us a manager named SPM that do the same thing.

SQL PLAN MANAGER (SPM) has three major components

• SQL Plan Baseline Capture - Create SQL Plan Baseline for trusted (accepted) SQL plans.
• SQL Plan Baseline Selection - Conform that only accepted SQl plans are used when an SQL statement is executed.
• SQL Plan Baseline Evolution - Evaluated all SQL Plans (old & new) for each SQL statement. The new/old plan will only be excepted if it gives better or equal performance compared with existing trusted plan otherwise rejected. (If no trusted plan exist then current plan will be accepted)
  

To enable this cool feature we just do the followings

ALTER SYSTEM set optimizer_capture_sql_plan_baselines= TRUE scope=both; [Default value is false]
ALTER SYSTEM set optimizer_user_sql_plan_baselines= TRUE scope=both; [Default value is true, so just check the parameter value]

You are done ! Keep an eye in DBA_SQL_PLAN_BASELINES view and enjoying SPM's magic

Top N SQL Query Run within 30 minuts

When the database load increase rapidly it is even hard to log in Enterprise Manager (EM) to see what is the actual cause by seeing top activity or generating AWR report. Most of the time bad SQl is behind such problem. Here is a script which give you Top 20 (sort by elapsed time) SQL quer run on last 30 minutes

SELECT x.sql_id,x.sql_text,x.users,x.executions,x.disk_reads
,x.buffer_gets,x.cpu_time,x.elapsed_time FROM
(select sql_id,sql_text,PARSING_SCHEMA_NAME users
,executions, disk_reads,buffer_gets
,DECODE (executions,0,0,round(((cpu_time/1000000)/executions),2)) cpu_time
,DECODE (executions,0,0,round(((elapsed_time/1000000)/executions),2)) elapsed_time
from v$sqlarea
where parsing_user_id !=3D
and
LAST_ACTIVE_TIME<(sysdate-60/2600)
)x WHERE rownum <=20
ORDER BY x.elapsed_time DESC;

Virtual Indexes

For tuning SQL statements often requires the testing of alternate indexing strategies to see the affect on execution plans. Adding extra indexes to large tables can take a considerable amount of time and disk space. The additional indexes are available for use by other sessions, which may affect the performance of other parts of your application that you are not currently testing. This can be problematic when you are trying to identify problems on a production system.

In contrast to conventional indexes, a virtual index has no associated segment, so the creation time and associated disk space are irrelevant. In addition, it is not seen by other sessions, so it doesn't affect the normal running of your system. This article presents a simple example of how virtual indexes are used.


First, we create and populate a table

CREATE TABLE objects_tab AS SELECT * FROM user_objects;

Now we create an primary key on that table. So delete some rows where object_id is null.

DELETE from objects_tab where object_id is null;
COMMIT;

ALTER TABLE objects_tab ADD CONSTRAINT objects_tab_pk PRIMARY KEY (object_id);
EXEC DBMS_STATS.gather_table_stats('EMP', 'objects_tab', cascade=>TRUE);

If we query the table using the primary key, we can see this reflected in the execution plan.

SQL> SET AUTOTRACE TRACEONLY ;
SQL> SELECT * FROM objects_tab WHERE object_id = 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 2097082964

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| OBJECTS_TAB | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | OBJECTS_TAB_PK | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------


If we query the table using a non-indexed column, we see a full table scan

SQL> SELECT * FROM objects_tab WHERE object_name = 'LOGIN';


Execution Plan
----------------------------------------------------------
Plan hash value: 821620785

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 11 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| OBJECTS_TAB | 1 | 87 | 11 (0)| 00:00:01 |
----------------------------------------------------------------------------

To create the virtual index on this column, simply add the NOSEGMENT clause to the CREATE INDEX statement.

SQL> CREATE INDEX objects_tab_object_name_vi ON objects_tab(object_name) NOSEGMENT;

Index Created

If we repeat the previous query we can see the virtual index is not visible to the optimizer.

SQL> SELECT * FROM objects_tab WHERE object_name = 'LOGIN';


Execution Plan
----------------------------------------------------------
Plan hash value: 821620785

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 11 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| OBJECTS_TAB | 1 | 87 | 11 (0)| 00:00:01 |
----------------------------------------------------------------------------

To make the virtual index available we must set the _use_nosegment_indexes parameter.

SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

Session Altered

If we repeat the query we can see that the virtual index is now used.

SELECT * FROM objects_tab WHERE object_name = 'LOGIN';


Execution Plan
----------------------------------------------------------
Plan hash value: 4006507992

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| OBJECTS_TAB | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | OBJECTS_TAB_OBJECT_NAME_VI | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------


The virtual index does not appear in the USER_INDEXES view, but it present in the USER_OBJECTS view.

SQL> SET AUTOTRACE OFF;
SQL> SELECT index_name FROM user_indexes WHERE INDEX_NAME = 'OBJECTS_TAB_OBJECT_NAME_VI';

no rows selected

SQL> SELECT object_name FROM user_objects WHERE object_type = 'INDEX'
AND OBJECT_NAME='OBJECTS_TAB_OBJECT_NAME_VI';

OBJECT_NAME
----------------------------------------------------------------------------------------------------
OBJECTS_TAB_OBJECT_NAME_VI

1 rows selected.

Statistics can be gathered on virtual indexes in the same way as regular indexes, but as we have seen previously, there will be no record of this in the USER_INDEXES view.

SQL> EXEC DBMS_STATS.gather_index_stats('EMP', 'objects_tab_object_name_vi');

PL/SQL procedure successfully completed.

Oracle will prevent us from creating another virtual index with the same column list, but it will allow us to create a real index with the same column list.

SQL> CREATE INDEX objects_tab_object_name_vi2 ON objects_tab(object_name) NOSEGMENT;

CREATE INDEX objects_tab_object_name_vi2 ON objects_tab(object_name) NOSEGMENT
*
ERROR at line 1:
ORA-01408: such column list already indexed


SQL> CREATE INDEX objects_tab_object_name_i ON objects_tab(object_name);

Index created.


Related Links

1. Create INDEX
2. Execution Plan

ENABLE AUTOTTRACE

Any session can run the autotrace by SET AUTOTRACE ON at SQL*PLUS. But you will get the following error if it is not enabled.

ora816 SamSQL :> set autotrace on;
SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

To Enable autotrace, please do the following:

1.Run plustrce.sql through SYS schema if it did not run before as

ON NT :- @$ORACLE_HOME\sqlplus\admin\plustrce.sql
ON UNIX :- @$ORACLE_HOME/sqlplus/admin/plustrce.sql


2.Grant PLUSTRACE to

Also PLAN_TABLE must exist in the USER’s Schema ,if user want to do autotrace with explain plan . For creation of plan_table ,UTLXPLAN.sql have to be in user’s schema.

ON NT :- @$ORACLE_HOME\rdbms\admin\utlxplan.sql
ON UNIX :- @$ORACLE_HOME/rdbms/admin/utlxplan.sql


User can use the AUTOTRACE options as follows:

A. SET AUTOTRACE OFF - No AUTOTRACE report is generated. This is the default.

B. SET AUTOTRACE ON EXPLAIN - The AUTOTRACE report shows only the optimizer execution path.

C. SET AUTOTRACE ON STATISTICS - The AUTOTRACE report shows only the SQL statement execution statistics.

D. SET AUTOTRACE ON - The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.

E. SET AUTOTRACE TRACEONLY - Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any.

The Clustering Factor Of Index

The clustering factor is a number which represent the degree to which data is randomly distributed in a table. In simple terms it is the number of “block switches” while reading a table using an index.

Bad clustering factor

Suppose the first index entry (from left of index) points to the first data block and second index entry points to second data block. So while making index range scan or full index scan, optimizer have to switch between blocks and have to revisit the same block more than once because rows are scatter. So the number of times optimizer will make these switches is actually termed as “Clustering factor”.


Good clustering factor

In an event of index range scan, optimizer will not have to jump to next data block as most of the index entries points to same data block. This helps significantly in reducing the cost of your SELECT statements. Clustering factor is stored in data dictionary and can be viewed from dba_indexes (or user_indexes)

SQL> create table sac as select * from all_objects;

Table created.

SQL> create index obj_id_indx on sac(object_id);

Index created.

SQL> select clustering_factor from user_indexes where index_name='OBJ_ID_INDX';

CLUSTERING_FACTOR
-----------------
545

SQL> select count(*) from sac;

COUNT(*)
----------
38956

SQL> select blocks from user_segments where segment_name='OBJ_ID_INDX';

BLOCKS
----------
96

The above example shows that index has to jump 545 times to give you the full data had you performed full table scan using the index.

Note:
- A good Clustering Factor is equal (or near) to the values of number of blocks of table.

- A bad Clustering Factor is equal (or near) to the number of rows of table.

Myth:
- Rebuilding of index can improve the CF.

Then how to improve the CF?

- To improve the CF, it’s the table that must be rebuilt (and reordered).
- If table has multiple indexes, careful consideration needs to be given by which index to order table.

AWR top sql quarries

FOR EXECUTION : EXECUTIONS_DELTA
FOR CPU TIME: CPU_TIME_DELTA
FOR DISK READ: DISK_READS_DELTA
FOR BUFFER GETS: BUFFER_GETS_DELTA


SELECT SQL_TEXT,X.CPU_TIME FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHS.SQL_ID SQL_ID,SUM(DHS.CPU_TIME_DELTA) CPU_TIME
FROM DBA_HIST_SQLSTAT DHS
WHERE DHS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME>=TO_DATE('07/20/2008','MM/DD/YYYY')
AND END_INTERVAL_TIME<=TO_DATE('07/27/2008','MM/DD/YYYY'))
GROUP BY DHS.SQL_ID) X WHERE X.SQL_ID=DHST.SQL_ID
ORDER BY X.CPU_TIME DESC;

Segments Shrink

Among Windows users, disk drive fragmentation is a well known terminology. Regular data write and delete operations causes disk drive fragmentation. We usually avoid this by performing disk de-fragmentation.

Now the question is “Does Oracle has same problem ?”
If yes then, what is its remade ?

Unfortunately (i would say naturally :-) ) Oracle also has same problem because a data segment may experienced heavy UPDATE and DELETE. When the segment is rewrite, there is a scope to leave some free space within data blocks, just like disk fragmentation. This free space cause space waist and poor performance because Oracle need to scan more data block to fetch data. Now you should ask “What is the remade?” and the answer is “Segment Shrink”.


Before Oracle 10g

In earlier versions of Oracle, you can reclaim the free segment space by performing the following ways:

1. Create a table same as old table, move data from old table to new table and finally drop the old table.
   
2. Or create the new table as CAST (create table test2 as select * from test). drop test table and rename table test2 to test.
3. You also can move the table to another datafile by using ALTER TABLE MOVE command.

Both of the three processes, cause the table unavailable while performing operations. Another alternative way is 'Online table reorganization'. The benefit is, this time the table is available while performing 'Online table reorganization' but it consume significant disk space.


In Oracle 10g

You can shrink oracle data segments online that means you need not taking tablespaces or objects off line while performing shrinking . Shrinking of a data segment has two phases:

1. Compacted Segment Data: compacting objects. If the object is a table You must enable row movement so that rowid can changed and disable all triggers on that table. It also readjust High-water mark (HWM) to an appropriate location.
   
2. And Shrink Space: Free up unnecessary space.

Lets go to do the job !

ALTER TABLE TEST SHRINK SPACE COMPACT
--This command only compact the table object.

ALTER TABLE TEST SHRINK SPACE
--This command free up unnecessary space from table object.

1. How do you identify which segments should undergo shrinking?
2. How do you make this check process to a regular part of your system maintenance?
   

With Oracle Database 10g, you can easily gather those answer from Oracle Automatic Workload Repository (AWR) infrastructure. You can also use DBMS_ADVISOR expectingly Segment Advisor. You can also use Oracle Enterprise manager to perform the shrinking stuff with some clicks.

So check the cool feature of oracle now !

Online Segment Shrink

Based on the recommendations from the segment advisor you can recover space from specific objects using one of the variations of the ALTER TABLE ... SHRINK SPACE command:

-- Enable row movement.
ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

-- Recover space and amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE;

-- Recover space, but don't amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE COMPACT;

-- Recover space for the object and all dependant objects.
ALTER TABLE scott.emp SHRINK SPACE CASCADE;

The shrink is accomplished by moving rows between blocks, hence the requirement for row movement to be enabled for the shrink to take place. This can cause problem with ROWID based triggers. The shrinking process is only available for objects in tablespaces with automatic segment-space management enabled.

The COMPACT option allows the shrink operation to be broken into two stages. First the rows are moved using the COMPACT option but the HWM is not adjusted so no parsed SQL statements are invalidated. The HWM can be adjusted at a later date by reissuing the statement without the COMPACT option. At this point any depenedant SQL statements will need to be reparsed.

Bind variables

If you've been developing applications on Oracle for a while, you've no doubt come across the concept of «Bind Variables». Bind variables are one of those Oracle concepts that experts frequently cite as being key to application performance, but it's often not all that easy to pin down exactly what they are and how you need to alter your programming style to use them.

To understand bind variables, consider an application that generates thousands of SELECT statements against a table; for example:

SELECT fname, lname, pcode FROM cust WHERE id = 674;
SELECT fname, lname, pcode FROM cust WHERE id = 234;
SELECT fname, lname, pcode FROM cust WHERE id = 332;

Each time the query is submitted, Oracle first checks in the shared pool to see whether this statement has been submitted before. If it has, the execution plan that this statement previously used is retrieved, and the SQL is executed. If the statement cannot be found in the shared pool, Oracle has to go through the process of parsing the statement, working out the various execution paths and coming up with an optimal access plan before it can be executed. This process is know as a «hard parse» and for OLTP applications can actually take longer to carry out that the DML instruction itself.

When looking for a matching statement in the shared pool, only statements that exactly match the text of the statements are considered; so, if every SQL statement you submit is unique (in that the predicate changes each time, from id = 674 to id=234 and so on) then you'll never get a match, and every statement you submit will need to be hard parsed. Hard parsing is very CPU intensive, and involves obtaining latches on key shared memory areas, which whilst it might not affect a single program running against a small set of data, can bring a multi-user system to it's knees if hundreds of copies of the program are trying to hard parse statements at the same time. The extra bonus with this problem is that contention caused by hard parsing is pretty much immune to measures such as increasing available memory, numbers of processors and so on, as hard parsing statements is one thing Oracle can't do concurrently with many other operations, and it's a problem that often only comes to light when trying to scale up a development system from a single user working on subset of records to many hundreds of users working on a full data set.

The way to get Oracle to reuse the execution plans for these statements is to use bind variables. Bind variables are «substituion» variables that are used in place of literals (such as 674, 234, 332) and that have the effect of sending exactly the same SQL to Oracle every time the query is executed. For example, in our application, we would just submit

SELECT fname, lname, pcode FROM cust WHERE id = :cust_no;

and this time we would be able to reuse the execution plan every time, reducing the latch activity in the SGA, and therefore the total CPU activity, which has the effect of allowing our application to scale up to many users on a large dataset.

Bind Variables in SQL*Plus

In SQL*Plus you can use bind variables as follows:

SQL> variable deptno number
SQL> exec :deptno := 10
SQL> select * from emp where deptno = :deptno;

What we've done to the SELECT statement now is take the literal value out of it, and replace it with a placeholder (our bind variable), with SQL*Plus passing the value of the bind variable to Oracle when the statement is processed. This bit is fairly straighforward (you declare a bind variable in SQL*Plus, then reference the bind variable in the SELECT statement)

Bind Variables in PL/SQL

Taking PL/SQL first of all, the good news is that PL/SQL itself takes care of most of the issues to do with bind variables, to the point where most code that you write already uses bind variables without you knowing. Take, for example, the following bit of PL/SQL:

create or replace procedure dsal(p_empno in number)
as
begin
update emp
set sal=sal*2
where empno = p_empno;
commit;
end;
/

Now you might be thinking that you've got to replace the p_empno with a bind variable. However, the good news is that every reference to a PL/SQL variable is in fact a bind variable.

Dynamic SQL

In fact, the only time you need to consciously decide to use bind variables when working with PL/SQL is when using Dynamic SQL.

Dynamic SQL, allows you to execute a string containing SQL using the EXECUTE IMMEDIATE command. For next example would always require a hard parse when it is submitted:

create or replace procedure dsal(p_empno in number)
as
begin
execute immediate
'update emp set sal = sal*2 where empno = '||p_empno;
commit;
end;
/

The way to use bind variables instead is to change the EXECUTE IMMEDIATE command as follows:

create or replace procedure dsal(p_empno in number)
as
begin
execute immediate
'update emp set
sal = sal*2 where empno = :x' using p_empno;
commit;
end;
/

And that's all there is to it. One thing to bear in mind, though, is that you can't substitute actual object names (tables, views, columns etc) with bind variables - you can only subsitute literals. If the object name is generated at runtime, you'll still need to string concatenate these parts, and the SQL will only match with those already in the shared pool when the same object name comes up. However, whenever you're using dynamic SQL to build up the predicate part of a statement, use bind variables instead and you'll reduce dramatically the amount of latch contention going on.

The Performance Killer

Just to give you a tiny idea of how huge of a difference this can make performance wise, you only need to run a very small test:

Here is the Performance Killer ....

SQL> alter system flush shared_pool;
SQL> set serveroutput on;

declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 1000
loop
open l_rc for
'select object_name
from all_objects
where object_id = ' || i;
fetch l_rc into l_dummy;
close l_rc;
-- dbms_output.put_line(l_dummy);
end loop;
dbms_output.put_line
(round((dbms_utility.get_time-l_start)/100, 2) ||
' Seconds...' );
end;
/
101.71 Seconds...

... and here is the Performance Winner:

declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 1000
loop
open l_rc for
'select object_name
from all_objects
where object_id = :x'
using i;
fetch l_rc into l_dummy;
close l_rc;
-- dbms_output.put_line(l_dummy);
end loop;
dbms_output.put_line
(round((dbms_utility.get_time-l_start)/100, 2) ||
' Seconds...' );
end;
/
1.9 Seconds...

That is pretty dramatic. The fact is that not only does this execute much faster (we spent more time PARSING our queries then actually EXECUTING them!) it will let more users use your system simultaneously.

Bind Variables in VB, Java and other applications

The next question is though, what about VB, Java and other applications that fire SQL queries against an Oracle database. How do these use bind variables? Do you have to in fact split your SQL into two statements, one to set the bind variable, and one for the statement itself?

In fact, the answer to this is actually quite simple. When you put together an SQL statement using Java, or VB, or whatever, you usually use an API for accessing the database; ADO in the case of VB, JDBC in the case of Java. All of these APIs have built-in support for bind variables, and it's just a case of using this support rather than just concatenating a string yourself and submitting it to the database.

For example, Java has PreparedStatement, which allows the use of bind variables, and Statement, which uses the string concatenation approach. If you use the method that supports bind variables, the API itself passes the bind variable value to Oracle at runtime, and you just submit your SQL statement as normal. There's no need to separately pass the bind variable value to Oracle, and actually no additional work on your part. Support for bind variables isn't just limited to Oracle - it's common to other RDBMS platforms such as Microsoft SQL Server, so there's no excuse for not using them just because they might be an Oracle-only feature.

Related Topics:

1. Value of a bind variable
   

Oracle Session Resource Profiler

Introduction

In short, OraSRP is a profiler. It parses extended SQL trace files and creates report which allows you to learn where your session spent its time and why. OraSRP may generate reports either in html or in text format.

Features

1. Builds complete and detailed session profile (which includes such pseudo-events like 'PARSE/EXEC/FETCH calls' and 'unaccounted-for time').
2. Display results either in html or text format.
3. Display graph of statements calls.
4. Display events histograms.
5. Display various session statistics like 'blocks read', 'datafiles read', 'parallel execution' etc.
6. Group similar statements' statistics into one.
7. Display values of bind variables.
8. Able to parse trace files from Oracle version 7.2 upto 11g.
   

Installation

OraSRP comes in several forms:

1. Installer for Windows (orasrp-setup.exe). Just run it and follow usual instructions.
2. Binary for Windows (orasrp-windows.zip). Just unzip anywhere you want.
3. Binary for Linux (orasrp-linux.zip). Just unzip anywhere you want.
   

Usage

OraSRP is a command-line utility. Pass trace file name as a first parameter and output report name as a second. Like this:

$ orasrp trace.trc report.html

That's it.

Command-line options

Brief explanation:

$ orasrp -h
usage: orasrp [options] file [outfile]

options:
-h, --help show this help message and exit
--version show program's version number and exit
-t, --text output in text format
--aggregate=YES/no aggregate similar statements
-bn, --binds=n how many unique bind-sets to display (1 by defaut)
--maxbinds=n how many unique bind-sets to analyze (all by default)
--recognize-idle-events=YES/no recognize or not idle wait events
--sys=YES/no print sys statements
--sort how to sort statements in output (values like in tkprof)
--display-sections what sections of report to display (all by default)
--skip-sections what sections of report to skip
--sessionid analyze data only for the specified session id
--threshold omit statements which spend less than threshold % from total time (not used by defaut)
--google-charts display charts using Google Chart API

Downloads

Current version is 3.2.0 beta released 20 Dec 2007.

• Binary distribution for Windows.
  
• Binary installer for Windows.
  
• Binary distribution for Linux.

Samples

• Sample One (excessive parsing; demonstration of grouping similar statements' statistics)
• Sample Two (slow delete because of row lock contention)
• Sample Three (text output; display values of bind variables)

To find the location of Tracle File :

-bash-3.00$ sqlplus / as sysdba

SQL> show parameter dump_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /oracle/app/oracle/product/10.
2.0/db_1/admin/bdump
core_dump_dest string /backup2/Core_Dump
user_dump_dest string /backup2/User_dump

So the trace files are stored in /backup2/User_dump.Here you find lot of trace files, to identify which trace file has created for current session :

select d.value || '/ora_' || p.spid || '.trc' trace_file_name
from
( select p.spid from sys.v_$mystat m,
sys.v_$session s, sys.v_$process p
where m.statistic# = 1 and
s.sid = m.sid and p.addr = s.paddr
) p,
( select value from sys.v_$parameter
where name = 'user_dump_dest'
) d;

To Enable SQL TRACE:

alter session set sql_trace=true;
alter system set sql_trace=true scope=spfile;

Eliminate FULL TABLE ACCESS

In SQL quarry tuning one of the major goals is to eliminate FULL TABLE ACCESS. Using index we can achieve the goal. let see how it can be achieve

Suppose we have the following table

SQL> desc emp;

Name Type
---------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

Now enable the autotrace option to see sql quarry execution plan.

SQL> set autotrace on;
SQL>
SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1218 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 1218 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

Update some rows for experimant purpose.

SQL> update emp set sal=500 where sal<2000;
8 row updated.
SQL> commit;
Commit complete.

SQL> create unique index empno_emp on emp(empno);
Index created.
SQL> select empno from emp where empno>0;

EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 182 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 182 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

The EMPNO_EMP index is not used because we access all 14 row.


SQL> select empno from emp where empno<7700;

EMPNO
----------
7369
7499
7521
7566
7654
7698

6 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2992108249
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 78 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| EMPNO_EMP | 6 | 78 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------

Here you can see the index is used because we access a portion of 14 rows.

SQL> select empno from emp where sal=500;

EMPNO
----------
7369
7499
7521
7654
7844
7876
7900
7934

8 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 208 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 8 | 208 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

Again you can see the index is not used because indexed column empno in not prasent in the "WHERE" close.


To ellimenate full table scane of previous sql quarry create an index on sal column which is in the "WHERE" close.

SQL> create index sal_emp on emp(sal);

Index created.

SQL> select empno from emp where sal=500;

EMPNO
----------
7369
7499
7521
7654
7844
7876
7900
7934

8 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 216044341
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 208 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 8 | 208 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SAL_EMP | 8 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

See ! we r abole to elliminate the full table scan..


Learning: If you have an index and the indexed column is not present in "Where" close the index will not be used.

Let go to our second experimant. for this drop the previously build indexs

SQL> drop index empno_emp;

Index dropped.

SQL> drop index sal_emp;

Index dropped.

Create a composite index

SQL> create index EMPNO_SAL_EMP on emp(empno,sal);
Index created.


SQL> select empno from emp where empno<7900;

EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876

11 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 615958546
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 143 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| EMPNO_SAL_EMP | 11 | 143 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------

SQL> select empno from emp where sal=500;

EMPNO
----------
7369
7499
7521
7654
7844
7876
7900
7934

8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1761287244
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 208 | 1 (0)| 00:00:01 |
|* 1 | INDEX FULL SCAN | EMPNO_SAL_EMP | 8 | 208 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------

See EMPNO_SAL_EMP index experienced FULL SCAN because the quarry has sal (right part of index) in "WHERE" close.


SQL> select ename,empno,deptno from emp where deptno=30 and sal=500;

ENAME EMPNO DEPTNO
---------- ---------- ----------
ALLEN 7499 30
WARD 7521 30
MARTIN 7654 30
TURNER 7844 30
JAMES 7900 30


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 230 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 5 | 230 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

Again an interesting thing .. now the table experienced FULL SCAN because select close has non indexed column and sal (right part of index) in "WHERE" close.


SQL> select ename,deptno from emp where empno<7700;

ENAME DEPTNO
---------- ----------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30

6 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1330938245
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 198 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 6 | 198 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMPNO_SAL_EMP | 6 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

This time empno (left part of the index) prasent in the "WHERE"close.

Learning : if you have composite index, make sure all/most of columns are present in "where" close in same order (suppose you have 3 column index and 2 &3 are in where close, the index is not used, you must have 1,2,3 or 1,2 or 1,3 colume in "WHERE" close)

Automatic Shared Memory Management

Automatic Shared Memory Management puts Oracle in control of allocating memory within the SGA. The SGA_TARGET parameter sets the amount of memory available to the SGA. This parameter can be altered dynamically up to a maximum of the SGA_MAX_SIZE parameter value. Provided the STATISTICS_LEVEL is set to TYPICAL or ALL and the SGA_TARGET is set to a value other than "0" Oracle will control the memory pools which would otherwise be controlled by the following parameters:

1. BD_CACHE_SIZE (default block size)
   
2. SHARED_POOL_SIZE
3. LARGE_POOL_SIZE
   
4. JAVA_POOL_SIZE

If these parameters are set to a non-zero value they represent the minimum size for the pool. These minimum values may be necessary if you experience application errors when certain pool sizes drop below a specific threshold.
The following parameters must be set manually and take memory from the quota allocated by the SGA_TARGET parameter:

1. DB_KEEP_CACHE_SIZE
2. DB_RECYCLE_CACHE_SIZE
3. DB_nK_CACHE_SIZE(non-default block size)
4. STREAMS_POOL_SIZE
5. LOG_BUFFER

Enable Automatic Shared Memory Management

suppose we want to set sga_max_size = 1.5 GB and sga_target =1.2 GB

sql> shutdown immediate;
database shutdown.
sql> startup mount;
database mounted.
sql> alter system set SGA_MAX_SIZE = 1500M scope=spfile;
database altered.
sql> shutdown immediate;
database shutdown.
sql> startup ;
database open.
sql> alter system set SGA_TARGET = 800M scope=both;
database altered.

From now oracle dynamically manage the size of SHARED_POOL_SIZE,Buffer Cache, LARGE_POOL_SIZE,JAVA_POOL_SIZE and if needed oracle increase sga_target upto 1.5BG .

Use Multiple blocksizes in Oracle

The introduction of Oracle 9i brought an amazing amount of complexity to the Oracle database engine. Oracle introduced many new internal features, including bitmap free lists, redo log based replication, dynamic SGA, and perhaps the most important feature of all, the ability to support multiple block sizes.

Multiple blocksizes are indeed beneficial in large databases to eliminate superfluous I/O and isolate critical objects into a separate data buffer cache. This allows large tables and indexes to have a larger block size than smaller objects. This is especially useful in hybrid databases where DSS transactions benefit from large block sizes, whilst OLTP operations are best suited to smaller block sizes. It also means that tablespaces can be transported between databases with differing block sizes. The database is created with a standard block size and up to 5 none-standard block sizes.

The DB_BLOCK_SIZE is used to specify the standard block size which is used for the SYSTEM and TEMPORARY tablespaces. All subsequent tablespaces will also be assigned this block size unless specified otherwise. To see default block size of the Database :
SQL> conn system/password as sysdba;
SQL> show parameter DB_BLOCK_SIZE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192


The DB_CACHE_SIZE parameter, rather than DB_BLOCK_BUFFERS, is used to define the size of the buffer cache for the standard block size. This can be set to any size, but it will be rounded to the nearest whole granule. If the total SGA is less then 128M the granule size is 4M, greater than 128M and it becomes 16M. The DB_CACHE_SIZE must be at least 1 granule in size and defaults to 48M. An individual buffer cache must be defined for each non-standard block size used. These are set up with the following parameters which default to a size of 0

DB_2K_CACHE_SIZE = 0M
DB_4K_CACHE_SIZE = 0M
DB_8K_CACHE_SIZE = 0M
DB_16K_CACHE_SIZE = 0M
DB_32K_CACHE_SIZE = 0M

Each cache that is used must have at least 1 granule assigned to it. The instance must be restarted before changes to these parameters take effect. The DB_nK_CACHE_SIZE parameters can only be set to zero if there are no online tablespace with a nK block size.

The following example shows whole the process of setting up a 16K buffer cache and defining a tablespace to use it:

SQL> CONN sys/password AS SYSDBA
Connected.
SQL> ALTER SYSTEM SET DB_16K_CACHE_SIZE=50M SCOPE=SPFILE;
System altered.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 787968 bytes
Variable Size 78641664 bytes
Database Buffers 88080384 bytes
Redo Buffers 262144 bytes
Database mounted.
Database opened.

SQL> CREATE TABLESPACE test_16k_ts DATAFILE '/u01/oradata/TSH1/test_16k_ts01.dbf' SIZE 100M BLOCKSIZE 16M;
Tablespace created.
SQL>

The KEEP and RECYCLE buffer caches can only be defined for the standard block size. In 8i these were defined using the BUFFER_POOL_KEEP and BUFFER_POOL_RECYCLE parameters, with their memory was taken from the total DB_BLOCK_BUFFERS. In 9i the parameters have been changed to DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE, with their memory allocations being totally separate to the main buffer cache.

A number of rules apply to the use of variable block sizes:

1. All partitions of a partitioned object must reside in tablespaces with the same block size.
2. All temporary tablespaces must be of the standard block size.
3. Index Organized Table Overflow and out-of-line LOB segments can be stored in a tablespace with a different block size that that of the base table.

Gather Database Statistics

Whenever a valid SQL statement is processed Oracle has to decide how to retrieve the necessary data. This decision can be made using one of two methods:

1. Rule Based Optimizer (RBO) - This method is used if the server has no internal statistics relating to the objects referenced by the statement. This method is no longer favoured by Oracle and will be desupported in future releases.
2. Cost Based Optimizer (CBO) - This method is used if internal statistics are present. The CBO checks several possible execution plans and selects the one with the lowest cost, where cost relates to system resources.
   

If new objects are created, or the amount of data in the database changes the statistics will no longer represent the real state of the database so the CBO decision process may be seriously impaired. The mechanisms and issues relating to maintenance of internal statistics are explained below:

1. Analyze Statement
2. DBMS_UTILITY
3. DBMS_STATS
4. Scheduling Stats
5. Transfering Stats
   

Analyze Statement

The ANALYZE statement can be used to gather statistics for a specific table, index or cluster. The statistics can be computed exactly, or estimated based on a specific number of rows, or a percentage of rows:

ANALYZE TABLE employees COMPUTE STATISTICS;
ANALYZE INDEX employees_pk COMPUTE STATISTICS;

ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 100 ROWS;
ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 15 PERCENT;


DBMS_UTILITY

The DBMS_UTILITY package can be used to gather statistics for a whole schema or database. Both methods follow the same format as the analyze statement:

EXEC DBMS_UTILITY.analyze_schema('SCOTT','COMPUTE');
EXEC DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_rows => 100);
EXEC DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_percent => 15);

EXEC DBMS_UTILITY.analyze_database('COMPUTE');
EXEC DBMS_UTILITY.analyze_database('ESTIMATE', estimate_rows => 100);
EXEC DBMS_UTILITY.analyze_database('ESTIMATE', estimate_percent => 15);



DBMS_STATS

The DBMS_STATS package was introduced in Oracle 8i and is Oracles preferred method of gathering object statistics. Oracle list a number of benefits to using it including parallel execution, long term storage of statistics and transfer of statistics between servers. Once again, it follows a similar format to the other methods:

EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

EXEC DBMS_STATS.gather_schema_stats('SCOTT');
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15);
EXEC DBMS_STATS.gather_schema_stats(
ownname => 'SCOTT',
options => 'GATHER',
cascade => true,
estimate_percent => dbms_stats.auto_sample_size
);

exec dbms_stats.gather_table_stats( ownname => 'SCOTT',
tabname => 'EMP', cascade => true);

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15);

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK', estimate_percent => 15);

This package also gives you the ability to delete statistics:

EXEC DBMS_STATS.delete_database_stats;
EXEC DBMS_STATS.delete_schema_stats('SCOTT');
EXEC DBMS_STATS.delete_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.delete_index_stats('SCOTT', 'EMPLOYEES_PK');

For details Please go there
Or see the document: Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2)


Scheduling Stats

Scheduling the gathering of statistics using DBMS_Job is the easiest way to make sure they are always up to date:

SET SERVEROUTPUT ON
DECLARE
l_job NUMBER;
BEGIN

DBMS_JOB.submit(l_job,
'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
SYSDATE,
'SYSDATE + 1');
COMMIT;
DBMS_OUTPUT.put_line('Job: ' || l_job);
END;
/

The above code sets up a job to gather statistics for SCOTT for the current time every day. You can list the current jobs on the server using the DBS_JOBS and DBA_JOBS_RUNNING views.

Existing jobs can be removed using:

EXEC DBMS_JOB.remove(X);
COMMIT;

Where 'X' is the number of the job to be removed.



Transfering Stats

It is possible to transfer statistics between servers allowing consistent execution plans between servers with varying amounts of data. First the statistics must be collected into a statistics table. In the following examples the statistics for the APPSCHEMA user are collected into a new table, STATS_TABLE, which is owned by DBASCHEMA:

SQL> EXEC DBMS_STATS.create_stat_table('DBASCHEMA','STATS_TABLE');
SQL> EXEC DBMS_STATS.export_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');

This table can then be transfered to another server using your preferred method (Export/Import, SQLPlus Copy etc.) and the stats imported into the data dictionary as follows:

SQL> EXEC DBMS_STATS.import_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');
SQL> EXEC DBMS_STATS.drop_stat_table('DBASCHEMA','STATS_TABLE');

Issues

1. Exclude dataload tables from your regular stats gathering, unless you know they will be full at the time that stats are gathered.
2. Gathering statistics can be very resource intensive for the server so avoid peak workload times or gather stale stats only.
3. Even if scheduled, it may be necessary to gather fresh statistics after database maintenance or large data loads.
   

Avoid Sorting done by 'ORDER BY' clause

Sorting is mostly expensive, if you have an index, you can use it to avoid sorting ! Is NOT NULL constraint required? The following TEST CASES will answer your question.

TASTCASE 1:

Step1: Create a table EMP as

SQL> conn system/password as sysdba;
Connected.
SQL> CREATE TABLE HASAN.EMP AS SELECT * FROM SCOTT.EMP;
Table created.
SQL> conn hasan/password
Connected.

Step2: Query the EMP

SQL> set autotrace on explain;
SQL> select * from EMP ORDER BY EMPNO;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2007178810

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1218 | 4 (25) | 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 1218 | 4 (25) | 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0) | 00:00:01 |
---------------------------------------------------------------------------

Observation: Full Table Scan of EMP and SORTING IS DOING WITH EMPNO

Step3: Create a index TESTEMPIDX on EMPNO OF TESTEMP table as

SQL> create index emp_empno_indx on emp(empno);
Index created.
SQL> select * from emp order by empno;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 150391907

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1218 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 1218 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

Observation: Full Table Scan of EMP and SORTING (ORDERBY)

Step4: Create a NOT NULL Constraint on EMPNO of EMP table as

SQL> ALTER TABLE EMP MODIFY (EMPNO CONSTRAINT NOT_NULL_CONS NOT NULL);
Table altered.
SQL> desc emp;

Name Null? Type
----------------- -------- ------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)


SQL> select * from emp order by empno;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10


14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1969959806

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1218 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 1218 | 2 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | EMP_EMPNO_INDX | 14 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Observation: INDEX TESTEMPIDX of TESTEMP is USING, TABLE ACCESS BY INDEX ROWID AND NOSORTING

Learning:

1) Order by column should have NOT NULL constraint.
2) Index column should be in the Order by Clause

TESTCASE 2 :

SQL> desc emp;

Name Null? Type
----------------- -------- ------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> select index_name,column_name from user_ind_columns where table_name='EMP';

INDEX_NAME COLUMN_NAME
------------- --------------
EMP_EMPNO_INDX EMPNO


Step1: QUERY the EMP as

SQL> SELECT * FROM EMP ORDER BY ENAME;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7934 MILLER CLERK 7782 23-JAN-82 1300 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7369 SMITH CLERK 7902 17-DEC-80 800 20

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30


14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 150391907

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1218 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 1218 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------


Observation: After adding ENAME in Order by clause, index TESTEMPIDX is not using

Step 2: Create a Composite index on EMPNO and ENAME and run a SQL again as

SQL> CREATE INDEX TESTEMPIDX2 ON EMP(EMPNO,ENAME);
Index created.
SQL> SELECT * FROM EMP ORDER BY EMPNO,ENAME;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10


14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1356242066

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1218 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 1218 | 2 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | TESTEMPIDX2 | 14 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Observation: INDEX TESTEMPIDX2 of EMP is USING, TABLE ACCESS BY INDEX ROWID AND NOSORTING

Step3: alternate the order of EMPNO and ENAME and run a SQL again as

SQL> SELECT * FROM EMP ORDER BY ENAME,EMPNO;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7934 MILLER CLERK 7782 23-JAN-82 1300 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7369 SMITH CLERK 7902 17-DEC-80 800 20

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30


14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 150391907

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1218 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 1218 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

Observation: Full Table Scan of EMP and SORTING (ORDER BY) (THE INDEX TESTEMPIDX2 is not used !)

Conclusion:

1. All the Order by column should be in composite index

2. Order by columns should be of the same order as of composite index

3. At least one of the composite columns should have NOT NULL constraint.