About Me.

My self Adinarayana working as Implementation Application DBA with advanced technologies like RAC/PCP,OID/SSO,DMZ,Exadata and Fusion Middleware i.e Demantra,Application Server,SOA,FMW,BPEL and UPK. Created this blog to share the useful information related to DBA and Application DBA Your comments and suggestions are most welcome. Disclaimer: Please note all the views and opinions expressed in this site are my own. It's not recommend to use the fixes/suggestions provided in this site directly in production instance, please test them before implementing.
Showing posts with label Oracle Performance Tuning. Show all posts
Showing posts with label Oracle Performance Tuning. Show all posts

Wednesday, December 9, 2015

Locks and Blocking sessions

1.DB Locks:
  ---------
set lines 200
set pages 200
spool DB_Locks.lst
select
blocking_session,
sid,
serial#,
wait_class,
seconds_in_wait
from
v$session
where
blocking_session is not NULL
order by
blocking_session;
spool off

SQL> /

BLOCKING_SESSION        SID    SERIAL# WAIT_CLASS                                                       SECONDS_IN_WAIT
---------------- ---------- ---------- ---------------------------------------------------------------- ---------------
             543       3604      44225 Application                                                                13168
            1637       5027      34229 Application                                                                 4629
            2347       1637      34589 Concurrency                                                                  790
            2347       1772      30581 Concurrency                                                                  712
            2347       2133      30613 Concurrency                                                                  113
            2347        427      51691 Concurrency                                                                  790
            2347       6552       9531 Concurrency                                                                   73
            2347        283      32075 Concurrency                                                                  791
            2347       6336      43573 Concurrency                                                                  791
            2347       6226       6863 Concurrency                                                                  781
            2347       2598       9991 Concurrency                                                                  553
            2347       2884       6587 Concurrency                                                                  790
            2347       2908      19783 Concurrency                                                                  790
            2347       3095      15317 Concurrency                                                                  791
            2347       3301      11573 Concurrency                                                                  556
            2347       3839      28959 Concurrency                                                                  790
            2347       3958       5749 Concurrency                                                                  790
            2347       4044      13291 Concurrency                                                                  766
            2347       4279       5453 Concurrency                                                                  789
            2347       4370      36515 Concurrency                                                                  790
            2347       4813         87 Concurrency                                                                  789
            2347       4962      15327 Concurrency                                                                  556
            2347       5202      63227 Concurrency                                                                  790
            2347       5375      16397 Concurrency                                                                  790
            2347       5994      21109 Concurrency                                                                  790
            3604        111      19897 Application                                                                13165
            4692       2347      25165 Concurrency                                                                  791
            5202        543      54389 Application                                                                18975

28 rows selected.

2.SQL> select sql_id from v$session where sid=543;

SQL_ID
-------------
7xsrcn3c3ssfy

3.
SQL> select sql_text from v$sql where sql_id='7xsrcn3c3ssfy';

SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
UPDATE OE_ORDER_HEADERS SET PRICE_REQUEST_CODE = :B2 WHERE HEADER_ID = :B1
UPDATE OE_ORDER_HEADERS SET PRICE_REQUEST_CODE = :B2 WHERE HEADER_ID = :B1
UPDATE OE_ORDER_HEADERS SET PRICE_REQUEST_CODE = :B2 WHERE HEADER_ID = :B1



scenario 1
==========


first query
===========

Enter the request which is running long time,
Example 1:request Id=11263341


SQL> SELECT request_id,oracle_process_id,
DECODE(phase_code,'R','Running','C','Completed','P','Inactive',phase_code) Phase,
DECODE(status_code,'R','Normal','C','Normal','E','Error','Q','No Manager','T',
'Terminating','X','Terminated','G','Warning','Canceled','D',status_code) Status
FROM apps.fnd_concurrent_requests
where request_id=11263341;

REQUEST_ID ORACLE_PROCESS_ID              PHASE     STATUS
---------- ------------------------------ --------- -----------
  11263341 7694                           Running   Normal

O/p Gives u the Oracle Process ID:7694


Second query
=============

Enter the request which is running long time,
Example 1:request Id=11263341

Also gives u the Oracle Process ID...

SQL> select oracle_process_id ,
decode(status_code,'R','Running','D','Canceled','E','Error','X','Terminated','G',  'Warning','T','Terminating')"Status_code",
phase_code,to_char(actual_start_date,'DD-MON-YYYY=>hh24:mi:ss') "Login Time"
from apps.fnd_concurrent_requests where request_id='&Enter_conn_req_id';
Enter value for enter_conn_req_id: 11263341
old   4: from apps.fnd_concurrent_requests where request_id='&Enter_conn_req_id'
new   4: from apps.fnd_concurrent_requests where request_id='11263341'

ORACLE_PROCESS_ID              Status_code P Login Time
------------------------------ ----------- - ---------------------------
7694                           Running     R 07-OCT-2010=>09:36:09

select oracle_process_id ,request_id,
decode(status_code,'R','Running','D','Canceled','E','Error','X','Terminated','G',  'Warning','T','Terminating')"Status_code",
phase_code,to_char(actual_start_date,'DD-MON-YYYY=>hh24:mi:ss') "Login Time"
from apps.fnd_concurrent_requests where oracle_process_id='&a';

Third Query
===========

With that oracle Process ID We can find the SID and Serial# and waht it is doing ..
Oralce Procedd ID = 7694

select s.sid,s.serial#,module,s.status from v$session s,v$process p where s.paddr=p.addr and p.spid=&oracle_process_id;
Enter value for oracle_process_id: 7694
old   3: and p.spid=&oracle_process_id
new   3: and p.spid=7694

       SID    SERIAL# MODULE                                           STATUS
---------- ---------- ------------------------------------------------ --------
      3342       5626 ALTMFGINVFA                                      ACTIVE

SQL>


Fourth Query
=============

To find the Text we can find out what it is running..
USING SID=3342


SELECT sql_text FROM V$sql WHERE address IN ( SELECT sql_address FROM v$session WHERE sid =3342);

SQL> SELECT sql_text FROM V$sql WHERE address IN ( SELECT sql_address FROM v$session WHERE sid =3342);


SQL_TEXT
--------------------------------------------------------------------------------
SELECT NVL(CIC.ITEM_COST, NVL(CIC_DEF.ITEM_COST, 0)), NVL(CIC.BASED_ON_ROLLUP_FL
AG,NVL(CIC_DEF.BASED_ON_ROLLUP_FLAG, 2)), NVL(CIC.SHRINKAGE_RATE, NVL(CIC_DEF.SH
RINKAGE_RATE, 0)), (BET1.EXTENDED_QUANTITY * NVL(BET.COMPONENT_QUANTITY, 0) * (N
VL(BET.PLANNING_FACTOR, 100) / 100) ) / (DECODE(:B7 , 2, 1, DECODE(NVL(BET.COMPO
NENT_YIELD_FACTOR, 0), 0, 1, NVL(BET.COMPONENT_YIELD_FACTOR, 0) ) ) * (1 - NVL(B
ET1.SHRINKAGE_RATE, 0)) ), DECODE(BET1.EXTEND_COST_FLAG, 2, 2, DECODE(BET1.INVEN
TORY_ASSET_FLAG, 2, 2, DECODE(BET1.BASED_ON_ROLLUP_FLAG, 2, 2, DECODE(BET.INCLUD
E_IN_ROLLUP_FLAG, 1, 1, 2)))), NVL(CIC.COST_TYPE_ID, NVL(CIC_DEF.COST_TYPE_ID, B
ET1.ACTUAL_COST_TYPE_ID)), NVL(CIC.INVENTORY_ASSET_FLAG, NVL(CIC_DEF.INVENTORY_A
SSET_FLAG, 2)), BET.TOP_BILL_SEQUENCE_ID, BET.ROWID FROM CST_ITEM_COSTS CIC, CST
_ITEM_COSTS CIC_DEF, BOM_EXPLOSION_TEMP BET_MUST_HAVE, BOM_EXPLOSION_TEMP BET1,

SQL_TEXT
--------------------------------------------------------------------------------
BOM_EXPLOSION_TEMP BET WHERE BET1.GROUP_ID = :B2 AND BET1.TOP_BILL_SEQUENCE_ID =
 BET.TOP_BILL_SEQUENCE_ID AND BET1.SORT_




SQL> select process,sid,blocking_session,BLOCKING_INSTANCE from gv$session where blocking_session is not null;

PROCESS                         SID BLOCKING_SESSION BLOCKING_INSTANCE
------------------------ ---------- ---------------- -----------------
115740                         1267             5265                 2
20969                          5265             7261                 2
21000                          6003             5265                 2
20993                          6754             5265                 2
93890                          7275             7261                 2
20131                           772             7261                 2

SQL> select sid,serial#,p2,program,module from gv$session where sid=5265;

Sunday, August 4, 2013

Oracle wait events

db file sequential read  => tune indexing, tune SQL (to do less I/O), tune disks, increase buffer cache. This event is indicative of disk contention on index reads. Make sure all objects are analyzed. Redistribute I/O across disks. The wait that comes from the physical side of the database. It related to memory starvation and non selective index use. Sequential read is an index read followed by table read because it is doing index lookups which tells exactly which block to go to.
db file scattered read => disk contention on full table scans. Add indexes, tune SQL, tune disks, refresh statistics, and create materialized view. caused due to full table scans may be because of insufficient indexes or unavailability of updated statistics.
·                     Oracle monitoring tools

·                     Make money blogging

·                     Twitter marketing strategy

·                     Email

·                     Blog
db file parallel read  => tune SQL, tune indexing, tune disk I/O, increase buffer cache. if you are doing a lot of partition activity then expect to see that wait even. it could be a table or index partition.
db file parallel write  => if you are doing a lot of partition activity then expect to see that wait even. it could be a table or index partition.
db file single write  => if you see this event than probably you have a lot of data files in your database.

control file sequential read
control file parallel write

log file sync    => committing too often, archive log generation is more. Tune applications to commit less, tune disks where redo logs exist, try using nologging/unrecoverable options, log buffer could be too large.
log file switch completion => May need more log files per group.
log file parallel write  => Deals with flushing out the redo log buffer to disk. Disks may be too slow or have an I/O bottleneck. Look for log file contention.
log buffer space   => Increase LOG_BUFFER parameter or move log files to faster disks. Tune application, use NOLOGGING, and look for poor behavior that updates an entire row when only a few columns change.
log file switch (checkpoint incomplete) => May indicate excessive db files or slow IO subsystem.
log file switch (archiving needed)    => Indicates archive files are written too slowly.
redo buffer allocation retries  => shows the number of times a user process waited for space in the redo log buffer.
redo log space wait time  => shows cumulative time (in 10s of milliseconds) waited by all processes waiting for space in the log buffer.

buffer busy waits/ read by other session  => Increase DB_CACHE_SIZE. Tune SQL, tune indexing, we often see this event along with full table scans, if the SQL is inserting data, consider increasing FREELISTS and/or INITRANS, if the waits are on segment header blocks, consider increasing extent sizes.
free buffer waits  => insufficient buffers, process holding buffers too long or i/o subsystem is over loaded. Also check you db writes may be getting clogged up.
cache buffers lru chain  => Freelist issues, hot blocks.
no free buffers   => Insufficient buffers, dbwr contention.

latch free
latch: session allocation
latch: in memory undo latch  => If excessive could be bug, check for your version, may have to turn off in memory undo.
latch: cache buffer chains  => check hot objects.
latch: cache buffer handles  => Freelist issues, hot blocks.

direct path write
=> You wont see them unless you are doing some appends or data loads.
direct path reads => could happen if you are doing a lot of parallel query activity.
direct path read temp or direct path write temp => this wait event shows Temp file activity (sort,hashes,temp tables, bitmap) check pga parameter or sort area or hash area parameters. You might want to increase them.

library cache load lock

library cache pin => if many sessions are waiting, tune shared pool, if few sessions are waiting, lock is session specific.
library cache lock  => need to find the session holding the lock, look for DML manipulating an object being accessed, if the session is trying to recompile PL/SQL, look for other sessions executing the code.

undo segment extension
  => If excessive, tune undo.
wait for a undo record   => Usually only during recovery of large transactions, look at turning off parallel undo recovery.

enque wait events   => Look at V$ENQUEUE_STAT


Wednesday, July 31, 2013

Locked Sessions

What's blocking my lock?

If you've ever gotten a phone call from an annoyed user whose transaction just won't go through, or from a developer who can't understand why her application sessions are blocking each other, you know how useful it can be to identify not just whose lock is doing the blocking, but what object is locked. Even better, you can identify the exact row that a session is waiting to lock.

Create a blocking lock

To begin, create a situation where one user is actively blocking another. Open two sessions. Issue the following commands in Session 1 to build the test table:
SQL> create table tstlock (foo varchar2(1), bar varchar2(1));
 
Table created.
 
SQL> insert into tstlock values (1,'a'); 
 
1 row created.
 
SQL> insert into tstlock values (2, 'b');
 
1 row created.
 
SQL> select * from tstlock ;
 
FOO BAR
--- ---
1   a
2   b
 
2 rows selected.
 
SQL> commit ;
 
Commit complete.
Now grab a lock on the whole table, still in Session 1:
SQL> select * from tstlock for update ;
And in Session 2, try to update a row:
SQL> update tstlock set bar=
  2  'a' where bar='a' ;
This statement will hang, blocked by the lock that Session 1 is holding on the entire table.

Identify the blocking session

Oracle provides a view, DBA_BLOCKERS, which lists the SIDs of all blocking sessions. But this view is often, in my experience, a good bit slower than simply querying V$LOCK, and it doesn't offer any information beyond the SIDs of any sessions that are blocking other sessions. The V$LOCK view is faster to query, makes it easy to identify the blocking session, and has a lot more information.
SQL> select * from v$lock ;
 
ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
AF9E2C4C AF9E2C60        479 TX     131078      16739          0          6        685          0
ADDF7EC8 ADDF7EE0        422 TM      88519          0          3          0        697          0
ADDF7F74 ADDF7F8C        479 TM      88519          0          3          0        685          0
ADEBEA20 ADEBEB3C        422 TX     131078      16739          6          0        697          1
....     ....            ... ...      ....       ....       ....       ....        ....      ....
Note the BLOCK column. If a session holds a lock that's blocking another session, BLOCK=1. Further, you can tell which session is being blocked by comparing the values in ID1 and ID2. The blocked session will have the same values in ID1 and ID2 as the blocking session, and, since it is requesting a lock it's unable to get, it will have REQUEST > 0.
In the query above, we can see that SID 422 is blocking SID 479. SID 422 corresponds to Session 1 in our example, and SID 479 is our blocked Session 2.
To avoid having to stare at the table and cross-compare ID1's and ID2's, put this in a query:
SQL> select l1.sid, ' IS BLOCKING ', l2.sid
  2  from v$lock l1, v$lock l2
  3  where l1.block =1 and l2.request > 0
  4  and l1.id1=l2.id1
  5  and l1.id2=l2.id2
SQL> /
 
       SID 'ISBLOCKING'         SID
---------- ------------- ----------
       422  IS BLOCKING         479
 
1 row selected.
Even better, if we throw a little v$session into the mix, the results are highly readable:
SQL> select s1.username || '@' || s1.machine
  2  || ' ( SID=' || s1.sid || ' )  is blocking '
  3  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
  4  from v$lock l1, v$session s1, v$lock l2, v$session s2
  5  where s1.sid=l1.sid and s2.sid=l2.sid
  6  and l1.BLOCK=1 and l2.request > 0
  7  and l1.id1 = l2.id1
  8  and l2.id2 = l2.id2 ;
 
 
BLOCKING_STATUS
----------------------------------------------------------------------------------------------------
BULKLOAD@yttrium ( SID=422 )  is blocking BULKLOAD@yttrium ( SID=479 )
 
1 row selected.
There's still more information in the v$lock table, but in order to read that information, we need to understand a bit more about lock types and the cryptically-named ID1 and ID2 columns.

Lock type and the ID1 / ID2 columns

In this case, we already know that the blocking lock is an exclusive DML lock, since we're the ones who issued the locking statement. But most of the time, you won't be so lucky. Fortunately, you can read this information from the v$lock table with little effort.
The first place to look is the TYPE column. There are dozens of lock types, but the vast majority are system types. System locks are normally only held for a very brief amount of time, and it's not generally helpful to try to tune your library cache, undo logs, etc. by looking in v$lock! (See the V$LOCK chapter in the Oracle Database Reference for a list of system lock types.)
There are only three types of user locks, TX, TM and UL. UL is a user-defined lock -- a lock defined with the DBMS_LOCK package. The TX lock is a row transaction lock; it's acquired once for every transaction that changes data, no matter how many objects you change in that transaction. The ID1 and ID2 columns point to the rollback segment and transaction table entries for that transaction.
The TM lock is a DML lock. It's acquired once for each object that's being changed. The ID1 column identifies the object being modified.

Lock Modes

You can see more information on TM and TX locks just by looking at the lock modes. The LMODE and REQUEST columns both use the same numbering for lock modes, in order of increasing exclusivity: from 0 for no lock, to 6 for exclusive lock. A session must obtain an exclusive TX lock in order to change data; LMODE will be 6. If it can't obtain an exclusive lock because some of the rows it wants to change are locked by another session, then it will request a TX in exclusive mode; LMODE will be 0 since it does not have the lock, and REQUEST will be 6. You can see this interaction in the rows we selected earlier from v$lock:
ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
AF9E2C4C AF9E2C60        479 TX     131078      16739          0          6        685          0
ADEBEA20 ADEBEB3C        422 TX     131078      16739          6          0        697          1
Note that ID1 and ID2 in Session 2, which is requesting the TX lock (LMODE=0, REQUEST=6), point back to the rollback and transaction entries for Session 1. That's what lets us determine the blocking session for Session 2.
You may also see TX locks in mode 4, Shared mode. If a block containing rows to be changed doesn't have any interested transaction list (ITL) entries left, then the session acquires a TX lock in mode 4 while waiting for an ITL entry. If you see contention for TX-4 locks on an object, you probably need to increase INITRANS for the object.
TM locks are generally requested and acquired in modes 3, aka Shared-Row Exclusive, and 6. DDL requires a TM Exclusive lock. (Note that CREATE TABLE doesn't require a TM lock -- it doesn't need to lock any objects, because the object in question doesn't exist yet!) DML requires a Shared-Row Exclusive lock. So, in the rows we selected earlier from v$lock, you can see from the TM locking levels that these are DML locks:
ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
ADDF7EC8 ADDF7EE0        422 TM      88519          0          3          0        697          0
ADDF7F74 ADDF7F8C        479 TM      88519          0          3          0        685          0

Identifying the locked object

Now that we know that each TM row points to a locked object, we can use ID1 to identify the object.
SQL> select object_name from dba_objects where object_id=88519 ;
 
OBJECT_NAME
--------------
TSTLOCK
Sometimes just knowing the object is enough information; but we can dig even deeper. We can identify not just the object, but the block and even the row in the block that Session 2 is waiting on.

Identifying the locked row

We can get this information from v$session by looking at the v$session entry for the blocked session:
SQL> select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
  2* from v$session where sid=479 ;
 
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
        88519             16          171309             0
This gives us the object ID, the relative file number, the block in the datafile, and the row in the block that the session is waiting on. If that list of data sounds familiar, it's because those are the four components of an extended ROWID. We can build the row's actual extended ROWID from these components using the DBMS_ROWID package. The ROWID_CREATE function takes these arguments and returns the ROWID:
SQL> select do.object_name,
  2  row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
  3  dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
  4  from v$session s, dba_objects do
  5  where sid=543
  6  and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;
 
OBJECT_NAME     ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C
--------------- ------------- -------------- --------------- ------------- ------------------
TSTLOCK                 88519             16          171309             0 AAAVnHAAQAAAp0tAAA
And, of course, this lets us inspect the row directly.
SQL> select * from tstlock where rowid='AAAVnHAAQAAAp0tAAA' ;
 
FOO BAR
--- ---
1   a

Conclusion

We've seen how to identify a blocking session, and how to inspect the very row that the waiting session is waiting for. And, I hope, learned a bit about v$lock in the process.


Monday, July 22, 2013

How To Generate ASH Report

How To Generate ASH Report:

Active Session History (ASH) is a very important feature to analyze sessions in Oracle to troubleshoot transient problems that last for just few minutes. ASH reports gives the details needed to analyze the sessions.
Below example shows how to generate ASH(Active Session History) reports from SQLPLUS. The same can be done using Enterprise Manager also.
Default Script Location : $ORACLE_HOME/rdbms/admin/
Scripts                             : ashrpt.sql (for singleinstance)

ashrpti.sql (for  RAC)

1. Running ashrpt.sql (for single instance)
SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id    DB Name      Inst Num Instance
———– ———— ——– ————
12345     TESTDB             1  TESTDB1

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter ‘html’ for an HTML report, or ‘text’ for plain text
Defaults to ‘html’
Enter value for report_type: html
..
..
Defaults to -15 mins
Enter value for begin_time: 09/15/11 00:00:00
Report begin time specified: 09/15/11 00:00:00
Enter value for duration: 5
Report duration specified:   5
Using 15-Sep-11 00:00:00 as report begin time
Using 15-Sep-11 00:05:00 as report end time
Enter value for report_name: ASH
Summary of All User Input
————————-
Format         : HTML
DB Id          : 12345
Inst num       : 1
Begin time     : 15-Sep-11 00:00:00
End time       : 15-Sep-11 00:05:00
Slot width     : Default
Report targets : 0
Report name    : ASH
End of Report
</body></html>
Report written to ASH

2. Running the ashrpti.sql (for RAC)
SQL> @$ORACLE_HOME/rdbms/admin/ashrpti.sql
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter ‘html’ for an HTML report, or ‘text’ for plain text
Defaults to ‘html’
Enter value for report_type: html
Type Specified:                                  html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id     Inst Num DB Name      Instance     Host
———— ——– ———— ———— ————
12345         1     TESTDB       TESTDB1      abcd123

Defaults to current database
Enter value for dbid: 12345
Using database id:
12345
Defaults to current instance
Enter value for inst_num: 1
Using instance number:
1
..
..
Defaults to -15 mins
Enter value for begin_time: 09/15/11 00:00:00
Report begin time specified: 09/15/11 00:00:00
Enter duration in minutes starting from begin time:
Defaults to SYSDATE – begin_time
Press Enter to analyze till current time
Enter value for duration: 5
Report duration specified:   5
Using 15-Sep-11 00:00:00 as report begin time
Using 15-Sep-11 00:05:00 as report end time
Enter value for slot_width: 1
(Here we get values for each minutes. So we get more precise values.)
Specify SESSION_ID (eg: from V$SESSION.SID) report target:
Defaults to NULL:
Enter value for target_session_id:
SESSION report target specified:
Specify SQL_ID (eg: from V$SQL.SQL_ID) report target:
Defaults to NULL: (% and _ wildcards allowed)
Enter value for target_sql_id:
SQL report target specified:
Specify WAIT_CLASS name (eg: from V$EVENT_NAME.WAIT_CLASS) report target:
[Enter 'CPU' to investigate CPU usage]
Defaults to NULL: (% and _ wildcards allowed)
Enter value for target_wait_class:
WAIT_CLASS report target specified:
Specify SERVICE_HASH (eg: from V$ACTIVE_SERVICES.NAME_HASH) report target:
Defaults to NULL:
Enter value for target_service_hash:
SERVICE report target specified:
Specify MODULE name (eg: from V$SESSION.MODULE) report target:
Defaults to NULL: (% and _ wildcards allowed)
Enter value for target_module_name:
MODULE report target specified:
Specify ACTION name (eg: from V$SESSION.ACTION) report target:
Defaults to NULL: (% and _ wildcards allowed)
Enter value for target_action_name:
ACTION report target specified:
Specify CLIENT_ID (eg: from V$SESSION.CLIENT_IDENTIFIER) report target:
Defaults to NULL: (% and _ wildcards allowed)
Enter value for target_client_id:
CLIENT_ID report target specified:
Specify PLSQL_ENTRY name (eg: “SYS.DBMS_LOB.*”) report target:
Defaults to NULL: (% and _ wildcards allowed)
Enter value for target_plsql_entry:
PLSQL_ENTRY report target specified:
End of Report
</body></html>
Report written to ashrpt_1_0915_0005.html

Different Types of Indexes

Using a Different Index Type:
=============================
1.B-Tree Indexes
These indexes are the standard index type, and they are excellent for primary key and highly-selective indexes.
Used as concatenated indexes, the database can use B-tree indexes to retrieve data sorted by the index columns.

2.Bitmap Indexes
These indexes are suitable for low cardinality data. Through compression techniques, they can generate a large number of rowids with minimal I/O.
Combining bitmap indexes on non-selective columns allows efficient AND and OR operations with a great number of rowids with minimal I/O.
Bitmap indexes are particularly efficient in queries with COUNT(), because the query can be satisfied within the index.

3.Function-based Indexes
These indexes allow access through a B-tree on a value derived from a function on the base data.
Function-based indexes have some limitations with regards to the use of nulls, and they require that you have the query optimizer enabled.
Function-based indexes are particularly useful when querying on composite columns to produce a derived result or to overcome limitations
in the way data is stored in the database. An example is querying for line items in an order exceeding a certain value derived from
(sales price - discount) x quantity, where these were columns in the table. Another example is to apply the UPPER function to the data
to allow case-insensitive searches.

4.Partitioned Indexes
Partitioning a global index allows partition pruning to take place within an index access, which results in reduced I/Os.
By definition of good range or list partitioning, fast index scans of the correct index partitions can result in very fast query times.

5.Reverse Key Indexes
These indexes are designed to eliminate index hot spots on insert applications. These indexes are excellent for insert performance,
but they are limited because the database cannot use them for index range scans.

Tracing a Session in Oracle

What is the correct way to trace a session in Oracle
What is the “correct” or recommended way to trace other sessions in Oracle 11g? I was wondering this lately because over the years I’ve seen a few different methods come up. So, this morning when prepping to trace something as part of a test, I got to thinking – I’d rather do it using the most appropriate method, but what is that? I did just a bit of research on the methods that I’m aware of, and here’s what I think is the way to go. I want to make it clear that this isn’t an “official” statement for Oracle, just what I’ve used and what I believe is what we should be using (if it really matters at all.) First, identify the session you want, in my case it is easy since I’m just mucking around:

SQL> select username, status, sid, serial# from v$session
where username ='PRODSUPT' order by 1;

USERNAME STATUS SID SERIAL#
-------- ------ --- -------
PRODSUPT ACTIVE 3   5023

Note, the serial# will change through this example because I need to logout and back in again, to verify I get a new trace file each time.

ORADEBUG

This goes way back to when I first started in 7.3 or 8.0 (and before?) and I’m not going to cover it since Oracle provides many newer methods.

ALTER SESSION SET EVENTS ’10046 TRACE NAME CONTEXT FOREVER,LEVEL 12′;

Then I learned the “alter session” command, which works if you’re tracing your own session. In the case of a DBA though, you are often tracing another session, not yourself. This is worth trying out sometime though.
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID, SERIAL#, TRUE);
Next in my memory came the dbms_system procedure. This has been around since I believe 8i or 9i.

exec dbms_system.set_sql_trace_in_session(3,5023,true);
exec dbms_system.set_sql_trace_in_session(3,5023,false);

DBMS_SUPPORT.START_TRACE_IN_SESSION(SID, SERIAL#, WAITS, BINDS);

Next I learned about dbms_support, but the catch here is that it isn’t in the database by default. You have to create it first.

SQL> @?/rdbms/admin/dbmssupp.sql
Package created. Package body created.

exec dbms_support.start_trace_in_session(3,5025,true,true);
exec dbms_support.stop_trace_in_session(3,5025);

DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID, SERIAL_NUM,BINDS,WAITS);

So then comes dbms_monitor, which as best I can tell, is the official way we should be doing this as of 11g (since it is new in 11g). I really don’t know if there’s more to it than the previous methods, but it seems to be the future. I’m guessing the others might be deprecated over time, and this is what we’ll use. Anyone that knows more, please let me know.

SQL> exec dbms_monitor.session_trace_enable(session_id=>3,serial_num=>5027,binds=>true,waits=>true);
PL/SQL procedure successfully completed.

SQL> exec dbms_monitor.session_trace_disable(session_id=>3,serial_num=>5027);
PL/SQL procedure successfully completed.

WITH THAT COVERED
After you’ve done your trace, well – and while you’re doing it, you should be able to find a trace file in your trace directory:
->ll -rt *.trc
-rw-r----- 1 oracle dba 1057 May 24 10:35 T10AC_arc3_9069.trc
-rw-r----- 1 oracle dba 977 May 24 10:39 T10AC_lgwr_8922.trc
-rw-r----- 1 oracle dba 2127 May 24 10:49 T10AC_ora_3029.trc
-rw-r----- 1 oracle dba 65169 May 24 10:50 T10AC_ora_4412.trc

And the beginning of the file should look something like this:
Trace file /oracle/product/diag/rdbms/t10ac/T10AC/trace/T10AC_ora_4412.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /oracle/product/11.2.0.2
System name:    Linux
Node name:      ac-tst-db1.cmc.cable.comcast.com
Release:        2.6.18-128.1.1.el5
Version:        #1 SMP Mon Jan 26 13:58:24 EST 2009
Machine:        x86_64
Instance name: T10AC
Redo thread mounted by this instance: 1
Oracle process number: 32
Unix process pid: 4412, image: oracle@ac-tst-db1.cmc.cable.comcast.com (TNS V1)

TKPROF

You can now use tkprof to format you trace file into a more readable format. The basic syntax is:
tkprof <source trace file> <output file>
tkprof T10AC_ora_4412.trc T10AC_ora_4412.tkprofs

TRACING AND MAX_DUMP_FILE_SIZE
Today I was tracing a poor query. The database had max_dump_file_size set to prevent overwhelming the system with huge trace files. I needed to increase the size to unlimited to so I could get my trace to complete. When you do this

alter session set max_dump_file_size = unlimited;

you have to set it on the session running the query, not the session that you run the trace from

PT Basics

1. What is Performance Tuning?
Ans: Making optimal use of system using existing resources called performace tuning.

2. Types of Tunings?
Ans: 1. CPU Tuning
2. Memory Tuning
3. IO Tuning
4. Application Tuning
5. Databse Tuning

3. What Mailny Database Tuning contains?
Ans: 1. Hit Ratios
2. Wait Events

3. What is an optimizer?
Ans: Optimizer is a mechanizm which will make the execution plan of an sql statement

4. Types of Optimizers?
Ans: 1. RBO(Rule Based Optimizer)
2. CBO(Cost Based Optimzer)

5. Which init parameter is used to make use of Optimizer?
Ans: optimizer_mode=   rule----RBO
      cost---CBO
      choose--------First CBO otherwiser RBO

6. Which optimizer is the best one?
Ans: CBO

7. What are the pre requsited to make user of Optimizer?
Ans: 1. Set the optimizer mode
2. Collect the statistics of an object

8. How do you collect statistics of a table?
Ans: analyze table emp compute statistics or analyze table emp estimate statistics

9. What is the diff between compute and estimate?
Ans: If you use compute, The FTS will happen, if you use estimate just 10% of the table will be read

10. What wll happen if you set the optimizer_mode=choose?
Ans: If the statistics of an object is available then CBO used. if not RBO will be used

11. Data Dictionay follows which optimzer mode?
Ans: RBO

12. How do you delete statistics of an object?
Ans: analyze table emp delete statistics

13. How do you collect statistics of a user/schema?
Ans: exec dbms_stats.gather_schema_stats(scott)

14. How do you see the statistics of a table?
Ans: select num_rows,blocks,empty_blocks from dba_tables where tab_name='emp'

15. What are chained rows?
Ans: These are rows, it spans in multiple blocks

16. How do you collect statistics of a user in Oracle Apps?
ans: fnd_stats package

17. How do you create a execution plan and how do you see?
Ans: 1. @?/rdbms/admin/utlxplan.sql   --------- it creates a plan_table
2. explain set statement_id='1' for select * from emp;
3. @?/rdbms/admin/utlxpls.sql -------------it display the plan


18. How do you know what sql is currently being used by the session?
Ans: by goind v$sql

19. What is a execution plan?
Ans: Its a road map how sql is being executed by oracle db

20. How do you get the index of a table and on which column the index is?
Ans: dba_indexes and dba_ind_columns

21. Which init paramter you have to set to by pass parsing?
Ans: cursor_sharing=force

22. How do you know which session is running long jobs?
Ans: by going v$session_longops

23. How do you flush the shared pool?
Ans: alter system flush shared_pool

24. How do you get the info about FTS?
Ans: using v$sysstat

25. How do you increase the db cache?
Ans: alter table emp cache

26. Where do you get the info of library cache?
Ans: v$librarycache

27. How do you get the information of specific session?
Ans: v$mystat

28. How do you see the trace files?
Ans: using tkprof --- usage: tkprof allllle.trc llkld.txt

29. Types of hits?
Ans: Buffer hit and library hit

30. Types of wait events?
Ans: cpu time and  direct path read


SQL Advisor

1.Creating Task Name:
---------------------

 SQL> SET SERVEROUTPUT ON
declare
stmt_task VARCHAR2(100);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'dfaw5z40nt5ww',time_limit  => 3600);
DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
end;
/SQL>   2    3    4    5    6    7
task_id: TASK_7019

PL/SQL procedure successfully completed.


2.Tuning the Task:
------------------

SQL> EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_174021');

PL/SQL procedure successfully completed.


3.Generating the sql tuned report:
----------------------------------

SET linesize 200
SET LONG 999999999
SET pages 1000
SET longchunksize 20000
SELECT dbms_sqltune.report_tuning_task('TASK_174021', 'TEXT', 'ALL') FROM dual;

How To Compare Two AWR Reports

How To Compare Two AWR Reports?

Often we generate AWR reports for performance tuning our system. Mostly we have a baseline AWR when the system was performing fine and an AWR when the system was showing degraded performance. Comparing these two reports would give us an idea of where the problem could be and we can fine tune those parameters.
Lets compare AWR reports of two days , say 26 and 27th Aug for the time
period 1:00 to 2:00AM , we have 2 ways to do this from the sqlplus:
1. Using the default script awrddrpt.sql. Steps are shown below:
SQL> @$ORACLE_HOME/rdbms/admin/awrddrpt.sql
Enter value for report_type: html
Enter value for num_days: 2
Listing the last 2 days of Completed Snapshots
Snap
Instance     DB Name        Snap Id    Snap Started    Level
———— ———— ——— —————— —–
DB11     DB1                  19676 26 Aug 2011 00:00      1
19677 26 Aug 2011 01:00      1
19678 26 Aug 2011 02:00      1
Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 19677
First Begin Snapshot Id specified: 19677
Enter value for end_snap: 19678
First End   Snapshot Id specified: 19678
Enter value for num_days2: 2
Listing the last 2 days of Completed Snapshots
Instance     DB Name        Snap Id    Snap Started    Level
———— ———— ——— —————— —–
DB11     DB1                19700 27 Aug 2011 00:00      1
19701 27 Aug 2011 01:00      1
19702 27 Aug 2011 02:00      1
Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap2: 19701
Second Begin Snapshot Id specified: 19701
Enter value for end_snap2: 19702
Second End   Snapshot Id specified: 19702
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrdiff_1_19677_1_19701.html
2. From the sqlplus using DBMS_WORKLOAD_REPOSITORY package.
Unlike the above method, we need to have the DBID, INSTANCE NUMBER, and the two paisr of snapshot ids.
To get the DBID
SQL> select dbid from V$database;
DBID
———-
1224460560
To get the instance ID
SQL> select instance_number from v$instance;
INSTANCE_NUMBER
—————
1
To get snap shot ID and time period

select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(4034329550,1,8947,8951,
                                                                  4034329550,1,8971,8975));

SQL> select snap_id, snap_level, to_char(begin_interval_time, ‘dd/mm/yy hh24:mi:ss’) from dba_hist_snapshot where INSTANCE_NUMBER =1 order by 1;

Choose the snap shots we require. Here in this case , we need the snapshot ids of 26th 27th 1:00 AM to 2:00AM.
19678          1 26/08/11 01:00:36
19679          1 26/08/11 02:00:16
…
19702          1 27/08/11 01:00:11
19703          1 27/08/11 02:00:03
Now the spool the output in a spool file.
SQL> Spool path/file-name. html
SQL> select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html
(1224460560,1,19678,19679,1224460560,1,19702,19703));
SQL> spool off
The outfile clearly gives the difference and difference percentage of the first and second set of values.