handle cancelled session request from java to oracle,When a user run a report query, and he closes the window immediately does the query still keeps running

Backend db is oracle 11g Frontend is Java 

handle cancelled session request from java in oracle,When a user run a report query, and he closes the window immediately does the query still keeps running.

Due to an abnormal client termination. If the probe finds a terminated connection, or a connection that is no longer in use.

The SQLNET.EXPIRE_TIME (set in your sqlnet.ora) determines how often you check for dead connections:

http://docs.oracle.com/database/122/NETAG/configuring-profiles.htm#NETAG091 

SQLNET.SEND_TIMEOUT=3

lsnrctl stop and start

Oracle dba/monitoring/tuning.sql

Oracle dba/monitoring/tuning.sql

SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF

SELECT *
FROM   v$database;
PROMPT

DECLARE
  v_value  NUMBER;

  FUNCTION Format(p_value  IN  NUMBER) 
    RETURN VARCHAR2 IS
  BEGIN
    RETURN LPad(To_Char(Round(p_value,2),'990.00') || '%',8,' ') || '  ';
  END;

BEGIN

  -- --------------------------
  -- Dictionary Cache Hit Ratio
  -- --------------------------
  SELECT (1 - (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100
  INTO   v_value
  FROM   v$rowcache;

  DBMS_Output.Put('Dictionary Cache Hit Ratio       : ' || Format(v_value));
  IF v_value < 90 THEN
    DBMS_Output.Put_Line('Increase SHARED_POOL_SIZE parameter to bring value above 90%');
  ELSE
    DBMS_Output.Put_Line('Value Acceptable.');  
  END IF;

  -- -----------------------
  -- Library Cache Hit Ratio
  -- -----------------------
  SELECT (1 -(Sum(reloads)/(Sum(pins) + Sum(reloads)))) * 100
  INTO   v_value
  FROM   v$librarycache;

  DBMS_Output.Put('Library Cache Hit Ratio          : ' || Format(v_value));
  IF v_value < 99 THEN
    DBMS_Output.Put_Line('Increase SHARED_POOL_SIZE parameter to bring value above 99%');
  ELSE
    DBMS_Output.Put_Line('Value Acceptable.');  
  END IF;

  -- -------------------------------
  -- DB Block Buffer Cache Hit Ratio
  -- -------------------------------
  SELECT (1 - (phys.value / (db.value + cons.value))) * 100
  INTO   v_value
  FROM   v$sysstat phys,
         v$sysstat db,
         v$sysstat cons
  WHERE  phys.name  = 'physical reads'
  AND    db.name    = 'db block gets'
  AND    cons.name  = 'consistent gets';

  DBMS_Output.Put('DB Block Buffer Cache Hit Ratio  : ' || Format(v_value));
  IF v_value < 89 THEN
    DBMS_Output.Put_Line('Increase DB_BLOCK_BUFFERS parameter to bring value above 89%');
  ELSE
    DBMS_Output.Put_Line('Value Acceptable.');  
  END IF;
  
  -- ---------------
  -- Latch Hit Ratio
  -- ---------------
  SELECT (1 - (Sum(misses) / Sum(gets))) * 100
  INTO   v_value
  FROM   v$latch;

  DBMS_Output.Put('Latch Hit Ratio                  : ' || Format(v_value));
  IF v_value < 98 THEN
    DBMS_Output.Put_Line('Increase number of latches to bring the value above 98%');
  ELSE
    DBMS_Output.Put_Line('Value acceptable.');
  END IF;

  -- -----------------------
  -- Disk Sort Ratio
  -- -----------------------
  SELECT (disk.value/mem.value) * 100
  INTO   v_value
  FROM   v$sysstat disk,
         v$sysstat mem
  WHERE  disk.name = 'sorts (disk)'
  AND    mem.name  = 'sorts (memory)';

  DBMS_Output.Put('Disk Sort Ratio                  : ' || Format(v_value));
  IF v_value > 5 THEN
    DBMS_Output.Put_Line('Increase SORT_AREA_SIZE parameter to bring value below 5%');
  ELSE
    DBMS_Output.Put_Line('Value Acceptable.');  
  END IF;
  
  -- ----------------------
  -- Rollback Segment Waits
  -- ----------------------
  SELECT (Sum(waits) / Sum(gets)) * 100
  INTO   v_value
  FROM   v$rollstat;

  DBMS_Output.Put('Rollback Segment Waits           : ' || Format(v_value));
  IF v_value > 5 THEN
    DBMS_Output.Put_Line('Increase number of Rollback Segments to bring the value below 5%');
  ELSE
    DBMS_Output.Put_Line('Value acceptable.');
  END IF;

  -- -------------------
  -- Dispatcher Workload
  -- -------------------
  SELECT NVL((Sum(busy) / (Sum(busy) + Sum(idle))) * 100,0)
  INTO   v_value
  FROM   v$dispatcher;

  DBMS_Output.Put('Dispatcher Workload              : ' || Format(v_value));
  IF v_value > 50 THEN
    DBMS_Output.Put_Line('Increase MTS_DISPATCHERS to bring the value below 50%');
  ELSE
    DBMS_Output.Put_Line('Value acceptable.');
  END IF;
  
END;
/

PROMPT
SET FEEDBACK ON

Script to find Oracle database performance

Script to find Oracle database performance

Just the below line up to last / copy and paste to sqlplus using / as sysdba

SET SERVEROUTPUT ON

DECLARE
   CURSOR c1
   IS
      SELECT version FROM v$instance;

   CURSOR c2
   IS
      SELECT host_name,
             instance_name,
             TO_CHAR (SYSDATE, 'HH24:MI:SS DD-MON-YY') currtime,
             TO_CHAR (startup_time, 'HH24:MI:SS DD-MON-YY') starttime
        FROM v$instance;

   CURSOR c4
   IS
      SELECT *
        FROM (  SELECT COUNT (*) cnt, SUBSTR (event, 1, 50) event
                  FROM v$session_wait
                 WHERE wait_time = 0
                       AND event NOT IN
                              ('smon timer',
                               'pipe get',
                               'wakeup time manager',
                               'pmon timer',
                               'rdbms ipc message',
                               'SQL*Net message from client')
              GROUP BY event
              ORDER BY 1 DESC)
       WHERE ROWNUM < 6;

   CURSOR c5
   IS
      SELECT ROUND (SUM (VALUE) / 1048576) AS sgasize FROM v$sga;

   CURSOR c6
   IS
      SELECT ROUND (SUM (bytes) / 1048576) AS dbsize FROM v$datafile;

   CURSOR c7
   IS
      SELECT 'top physical i/o process' category,
             sid,
             username,
             total_user_io amt_used,
             ROUND (100 * total_user_io / total_io, 2) pct_used
        FROM (  SELECT b.sid sid,
                       NVL (b.username, p.name) username,
                       SUM (VALUE) total_user_io
                  FROM v$statname c,
                       v$sesstat a,
                       v$session b,
                       v$bgprocess p
                 WHERE     a.statistic# = c.statistic#
                       AND p.paddr(+) = b.paddr
                       AND b.sid = a.sid
                       AND c.name IN
                              ('physical reads',
                               'physical writes',
                               'physical reads direct',
                               'physical reads direct (lob)',
                               'physical writes direct',
                               'physical writes direct (lob)')
                       AND b.username NOT IN
                              ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
              GROUP BY b.sid, NVL (b.username, p.name)
              ORDER BY 3 DESC),
             (SELECT SUM (VALUE) total_io
                FROM v$statname c, v$sesstat a
               WHERE a.statistic# = c.statistic#
                     AND c.name IN
                            ('physical reads',
                             'physical writes',
                             'physical reads direct',
                             'physical reads direct (lob)',
                             'physical writes direct',
                             'physical writes direct (lob)'))
       WHERE ROWNUM < 2
      UNION ALL
      SELECT 'top logical i/o process',
             sid,
             username,
             total_user_io amt_used,
             ROUND (100 * total_user_io / total_io, 2) pct_used
        FROM (  SELECT b.sid sid,
                       NVL (b.username, p.name) username,
                       SUM (VALUE) total_user_io
                  FROM v$statname c,
                       v$sesstat a,
                       v$session b,
                       v$bgprocess p
                 WHERE     a.statistic# = c.statistic#
                       AND p.paddr(+) = b.paddr
                       AND b.sid = a.sid
                       AND c.name IN ('consistent gets', 'db block gets')
                       AND b.username NOT IN
                              ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
              GROUP BY b.sid, NVL (b.username, p.name)
              ORDER BY 3 DESC),
             (SELECT SUM (VALUE) total_io
                FROM v$statname c,
                     v$sesstat a,
                     v$session b,
                     v$bgprocess p
               WHERE     a.statistic# = c.statistic#
                     AND p.paddr(+) = b.paddr
                     AND b.sid = a.sid
                     AND c.name IN ('consistent gets', 'db block gets'))
       WHERE ROWNUM < 2
      UNION ALL
      SELECT 'top memory process',
             sid,
             username,
             total_user_mem,
             ROUND (100 * total_user_mem / total_mem, 2)
        FROM (  SELECT b.sid sid,
                       NVL (b.username, p.name) username,
                       SUM (VALUE) total_user_mem
                  FROM v$statname c,
                       v$sesstat a,
                       v$session b,
                       v$bgprocess p
                 WHERE     a.statistic# = c.statistic#
                       AND p.paddr(+) = b.paddr
                       AND b.sid = a.sid
                       AND c.name IN
                              ('session pga memory', 'session uga memory')
                       AND b.username NOT IN
                              ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
              GROUP BY b.sid, NVL (b.username, p.name)
              ORDER BY 3 DESC),
             (SELECT SUM (VALUE) total_mem
                FROM v$statname c, v$sesstat a
               WHERE a.statistic# = c.statistic#
                     AND c.name IN
                            ('session pga memory', 'session uga memory'))
       WHERE ROWNUM < 2
      UNION ALL
      SELECT 'top cpu process',
             sid,
             username,
             total_user_cpu,
             ROUND (100 * total_user_cpu / GREATEST (total_cpu, 1), 2)
        FROM (  SELECT b.sid sid,
                       NVL (b.username, p.name) username,
                       SUM (VALUE) total_user_cpu
                  FROM v$statname c,
                       v$sesstat a,
                       v$session b,
                       v$bgprocess p
                 WHERE     a.statistic# = c.statistic#
                       AND p.paddr(+) = b.paddr
                       AND b.sid = a.sid
                       AND c.name = 'CPU used by this session'
                       AND b.username NOT IN
                              ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
              GROUP BY b.sid, NVL (b.username, p.name)
              ORDER BY 3 DESC),
             (SELECT SUM (VALUE) total_cpu
                FROM v$statname c,
                     v$sesstat a,
                     v$session b,
                     v$bgprocess p
               WHERE     a.statistic# = c.statistic#
                     AND p.paddr(+) = b.paddr
                     AND b.sid = a.sid
                     AND c.name = 'CPU used by this session')
       WHERE ROWNUM < 2;


   CURSOR c8
   IS
        SELECT username, SUM (VALUE / 100) cpu_usage_sec
          FROM v$session ss, v$sesstat se, v$statname sn
         WHERE     se.statistic# = sn.statistic#
               AND name LIKE '%CPU used by this session%'
               AND se.sid = ss.sid
               AND username IS NOT NULL
               AND username NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
      GROUP BY username
      ORDER BY 2 DESC;
BEGIN
   DBMS_OUTPUT.put_line ('Database Version');
   DBMS_OUTPUT.put_line ('-----------------');

   FOR rec IN c1
   LOOP
      DBMS_OUTPUT.put_line (rec.version);
   END LOOP;

   DBMS_OUTPUT.put_line (CHR (13));
   DBMS_OUTPUT.put_line ('Hostname');
   DBMS_OUTPUT.put_line ('----------');

   FOR rec IN c2
   LOOP
      DBMS_OUTPUT.put_line (rec.host_name);
   END LOOP;

   DBMS_OUTPUT.put_line (CHR (13));
   DBMS_OUTPUT.put_line ('SGA Size (MB)');
   DBMS_OUTPUT.put_line ('-------------');

   FOR rec IN c5
   LOOP
      DBMS_OUTPUT.put_line (rec.sgasize);
   END LOOP;

   DBMS_OUTPUT.put_line (CHR (13));
   DBMS_OUTPUT.put_line ('Database Size (MB)');
   DBMS_OUTPUT.put_line ('-----------------');

   FOR rec IN c6
   LOOP
      DBMS_OUTPUT.put_line (rec.dbsize);
   END LOOP;

   DBMS_OUTPUT.put_line (CHR (13));
   DBMS_OUTPUT.put_line ('Instance start-up time');
   DBMS_OUTPUT.put_line ('-----------------------');

   FOR rec IN c2
   LOOP
      DBMS_OUTPUT.put_line (rec.starttime);
   END LOOP;

   DBMS_OUTPUT.put_line (CHR (13));

   FOR b IN (SELECT total,
                    active,
                    inactive,
                    SYSTEM,
                    killed
               FROM (SELECT COUNT (*) total FROM v$session),
                    (SELECT COUNT (*) SYSTEM
                       FROM v$session
                      WHERE username IS NULL),
                    (SELECT COUNT (*) active
                       FROM v$session
                      WHERE status = 'ACTIVE' AND username IS NOT NULL),
                    (SELECT COUNT (*) inactive
                       FROM v$session
                      WHERE status = 'INACTIVE'),
                    (SELECT COUNT (*) killed
                       FROM v$session
                      WHERE status = 'KILLED'))
   LOOP
      DBMS_OUTPUT.put_line ('Active Sessions');
      DBMS_OUTPUT.put_line ('---------------');
      DBMS_OUTPUT.put_line (
            b.total
         || ' sessions: '
         || b.inactive
         || ' inactive,'
         || b.active
         || ' active, '
         || b.SYSTEM
         || ' system, '
         || b.killed
         || ' killed ');
   END LOOP;

   DBMS_OUTPUT.put_line (CHR (13));
   DBMS_OUTPUT.put_line ('Sessions Waiting');
   DBMS_OUTPUT.put_line (CHR (13));
   DBMS_OUTPUT.put_line ('Count      Event Name');
   DBMS_OUTPUT.put_line (
      '-----      -----------------------------------------------------');

   FOR rec IN c4
   LOOP
      DBMS_OUTPUT.put_line (rec.cnt || '          ' || rec.event);
   END LOOP;

   DBMS_OUTPUT.put_line (CHR (13));


   DBMS_OUTPUT.put_line (
      '-----      -----------------------------------------------------');


   DBMS_OUTPUT.put_line (
      'TOP Physical i/o, logical i/o, memory and CPU processes');
   DBMS_OUTPUT.put_line ('---------------');

   FOR rec IN c7
   LOOP
      DBMS_OUTPUT.put_line (
            rec.category
         || ': SID '
         || rec.sid
         || ' User : '
         || rec.username
         || ': Amount used : '
         || rec.amt_used
         || ': Percent used: '
         || rec.pct_used);
   END LOOP;


   DBMS_OUTPUT.put_line (
      '------------------------------------------------------------------');


   DBMS_OUTPUT.put_line ('TOP CPU users by usage');
   DBMS_OUTPUT.put_line ('---------------');

   FOR rec IN c8
   LOOP
      DBMS_OUTPUT.put_line (rec.username || '--' || rec.cpu_usage_sec);
      DBMS_OUTPUT.put_line ('---------------');
   END LOOP;
END;
/

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

1) find the status of your rollback segments by using the following query. You will find that your tablespace status is offline. Alter the tablespace to online.

SQL> select segment_name, tablespace_name, initial_extent,status
2 from dba_rollback_segs;

SEGMENT_NAME TABLESPACE_NAME INITIAL_EXTENT STATUS
--------------            ---------------                --------------           -------
SYSTEM                  SYSTEM                       57344                      ONLINE
ASHOKAN                 SYSTEM                       16384                      OFFLINE


2) alter rollback segment to online status.

SQL> alter rollback segment ASHOKAN online;
Rollback segment altered.

Now, you should be able to create table and manipulate your tables. To the following to see whether your undo rollback segments are MANUAL or AUTO..

SQL> show parameter undo

NAME                                            TYPE       VALUE
------------------------------------ ----------- --------------------
undo_management                           string          MANUAL

ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO scope=spfile;

3) If the value of undo_management is AUTO, then do the following.

Create an UNDO tablespace to automatically create UNDO segments set online immediately.

SQL> create undo tablespace UNDOTBS2
2 datafile '/oracle/ora11/oradata/orcl/undotbs01.dbf' size 10M;
Tablespace created.

And remove your Manual UNDO Rollback segments.

SQL> create table mytable (mycolumns number) tablespace users;
Table created.

Now, still you should not have any problem.

Oracle recommends you to use the Automatic Undo for your rollback segments. If the database is set in Automatic Undo Management (AUM) mode - it must have at least one UNDO tablespace.

Notice that when the database is using Automatic Undo Management, but no UNDO tablespace is available at the time the error was received.

You could find out when the database is using Automatic Undo Management by to check the following parameters:

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- --------------------
undo_management string AUTO
undo_retention integer 900
undo_suppress_errors boolean TRUE
undo_tablespace string

ORA-00445: background process "m001" did not start after 120 seconds ---ORA-00445: background process "J003" did not start after 120 seconds

ORA-00445: background process "m001" did not start after 120 seconds 
    Incident details in: /app/u01/app/oracle/diag/rdbms/incident/incdir_3721/db1_mmon_7417_i3721.trc 
    ERROR: Unable to normalize symbol name for the following short stack (at offset 2): 
    Tue Jun 21 03:03:06 2011 
    ORA-00445: background process "J003" did not start after 120 seconds 
    or 
    Waited for process W002 to initialize for 60 seconds 
    The system appears to be running very slowly and defunct processes can appear. 
    Changes 
    REDHAT 5 kernel 2.6.18-194.el5 #1 SMP Tue Mar 16 
    Oracle 11.2.0.2 Single Instance 
    IBM: Linux on System z 
    Cause 
    Recent linux kernels have a feature called Address Space Layout Randomization (ASLR). 
    ASLR  is a feature that is activated by default on some of the newer linux distributions. 
    It is designed to load shared memory objects in random addresses. 
    In Oracle, multiple processes map a shared memory object at the same address across the processes. 
     
    With ASLR turned on Oracle cannot guarantee the availability of this shared memory address. 
    This conflict in the address space means that a process trying to attach a shared memory object to a specific address may not be able to do so, resulting in a failure in shmat subroutine. 
     
    However, on subsequent retry (using a new process) the shared memory attachment may work. 
    The result is a random set of failures 
    Solution 
    It should be noted that this problem has only been positively diagnosed in Redhat 5 and Oracle 11.2.0.2. 
    It is also likely, as per unpublished BUG:8527473,  that this issue will reproduce running on Generic Linux platforms running  any Oracle 11.2.0.x. on Redhat/OEL kernels which have ASLR.  
     
    ASLR also exists in SLES10 and SLES 11 kernels and by default ASLR is turned on, however, to date not problem has been seen on SuSE. 
     
    You can verify whether ASLR is being used as follows: 
     
     # /sbin/sysctl -a | grep randomize 
    kernel.randomize_va_space = 1 
     
    If the parameter is set to any value other than 0 then ASLR is in use. 
    On Redhat 5 to permanently disable ASLR. 
    add/modify this parameter in /etc/sysctl.conf 
    kernel.randomize_va_space=0 
    kernel.exec-shield=0 
     
    You need to reboot for kernel.exec-shield parameter to take effect.  
    Note that both kernel parameters are required for ASLR to be switched off. 

ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], []

ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], []

The Problem:

Oracle Database remains in mounted state. It can't be opened. If it is focibly opened it throws error (ora-00600) as shown below:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [],
[], [], [], [], [], [], [], []

SOLUTION:

Use the command- 'recover database' as shown below:

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2.3517E+10 bytes
Fixed Size                  2213456 bytes
Variable Size            1.4496E+10 bytes
Database Buffers         8858370048 bytes
Redo Buffers              160751616 bytes
Database mounted.
SQL> recover database;
Media recovery complete.

SQL> alter database open;

Database altered.

Bulk collect is the bulk binding syntax for select statements.(Performance tuning - PL/SQL)

Bulk Collect

Executing sql statements in plsql programs causes a context switch between the plsql engine and the sql engine.
Too many context switches may degrade performance dramatically. In order to reduce the number of these context
switches we can use a feature named bulk binding. Bulk binding lets us to transfer rows between the sql engine and the plsql engine as collections.
Bulk binding is available for select, insert, delete and update statements.

Bulk collect is the bulk binding syntax for select statements. All examples below are simplified versions of the live cases i have seen.

One of the things i usually come across is that developers usually tend to use cursor for loops to process data. They declare a cursor, open it, fetch from it row by row in a loop and process the row they fetch.


SQL> create table a_all_objects as select * from all_objects;

Table created.

SQL> select count(*) from a_all_objects;

  COUNT(*)
----------
     90154

SQL> insert into a_all_objects select * from a_all_objects;

90154 rows created.

SQL> /

180308 rows created.

SQL> commit;

Commit complete.

SQL> set timing on

SQL> declare
cursor c1 is select OBJECT_NAME from a_all_objects;
      rec1 c1%rowtype;
  begin
  open c1;
  loop
     fetch c1 into rec1;
     exit when c1%notfound;

     null;

  end loop;
  end;
  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.17
SQL>

SQL> declare
 cursor c1 is select OBJECT_NAME from a_all_objects;
  type c1_type is table of c1%rowtype;
  rec1 c1_type;
  begin
  open c1;

     fetch c1 bulk collect into rec1;


  end;
  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.37
SQL>
SQL>
SQL> alter system flush buffer_cache;

System altered.

As can be clearly seen, bulk collecting the rows shows a huge performance improvement over fetching row by row.

The above method (which fetched all the rows) may not be applicable to all cases. When there are many rows to process,
 we can limit the number of rows to bulk collect, process those rows and fetch again. Otherwise process memory gets bigger and bigger as you fetch the rows.

SQL> declare

cursor c1 is select object_name from a_all_objects;
  type c1_type is table of c1%rowtype;
  rec1 c1_type;
  begin
  open c1;
  loop
     fetch c1 bulk collect into rec1 limit 200;
     for i in 1..rec1.count loop
             null;
     end loop;
     exit when c1%notfound;
  end loop;
  end;
  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.35
SQL>