Query Oracle sessions processes transactions cursors
With the article “Query Oracle sessions processes transactions cursors”, we have the possibility to identify the maximum number of sessions or processes or transactions or cursors, their usage and percentage.
Several times I have been contacted by the monitoring group due to the increase in the number of sessions.
If the number increases until reaching the session limit, the database will no longer accept incoming connections.
The sessions inside the database will continue to work, but new sessions will return the error ORA-00018 “maximum number of sessions exceeded”.
In case we find ourselves in this situation, we have to kill the oldest sessions that can be identified by the following command:
ps -ef | grep dbname | grep LOCAL=NO
Once we have managed to enter, we can check the current usage of sessions and processes next to their maximum limit and usage percentage.
The output of the “Sessions processes transactions cursors” procedure:
TAG PARAMETER CURRENT MAXIMUM USAGE%
----- ---------------------------------------------------------------- ------- ------- ------
PSTAT open_cursors 33 300 11
PSTAT processes 52 300 17
PSTAT sessions 33 472 6
PSTAT transactions 0 519 0Query Oracle for display sessions, processes, transactions and open cursors:
set verify off
set termout off
set feedback off
set echo off
set serveroutput on size 999999
set linesize 132
set termout on
declare
MISSING_PARAMETER exception;
MISSING_STAT_OPENCURS exception;
--
MaxIdxParameter integer;
type parameter_name is record (
name varchar2(64),
value integer);
type parameter_list_array is table of parameter_name index by binary_integer;
parameter_lst parameter_list_array;
type parameter_type is record (
current_value integer);
type parameter_array is table of parameter_type index by binary_integer;
parameter_stat parameter_array;
--
function LoadParameterList return number is
cursor parameter_list is
select 1 parameter_id, name, to_number(value) value from v$parameter where name = 'open_cursors'
union all
select 2, name, to_number(value) from v$parameter where name = 'processes'
union all
select 3, name, to_number(value) from v$parameter where name = 'sessions'
union all
select 4, name, to_number(value) from v$parameter where name = 'transactions';
begin
MaxIdxParameter := 1;
for rec_parameter_list in parameter_list loop
if rec_parameter_list.parameter_id = MaxIdxParameter then
parameter_lst(MaxIdxParameter).name := rec_parameter_list.name;
parameter_lst(MaxIdxParameter).value := rec_parameter_list.value;
MaxIdxParameter := MaxIdxParameter + 1;
else
return MaxIdxParameter;
end if;
end loop;
MaxIdxParameter := MaxIdxParameter - 1;
return 0;
end LoadParameterList;
--
procedure LoadParameterStat is
stat_id number;
cursor parameter_list (p_stat in number) is
select 1 parameter_id, max(count(*)) current_value from v$open_cursor group by sid
union all
select 2, count(*) current_value from v$process
union all
select 3, count(*) current_value from v$session
union all
select 4, count(*) current_value from v$transaction;
begin
begin select statistic# into stat_id from v$statname where name = 'opened cursors current';
exception when no_data_found then raise MISSING_STAT_OPENCURS;
end;
for rec_parameter_list in parameter_list(stat_id) loop
parameter_stat(rec_parameter_list.parameter_id).current_value := rec_parameter_list.current_value;
end loop;
end LoadParameterStat;
--
procedure OutputStats is
ParameterId integer;
PctUsage number;
begin
dbms_output.put(rpad('TAG',5));
dbms_output.put(' '||rpad('PARAMETER',64));
dbms_output.put(' '||rpad('CURRENT',7));
dbms_output.put(' '||rpad('MAXIMUM',7));
dbms_output.put(' '||rpad('USAGE%',6));
dbms_output.new_line;
dbms_output.put(rpad('-',5,'-'));
dbms_output.put(' '||rpad('-',64,'-'));
dbms_output.put(' '||rpad('-',7,'-'));
dbms_output.put(' '||rpad('-',7,'-'));
dbms_output.put(' '||rpad('-',6,'-'));
dbms_output.new_line;
for ParameterId in 1..MaxIdxParameter loop
dbms_output.put(rpad('PSTAT',5));
dbms_output.put(' '||rpad(parameter_lst(ParameterId).name,64));
PctUsage := trunc(100*parameter_stat(ParameterId).current_value/parameter_lst(ParameterId).value);
dbms_output.put(' '||lpad(parameter_stat(ParameterId).current_value,7));
dbms_output.put(' '||lpad(parameter_lst(ParameterId).value,7));
dbms_output.put(' '||lpad(PctUsage,6));
dbms_output.new_line;
end loop;
end OutputStats;
begin
-----------------------------------------------------
-- catalogazione parametri sistema
-----------------------------------------------------
if LoadParameterList != 0 then raise MISSING_PARAMETER;
end if;
LoadParameterStat;
-----------------------------------------------------
-- output statistiche
-----------------------------------------------------
OutputStats;
exception
when MISSING_PARAMETER then raise_application_error(-20000,'missing parameter id='||MaxIdxParameter);
when MISSING_STAT_OPENCURS then raise_application_error(-20001,'missing statistic: "opened cursors current"');
end;
/So, once we retrieve the result, we will immediately have the possibility to identify whether the number of sessions or cursors has reached saturation or not.
If you are nearing saturation, you need to try to identify the bottleneck. In most cases, the issue is caused by a database lock situation.
Database locks can be a common cause of bottlenecks and issues within an Oracle database.
It is important to monitor the number of sessions and processes to ensure that the database can handle incoming connections without reaching its limit.
In addition, regularly checking the usage and percentage of sessions, processes, transactions, and cursors can help to identify potential performance issues and prevent saturation.
By using the “Query Oracle sessions processes transactions cursors” article, database administrators can quickly and easily identify these potential issues and take steps to resolve them, such as killing older sessions. It is important to regularly monitor and optimize the performance of Oracle databases to ensure that they are running smoothly and efficiently.
To view my guides on CRS commands click here!
At this LINK, however, you will find SQL queries
Stay updated on our activities by joining our community:
For any doubt, do not hesitate to comment on the article.
0 Comments