Jayesh Pande

Listed below are some SQL queries which I find particularly useful for performance tuning. These are based on the Active Session History V$ View to get a current perspective of performance and the DBA_HIST_* AWR history tables for obtaining performance data pertaining to a period of time in the past. I would like to add that these queries have been customised by me based on SQL scripts obtained from colleagues and peers. So if I am infringing any copyright material let me know and I shall remove the same. Also If anyone has any similar useful scripts to contribute for use by the community do send it to me and I shall include the same on this page Top Recent Wait Events col EVENT format a60 select * from ( select active_session_history.event, sum(active_session_history.wait_time + active_session_history.time_waited) ttl_wait_time from v$active_session_history active_session_history where active_session_history.event is not null group by active_session_history.event order by 2 de...

About Cloning Cloning is the process of copying an existing installation to a different location while preserving its configuration. The source and the destination path (host to be cloned) need not be the same. During cloning, Oracle Universal Installer replays all actions that have been executed to install the home. It is very similar to installation except that Oracle Universal Installer will run the actions in a special mode called the clone mode. Some situations in which cloning is useful are: Creating an installation that is a copy of a production, test, or development installation. Cloning enables you to create a new installation with all patches applied to it in a single step. This is in contrast with going through the installation process by performing separate steps to install, configure, and patch the installation. Rapidly deploying an instance and the applications it hosts. Preparing an Oracle home and deploying it to many hosts. The cloned installation behaves th...

I’ll explain how easy is to install Oracle Software using command line with no need to get a VNC session or a GNOME/KDE environment. Most of people use the OUI (Oracle Universal Installer) graphic interface to install oracle database software, but sometimes we need a small footprint server installation without installing all the garbage that usually everyone install to run a simple Oracle database. If you have installed a slimmed down OS installation(or Centos or RHEL) either by only choosing the base components or by using a VM template (OVM or EC2), you can install Oracle software in 3 simple steps. 1. Add Oracle public yum repository. [root@localhost ~]$ cd /etc/yum.repos.d [root@localhost ~]$ wget http://public-yum.oracle.com/public-yum-el5.repo # edit the file public-yum-el5.repo and enable both the [el5_u5_base] and the [ol5_u5_base] repositories in the yum configuration file by changing enable=0 to enable=1 in those sections 2. Install Oracle validated ...

In order to support the rollback facility in oracle database, oracle takes the help of rollback segments. Rollback segments basically holds the  before image or  undo  data or uncommitted data  of a particular transaction, once the transaction is over the blocks in that rollback segment can help any other transaction. Rollback segment is just like any other table segments and index segments, which consist of extents, also demand space and they get created in a tablespace. In order to perform any DML operation against a table which is in a non system tablespace ('emp' in 'user' tablespace), oracle requires a rollback segment from a non system tablespace. When a transaction is going on a segment which is in non system tablespace, then Oracle needs a rollback segment which is also in non system tablespace. This is the reason we create a separate tablespace just for the rollback segment. Why rollback segments? ...