Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Mike.

Asked: June 12, 2018 - 1:24 pm UTC

Last updated: June 15, 2018 - 1:12 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hello,

I'm creating a script to automatically generate plan reports usings DBMS_XPLAN.DISPLAY_CURSOR, and to do so I want to put in a standard comment in the table and query it via dba_source and v$sql. e.g.

select /* xplan_my_test_pkg01 */ * from dual;


When I use a normal comment I'm able to identify PLSQL blocks by searching v$sql, but not the actual sql statement. Therefore I've updated the comment to be a fake hint which allows me to identify the query correctly in v$sql;

select /*+ xplan_my_test_pkg01 */ * from dual;


Based on this, I was wondering is there any negative effects that could occur by leaving the fake hint in the query, as I'm assuming it shouldn't affect the optimiser; or should it be removed before going to a production environment.

Thanks

with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/gtn4w6u67e6do36bgizwhod2x

and Connor said...

An invalid hint is simply ignored. And I've lost track of the number of times I've seen invalid hints in code that were *meant* to be valid :-)

But this approach is obviously not without some (minor) risk, namely

1) one day we might make invalid hints raise an error
2) one day your comment might become a valid hint

So my advice would be to strip it out before production. If you have a good naming standard, I'd imagine you could achieve that with automation and hence make it a no-effort task.

Others welcome to offer their thoughts.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Identifying statements from v$sql

lh, June 14, 2018 - 7:08 am UTC

Hi

For 'batch' type programs one can use setting of module to identify sql statements.( dbms_application_info.set_module)


But if this oltp-type system setting module before executing each service would require strong coding discipline. (what actually is performance impact for setting module/action? Also dbms_application_info is not 'stack' based, so using this for nested calls can cause problems).

I have found it usefull to recommend that these oltp services would use hint as way to identify where sql-statement originated. Helps solving performance problems.



lh

whats in a name?

Racer I., June 14, 2018 - 10:58 am UTC

Hi,

We use both approaches.

SELECT /*+ FULL(a) "package/class/script here_i_am.sql" */

The /*+ stops Oracle (in PLSQL) from stripping the comments (bad habit) as the OP noticed. We never had (knowingly) any problems with our comments becoming hints, but the quoting and placing last might help with that.

It's also explicitely meant for production, so problematic statement can quickly be connected with their source. Say via fisheye. Developer discipline is mostly addressed by code generation.

We also use dbms_application_info : module/action/clientinfo. They are somewhat short though, so we use IDs mostly (webservices, batches und messages have those). This is done via the corresponding frameworks, so requires no developer disclipine except for occasional manual scripting. This works well with ASH/AWR.

Granularity below that is mostly handled via java-/plsql-stackframes and logging.

regards,
Connor McDonald
June 15, 2018 - 1:12 am UTC

Certainly I'm a big fan of exploiting module/action/client_info to track SQL
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.