Shulou(Shulou.com)06/01 Report--

1 introduction to Spotlight

Spotlight is a graphical tool for third-party performance monitoring produced by Quest. There are many Spotlight-related products, such as Spotight on Linux/Unix, Spotight on Windows, Spotlight on SQL Server, Spotlight on DB2, Spotight on Oracle and so on. Spotlight introduced in this paper is a graphical and real-time performance monitoring and diagnosis tool used in the field of oracle. It provides intuitive and visual database activity presentation capability, once a performance bottleneck occurs in a certain indicator. You can visualize it. Therefore, DBA or performance testers can simply and quickly trace the underlying causes of performance bottlenecks. In addition, Spotlight does not need to install agents on the server side, just need to create some management objects to facilitate deployment and application.

2 deploy Spotlight

The use of Spotlight depends on the oracle client. The following installation files need to be prepared before deployment and installation:

1) Oracle client instant client

Download address on the official website:

Https://www.oracle.com/technetwork/cn/database/features/instant-client/index-092699-zhs.html

2) Spotlight

Download address on the official website:

Https://support.quest.com/zh-cn/spotlight-on-oracle/10.3.3/download-new-releases

The above two tools can be installed step by step according to the prompt steps, and the detailed installation steps will not be repeated. Among them, oracle client needs to configure system environment variables PATH and TNS_ADMIN, whose value is the directory where the tnsnames.ora file is located, such as

PATH:D:\ oracle\ instantclient_11_2

TNS_ADMIN:D:\ oracle\ instantclient_11_2

3 use Spotlight for performance monitoring

Before using Spotlight for performance monitoring, you need to make some simple configurations as follows:

3.1 configure database connection information

Find the tnsnames.ora file in the installation directory of the oracle client tool, and configure the connection information of the database instance that needs to be monitored in the file, as follows:

3.2 configure monitoring users

In actual monitoring, it is recommended to create a dedicated monitoring user to obtain monitoring information, click the menu item file-Oracle User Wizard, open the user creation wizard, as shown in the figure, and then follow the prompts to enter DBA connection authentication, create a new user, enter a user name and password (for example, user name: spotlight, password: test123), and select a tablespace to complete step by step.

3.3 create a database connection

1. Click the toolbar Connect shortcut to open the connection Manager wizard, double-click the New connection icon, enter the connection name, and then open the connection properties configuration window, as shown in the following figure:

2. After opening the connection properties configuration window, you can see that the window mainly has three parts: ORACLE connection configuration, ASM connection configuration and server connection configuration. The latter two are optional. If not, do not check it. After the connection information is configured, click ok to see the main monitoring interface.

3. Introduction to the monitoring interface

When the above configuration is complete, execute the connection, and you can see the monitoring interface of Spotlight, as shown in the figure. The data flow can be observed in real time from the interface, and the overall operation of the system can be seen visually. If there are performance bottlenecks or problems in the system, the corresponding warning will be reported, and the red warning is the most serious.

Note: the green area indicates that the relevant area is normal, the × × area represents the warning that the area exceeds the standard value, and the red area indicates that there is an exception in the area.

3 Spotlight key monitoring and analysis 3.1 Top Sessions

Click the toolbar icon to open the Top Sessions panel, as shown in the figure, through which you can view the current session, such as which session takes up more resources, in order to locate database problems. You can sort by clicking on the column name, such as CPU (ms/s), which will be sorted in ascending or descending order according to CPU; select a session and double-click to view the details of the session, such as Session Details, Session Waits, Session SQL, Session Locks, etc., or right-click on a session to perform operations such as kill session, trace session, etc.

In the Session Infomation window, you need to focus on Session Waits, Session SQL, and Session Locks. As shown in the figure, their functions are:

Session Waits: find out the wait events associated with the session

Session SQL: find out the recently executed sql statements related to the session

Session Locks: lock information related to the session to see if it is blocked

3.2 Top SQL

Click the toolbar icon to open the Top SQL panel, as shown in the figure, through which you can view the resource-intensive sql statements in the system, select the corresponding row, and view the SQL information details.

This panel is mainly used to find SQL statements with bottlenecks, such as which SQL takes a long time, which SQL disks have more read and write, and low cache hit rate, etc., and then optimize for the problematic SQL, which can be optimized first from syntax, and then from business logic.

3.3 Activity

Click the toolbar icon to open the Activity panel, as shown in the figure. The panel shows IO, active session, waiting events and other information in the form of a histogram. You can view specific waits events, Locks and other information through other tab pages. The interface data is generally used to assist analysis.

3.4 Icano and configure memory

Click the toolbar icon to open the session O and configuration memory panels, as shown in the figure. Through these two panels, you can view the session, IO of data files, database memory configuration and usage, etc., generally used for auxiliary analysis.

In the I session O interface, by caring about which tablespace or data file has higher physical read and write, and which session executes the SQL that causes these IO exceptions. From this interface, we can locate the IO exception of the session, and then analyze and locate it with the SQL of the Top Session interface. Of course, it may also be the IO problem caused by the table structure design, which is analyzed in combination with the usage of Segment's IO.

In the memory configuration interface, you need to pay attention to the SGA utilization, shared pool, buffer cache utilization and hit rate. The library hit is mainly viewed in shared pool, and the hit rate is mainly focused on buffer cache. If the hit interest rate is too low, there may be problems.

4 other resources

About python learning, sharing and exchange, the author opened the official account of Wechat [Little Python Community]. Interested friends can follow, welcome to join, establish our own small circle and learn python together.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.