SQL Parameter Sniffing

There are several articles out there on parameter sniffing. I had never come across this issue until this week. Here are some of my notes. Here is a good article for definitions and examples. http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/

Lessons learned

1. In certain cases you will need to utilize optimizer hints. The Optimizer Engine is real good, but its not perfect
2. Use the WITH RECOMPILE in a STORED PROCEDURE Create statement with caution. The system overhead involved in recompiling the plan every execution may eliminate the perofrmance gains of picking the correct plan.
3. Make sure you check your index fragmentation and statistics. Just to eliminate the obvious.
4. Learned some other techniques to reduce parameter sniffing (using local variables).

select COUNT(*) from dbo.table1

 

select * from table1

--Hardcoded Date
select * from table1 with (nolock) where CollectionDate >= '4/24/2012 4:04PM'

--Date Variable
DECLARE @FromDate datetime
set @FromDate = '4/24/2012 4:04PM'
select @FromDate select * from table1 with (nolock) where CollectionDate >= @FromDate

Using an Index HINT
sourcecode language="sql"]
--Date Variable with INDEX HINT
DECLARE @FromDate datetime
set @FromDate = '4/24/2012 4:04PM'
select @FromDate
select * from table1 with (nolock  INDEX(IX_table1_CollectionDate)) where CollectionDate >= @FromDate
[/sourcecode]

CREATE PROCEDURE testSearch2
@FromDate
datetime
AS
select * from table1 with (nolock)
where CollectionDate >= @FromDate

--NO Recompile exec testSearch2 '4/24/2012 4:04PM'



Then I dropped the procedure and recreated it with the RECOMPILE Option:
DROP PROCEDURE testSearch2
GO
CREATE PROCEDURE testSearch2
@FromDate datetime
WITH RECOMPILE
AS select *
from table1 with (nolock)
where CollectionDate >= @FromDate

--AFTER RECOMPILE
exec testSearch2 '4/24/2012 4:04PM' 

			

			
						

Grant Select on all tables Oracle

Here is a SQL Script that will generate a script to allow you to grant select on all the tables of a specified owner.

select 'grant select on '||owner||'.'||table_name||' to other_user;' from all_tables where owner = 'some_user';

The resulting script can be executed to do a mass grant on all tables.

Tracking down Oracle Sessions

This query will show the current users and the OS PID…you can use this to then run a top command to watch the session.

ACTIVE users

select s.sid || ',' || s.serial# "SID/SERIAL"
, s.username
, s.osuser
, p.spid "OS PID"
, s.program
, s.status
, s.last_call_et
, sq.physical_read_bytes
, sq.physical_write_bytes
, sq.sql_text
from v$session s LEFT OUTER JOIN v$sql sq ON sq.sql_id = s.sql_id
, v$process p
Where s.paddr = p.addr
and s.status = 'ACTIVE'
and s.osuser <> 'oracle'
order by to_number(p.spid)

ALL Users (except oracle)

select s.sid || ',' || s.serial# "SID/SERIAL"
 , s.username
 , s.osuser
 , p.spid "OS PID"
 , s.program
 , s.status
 , s.last_call_et
 , sq.physical_read_bytes
 , sq.physical_write_bytes
 , sq.sql_text
 from v$session s LEFT OUTER JOIN v$sql sq ON sq.sql_id = s.sql_id
 , v$process p
 Where s.paddr = p.addr
 and s.osuser <> 'oracle'
 order  by to_number(p.spid)

From there you can use the OS PID to run a top command to see what kind of resources the session is consuming

top -p PID

Or you can go the opposite way. Run a top command and then track down the Oracle Sessions using the PID

Find Active Users

select s.sid || ',' || s.serial# "SID/SERIAL"
 , s.username
 , s.osuser
 , p.spid "OS PID"
 , s.program
 , s.status
 , s.last_call_et
 , sq.physical_read_bytes
 , sq.physical_write_bytes
 , sq.sql_text
 from v$session s LEFT OUTER JOIN v$sql sq ON sq.sql_id = s.sql_id
 , v$process p
 Where s.paddr = p.addr
 and p.spid IN (22057, 28919, 21972)
 order  by to_number(p.spid)

Oracle Index Information

Some queries to help find out infomration about specific indexes.

This will show you the indexes for a specified table

select	i.index_name
,	i.tablespace_name
,	ceil(s.bytes / 1048576) "Size MB"
from 	dba_indexes i
,	dba_segments s
where 	i.index_name = s.segment_name
and 	table_name like '&table'
order 	by 2, 1

This will show you the columns of a specific index

select 	column_name
from 	dba_ind_columns
where 	index_name = '&index'
order 	by column_position

This will give you some extra details about the index

select index_name, num_rows, last_analyzed from dba_ind_statistics where table_name = 'Table Name';