The first step in determining an expected number of active sessions for the
system is to monitor the day-to-day activity in the database for a period of
time. This will allow you to determine the number of active sessions that are
expected for your environment.
The following query will return the number of sessions in an active state:
SQL>select count(1) from v$session where status='ACTIVE';
Once trending data is captured and analyzed, the following code can be
used to alert the Applications DBA if the number of active sessions exceeds
the predetermined threshold, creating an environment of high active
sessions:
#Script used to monitor high active sessions
#THRESHOLD is the maximum number of high active sessions
#connected to the database at one time
THRESHOLD=$1
LOGFILE=/tmp/high_active_$ORACLE_SID.txt
sqlplus -s apps/apps << EOF
set heading off
spool $LOGFILE
select '$ORACLE_SID - High Active Sessions exceeds Threshold -
'||count(1)
from v\$session
where status='ACTIVE'
having count(1) > $THRESHOLD
union
select 'no rows'
from v\$session
where status='ACTIVE'
having count(1) <= $THRESHOLD;
spool off
exit
EOF
RETURN_CODE=`grep "Threshold" $LOGFILE | wc -l`
if [ $RETURN_CODE -eq 0 ]
then
exit 0
else
exit 1
fi
Upon being notified that the active session count is high in your
instance, the next step is to determine what caused the unexpected increase
in the number of active sessions. Often this may occur when one or more
sessions are consuming a lot of system resources. This can cause a bottleneck
in the system, causing other sessions in the database to remain in an
active state because they are unable to get enough resources to complete.
(Assessing high CPU consuming queries is discussed in the following section.)
Sometimes you may see high active sessions due to one-time processing or
increased overall activity in your database.
Communicate with your user community to understand what processing
may be occurring that is not normally scheduled. Also, get an understanding
of usage requirements if you notice upward or downward trends in database
sessions.
Apps DBA Portal Tip: The high active sessions threshold should be periodically evaluated to determine whether it is still relevant to your organization. Database trends should be evaluated.Labels: Identifying High Active Sessions |
