Explain Plan in Oracle

Easiest way to SQL Tuning

Here is the sample format:

explain plan for your-precious-sql-statement;

select * from table(dbms_xplan.display);

Sample Query:

explain plan for
select * from srgt_guarantee where srgt_facility_grading_id=1882

select * from table(dbms_xplan.display);

Here is the Results:

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 |
| 1 | SORT AGGREGATE | | 1 | |
| 2 | INDEX FAST FULL SCAN| PK_SRGT_GUARANTEE | 763 | 2 |
-------------------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version

You can also create your own plan table in ur schema. Howevery it shud be in the following format:

CREATE TABLE SRGT_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)
);

and you can explain plan the query into the table:

explain plan into SRGT_PLAN_TABLE for (select context_id,EFF_TIMESTAMP from srgt_ref_client_info
where srgt_ref_client_id=(select srgt_ref_client_id from srgt_client_grading_info where
srgt_client_grading_id=(select srgt_client_grading_id from srgt_v_facility_grading_info
where srgt_ref_facility_id=2113)))

SELECT * FROM SRGT_PLAN_TABLE


Deafult way of doing it is:

explain plan FOR (select context_id,EFF_TIMESTAMP from srgt_ref_client_info
where srgt_ref_client_id=(select srgt_ref_client_id from srgt_client_grading_info where
srgt_client_grading_id=(select srgt_client_grading_id from srgt_v_facility_grading_info
where srgt_ref_facility_id=2113)))

select * from table(dbms_xplan.display);

will display the results of the query just executed =)



---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 13 |
| 1 | TABLE ACCESS BY INDEX ROWID | SRGT_REF_CLIENT_INFO | 1 | 14 | 2 |
| 2 | INDEX UNIQUE SCAN | PK_SRGT_REF_CLIENT_INFO | 1 | | 1 |
| 3 | TABLE ACCESS BY INDEX ROWID| SRGT_CLIENT_GRADING_INFO | 1 | 8 | 2 |
| 4 | INDEX UNIQUE SCAN | PK_SRGT_CLIENT_GRADING_INFO | 1 | | 1 |
| 5 | NESTED LOOPS OUTER | | 1 | 22 | 9 |
| 6 | NESTED LOOPS OUTER | | 1 | 18 | 9 |
| 7 | TABLE ACCESS FULL | SRGT_FACILITY_GRADING_INFO | 1 | 14 | 9 |
| 8 | INDEX UNIQUE SCAN | PK_SRGT_REF_FACILITY_INFO | 1 | 4 | 0 |
| 9 | INDEX UNIQUE SCAN | PK_SRGT_TEMP_FACILITY_INFO | 1 | 4 | 0 |
---------------------------------------------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version


Setting and using a Statement Id in Explain Plan:

explain plan SET STATEMENT_ID = 'srgt context' FOR (select context_id,EFF_TIMESTAMP from srgt_ref_client_info
where srgt_ref_client_id=(select srgt_ref_client_id from srgt_client_grading_info where
srgt_client_grading_id=(select srgt_client_grading_id from srgt_v_facility_grading_info
where srgt_ref_facility_id=2113)))


Form Plan table execute this query to get the values:

SELECT operation, options, object_name, id, parent_id, position, cost, cardinality,
other_tag, optimizer
FROM plan_table
WHERE statement_id = 'srgt context'
ORDER BY id;

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


Now I know abt explain plan in Oracle..!! yay !!

-Sunita

Rownum in Oracle --Something cool..!!!

Here is how you retrieve the first row which meets the crieteria from amoung a set of rows.. =)


select * from srgt_guarantee where srgt_facility_grading_id=1882

will return multiple rows like:
Gid
2408 1882 on 1 50 -1 455 [NULL] YES NO 40
2409 1882 on 23 20 -1 100 [NULL] YES NO 50
2410 1882 [NULL] 24 100 -1 190 [NULL] YES NO 40
2414 1882 [NULL] 1000 100 -1 100 [NULL] YES NO 52
2411 1882 [NULL] 68 100 -1 100 [NULL] YES NO 40
2412 1882 [NULL] 20 100 -1 100 [NULL] YES NO 50
2413 1882 [NULL] 900 100 -1 100 [NULL] NO NO 50

now if you do this will return just a single row =)

select * from srgt_guarantee where srgt_facility_grading_id=1882 and rownum=1;

Will return just a singl row. =)

2408 1882 on 1 50 -1 455 [NULL] YES NO 40


This cool to explore
enjoy
sunita

JDK JRE problem surfaced again

My weblogic is running on a 4.8 version on jdk/jre

and my ant is using 5.11 to compile so my deployment is failing.

So the solution is:

got: C:\bea\weblogic81\server\bin --> open startWLS.cmd

check where your JAVA_HOME is pointing to : its this variable

set JAVA_HOME=C:\bea\jrockit81sp5_142_08

Make everything else in your system point to here..

change ur env variable to point to here C:\bea\jrockit81sp5_142_08

1) my computer--> properties --> advanced-->environment variables-->edit JAVA_HOME

2) In Eclipse window-->preferences-->Ant -->Runtime --> GlobalEntries --> get tools.jar from C:\bea\jrockit81sp5_142_08\jdk\tools.jar

3) In Eclipse window-->preferences-->Java-->BuildPath-->edit JAVA_HOME to point to C:\bea\jrockit81sp5_142_08

4) In Eclipse window-->preferences-->MyEclipse-->ApplicationServers-->Weblogic8-->
JDK -->wls JDK make it point to C:\bea\jrockit81sp5_142_08

5) In Eclipse Project-->Properties-->JavaBuildPath-->Libraries(tab) make your jre point to C:\bea\jrockit81sp5_142_08


Now you are good to go.. all your JAVA_HOME are pointing to one single place =)

--Had fun with Vipul trying to figure this out. =) Finally I learnt a new thing :)

--Suneetha.

DBMS_UTILITY.GET_TIME()

Sample code to test if dbms_utility is working

SELECT name
FROM dba_dependencies
WHERE referenced_name = 'DBMS_UTILITY'
UNION
SELECT referenced_name
FROM dba_dependencies
WHERE name = 'DBMS_UTILITY';

o/p:
ALL_OBJECTS
etc.,

once u successfully execute this w/o exceptions..ur ready to use the dbms_utility.get_time() in the stored procs

dbms_output.put_line('time: ' || ROUND((dbms_utility.get_time() - nStart)/100, 3) || ' s');

Know More: http://thinkoracle.blogspot.com/2006_08_01_archive.html

Cool function..to estimat ur sql times

Ant cannot compile java files

[javac] BUILD FAILED: [build file location here]
Unable to find a javac compiler;
com.sun.tools.javac.Main is not on the classpath.
Perhaps JAVA_HOME does not point to the JDK

You get this err in Eclipse when trying to compile it with ANT

Its really cool to resolve this issue =)

Go to Window --> Preferences -->Ant-->Runtime-->Global Entries --> Add External Jars

Add the following Jar from your java home,

In my case its C:\Program Files\Java\jdk1.5.0_11\lib\tools.jar

yeppee..!!! and it all worked magically when I hit build again.. cool I love ant..!!!!

To Learn More go to: http://www.dynamicobjects.com/d2r/archives/002591.html


--Sonu

Detectin applications which load on startup in windows xp.
here is the path where you look for..

C:\Documents and Settings\All Users\Start Menu\Programs\Startup
C:\Documents and Settings\{Username}\Start Menu\Programs\Startup

hehe I didnt know this bfr =)

Substr,Length of Str in Oracle

This is the trickest I think I have ever been thru... hmm I kind of hated it as the string in Oracle starts from 1 and not from 0.

here is an example:

l_s_mystring varchar2(20):='suneetha';

l_s_firstString:=substr(l_s_mystring,1,1);

l_s_firstString1:=substr(l_s_mystring,3,1);

l_s_firstString2:=substr(l_s_mystring,5,1);

will print s,n,e etc

here is how it processes

S U N E E T H A
1 2 3 4 5 6 7 8

So when u ask for substr(l_s_mystring,1,1) will print s

when u ask for substr(l_s_mystring,1,3) will print SUN (1 AND 3 INCLUDED)

this sucks and I totally hate it grrrrr!!!!!

So dont forget this from next time onwards :)


Function to get the length of the string. This is soo different than in Java
so had to make a note :)

select length(to_char('suneetha')) from dual;

syntax: length()