Oracle Application Experience (2) _oracle

The Oracle tutorial you are looking at is: Oracle Application Experience (2). ------who is accessing the database?
Select C.sid, C.serial#,c.username,a.object_id,b.object_name,
C.program,c.status,d.name,c.osuser
From V$locked_object A,
All_objects B,
V$session C,
Audit_actions D
where a.object_id=b.object_id
and a.session_id =c.sid (+)
and c.command=d.action;

Alter system kill session &1,&2;

Select A.sid,a.serial#,a.username,a.status,a.program,b.name,a.osuser
From V$session a,audit_actions b
where a.command=b.action
and username=&1;
------who is locked up?
Select A.sid,a.serial#,a.username,a.lockwait,a.status,a.program,b.name
From V$session a,audit_actions b
where a.command=b.action
And lockwait is not NULL;
------Who's locked in the watch?
Select A.sid,a.serial#,a.username,a.lockwait,a.status,a.program,b.name
From V$session a,audit_actions b
where a.command=b.action
and status=active;

Select SID, Serial#, object_name, row_wait_block#,
row_wait_row#, row_wait_file#
From All_objects, v$session
where row_wait_obj#=object_id and Type=user
And lockwait is not null;

Select Sl.username, Sl.sid, sl.serial#
From V_$lock L1, v$session s1
where exists (SELECT * from V_$lock L2, v$session s2
where L2.sid=s2.sid and L2.ID1=L1
and S2.LOCKWAIT=L2.KADDR
and request=0
and L1.sid=s1.sid);

Select COUNT (*) from v$session;
Select COUNT (*) from sys.v_$process;
Select COUNT (*) from sys.v_$transaction;

zyp_35.98

------See which packages are fixed
COLUMN OWNER FORMAT A10
Select owner, name, type,
Source_size+code_size+parsed_size+error_size Bypes
From Dba_object_size
where Type=package body order by 4 DESC;

------View entity information for which table spaces a user has:
Select Tablespace_name, owner, Segment_name,segment_type
From Dba_segments
where Owner-sys
and Segment_type_-rollback
Order by Tablespace_name, owner, Segment_name;

Break on owner on Segment_name
COLUMN segment_name FORMAT A15
COLUMN tablespace_name FORMAT A15
COLUMN file_name FORMAT A20
SELECT A.owner, A.segment_name, B.tablespace_name, B.file_name,
sum (a.bytes) bytes
From Dba_extents A, dba_data_files b
where a.file_id-b.file_id GROUP by A.owner, A.segment_name,
B.tablespace_name, B.file_name;

------The index of the efficiency of memory buffer usage is the hit rate hits:
hits=logical_reads/(Logical_reads+physical_reads)
of which: Logical_reads=db_block_gets+consistent_reads

Select Cur.value db, Con.value con, Phy.value PHY,
(cur.value+con.value)/cur.value+con.value+phy.value) *100 HITS
From V$sysstat cur, v$sysstat con, v$sysstat PHY
where cur.name=db block gets and
Con.name=consistent gets and
Phy.name=physical reads;

------How to detect rollback segment competition?
Select Class, Count from V$waitstat
where class in
(System undo Header, System undo block,
Undo header, undo block);

Select sum (value) from V$sysstat where name in
(db block gets, consistents gets);

If Count/sum (value) is greater than 1%, consideration should be given to increasing rollback SEGMENT

------See where transactions are in a few fallback segments:
COLUMN u FORMAT A15
COLUMN s FORMAT A15
COLUMN s FORMAT A80
Select Osuser O, username u, segment_name s, sa.sql_text
From V$session S, V$transaction T, Dba_rollback_segs R, V$sqlarea SA
where S.taddr=t.addr and t.sidusn=r.segmant_id (+)
and s.sql_address=sa.address (+);

<