HowToOracle:Underlying query tuning in Oracle

A tons of materials are there in net.
I found the followings are good enough to tell the internals and explains the basics:-
http://philip.greenspun.com/sql/tuning.html
http://www.billmagee.co.uk/oracle/sqltune/index.html

The following site describes how a query can be re-written in different ways:-
http://www.wisc.edu/drmt/oratips/sess006.html

The Orafaq page which answers why oracle dont use an index or in other way when a developer should go for index and when not.
http://www.orafaq.com/wiki/Oracle_database_Performance_Tuning_FAQ

And finally Oracle`s doc:-
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_1016.htm#g42927

My Own research:-
The performance of a query against a oracle database depends
*> on the hardware
*> The database parameters.
*> the query you are firing.

Database parameters usually defined according to the future query which will may be fired against it.

I did some experiments on query level.

1/ Avoid view to use in query if possible.And if there is a multiple layer of views where the results is coming up from other views it becomes much more costly.

SQL> SELECT DISTINCT e.deptno, dname deptname
2 FROM scott.dept D,
3 scott.emp E
4 WHERE D.deptno = E.deptno;

DEPTNO DEPTNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 652980069

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 135 | 5 (20)| 00:00:01 |
| 1 | HASH UNIQUE | | 9 | 135 | 5 (20)| 00:00:01 |
| 2 | NESTED LOOPS | | 14 | 210 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMP | 14 | 28 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("E"."DEPTNO" IS NOT NULL)
5 - access("D"."DEPTNO"="E"."DEPTNO")


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
540 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

SQL> create view test_view as SELECT DISTINCT e.deptno, dname deptname
2 FROM scott.dept D,
3 scott.emp E
4 WHERE D.deptno = E.deptno;

View created.

Elapsed: 00:00:00.07
SQL> select * from test_view;

DEPTNO DEPTNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1801966990

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 198 | 5 (20)| 00:00:01 |
| 1 | VIEW | TEST_VIEW | 9 | 198 | 5 (20)| 00:00:01 |
| 2 | HASH UNIQUE | | 9 | 135 | 5 (20)| 00:00:01 |
| 3 | NESTED LOOPS | | 14 | 210 | 4 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMP | 14 | 28 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("E"."DEPTNO" IS NOT NULL)
6 - access("D"."DEPTNO"="E"."DEPTNO")


Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
25 consistent gets
0 physical reads
0 redo size
540 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

SQL> spool off


2/ Use bind variables if your specific query is about to execute many times inside a loop.
Because when you use bind variables oracle picks up earlier query from shared pool.See the following example and look for the time taken after using bind variable.

The DDL of src_rand table is
SL_NO NUMBER(7),
RANDNUM VARCHAR2(14 BYTE),
RANDSTR VARCHAR2(45 BYTE)

SQL> select * from scott.src_rand where rownum < 4;

SL_NO RANDNUM RANDSTR
---------- -------------- ---------------------------------------------
556 kbtnpfiddmfu 371555940865.396909387092321361009129498
557 ylxiddflorsp 462786513408.184026987188015346255644919
558 detqtvbxxtpa 686749281069.89158302932789178713581963

Elapsed: 00:00:00.64

SQL> declare
2 i number(8) := 0;
3 begin
4 for i in 1..10000 loop
5 execute immediate
6 'select sl_no from scott.src_rand where sl_no='||i;
7 end loop;
8 end;
9 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.65
SQL> declare
2 i number(8) := 0;
3 begin
4 for i in 1..10000 loop
5 execute immediate
6 'select sl_no from scott.src_rand where sl_no = :x' using i;
7 end loop;
8 end;
9 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.37
SQL> spool off

HowToOracle:How to check explain plan in sql* plus?

Check on Burleson page on SQL Execution Plans
http://www.dba-oracle.com/plsql/t_plsql_plans.htm