Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Wednesday, September 28, 2011

Copying Files Over Remote Desktop

Start up a remote desktop dialog and set the local resources as desired using options button. After this you will be able to use the clipboard of local system on remote machine.

How To Use PROFILES To Limit User Resources

You have a group of users that do not always disconnect from the database when they are done, or, they leave their connection idle for long periods of time. You want to eliminate these connections to make more connections available to other users.

How to Audit Connect AS SYSDBA Using Oracle Server

Windows Systems
---------------
On Windows Systems, you can monitor audited connects to Oracle as an administrative
user (former connect INTERNAL as of 8.1.7 connections ' as sysdba ') in the
event viewer. 

Tuesday, September 27, 2011

Application Tuning using Explain

The EXPLAIN facility can be used quickly and easily to
determine how the data is accessed (known as the access path) for
any given SQL statement, namely queries.  The ability to modify
this access path can yield an incredible performance benefit.

SCRIPT TO CHECK FOR FOREIGN KEY LOCKING ISSUES

Description

This script checks the current users Foreign Keys to make sure of the
following:

1) All the FK columns have indexes to prevent a possible locking

How to Collect Diagnostics for Database Hanging Issues


If you are encountering a database hanging situation, you need to take system
state dumps/hanganalyze so that Oracle Support can begin to diagnose the cause of the problem.
Whenever you take such dumps for a hang it is important to take at least 3 of
them a few minutes apart, on all instances of your database.

How To Connect Using A Sqlplus Preliminary Connection

There is an another  way to connect to a database through Sqlplus when all other connection methods are hanging.  Using a Sqlplus preliminary connection you will be able to connect to the database since no session is actually created,

Using DBMS_MONITOR

Using DBMS_MONITOR, Tracing has been enabled for diagnose and workload management based on, a specified client identifier or a hierarchical combination of service name, module name and action name. Also we have the facility to trace on session level.

Using TKPROF

The TKPROF facility accepts as input an SQL trace file and produces a formatted output file.  Note that TKPROF can be run on individual or
appended trace files to produce the formatted output file.

How to Enable SQL_TRACE for Another Session or in MTS Using Oradebug


The ORADEBUG utility can enable/disable setting the SQL tracing for another 
user's session or an MTS session.  To enable tracing for another session, the 
Oracle process identifier (PID) or the Operating System processes identifier 
(SPID) must be identified from v$process.  This is an effective way of capturing 
a SQL trace from a process which is already running.  The output can be used to 
analyze SQL related performance issues.

How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues

Event 10046 is the standard method of gathering extended sql_trace information for Oracle sessions.
This is a special EVENT code. It can be used to signal Oracle to perform SQL_TRACE type 
actions. The 10046 trace is the equivalent of setting SQL_TRACE=TRUE.The advantage of using 
the event is that extra details may be output to the trace file depending on the level
specified with the event. 

Monday, September 26, 2011

Investigating a Database Performance Issue

To investigate a slow performance problem, begin by deciding what diagnostics will be gathered. To do this, consider the following questions and take the appropriate action:-
Is the performance problem constant or does it occur at certain times of the day ?

Optimizing Joins

The first thing to remember about optimising the performance of joins is that the performance can decrease considerably as a 3rd or 4th or 5th table is added to the query. The more tables that are added, the more important it is to ensure that the query is properly tuned and tested.

Oracle database Performance Tuning

The Oracle RDBMS is highly tunable and allows the database to be monitored and adjusted to increase its performance. ne should do performance tuning for the following reasons:
  • The speed of computing might be wasting valuable human time (users waiting for response);

Monitoring Index Usage

Monitoring Index Usage

Oracle Database provides a means of monitoring indexes to determine whether they are being used. If an index is not being used, then it can be dropped, eliminating unnecessary statement overhead.

Sunday, September 18, 2011

Desktop Heap Overview

Desktop heap is probably not something that you spend a lot of time thinking about, which is a good thing.  However, from time to time you may run into an issue that is caused by desktop heap exhaustion, and then it helps to know about this resource.  Let me state up front that things have changed significantly in Vista around kernel address space, and much of what I’m talking about today does not apply to Vista.

Tuesday, September 13, 2011

Schedule Killing sessions dynamically

You can kill session dynamically as below in a single instance but for the RAC you may face issues , so approach below can be used.