Oracle Processes And Sessions

The processes configuration parameter sets the upper limit on the number of processes (including background processes). Each user session can use one or more processes.

Determine the current settings with the following queries.

select name, value from v$parameter where name in ('processes', 'sessions');

NAME
----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
processes
300

sessions
600

In this query, column SESSIONS_HIGHWATER is useful for determining if the running instance is close to the limit.

SQL> select * from v$license;

SESSIONS_MAX SESSIONS_WARNING SESSIONS_CURRENT SESSIONS_HIGHWATER USERS_MAX
------------ ---------------- ---------------- ------------------ ----------
0 0 161 246 0

Oracle Utility maxmem

maxmem is a utility that ships with the unix version of the Oracle database. It is useful for showing available free memory on the unix machine.

maxmem is in $ORACLE_HOME/bin, and takes no parameters.

$ uname -a
HP-UX host21 B.11.11 U 9000/800 1288319306 unlimited-user license

$ swapinfo -t
Kb Kb Kb PCT START/ Kb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 35651584 0 35651584 0% 0 - 1 /dev/vg21_SWAP/lvol1
reserve - 20312372 -20312372
memory 27341052 3836680 23504372 14%
total 62992636 24149052 38843584 38% - 0 -

$ maxmem
Memory starts at: 9223372041149874176 (8000000100020000)
Memory ends at: 9223372047592194047 (800000027fffffff)
Memory available: 6442319871 (17ffdffff)

Oracle SQL via dbms_job

For long-running SQL, it can be useful to use dbms_job.submit so that the SQL will be detached from the client terminal. A SELECT statement would need to write results out to another table. Errors will be recorded to the database alert.log.



Create a test, in file named file.sql:

set lines 100
create table test (cola int);
select * from user_jobs;

VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno, 'insert into test values (1);');
commit;
END;
/

print jobno
select job,what from user_jobs;

exec dbms_lock.sleep(10)
select * from test;

select job,what from user_jobs;

Submit the test:

sqlplus -s user@SID @file.sql


Table created.


no rows selected


PL/SQL procedure successfully completed.


JOBNO
----------
208


JOB
----------
WHAT
----------------------------------------------------------------------------------------------------
208
insert into test values (1);



PL/SQL procedure successfully completed.


COLA
----------
1


no rows selected

HP-UX Memory Page Size Tuning With chatr

Useful links.

HP-UX Kernel Tuning and Performance Guide, from 2000. Mostly versions 9 and 10, with a section on 11.0.

chatr information.
chatr +pd=256M +pi=16M progname.x
This will increase the data page size to 256MB and the instruction page size to 16MB. This will result in fewer virtual memory pages, and hence requires fewer page translation entries in the tlb.

chatr information.
To improve Translation Lookaside Buffer (TLB) hit rates in an
application running on a PA 8000-based system, use the
following linker or chatr virtual memory page setting options:
+pd size -- requests a specified data page size of 4K bytes,
16K, 64K, 256K, 1M, 4M, 16M, 64M, 256M, or L. Use L to
specify the largest page size available. The actual page
size may vary if the requested size can not be fulfilled.
+pi size -- requests a specified instruction page size. (See
+pd size for size values.). The default data and instruction
page size is 4K bytes on PA-RISC systems.

The PA-RISC 2.0 architecture supports multiple page sizes, from
4K bytes to 64M bytes, in multiples of four. This enables large
contiguous regions to be mapped into a single TLB entry. For
example, if a contiguous 4MB of memory is actively used, 1000
TLB entries are created if the page size is 4K bytes, but only 64
TLB entries are created if the page size is 64K bytes.
Examples:
To set the virtual memory page size by using the linker:
ld +pd 64K +pi 16K /opt/langtools/lib/crt0.o myprog.o -lc
To set the page size by using chatr:
chatr +pd 64K +pi 16K a.out
See also “Performance Optimized Page Sizing”:
http://www.unixsolutions.hp.com/products/hpux/pop.html

Personal Content Aggregation Sites

I recall something like this in 1999 that was specific to Internet Explorer.

Netvibes
Pageflakes
Confluence Commons