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

The problems in the use of the database may be caused by any one of the components such as tablespaces, file systems, data files, processes, etc.

It may even be due to the poor performance of a SQL statement that causes system performance problems. Therefore, when there is a problem with the database, a thorough investigation of the root cause of the problem becomes repetitive and complicated work.

However, before troubleshooting problems, as a performance testing technician, you need to know what tools to use and which database performance metrics to monitor in order to gain results, such as oracle monitoring performance metrics and using monitoring analysis methods:

Indicator name

Fault problem description and diagnosis Analysis

Oracle data access data mode

Full table scan per second

(Full Scans/sec)

Index description

Refers to the number of full table scans per second. A full table scan can be a basic table scan or a full index scan. Because full table scanning takes a lot of time, if the frequency of full table scanning is too high, the CPU utilization will be too high, which will affect the performance.

Index diagnosis

1. Obtain SQL questions by using Spotlight.

2. Obtain the problematic SQL of the object through database log analysis

Index analysis

1. Optimize SQL query performance by using Spotlight or event profiler to analyze whether it is necessary to scan the full table.

2. For the process of querying table data in the case of a large number of historical data, we can improve the query efficiency by analyzing whether to carry out partition table or not.

Other

Index description

SQL syntax writing problem, resulting in excessive consumption of resource CPU/ memory

Index diagnosis

1. Obtain SQL questions by using Spotlight.

2. Obtain the problematic SQL of the object through database log analysis

Index analysis

Problems such as nesting too many subqueries or sorting syntax can be implemented by modifying SQL.

Parameter configuration Analysis of ORACLE Database instance

Buffer wait

Buffer wait

Index description

Refers to the waiting probability of getting Buffer in the buffer

Index diagnosis

Obtain the corresponding metric values by using Spotlight or snapshot

Index analysis

By using Spotlight, the performance indicator should be close to 100%.

Buffer hit ratio

Buffer Hit%

Index description

Hit rate of exponential data blocks in data buffer

Index diagnosis

Obtain the corresponding metric values by using Spotlight or snapshot

Index analysis

The value should generally be more than 90%

Shared area hit rate

SharedPool%

Index description

Usage of shared pools

Index diagnosis

Obtain the corresponding metric values by using Spotlight or snapshot

Index analysis

By using Spotlight analysis, this value should generally be more than 90%

Memory sort utilization

Index description

Refers to the rate at which sorting operations take place in memory. When the query needs sorting, now memory sorting, insufficient memory will use temporary table space for disk sorting, resulting in IO problems, affecting efficiency.

Index diagnosis

Obtain the corresponding metric values by using Spotlight or snapshot

Index analysis

This value is usually close to 100% for better performance.

Log file synchronization

Log file sync

Index description

This wait event means that when a session completes a thing, it must wait until the LGWR process writes the redo information of the session from the log buffer to the log file before execution can continue.

Index diagnosis

Obtain the corresponding metric values by using Spotlight or snapshot

Index analysis

The waiting time for this event is too long, probably because the data is submitted or updated too frequently or the log is written too long at a time.

Redo buffer waiting rate

Redo wait

Index description

Refers to the wait probability of getting Buffer in the Redo buffer

Index diagnosis

Obtain the corresponding metric values by using Spotlight or snapshot

Index analysis

The performance indicator should be close to 100%

Lock (Locks) performance counter in oracle

Queue waiting

(enqueue (cs))

Index description

This locking mechanism protects shared resources, such as data in records, to prevent two people from updating the same data at the same time. Enqueue includes a queuing mechanism, namely FIFO (first-in, first-out) queuing mechanism.

Index diagnosis

Obtain the corresponding indicator values by using Spotlight, snapshot, and LR monitoring

Index analysis

If the enqueue wait event is significant, then the appropriate optimization method needs to be taken according to the enqueue wait type.

Prevent multiple transactions from using resources concurrently and use as few locks as possible to increase performance overhead.

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.