Oracle11g Upgrade

Oracle11g Workshop

Notes from 11g Oracle workshop

Find Sessions with the Highest CPU Consumption

Monday, January 10, 2011

The following queries will allow you to find the sessions currently logged into the database that have accumulated the most time on CPU or for certain wait events. Use them to identify potential sessions to trace using 10046.

These queries are filtering the sessions based on logon times less than 4 hours and the last call occurring within 30 minutes. This is to find more currently relevant sessions instead of long running ones that accumulate a lot of time but aren't having a performance problem. You may need to adjust these values to suit your environment.

Find Waits in the Database causing Performance issue

Monday, January 10, 2011

The following queries will allow you to find the sessions currently logged into the database that have accumulated the most time on CPU or for certain wait events. Use them to identify potential sessions to trace using 10046.

Run following sqls in the order

    @sess_waits   <-- shows sid on clock

    @sql_text        <-- show the sql for sid

    @sqlplan         <-- show sqlplan for sql_id

    @wait_sess      <-- list all the waits in db

    @wait             <-- waits for sid

Max datafile size in oracle

Max datafile size for SMALL FILE NORMAL TABLESPACE would be:

Database Block Size Maximum Datafile File Size
2k 4194303 * 2k = 8 GB
4k 4194303 * 4k = 16 GB
8k 4194303 * 8k = 32 GB
16k 4194303 * 16k = 64 GB
32k 4194303 * 32k = 128 GB

CPU utilization on linux

To find out processes, which have exceeded CPU utilization threshold on the server.

In the example,

a) CPU utilization of java processes are checked. You may replace it for any other process.

b) Also, input the threshold value

Query on Dynamic View is slow ( v$ views)

 QUERY PERFORMANCE SLOW FOR V$ VIEWS


PROBLEM:
--------
Query performance containing v$ views (v$sql, v$session) are slow as compared
to same query using RULE hints.

ADR_BASE_LISTENER for listener log adn trace files

ADR_BASE_LISTENER directory

To Change the adr directory for listener from default

Make Changes in listener.ora file

 e.g. ADR_BASE_LISTENER = /oralog/oracle

example:

/oralog/oracle/oradiag_oracle/diag/clients/user_oracle/

Hope this helps. Regards Rupam