memory

In the unix command prompt,

>svmon -O summary=basic,unit=auto

available column shows the memory available

we can kill the session in order to release some memory

How to get the username who is consuming more memory
------------------------------------------------------------------------------------
run the below query:

SELECT spid, program,
pga_max_mem max,
pga_alloc_mem alloc,
pga_used_mem used,
pga_freeable_mem free
FROM V$PROCESS
order by 3;


SQL> select spid,addr from v$process where spid='xxxxxxxx';

SQL> select sid,serial#,username,status,sql_id,machine from v$session
where paddr='xxxxxxxxxxx';

filesystem /

filesystem / is managed by DBA and not by unix SA

history of sql commands

To track the sql commands, we can either check in OEM grid control or AWR report.

blocking session

select sid, username, serial#, process, nvl(sql_id, 0), sql_address, blocking_session, wait_class, event, p1, p2, p3, seconds_in_wait from v$session where blocking_session_status = 'VALID' OR sid IN (select blocking_session from v$session where blocking_session_status = 'VALID');

another query
--------------------------
select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2
/

The output will be something like the below

SID 'ISBLOCKING' SID
--- ------------- ------
422 IS BLOCKING 479


Another query
------------------------
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 ;


The output will be like this :

BLOCKING_STATUS
-----------------------------------------------------------------------
BULKLOAD@yttrium ( SID=422 ) is blocking BULKLOAD@yttrium ( SID=479 )

compress all the archive logs at one time

go to the location where archive logs are present
compress *.arc

gather stats on index

SQL> Execute DBMS_STATS.GATHER_INDEX_STATS ('schemaname','indexname');

PL/SQL procedure successfully completed.

compress archive log files

compress -f complete path/*.arc