SQL background execution

cat exec.sh
x=`${ORACLE_HOME}/bin/dekr /export/home/oracle/scripts/dba/.Schedulejobs/perf.dat`
${ORACLE_HOME}/bin/sqlplus -s <<EOS
username/password
set pages 200 lines 130
set long 50000
start script_name.sql
exit;
EOS

Statspack Purge Snapshot Job

Statspack Purge Snapshot Job
=============================
create PROCEDURE "PERFSTAT"."PURGE_SNAPS" as
begin

delete from perfstat.stats$snapshot
where snap_time < (sysdate-30)
and rownum < 101;

while sql%found
loop

commit;

delete from perfstat.stats$snapshot
where snap_time < (sysdate-30)
and rownum < 101;

end loop;

commit;

end;
/



exec dbms_job.submit(:jobno, 'perfstat.purge_snaps;', trunc(sysdate+7), 'trunc(sysdate+7)', TRUE, 1);

Resource Intensive Query

select s.sid,s.serial#,s.username,s.program,s.sql_hash_value,s.taddr
from v$session s,v$process p
where s.paddr=p.addr and p.spid=&spid;

select sql_text from v$sql where hash_value in (
select s.sql_hash_value
from v$session s,v$process p
where s.paddr=p.addr and p.spid=&spid);

Rollback Monitoring

SELECT XIDUSN,USED_UREC FROM V$TRANSACTION
where addr=(select taddr from v$session where username='&uname');

select usn,RSSIZE,WRITES,STATUS from v$rollstat where usn=(select XIDUSN FROM V$TRANSACTION where addr=(select taddr from v$session where username='&uname'));

Maximizing Export Performance

Maximizing Export Performance

Many DBAs are faced with the challenge of speeding up utility functions such as export. Typically, an organization has only a small window for maintenance, and utility jobs must complete within that timeframe. Fortunately, there are a few things a DBA can do to expedite exports. These include:



· Use Direct Path – Direct path exports (DIRECT=Y) allow the export utility to skip the SQL evaluation buffer, whereas the conventional path export executes SQL SELECT statements. With direct path, the data is read from disk into the buffer cache, returning rows directly to the export client. This can offer substantial performance gains, depending on the actual data. When using the direct path, the recordlength parameter should also be used to optimize performance.

· Use Subsets – By subsetting the data using the QUERY option, the export process is only executed against the data that needs to be exported. If tables have old rows that are never updated, the old data should be exported once, and from that point only the newer data subsets should be exported. Subsets cannot be specified with direct path exports since SQL is necessary to create the subset.

· Use a Larger Buffer – For conventional path exports, a larger buffer will increase the number of rows that are processed between each physical write to the export file. Fewer physical writes equals greater performance. The following formula can be used to determine a proper buffer size:

buffer size = rows in array * max row size