I have recently recommended a client to increase PROCESSES parameter from 2000 to 8000. The question was how much memory will that use? The answer is surprisingly small. The first snippet shows the initial state:
QL> show parameter processes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes integer 1 db_writer_processes integer 1 gcs_server_processes integer 0 global_txn_processes integer 1 job_queue_processes integer 80 log_archive_max_processes integer 4 processes integer 320 SQL> select name,value from v$sga 2 / NAME VALUE -------------------- ---------- Fixed Size 9144984 Variable Size 2214592512 Database Buffers 2734686208 Redo Buffers 7630848
After that, I increased the PROCESSES for 1000 and restarted the instance:
SQL> alter system set processes=1320 scope=spfile; System altered. SQL> startup force ORACLE instance started. Total System Global Area 4966052456 bytes Fixed Size 9146984 bytes Variable Size 2214592512 bytes Database Buffers 2734686208 bytes Redo Buffers 7626752 bytes Database mounted. Database opened.
With 320 processes, the fixed size was 9144984 bytes. With 1320 processes, it was 9146984 bytes. The difference is 2000 bytes. So, it gives only 2 bytes per process. That looks a bit too small. Alternatively, we could sum the column sizes from V$PROCESS. Unfortunately, that too gives an unreliable result
SQL> select sum(vsize(column_name)) from user_tab_columns where table_name='V_$PROCESS';
SUM(VSIZE(COLUMN_NAME))
__________________________
200
Elapsed: 00:00:00.003
Just the trace file name can take up to 513 bytes so 200 bytes is not a credible result. Unfortunately, V_$PROCESS is a view, not a table, so it is not possible to extract AVG_ROW_LEN from DBA_TABLES. To calculate the best possible size, we need a little script:
#!/usr/bin/env python3
""" This script prints the row size of V$PROCESS table
Author: Mladen Gogala
Date : 04/07/2021
Version: 1.0
"""
import argparse
import sys
import re
import cx_Oracle
cols = []
parser = argparse.ArgumentParser(
description='V_PROCESS: describe and print the row size of V$PROCESS table')
parser.add_argument("-u", "--username", dest="username", required=True,
help='Oracle connection using <usr>/<pwd>@<tns> syntax')
args = parser.parse_args()
row_size = 0
try:
con = cx_Oracle.connect(args.username)
query = con.cursor()
query.execute("select * from v$process")
for entry in query.description:
srch=re.search("DB_TYPE_([A-Z]+)",str(entry[1]))
ftype=srch.group(1)
print(
f"Column name: {entry[0]} Type: {ftype} Size: {entry[2]}")
row_size += int(entry[2])
print(f"Row size={row_size}")
except cx_Oracle.DatabaseError as ora_err:
oerr = ora_err.args[0]
print(f"Oracle error: {oerr}")
sys.exit(-1)
This will give a lavish upper estimate on the size of the V$PROCESS table. This table is allocated when the instance is started, based on the PROCESSES parameter and cannot be changed until the the instance is restarted. So, here is the output:
mgogala@umajor python]$ ./v_process.py -u system/qwerty@ora19c Column name: ADDR Type: RAW Size: 8 Column name: PID Type: NUMBER Size: 127 Column name: SOSID Type: VARCHAR Size: 24 Column name: SPID Type: VARCHAR Size: 24 Column name: STID Type: VARCHAR Size: 24 Column name: EXECUTION_TYPE Type: VARCHAR Size: 20 Column name: PNAME Type: VARCHAR Size: 5 Column name: USERNAME Type: VARCHAR Size: 15 Column name: SERIAL# Type: NUMBER Size: 127 Column name: TERMINAL Type: VARCHAR Size: 30 Column name: PROGRAM Type: VARCHAR Size: 48 Column name: TRACEID Type: VARCHAR Size: 255 Column name: TRACEFILE Type: VARCHAR Size: 513 Column name: BACKGROUND Type: VARCHAR Size: 1 Column name: LATCHWAIT Type: VARCHAR Size: 16 Column name: LATCHSPIN Type: VARCHAR Size: 16 Column name: PGA_USED_MEM Type: NUMBER Size: 127 Column name: PGA_ALLOC_MEM Type: NUMBER Size: 127 Column name: PGA_FREEABLE_MEM Type: NUMBER Size: 127 Column name: PGA_MAX_MEM Type: NUMBER Size: 127 Column name: NUMA_DEFAULT Type: NUMBER Size: 127 Column name: NUMA_CURR Type: NUMBER Size: 127 Column name: CPU_USED Type: NUMBER Size: 127 Column name: CON_ID Type: NUMBER Size: 127 Row size=2269
This was done on Oracle 19.10 on Oracle Linux 7.9 x86_64. So, increasing the PROCESSES parameter for 1000 increases the memory consumption at maximum for 2.2 MB. This is a bit too much since V$ tables map SGA structures. SGA structures don’t use database representation of numbers they use 64 bit integers, which means that every “NUMBER Size: 127” in the output above should be replaced by “8”. However, I will take it at face value since even with 127 bytes per number it really isn’t all that much today, when memory is counted in Gigabytes. It is hard to understand reluctance of some database administrators to increase the processes parameter.
For those who would like to know what the number looks like when “127” is replaced by “8”, it’s 1079. The above script can be trivially modified to replace “127” by “8”. However, that may not be the whole story since there may be some unmapped fields in the SGA structure. That is why I choose to operate with 2KB per row in the process table.
