Find Oracle SQL Execution History Details

DBAs are always asked about the database performance issues, which are mostly caused by some SQL queries’ execution plan changes.

This post demonstrates how to get a SQL query performance details like executions, disk/physical read/write, cpu time, elapsed time and waiting time, etc.

With the help of those statistics of a specific SQL query, DBA can easily identify the issue, and remediate it by applying SQL plan baselines.

Subscribe to get access

Read more of this content when you subscribe today.

What to Check Before Truncate a Table?

This post shows what need to be checked before truncating a table in Oracle. Otherwise it might cause unexpected tables’ data loss and application outage in mission critical environment.

Subscribe to get access

Read more of this content when you subscribe today.

How to Find Bind Variable Values of Oracle SQL Query

A couple of ways to retrieve bind variables in real time or from history

Oracle database captures a SQL query bind variables and their  values periodically, and the default capture bind variables interval is 900 seconds(15 minutes), which is modifiable but facing performance concerns.

Parameter Description                 Session Value  Instance Value
------------------------------------- -------------- --------------- 
_cursor_bind_capture_interval              900           900
interval(in seconds) between two bind 
capture for a cursor
SQL>alter system set "_cursor_bind_capture_interval"=30 scope=both;

Oracle intends to minimize the impact on database performance, and makes the capturing interval a pretty big value( like 15 minutes). So it is not a very reliable method to retrieve bind variables from Oracle database views.

Please note you need have licenses of “Oracle Diagnostics & Tuning Packs” if you query dba_hist_****** and v$sql_monitor in this post.

There are a couple of ways to retrieve bind variables in real time or from history.

Subscribe to get access

Read more of this content when you subscribe today.

How to Set SQL or Oracle Database to Single-User Mode and How to Check Single User or Multi User Mode

In Oracle database, place the instance in restricted mode.  The users already connected to the database will not get disconnected. You need to manually kill these sessions.

SQL> alter system enable restricted session;

OR

Startup in restricted mode.

SQL> startup restrict;

Check the instance mode

SQL> select logins from v$instance;

LOGINS
----------
RESTRICTED

In SQL database,  Below are the two most popular ways to set up database to single-user mode in SQL Server:

Using SQL Server Management Studio

Right-click the database to change, and then click Properties -> click the Options page->From the Restrict Access option, select Single.
If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, click Yes.

Using Transact-SQL

USE master;
GO
ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

Check SQL Database Single User or Multi User Mode

a)  Select ServerProperty(‘IsSingleUser’)

It should return 1, 0, or Null.

1 = Single user.
0 = Not single user
NULL = Input is not valid, or an error.

use MyDatabase
go
Select ServerProperty('IsSingleUser')

b) Select from sys.database,  it should retuen:

  • SINGLE_USER
  • MULTI_USER
SELECT user_access_desc 
FROM sys.databases 
WHERE name = 'MyDatabase'

3) From SSMS, Right-click the database , and then click Properties -> click the Options page->From the Restrict Access option.

Schema Logon Trigger to Block Logon Sessions from Toad, SQL Developer, Sqlplus….

In production environment, sessions from developers using tools like Toad, SQL Developer, Sqlplus, etc can be blocked by after schema logon trigger.

Subscribe to get access

Read more of this content when you subscribe today.