Flash Tip: Running Profiler on a Busy SQL Server
Flash Tip: Running Profiler on a Busy SQL Server
http://www.microsoft.com/technet/abouttn/flash/tips/tips_020205.mspx
How to list triggers on a database in SQLServer?
Using the following SQL statement, it will list triggers on database. It also prints trigger name, table the trigger is on, is it a insert, update and delete triggering event, after and instead of trigger, if the trigger is enabled or disabled
SELECT trigger_name = name
, trigger_owner = USER_NAME(uid)
, table_name = OBJECT_NAME(parent_obj)
, isinsert = OBJECTPROPERTY( id, ‘ExecIsInsertTrigger’)
, isupdate = OBJECTPROPERTY( id, ‘ExecIsUpdateTrigger’)
, isdelete = OBJECTPROPERTY( id, ‘ExecIsDeleteTrigger’)
, isafter = OBJECTPROPERTY( id, ‘ExecIsAfterTrigger’)
, isinsteadof = OBJECTPROPERTY( id, ‘ExecIsInsteadOfTrigger’)
, status = CASE OBJECTPROPERTY(id, ‘ExecIsTriggerDisabled’) WHEN 1 THEN ‘Disabled’ ELSE ‘Enabled’ END
FROM sysobjects
WHERE type = ‘TR’
Output:
tr_test1_insert dbo test1 1 0 0 1 0 Enabled
How to find the session and SQL statement that is not closing the ResultSet?
There was an issue the other day where the application session ran out of open cursors resulting the following error
Exception: java.sql.SQLException: ORA-01000: maximum open cursors exceeded
So using the following SQL we found the session had reached the max limit of cursors (open_cursors) and thereby found the SQL which was causing the issue and
SQL> select s.username, s.sid, a.value
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current'
and s.username = 'SCOTT';
USERNAME SID COUNT(1)
—————————— ———- ———-
SCOTT 143 17
SCOTT 89 1000
To display the current limit set in the database
SQL> show parameter open_cursors
NAME TYPE VALUE
———————————— ———– ——-
open_cursors integer 1000
So using V$open_cursor and joining with V$session we were able to find the SQL that wasn’t closing the cursor.
select vo.sql_id, count(1) from v$open_cursor vo, v$session vs
where vs.sid = vo.sid and vs.username = 'SCOTT'
group by vo.sql_id order by count(1);
SQL_ID COUNT(1)
————- ———-
…
9gs5t9bj7q686 21
2na94tfc9ukuk 1000
SQL> select sql_text from v$sqltext where sql_id = ’2na94tfc9ukuk’;
SQL_TEXT
—————————————————————-
select * from table1
Lists foreign key(s) on a table
Using the following anonymous store procedure one can list the foreign key on a table and it’s column. It also recursively traverses tables to see if there are other tables dependent on it’s primary key.
set serveroutput on format wrapped
DECLARE
l_vc2_table_owner VARCHAR2(30) := upper('&1');
l_vc2_table_name VARCHAR2(30) := upper('&2');
PROCEDURE get_ind_columns(l_vc2_owner IN VARCHAR2, l_vc2_index_name IN VARCHAR2, l_vc2_pad IN VARCHAR2
, l_vc2_out_table_key OUT VARCHAR2)
IS
CURSOR l_cur_ind_columns IS
SELECT column_name FROM dba_ind_columns
WHERE index_owner = l_vc2_owner AND index_name = l_vc2_index_name
ORDER BY column_position;
l_vc2_columns VARCHAR2(100);
BEGIN
FOR l_rec IN l_cur_ind_columns
LOOP
IF l_vc2_columns IS NULL THEN
l_vc2_columns := l_rec.column_name;
ELSE
l_vc2_columns := l_vc2_columns || ' ' || l_rec.column_name;
END IF;
END LOOP;
l_vc2_out_table_key := ' (' || l_vc2_columns || ')';
END;
PROCEDURE print_pk(l_vc2_table_owner IN VARCHAR2, l_vc2_table_name IN VARCHAR2, l_vc2_pad IN VARCHAR2) IS
CURSOR l_cur_main IS
SELECT di.owner, di.index_name
FROM dba_indexes di, dba_constraints dc
WHERE di.table_owner = l_vc2_table_owner and di.table_name = l_vc2_table_name
AND dc.owner = di.owner and dc.constraint_name = di.index_name
AND di.uniqueness = 'UNIQUE';
l_rec_main l_cur_main%ROWTYPE;
l_vc2_ind_columns VARCHAR2(1000);
FUNCTION check_fk(l_vc2_owner IN VARCHAR2, l_vc2_index_name IN VARCHAR2)
RETURN BOOLEAN
IS
CURSOR l_cur_fk IS
SELECT dc.owner, dc.table_name, dc.constraint_name
FROM dba_constraints dc, dba_cons_columns dcc
WHERE dc.r_owner = l_vc2_owner AND dc.r_constraint_name = l_vc2_index_name
AND dc.owner = dcc.owner AND dc.constraint_name = dcc.constraint_name;
l_rec l_cur_fk%rowtype;
l_b_status BOOLEAN;
BEGIN
OPEN l_cur_fk;
FETCH l_cur_fk INTO l_rec;
IF l_cur_fk%FOUND THEN
l_b_status := TRUE;
ELSE
l_b_status := FALSE;
END IF;
CLOSE l_cur_fk;
RETURN l_b_status;
END;
PROCEDURE print_fk(l_vc2_owner IN VARCHAR2, l_vc2_index_name IN VARCHAR2,
l_vc2_table_owner IN VARCHAR2, l_vc2_table_name IN VARCHAR2,
l_vc2_pad IN VARCHAR2)
IS
CURSOR l_cur_fk IS
SELECT dc.owner, dc.table_name, dc.constraint_name
FROM dba_constraints dc, dba_cons_columns dcc
WHERE dc.r_owner = l_vc2_owner AND dc.r_constraint_name = l_vc2_index_name
AND dc.owner = dcc.owner AND dc.constraint_name = dcc.constraint_name;
l_rec l_cur_fk%rowtype;
CURSOR l_cur_dcc(l_vc2_owner IN VARCHAR2, l_vc2_constraint_name IN VARCHAR2) IS
SELECT column_name FROM dba_cons_columns
WHERE owner = l_vc2_owner AND constraint_name = l_vc2_constraint_name
ORDER BY position;
l_vc2_cons_columns VARCHAR(500);
BEGIN
OPEN l_cur_fk;
FETCH l_cur_fk INTO l_rec;
IF l_cur_fk%FOUND THEN
WHILE l_cur_fk%FOUND
LOOP
l_vc2_cons_columns := '';
FOR l_rec_dcc IN l_cur_dcc(l_rec.owner, l_rec.constraint_name)
LOOP
IF l_vc2_cons_columns IS NULL THEN
l_vc2_cons_columns := l_rec_dcc.column_name;
ELSE
l_vc2_cons_columns := l_vc2_cons_columns || ' ' || l_rec_dcc.column_name;
END IF;
END LOOP;
dbms_output.put_line(l_vc2_pad || ' Table FK: ' || l_rec.owner || '.' || l_rec.table_name
— || ' ' || l_rec.constraint_name
|| ' (' || l_vc2_cons_columns || ')');
print_pk(l_rec.owner, l_rec.table_name, l_vc2_pad || ' ');
FETCH l_cur_fk INTO l_rec;
END LOOP;
END IF;
CLOSE l_cur_fk;
END;
BEGIN
OPEN l_cur_main;
FETCH l_cur_main INTO l_rec_main;
IF l_cur_main%FOUND THEN
WHILE l_cur_main%FOUND
LOOP
IF check_fk(l_rec_main.owner, l_rec_main.index_name) THEN
l_vc2_ind_columns := '';
get_ind_columns(l_rec_main.owner, l_rec_main.index_name, l_vc2_pad, l_vc2_ind_columns);
dbms_output.put_line(l_vc2_pad || 'Table: ' || l_vc2_table_owner || '.' || l_vc2_table_name
|| ' ' || l_vc2_ind_columns);
print_fk(l_rec_main.owner, l_rec_main.index_name, l_vc2_table_owner, l_vc2_table_name, l_vc2_pad);
END IF;
FETCH l_cur_main INTO l_rec_main;
END LOOP;
END IF;
END;
BEGIN
print_pk(l_vc2_table_owner, l_vc2_table_name, '');
END;
/
Output:
Table: SCOTT.TABLE1 (TABLE1_KEY)
Table FK: SCOTT.TABLE2 (TABLE2_FKEY)
Table: SCOTT.TABLE2 (TABLE2_KEY)
Table FK: SCOTT.TABLE3 (TABLE3_FK)
