UnCommitted Read in Oracle

There are cases where we would like to find out as to how many rows are processed by a long running SQL query. I have experienced many times where a long running query is still executing and I wanted to know as to how much percentage is complete, to decide to terminate or let the query continue to drag to completion. There are few ways to determine the completion status or the number of rows processed by the long running queries.

From TOAD
In TOAD, open the session browser. (an icon on the top right corner with a lens symbol). Select the session that is executing the query. This can be identified using the userid and the tool from where the query is executed. Navigate to the longops tab and it will list the percentage completion of the query.

Using V$SQLQREA
This approach need to be used when the long running query is a select query and no writes are happening on the database using this query. Here, we use the below query to find the number of rows processed for the sql address and hash value

SELECT ROWS_PROCESSED,sql_text
FROM v$sqlarea
WHERE (address, hash_value) IN
(SELECT sql_address, sql_hash_value
FROM v$session
WHERE sid = &1)
Note: The sid can be obtained by querying the v$session table using the details available in hand like login userid, server name, logon timestamp etc.

Also more details about the query on an aggregate can be obtained by querying the v$sql table. The address of the current SQL that is getting executed can be obtained from the v$session table.

Select * from v$sql where sql_id=? (obtain the sqlid from the v$session table against the corresponding session)

Using UNDO Segments

This approach can be applied where the sql query is performing a DML operation. The data gets written to the UNDO segment and the below query refers to the UNDO segments to get the details.

select a.sid,
a.username,b.xidusn undoseg,
b.used_urec totrecords,
b.used_ublk totblocks,
to_char(sysdate,'mmdd hh24:mi:ss') currtime
from v$session a,
v$transaction b
where a.saddr=b.ses_addr and a.sid=&1;

Sequence NoCache Significance

It’s kind of usual habit to use sequence values for the primary key of a table. In this approach, what you do is to create a sequence and then access the sequence like mysequence.nextval in the insert statement. In this blog, I’m trying to explain the importance of the CACHE value of the sequence create command.

A sequence can be created using the below 2 commands, one with cache and another without cache.

CREATE SEQUENCE

MY_SEQUENCE_PK

INCREMENT BY 1

START WITH 1

NOCYCLE

NOCACHE

NOORDER;

CREATE SEQUENCE

MY_SEQUENCE_PK

INCREMENT BY 1

START WITH 1

NOCYCLE

CACHE 1000

NOORDER;

How does CACHE work:

When the sequence.nextval is invoked on a sequence created with NOCACHE, an update query fired to set the current value to the sequence in the system tables, apart from the select query. If the sequence is accessed 1000 times, then the update query is also fired 1000 times.

When the sequence.nextval is invoked on a sequence created with CACHE 1000, an update query is fired only once and it will set the current value to a number incremented by 1000. The sequence values will be in the buffer cache and the insert query can use those values. Gain is that we save 1000 update queries while inserting data into the table.

Where this should be used:

The CACHE value can be used, for sure, in data migration cases where large number of records is inserted into the database.

Where this should not be used:

1. You should use the cache pretty much everywhere.
2. If the sequence is seldom used, then cache value need not be provided. For e.g, primary key for a Airplane Model table. New Models doesn’t get added daily and hence the access to this sequence would be very limited.
3. If you need almost continuous values from the sequence and it’s a multi user system, then probably cache should not be used. However, this would cost performance of the statements that use nextval call.