Identify the blocking session

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
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
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
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
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.

How do you find out who is locking a specific row in a table?

1- Select the sid and other desired columns from v$session where lockwait is not null

2- Select * from v$lock where sid = the sid(s) from the prior step

3- Look at the ID1 column for the requested but not held lock

4- Select * from v$lock where ID1 = the ID1 value returned in the prior step

5- Select from v$session for the sid found in step 4 that has the lock

At this point you can also use the sid to query against v$sql or any of the other v$ tables that contains SQL statements to see the SQL being executed by the blocking session if the v$session table shows a status of ACTIVE for this session. SQL does not normally display for INACTIVE sessions or sessions executing DDL.

Here is a series of sample SQL scripts to perform items 1 – 5:

Find all lock waited sessions:

set echo off

rem
rem filename: session_locked.sql
rem SQL*Plus script to display selected sessions and related process infor-
rem mation for all Oracle sessions blocked from processing due to a lock
rem held by another session.
rem
rem 11/27/95 s3527 m d powell new script
rem 19991207 Mark D Powell Chg headings to reflect Oracle terminology
rem

set verify off
column machine format a08 heading "APPL.|MACHINE"
column sid format 99999
column serial# format 99999
column spid format 99999 heading "ORACLE|SERVER|PROCESS"
column process format 99999 heading "USER|APPL.|PROCESS"
column username format a12

rem

select
s.username, s.status, s.sid, s.serial#,
p.spid, s.machine, s.process, s.lockwait
from v$session s, v$process p
where s.lockwait is not null
and s.paddr = p.addr
/

Find lock information based on SID of lock waited session:

set echo off

rem
rem filename: lock_sid.sql
rem SQL*Plus script to display Oracle dml locks held by an Oracle session.
rem
rem 11/27/95 s3527 m d powell new script
rem

set verify off
set feedback on
column "Mode Held" format a9
column "Mode Req " format a9

REM
REM If type = TM then id1 = object id
REM TX rbs number and slot
REM id2 = wrap number
REM

select
sid, type, id1, id2,
decode(lmode,
0,'WAITING' ,
1,'Null' ,
2,'Row Shr' ,
3,'Row Exc' ,
4,'Share' ,
5,'Shr Row X',
to_char(lmode)
) "Mode Held",
decode(request,
0,'None' ,
1,'Null' ,
2,'Row Shr' ,
3,'Row Exc' ,
4,'Share' ,
5,'Shr Row X',
6,'Exclusive',
to_char(request)
) "Mode Req "
from v$lock
where sid = &session
/

Find holder and other waiters for a lock ID:

set echo off

rem
rem filename: lock_find.sql
rem SQL*Plus script to display all Oracle sessions holding or attempting
rem to obtain a particular lock (id1) on an object.
rem
rem 11/27/95 s3527 m d powell new script
rem 04/28/98 dbawrk m d powell + comments on value of id1 and id2
rem

set verify off
set feedback on
column "Mode Held" format a9
column "Mode Req " format a9

rem
rem If type = TM then id1 = object id
rem TX rbs number and slot
rem id2 = wrap number
rem

select
sid, type, id1, id2,
decode(lmode,
0,'WAITING' ,
1,'Null' ,
2,'Row Shr' ,
3,'Row Exc' ,
4,'Share' ,
5,'Shr Row X',
6,'Exclusive',
to_char(lmode)
) "Mode Held",
decode(request,
0,'None' ,
1,'Null' ,
2,'Row Shr' ,
3,'Row Exc' ,
4,'Share' ,
5,'Shr Row X',
6,'Exclusive',
to_char(request)
) "Mode Req "
from v$lock
where id1 = '&lockno'
/

Find the SQL for the sid

set echo off

rem
rem filename: sql_user.sql
rem SQL*Plus script to display the sql being executed for a particular
rem Oracle session.
rem
rem 11/27/95 s3527 m d powell new script
rem

set verify off

select
sid, username, command, lockwait, status,
osuser, sql_text
from v$session s, v$sqlarea q
where s.sql_hash_value = q.hash_value
and s.sql_address = q.address
and s.sid = &usrsid
/

Just change the where clause in the first query from where lockwait is not null to where s.sid = &session_id and you have the code for session_sid.sql

Once you know the blocking session you now need to determine if the blocking session is a run away session, the user has gone to lunch or left for the day, or is a sign of a system problem. That task is beyond this article, which I hope will give you a basic introduction to lock blocker determination and tracking.

Oracle enqueue wait tips

There are many types of Oracle enqueues, here is a sample of the most common types:

· CF enqueue – The CF enqueue is a Control File enqueue (a.k.a. enq: CF – contention) and happens during parallel access 6to the control files. The CF enqueue can be seen during any action that requires reading the control file, such as redo log archiving, redo log switches and begin backup commands.

· CI Enqueue - The CI enqueue is the Cross Instance enqueue (a.k.a. enq: US - contention) and happens when a session executes a cross instance call such as a query over a database link.


· FB enqueue – This is the Format Block enqueue, used only when data blocks are using ASSM (Automatic Segment Space Management or bitmapped freelists). As we might expect, common FB enqueue relate to buffer busy conditions, especially since ASSM tends to cause performance problems under heavily DML loads.


· HV enqueue – The HV enqueue (a.k.a. enq: HV - contention) is similar to the HW enqueue but for parallel direct path INSERTs.


· HW enqueue – The HW High Water enqueue (a.k.a. enq: HW – contention) occurs when competing processing are inserting into the same table and are trying to increase the high water mark of a table simultaneously. The HW enqueue can sometimes be removed by adding freelists or moving the segment to ASSM.


· KO enqueue – The KO enqueue (a.k.a. enq: KO - fast object checkpoint) is seem in Oracle STAR transformations and high enqueue waits can indicate a sub-optimal DBWR background process.


· PE enqueue – The PE enqueue (a.k.a. enq: PE – contention) is the Parameter Enqueue, which happens after “alter system” or “alter session” statements.


· PS enqueue – The PS enqueue is the Parallel Slave synchronization enqueue (a.k.a enq: PS – contention), which is only seen with Oracle parallel query. The PS enqueue happens when pre-processing problems occur when allocating the factotum (slave) processes for OPQ.


· RO Enqueue – The RO enqueue is the Reuse Object enqueue and is a cross-instance enqueue related to truncate table and drop table DDL operations.


· SQ enqueue – The SQ enqueue is the Sequence Cache enqueue (a.k.a. enq: SQ – contention) is used to serialize access to Oracle sequences.


· SS enqueue – The SS enqueue is the Sort Segment enqueue (a.k.a. enq:SS – contention) and these are related to the sorting of large result sets.


· SS enqueue – The SS enqueues are Sort Segment enqueues (a.k.a. enq: SS – contention), and occur when a process is performing a large sort operation.


· ST enqueue – The ST enqueue can be seen in a partitioned environment when a large number of partitions are created simultaneously.


· TC enqueue – The TC enqueue is related to the DBWR background process and occur when “alter tablespace” commands are issued. You will also see the TC enqueue when doing parallel full-table scans where rows are accessed directly, without being loaded into the data buffer cache.


· TM enqueue – The TM enqueue related to Transaction Management (a.k.a. enq: TM - contention) and can be seen when tables are explicitly locked with reorganization activities that require locking of a table.


· TQ enqueue – The TQ enqueue is the Queue Table enqueue (a.k.a. enq: TQ - DDL contention) and happens during Data ump (export import) operations.


· TS enqueue – The TS enqueue is the temporary segment enqueue (a.k.a. enq: TS – contention) and these enqueues happen during disk sort operations.


· TT enqueue – The TT enqueue (a.k.a. enq: TT – contention) is used to avoid deadlocks in parallel tablespace operations. The TT enqueue can be seen with parallel create tablespace and parallel point in time recovery (PITR)


· TX runqueue – The TX enqueue is the transaction enqueue (a.k.a. enq: TX – contention) and is commonly related to buffer busy waits, in conditions where multiple transaction attempt to update the same data blocks.


enq: TX - row lock contention

enq: TX - allocate ITL entry

enq: TX - row lock contention



· UL enqueue – The UL enqueue is a User Lock enqueue (a.k.a. enq: UL – contention) and happens when a lock is requested in dbms_lock.request. The UL enqueue can be seen in Oracle Data Pump.


· US Enqueue - The US enqueue happens with Oracle automatic UNDO management was undo segments are moved online and offline.

Solving Waits on "enq: TM - contention"

Solving Waits on "enq: TM - contention"
by Dean R. on Jul 14 2008 at 4:55 AM in Wait Time Analysis

Recently, I was assisting one of our customers of Ignite for Oracle trying to diagnose sessions waiting on the "enq: TM - contention" event. The blocked sessions were executing simple INSERT statements similar to:

INSERT INTO supplier VALUES (:1, :2, :3);


Waits on "enq: TM - contention" indicate there are unindexed foreign key constraints. Reviewing the SUPPLIER table, we found a foreign key constraint referencing the PRODUCT table that did not have an associated index. This was also confirmed by the Top Objects feature of Ignite for Oracle because all the time was associated with the PRODUCT table. We added the index on the column referencing the PRODUCT table and the problem was solved.
Cause

After using Ignite for Oracle's locking feature to find the blocking sessions, we found the real culprit. Periodically, as the company reviewed its vendor list, they "cleaned up" the SUPPLIER table several times a week. As a result, rows from the SUPPLIER table were deleted. Those delete statements were then cascading to the PRODUCT table and taking out TM locks on it.
Reproducing the Problem

This problem has a simple fix, but I wanted to understand more about why this happens. So I reproduced the issue to see what happens under the covers. I first created a subset of the tables from this customer and loaded them with sample data.

CREATE TABLE supplier
( supplier_id number(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

INSERT INTO supplier VALUES (1, 'Supplier 1', 'Contact 1');
INSERT INTO supplier VALUES (2, 'Supplier 2', 'Contact 2');
COMMIT;

CREATE TABLE product
( product_id number(10) not null,
product_name varchar2(50) not null,
supplier_id number(10) not null,
CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
ON DELETE CASCADE
);

INSERT INTO product VALUES (1, 'Product 1', 1);
INSERT INTO product VALUES (2, 'Product 2', 1);
INSERT INTO product VALUES (3, 'Product 3', 2);
COMMIT;


I then executed statements similar to what we found at this customer:

User 1: DELETE supplier WHERE supplier_id = 1;
User 2: DELETE supplier WHERE supplier_id = 2;
User 3: INSERT INTO supplier VALUES (5, 'Supplier 5', 'Contact 5');


Similar to the customer's experience, User 1 and User 2 hung waiting on "enq: TM - contention". Reviewing information from V$SESSION I found the following:

SELECT l.sid, s.blocking_session blocker, s.event, l.type, l.lmode, l.request, o.object_name, o.object_type
FROM v$lock l, dba_objects o, v$session s
WHERE UPPER(s.username) = UPPER('&User')
AND l.id1 = o.object_id (+)
AND l.sid = s.sid
ORDER BY sid, type;


Following along with the solution we used for our customer, we added an index for the foreign key constraint on the SUPPLIER table back to the PRODUCT table:

CREATE INDEX fk_supplier ON product (supplier_id);


When we ran the test case again everything worked fine. There were no exclusive locks acquired and hence no hanging. Oracle takes out exclusive locks on the child table, the PRODUCT table in our example, when a foreign key constraint is not indexed.

Query to Find Unindexed Foreign Key Constraints

Now that we know unindexed foreign key constraints can cause severe problems, here is a script that I use to find them for a specific user (this can easily be tailored to search all schemas):

SELECT * FROM (
SELECT c.table_name, cc.column_name, cc.position column_position
FROM user_constraints c, user_cons_columns cc
WHERE c.constraint_name = cc.constraint_name
AND c.constraint_type = 'R'
MINUS
SELECT i.table_name, ic.column_name, ic.column_position
FROM user_indexes i, user_ind_columns ic
WHERE i.index_name = ic.index_name
)
ORDER BY table_name, column_position;

What is enq: TX - row lock contention

What is enq: TX - row lock contention
Enqueues are locks that coordinate access to database resources. enq: wait event indicates that the session is waiting for a lock that is held by another session. The name of the enqueue is as part of the form enq: enqueue_type - related_details.

The V$EVENT_NAME view provides a complete list of all the enq: wait events.

TX enqueue are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.

Several Situation of TX enqueue:
--------------------------------------
1) Waits for TX in mode 6 occurs when a session is waiting for a row level lock that is already held by another session. This occurs when one user is updating or deleting a row, which another session wishes to update or delete. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.

The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.

2) Waits for TX in mode 4 can occur if a session is waiting due to potential duplicates in UNIQUE index. If two sessions try to insert the same key value the second session has to wait to see if an ORA-0001 should be raised or not. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.

The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.

3)Waits for TX in mode 4 is also possible if the session is waiting due to shared bitmap index fragment. Bitmap indexes index key values and a range of ROWIDs. Each ‘entry’ in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock in mode 4. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.

Troubleshooting:

for which SQL currently is waiting to,

select sid, sql_text
from v$session s, v$sql q
where sid in (select sid
from v$session where state in ('WAITING')
and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id));

The blocking session is,
SQL> select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;

Perform Without Waiting

Perform Without Waiting
By Arup Nanda
OTN
Diagnose performance problems, using the wait interface in Oracle 10g.

John, the DBA at Acme Bank, is on the phone with an irate user, Bill, who complains that his database session is hanging, a complaint not unfamiliar to most DBAs. What can John do to address Bill's complaint?

Acme Bank's database is Oracle Database 10g, so John has many options. Automatic Database Diagnostic Manager (ADDM), new in Oracle Database 10g, can tell John about the current overall status and performance of the database, so John starts with ADDM to determine whether what Bill's session is experiencing is the result of a databasewide issue. The ADDM report identifies no databasewide issues that could have this impact on Bill's session, so John moves on to the next option.

One way to diagnose session-level events such as Bill's is to determine whether the session is waiting for anything, such as the reading of a block of a file, a lock on a table row, or a latch. Oracle has provided mechanisms to display the waits happening inside the database since Oracle7, and during the last several years, the model has been steadily perfected, with more and more diagnostic information added to it. In Oracle Database 10g, which makes significantly improved wait event information available, diagnosing a session slowdown has become even easier. This article shows you how to use the wait events in Oracle Database 10g to identify bottlenecks.

Session Waits

How can John the DBA determine what's causing Bill's session to hang? Actually, the session is not hanging; it's waiting for an event to happen, and that's exactly what John checks for.

To continue his investigation, John could use Oracle Enterprise Manager or he could directly access V$ views from the command line. John has a set of scripts he uses to diagnose these types of problems, so he uses the command line.

John queries the V$SESSION view to see what Bill's session is waiting for. (Note that John filters out all idle events.)


select sid, username, event, blocking_session,
seconds_in_wait, wait_time
from v$session where state in ('WAITING')
and wait_class != 'Idle';

The output follows, in vertical format.

SID : 270
USERNAME : BILL
EVENT : enq: TX - row lock contention
BLOCKING_SESSION : 254
SECONDS_IN_WAIT : 83
WAIT_TIME : 0


Looking at this information, John immediately concludes that Bill's session with SID 270 is waiting for a lock on a table and that that lock is held by session 254 (BLOCKING_SESSION).

But John wants to know which SQL statement is causing this lock. He can find out easily, by issuing the following query joining the V$SESSION and V$SQL views:

select sid, sql_text
from v$session s, v$sql q
where sid in (254,270)
and (q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id);

Listing 1 shows the result of the query. And there (in Listing 1) John sees it—both sessions are trying to update the same row. Unless session 254 commits or rolls back, session 270 will continue to wait for the lock. He explains this to Bill, who, considerably less irate now, decides that something in the application has gone awry and asks John to kill session 254 and release the locks.

Code Listing 1: V$SESSION and V$SQL query finds lock
SID SQL_TEXT
--- ---------------------------------------------------------------------
270 update accounts set balance = balance - 750 where account_no = 333
254 update accounts set balance = balance - 1000 where account_no = 333


Wait Classes

After John kills the blocking session, Bill's session continues but is very slow. John decides to check for other problems in the session. Again, he checks for any other wait events, but this time he specifically checks Bill's session.

In Oracle Database 10g, wait events are divided into various wait classes, based on their type. The grouping of events lets you focus on specific classes and exclude nonessential ones such as idle events. John issues the following against the V$SESSION_WAIT_CLASS view:

select wait_class_id, wait_class,
total_waits, time_waited
from v$session_wait_class
where sid = 270;


The output, shown in Listing 2, shows the wait classes and how many times the session has waited for events in each class. It tells John that application-related waits such as those due to row locks have occurred 17,760 times, for a total of 281,654 centiseconds (cs)—hundredths of a second—since the instance started. John thinks that this TIME_WAITED value is high for this session. He decides to explore the cause of these waits in the application wait class. The times for individual waits are available in the V$SYSTEM_EVENT view. He issues the following query to identify individual waits in the application wait class (class id 4217450380):
Code Listing 2: Waits summarized by wait classes

WAIT_CLASS_ID WAIT_CLASS TOTAL_WAITS TIME_WAITED
------------- --------------- ----------- -----------
1893977003 Other 16331 44107
4217450380 Application 17760 281654
3290255840 Configuration 834 2794
3875070507 Concurrency 1599 96981
3386400367 Commit 13865 4616
2723168908 Idle 513024 103732677
2000153315 Network 254534 379
1740759767 User I/O 32709 53182
4108307767 System I/O 103019 9921

select event, total_waits, time_waited
from v$system_event e, v$event_name n
where n.event_id = e.event_id
and wait_class_id = 4217450380;

Listing 3 shows the output of this query. It shows that lock contentions (indicated by the event enq: TX - row lock contention) constitute the major part of the waiting time in the application wait class. This concerns John. Is it possible that a badly written application made its way through to the production database, causing these lock contention problems?

Code Listing 3: Waits in a specific wait class—"application"
EVENT TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
enq: RO - fast object reuse 5 24
enq: TX - row lock contention 2275 280856
SQL*Net break/reset to client 15696 822


Being the experienced DBA that he is, however, John does not immediately draw that conclusion. The data in Listing 3 merely indicates that the users have experienced lock-contention-related waits a total of 2,275 times, for 280,856 cs. It is possible that mostly 1- or 2-cs waits and only one large wait account for the total wait time, and in that case, the application isn't faulty. A single large wait may be some freak occurrence skewing the data and not representative of the workload on the system. How can John determine whether a single wait is skewing the data?

Oracle 10g provides a new view, V$EVENT_HISTOGRAM, that shows the wait time periods and how often sessions have waited for a specific time period. He issues the following against V$EVENT_HISTOGRAM:


select wait_time_milli bucket, wait_count
from v$event_histogram
where event =
'enq: TX - row lock contention';

The output looks like this:

BUCKET WAIT_COUNT
----------- ----------
1 252
2 0
4 0
8 0
16 1
32 0
64 4
128 52
256 706
512 392
1024 18
2048 7
4096 843


The V$EVENT_HISTOGRAM view shows the buckets of wait times and how many times the sessions waited for a particular event—in this case, a row lock contention—for that duration. For example, sessions waited 252 times for less than 1 millisecond (ms), once less than 16 ms but more than 1 ms, and so on. The sum of the values of the WAIT_COUNT column is 2,275, the same as the value shown in the event enq: TX - row lock contention, shown in Listing 3. The V$EVENT_HISTOGRAM view shows that the most waits occurred in the ranges of 256 ms, 512 ms, and 4,096 ms, which is sufficient evidence that the applications are experiencing locking issues and that this locking is the cause of the slowness in Bill's session. Had the view showed numerous waits in the 1-ms range, John wouldn't have been as concerned, because the waits would have seemed normal.

Time Models

Just after John explains his preliminary findings to Bill, Lora walks in with a similar complaint: Her session SID 355 is very slow. Once again, John looks for the events the session is waiting for, by issuing the following query against the V$SESSION view:


select event, seconds_in_wait,
wait_time
from v$session
where sid = 355;


The output, shown in Listing 4, shows a variety of wait events in Lora's session, including latch contention, which may be indicative of an application design problem. But before he sends Lora off with a prescription for an application change, John must support his theory that bad application design is the cause of the poor performance in Lora's session. To test this theory, he decides to determine whether the resource utilization of Lora's session is extraordinarily high and whether it slows not only itself but other sessions too.

Code Listing 4: Waits experienced by a specific session

EVENT SECONDS_IN_WAIT WAIT_TIME
------------------------------- --------------- ---------
latch: cache buffers lru chain 172 0
latch: checkpoint queue latch 2 0
latch: cache buffers chains 607 0
buffer busy waits 500 0
db file sequential read 30247 0
db file scattered read 887 0


In the Time Model interface of Oracle Database 10g, John can easily view details of time spent by a session in various activities. He issues the following against the V$SESS_TIME_MODEL view:



select stat_name, value
from v$sess_time_model
where sid = 355;

The output, shown in Listing 5, displays the time (in microseconds) spent by the session in various places. From this output, John sees that the session spent 503,996,336 microseconds parsing (parse time elapsed), out of a total of 878,088,366 microseconds on all SQL execution (sql execute elapsed time), or 57 percent of the SQL execution time, which indicates that a cause of this slowness is high parsing. John gives Lora this information, and she follows up with the application design team.

Code Listing 5: Session time model

STAT_NAME VALUE
--------------------------------------------------- ----------
DB time 878239757
DB CPU 835688063
background elapsed time 0
background cpu time 0
sequence load elapsed time 0
parse time elapsed 503996336
hard parse elapsed time 360750582
sql execute elapsed time 878088366
connection management call elapsed time 7207
failed parse elapsed time 134516
failed parse (out of shared memory) elapsed time 0
hard parse (sharing criteria) elapsed time 0
hard parse (bind mismatch) elapsed time 0
PL/SQL execution elapsed time 6294618
inbound PL/SQL rpc elapsed time 0
PL/SQL compilation elapsed time 126221
Java execution elapsed time 0

OS Statistics

While going over users' performance problems, John also wants to rule out the possibility of the host system's being a bottleneck. Before Oracle 10g, he could use operating system (OS) utilities such as sar and vmstat and extrapolate the metrics to determine contention. In Oracle 10g, the metrics at the OS level are collected automatically in the database. To see potential host contention, John issues the following query against the V$OSSTAT view:

select * from v$osstat;

The output in Listing 6 shows the various elements of the OS-level metrics collected. All time elements are in cs. From the results in Listing 6, John sees that the single CPU of the system has been idle for 51,025,805 cs (IDLE_TICKS) and busy for 2,389,857 cs (BUSY_TICKS), indicating a CPU that is about 4 percent busy. From this he concludes that the CPU is not a bottleneck on this host. Note that if the host system had more than one CPU, the columns whose headings had the prefix AVG_, such as AVG_IDLE_TICKS, would show the average of these metrics over all the CPUs.
Code Listing 6: Output from V$OSSTAT view

STAT_NAME VALUE OSSTAT_ID
-------------------------- ----------- ----------
NUM_CPUS 1 0
IDLE_TICKS 51025805 1
BUSY_TICKS 2389857 2
USER_TICKS 1947618 3
SYS_TICKS 439736 4
NICE_TICKS 2503 6
AVG_IDLE_TICKS 51025805 7
AVG_BUSY_TICKS 2389857 8
AVG_USER_TICKS 1947618 9
AVG_SYS_TICKS 439736 10
AVG_NICE_TICKS 2503 12
RSRC_MGR_CPU_WAIT_TIME 0 14
IN_BYTES 16053940224 1000
OUT_BYTES 96638402560 1001
AVG_IN_BYTES 16053940224 1004
AVG_OUT_BYTES 96638402560 1005

Active Session History

So far the users have consulted John exactly when each problem occurred, enabling him to peek into the performance views in real time. This good fortune doesn't last long—Janice comes to John complaining about a recent performance problem. When John queries the V$SESSION view, the session is idle, with no events being waited for. How can John check which events Janice's session was waiting for when the problem occurred?

Oracle 10g collects the information on active sessions in a memory buffer every second. This buffer, called Active Session History (ASH), which can be viewed in the V$ACTIVE_SESSION_HISTORY dynamic performance view, holds data for about 30 minutes before being overwritten with new data in a circular fashion. John gets the SID and SERIAL# of Janice's session and issues this query against the V$ACTIVE_SESSION_HISTORY view to find out the wait events for which this session waited in the past.

select sample_time, event, wait_time
from v$active_session_history
where session_id = 271
and session_serial# = 5;


The output, excerpted in Listing 7, shows several important pieces of information. First it shows SAMPLE_TIME—the time stamp showing when the statistics were collected—which lets John tie the occurrence of the performance problems to the wait events. Using the data in the V$ACTIVE_SESSION_HISTORY view, John sees that at around 3:17 p.m., the session waited several times for the log buffer space event, indicating that there was some problem with redo log buffers. To further aid the diagnosis, John identifies the exact SQL statement executed by the session at that time, using the following query of the V$SQL view:
Code Listing 7: Output of active session history


SAMPLE_TIME EVENT WAIT_TIME
-------------------------- --------------------------- ---------
22-FEB-04 03.17.23.028 PM latch: library cache 14384
22-FEB-04 03.17.24.048 PM latch: library cache 14384
22-FEB-04 03.17.25.068 PM log file switch completion 17498
22-FEB-04 03.17.26.088 PM log file switch completion 17498
22-FEB-04 03.17.27.108 PM log buffer space 11834
22-FEB-04 03.17.28.128 PM log buffer space 11834
22-FEB-04 03.17.29.148 PM log buffer space 11834
22-FEB-04 03.17.30.168 PM log buffer space 11834
22-FEB-04 03.17.31.188 PM log buffer space 11834


select sql_text, application_wait_time
from v$sql
where sql_id in (
select sql_id
from v$active_session_history
where sample_time =
'22-FEB-04 03.17.31.188 PM'
and session_id = 271
and session_serial# = 5
);


The output is shown in Listing 8.
Code Listing 8: Text of the SQL from active session history

SQL_TEXT APPLICATION_WAIT_TIME
------------------------------------------------------- ---------------------
update accounts set balance = balance -750where950 account_no= 333
update accounts set balance = balance - 845 where 457 account_no = 451
update accounts set balance = balance - 434 where 1235 account_no = 239


The column APPLICATION_WAIT_TIME shows how long the sessions executing that SQL waited for the application wait class. In addition to the SQL_ID, the V$ACTIVE_SESSION_HISTORY view also lets John see specific rows being waited for (in case of lock contentions), client identifiers, and much more.

What if a user comes to John a little late, after the data is overwritten in this view? When purged from this dynamic performance view, the data is flushed to the Active Workload Repository (AWR), a disk-based repository. The purged ASH data can be seen in the DBA_HIST_ACTIVE_SESSION_HIST view, enabling John to see the wait events of a past session. The data in the AWR is purged by default after seven days.

Conclusion

Oracle Database 10g introduces a number of enhancements designed to automate and simplify the performance diagnostic process. Wait event information is more elaborate in Oracle Database 10g and provides deeper insight into the cause of problems, making the diagnosis of performance problems a breeze in most cases, especially in proactive performance tuning.

Here is a script that could help a DBA to see which session is blocking another and on which row exactly.

set serverout on size 1000000
set lines 132
declare
cursor cur_lock is
select sid,id1,id2,inst_id, ctime
from gv$lock
where block = 1;
vid1 number;
vid2 number;
cursor cur_locked is
select sid, inst_id, ctime
from gv$lock
where id1 = vid1
and id2 = vid2
and block <> 1;
vlocks varchar2(30);
vsid1 number;
vobj1 number;
vfil1 number;
vblo1 number;
vrow1 number;
vrowid1 varchar2(20);
vcli1 varchar2(64);
vobj2 number;
vfil2 number;
vblo2 number;
vrow2 number;
vrowid2 varchar2(20);
vcli2 varchar2(64);
vobjname varchar2(30);
vlocked varchar2(30);
ctim1 number;
ctim2 number;
begin
dbms_output.put_line('=====================================================');
dbms_output.put_line('Blocking lock list.');
dbms_output.put_line('=====================================================');
dbms_output.put_line('Block / Is blocked SID INST_ID OBJECT TIME(secs) ROWID CLIENT_IDENTIFIER');
dbms_output.put_line('------------------------- --------- ------- ------------------------------ ---------- ------------------ -----------------');
for c1 in cur_lock loop
vid1 := c1.id1;
vid2 := c1.id2;
select username,sid,row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row#,client_identifier
into vlocks,vsid1,vobj1,vfil1,vblo1,vrow1,vcli1
from gv$session where sid = c1.sid and inst_id = c1.inst_id;
if vobj1 = -1 then
vobjname := 'UNKNOWN';
else
select name into vobjname from sys.obj$ where obj# = vobj1;
select decode(vrow1,0,'MANY ROWS',dbms_rowid.rowid_create(1, vobj1, vfil1, vblo1, vrow1)) into vrowid1 from dual;
end if;
dbms_output.put_line(rpad(vlocks,25) || ' ' ||
to_char(vsid1,'999999999') || ' ' ||
to_char(c1.inst_id,'9999999') || ' ' ||
rpad(vobjname,30) || ' ' ||
to_char(c1.ctime,'999999999') || ' ' || rpad(vrowid1,18) || ' ' || vcli1);
for c2 in cur_locked loop
select username, row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row#
into vlocked, vobj2, vfil2, vblo2, vrow2
from gv$session where sid = c2.sid and inst_id = c2.inst_id;
if vobj2 = -1 then
vobjname := 'UNKNOWN';
else
select name into vobjname from sys.obj$ where obj# = vobj2;
select decode(vrow2,0,'MANY ROWS',dbms_rowid.rowid_create(1, vobj2, vfil2, vblo2, vrow2)) into vrowid2 from dual;
end if;
dbms_output.put_line(chr(9) || '\--> ' || rpad(vlocked,12) || ' ' ||
to_char(c2.sid,'999999999') || ' ' ||
to_char(c2.inst_id,'9999999') || ' ' || rpad(vobjname,30) || ' ' ||
to_char(c2.ctime,'999999999') || ' ' || rpad(vrowid2,18) || ' ' || vcli2 ) ;
end loop;
end loop;
commit;
end;
/

Oracle 10g Hints for Performance Improvement

List of Oracle 10g Hints for the Oracle Optimizer so that you can dictate the execution plan of a query/statement..
Undocumented Hints
* bypass_recursive_check
* bypass_ujvc
* cache_cb
* cache_temp_table
* civ_gb
* collections_get_refs
* cube_gb
* cursor_sharing_exact
* deref_no_rewrite
* dml_update
* domain_index_no_sort
* domain_index_sort
* dynamic_sampling
* dynamic_sampling_est_cdn
* expand_gset_to_union
* force_sample_block
* gby_conc_rollup
* global_table_hints
* hwm_brokered
* ignore_on_clause
* ignore_where_clause
* index_rrs
* index_ss
* index_ss_asc
* index_ss_desc
* like_expand
* local_indexes
* mv_merge
* nested_table_get_refs
* nested_table_set_refs
* nested_table_set_setid
* no_expand_gset_to_union
* no_fact
* no_filtering
* no_order_rollups
* no_prune_gsets
* no_stats_gsets
* no_unnest
* nocpu_costing overflow_nomove
* piv_gb
* piv_ssf
* pq_map
* pq_nomap
* remote_mapped
* restore_as_intervals
* save_as_intervals
* scn_ascending
* skip_ext_optimizer
* sqlldr
* sys_dl_cursor
* sys_parallel_txn
* sys_rid_order
* tiv_gb
* tiv_ssf
* unnest
* use_ttt_for_gsets
Documented Hints
* all_rows
* first_rows
* first_rows_1
* first_rows_100
* choose
* rule
* full
* rowid
* cluster
* hash
* hash_aj
* index
* no_index
* index_asc
* index_combine
* index_join
* index_desc
* index_ffs
* no_index_ffs
* index_ss
* index_ss_asc
* index_ss_desc
* no_index_ss
* no_query_transformation
* use_concat
* no_expand
* rewrite
* norewrite
* no_rewrite
* merge
* no_merge
* fact
* no_fact
* star_transformation
* no_star_transformation
* unnest
* no_unnest
* leading
* ordered
* use_nl
* no_use_nl
* use_nl_with_index
* use_merge
* no_use_merge
* use_hash
* no_use_hash
* parallel
* noparallel / no_parallel
* pq_distribute
* no_parallel_index
* append
* noappend
* cache
* nocache
* push_pred
* no_push_pred
* push_subq
* no_push_subq
* qb_name
* cursor_sharing_exact
* driving_site
* dynamic_sampling
* spread_min_analysis
* merge_aj
* and_equal
* star
* bitmap
* hash_sj
* nl_sj
* nl_aj
* ordered_predicates
* expand_gset_to_union

Using LogMiner Viewer to Perform a Logical Recovery

Module Objectives
Purpose

In this module, you will learn how to use LogMiner to analyze your redo log files so that you can logically recover your database.
Objectives

After completing this module, you should be able to:
Use LogMiner to analyze the redo log files
Perform a logical recovery of the database
Prerequisites

Before starting this module, you should have:


Preinstallation Tasks


Install the Oracle9i Database


Postinstallation Tasks
Review the Sample Schema
Enabling Archiving
Downloaded the logminer.zip module files and unzipped them into your working directory
Reference Material

The following is a list of useful reference material if you want additional information about the topics in this module:


Documentation: A96521-01: Oracle9i Database Administrator's Guide


Overview

In this module, you will examine the following topics:
Analysis of Redo Logs
Oracle9i LogMiner
Analysis of Redo Logs

Knowing what changes have been made to data is useful for many things:
Trace and audit requirements
Application debugging
Logical recovery
Tuning and capacity planning

The redo logs provide a single source of data, including:
All of the data needed to perform recovery (a record of every change to the data and the metadata or data structures)
All of the data needed to pinpoint when a logical corruption to the database began. It is important to pinpoint this time so that you know how to perform a time-based or change-based recovery that restores the database to the state just prior to corruption.
An alternative, supplemental source of data for tuning and capacity planning

Many customers keep this data in archived files, but using redo logs as a source of this information means that the data is always available. No resources need to be used for data collection or overhead (although the data still needs to be translated into usable form).

You can analyze redo logs for historical trends, data access patterns, and frequency of changes to the database, as well as to track specific sets of changes based on transaction, user ID, table, block, and so on.
Oracle9i LogMiner Viewer

Oracle9i LogMiner is a powerful tool that reads the redo logs to provide direct access to the changes that have been made to your databases. With LogMiner, you can audit past database activity, pinpoint when logical corruption occurred and where to perform statement-level recovery. LogMiner also gives you a wealth of information for diagnosing problems with an application, analyzing resource utilization and historical performance, and tuning the database.

LogMiner Viewer is a graphical user interface integrated with Enterprise Manager. LogMiner Viewer allows you to easily specify queries to view data from the redo logs, including SQL redo and undo statements.

In Oracle8i, LogMiner was introduced as the only Oracle-supported method of accessing the redo logs. This module highlights the new features introduced with Oracle9i LogMiner Viewer: additional data types and storage types, additional DDL statements, queries based on actual data values in the redo logs, and continuous monitoring of the redo stream.
Using LogMiner: Steps

In this module, you will use LogMiner to analyze redo log files and logically recover the database. After enabling archiving in the production environment, you will perform the following steps:
1.

Perform an operation which corrupts the database.
2.

Use LogMiner Viewer to analyze the redo log files.
3.

Perform a logical recovery.

Step 3, in turn, has several substeps, which will be detailed later.

Performing an Operation Which Corrupts the Database

Go Back to List

Now you will create a situation in the database that could lead to the need to perform a logical recovery. You will change the data schema definition for the OE user in such a way that deleting a single product will leave the tables logically corrupt.
1.

From a SQL*Plus session connected to the production database, execute the following SQL script:

@alter_table.sql

CONNECT OE/OE@orcl.world

ALTER TABLE Order_Items
DROP CONSTRAINT Order_Items_Product_ID_FK;

ALTER TABLE Inventories
DROP CONSTRAINT Inventories_Product_ID_FK;

ALTER TABLE Product_Descriptions
DROP CONSTRAINT PD_Product_ID_FK;

ALTER TABLE Order_Items
ADD CONSTRAINT Order_Items_Product_ID_FK
FOREIGN KEY (Product_Id)
REFERENCES Product_Information
ON DELETE CASCADE;

ALTER TABLE Inventories
ADD CONSTRAINT Inventories_Product_ID_FK
FOREIGN KEY (Product_Id)
REFERENCES Product_Information
ON DELETE CASCADE;

Although these are perfectly valid DDL statements, adding the ON DELETE CASCADE clauses to these constraints causes all ORDER_ITEMS and ORDERS records to be automatically deleted whenever a product is deleted.
2. To illustrate this, delete a product from the product_information table. From a SQL*Plus session connected to the production database, execute the following SQL script:

@delete_product.sql
CONNECT OE/OE@orcl.world
DELETE FROM Product_Information
WHERE Product_Id = 3000;
COMMIT;

Although only one row has been deleted from the product_information table, rows have also been deleted from the order_items and orders tables. This may not be discovered until later, when executing a report reveals the logical corruption.


3.

Now run a report that compares the sum of all order items in an order with the total for that order. From a SQL*Plus session connected to the production database, execute the following SQL script:

@comp_totals.sql

CONNECT OE/OE@orcl.world
SELECT so.order_id, SUM(so.order_total), SUM(si.total)
FROM ( SELECT order_id, SUM((unit_price * quantity)) AS total
FROM order_items
GROUP BY order_id
) si,
orders so
WHERE so.order_id = si.order_id
GROUP BY so.order_id
HAVING SUM(so.order_total) != SUM(si.total);

The inconsistency in the data is easy to see; notice that the sale order totals no longer reflect the total of their sale item lines. The data has become logically corrupt.


4.

In the SQL*Plus Session, execute the following script:

@force_switch.sql

connect sys/oracle@orcl.world as sysdba
alter system archive log current;
archive log list;


This switch has been forced to move DML statements from the on-line redo log to archived redo logs. The undo SQL statements to fix this will be mined from the archived redo logs.



You can now use LogMiner Viewer to detect what happened, and even to undo the transactions that caused the corruption.
Using LogMiner Viewer to Analyze the Redo Log Files

Go Back to List

You are interested in finding all the transactions performed on the product_information, order_items, and orders tables which may account for the discrepancy in your report. Therefore, you decide to analyze all the transactions in the list of archived redo log files.
1.

Connect to the OMS as sysman/sysman.

2.

Select Tools > Database Applications > LogMiner Viewer


3.

LogMiner Viewer requires a database account with SYSDBA privilege. You will log on as SYS, the preferred credentials are already defined in the Enterprise Manager environment.

From the Navigator pane, Right Click on orcl.world and Select Create Query.


4.

The three tables involved in the DELETE operation were PRODUCT_INFORMATION, ORDER_ITEMS, and ORDERS. We want to search for DELETE operations performed on objects owned by OE. The search should be

OWNER=OE AND OPERATION=DELETE

First from the drop down menu select OWNER, then type OE in the box after the equal. Then click AND.


5.

Select OPERTATION and enter DELETE.


6.

Click Execute.

The DELETE operations previously performed now appear in the Query Results panel at the bottom of the window. Although one delete operation was issued, the ON DELETE CASCADE OPTION deleted two additional rows. You may review any row by double clicking on the row.


7.

Click Close, after reviewing.


8.

Select the Display Options tab. Uncheck everything but SQL_UNDO. Then click Execute.


9.

Click the Save Redo/Undo... button.


10.

Change Format to Text. Change File Name to D:\wkdir\sqlundo.sql. Then click OK.


11.

To exit click Cancel then File > Exit.




Performing a Logical Recovery

Go Back to List

Performing a logical recovery consists of several substeps:
1 Performing the logical recovery
2 Verifying the logical recovery


1. Performing the Logical Recovery

To undo the changes that were made, use the generated script by performing the following steps:
1.

In SQL*Plus connect as OE/OE and execute the script sqlundo.sql. Then type COMMIT.

connect oe/oe@orcl.world
@sqlundo.sql
commit;


2. Verifying the Logical Recovery

To test that the data has been recovered, perform the following steps:
1.

Run the same report, which compares the sum of all orderitems for an order with the total for the order. From a SQL*Plus session connected to the production database, execute the following script:

@comp_totals.sql

CONNECT OE/OE@orcl.world

SELECT so.order_id, SUM(so.total), SUM(si_total)
FROM (
SELECT si.order_id si_order_id,
SUM(si.total)AS si_total
FROM order_items si
GROUP BY si.order_id), sale_orders so
WHERE so.order_id = si_order_id
GROUP BY so.order_id
HAVING SUM(so.total) != SUM(si_total);


This time there are no sale order totals that do not equal the sum of their order items. You have fully recovered the database from the mistake.




Resetting the Environment

Now you should reset the foreign key constraints for the order_items and orders tables back to their correct versions.
1.

From a SQL*Plus session connected to the production database, enter @reset_constraints.sql to reset the constraints to their original version.

@reset_constraints.sql

CONNECT OE/OE@orcl.world

ALTER TABLE Order_Items
DROP CONSTRAINT Order_Items_Product_ID_FK;
ALTER TABLE Inventories
DROP CONSTRAINT Inventories_Product_ID_FK;
ALTER TABLE Order_Items ADD CONSTRAINT Order_Items_Product_ID_FK
FOREIGN KEY (Product_Id)
REFERENCES Product_Information;
ALTER TABLE Inventories ADD CONSTRAINT Inventories_Product_ID_FK
FOREIGN KEY (Product_Id)
REFERENCES Product_Information;
ALTER TABLE Product_Descriptions ADD CONSTRAINT PD_Product_ID_FK
FOREIGN KEY (Product_Id)
REFERENCES Product_Information;


Module Summary

In this module, you should have learned how to:
Use LogMiner to analyze the redo log files
Perform a logical recovery of the database

How To Disable Automatic Statistics Collection in Oracle 10G

To disable the automatic statistics collection, you can execute the following procedure:

exec dbms_scheduler.disable(’GATHER_STATS_JOB’);

To check whether the job is disabled, run the following Query:

select state from dba_scheduler_jobs where job_name = ‘GATHER_STATS_JOB’;

The job details can be viewed by querying the DBA_SCHEDULER_JOBS view:

select job_name, job_type, program_name, schedule_name, job_class
from dba_scheduler_jobs
where job_name = ‘GATHER_STATS_JOB’;

The output will show that the job schedules a program called ‘GATHER_STATS_PROG’ in the ‘MAINTENANCE_WINDOW_GROUP’ time schedule. The program named ‘GATHER_STATS_PROG’ starts the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC stored procedure:

select program_action
from dba_scheduler_programs
where program_name = ‘GATHER_STATS_PROG’;

The job is scheduled according to the value of the SCHEDULE_NAME field. In this example, the scheduled being used is: ‘MAINTENANCE_WINDOW_GROUP’. This schedule is defined in the DBA_SCHEDULER_WINGROUP_MEMBERS view:

select *
from dba_scheduler_wingroup_members
where window_group_name = ‘MAINTENANCE_WINDOW_GROUP’;

The meaning of these ‘WINDOWS’ can be found in ‘DBA_SCHEDULER_WINDOWS’:

select window_name, repeat_interval, duration
from dba_scheduler_windows
where window_name in (’WEEKNIGHT_WINDOW’,'WEEKEND_WINDOW’);

The meaning of these entries is as follows:

The WEEKNIGHT_WINDOW is schedule each week day at 10PM and should last a maximum of 8 hours. The WEEKEND_WINDOW is scheduled Saturday at 0AM and should last 2 days maximum. If the START_DATE and END_DATE columns (not shown) are NULL, then this job will run continuously. All these definitions can be found in the $ORACLE_HOME/rdbms/admin/catmwin.sql script.

The point of illustrating the Automatic Statistics Collection in Oracle 10g+ was simply to show how to check it the delivered stats job is enabled and if you need to disable or turn it off how to do so. In our case we disable the delivered job for our production databases because we execute our own custom scripts via crontab at least once a day and in some cases twice a day. We are running PeopleSoft Financials as well as HRMS. In regards to PeopleSoft Financials we rely on nVision for generating and delivering Financial Reports and we are using dynamic tree selectors. If we let Oracle generate the statistics for us via the delivered scheduled jobs some of our nVision Report Books which consist of up to 10 nVision Reports would never finish generating the report. At the end of our custom shell script to generate schema and system dictionary statistics for the CBO we delete the statistics on the PSTREESELECTxx tables. As a result the majority of our nVision Reports execute in less than 2 minutes and the Report Books with 10 nVision Reports execute in less than 20 minutes for all 10 reports.

Personally in order to ensure the best possible performance in Oracle 10g R2+ I believe it is best to keep the CBO statistics as current as possible.

Oracle System Privileges

General Information
Note: System privileges are privileges that do not relate to a specific schema or object.

Data Dictionary Objects Related To System Privileges
all_sys_privs session_privs user_sys_privs
dba_sys_privs system_privilege_map

Administer
Administer Any SQL Tuning Set
Administer Database Trigger (database level trigger)
Administer Resource Manager
Administer SQL Management Object
Administer SQL Tuning Set
Flashback Archive Administrator
Grant Any Object Privilege
Grant Any Privilege
Grant Any Role
Manage Scheduler
Manage Tablespace
Advanced Queuing
Dequeue Any Queue
Enqueue Any Queue
Manage Any Queue

Advisor Framework
Advisor
Administer SQL Tuning Set
Administer Any SQL Tuning Set
Administer SQL Management Object
Alter Any SQL Profile
Create Any SQL Profile
Drop Any SQL Profile

Alter Any Privileges
Alter Any Cluster
Alter Any Cube
Alter Any Cube Dimension
Alter Any Dimension
Alter Any Evaluation Context
Alter Any Index
Alter Any Indextype
Alter Any Library
Alter Any Materialized View
Alter Any Mining Model
Alter Any Operator
Alter Any Outline
Alter Any Procedure
Alter Any Role
Alter Any Rule
Alter Any Rule Set
Alter Any Sequence
Alter Any SQL Profile
Alter Any Table
Alter Any Trigger
Alter Any Type

Alter Privileges
Alter Database
Alter Profile
Alter Resource Cost
Alter Rollback Segment
Alter Session
Alter System
Alter Tablespace
Alter User
Analyze Privileges
Analyze Any
Analyze Any Dictionary
Audit Privileges
Audit Any
Audit System
Backup Privileges
Backup Any Table
Change Privilege
Change Notification
Clusters
Alter Any Cluster
Create Cluster
Create Any Cluster
Drop Any Cluster
Comment Privileges
Comment Any Mining Model
Comment Any Table
Contexts
Create Any Context
Drop Any Context

Create Any Privileges
Create Any Cluster
Create Any Context
Create Any Cube
Create Any Cube Build Process
Create Any Cube Dimension
Create Any Dimension
Create Any Directory
Create Any Evaluation Context
Create Any Index
Create Any Indextype
Create Any Job
Create Any Library
Create Any Materialized View
Create Any Measure Folder
Create Any Mining Model
Create Any Operator
Create Any Outline
Create Any Procedure
Create Any Rule
Create Any Rule Set
Create Any Sequence
Create Any SQL Profile
Create Any Synonym
Create Any Table
Create Any Trigger
Create Any Type
Create Any View

Create Privileges
Create Cluster
Create Cube
Create Cube Build Process
Create Cube Dimension
Create Database Link
Create Dimension
Create Evaluation Context
Create External Job
Create Indextype
Create Job
Create Library
Create Materialized View
Create Measure Folder
Create Mining Model
Create Operator
Create Procedure
Create Profile
Create Public Database Link
Create Public Synonym
Create Role
Create Rollback Segment
Create Rule
Create Rule Set
Create Sequence
Create Session
Create Synonym
Create Table
Create Tablespace
Create Trigger
Create Type
Create User
Create View
Database
Alter Database
Alter System
Audit System
Database Links
Create Database Link
Create Public Database Link
Drop Public Database Link
Debug
Debug Any Procedure
Debug Connect Session
Delete
Delete Any Cube Dimension
Delete Any Measure Folder
Delete Any Table
Dimensions
Alter Any Dimension
Create Any Dimension
Create Dimension
Drop Any Dimension
Directories
Create Any Directory
Drop Any Directory

Drop Any Privileges
Drop Any Cluster
Drop Any Context
Drop Any Cube
Drop Any Cube Build Process
Drop Any Cube Dimension
Drop Any Dimension
Drop Any Directory
Drop Any Evaluation Context
Drop Any Index
Drop Any Indextype
Drop Any Library
Drop Any Materialized View
Drop Any Measure Folder
Drop Any Mining Model
Drop Any Operator
Drop Any Outline
Drop Any Procedure
Drop Any Role
Drop Any Rule
Drop Any Rule Set
Drop Any Sequence
Drop Any SQL Profile
Drop Any Synonym
Drop Any Table
Drop Any Trigger
Drop Any Type
Drop Any View
Drop Privileges
Drop Profile
Drop Public Database Link
Drop Public Synonym
Drop Rollback Segment
Drop Tablespace
Drop User
Evaluation Context
Alter Any Evaluation Context
Create Any Evaluation Context
Create Evaluation Context
Drop Any Evaluation Context
Execute Any Evaluation Context

Execute Any Privileges
Execute Any Class
Execute Any Evaluation Context
Execute Any Indextype
Execute Any Library
Execute Any Operator
Execute Any Procedure
Execute Any Program
Execute Any Rule
Execute Any Rule Set
Execute Any Type
Export & Import
Export Full Database
Import Full Database
Fine Grained Access Control
Exempt Access Policy
File Group
Manage Any File Group
Manage File Group
Read Any File Group
Flashback
Flashback Any Table
Flashback Archive Administrator
Force
Force Any Transaction
Force Transaction
Indexes
Alter Any Index
Create Any Index
Drop Any Index
Indextype
Alter Any Indextype
Create Any Indextype
Create Indextype
Drop Any Indextype
Execute Any Indextype
Insert
Insert Any Cube Dimension
Insert Any Measure Folder
Insert Any Table
Job Scheduler
Create Any Job
Create External Job
Create Job
Execute Any Class
Execute Any Program
Manage Scheduler
Libraries
Alter Any Library
Create Any Library
Create Library
Drop Any Library
Execute Any Library
Locks
Lock Any Table
Materialized Views
Alter Any Materialized View
Create Any Materialized View
Create Materialized View
Drop Any Materialized View
Flashback Any Table
Global Query Rewrite
On Commit Refresh
Query Rewrite
Mining Models
Alter Any Mining Model
Comment Any Mining Model
Create Any Mining Model
Create Mining Model
Drop Any Mining Model
Select Any Mining Model
OLAP Cubes
Alter Any Cube
Create Any Cube
Create Cube
Drop Any Cube
Select Any Cube
Update Any Cube
OLAP Cube Build
Create Any Cube Build Process
Create Cube Build Process
Drop Any Cube Build Process
Update Any Cube Build Process
OLAP Cube Dimensions
Alter Any Cube Dimension
Create Any Cube Dimension
Create Cube Dimension
Delete Any Cube Dimension
Drop Any Cube Dimension
Insert Any Cube Dimension
Select Any Cube Dimension
Update Any Cube Dimension
OLAP Cube Measure Folders
Create Any Measure Folder
Create Measure Folder
Delete Any Measure Folder
Drop Any Measure Folder
Insert Any Measure Folder
Operator
Alter Any Operator
Create Any Operator
Create Operator
Drop Any Operator
Execute Any Operator
Outlines
Alter Any Outline
Create Any Outline
Drop Any Outline
Procedures
Alter Any Procedure
Create Any Procedure
Create Procedure
Drop Any Procedure
Execute Any Procedure
Profiles
Alter Profile
Create Profile
Drop Profile
Query Rewrite
Global Query Rewrite
Query Rewrite
Refresh
On Commit Refresh
Resumable
Resumable
Roles
Alter Any Role
Create Role
Drop Any Role
Grant Any Role
Rollback Segment
Alter Rollback Segment
Create Rollback Segment
Drop Rollback Segment
Scheduler
Manage Scheduler
Select
Select Any Cube
Select Any Cube Dimension
Select Any Dictionary
Select Any Mining Model
Select Any Sequence
Select Any Table
Select Any Transaction
Sequence
Alter Any Sequence
Create Any Sequence
Create Sequence
Drop Any Sequence
Select Any Sequence
Session
Alter Resource Cost
Alter Session
Create Session
Restricted Session
Synonym
Create Any Synonym
Create Public Synonym
Create Synonym
Drop Any Synonym
Drop Public Synonym
Sys Privileges
SYSDBA
SYSOPER
Tablespace
Alter Tablespace
Create Tablespace
Drop Tablespace
Manage Tablespace
Unlimited Tablespace

Table
Alter Any Table
Backup Any Table
Comment Any Table
Create Any Table
Create Table
Delete Any Table
Drop Any Table
Flashback Any Table
Insert Any Table
Lock Any Table
Select Any Table
Update Any Table
Transaction
Force Any Transaction
Force Transaction
Trigger
Administer Database Trigger
Alter Any Trigger
Create Any Trigger
Create Trigger
Drop Any Trigger
Types
Alter Any Type
Create Any Type
Create Type
Drop Any Type
Execute Any Type
Under Any Type
Update
Update Any Cube
Update Any Cube Build Process
Update Any Cube Dimension
Update Any Table
Under
Under Any Table
Under Any Type
Under Any View
User
Alter User
Become User
Create User
Drop User
View
Create Any View
Create View
Drop Any View
Flashback Any Table
Merge Any View
Under Any View

Granting System Privileges
Grant A Privilege GRANT TO ;
GRANT create table TO uwclass;

Revoking System Privileges
Revoke A Single Privilege REVOKE FROM ;
REVOKE create table FROM uwclass;

Determine User Privs

This query will list the system privileges assigned to a user SELECT LPAD(' ', 2*level) || granted_role "USER PRIVS"
FROM (
SELECT NULL grantee, username granted_role
FROM dba_users
WHERE username LIKE UPPER('%&uname%')
UNION
SELECT grantee, granted_role
FROM dba_role_privs
UNION
SELECT grantee, privilege
FROM dba_sys_privs)
START WITH grantee IS NULL
CONNECT BY grantee = prior granted_role;

or

SELECT path
FROM (
SELECT grantee,
sys_connect_by_path(privilege, ':')||':'||grantee path
FROM (
SELECT grantee, privilege, 0 role
FROM dba_sys_privs
UNION ALL
SELECT grantee, granted_role, 1 role
FROM dba_role_privs)
CONNECT BY privilege=prior grantee
START WITH role = 0)
WHERE grantee IN (
SELECT username
FROM dba_users
WHERE lock_date IS NULL
AND password != 'EXTERNAL'
AND username != 'SYS')
OR grantee='PUBLIC'
/

Dangerous Demo

Execute Any Procedure SELECT *
FROM dba_sys_privs
WHERE privilege LIKE '%CREATE ANY PROC%';

conn owb/owb

CREATE OR REPLACE PROCEDURE .do_sql(sqlin VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE sqlin;
END;
/

BEGIN
.do_sql('drop table emp cascade constraints');
END;
/

Oracle Object Privileges

General Information
Note: While the data dictionary objects are named tab, as in table, the privileges that can be granted and revoked extend to other object types including functions, packages, and procedures.
Data Dictionary Objects Related To Object Privileges
objauth$ objpriv$
dba_col_privs all_col_privs user_col_privs
- all_col_privs_made user_col_privs_made
- all_col_privs_recd user_col_privs_recd
dba_tab_privs all_tab_privs user_tab_privs
- all_tab_privs_made user_tab_privs_made
- all_tab_privs_recd user_tab_privs_recd
column_privileges table_privileges table_privilege_map
Object Privileges
0 ALTER 9 SELECT 22 UNDER
1 AUDIT 10 UPDATE 23 ON COMMIT REFRESH
2 COMMENT 11 REFERENCES 24 QUERY REWRITE
3 DELETE 12 EXECUTE 26 DEBUG
4 GRANT 16 CREATE 27 FLASHBACK
5 INDEX 17 READ 28 MERGE VIEW
6 INSERT 18 WRITE 29 USE (for 11gR2 - not 11gR1)
7 LOCK 20 ENQUEUE 30 FLASHBACK ARCHIVE
8 RENAME 21 DEQUEUE
Note: Privilege 29, USE, was introduced in preparation for a new feature that will first appear in 11gR2.

Granting Object Privileges

Grant A Single Privilege GRANT ON TO
conn uwclass/uwclass

CREATE TABLE test (
testcol VARCHAR2(20));

GRANT SELECT ON test TO abc;

set linesize 100
col grantee format a30
col table_name format a30
col privilege format a20

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abc/abc

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

Grant Multiple Privileges GRANT ON TO
conn uwclass/uwclass

GRANT INSERT, DELETE ON test TO abc;

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abc/abc

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

Grant All Privileges GRANT ALL ON TO
conn abc/abc

GRANT ALL ON test TO uwclass;

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn uwclass/uwclass

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

Grant Execute GRANT EXECUTE ON TO
conn uwclass/uwclass

GRANT EXECUTE ON getosuser TO abc;

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abc/abc

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

Revoking Object Privileges

Revoke A Single Privilege REVOKE ON FROM
conn uwclass/uwclass

REVOKE SELECT ON test FROM abc;

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abc/abc

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

Revoke Multiple Privileges REVOKE ON FROM
conn uwclass/uwclass

REVOKE INSERT, DELETE ON test FROM abc;

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abc/abc

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

Revoke All Privileges REVOKE ALL ON FROM
conn uwclass/uwclass

REVOKE ALL ON test FROM abc;

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abc/abc

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

Revoke Execute REVOKE EXECUTE ON FROM
conn uwclass/uwclass

REVOKE EXECUTE ON getosuser FROM abc;

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abc/abc

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

Granting Column Privileges
Grant Column Privileges GRANT () ON TO ;
GRANT UPDATE (first_name, last_name) ON person TO uwclass;

Revoking Column Privileges
Revoke Column Privilege REVOKE () ON FROM ;
REVOKE UPDATE (first_name, last_name) ON person FROM uwclass;

Object Privilege Related Query
Show privileges by object

set linesize 121
col select_priv format a10
col insert_priv format a10
col update_priv format a10
col delete_priv format a10

SELECT table_name, grantee,
MAX(DECODE(privilege, 'SELECT', 'SELECT')) AS select_priv,
MAX(DECODE(privilege, 'DELETE', 'DELETE')) AS delete_priv,
MAX(DECODE(privilege, 'UPDATE', 'UPDATE')) AS update_priv,
MAX(DECODE(privilege, 'INSERT', 'INSERT')) AS insert_priv
FROM dba_tab_privs
WHERE grantee IN (
SELECT role
FROM dba_roles)
GROUP BY table_name, grantee;

use full scripts

-- /www/doc/sql/useful_misc.sql
-- $Id: useful-misc.sql,v 1.1.3.1 2001/08/29 05:31:19 andy Exp $
--
-- Useful stuff culled largely from Joe Trezzo's Oracle PL/SQL book.
-- See also the other useful_*.sql files, for the longer code.
--
-- by atp@arsdigita.com, 2000-07-17

-- Note that a lot (but not all) of this stuff is already covered by
-- the current version of Cassandracle, but I haven't made any attempt
-- to compare the two or pare down this list.


-- References:
--
-- Trezzo, Joseph C.; _Oracle PL/SQL Tips and Techniques_;
-- Osborne/McGraw-Hill, 1999, www.osborne.com; ISBN: 0-07-882439-9.
-- [Very useful book, though fat and poorly organized.]
--
-- http://www.tusc.com/tusc/nonhtml/plsql_bookcode.zip
-- Code from Trezzo's book.
--
-- Greenspun, Philip; "Tips for Using Oracle";
-- http://www.arsdigita.com/asj/oracle-tips


-- Useful Oracle shells:
-- $ORACLE_HOME/bin/sqlplus
-- $ORACLE_HOME/bin/svrmgrl
-- $ORACLE_HOME/bin/lsnrctl
-- At aD, do NOT use lsnrctl to restart the listener!  Instead, use:
--   sudo /etc/init.d/ora8-net8
-- If you do not, Oracle will not be able to find any external
-- libraries you may be using.  (Something to do with Environment
-- variables, but I haven't looked into it.)

-- Oracle Error Messgage Index:
--   http://oradoc.photo.net/ora816/server.816/a76999/index.htm

-- You probably want to put these in your login.sql file.
-- (When you start up sqlplus, it will execute any login.sql file it
-- find in your current directory.)
set linesize 200
set pagesize 1000
set serveroutput on size 1000000
begin dbms_output.enable(1000000); end;
/


-- To turn off interpretation of & in Sqlplus:
set def off

-- To retrieve a view definition from the database:
-- Sqlplus truncates long type columns, so do:
--
select VIEW_NAME, TEXT_LENGTH from user_views where view_name = upper('your_view_name_here');
--
-- Then, do "set long n" in Sqlplus.  Make sure n is >= TEXT_LENGTH.
set long 1000;
select TEXT from user_views where view_name = upper('your_view_name_here');


-- ** Regular Expressions **
-- The OWA_PATTERN pl/sql regexp package is part of the Oracle PL/SQL Web
-- Toolkit.  Install the whole toolkit with:
--   $ORACLE_HOME/webdb30/admin/plsql/owa/owains.sql
-- or read about OWA_PATTERN here:
--   http://technet.oracle.com/files/search/search.htm?OWA_PATTERN
--   $ORACLE_HOME/webdb30/admin/plsql/owa/pubpat.sql
--   $ORACLE_HOME/webdb30/admin/plsql/owa/privpat.sql


-- Find the table, column, etc. for a constraint.
select
 uc.constraint_name
,uc.table_name
,ucc.column_name
,uc.SEARCH_CONDITION
from user_constraints uc, user_cons_columns ucc
where uc.constraint_name = ucc.constraint_name
 and ucc.column_name = 'LIMIT_PRICE'
 --and uc.table_name in ('ORDERS','ORDERS_AUDIT')
;


-- Get list of invalid objects.
--   Trezzo c. pg. 207, 451
select
 STATUS ,OBJECT_TYPE ,CREATED ,LAST_DDL_TIME ,TIMESTAMP ,OBJECT_NAME
from user_objects
--from dba_objects
where status != 'VALID'
order by
 timestamp
 --object_name
;
 

-- Get list of disabled triggers.
--   Trezzo pg. 452
-- File: 9_5.sql
SELECT owner, trigger_name, trigger_type, triggering_event, table_owner||'.'||table_name
FROM dba_triggers
WHERE status <> 'ENABLED'
--  AND owner  = 'PLSQL_USER'
ORDER BY owner, trigger_name;



-- According to Xuequn (Robert) Xu xux@arsdigita.com 2000/12/01 in:
--   http://www.arsdigita.com/ticket/issue-view.tcl?msg_id=74326
-- The below queries do NOT show locks on packages.  However you can
-- check the v$access view like this:
select
username, osuser, process, type, terminal,
to_char(logon_time, 'YYYY-MM-DD HH24:MI') as logon_time,
(sysdate-logon_time)*24 as hours_ago
from v$session
order by hours_ago desc, process ;


-- Find Oracle locks:
--   Originally from Philip's Oracle tips document.
-- Usually very fast, but I've seen it hang on a really hosed Oracle
-- with hundreds of resource locks.
set linesize 180
 column username  format a18
 column program   format a32
 column machine   format a12
 column n_seconds format a6
select
 s.username ,s.sid ,s.serial#
 ,ltrim(w.seconds_in_wait) as n_seconds
 ,process ,machine ,terminal ,program
from  v$session s, v$session_wait w
where s.sid = w.sid
 and s.sid in (select sid from v$lock where lmode=6)
order by s.username ,s.sid ,s.serial#
;

-- alter system kill session 'SID,SERIAL#';

-- Find Oracle locks and what they're doing.
set linesize 180
 column username  format a18
 column program   format a32
 column machine   format a12
 column n_seconds format a6
select
 s.username ,s.sid ,s.serial#
 ,ltrim(w.seconds_in_wait) as n_seconds
 ,process ,machine ,terminal ,program
 ,sql.sql_text
from  v$session s, v$session_wait w
 ,v$sqltext sql
where s.sid = w.sid
 and s.sid in (select sid from v$lock where lmode=6)
 and sql.address    = s.sql_address
 and sql.hash_value = s.sql_hash_value
 and upper(username) like 'MUNIVERSAL%'
order by s.username ,s.sid ,s.serial# ,sql.piece
;

-- See which users are waiting for a lock.
-- This is fast.
SELECT s.username, s.serial#, s.lockwait
FROM   v$session s
where s.lockwait is not null
order by s.lockwait, s.username ;

-- Who's waiting for a lock, and what SQL is he executing?
-- This is fast.
SELECT s.username, s.serial#, s.lockwait
 ,sql.sql_text
FROM   v$session s
 ,v$sqltext sql
where s.lockwait is not null
 and sql.address    = s.sql_address
 and sql.hash_value = s.sql_hash_value
 and upper(username) like 'MUNIVERSAL%'
order by s.lockwait, s.username ,sql.piece ;




-- What are users currently doing?
set linesize 180
SELECT s.username, s.serial#, sql.sql_text
FROM   v$session s, v$sqltext sql
WHERE  sql.address    = s.sql_address
 AND  sql.hash_value = s.sql_hash_value
 --and upper(s.username) like 'USERNAME%'
order by s.username ,s.sid ,s.serial# ,sql.piece ;


-- What users are currently locked, and what are they doing?
-- Really slow, I never let it finish.
--   Trezzo pg. 475
-- File: 9_21.sql
SELECT s.username, s.serial#, l.id1, st.sql_text
FROM   v$session s, v$lock l, v$sqltext st
 -- It's the joining v$session to v$lock that seems to really slow things down.
WHERE  s.lockwait   = l.kaddr
AND    st.address    = s.sql_address
AND    st.hash_value = s.sql_hash_value;

-- What users are currently locked?
-- Really slow.
SELECT s.username, s.serial#, l.id1
FROM   v$session s, v$lock l
WHERE  s.lockwait   = l.kaddr;

-- Who is CAUSING a user to be locked?
-- Really slow - took almost 8 minutes to return "no rows selected".
--   Trezzo pg. 476
-- File: 9_22.sql
SELECT a.serial#, a.sid, a.username, b.id1, c.sql_text
FROM   v$session a, v$lock b, v$sqltext c
WHERE  b.id1 IN (
   SELECT DISTINCT e.id1
   FROM v$session d, v$lock e
   WHERE d.lockwait   = e.kaddr )
 AND     a.sid        = b.sid
 AND     c.hash_value = a.sql_hash_value
 AND     b.request    = 0;


-- Check init.ora parameters.
--   Trezzo pg. 453
-- File: 9_6.sql
SELECT name, value, isdefault, isses_modifiable, issys_modifiable
FROM v$parameter
ORDER BY name;

-- Check some particular DBMS_JOB related init.ora parameters.
--   Trezzo pg. 576
-- File: 12_9.sql
SELECT name, value, isdefault, isses_modifiable, issys_modifiable
FROM   v$parameter
WHERE UPPER(name) IN ('JOB_QUEUE_PROCESSES','JOB_QUEUE_INTERVAL')
ORDER BY NAME;


-- What database objects are being accessed right now?
-- Returns a potentially massive list of stuff.
--   Trezzo pg. 474
-- File: 9_20.sql
SELECT s.sid, s.username, a.owner, a.object, a.type
FROM   v$session s, v$access a
WHERE  s.sid = a.sid;


-- What statements are executing in each rollback segment?
--   Trezzo pg. 477
-- File: 9_23.sql
SELECT a.name, b.xacts tr, c.sid, c.serial#, c.username, d.sql_text
FROM   v$rollname a, v$rollstat b, v$session c,
      v$sqltext d,v$transaction e
WHERE  a.usn            = b.usn   
AND    b.usn            = e.xidusn
AND    c.taddr          = e.addr
AND    c.sql_address    = d.address
AND    c.sql_hash_value = d.hash_value
ORDER BY a.name, c.sid, d.piece;


-- Show all open cursors  :
column user_name format a15
column osuser    format a15
column machine   format a15
select user_name ,status ,osuser --,machine
 ,a.sql_text
from v$session b, v$open_cursor a
where a.sid = b.sid
order by status ,user_name ,osuser ,a.sql_text ;

-- Show all open cursors, with full text of SQL - slower:
select user_name ,status ,osuser --,machine
 ,c.sql_text
from v$session b, v$open_cursor a,  v$sqlarea c
where a.sid = b.sid
 and c.address = a.address ;
order by status ,user_name ,osuser ,c.sql_text ;

-- Show how many open cursors are currently in use:
select a.value, b.name
from v$mystat a, v$statname b
where a.statistic# = b.statistic#
 and a.statistic#= 3 ;


-- A list of some useful Oracle V$ views
--   Trezzo pg. 486


-- Jobs running - useful views:
--   Trezzo pg. 577
-- DBA_JOBS, USER_JOBS, DBA_JOBS_RUNNING

-- Submit and control jobs with DBMS_JOB.
--   Trezzo pg. 579 - 583

-- What jobs exist?
--   Trezzo pg. 578
-- File: 12_10.sql
SELECT what, job, priv_user,
      TO_CHAR(last_date, 'MM/DD/YYYY HH24:MI:SS') last,
      DECODE(this_date, NULL, 'NO', 'YES') running,
      TO_CHAR(next_date, 'MM/DD/YYYY HH24:MI:SS') next,
      interval, total_time, broken
FROM   dba_jobs
ORDER BY what;

-- What jobs are running right now?
--   Trezzo pg. 579
-- File: 12_11.sql
SELECT a.job, what,
      TO_CHAR(SYSDATE, 'mm/dd/yyyy hh24:mi:ss') now,
      TO_CHAR(a.this_date, 'mm/dd/yyyy hh24:mi:ss') this
FROM   dba_jobs_running a, dba_jobs b
WHERE  a.job = b.job;


-- Stuff about the Data Dictionary
--   Trezzo pg. 675 - 697


-- Go into svrmgrl, connect internal, and do these for your username,
-- so that you can run all the various queries above.  FYI, our Oracle
-- DBA says this is ok to do, to wit:
--
--     "Granting select privileges on dynamic performance views to
--     users is harmless. The "ad_cassandracle" role has almost all of
--     the read privileges on system tables and data dictionary
--     views. You can certainly do the same for [our ACS Oracle user
--     name]."

grant select on dba_objects      to your_oracle_username;
grant select on dba_triggers     to your_oracle_username;
grant select on dba_jobs_running to your_oracle_username;
grant select on dba_jobs         to your_oracle_username;
grant select on dba_dependencies to your_oracle_username;
grant select on v_$parameter     to your_oracle_username;
grant select on v_$session       to your_oracle_username;
grant select on v_$session_wait  to your_oracle_username;
grant select on v_$access        to your_oracle_username;
grant select on v_$lock          to your_oracle_username;
grant select on v_$sqltext       to your_oracle_username;
grant select on v_$rollname      to your_oracle_username;
grant select on v_$rollstat      to your_oracle_username;
grant select on v_$sqltext       to your_oracle_username;
grant select on v_$transaction   to your_oracle_username;
grant select on SYS.FILEXT$      to muniversaldev;
grant select on DBA_FREE_SPACE   to muniversaldev;
grant select on DBA_DATA_FILES   to muniversaldev;
grant select on SYS.DBA_SOURCE   to muniversaldev;


-- Data Dictionary Tables about Columns:
--user_coll_types
--user_tab_columns
--user_clu_columns
--user_col_comments
--user_col_privs
--user_cons_columns
--user_ind_columns
--user_tab_col_statistics
--user_trigger_cols
--user_updatable_columns

connect internal;
-- Grant a user read privileges on all tables in the database:
grant exp_full_database to username;
-- Grant read access to all v$ views, etc.:
grant select_catalog_role to username;


-- Note that you can use bind variables in pl/sql.  This makes using
-- queries out of an ACS 4.x AOLserver error log much easier.  E.g.:
variable user_id         number
variable db_code         varchar2(20)
variable db_access_level varchar2(20)
variable proteome_code   varchar2(20)
variable ip_address      varchar2(20)
variable hostname        varchar2(50)
begin
 :user_id         := 95321;
 :db_code         := 'human';
 :db_access_level := 'lite';
 :proteome_code   := 'proteome';
 :ip_address      := '207.46.131.91';
 :hostname        := 'microsoft.com';
end;
/
show errors


-- Here's how to escape the wildcard meanings of _ with like in:
select object_type  from acs_objects  where object_type like 'p\_%'  escape '\';


-- Heres something out of the BBoard, to find all contraints
-- relationships:
--
-- make a table containing on row per relationship
create table tmp_relations as
select
 c1.table_name parent,
 c1.constraint_name pk,
 c2.table_name child,
 c2.constraint_name fk,
 c2.r_constraint_name parent_pk
from
 user_constraints c1,
 user_constraints c2
where c1.constraint_type = 'P'
 and c2.constraint_type = 'R'
 and c2.r_constraint_name = c1.constraint_name
;
-- do a tree query to get complete depth
select child, level  from tmp_relations
start with parent = 'ACS_OBJECTS'
connect by parent = PRIOR child ;