Friday, May 27, 2011

Ulimit

ulimit Command
Purpose
Sets or reports user resource limits.
Syntax
ulimit [ -H ] [ -S ] [ -a ] [ -c ] [ -d ] [  -f ] [ -m ] [ -n ] [ -r ] [ -s ] [ -t ] [ -u ][ Limit ]
Description
The ulimit command sets or reports user process resource limits, as defined in the /etc/security/limits file. This file contains these default limits:
fsize = 2097151 , core = 2097151 , cpu = -1 , data = 262144,  rss = 65536, stack = 65536
nofiles = 2000, threads = -1, nproc = -1
These values are used as default settings when a new user is added to the system. The values are set with the mkuser command when the user is added to the system, or changed with the chuser command.

Limits are categorized as either soft or hard. With the ulimit command, you can change your soft limits, up to the maximum set by the hard limits. You must have root user authority to change resource hard limits.
Many systems do not contain one or more of these limits. The limit for a specified resource is set when the Limit parameter is specified. The value of the Limit parameter can be a number in the unit specified with each resource, or the value unlimited. To set the specific ulimit to unlimited, use the word unlimited
Note: Setting the default limits in the /etc/security/limits file sets system wide limits, not just limits taken on by a user when that user is created
The current resource limit is printed when you omit the Limit parameter. The soft limit is printed unless you specify the -H flag. When you specify more than one resource, the limit name and unit is printed before the value. If no option is given, the -f flag is assumed.
Since the ulimit command affects the current shell environment, it is provided as a shell regular built-in command. If this command is called in a separate command execution environment, it does not affect the file size limit of the caller's environment. This would be the case in the following examples:
nohup ulimit -f 10000
env ulimit 10000

Once a hard limit has been decreased by a process, it cannot be increased without root privilege, even to revert to the original limit.

Scripts Used in Critical Production Oracle-EBiz Time Monitoring


=== Temp Usage ===

select sum(blocks)/1024*8 "Size in MB" FROM v$sort_usage;

SELECT ss.sid, sum(st.blocks)/1024*8 FROM v$sort_usage st, v$session ss where ss.saddr=st.session_addr group by ss.sid having sum(st.blocks)/1024*8 > 100 order by 2;

SELECT  /*+ RULE */ s.username, s.osuser, s.sid, NVL(s.machine,'N/A'),
                         NVL(s.module,'N/A'), NVL(s.action,'N/A'), NVL(s.program,'N/A'),
                         s.status ||' for '||
                         LPAD(((last_call_et/60)-mod((last_call_et/60),60))/60,2,'0') ||':'||
                         LPAD(ROUND(mod((last_call_et/60),60)),2,'0') ||' Hr' ,
                         u.tablespace, u.contents, u.extents, round((u.blocks*8)/1024),
                         s.sql_address, s.sql_hash_value
                 FROM    v$session s, v$sort_usage u
                 WHERE   s.saddr    = u.session_addr
                 AND     u.contents = 'TEMPORARY'
                 AND     s.audsid != USERENV('sessionid')
                 AND    (u.blocks*8)/1024 >= 1000
                 ORDER   BY 1,2,3,4,5 Desc;

=== High Redo ===

SELECT s.inst_id,s.sid, s.serial#, s.username, s.program, i.block_changes
FROM gv$session s, gv$sess_io i
WHERE s.sid = i.sid
AND i.block_changes > 10000000
ORDER BY 6 desc, 1, 2, 3, 4;

=== Rollback Used ===
                                 
SELECT rn.name, ROUND(rs.rssize/1024/1024),
                        s.username, s.osuser, s.sid, NVL(s.machine,'N/A'),
                        NVL(s.module,'N/A'), NVL(s.action,'N/A'), NVL(s.program,'N/A'),
                        s.sql_address, s.sql_hash_value, p.spid,
                        s.status ||' for '||
                        LPAD(((last_call_et/60)-mod((last_call_et/60),60))/60,2,'0') ||':'||
                        LPAD(ROUND(mod((last_call_et/60),60)),2,'0') ||' Hr' ,
                        round(ceil((t.used_ublk*8)/1024),1)
                 FROM   v$rollname rn, v$rollstat rs,
                        v$session s, v$transaction t, v$process p
                 WHERE  rn.usn = rs.usn
                 AND    round((t.used_ublk*8)/1024) >= 1000
                 AND    rs.usn = t.xidusn
                 AND    s.sid = p.pid (+)
                 AND    s.taddr = t.addr
                 ORDER  BY 2 desc, s.sid ,s.status

=== UNDO segement Information ====
 
select tablespace_name, status segment_status, count(extent_id) "Extent Count", sum(blocks) "Total Blocks", sum(blocks)*8/(1024*1024) "Total Space in GB"
from dba_undo_extents where tablespace_name like '%UNDO%'
group by tablespace_name, status;

=== Shared Pool Usage ===

SELECT INST_ID,ROUND(bytes/1024/1024, 2)||' MB' FROM gv$sgastat  WHERE name='free memory' AND pool='shared pool';

=== Archive Generation for last 5 hours ===

SELECT TO_CHAR(first_time, 'DD-MM-YY') AS Day,TO_CHAR(first_time, 'HH24') AS Hour,COUNT(*)  FROM v$log_history WHERE TO_CHAR(first_time, 'DD-MM-YY') = TO_CHAR(sysdate, 'DD-MM-YY') AND  TO_CHAR(first_time, 'HH24') >= TO_CHAR(sysdate, 'HH24') - 5 GROUP BY  TO_CHAR(first_time, 'DD-MM-YY'), TO_CHAR(first_time, 'HH24') ORDER BY 2;

=== High Memory ===

select s.inst_id, s.sid, name, round(value/1024/1024) Mem_in_mb, p.*
from gv$sesstat s, v$statname n, gv$session p
where n.statistic# = s.statistic#
and s.inst_id = p.inst_id
and s.sid = p.sid
and name like '%pga memory%'
and value > 1*1024*1024*512;

=== Performance ===

select sql_hash_value, count(*) from v$session where event like 'enq%' group by sql_hash_value;

select sql_hash_value,username,osuser, count(*) from v$session where event like 'enq%' and SQL_HASH_VALUE='&event' group by sql_hash_value,username,osuser;

select sql_text from v$sqlarea where hash_value = '&hash_value';
select s1.sid,FETCHES,ROWS_PROCESSED from v$sql s,v$session s1 where s.HASH_VALUE=s1.SQL_HASH_VALUE and s1.sid=4885;


col EVENT for a50;
col OSUSER for a20;
col USERNAME for a20;
set linesize 175;
select s.sid,s.serial#,s.status,s.username,s.module,s.osuser,x.event
from v$session s,v$session_wait x where s.paddr in
( select addr from v$process where spid in (2340,23869,13827,18261,14880,2381))
and x.sid=s.sid;

col EVENT for a50;
col OSUSER for a20;
col USERNAME for a20;
set linesize 175;
select s.sid,s.serial#,s.status,s.username,s.module,s.osuser,x.event from v$session s,v$session_wait x
where x.sid=s.sid and x.event like '&event_name';


=== Top 10 Memory Process at OS level ===

UNIX95=1 ps -eo vsz,pid,args | sort +0n -1 | grep -i `echo $ORACLE_SID` | tail -10

=== Other Script to update the daily report ===

select status,count(1) from gv$session group by status; 

STATUS     COUNT(1)
-------- ----------
ACTIVE           22
INACTIVE        155

select count(1) from dba_tables where logging='NO';

  COUNT(1)
----------
       919

select distinct status,count(1) from dba_indexes group by status;


STATUS     COUNT(1)
-------- ----------
N/A            1639
UNUSABLE          1
VALID          6162

select count(1) from dba_objects where status='INVALID';

  COUNT(1)
----------
       681

Number of Users Connected with Oracle Apps/Count Concurrent_users in Oracle Apps


  How do we know how many users are connected to Oracle Applications?

1st Method :

1. Enable Profile Option "Sign-On Audit" at "Form" level.
2. Run "Purge Signon Audit" request.
3. Security:Users -> Monitor" option

2nd Method :

select distinct fu.user_name User_Name,fr.RESPONSIBILITY_KEY Responsibility
from fnd_user fu, fnd_responsibility fr, icx_sessions ic
where fu.user_id = ic.user_id AND
fr.responsibility_id = ic.responsibility_id AND
ic.disabled_flag='N' AND
ic.responsibility_id is not null AND
ic.last_connect like sysdate;

Count Number of concurrent_users in Oracle apps?

select count(distinct d.user_name) from apps.fnd_logins a,
v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1);

Free Memory Available in Shared Pool, Java Pool, Large Pool and Streams Pool



Free Memory Available in Shared Pool, Java Pool, Large Pool and Streams Pool

Sql > select pool, name, bytes/1024/1024 MB
from v$sgastat
where name='free memory';

Free Memory Available in Shared pool :

Sql> select pool, name, bytes/1024/1024 MB
from v$sgastat
where name='free memory' and POOL='shared pool';

Total Memory of shared Pool :

Sql > Show parameter shared_pool