Well, some information about how to go about generating and reading explain plan of a SQL query. Explain plan gives the information about the access path that Oracle is going to follow to execute your query [explain plan doesn't actually execute the query, it just tells about the access path that Oracle will follow to execute the query; that too in current session, with all the current settings, it may be totally different in another session on the very same database].

So after reading the explain plan of a particular query, we can see that is it executing efficiently ? Are the indexes being used ? and so on. Let us go through the basic setup required to use explain plan.

First of all we need to create a table called PLAN_TABLE (It is the default name used by Oracle, you can use any other name also. But then, you would need to mention the name every time you run the explain plan stuff) where explain plan will store the details of the query plan.

To create this table, run the script provided by Oracle $ORACLE_HOME/rdbms/admin/utlxplan.sql

I would like to mention the Tom Kyte approach here. In his book Effective Oracle by design, Tom suggests to create plan_table as a GLOBAL TEMPORARY TABLE with the ON COMMIT PRESERVE ROWS option and then granting all privileges on PLAN_TABLE to public. Also create a public synonym, so that everyone can use the same table.

Now when the plan table has been created, we are ready to go. Lets do EXPLAIN PLAN of a query:

For the first time, we are going to use the simplest “Hello World” query: select * from emp; So lets see…

SQL> explain plan for2  select * from emp;Explained.

This is the way to run EXPLAIN PLAN for some query. I simply write explain plan for and then the query itself. Now this Explained means Oracle has stored all the details n PLAN_TABLE and we have to read that data to see the details.

Again to view those details, Oracle provides a script called $ORACLE_HOME/rdbms/admin/utlxpls.sql. For your convenience you can ed in your session and create this script over there itself as I did with the name explain.

So its the time to see that explain plan which we have just run. As I said I run the script explain:

select * from emp;

SQL> @explainPLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 3956160932--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |

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

8 rows selected.SQL>
Now we are ready to use it. Well, lets use a query based on emp and deptno tables.
select * from emp,deptwhere emp.deptno=dept.deptno;
To see explain plan of this query we write
SQL> explain plan set statement_id=’q1′ for  2  select * from emp,dept  3  where emp.deptno=dept.deptno;Explained.SQL>
This is the syntax for using explain plan. set statement_id we use to store multiple plans in the plan table. Then we write for and the SQL query we want to see explain plan for. SQL Plus will prompt “Explained” and we are back to SQL prompt. Now the plan has been stored in PLAN_TABLE table and we need to read it. For that Oracle provides us with a script called utlxpls.sql (in $ORACLE_HOME/rdbms/admin/). [I generally create the same script in bin folder, so that to run it I can simply write @utlxpls]
SQL> @utlxplsPLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------| Id  | Operation                    |  Name       | Rows  | Bytes | Cost  |----------------------------------------------------------------------------|   0 | SELECT STATEMENT             |             |       |       |       ||   1 |  NESTED LOOPS                |             |       |       |       ||   2 |   TABLE ACCESS FULL          | EMP         |       |       |       ||   3 |   TABLE ACCESS BY INDEX ROWID| DEPT        |       |       |       ||*  4 |    INDEX UNIQUE SCAN         | PK_DEPT     |       |       |       |----------------------------------------------------------------------------PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")Note: rule based optimization17 rows selected.SQL>
These are the details that explain plan has provided us. Now there comes interpretation of this information. First of all to figure out what happens first, second and so on ? Well this is shown by the indentation of various steps in plan_table_output. The rightmost step (the most indented one) is executed first and then the 2nd most indented and so on. So in our case step with id 4 is executed first, then 2 and 3, and then 1. In predicate information we can see that…….
to be continued…  

Well, here I am listing down the important tools and utilities I seem to use on the daily basis and without them life on internet & laptop really seems to be stuck, sort of

1. Winamp: No need to say anything. Winamp is possibly the best and lightest mp3 (these days videos also) player. Just run Winamp in the system tray and enjoy

2. Winrar: Utility to uncompress compressed files. Earlier winzip was my favorite but recently I swichted to Winrar as Winzip doesn’t support rar format. I am pretty happy using it.

3. Mozilla: I have almost stopped using Internet Explorer (except with some of the websites which say flat NO, sort of, to Mozilla). It is really good. With this I have stopped using any standalone download manager (DAP and Flashget have been among my favorites) also as Mozilla has one inbuilt and there are many addons also available.

4. Messengers: The latest communication channel: messengers. Latest versions of Yahoo, Gtalk and Skype.

5. uTorrent: Recently I started using torrents also. uTorrent, I am using as torrent client. It is light weight and pretty heavy to use. The only issue I have with it is that there is no feature like automatic bandwidth management. I have manually set a speed for download and upload as assiging the full badwidth to utorrent screws up normal surfing. And with manual settings when I am not surfing some of the bandwidth goes wasted

6. Realplayer Alternate: I was a typical user who would stick with Real Player though its pretty heavy and eats a lot of resources. Sometime back my friend Vaibhav suggested me to go for Real Player alternate. I am happy using it now. very simple, small and precise

7. Replay Music: Just a new thing in my life too. A tool to record streaming audio, infact any audio coming out of sound card. Good one, saves as mp3 and has a very simple interface, just start, stop buttons and you are done.

8. RSS reader: Again a new item I am experimenting with. Till now I havn’t been able to find a good one. Generally the interface is clumsy and you don’t enjoy reading in that small window. I used RSS Reader first and using Omea Reader these days.

9. FSL Super finder: A replacement of windows search. I never liked (I seem to hate, indeed) windows search after Windows 2000. In XP it is totally screwed up. This one is a free utility with good interface and speed.

Use of all these tools makes me a happy user & surfer

Cheers
Sidhu

I was going through Eddie Awad’s blog. There was one post about an article about NO_DATA_FOUND exception in Oracle. Both the posts you can find here and here. Do read. It is really interesting.

Sidhu

There are times when you just can’t do anything, I repeat, you just can’t do anything. In the evening I was working on my laptop and everything was fine. Next day in the morning when I got up, switched on the laptop and clicked on IE icon, it happily gave a beautiful error message “This application failed to start because msvcrl.dll was not found. Re-installing the application may fix the problem.” So it was the time for Googling about the error. Thank God I had Opera installed. After 2-3 hits I came to know that it was a trojan. Finally, there was one dll which needed to be deleted to get rid of this error message But after that there was some problem with IE in opening some of the sites. One option was to upgrade to IE 7 but don’t know why, I havn’t started liking IE 7 yet. So no other option left than restoring windows (I have Windows XP Media Center Edition installed).

On all the laptops HP creates a partition where they store all the crap to restore windows back to original condition. They call it recovery partition (It eats upto 11-12 Gigs of space and recovers nothing really In the recovery tool provided by HP, there are 2 options one is called normal recovery which will restore the OS and won’t touch your data (They say so but I read many posts where people talked about having lost everything to this so called normal recovery another is called destructive recovery (I just love the name :-* ) which erases everything on the hard drive and restore back the system to factory shipped condition (Term courtesy HP). Obviously I didn’t want to lose any data. So I tried with normal recovery and it didn’t work actually. So I contacted HP for the same and asked about any other option. They said NO in a very stylish manner as technical people are taught to do.

Now the only option left was to go for destructive recovery. I got some blanks DVDs and burned the data so as to free my hard drive. Finally after a few hours of work my hard drive was ready to face destructive recovery. I ran the tool and it worked out something for around 40 mins showing me a progress bar for which I waited to complete as a lover would wait for his watch to strike the moment when his sweet heart has to come Finally it was 100% complete and system rebooted. You know to find everything intact as if not even touched. So started looking for why the hell this 12 GB recovery partition if it has to do nothing ?

I also had Fedora Core installed so one thought struck that might be possible it is not able to do because of those Linux partitions, so deleted those partitions also and then tried but of no use. Finally only one thing was left that delete all the partitions and give the whole space to one partition and then try. Thank God it worked and my laptop was back with a fresh installation of windows. (Later on I came to know that existing of more than one partition was the reason why normal recovery also didn’t work and few more interesting things - in all the cases it didn’t give any error that was not able to restore or something and in case of normal recovery they delete all the softwares installed later but their shortcuts will still be there ? ridiclous, foolish, stupid ). I already had made my mind to make Norton Ghost image of the C drive to avoid this operation again in future. Before making the image, many things had to be done like configuring internet connection, updating windows, installation of some utilities & uninstalling Norton Internet Security that I got pre-installed along with the OS.

I use Sify broadband for internet. So installed the sify dialer and when I tried to connect it gave one strange bloody,out of hell message that “You don’t have any anti-virus installed, click here to download updated antivirus from Sify”. I just can’t understand why the hell Sify is worried about anti-virus on my system. As I said at sometimes you just can’t do anything, had to install Norton Internet Security again to be able to connect to internet. Later on I got another command based dialer Supersify for Sify from internet developed by an individual (Many thanks to him (using it I can connect to internet with AVG Free anti-virus installed which is not recognised by Sify’s dialer as a good anti-virus) & one of my friend Vaibhav who googled this thing for me and was with me on voice during whole of this story which lasted for a day, a good short story it could make on a TV channel broadcasting peoples’ frustrations with the technology) So after sweating out for one full day my laptop was back to normal

PS: If you just got a laptop and Windows is in fine condition, please make an image of your C drive using some tool. Other wise probably you also have to write a blog

Sidhu