Clarity

CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 2/8/2011

Today, we'll take a short break from the details of Oracle and SQL Server performance and look at a related question that comes up when looking at top running queries.

Do you ever see queries and wonder where they came from? This may be tricky as queries can come from a number of places in Clarity. However, if the query is an NSQL query, there is a fairly easy way to track it down. This is definitely useful, since NSQL queries can be added to Clarity at any time by users who have access to the Admin Tool -> Studio area of the product.

Here's a method for locating the origin of such queries.

1) Identify the query from an AWR or SQL Server Top Queries report. (Sometimes DBA's will bring a query to attention.)

My example query right below, came from a coworker, who asked, "Where does this query originate from?"

select * from (select row_number() over ( order by report_name asc, run_date desc) row_num, count(*) over () num_rows, q.* from ( SELECT /*+ RULE */ jr.id id, jr.job_id job_id, jr.output_path report_id, job.name report_name, cts.name report_type, trunc(jr.end_date) run_date FROM cmn_captions_nls cts , cmn_sch_job_runs jr , cmn_sch_jobs job , cmn_sch_job_definitions jd , CMN_SEC_CHK_USER_INST_V0 pv WHERE job.id = jr.job_id AND jr.job_id = pv.object_instance_id AND pv.object_id = :"SYS_B_0" AND pv.permission_code = :"SYS_B_1" AND pv.user_id = :v0 AND jr.status_code <> :"SYS_B_2" AND jd.id = job.job_definition_id AND jd.job_type = : "SYS_B_3" AND cts.pk_id = jd.id AND cts.table_name = :"SYS_B_4" AND cts.language_code = :v1 AND job.is_visible = :"SYS_B_5" AND job.status_code = :"SYS_B_6" AND :"SYS_B_7"=:v2 and :"SYS_B_8"=:"SYS_B_9" ) q) q where q.row_num between :v3 and :v4 order by q.row_num

Queries that drive a grid or list of results will typically have a similar form to the one above. The "select * from (select row_number() over " is your key clue that the query is likely an NSQL originating one.

2) Identify something in the query that is unique. I usually choose something including an alias name. If you notice a custom table, that would also be a good choice. In our example above I decided that "jr.end_date" was unique enough to narrow down the query.

3) Run the following query to determine if the query is an NSQL query stored in the Clarity NSQL tables. Use you unique string and place it into the like clause below.

i.e.

select query_code, source, nsql_text from cmn_nsql_queries nsql, CMN_GG_NSQL_QUERIES gg
where nsql_text like '%jr.end_date%'
and gg.CMN_NSQL_QUERIES_ID = nsql.id

4) Run the query. In my example I received the following results.

[font=Courier New]QUERY_CODE SOURCE NSQL_TEXT
------------- ------------ -------------------------------------------------------------------------------------
cop.reports csk.niku.com SELECT /*+ RULE */ @select:dim:user_def:implied:report:jr.id:id@,
@select:dim_prop:user_def:implied:report:jr.job_id:job_id@,
@select:dim_prop:user_def:implied:report:jr.output_path:report_id@[font]

5) Navigate to the Admin Tool Page of Clarity and choose "queries" under CA Clarity Studio

6) In the query filter, you can now copy the QUERY_CODE result from above and place it into the Query ID field. Click "Filter" and you should find your query.

Hope you find this useful!

-shawn

This technique (searching the [font=Courier New]CMN_GG_NSQL_QUERIES[font] table) is also handy for doing some simple "impact analysis"* - i.e. answering the question "where is my custom-attribute used in my custom portlets?". You'd need to start out with the custom attribute's "id" and use that in the [font=Courier New]nsql_text[font] wildcard test above.

(* - Of course you also need to consider where a custom attribute might be used in a report / database routine etc)

Great Tips Shawn and Dave!

Thanks,
Chris

To assist in quickly resolving problems knowing where queries come from is exceptionally useful, so, a recommended "best practice" from me is to include the query id of any custom NSQL queries IN the query itself so it displays automatically in any sql traces.

I typically suggest doing this at the end, like this:

select
   @select:dim:user_def:implied:AISTEPS:step_id:step_id@
,  @select:dim_prop:user_def:implied:AISTEPS:version_id:version_id@
.....
from (
     /* actual sql query is here */
    select
      step.id              as step_id
    , version.id           as version_id
         ) sqlwarp
where @filter@
/*
query id = a_sample_nsql_id
*/

Additionally "portletize" the ability to search through your NSQL: this makes finding queries that need updating or fixing much more reliable and faster. I typically add such portlets to pages under the Clarity Studio heading using pages based on the 'Admin Page Template' .

Excellent practice!

Wouldn't that be nice if Clarity was enhanced to add a comment in the query that included information that identified the query. :wink:

-shawn