set linesize 100 set pagesize 100 column day format a15 heading ‘Day’ column d_0 format a2 heading ’00’ column d_1 format a2 heading ’01’ column d_2 format a2 heading ’02’ column d_3 format a2 heading ’03’ column d_4 format a2 heading ’04’ column d_5 format a2 heading ’05’ column d_6 format a2 heading ’06’ column…
Read More “find_log_switch.sql Find log switches in graphical manner” »
set lines 132 set pages 1400 accept v_address prompt ‘Enter ADDRESS :’ accept v_hash_value prompt ‘Enter HASH VALUE :’ select sql_text from v$sqltext where address=’&v_address’ and hash_value=’&v_hash_value’ order by piece; SELECT LPAD(‘ ‘,2*(LEVEL-1))||operation||’ ‘||options ||’ ‘||object_name ||’ ‘|| DECODE(id, 0, ‘Cost = ‘||position) “Query Plan” FROM v$sql_plan START WITH id = 0 and address=’&v_address’ and…
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…
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…
