Hi. My name is Mathew Phan, Systems Consultant In this video, I'm going to demonstrate how you can use Spotlight on Oracle to quickly identify and resolve database locks.
Spotlight on Oracle is running on my desktop and connected to a couple Oracle instances. A user of mine notified me that they're not able to execute their application against my Oracle environment. Instead of going back and forth troubleshooting with my user, I could just take a look at Spotlight and instantly see that my ORCL database is flashing in red. In fact, if I set up email alerts in Spotlight, I could have been notified of the issue before the user even experienced it.
Nonetheless, with Spotlight, I can diagnose the issue visually rather than running a bunch of scripts against my database trying to figure out where the issue is. I can quickly isolate what part of my database is having the bottleneck. One of my metrics, block changes, is in red. If I hover my mouse over it, Spotlight is telling me that the block wait alarm is going off. The average session in my database is spending 99% of its time waiting for a lock to be released, which is above my normal threshold.
To learn more, I click on the metric and it will pop up a brief description of the alarm, possible causes, and possible resolutions. Spotlight will also guide me to what screen I should check out next. Here it's suggesting that I check out the blocking locks drill down on the session activity page. So I'll go ahead and select that. On this screen, Spotlight presents me with the sessions that are tangled in locks in the tree format.
The session at the root here is blocking out the other sessions below it. By selecting a session, I can see all the data that Spotlight has on it, such as who's running it, from what system or application, the resources it's using, and the actual current statement that Oracle is processing. I can get even more granular information on these other tabs.
If I select one of these sessions, they appear to have been in the system for a few minutes now. In the pie graph, you can see that it's spending a vast majority of its time waiting for a lock to be released. The current statement window tells me that the user is trying to update this Quest SOO customer table. When I go back and take a look at the session above it, I can see that in this case, looks like a developer is incorrectly using the DBMS lock sleep command in their code and they've stepped away from their computer.
This issue is pretty straightforward. I reviewed the current statement of the session holding the lock and I've deemed that it's OK to go ahead and kill this user's session immediately and that no data will be lost. I can do so from Spotlight with a right click and kill session immediately. I'll do the same for this other session running here, after I review their sessions properties and current statement.
I would then educate the developers who caused the issue not to use that sleep command. Or I can revoke their privileges to issue the command in the first place. Once the troublesome sessions have been killed, the other sessions below it are able to proceed and this page will clear out. When I go back to my home page, after some time the averages will start to fall to normal levels and the color of the alerts will go from orange to yellow then to green.
So with Spotlight, I was able to quickly find an issue in my database, correct it, without having to run a lot of manual queries, parsing them, and analyzing the results myself. This saves me a lot of time and trouble and helps me minimize the downtime for my users.
03:39