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

Problem phenomenon

In the afternoon, the developer reported that the database access to a test environment was very slow, so let me help analyze the reason.

Just installed SQLBooster, through it to analyze, by the way familiar with its use.

Cause analysis get wait event

If the database is slow, first look at the waiting events. Click "event ranking" on the main interface of SQLBooster, and the interface shows that TOP5 waiting events are as follows:

Among them, row cache lock ranks first.

Analyze blocking session

Session lock appears, through the "blocking tree" of the main interface, you can see the session blocking situation.

However, these session blocks are changing dynamically and do not execute SQL.

Check the current active session as follows. The session can be filtered flexibly according to the conditions:

Analyze the details of waiting events

For the row cache lock wait event, we will get the P1 parameter of the session.

Use the P1 parameter to query the parameter name in v$rowcache and find that it is "dc_users".

Related to dc_users, usually caused by a user login. From the audit view, looking at the login audit for the day, it is found that the error code ORA-1017 accounts for the vast majority.

This error is the wrong username and password.

So the problem is clear: a client uses jdbc to connect to the database, but reconnects repeatedly because the password is misrecorded in the configuration file.

And connection pooling is configured, so there are multiple connection requests to connect to the database in an instant. In turn, the response of the database is slow.

Treatment method

Add the IP of the client to the access blacklist in sqlnet.ora, and the performance problem disappears. Then contact the developer of the client to modify the jdbc configuration file.

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.