Swap space Calculation

Refer MOS Doc: 286388.1

There are two swap memory allocation algorithms, used widely.
1. Eager Allocation Model
This algorithm preallocates swap at the time of process creation.
2. Lazy Allocation Model
In this swap is allocated on-demand. (Called Paging).

In Eager model, 
Minimum swap required = Total virtual memory of all the processes on the Machine

In Lazy Model:
Minimum swap required = Total virtual memory of all processes - Size of RAM

In both models, the Virtual Memory is calculated using, the following sql (in peak time)

select (value/1024/1024)+(select (sum(st.value )/1024/1024)
 from v$sesstat st , v$statname sn
where st.statistic# = sn.statistic#
and sn.name like '%ga memory') "Virtual Memory" 
from v$parameter 
where name='sga_max_size'
;




For 32-bit machines swap can be 
Minimum of 400Mb or 2* size of RAM which ever is greater.
OR
If physical RAM < 2GB , swap = 2* Physical RAM
If Physical RAM >2B & <4GB swap = Size of Physical RAM
If Physical RAM > 4GB swap = 4GB

Difference Between GV$ASM_DISKGROUP_STAT and GV$ASM_DISKGROUP

Last night I was working through some of the ASM Global Dynamic Views.

I was wondering, why both GV$ASM_DISKGROUP_STAT and GV$ASM_DISKGROUP are having same columns and showing the same data.

I got a nice explanation from www.orafaq.com.  (Link: http://www.orafaq.com/wiki/ASM_FAQ)

GV$ASM_DISKGROUP_STAT fetches data from cache  while
GV$ASM_DISKGROUP  fetches data from disk headers.


The former view (%_stat) operates faster than the later.

Moreover, they suggested to change the references of GV$ASM_DISKGROUP to GV$ASM_DISKGROUP_STAT in $ORACLE_HOME/bin/asmcmdcore, if someone feels that, asmcmd command line utility is slow.
There are 4 references of GV$ASM_DISKGROUP in "asmcmdcore" file.

(These findings are applicable to oracle 10.2.0.3 EE. Readers kindly check the documentation of their Db version)

Stats Gathering script

Here's a nice Statistics gathering script.

spool '/tmp/gather.spl';
set heading off;
select '################################' from dual;
select 'Start of Gathering Schema Stats...' from dual;

select sysdate from dual;

exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascad
e => TRUE);

exec dbms_stats.gather_schema_stats('APPS',options=>'GATHER',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', casca
de => TRUE);

exec dbms_stats.gather_schema_stats('APPLSYS',options=>'GATHER',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', ca
scade => TRUE);

exec dbms_stats.gather_schema_stats('AP',options=>'GATHER',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade
 => TRUE);

exec dbms_stats.gather_schema_stats('AR',options=>'GATHER',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade
 => TRUE);

exec dbms_stats.gather_schema_stats('INV',options=>'GATHER',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascad
e => TRUE);

exec dbms_stats.gather_schema_stats('GL',options=>'GATHER',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade
 => TRUE);

select sysdate from dual;
select null from dual;
select 'End of Stats gathering!!!' from dual;
select '################################' from dual;
spool off;

Thanks to Nazim.

Status of Conc Request with last update date

Below given sql query provides the status, phase, user submitted, last update time with Concurrent request name.

select r.concurrent_program_id,
r.request_id,
r.last_update_date,
r.last_updated_by,
u.user_name,r.phase_code,
(case r.phase_code
when 'I' then 'Inactive'
when 'P' then 'Pending'
when 'R' then 'Running'
when 'C' then 'Completed'
else 'N/a'
end) Phase,r.status_code,
( case r.status_code
when 'A' then  'Waiting'
when 'B' then 'Resuming'
when 'C' then 'Normal'
when 'D' then 'Cancelled'
when 'E' then 'Error'
when 'F' then 'Scheduled'
when 'G' then 'Warning'
when 'H' then 'On Hold'
when 'I' then 'Normal'
when 'M' then 'No Manager'
when 'Q' then 'Standby'
when 'R' then 'Normal'
when 'S' then 'Suspended'
when 'T' then 'Terminating'
when 'U' then 'Disabled'
when 'W' then 'Paused'
when 'X' then 'Terminated'
when 'Z' then 'Waiting'
else 'N/a'
end
) Status,
t.user_concurrent_program_name 
from 
fnd_concurrent_requests r,
fnd_concurrent_programs_tl t,
fnd_user u
where
-- status_code='I' and
r.concurrent_program_id=t.concurrent_program_id
and
r.last_updated_by=u.user_id
order by r.last_update_date;







The status and phase code seems mis-matching with the application. 
Anybody, knows the correct codes ?

Today's Count of Concurrent Requests

A) This query provides the concurrent requests submitted since 12AM, today

select count(*) from apps.fnd_concurrent_requests
where request_date > sysdate - (1/24*(select extract(hour from current_timestamp)+4
/*since my timezone is GST+4 */   from dual)) order by request_date ;

This can be fine tuned for count of completed requests.

B) This query provides the count of conc requests submitted in last one hour.

select count(request_date) from apps.fnd_concurrent_requests where request_date > sysdate -(1/24)

I created a job that uses this query to insert count and sysdate values into an "xyz" table on scott schema.
Later, I used this "xyz" table to create a graph (using ODBC) on MS Excel spreadsheet, that represents the Request submitting pattern of my organization.
The funny part is, the graph dives deep at coffee breaks and lunch break.


Your feedback are welcome.
Sundar K