tailieunhanh - oracle Applications DBA Field Guide phần 4

Tài liệu tham khảo về công nghệ thông tin dành cơ sở dữ liệu máy tính cho các bạn ngành tin học tham khảo. | CHAPTER 3 MONITORING AND TROUBLESHOOTING 63 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_ 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 -1 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. 64 CHAPTER 3 MONITORING AND TROUBLESHOOTING 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. rip The high active sessions threshold should be periodically evaluated to determine whether it is still relevant to your organization. Database trends should be evaluated. Identifying High CPU Consuming Sessions A typical cause of high active sessions in the database is