Monday, 26 March 2018

Wait Events : checkpoint busy waits or archiver busy waits

While such wait events occur  in AlertLog file, one must consider to proceed with ARCHIVER TUNING

1. Check the number of Online Redolog Members and size of the online redo logs. 
Excessive size and the number of online redo log groups will give archiver more time to catch up. Hence Adding more online logs does not help a situation where the archiver cannot keep up with LGWR process.
It can help if there are bursts of redo generation since it gives ARCH more time to average its processing rate over time.

2. In such cases you can add multiple archiver (ARCh) processes
Create 'alter system archive log all'. This will spawn archive processes at some fixed interval may be required. These processes once spawned will assist archiver in archiving any un-archived log in that thread of redo. Once it has been completed, the temporary processes will go away.

3. Evaluate checkpoint interval and frequency
There are several possible actions include adding DBWR processes,  increasing db_block_checkpoint_batch, reducing db_block_buffers. Turning on or allowing async IO capabilities definitely helps alleviate most DBWR inefficiencies.

4. Check OS supportability of asynchronous I/Os
Async reads should help tremendously. Async writes may help if OS supports asynchronous I/Os on file systems.
You can check with your vendor if the current version of your operating system supports async IO to file systems (ufs).

5. Check for system or IO contention.
Check CPU waits and usage, disk  level bottlenecks. Also check operating system manuals for the appropriate commands to monitor system performance.
For example, you can use UNIX  commands such as "sar  5 5 5"  “sar –d ”or "iostat" to identify disk bottlenecks.

Sunday, 11 March 2018

Accessing a schema without knowing the password

Most of the times that you may need to logon to a database user / schema owner, to do so emergency maintenance,,  but you don’t know the password.

There is an alternative where you can use alter session set current schema “Schema-Name” ;

Other than this you can use the below process, where you record the current password encryption, change the password, logon and do your maintenance.

Let’s try an example here

Create an account:

SQL> conn / as sysdba



SQL>  create user nikhil identified by mypass1 ;

User created.

SQL> grant connect , resource to nikhil ;

Grant succeeded.


Now if I want to change the password, then I should know the ‘Old Password’

SQL> conn nikhil/mypass1 ;

SQL> password 

Changing password for NIKHIL

Old password:


In such cases you can use the user$ view under sys user which will give us the encrypted password so that we will preserve the old password
Let’s see..

SQL> conn / as sysdba


SQL> select name,'alter user '||name||' identified by values '''||spare4||';'||password||''';' command from sys.user$ where name = 'NIKHIL';






alter user NIKHIL identified by values 'S:2549CDA4335FCEF7814FD9832AD653A937AAE8


Now I will set a temporary password for the account to perform the activity.. Later will set the old password using this encrypted values.

SQL> alter user nikhil identified by demopassword ;

User altered.

SQL> conn nikhil/demopassword ;


Now connect as sysdba,  and revert the password using the encrypted values

SQL> conn / as sysdba


SQL> alter user NIKHIL identified by values 'S:2549CDA4335FCEF7814FD9832AD653A937AAE87105AB962A873A59A576E8;FD135DE4875002BA';

User altered.

SQL> conn nikhil/mypass1 ;



Alternatively, we can use the DBMS_METADATA package to get the encryption;

SQL> set long 10000

select dbms_metadata.get_ddl('USER','NIKHIL') command from dual;SQL>







Friday, 26 January 2018

Find sessions performing sort operations : TEMP Tablespace occupants

Most of the time, user will ask you to check long running sessions in database and you will figure out the query which the session is performing sort/join operation – reading the query you can say the query is related to sorting. But  you will also see the high usage of Temporary segments or will see wait events such as “Direct Path Temp Read” Or “Direct Path Temp Write”.

To rectify this you can use views such as  V$SORT_USAGE and V$SORT_SEGMENT to identify the other sessions performing sort operation

Oracle provides two basic dynamic performance views for online monitoring of temporary tablespace usage.

These are v$sort_usage to query online sessions using temporary segments and v$sort_segment to query the size of the temporary segments in the database.

V$SORT_USAGE has below main columns  which can be useful here:

USERNAME       –              Session User name.
SQL_ID                –              unique identifier of the SQL that requires the sort.
EXTENTS           –              number of extents in the temporary segment being used by particular sessions.
BLOCKS              –              number of blocks in the temporary segment being used by particular sessions.
SESSION_ADDR – address of the session, can be used to identify the session in V$SESSION according to the SADDR column.

--Use below query to find the sessions consuming temp segments

SELECT S.sid || , || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace ORDER BY sid_serial;

This will give the sid and serial# which be used to find current query that is taking temp segment using view v$sqltext and passing sql_id of the sid and serial#


SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total-SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
( SELECT, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts# GROUP BY, C.block_size ) D WHERE A.tablespace_name =
GROUP by A.tablespace_name, D.mb_total;