Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90

    Unanswered: Identifying App SQL

    Anyone doing App development have any techniques to map SQL back to the App?

    I'm in a J2EE Oracle 10.2 environment, with both Toplink generated SQL and ad-hoc in-line SQL being run.

    My thought is to append a short comment at the end of Ad-hoc SQL with a code for the module. This is to help track poorly performing SQL back to the actual code.

    Thoughts or ideas, anyone?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I am not sure I understand your question. You would like to look at SQL statements on the server (e.g. enterprise manager) and find out from which application they were sent?

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If you wrote a logon trigger which populated APPLICATION_INFO "intelligently", this might provide a path to a more complete solution.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    Quote Originally Posted by shammat
    look at SQL statements on the server (e.g. enterprise manager) and find out from which application they were sent?
    Yes - the problem I have is that the developers don't write SQL - they write Java

    I see poorly written, or flat-out wrong SQL from the database side - I have to hunt down the actual java code that it's coming from. Showing the end SQL to the developers usually doesn't help me.

    (They speak java. I talk SQL. So far we've been communicating with grunts and gestures... GROG LIKE BIND VARIABLES...)

    The Application_info is another conversation - I want to get client_id, module etc set so I can effectively trace.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I fully understand you
    Whenever I see SQL generated by the socalled "sophisticated" O/R mapping tools, I feel like looking at a complete idiot's SQL.

    We are using Hibernate and it usually takes a long time to convince Hibernate to write efficient SQL. I don't think you'll have a chance checking the statements on the server side. I have no idea how Toplink works, but with Hibernate we can set a property "showsql=true" which then prints out the generated stuff, that combined with the HQL query in the application logfiles gives a pretty decent hint on where the SQL is generated and where to start optimizing.

    And I do require from my developers that they are capable of finding the part of the application that creates the dumb SQL when I give it to them.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •