Monitor the running status of Aix and database management systems on small machines until performance is optimized (SQL statement optimization and hardware troubleshooting)

Commands under Aix

1) topas checks the operating status of the Operating System

2) nmon (c -- CPU, m -- Memory, d -- Disk) checks the three conditions

Commands in Oracle

Tip: The following views monitor the database status on the production machine in real time. The query results change with the database system status at all times.

(1)

Selectopname, target, start_time, last_update_time, SQL _hash_value from V $ session_longopsorder by start_time DESC

Select * from V $ sqltext wherehash _value = 529107050 order by piece

The preceding two statements are used to monitor which statements in the system scan the entire table, and then find the specific SQL statements in the hash_value to the 2nd view.

Note that the two operations to scan the entire table must be fast; otherwise, the 2nd sentences cannot be found, because the cache will be cleared quickly, so when the database system is busy, use these two sentences for monitoring, because the cache will not be cleared so quickly.

 

 

 

Select status from V $ session

Check the number of connections in the database connection pool used by the client. "active" indicates the connected connection (Session), and "inactive" indicates the connection (Session) that is not used in the connection pool ), if the connection pool reaches the maximum number of connections and all connections are occupied, the system will query the database and wait in queue for the release of the new idle connection in the connection pool.

(2)

Select substr (B. SQL _text, 100), count (*) from V $ sqlarea B having count (*)> group by substr (B. SQL _text,) orderby 2 DESC

It is used to query in real time which SQL statements of the same SQL statement being executed by the database system on the production machine exceed 80 characters and more than 100 SQL statements are being executed.