Oracle

This is some information about Oracle which may be useful.

I've been having this annoying error a lot with with Oracle XE, the free and lightweight version of the Oracle 10g database server.

java.sql.SQLException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found

It turns out to be an Oracle bug with how Oracle XE in particular monitors processes.

SQL> SELECT count(*) FROM v$process;
count(*)
--------
107
 
SQL> SELECT name, value FROM v$sysstat WHERE name LIKE '%logon%'

A work around is to increase the number of system processes:

$  sqlplus /nolog
SQL> connect / AS sysdba
SQL> ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE;
SQL> SHOW parameter process  ## shows the number of processes, restart Oracle first

You can also edit the init.ora file.

You will need to restart Oracle for these changes to take effect

$ /etc/init.d/oracle restart

How do you know the correct number of processes for your Oracle instance? There is no point setting this size too high. Once you start paging performance will to go hell in a handcart. The 'ps' command would seem to be useful however the information it gives on process memory size does not separate out memory shared with other processes such as the program code itself. If you have a number of Oracle processes the figures will add up to many times the memory on the system despite there being free memory available.

Run the free command to determine how much physical memory and swap space you have free.

$ free -m
             total       used       free     shared    buffers     cached
Mem:          //3948//       3273        675          0        167       2331
-/+ buffers/cache:        **774**       **3174**
Swap:         //8191//         50       8141

The important figures are on the second line: buffers/cache. Linux will use free memory for disk caching so the used value will always be high. On line two we see that 774Mb of memory is in use with 3174Mb of real memory free. We are using 50Mb of Swap with 8141Mb free. For optimum performance used memory should be less than the free physical memory. If we exceed the total memory and swap space we will get an “out of memory” panic.

vmstat reports information about processes, memory, paging, block IO, traps, and cpu activity. In this example we refresh the display every 5 seconds.

[rgisuser@localhost ~]$ vmstat 5
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  0  51568 617488 170372 2387496    0    0     4    13    2    1  9  1 90  0  0
 0  0  51568 617488 170388 2387488    0    0     0    23 1070  247  1  0 99  0  0
 1  0  51568 677204 170388 2387496    0    0     0    19 3063 4553  0  3 97  0  0

Swap In (si) and Swap Out (so) shows if there has been swapping. That is memory has been moved from or to the swap partition on the disk in order to free space for other processes to run. This is suboptimal as it consumes disk i/o and cpu resources. These figures should ideally be 0. If they are high, add more physical memory to the system or turn the application. The r/b/w columns show runnable processes, those waiting to be swapped and those blocked on i/o. Ideally the total of these figures should be less than the total number of CPUs or cores on your computer.

Bytes in (bi) and Bytes Out (bo) show total disk activity including swapping. User (us), System (sy) and Idle (id) show CPU usage in % for all the applications.

This is a bit of information for identify database locks using sqlplus logged in as the oracle sysdba:-

This sql statement will tell you the sql that threads are currently executing

SQL> SELECT /*+ ordered */ b.username, b.serial#, d.id1, a.sql_text
FROM v$lock d, v$session b, v$sqltext a
WHERE b.lockwait = d.kaddr AND a.address = b.sql_address AND a.hash_value = b.sql_hash_value;
 
USERNAME SERIAL# ID1 SQL_TEXT
------------------------------ ---------- -------------------------------------------------------------------------
GAPENT 201 196644 UPDATE SYS_RECORD SET FKSTATUSID='12'

We see the user GAPENT is trying to update a column in the SYS_RECORD.

You can drill down a bit further. The following statements shows what is running and blocking. We see there are 2 transactions running at the same time to the same block of information at the same time, this isn't necessarily a problem depending on the initrans value

SQL> SELECT /*+ ordered */ username, v$lock.sid, trunc(id1/power(2,16)) rbs, bitand(id1,to_number('ffff', 'xxxx'))+0 slot, id2 seq, lmode, request
FROM v$lock, v$session
WHERE v$lock.type = 'TX' AND v$lock.sid = v$session.sid;
 
USERNAME SID RBS SLOT SEQ LMODE REQUEST
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
GAPENT 10 3 36 5960 6 0
 
GAPENT 22 3 36 5960 0 6
 
SQL> SELECT xid, xidusn, xidslot, xidsqn, STATUS, start_scn
FROM v$transaction
ORDER BY start_scn;
 
XID XIDUSN XIDSLOT XIDSQN STATUS START_SCN
---------------- ---------- ---------- ---------- ---------------- ----------
0300240048170000 3 36 5960 ACTIVE 7864739

With this statement we see that two users are trying to update the same row (LMODE/REQUESTS different):-

SQL> SELECT /*+ ordered */ username, v$lock.sid, trunc(id1/power(2,16)) rbs, bitand(id1,to_number('ffff','xxxx'))+0 slot, id2 seq, lmode, request
FROM v$lock, v$session
WHERE v$lock.type='TX' AND v$lock.sid = v$session.sid;
 
USERNAME SID RBS SLOT SEQ LMODE REQUEST
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
GAPENT 10 3 36 5960 6 0
 
GAPENT 22 3 36 5960 0 6

and one of them is blocked (VALID):

SQL> SELECT sid, blocking_session, username, blocking_session_status
FROM v$session
WHERE username='GAPENT' ORDER BY blocking_session;
 
       SID BLOCKING_SESSION USERNAME BLOCKING_SE
---------- ---------------- ------------------------------ -----------
        22 10 GAPENT VALID
        12 GAPENT NO HOLDER
        13 GAPENT NO HOLDER
        14 GAPENT NO HOLDER
        27 GAPENT NO HOLDER
        19 GAPENT NO HOLDER
        20 GAPENT NO HOLDER
        21 GAPENT NO HOLDER
        10 GAPENT NO HOLDER
        15 GAPENT NO HOLDER

This would tell if there are table locks blocking say CDA and what the SQL being executed was.

See the book: Oracle Database 10g Performance Tuning Tips and Techniques, for more information.

As I don't have a Toad license I was looking around for a nice SQL front end. I tried using the SQL add-on for Eclipse 3 but it got in the way of coding. Anyway take a look at Squirrel SQL which seems to be pretty good.

http://www.squirrelsql.org/