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…
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…
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…
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” »
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.
Here is a much simpler version: ——————————- Create table t1 (c1 number primary); session1: insert into t1 values (1); session2: insert into t1 values (1); — would hang v$lock would reveal session 1 blocking session2 on TX lock why? because you have a constraint where you are asking Oracle to check uniqueness of data (unique…
