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.

Indexing NULL Value in a Table Column

As we know, a normal index does not include NULL value,  so SQL query won’t use this normal index for a SQL query with condition ‘IS NULL’.

Here is a tip for how to create an index on column with NULL value.

SQL> create index IDX_BATCH_ID_NULL on test ( BATCH_ID, 1);

Index created.

Actually the index is created by including two columns of BATCH_ID and constant number 1.

SQL> select count(*) from test where BATCH_ID is null;

COUNT(*)
----------
292

SQL> select count(*) from test where BATCH_ID is not null;

COUNT(*)
----------
172

SQL> create index idx_BATCH_ID on test(BATCH_ID);

Index created.

SQL> SET AUTOTRACE ON EXPLAIN

SQL> select MACHINE_ID from test 
     where BATCH_ID is not null and rownum<=2;

MACHINE_ID
----------------------
MACHINE_1
MACHINE_2

2 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3931117773

--------------------------------------------------------------------
| Id | Operation          | Name|Rows| Bytes | Cost(%CPU)| Time     |
---------------------------------------------------------------------
|  0 | SELECT STATEMENT   |     |   2|   130 |    2   (0)| 00:00:01 |
|* 1 |  COUNT STOPKEY     |     |    |       |           |          |
|* 2 |   TABLE ACCESS FULL|TEST | 172| 11180 |    2   (0)| 00:00:01 |
---------------------------------------------------------------------


SQL> select MACHINE_ID from test 
     where BATCH_ID is null and rownum<=2;

MACHINE_ID
---------------------------------------------
MACHINE_3
MACHINE_4

Execution Plan
----------------------------------------------------------
Plan hash value: 3931117773

-----------------------------------------------------------------
|Id| Operation        |Name|Rows|Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------
|0 | SELECT STATEMENT |    | 2  |  650 | 2 (0)      | 00:00:01 |
|*1| COUNT STOPKEY    |    |    |      |            |          |
|*2| TABLE ACCESS FULL|TEST|292 |18980 | 2 (0)      | 00:00:01 |
-------------------------------------------------------------------

SQL> create index idx_BATCH_ID_null on test ( BATCH_ID, 1);

Index created.

SQL> select MACHINE_ID from test 
    where BATCH_ID is null and rownum<=2;

MACHINE_ID
------------------------------------------------------
MACHINE_3
MACHINE_4


Execution Plan
----------------------------------------------------------
Plan hash value: 1426346817

--------------------------------------------------------------------
|Id| Operation                   |Name|Rows|Bytes|Cost(%CPU)| Time |
---------------------------------------------------------------------
|0 |SELECT STATEMENT             |    |  2 | 130 |  3   (0)|00:00:01|
|*1| COUNT STOPKEY               |    |    |     |         |        |
| 2|  TABLE ACCESS BY INDEX ROWID|TEST|292 |18980|  3   (0)|00:00:01|
|*3|   INDEX RANGE SCAN          | IDX_BATCH_ID_NULL 
                                      | 23 |     |  2   (0)|00:00:01|
----------------------------------------------------------------------

Empty String and NULL in Oracle and PostgreSQL

Oracle treats empty string as NULL

SQL> create table test ( col1 varchar(10), col2 varchar(10) );

Table created.

SQL> insert into test values ( '',null);

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from test where col1 is null;

COUNT(*)
----------
1

SQL> select count(*) from test where col2 is null;

COUNT(*)
----------
1

PostgreSQL  does not treat empty string as NULL

This could bring some code issue when migrating Oracle database to PostgreSQL.

elms=> create table test ( col1 varchar(10), col2 varchar(10) );
CREATE TABLE

testdb=> insert into test values ( '',null);
INSERT 0 1

testdb=> select count(*) from test where col1 is null;
count
-------
0
(1 row)

testdb=> select count(*) from test where col2 is null;
count
-------
1
(1 row)