p6spy Software.

This software is used in the applications as of which database queries are fired on each click from UI.By this , life becomes easier to analyze the queries and optimize them.

Go to the below link for downloading and details :- (download p6spy-install.zip)

http://www.p6spy.com/download.html

The zip file has steps to setup the p6spy.
Its pretty easy.

Oracle explain plan

When u got to know how does ur query is scanned by the Oracle, then here is the way u can get it.
Its called 'explain plan' which describes the how the query is analyzed and how it is looking the tables and also how effecient the indexes are used.

If you got to know more abt it, well there are many sites for it. Here is one for quick reference

http://www.csee.umbc.edu/help/oracle8/server.815/a67775/ch13_exp.htm


Here are the couple of things you need to create and do for getting a explain paln for a query.

Step 1 :- Create table plan_table.

CREATE TABLE PLAN_TABLE (
STATEMENT_ID VARCHAR2(30),
TIMESTAMP DATE,
REMARKS VARCHAR2(80),
OPERATION VARCHAR2(30),
OPTIONS VARCHAR2(30),
OBJECT_NODE VARCHAR2(128),
OBJECT_OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(30),
OBJECT_INSTANCE NUMBER(38),
OBJECT_TYPE VARCHAR2(30),
OPTIMIZER VARCHAR2(255),
SEARCH_COLUMNS NUMBER,
ID NUMBER(38),
PARENT_ID NUMBER(38),
POSITION NUMBER(38),
COST NUMBER(38),
CARDINALITY NUMBER(38),
BYTES NUMBER(38),
OTHER_TAG VARCHAR2(255),
PARTITION_START VARCHAR2(255),
PARTITION_STOP VARCHAR2(255),
PARTITION_ID NUMBER(38),
OTHER LONG,
DISTRIBUTION VARCHAR2(30)
);

Step 2 :- execute the following in your sql

explain plan for
paste your query here
select
substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation",
object_name "Object"
from
plan_table
start with id = 0
connect by prior id=parent_id;

truncate table plan_table;

By this you would be getting the detailed plan of a query.

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

And if you want the statistics of the query, try the below one :-

SET AUTOTRACE ON EXPLAIN STATISTICS;
ALTER SYSTEM FLUSH SHARED_POOL;
set timing on;
paste your query here

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

Its always good that before using any query in your project, try do explain plan which may lead you to better query performance and optimization.

Tips:
- Bitmap indexes are very faster than the normal unique one but use appropriately.
- Create index on every foreign key you create. Its a good practice.
- If a table is used more for reading , create more indexes on it. And if a table is used for writing
then avoid creating indexes as it increases the cost of creation.


Here are list of few basic things abt sql oracle as i am still a learner. You get many sites to refer.You have to just remember them. No need to note down like i am doing ;)

- for concate : - ||
- for quote :- '' (type two times)
- for storing query output
- type :- query and enter
- type :- spool C:\sample.sql
- type :- /
- type :- spool off

- for making ur header off
- set heading off

Link for ref:
http://pages.cs.wisc.edu/~dbbook/openAccess/thirdEdition/Oracle/user_guide/oracle_guide.html

. . . .