High Version Count In AWR

One day while reading AWR reports, i hav seen a section named Sql by high version count.
This was something new for me and i had cehcked the defination on other sites and found that this is mainly caused due to bind mismatch. Although the application is using Bind variables, but still sqls are not gettinsg shared due to this bind mismatch.
Anyhow we came across a new event that can be set to tackle this problem -
'10503 trace name context forever, level '

spooling sql output to excel

sometime it is required to spool sql output to excel file, i learned a new way to do this and it is very simple and handy. just posing an example below, this was told to me by one on my friends who is a dba and i have also seen such examples on otn website as well.
you just need to connect to sqlplus and use following :-
SQL>set markup html on;
SQL>spool 'c:\test.xls'
SQL>select empno,deptno from scott.emp;

SQL> SPOOL OFF;


SQL> SET MARKUP HTML OFF;


SQL>

Its an easy way...

More About Real Application Clusters

This is in continuation with my previous post , one thing i forgot to mention is that in RAC environment database resides on SAN, hence in the event of failure on any node , database is still available for other nodes to access. Here there are three types of network connection available, first is interconnect (This is the connection that is used as heartbeat and to transfer data blocks), second is VIP(Virtual Ip Address) that is accessed by users, and other is a physical Ip. Interconnects are heartbeats that play an important role in manging the cluster,if primary node is unable to communicate with secondary node through interconnects then the second node is evicted from the cluster or it reboots. Sometime later i will surely put some screenshots of how to install RAC on Vmware, for those who are new to this it is advisable to install on Vmware, and have a feel of the cluster environment.

So what is Oracle Real Application Clusters??

From some time i was thinking of writing about some basic stuff regarding RAC.
RAC stands for Real Application Cluster , which in plain term means a Database working in Cluster mode,now hope most of you are familier with the term CLUSTER, otherwie just do a google, am sure you will find good pages on this. Since this database works in cluster minimum of 2 nodes are required to operate. So what benefits does this provides!! First of all RAC is an active-active cluster, this means both the nodes of Oracle cluster can access the database simultaneously.Hence in the event of failure if one node goes down, the database can still be accessed from the second node. So you can give 99% uptime..:-)
Second benefit is that it also provides for feature such as load balancing, in this case if we have 4000 users accessing a database, they will be evenly distributed on both the nodes, i.e 2000 users on each node. This helps in utilizing system resources of both the nodes and also easing of CPU utilization. In some other post i will surely write about some really good scenario that i have come across in the environments i have managed..and will also try to explain more about RAC..

events in oracle DB

When i was very new to database, i always used to wonder why are there so many events in database and what is the use of all these..:-)..but now i really understand why are they there. In fact most of the times , when you feel like checking health of the database, just check out the event at that point of time and also at peak load.
The view v$session is a great place to start, the column event gives you event being faced by a particular session. This has helped me a lot..:-)

How to check for blocking session in oracle?

It happens many a times that a user calls you and tells you that he has executed a query a long time back which is a very small update or delete statement and the Sql screen is in a hung state. In this case it is proably the user session is being blocked by some other session. So the easiest way to track this is using the v$session view in oracle 10g. This view has a field blocking_session, which will tell you what sid is blocking your session.
e.g
suppose user joe calls and tells you the same problem, his userid is XY123
-
sql>select sid,serial#,blocking_session grom v$session where username='XY123';

output

sql> 523,1234,321

so here we can see session 321 is blocking session 523 of joe.

I have always found this useful as i come across this situation many a times.