Dynamic Performance Examples

Dynamic Performance

• V$CONTROLFILE

• V$DATABASE

• V$DATAFILE

• V$INSTANCE

• V$PARAMETER

• V$SESSION

• V$SGA

• $SPPARAMETER

• V$TABLESPACE

• V$THREAD

• V$VERSION

Examples

• V$CONTROLFILE: Lists the names of the control files

• V$DATABASE: Contains database information from the control file.

• V$DATAFILE: Contains data file information from the control file

• V$INSTANCE: Displays the state of the current instance

• V$PARAMETER: Lists parameters and values currently in effect for the session

• V$SESSION: Lists session information for each current session

• V$SGA: Contains summary information on the system global area (SGA)

• V$SPPARAMETER: Lists the contents of the SPFILE

• V$TABLESPACE: Displays tablespace information from the control file

• V$THREAD: Contains thread information from the control file.

• V$VERSION: Version numbers of core library components in the Oracle server

More detail of examples:

1.The V$FIXED_TABLE view can also be queried to get a listing of the dynamic performance views:

SQL> SELECT * FROM V$FIXED_TABLE;

2.To get an overview of the data dictionary views, the DICTIONARY view or its synonym

DICT can be queried.

SQL> SELECT * FROM dictionary;

Include the WHERE clause to narrow your responses:

SQL> SELECT * FROM dictionary

2 WHERE table_name LIKE ‘DBA_SEG%’

3.To view the contents of the view, use the SELECT command.

SQL> SELECT * from V$INSTANCE;

4.To get a list of columns within a view, use the DESCRIBE keyword:

SQL> DESCRIBE V$INSTANCE; 

Data Dictionary Views in Oracle

Data Dictionary Views in Oracle

Oracle data dictionary views will start with any one of 3 character strings i.e. starts with DBA_ or ALL_ or USER_

 DBA: What is in all the schemas
ALL: What the user can access
USER: What is in the user’s schema


DBA_ views --> All information and objects in the Oracle data dictionary. These will be accessible by users having DBA privileges.

ALL_ views --> All information and objects accessible by a user.

USER_ views --> All information and objects owned by a user.

Steps to create database using DBCA tool

Steps to create database using DBCA tool

1) Open a terminal and invoke DBCA




2) Click next to proceed further




3) Click create database and then click next to proceed further




4) Click general purpose and then click next to proceed further

5) Give database name and then click next to proceed further

6) To configure Enterprise Manager tick the checkbox and then click next to proceed further

7) Give the password for the following users and then click next to proceed further

or

8) Select file system and then click next to proceed further

9) Select the location to create database and then click next to proceed further

10) Tick checkbox of flash recovery area and to put database in archivelog mode Tick the checkbox of enable archiving and then click next to proceed further

11) Tick checkbox of sample schema to create some default users for testing purpose and then click next to proceed further

12) Select custom to alter sga size and pga size and then click sizing tab to proceed further

13) You can alter number of process according to your requierment and then click Character set to proceed further

14) Click connection mode to proceed further

15) Select network connection mode and then click next to proceed further

Select shared server mode

Select edit shared server parameter

Select number of dispatcher

16) Select ok to proceed further

17) Database creation will take place

18) Click exit to proceed further

Sql queries to check ACTIVE / INACTIVE Sessions

Sql queries to check ACTIVE / INACTIVE Sessions:

1.Total Count of sessions:

select count(s.status) TOTAL_SESSIONS
from gv$session s;

2.Total Count of Inactive sessions:

select count(s.status) INACTIVE_SESSIONS
from gv$session s, v$process p
where
p.addr=s.paddr and
s.status='INACTIVE';

3.Sessions Which are in Inactive Status From More Than 1Hour:

select count(s.status) "INACTIVE SESSIONS > 1HOUR "
from gv$session s, v$process p
where
p.addr=s.paddr and
s.last_call_et > 3600 and
s.status='INACTIVE';

4.Count Of Active Session:

select count(s.status) ACTIVE_SESSIONS
from gv$session s, v$process p
where
p.addr=s.paddr and
s.status='ACTIVE';

5.Total Sessions Count Ordered By Program:

col program for a30
select s.program,count(s.program) Total_Sessions
from gv$session s, v$process p
where  p.addr=s.paddr
group by s.program;

6.Total Count Of Session Ordered By Moudle:

col module  for a30
prompt TOTAL SESSIONS
select s.module,count(s.sid) Total_Sessions
from gv$session s, v$process p
where  p.addr=s.paddr
group by s.module;

7.Total Count Of Sessions Ordered By Action:

col action for a30
prompt TOTAL SESSIONS
select s.action,count(s.sid) Total_Sessions
from gv$session s, v$process p
where  p.addr=s.paddr
group by s.action;

8.Inactive Sessions:

prompt INACTIVE SESSIONS
select p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program
from gv$session s, v$process p
where
p.addr=s.paddr and
s.status='INACTIVE';

9.Inactive:

prompt INACTIVE SESSIONS
select count(s.status) INACTIVE
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE';

10.Inactive Programs:

col module for a40            
prompt INACTIVE SESSIONS
col INACTIVE_PROGRAMS FOR A40
select distinct (s.program) INACTIVE_PROGRAMS,s.module
from gv$session s, v$process p
where  p.addr=s.paddr and
s.status='INACTIVE';

11.Inactive Programs with disk reads:

prompt INACTIVE SESSIONS
select distinct (s.program) INACTIVE_PROGRAMS,SUM(T.DISK_READS)
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
GROUP BY S.PROGRAM;

12.Inactive  Session Count With Program:

col program for a30
prompt TOTAL INACTIVE SESSIONS
col INACTIVE_PROGRAMS FOR A40
select s.program,count(s.program) Total_Inactive_Sessions
from gv$session s,v$process p
where     p.addr=s.paddr  AND
s.status='INACTIVE'
group by s.program
order by 2 desc;

13.Total  Inactive Sessions More Than 1Hour:

col program for a30
col INACTIVE_PROGRAMS FOR A40
select s.program,count(s.program) Inactive_Sessions_from_1Hour
from gv$session s,v$process p
where     p.addr=s.paddr  AND
s.status='INACTIVE'
and s.last_call_et > (3600)
group by s.program
order by 2 desc;

14.Total Inactive Sessions Group By  Module:

col program for a60
COL MODULE FOR A30
prompt TOTAL SESSIONS
col INACTIVE_PROGRAMS FOR A40
select s.module,count(s.module) Total_Inactive_Sessions
from gv$session s,v$process p
where     p.addr=s.paddr  AND
s.status='INACTIVE'
group by s.module;

15.Inactive Session Details More Than 1 Hour:

set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et_Hrs for 9999.99
col sql_hash_value for 9999999999999col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a60
col elapsed_time for 999999999999
select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.last_call_et > (3600)
order by last_call_et;

16.Inactive Program  --Any--

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
And s.program='&PROGRAM_NAME'
order by last_call_et;

17.Inactive Modules  --Any-

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr
And s.module like '%order_cleanup_hazmat_v3.sql'
order by last_call_et;

18.Inactive Jdbc Session:

set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
col sql_hash_value for 9999999999999col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a60
col elapsed_time for 999999999999
select p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,
s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.program='JDBC Thin Client'
and s.last_call_et > 3600
order by last_call_et;

19.Count OF Inactive Sessions More Than One Hour:

SELECT COUNT(P.SPID)
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.program='JDBC Thin Client'
and s.last_call_et > 3600
order by last_call_et;

FORMS

20.Total Form Sessions:

SELECT COUNT(S.SID) INACTIVE_FORM_SESSIONS FROM V$SESSION S
WHERE S.STATUS='INACTIVE' and
s.action like ('%FRM%');

21.Forms Sessions Details:

col "Last SQL" for a30
select p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,
s.sid,t.disk_reads, t.elapsed_time,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.action like ('FRM%') and
s.last_call_et > 3600
order by spid;                    


col machine for a15
col "Last SQL" for a30
select p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,
S.ACTION,s.process Client_Process,s.machine
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.action like ('FRM%') and
s.last_call_et > 3600;        
order by 4;                          

22.Inactive Forms Sessions Details:

col program for a15
col last_call_et for 999.99
select p.spid, s.sid, s.process,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like 'FRM:%'
and s.last_call_et > 3600
order by last_call_et desc;

23.Unique SPID:

select unique(p.spid)
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like 'FRM:%'
and s.last_call_et > 3600;

24.Count Forms:

select COUNT(p.spid)
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like 'FRM:%'
and s.last_call_et > 3600;

25.Zero Hash Value:

select COUNT(p.spid)
from gv$session s,gv$process p
where
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like 'FRM:%'
and s.last_call_et > 3600
AND S.SQL_HASH_VALUE=0;

26.Inactive Form By Name:

select count(s.sid) from v$session S
where s.action like ('%&ACTION%')
AND S.STATUS='INACTIVE';

27.Group By Action:

SELECT S.ACTION,COUNT(S.SID) FROM V$SESSION S
WHERE S.STATUS='INACTIVE' and
s.action like ('%FRM%')
group by s.action;

28.From A Specfic Username:

SET LINSIZE 152
col spid for a10
col process_spid for a10
col user_name for a20
col form_name for a20
select a.pid,a.spid,a.process_spid, c.user_name,to_char(a.start_time,'DD-MON-YYYY HH24:MI:SS') "START_TIME" ,
d.user_form_name "FORM_NAME"
from apps.fnd_logins a, apps.fnd_login_resp_forms b, apps.fnd_user c,
apps.fnd_form_tl d
where
a.login_id=b.login_id
and c.user_name like 'JROMO'
and a.user_id=c.user_id
and trunc(b.start_time) >trunc(sysdate -11)
and trunc(b.end_time) is null
and b.form_id=d.form_id
and d.language='US';

29.Inactive Form:

set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
col sql_hash_value for 9999999999999col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a30
col elapsed_time for 999999999999
select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like ('FRM%')
and s.last_call_et > (3600*3)
order by last_call_et;


30.Inactive Form Sessions: 

col cli_proc for a9
COL AUDSID FOR A6
COL PID FOR A6
COL SID FOR A5
COL FORM_NAME FOR A25
COL USER_NAME FOR A15
col last_call_et for 9999.99
SELECT
-- /*+ ORDERED FULL(fl) FULL(vp) USE_HASH(fl vp) */
( SELECT SUBSTR ( fu.user_name, 1, 20 )
FROM apps.fnd_user fu
WHERE fu.user_id = fl.user_id
) user_name,vs.status,
TO_CHAR ( fl.start_time, 'DD-MON-YYYY HH24:MI' ) login_start_time,
TO_CHAR ( fl.end_time, 'DD-MON-YYYY HH24:MI' ) login_end_time,
vs.last_call_et/3600 last_call_et,
SUBSTR ( fl.process_spid, 1, 6 ) spid,
SUBSTR ( vs.process, 1, 8 ) cli_proc,
SUBSTR ( TO_CHAR ( vs.sid ), 1, 3 ) sid,
SUBSTR ( TO_CHAR ( vs.serial#), 1, 7 ) serial#,
SUBSTR ( TO_CHAR ( rf.audsid ), 1, 6 ) audsid,
SUBSTR ( TO_CHAR ( fl.pid ), 1, 3 ) pid,
SUBSTR ( vs.module || ' - ' ||
( SELECT SUBSTR ( ft.user_form_name, 1, 40 )
FROM apps.fnd_form_tl ft
WHERE ft.application_id = rf.form_appl_id
AND ft.form_id        = rf.form_id
AND ft.language       = USERENV('LANG')
), 1, 40 ) form_name
FROM apps.fnd_logins           fl,
gv$process            vp,
apps.fnd_login_resp_forms rf,
gv$session            vs
WHERE fl.start_time   > sysdate - 7 /* login within last 7 days */
AND fl.login_type   = 'FORM'
AND fl.process_spid = vp.spid
AND fl.pid          = vp.pid
AND fl.login_id     = rf.login_id
AND rf.end_time    IS NULL
AND rf.audsid       = vs.audsid
and vs.status='INACTIVE'
ORDER BY
vs.process,
fl.process_spid;

Active

prompt ACTIVE SESSIONS
select count(s.status) ACTIVE
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='ACTIVE';

Moudule

set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
col sql_hash_value for 9999999999999col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a30
col elapsed_time for 999999999999
select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr
and s.MODULE like ('&MODULE_NAME_1HR%')
and s.last_call_et > ('&TIME_HRS' * 3600)
order by last_call_et;

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
p.addr=s.paddr
and s.MODULE like ('%TOAD%')
Order by last_call_et;

Toad Sessions:

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program
from gv$session s, gv$process p
where
p.addr=s.paddr
and s.MODULE like ('%TOAD%')
Order by last_call_et;

Client Machine Sessions Count:

select count(s.process) TOTAL from v$session S
where s.machine like ('%&CLIENT_MACHINE%');

select count(s.process) INACTIVE from v$session S
where s.machine like ('%&CLIENT_MACHINE%')
and s.status='INACTIVE';

hash value=0

select count(s.process) from v$session S
where s.machine like ('%&CLIENT_MACHINE%')
AND S.SQL_HASH_VALUE=0;

select count(s.process) from v$session S
where s.machine like ('%&CLIENT_MACHINE%')
AND S.SQL_HASH_VALUE=0
AND S.LAST_CALL_ET > 3600;

Unique Actions

col module for a40            
prompt INACTIVE SESSIONS
col INACTIVE_PROGRAMS FOR A40
select distinct (s.program) INACTIVE_PROGRAMS,s.module
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
s.machine like ('%&CLIENT_MACHINE%') AND
p.addr=s.paddr and
s.status='INACTIVE';

GROUP BY  program

col program for a60
prompt TOTAL SESSIONS
col INACTIVE_PROGRAMS FOR A40
select s.program,count(s.program) Total_Inactive_Sessions
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr  AND
s.machine like ('%&CLIENT_MACHINE%') AND
s.status='INACTIVE'
group by s.program;

MySQL Commands

MySQL Commands 

1.To login (from unix shell) use -h only if needed.

# [mysql dir]/bin/mysql -h hostname -u root -p

2.Create a database on the sql server.

mysql> create database [databasename];

3.List all databases on the sql server.

mysql> show databases;

4.Switch to a database.

mysql> use [db name];

5.To see all the tables in the db.

mysql> show tables;

6.To see database's field formats.

mysql> describe [table name];

7.To delete a db.

mysql> drop database [database name];

8.To delete a table.

mysql> drop table [table name];

9.Show all data in a table.

mysql> SELECT * FROM [table name];

10.Returns the columns and column information pertaining to the designated table.

mysql> show columns from [table name];

11.Change a users password from unix shell.

# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password'

12. Update database permissions/privilages.

mysql> flush privileges;

13.Delete a column.

mysql> alter table [table name] drop column [column name];

14.Describe the fields (columns) of the "products" table.

mysql> DESCRIBE products;

15.create a new MySQL table:

CREATE TABLE potluck (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
food VARCHAR(30),
confirmed CHAR(1),
signup_date DATE);

Displaying Control File Information:

Displaying Control File Information:

The following views display information about control files:


1.V$DATABASE:
Displays database information from the control file.

2.V$CONTROLFILE:
Lists the names of control files.

3.V$CONTROLFILE_RECORD_SECTION:
Displays information about control file record sections.

4.V$PARAMETER:
Can be used to display the names of control files as specified in the CONTROL_FILES initialization parameter.

SCN Based Recovery

SCN Based Recovery

1.On the new physical standby, determine the current scn:

SQL> SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE;

CURRENT_SCN
-----------
    900117
   
2.On the new primary database, create an incremental backup based on the current SCN of the standby and a new standby control file:
Connect to rman :
% rman target /
RMAN> backup incremental from scn 900117 database format '/home/oracle/u01/%U';
RMAN> backup device type disk format '/home/oracle/u01/%U' current control file for standby;   

3.Transfer all backup pieces generated by the above command to the standby host.

% scp /home/oracle/u01/04m2vv18_1_1:`pwd`

4.Connect to the new physical standby as the RMAN target to do the following:
restore the standby controlfile,
catalog the standby datafiles and backup pieces,
and switch the standby controlfile to the standby datafile copies

% rman target /
RMAN> startup nomount force;
RMAN> restore standby controlfile from '/home/oracle/u01/0hm30a85_1_1';
RMAN> alter database mount;
RMAN> catalog start with '+DATA/titans_stby';
RMAN> switch database to copy;
RMAN> recover database noredo;

5.Restart managed recovery.
SQL> recover managed standby database using current logfile disconnect;

Wait events in Oracle:

Wait events in Oracle:
  
When Oracle executes an SQL statement, it is not constantly executing. Sometimes it has to wait for a specific event to happen before it can proceed.

For example, if Oracle (or the SQL statement) wants to modify data, and the corresponding database block is not currently in the SGA, Oracle waits for this block to be available for modification.

All possible wait events can be found in v$event_name. In Oracle 10g R1, there are some 806 different wait events.
What Oracle waits for and how long it has totally waited for these events can be monitored through the following views:

v$session_event
v$session_wait
v$system_event


1.Buffer Busy waits/Cache Buffers Chains Latch waits:

This wait happens when a session wants to access a database block in the buffer cache but it cannot as the buffer is "busy".

The two main cases where this can occur are:
1.Another session is reading the block into the buffer
2.Another session holds the buffer in an incompatible mode to our request.

2."Read By Other Session" wait event.

When user sessions request for data, Oracle will first read the data from disk into the database buffer cache. If two or more sessions request the same data, the first session will read the data into the buffer cache while other sessions wait. In previous versions, this wait was classified under the "buffer busy waits" event. However, in Oracle 10g and higher, this wait time is now broken out into the "read by other session" wait event.

3.Log File Sync waits:

Log file sync waits occur when sessions wait for redo data to be written to disk.
Typically this is caused by slow writes or committing too frequently in the application.

4. Db file sequential read

Wait for an I/O read request to complete. A sequential read is usually a single-block read. This differs from "db file scattered read" in that a sequential read reads data into contiguous memory. (whilst a scattered read reads multiple blocks and scatters them into different buffers in the SGA).

5.Db file scattered read


The process has issued an I/O request to read a series of contiguous blocks from a data file into the buffer cache, and is waiting for the operation to complete. This typically happens during a full table scan or full index scan.

6.Direct path read
The process has issued asynchronous I/O requests that bypass the buffer cache, and is waiting for them to complete. These wait events typically involve sort segments.

7.Db file parallel write
The process, typically DBWR, has issued multiple I/O requests in parallel to write dirty blocks from the buffer cache to disk, and is waiting for all requests to complete.

8.Log file sync :
The process is waiting for LGWR to finish flushing the log buffer to disk. This occurs when a user commits a transaction.
(A transaction is not considered committed until all of the redo to recover the transaction has been successfully written to disk.)

9.Enqueue:
The process is waiting on an enqueue (a lock you can see in v$lock).
This commonly occurs when one user is trying to update a row in a table that is currently being updated by another user.

10.Log file parallel write :
The process is waiting for blocks to be written to all online redo log members in one group.
LGWR is typically the only process to see this wait event. It will wait until all blocks have been written to all members.

TOP 10 SQL QUERIES

 The top 10 sql queries which consuming high IO, CPU's. in oracle db. 
 I am getting the sql query by applying the hash value in v$sqlarea. The queries are below.
Really it is very helpful for daily activity.
1.Top sql command:

SQL> select c.* from
       (select disk_reads,
          buffer_gets,
                   rows_processed,
               executions,
               first_load_time,
          sql_text
              from v$sqlarea
          where parsing_user_id !=0
          order by
             buffer_gets/decode(executions,null,1,0,1,executions) desc ) c
   where rownum < 11;
  
2..--Top 10 by Buffer Gets:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
        buffer_gets, executions, buffer_gets/executions "Gets/Exec",
        hash_value,address
   FROM V$SQLAREA
  WHERE buffer_gets > 10000
 ORDER BY buffer_gets DESC)
WHERE rownum <= 10
;
3...-Top 10 by Physical Reads:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
        disk_reads, executions, disk_reads/executions "Reads/Exec",
        hash_value,address
   FROM V$SQLAREA
  WHERE disk_reads > 1000
 ORDER BY disk_reads DESC)
WHERE rownum <= 10;

4. Top 10 by Executions:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
        executions, rows_processed, rows_processed/executions "Rows/Exec",
        hash_value,address
   FROM V$SQLAREA
  WHERE executions > 100
 ORDER BY executions DESC)
WHERE rownum <= 10;
  
5. Top 10 by Parse Calls:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
        sharable_mem, executions, hash_value,address
   FROM V$SQLAREA
  WHERE sharable_mem > 1048576
 ORDER BY sharable_mem DESC)
WHERE rownum <= 10;
6. Top 10 by Version Count:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
        version_count, executions, hash_value,address
   FROM V$SQLAREA
  WHERE version_count > 20
 ORDER BY version_count DESC)
WHERE rownum <= 10;

7. Long running sql:

set lines 200
col opname for a35
col progress for a15
col progress for a8
col TARGET for a45
col USERNAME for a8
select a.sid,a.serial#,b.username,b.opname,LAST_UPDATE_TIME,round(b.SOFAR*100 / b.TOTALWORK,2) || '%' as progress,
b.TIME_REMAINING,b.target from gV$SESSION_LONGOPS b,gV$SESSION a where a.sid=b.sid and TIME_REMAINING <> 0 order by 6;

alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
set lines 200
set pages 200
col opname for a35
col progress for a15
col progress for a8
col TARGET for a45
select distinct a.inst_id,a.sid,a.serial#,b.username,b.opname,a.logon_time,round(b.SOFAR*100 / b.TOTALWORK,2) || '%' as progress,
b.TIME_REMAINING,b.target from gV$SESSION_LONGOPS b,gV$SESSION a where a.inst_id=b.inst_id and a.sid=b.sid and TIME_REMAINING <> 0 order by a.inst_id,logon_time  ;

Three dynamic performance views of wait events

Three dynamic performance views of wait events:

V$SESSION_WAIT :-
It displays the events for which sessions have just completed waiting or are currently waiting.

V$SYSTEM_EVENT  :_
It displays the total number of times all the sessions have waited for the events in that view.

V$SESSION_EVENT-:
 It is similar to V$SYSTEM_EVENT, but displays all waits for each session.

Lock Script

Hii All

 Today i'm posting  lock script. sometime some session block to other session. This situation is very critical because our whole system is impacted. so this script is very helpful to us.

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 ;

Note-- The above script inform us to locking detail and sid detail.

select  p.spid,s.sid,s.serial#,s.p1,s.p1text,s.username,s.status,s.last_call_et,p.program,p.terminal,s.logon_time,s.module,s.osuser,l.SQL_TEXT,w.event,w.SECONDS_IN_WAIT
from V$process p,V$session s,v$sql l,v$session_wait w
where s.paddr = p.addr
and s.sid=w.sid
and l.SQL_ID=s.SQL_ID
and l.HASH_VALUE=s.SQL_HASH_VALUE
and l.ADDRESS=s.SQL_ADDRESS
and s.sid=&SID
order by  SECONDS_IN_WAIT;


Note- The above script inform us to sql detail.


If you want to grep more detail corresponding this session you can check with the help of  v$session. From this command  we can  check long running sql.

If suppose session is idle long time you can kill the session also. After that lock is automatically cleared.

SQL> Alter system kill session 'Sid&serial';

Initialization Parameters for ASM Instances

Initialization Parameters for ASM Instances

 Parameters that start with ASM_ cannot be set in a normal database instance, although they will be present to the show parameter command.

1.Instance_type:

The INSTANCE_TYPE initialization parameter must be set to Oracle ASM for an Oracle ASM instance. This parameter is optional for an Oracle ASM
instance in an Oracle grid infrastructure home.The default is RDBMS.

The following is an example of the INSTANCE_TYPE parameter in the initialization file:

INSTANCE_TYPE = ASM

2.Db_unique_name:

This parameter specifies the unique name for this group of ASM instances within the cluster or on a node. This parameter defaults to +ASM and
only needs to be modified if you are trying to run multiple ASM instances on the same node.

3.ASM_POWER_LIMIT:

The ASM_POWER_LIMIT initialization parameter specifies the default power for disk rebalancing. The default value is 1 and the range of allowable
values is 0 to 11 inclusive. A value of 0 disables rebalancing. Higher numeric values enable the rebalancing operation to complete more quickly,
but might result in higher I/O overhead.

4.Asm_diskgroups:

The ASM_DISKGROUPS initialization parameter specifies a list of the names of disk groups that an Oracle ASM instance mounts at startup.
Oracle ignores the value that you set for ASM_DISKGROUPS when you specify the NOMOUNT option at startup or when you issue the ALTER DISKGROUP
ALL MOUNT statement. The default value of the ASM_DISKGROUPS parameter is a NULL string.The ASM_DISKGROUPS parameter is dynamic.

The following is an example of setting the ASM_DISKGROUPS parameter dynamically:

SQL> ALTER SYSTEM SET ASM_DISKGROUPS = DATA, FRA;

The following is an example of the ASM_DISKGROUPS parameter in the initialization file:

ASM_DISKGROUPS = DATA, FRA

5.Asm_diskstring:

The purpose of this parameter is to limit the set of disks that Automatic Storage Management considers for discovery.
If not specified the entire system is searched for compatible volumes which can lengthen startup time. The parameters default value is NULL.

(This default causes ASM to find all of the disks in a platform-specific location to which it has read/write access.)

ASM Background Processes

ASM Background Processes 

Like normal database instances ASM instance too have the usual background processes like SMON, PMON, DBWr, CKPT and LGWr.
In addition to that the ASM instance also have the following background processes.

1.RBAL-
Opens all device files as part of discovery and coordinate the rebalance activity.

2.ARBx -
These are the slave processes that do the rebalance activity.

3.GMON-
 This process is responsible for managing the disk-level activities(drop/off-line) and advancing disk group compatibility.

4.MARK-
The Mark Allocation Unit (AU) for Re sync Coordinator (MARK)process coordinates the updates to the Staleness Registry when the disks go off-line.
This process runs in the RDBMS instance and is started only when disks go off-line in ASM redundancy disk groups.

5.Onnn-
One or more slave process forming a pool of connection to the ASM instance for exchanging message.

6.PZ9x-
These processes are parallel slave processes (where x is a number),used in fetching data on behalf of GV$ queries.

7.VKTM-
 This process is used to maintain the fast timer and has the same functionality in the RDBMS instances.

Note--
On Unix, the ASM processes can be listed using the following command:

ps –ef|grep asm

Difference Between CHAR vs VARCHAR

CHAR

1.Used to store character string value of fixed length.

2.The maximum no. of characters the data type can hold is 255 characters.

3.It's 50% faster than VARCHAR.

4.Uses static memory allocation.

5.It will waste a lot of disk space.

6.The system does not have to search for the end of string.

7. Use Char when the data entries in a column are expected to be the same size.

EX. 
Declare test Char(100);
test='Test'-
Then "test" occupies 100 bytes first four bytes with values and rest with blank data.



VARCHAR

1.Used to store variable length alphanumeric data.

2.The maximum this data type can hold is up to 4000 characters.

3.It's slower than CHAR.

4.Uses dynamic memory allocation.

5.It will not occupy any space.

6.In VARCHAR the system has to first find the end of string and then go for searching.

7. VARCHAR when the data entries in a column are expected to vary considerably in size.

EX. 
Declare test VARCHAR100);
test='Test'-
Then "test" occupies only 4+2=6 bytes. First four bytes for value and other two bytes for variable length information.


Conclusion:

1.When using the fixed length data's in column like phone number, use CHAR.
2.When using the variable length data's in column like address , use VARCHAR.

Difference Between Views vs Materialized Views

Views vs Materialized Views

1.First difference between View & Materlized View is that , In Views query result is not stored in the disk or database But MV allow to store query result in disk or table.

2. In case of view we always get latest data but in case of MV we need to refresh the view for getting latest data.

3.Performance of view is less than MV.

4.One more difference , In case of view its only the logical view of table no separate copy of table but in case of MV we get separate copy of table.

5.In case of MV we need extra trigger or some automatic method so that we can keep MV refreshed ,This is not required for view in database.

6.A materialized view may be used by the optimizer as a way of pre-aggregating certain interesting data sets in order to more efficiently answer business questions. A view is just a stored query that is executed at runtime.

7.A view occupies no space. but materialized view occupies space. It exists in the same way as a table:
 it sits on a disk and could be indexed or partitioned.




 

MONITOR AN IMPORT DATAPUMP JOB

MONITOR AN IMPORT DATAPUMP JOB (IMPDP)

Many time I find myself having to do imports of data and during the import process customers are asking for status on the import.  To help alleviate these questions and concerns there are a few ways to provide a status on the import process.  I will outline them below:

Use the UNIX “ps –ef” command to track the import the command problem.  Good way to make sure that the process hasn’t error our and quite.

From the UNIX command prompt, use the “tail –f” option against the import log file.  This will give you updates as the log file records the import process.

Set the “status” parameter either on the command line or in the parameter file for the import job.  This will display the status of the job on your standard output.

Use the database view “dba_datapump_jobs” to monitor the job.  This view will tell you a few key items about the job.  The important column in the view is STATUS.  If this column says “executing” then the job is currently running.

Lastly, a good way to watch this process is from the “v$session_longops” view.  This view will give you a way to calculate percentage completed.

There are 5 distinct ways of monitoring a datapump import job.  These approaches can also be used with datapump export jobs.  Overall, monitoring a datapump job could help you in resolving customer questions about how long it will take.

Difference Between Grep & Find

Grep & Find

The main difference between the two is that grep is used to search for a particular string in a file
 whereas
find is used to locate files in a directory,
also you might want to check out the two commands by typing 'man find' and 'man grep'.

Grep command:-> is used for finding any string in the file.
Ex-> 1. grep <String> <filename>
2.grep 'abc xyz' jump.txt

The Above noticable point that it display the whole line,in which line abc xyz string is found.

Find command :-> used to find the file or directory in given path,
Ex-> 1. find <filename>
2. find jump*
display all file name starting with jump,
3. find jump*game.txt

The above noticable point that it display all file in current directory starting with jump and ending with game.

Cluster Background process:

Cluster Background process:

There are difference between Rac and Cluster Background process. Cluster background Discription are Below:

A.Cluster ready Service Daemon (CRSD)
B.Cluster Synchronization Service (CSS)
C.Event Management Daemon (EVMD)
D.Disk Monitor daemon (diskmon)
E.Oracle Notification Service (ONS)

A.Cluster ready Service Daemon (CRSD)

1.Oracle clusterware uses CRS for interaction between the OS and the Database.
2.This metadata is stored in the OCR.
3.It is managing high availability operations in a cluster.
4.The crsd process generates events when the status of a resource changes.

B.Cluster Synchronization Service (CSS)

1.Manages the cluster configuration by controlling which nodes are members of the cluster and by notifying members
 when a node joins or leaves the cluster.
2.Oracle Clusterware performs synchronization of group/locks for the node.
3.Prevents data corruption in event of a split brain.
4.Read voting disk to determine the number and names of members in the cluster.
5.CSS tries to establish connection to all nodes in the cluster using the private interconnect.
6.CSS verifies the number of nodes already registered as part of the cluster by performing an active count function.
7.If no MASTER node has been established CSS authorizes the first node that attains the ACTIVE state as MASTER.
8.Read voting disk to determine the number and names of members in the cluster.
9.Determines the location of the OCR from the ocr.loc file and reads the OCR file to determine the location of the voting  disk.
10.CSS performs state changes to bring the voting disk online.

C.Event Management Daemon (EVMD)

1.It is a background process that publishes Oracle Clusterware events.
2.It is propagates events through the Oracle Notification Service (ONS).
3.EVMD is the communication bridge between the Cluster-Ready Service Daemon (CRSD) and CSSD.[ All communications between the
CRS and CSS happen via the EVMD].

D.Disk Monitor daemon (diskmon)

1.Monitors and performs input/output fencing for Oracle Exadata Storage Server.
2.Exadata storage can be added to any Oracle RAC node at any point in time, the diskmon daemon is always started when ocssd is start.

E.Oracle Notification Service (ONS)

1. It keeps the high availability information.
2.Whenever state of cluster resource changes ONS process , each node will communicate with each other .
3.It is a publish-and-subscribe service for communicating Fast Application Notification (FAN) events.

RAC Background Processes

RAC Background Processes

A.Lock Monitor Processes (LMON)
B.Lock Monitor Services (LMS)
C.Lock Monitor Daemon Process ( LMD)
D.LCKn ( Lock Process)
E.DIAG (Diagnostic Daemon)
F.ACMS:(Atomic Controlfile to Memory Service)--(from Oracle 11g)

A.Lock Monitor Processes (LMON)-

1.It is also called as  GES [Global Enqueue Service] monitor.
2.LMON Maintains GCS memory structures.
3.LMON handles the abnormal termination of processes and instances.
4.LMON deals with Reconfiguration of locks & resources when an instance joins or leaves the cluster (During reconfiguration LMON generate the trace files)
5.LMON Processes manages the global locks & resources.
6.LMON also provides cluster group services.
7.It checks for instance deaths and listens for local messaging.
8.Lock monitor co-ordinates with the Process Monitor (PMON) to recover dead processes that hold instance locks.

B.Lock Monitor Services (LMS)

1.It is also called the GCS (Global Cache Services) processes.
2.It consumes significant amount of CPU time.
3.It is the cache fusion part and the most active process.
4.Each node will have 2 or more LMS processes.
5.LMS also constantly checks with the LMD background process (or our GES process) to get the lock requests placed by the LMD process.
6.It is advised to Increase the parameter value, if global cache activity is very high.
7.It handles the consistent copies of blocks that are transferred between instances.

C. Lock Monitor Daemon Process ( LMDn)

1.It also monitors for lock conversion time outs.
2.LMD process performs lock and deadlock detection globally.
3.LMD process also handles deadlock detection and remote enqueue requests.
4.LMON-provided services are also known as cluster group services (CGS).

D.LCKn (Lock Process)

1.This process is called as instance enqueue process.
2.It manages instance resource requests & cross instance calls for shared resources.
3.During instance recovery, it builds a list of invalid lock elements and validates lock elements.
4.This process manages non-cache fusion resource requests such as library and row cache requests.

E.DIAG (Diagnostic Daemon)

1.A new background process introduced in Oracle 10g featuring new enhanced diagnosability framework.
2.Regularly monitors the health of the instance.
3.It is also checks instance hangs & deadlocks.
4.It captures the vital diagnostics data for instance & process failures.

F.ACMS:(Atomic Controlfile to Memory Service)--(from Oracle 11g)
1.ACMS stands for Atomic Control file Memory Service.
2.ACMS is a agent that help to ensure committed data written into the disk from SGA.