What would be the first thing you would do if an end user complains that performance is poor?

I always used to come up with this question on forums and well as from the Application support, and a time with my first interview in India.
As for the answer to the question, one should touch upon all these points:
- Check alert log, for any errors and UN even entries
- Check for archive log destination/file system full
- Check for database locks # Click here for a notes on locks
- Check server performance (CPU/IO)
- Check network performance
- Check if statistics is up to date
- If new SQL, then tune SQL (maybe take trace)
- Check if any parameters have been modified recently
- Check if any new updates as been done from the Application team.

And few more from Oracle Online Documentation as well
1) Identifying and Gathering Data on Resource-Intensive SQL # Oracle Doc
2) Oracle9i Database Performance Tuning Guide and Reference # Oracle Doc
I recommend reading the documents from Oracle, which give clear understanding of all the above. Please add lines if you I miss something which is useful.

Some SQL Statements - For Day to Day tasks

1) To grep & Drop user objects before Import
select 'drop 'object_type' 'object_namedecode(object_type,'TABLE','
cascade constraints;',';')
from user_objects
where object_type!='INDEX'/
********************************************************************
PCT_FREE, PCT_USED
SQL> select TABLE_NAME, PCT_FREE, PCT_USED, EMPTY_BLOCKS, CHAIN_CNT from dba_tables
2 where CHAIN_CNT > 0;
TABLE_NAME PCT_FREE PCT_USED EMPTY_BLOCKS CHAIN_CNT
------------------------------ ---------- ---------- ------------ ----------
BBN_MAS_LIQ_DET_TSY_TMP 10 40 1977 392
ORD_LEDGER 10 40 1982 6609
************************************************************************
SQL> select object_name,object_type from user_objects where status='INVALID';
******************************************************************************

Oracle Applications DBA (Apps DBA)

Hmm as long from now being a core DBA from past 3 years I want move to Apps. Apps DBA is what Applications DBA is said to be. I want to assimilate my self for Application expertise and as well as with Oracle 10 G Rel 2.

In my searches I found this reliable site for Applications http://www.appsdba.com/ hope this will be very useful for Apps DBA.

I always found of increasing my errands and promoting myself with the market. But what Apps DBA does? Is this the question I always asks myself and hope this is the most common question for a core DBA who wants to upgrade for Apps? Yes! Hope so! :- )
I want to solve this in my blog – hope it helps to know the responsibilities of being an Apps DBA.

What duties, if any, are different for an Apps DBA versus those of an Oracle 8/9i DBA?

Oracle APPS DBA's duties are almost same as of Oracle DBA but your work will be more specific to the Architecture (or I should say Environment) and OFA of Oracle Application. Apart from that there are few more things you should know like applying patches or adadmin utility and some basic functionality of SYSADMIN responsibility of APPS.

An APPs DBA knows all that a regular DBA knows plus the following:

Application fundamentals such as concepts and architecture

Application Installation and configuration

Patching and Upgrades

Patch Management and Version Control

Customization Updates

Cloning requirements.

This can be frequent in an Apps environment- Concurrent Managers - Forms/Report Servers

Web Servers

all the "ad" utilities

all the database restrictions for the applications.

That is so that the installation is still supportable and conforms to all vendor (Oracle) requirements.

All special application related regular and maintenance functions. Such as month end and period end functions.

Understand any backup and recovery constraints. Such as for table imports (NOT) and partial recoveries.

Special requirements and restrictions for tuning, example optimizer and index requirements.

Printer Configuration

User Access AdministrationThis could be a matter for further discussion as it could be argued that all DBA's should be aware of the above or equivalent for any database application (Oracle or otherwise).

These are two core answers I found for being a Apps DBA.

Resolving a Database Failure

Resolving a Database Failure

Phase I: Diagnose the problem
Phase II: Restore appropriate files
Phase III: Recover the database
Phase IV: Back up the database

Phase I: Diagnose the problem

The first phase is to research the nature of the failure. Use V$ views, data dictionary views, trace and log files, basic operating system commands and Oracle Enterprise Manager to diagnose the problem.

Determine if the database instance is available and the database is open.

Attempt to start the instance.

Shutdown the instance if a problem occurs while starting it or when opening the database.

Check the trace files and alert log files.

Determine the appropriate recovery method:

- Closed database recovery
- Open database, offline tablespace recovery
- Open database, offline tablespace, individual datafile recovery
- Cancel-based recovery
- Time-based recovery
- Change-based recovery


Phase II: Restore Appropriate Files

Before you perform a recovery scenario, determine what files to restore and what state the instance and database must be in to perform the recovery. Remember that the objective is to minimize down time and loss of data, so do not restore files if it is not necessary.

Phase III: Recover the Database

Once the appropriate files are restored, initiate your recovery operation. After completing the recovery, note any proactive measures that can be taken to prevent that type of failure in the future.

Phase IV: Back up the Database

Not all recovery operations require a database backup when they are complete. However, determine whether your database needs to backed up and, if so, perform another backup.