pvm_rbs1.sql (to collect rbs info from db)
select segment_name, status from dba_rollback_segs order by status, segment_name; select status, count(1) from dba_rollback_segs group by status;
Category: SQL scripts
Find sort details from Db find_sort.sql
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…
temp_use.sql diplays usage of temp ts
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…
get_ratio.sql get the ratio of users from v$session and this uses CASE-WHEN-THEN clause
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…
pvm_metric.sql for gathering report from vmstat tables
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” »
ORA-4031 issue and solution on 09-MAY-2008
++++++++++++++++++++ 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…
How to calculate PROCESSES parameter
SEMMSL Set to 10 plus the largest initsid.ora PROCESSES parameter of an Oracle database on the system. The PROCESSES parameter can be found in each initsid.ora file, loc ORACLE_HOME/dbs directory. The default value of PROCESSES for the 8.1.5 prebuilt database is 50. SEMMNS Set to the sum of the PROCESSES parameter for each Oracle database…
How to set Processes Parameter
The only drawback in setting the processes value high is that the corresponding kernel settings, SEMMNS and SEMMSL need to be set higher based on the following formulas: SEMMSL – Set to 10 plus the largest PROCESSES parameter of any Oracle database on the system. SEMMNS – Set to the sum of the PROCESSES parameter…
Explain Plan Doesn’T Change For Sql After New Statistics Generated
If you use dbms_stats in 10g it will not automatically invalidate dependant cursors. This is the default behaviour as of 10. To revert to older previous behaviour you will have to set the parameter no_invalidate to false. . no_invalidate Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors…
Read More “Explain Plan Doesn’T Change For Sql After New Statistics Generated” »
alter database backup controlfile to trace
Backing it up in a human readable format: alter database backup controlfile to trace; alter database backup controlfile to trace as ‘/some/arbitrary/path’; alter database backup controlfile to trace as ‘/some/arbitrary/path’ reuse; If you are using “alter database backup controlfile to trace as ‘test1.sql’; then test1.sql will be created in $ORACLE_HOME/dbs folder.