Forum Stats

  • 3,874,696 Users
  • 2,266,767 Discussions
  • 7,911,952 Comments

Discussions

New session level hidden parameter to NOT IGNORE failed hints.

Rodrigo Jorge DBA
Rodrigo Jorge DBA Member Posts: 86 Bronze Badge
edited Jan 11, 2016 5:50PM in Database Ideas - Ideas

A lot of DBA's comes to a situation where we put some hints in a query but we do some typo or semantic conflict/error.

So, instead of showing an error, oracle wisely executes the query ignoring the hints. I'm OK with that.

The problem is that in many situations we DO want to know that Oracle is not obeying our hints! Why not give us a way to know that, maybe throwing an error?

Maybe Oracle could create a session level hidden parameter "_optimizer_disallow_failed_hints" that default to FALSE, but if we set it to TRUE in one session, the query would fail because the hints are invalid.

Thus, we could easily track and check that we made some mistake.

Example:

alter session set _optimizer_disallow_failed_hints=true;

select /*+ PARALLEL (x,20) */ *

from   scott.emp t;

ORA-09876 - Your hint is invalid.

Thanks.

Rodrigo Jorge DBABPeaslandDBActriebkayemtop.gunLothar FlatzborneselFranck PachotkulikouskiulohmannWilliam RobertsonCloudDBMatheus BoesingSven W.berxHemant K ChitaleMKJ10930279jnicholas330
19 votes

Active · Last Updated

«1

Comments

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond

    I like this idea. I've always wondered why erroneous hints don't return some sort of error message. That being said, there are tools that will put a marker in the hint to make it easier to find the SQL statement. SQLT is one such tool from Oracle Corp.

    Cheers,

    Brian

    Rodrigo Jorge DBAMatheus Boesing
  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    Would be nice for v$sq to have a flag saying the hint was ignored....

    Matheus Boesing
  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    I like the idea, but what happens when you're using an application that is full of deprecated hints?

    The hints are ignored, you can't change the application because it's written by someone else, and you get log files full of hint errors. Alternatively users are spammed with hint errors.

    Matheus Boesing
  • Rodrigo Jorge DBA
    Rodrigo Jorge DBA Member Posts: 86 Bronze Badge

    I like the idea, but what happens when you're using an application that is full of deprecated hints?

    The hints are ignored, you can't change the application because it's written by someone else, and you get log files full of hint errors. Alternatively users are spammed with hint errors.

    In that case, as the default value of this new hidden parameter is to not generate any error, nothing would happen. Notice that the parameter can be changed at session level, so you could generate errors only for your tests sessions.

  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge

    That would be really helpful, particularly for testing.

  • kulikouski
    kulikouski Member Posts: 56

    It would be nice, but what does happen if a hint would be ingnored by optimizer (no error) ?
    May be an extention for explain plan (like as statistics) with details about errors/ignorance would be an alternative way.

    Like as (the same for hints):

    ...

    Note

    -----

       - Warning: basic plan statistics not available. These are only collected when

    William Robertson
  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown
    edited Jan 15, 2016 7:19AM

    It would be nice, but what does happen if a hint would be ingnored by optimizer (no error) ?
    May be an extention for explain plan (like as statistics) with details about errors/ignorance would be an alternative way.

    Like as (the same for hints):

    ...

    Note

    -----

       - Warning: basic plan statistics not available. These are only collected when

    If you mean add comments to the "Notes" section of the plan, then that's exactly how I would like to see this.

    Something along the lines of:

        Note
        ----
        Unrecognised hint NOLOGGING was ignored.

    kulikouskiSven W.
  • kulikouski
    kulikouski Member Posts: 56

    If you mean add comments to the "Notes" section of the plan, then that's exactly how I would like to see this.

    Something along the lines of:

        Note
        ----
        Unrecognised hint NOLOGGING was ignored.

    Yes, It's exactly what I mean.

  • Matheus Boesing
    Matheus Boesing Member Posts: 46 Blue Ribbon
  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown

    if it is implemented as William suggests, then I'm in favor for it. If an error is raised then ABSOLUTLY NOT. A hint should never have the ability to generate an error and stop execution.

    berx