• State Suggested Answer
  • Replies 4 replies
  • Answers 2 answers
  • Subscribers 7 subscribers
  • Views 331 views
  • Users 0 members are here
Options
Related

Sifting Through Workload Analysis

bill mentzer

Good Afternoon,

I'm getting pinged for some rather specific analysis by my Business Systems team, supporting Operations.

They're trying to identify when a specific change happened to a table and by whom.  The Vendor's self auditing doesn't catch this particular event, so in come the DBAs after the fact.  The Workload Analysis tool is very helpful in this regard.

However, the results are rather difficult to sort through when your qualifier is the actual SQL run.  I can eye-ball it.  But that's so inefficient.

Is there any way to query the Spotlight databases to retrieve the SQL Text from the sessions, the same way the WA tool does?  I figure if it can pull it, I might be able to, too.  All the Export does is give me the table data displayed.  I need the SQL to complete my R&A.

Thanks so much for any advice you can offer.

-Bill

  • 0

    Hi Bill,
    Thanks for using our User Forums. 
    Have you reviewed this How to query Spotlight’s Statistics Repository database for historical transactions blog? You can export the data that you're looking for directly from Repository database using the sqlfullconnectionslist collection. The blog was written for older version of Spotlight but the collections still exists for reporting. Ensure to check the comments sections for additional information. I hope this helps out.

  • 0 in reply to Gita Sharifi

    Thanks Gita!  That looks like it has what I was looking for.  I greatly appreciate the post & the direction.  I'll post back with my progress.

  • 0 in reply to Gita Sharifi

    Thanks again, Gita, for the reference.  Unfortunately, the construct doesn't seem to contain what I'm looking for.  Since we're on 13.6.0.2506, I had to use some of the instructions in the comments of the Blog post.  But I'm still unable to access a sqlstatement column or anything similar.  Even tables that do exist don't return the columns queried:

    The disparity between what's queried in the sproc and what returned suprised me, in the above figure.  SQLCONNECTIONS was the only table that even came close to what's referenced in blog post, but obviously isn't what we're looking for.

    Since no SQLCONNECTIONSLIST, nor SQLFULLCONNECTIONSLIST is available in my get_tables results, am I correct in assuming we don't have the correct items selected in the Collect and Store scheduling?  We probably won't affect any changes to our Spotlight Enterprise configuration at this time, unfortunately.

    I was really hoping I could query the actual SQL Statements returned from the Workload Analysis section:

    They list in a second in the tool, which is awesome.  But when I'm trying to track down what may have affected a specific column, eyeballing isn't the way to do it.  Maybe add a string search feature to the Tool?

    If you're thinking I should be able to get this via the method(s) in the Blog Post, but I'm just not executing the process correctly, I suppose I could open a support ticket.  

    Thanks again for your sage guidance!

    -Bill

  • 0 in reply to bill mentzer

    Hi Bill,
    You might already by aware but if you select the statement on the workload analysis drilldown on the right side, you can then select the SQL tab and view the entire query at the bottom. For exporting the data, it would be best to contact the Support team.