spool /export/home/oracle/scripts/idle_cpu.txt set linesize 120 set pagesize 120 SELECT TO_CHAR (start_date, ‘DD-MON-RRRR:HH24:MI’) start_dt, user_cpu, system_cpu, idle_cpu, wait_cpu FROM stats$vmstat2 where idle_cpu=(SYSDATE – 1/48) AND ROWNUM
column start_dt format a20 column start_dt1 format a20 set lines 120 pages 300 select to_char(start_date, ‘DD-MON-RRRR:HH24:MI’) start_dt, user_cpu, system_cpu, idle_cpu, wait_cpu from STATS$VMSTAT2 where start_date > trunc(sysdate) order by start_date; select to_char(measured_date, ‘DD-MON-RRRR:HH24:MI’) start_dt, total_users, Active_users from STATS$TOTALUSERS where measured_date > trunc(sysdate) order by measured_date; select to_char(start_date, ‘DD-MON-RRRR:HH24:MI’) start_dt1, system_sessions, active_users, inactive_users, dedicated_servers, user_sessions, transactions…
select segment_name, status from dba_rollback_segs order by status, segment_name; select status, count(1) from dba_rollback_segs group by status;
select sql_text,(executions-sorts) “Diff” from v$sqlarea where (executions-sorts) < 20 and executions > 100 order by 2 ; SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, T.sqladdr address, Q.hash_value, Q.sql_text FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS WHERE T.session_addr = S.saddr AND T.sqladdr = Q.address…
#!/bin/ksh # This parameter will be for executing environment file. $1 $ORACLE_HOME/bin/sqlplus -s “/ as sysdba”
#!/bin/ksh ORACLE_HOME=/oracle/app/oracle/product/9.2.0.8;export ORACLE_HOME ORACLE_SID=800P; export ORACLE_SID $ORACLE_HOME/bin/sqlplus -s “/ as sysdba”
SELECT b.TABLESPACE, b.segfile#, b.segblk#, b.blocks, a.SID, a.serial#, a.username, a.osuser, a.status, c.sql_text FROM v$session a, v$sort_usage b, v$sqlarea c WHERE a.saddr = b.session_addr AND a.SQL_HASH_VALUE = c.HASH_VALUE ORDER BY b.TABLESPACE, b.segfile#, b.segblk#, b.blocks / set pagesize 10000 set linesize 133 column tablespace format a15 heading ‘Tablespace Name’ column segfile# format 9,999 heading ‘File|ID’ column segblk# format…
set time on set timing on set verify off select to_char(sysdate, ‘DD-MON-RRRR:HH24:MI’) from dual; prompt ‘Total distinct users and total user sessions’ select count(distinct username), count(1), round(count(1)/count(distinct username),1 ) from v$session where username is not null; prompt ‘Total terminal server distinct users and total user sessions’ select count(distinct username), count(1), round(count(1)/count(distinct username),1 ) from v$session…
col AVG_ACTIVE_USR format 999999.99 col AVG_TOT_USR format 9999.99 col AVG_USR_CPU format 9999.99 col AVG_SYS_CPU format 99.99 col AVG_IDLE_CPU format 99.99 col AVG_WAIT_CPU format 99.99 col TOT_ODR format 99999999 col AVG_ACT_USR format 999999.99 col RUNQ format 999.99 col CSW format 99999.99 set lines 120 pages 2000 accept start1 prompt ‘Enter Start Dare in DD-MON-RRRR:HH24:MI format :…
Read More “pvm_metric.sql for gathering report from vmstat tables” »
++++++++++++++++++++ Problem description ++++++++++++++++++++ Some users started getting ORA-4031. This is the eror that show following: 4031, 00000, “unable to allocate %s bytes of shared memory (“%s”,”%s”,”%s”,”%s”)” ++++++++++++++++++++++ Problem solution ++++++++++++++++++++++ We ran following query and found failues that may result into 4031. set echo off verify off set lines 120 pages 555 /* V$SHARED_POOL_RESERVED_SIZE…
