05/26: Oracle > Performance check
select sql_text, executions from v$sql where cpu_time > 20000;
select * from v$session where machine = ‘xxxx’ and logon_time > sysdate - 1;
select sid, ctime from v$lock where block > 0;
select
oracle_username
os_user_name,
locked_mode,
object_name,
object_type
from
v$locked_object a,dba_objects b
where
a.object_id = b.object_id
select s.sid, p.spid, s.status,s.process,s.osuser, a.sql_text, p.program
from
v$session s, v$sqlarea a, v$process p
where
s.sql_hash_value=a.hash_value
and s.sql_address=a.address
and s.paddr=p.addr
and s.status='ACTIVE';
The views are owned by the SYS user. You can query $V$FIXED_TABLE to see all the view names. Read consistency is not guaranteed.
More complicated example:
Oracle Session I/O
New Performance Views to Identify Problem SQL
select * from v$session where machine = ‘xxxx’ and logon_time > sysdate - 1;
select sid, ctime from v$lock where block > 0;
select
oracle_username
os_user_name,
locked_mode,
object_name,
object_type
from
v$locked_object a,dba_objects b
where
a.object_id = b.object_id
select s.sid, p.spid, s.status,s.process,s.osuser, a.sql_text, p.program
from
v$session s, v$sqlarea a, v$process p
where
s.sql_hash_value=a.hash_value
and s.sql_address=a.address
and s.paddr=p.addr
and s.status='ACTIVE';
The views are owned by the SYS user. You can query $V$FIXED_TABLE to see all the view names. Read consistency is not guaranteed.
More complicated example:
Oracle Session I/O
New Performance Views to Identify Problem SQL
05/12: Oracle > timeout
ORA-12170: TNS: Connect timeout occurred tips
/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
/u01/app/oracle/product/10.2.0/db_1/network/log
Sidebar
