Friday, December 18, 2009

Its been a while...

Its been quite some time since I wrote anything to this blog, let alone anything valuable.

However, last few months have been quite eventful in my personal and professional life. I have landed a job with a company in Bangalore now, which works a lot more with open source systems/applications than my previous employer. I can easily say that this was one of the points in my decision making.

Currently, I am opening up to working on Ubuntu and probably virtualization, mysql, postgresql and most importantly, Informatica Powercenter and Pentaho. The suite of tools is just too exciting for me to find right words to express my feelings.

Recently I have picked up a project to do a Proof of Concept for using an open source columnar database and compare its behavior with oracle for a given application. Of course it involves data loading, and analytical queries, so there would be some interesting stats collection and comparison. Attempts to break a system are always more interesting than to make it J

Lets see how it goes…

Also, I would try to be more regular on the blog now onwards…

Thursday, July 2, 2009

Compile all packages in a schema

FOR cur IN
FROM all_objects
AND owner = ':OWNER
AND status = 'INVALID'
EXECUTE IMMEDIATE 'alter package "' || cur.owner || '"."' || cur.OBJECT_NAME ||
'" compile body';
EXECUTE IMMEDIATE 'alter ' || cur.OBJECT_TYPE || ' "' || cur.owner || '"."' ||
cur.OBJECT_NAME || '" compile';
DBMS_OUTPUT.PUT_LINE('Errors compiling - ' || cur.owner ||'.'||cur.object_name);

Tuesday, May 26, 2009

Wednesday, May 20, 2009

Friday, May 1, 2009

tweaks around Oracle 11g on Hardy 64bit

I did an oracle 11g 64bit install on my 64bit core 2 quad using the resources available on internet and some past experience.

The tutorial here is great :

However, some things still show up after the initial setup is done. Here are bits of my experience -

1. Starting Enterprise manager after a reboot -
Try emctl status agent
The last line of the output should tell you whats happening. If its not running, start it using the following -
emctl start dbconsole
This thanks to

2. The ORACLE_SID variable must be declared in either /etc/profile (for a single database on the system) or on user's local profile.

Wednesday, April 22, 2009

Earning money using cloud Computing ??

I dont know much more about cloud computing, but it sure sounds like an interesting idea. The way it allows computing resources to be shared and connected through internet and all that. The benefit to the average developer is huge, getting computing power so cheap..

However, I think the reverse should also be true and could turn out to be pretty interesting. The way we (developers) use resources from the cloud for our computing purposes, we could also share our spare resources to the same cloud so that someone else might use them and allow us to earn some money in exchange. There would be some preconditions and requirements and what not, but essentially that should be a possibility...

I dont know for sure whether this concept already exists, but i would sure like to see that happening...

Friday, April 17, 2009

Blank Subject Reminder in Outlook...

1. Open your outlook.

2. Press Alt+F11. This opens the Visual Basic editor and then Press Ctrl+R which in turn open Project-Project 1 (left side).

3. On the Left Pane, one can see "Microsoft Outlook Objects" or "Project1", expand this. Now one can see the "ThisOutLookSession".

4. Double click on "ThisOutLookSession". It will open up a code pane.

5. Copy and Paste the attached code in the right pane. (Code Pane) and save it.

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)

Dim strSubject As String
strSubject = Item.Subject
If Len(Trim(strSubject)) = 0 Then
Prompt$ = "Subject is Empty. Are you sure you want to send the Mail?"
If MsgBox(Prompt$, vbYesNo + vbQuestion + vbMsgBoxSetForeground, "Check for Subject") = vbNo Then
Cancel = True
End If
End If
End Sub

For this you need to create a digital certificate. Go to to see how to do it. Name your certificate whatever you like e.g No_Subject.

Once that’s done go back to the code window, click Tools, Digital Signature, Choose - and choose that signature you just created. Hit ok, save this project, close it. Close Outlook completely too.

Start up Outlook, you get the security box; click always trust running this macro thing. And you’re done! Try sending a message without body, you’ll get an alert. Now even if you restart outlook it’ll still work.

Courtesy a colleague, Mukesh.

Wednesday, April 8, 2009

All about Dual Table : AskTom

A very very interesting article from AskTom on dual table, its purpose and usage and everything else about it...

Here's some excerpts from it, leaving the rest for reading...
"DUAL is owned by SYS.  SYS owns the data dictionary, 
therefore DUAL is part of the data dictionary. You are not to modify the data dictionary
via SQL ever -- wierd things can and will happen"


the optimizer understands dual is a magic, special 1 row table.  It stopped on the 
select * because there is to be one row in there. Its just the way it works.
Read the rest of article here...

Ask Tom "All about the DUAL table "
Blogged with the Flock Browser

Tuesday, April 7, 2009

Oracle SQL tuning - Burleson Consulting

Burleson Consulting website has come to rescue in many situations. For many questions their site provides great pointers and ideas.

Here's a series of papers/articles on Oracle SQL Tuning -

Oracle SQL tuning parameters

And, here's an article on tuning of individual SQL Statements -

Tuning Individual SQL Statements

Blogged with the Flock Browser

Beware of Question Authorities « H.Tonguç Yılmaz - Oracle Blog

Beware of Question Authorities
Following suggestions are from Mr.Lewis Blog ;
  1. If it’s not dated - don’t assume it’s true*
  2. If its date is more than about 18 months old - don’t assume it’s (still) true*
  3. If there’s no version number - don’t assume it’s true*
  4. If it’s not your exact version number - don’t assume it’s (still) true
  5. For ‘technical implementation’ details, if there’s no platform mentioned - don’t assume it’s true
  6. For ‘technical implementation’ details, if the platform’s not the same as yours - don’t assume it’s true
  7. If there’s no rational justification supplied, and no repeatable test case - don’t assume it’s true.
And even when all the details are perfect and there is a repeatable test case - and even after the repeatable test case produces the same results - ask yourself this question
  • Could there be a different explanation for the same set of results - and if so, how badly could this advice damage my system, and how hard would it be to test my alternative hypothesis ?
Once you’ve got through that lot - then you might be safe trying the advice on a development system.

Mr.Kyte also mentions this problem under the ‘Question Authority.’ terminology;

There are lots of ‘experts’ out there;
  • Make them prove everything
  • Statements that should raise your eyebrows:
  •     It is my opinion…
  •     I claim…
  •     I think…
  •     I feel…

  • Everything can (and should) be proven - TKPROF goes a long way hereStatspack is great‘Runstats’ is a tool I use as well (search asktom for runstats)
  • Things change, expect that

Remember a test becomes a proof when;
  • it has a specification- the results are reproducible
  • alternative explanations have been eliminated
  • it is published- it survives peer-group review
“The Burden of Proof” presentation by Jonathan LewisDon’t take any “guru’s” word, test it and make sure you are convinced of the results..
Beware of Question Authorities « H.Tonguç Yılmaz - Oracle Blog
Blogged with the Flock Browser

SQL - Difference Between Hash Join & Merge Join

Difference Between Hash Join & Merge Join

Merge Join :Oracle performs a join between two sets of row data using the mergejoin algorithm. The inputs are two separate sets of row data. Output isthe results of the join. Oracle reads rows from both inputs in analternating fashion and merges together matching rows in order togenerate output. The two inputs are sorted on join column.

Hash Join :Oracle performs a join between two sets of row data using hash joinalgorithm. Input and Output same as Merge Join. Oracle reads all rowsfrom the second input and builds a hash structure (like has table injava), before reading each row from the first input one at a time. Foreach row from the first input, the hash structure is probed and matchingrows generate output.
SQL - Difference Between Hash Join & Merge Join
Blogged with the Flock Browser

Thursday, April 2, 2009

An Extract from AskTom

Efficient SQL

This was probably the hardest part of the book to write - this chapter. That is not
because the material is all that complex, rather because I know what people want - and I
know what can be delivered. What people want: The 10 step process by which you can tune
any query. What can be delivered: Knowledge about how queries are processed, knowledge
you can use and apply day to day as you develop them.

Think about it for a moment. If there were a 10 step or even 1,000,000 step process by
which any query can be tuned (or even X% of queries for that matter), we would write a
program to do it. Oh don't get me wrong, there are many programs that actually try to do
this - Oracle Enterprise Manager with its tuning pack, SQL Navigator and others. What
they do is primarily recommend indexing schemes to tune a query, suggest materialized
views, offer to add hints to the query to try other access plans. They show you
different query plans for the same statement and allow you to pick one. They offer
"rules of thumb" (what I generally call ROT since the acronym and the word is maps to are
so appropriate for each other) SQL optimizations - which if they were universally
applicable - the optimizer would do it as a matter of fact. In fact, the cost based
optimizer does that already - it rewrites our queries all of the time. These tuning
tools use a very limited set of rules that sometimes can suggest that index or set of
indexes you really should have thought of during your design.

I'll close this idea out with this thought - if there were an N step process to tuning a
query, to writing efficient SQL - the optimizer would incorporate it all and we would not
be having a discussion about this topic at all. It is like the search for the holy grail
- maybe someday the software will be sophisticated enough to be perfect in this regards,
it will be able to take our SQL, understand the question being asked and process the
question - rather then syntax.

To me - writing efficient SQL requires a couple of things:

o Knowledge of the physical organization of what I'm asked to query against. That is
- the schema. Knowledge that the physical organization was actually designed in order to
help me answer my frequently asked questions (refer back to the chapter on designing an
efficient schema for advice in that arena)

o Knowledge of what the database is capable of doing. If I did not know about "skip
scan indexes" and what they did (we'll cover them below) - I might look at a schema and
say "ah hah, we are missing an index" when in fact we are not.

o Knowledge of all of the intricacies of SQL - from the lowly "WHERE" clause on up to
analytics and psuedo columns. Knowledge of what using a particular construct will do to
my runtime processing.

o And most importantly of all - a solid understanding of the goal, of what the
question is. Tuning a query or process is really hard (impossible I would say) - unless
you understand the question in the first place. I cannot tell you how many times I've
not been able to tune a query until I had the question in hand. Certainly you can derive
a question from a query - however, many times that derived question is much more
confining then the real question being asked. For example, many people use outer joins
in all queries - they are "afraid" of losing a row (perhaps they got "burned" in some
past experience and now use outer joins everywhere). If the objects are related in a one
to one mandatory fashion - we don't need an outer join at all. The question derived from
the query is much more confining then reality.

That last topic or point is so important, I'll close out this section with it. In this
chapter we'll cover the topics of what the database is capable of doing in general -
looking at many of the access paths and join operations available to us. We'll look at
what SQL is capable of doing - not by discussing the entire language, that in itself is a
book. Rather, we'll look at a couple of things that will whet you appetite - show you
how powerful this language can be, how much more than just "SELECT" "FROM" "WHERE" and
"ORDER BY" there is. Then we'll close up with a look at that most important topic - why
understanding the question is more important then having a query at hand to tune.

So, this section will not provide you with the N steps you need to follow in order to
tune a query or write the best queries in the world. For every rule of thumb out there
anyone has ever shown me regarding writing "efficient SQL", I've been able to come up
with a slew of common (not esoteric) counter cases to prove that rule of thumb is wrong
in as many cases as it is right. I've talked to people who swear "NOT IN" is fatal,
never use it - always use NOT EXISTS. Then I show them NOT IN running a query 10 times
faster then NOT EXISTS. I talk with people who feel NOT EXISTS is the worst construct
on the planet - you must use IN. Then I do the same - showing them how NOT EXISTS can
run many times faster then IN.

On SQL Tuning

Found some interesting stuff... (Collection from many sources over internet)

1. Basic Tipcs from -
2. a more light hearted approach -

On Oracle Joins

Some useful links on Oracle SQL optimization (more specifically on the way oracle joins tables)

1. Sachin Arora's explanation on Hash Joins/Nested Loops
2. Optimization of joins
3. Hash joins & Nested Loops on dbaForums
4. @OTN Forums on Nested Loops
5. Hash join Tips
6. Sizing PGA

Friday, March 13, 2009

Useful scripts for DBA perspective -

More stuff on finding free space available in a database -

A sql that we found useful is here - (from the above link itself) -

NVL (b.tablespace_name, NVL (a.tablespace_name, 'UNKOWN')) NAME ,
mbytes_alloc mbytes ,
mbytes_alloc - NVL (mbytes_free, 0) used ,
NVL (mbytes_free, 0) free ,
((mbytes_alloc - NVL (mbytes_free, 0)) / mbytes_alloc) * 100 pct_used,
100 - (((mbytes_alloc - NVL (mbytes_free, 0)) / mbytes_alloc) * 100) pct_free
SUM(BYTES) / 1024 / 1024 mbytes_free,
SUM(BYTES) / 1024 / 1024 mbytes_alloc,
a.tablespace_name(+) = b.tablespace_name


f.tablespace_name ,
SUM (ROUND((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) ) "total MB" ,
SUM (ROUND(NVL (p.bytes_used, 0) / 1024 / 1024, 2)) "Used MB" ,
SUM (ROUND ( ((f.bytes_free + f.bytes_used) - NVL (p.bytes_used, 0) ) / 1024 / 1024, 2 ) ) "Free MB" ,
(SUM (ROUND (NVL (p.bytes_used, 0) / 1024 / 1024, 2)) * 100) / (SUM (ROUND ((f.bytes_free + f.bytes_used) / 1024 / 1024, 2))),
100 - (SUM (ROUND (NVL (p.bytes_used, 0) / 1024 / 1024, 2)) * 100) / (SUM (ROUND ((f.bytes_free + f.bytes_used) / 1024 / 1024, 2)))
SYS.v_$temp_space_header f,
dba_temp_files d ,
SYS.v_$temp_extent_pool p
f.tablespace_name(+) = d.tablespace_name AND
f.file_id(+) = d.file_id AND
p.file_id(+) = d.file_id
5 DESC --&orderby

Wednesday, February 25, 2009

Compile all invalid packages in a schema : oracle

Came across this from sql developer, Looks like a very useful script, especially when its made available readymade

FOR cur IN
FROM all_objects
WHERE object_type in ('PACKAGE','PACKAGE BODY')
and owner = :OBJECT_OWNER AND status = 'INVALID' )
EXECUTE IMMEDIATE 'alter package "' || cur.owner || '"."' || cur.OBJECT_NAME || '" compile body';
EXECUTE IMMEDIATE 'alter ' || cur.OBJECT_TYPE || ' "' || cur.owner || '"."' || cur.OBJECT_NAME || '" compile';
end if;
end loop;

And then another one to check as to what all objects are invalid -

select object_type, count(*)
from user_objects
where status = 'INVALID'
GROUP BY object_type

Thursday, February 12, 2009

Oracle : count(1) vs count(*)

It seems we should prefer using count (*) over count(1)
read the article from asktom here...
there've been other discussions and debates on this as well.

Thursday, January 15, 2009

Free Adobe Flex Video Training

Just found about it...

not been through it yet, but it looks pretty good, combination of pdf ebooks, audio/video stuff and all that...

Saturday, January 3, 2009

More Free ebooks on Oracle - Unix - Linux

Find free to download ebooks on this site -

Changing System name in Ubuntu

When I installed Ubuntu for the first time, I named my system to reflect the os name (gutsy) and the architecture (64 bit). However, whenever there were system upgrades, the name got irrelevant. For example, when I upgraded to Hardy, the name still remained stuck to the last version of OS.

Therefore, I now thought of renaming my system to separate it from the os versions. This is so very simple to achieve in Ubuntu -

just edit the file /etc/hostname (owned by root, therefore use sudo). Put in the new name, and restart the system.

However, the consequences of this name change will have to be reflected across the network (if you have one). Hostname files on all other systems or dns entries will have to be updated to reflect the new system name.