Wednesday, January 16, 2008

Analyzing Long Running SQL Queries

If you are able to identify long running SQL queries in your database and want to trace them back to the report or query in the application that caused the SQL to be generated, use the following method:

Consider the following SQL statement:

select t1.spec_id ,t1.SYS_TYPE1 , t1.triUserMessageFlagTX , t1.triIdTX ,
t1.triNameTX , t1.triIdTX , t1.triNameTX , t1.triRequestedByTX , t1.triCreatedSY ,
t1.triStatusCLOBJID from
M_TRIREQUEST t1 where t1.SYS_OBJECTID>0 and t1.SYS_TYPE1 = 1.0002583E7
and t1.SYS_PROJECTID = 1.0
and isnull(t1.triStatusCL, -9223372036854775808)
not in ('Retired','Upload Error','Template', 'History', 'Deleted')
and t1.spec_id in ( select ta1.ass_spec_id from ibs_spec_assignments ta1
where ta1.spec_id in (208133)
union select ta1.spec_id from
ibs_spec_assignments ta1 where
ta1.ass_spec_id in (208133) )
order by upper(t1.triIdTX) ,upper(t1.triNameTX) ,upper(t1.triIdTX) ,upper(t1.triNameTX)

Take a fairly unique column, triRequestedByTX for example, and find all the places that is being used in conjunction with the table/view name: M_TRIREQUEST by running the following SQL query:

select * from REP_TEMPLATE_COLUMNS where field_name = 'triRequestedByTX'
and table_name = 'M_TRIREQUEST'

Then, select the rep_template_id and find the names of the reports from the rep_template_hrd table

select rep_name from rep_template_hdr where rep_template_Id in (17879,25749)

This could be done in one statement like:

select rep_name, rep_template_id from REP_TEMPLATE_HDR where rep_template_id in
(
select distinct rep_template_id from REP_TEMPLATE_COLUMNS
where field_name = 'triRequestedByTX'
and table_name = 'M_TRIREQUEST'
)

Now, open TRIRIGA, go into report manager, click on “open” for any report. Press CTRL-N to open a new window, where you can edit the URL. Replace the report template id in the URL with the one provided above and the offending report will open.

Once you find the offending report you may want to reconsider how you have your filters set up, or you may want to remove it from a portal section or frequently used object. An alternative to removing it is to just remove it from the first tab of an object so it does not run as often.

No comments: