Forum Stats

  • 3,874,702 Users
  • 2,266,767 Discussions
  • 7,911,956 Comments

Discussions

Initialization/Session parameter for HINT indicator.

ajallen
ajallen Member Posts: 1,796
edited Jan 11, 2016 5:50PM in Database Ideas - Ideas

Some COTS applications use hints more liberally than they probably should. Especially since the vendor's software has to support varying data volumes across the tables and several optimizer upgrades. If we had the ability to set an initialization and session HINT marker (default is +) then we could easily turn off hints across a database or session and substitute our own. The acceptable values for non-default HINT markers would be limited to two certain special characters. Something like ++, &&, **, $$, ##. Using a double special character for the non-default indicator would eliminate any misunderstanding by the optimizer of the intent.

ctrieb
7 votes

Active · Last Updated

Comments

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

    Vendors using hints is always going to be a problem, but how would you introduce an override for an application that only the vendor can change?

  • ajallen
    ajallen Member Posts: 1,796

    Vendors using hints is always going to be a problem, but how would you introduce an override for an application that only the vendor can change?

    By having an initialization parameter that allows us to set our own HINT indicator that is not +. If the parameter is set to something other than + then the optimizer will ignore hints marked/identified by + and only process those marked/identified with our own flag - perhaps **. Now vendor supplied hints will be ignored without having to touch the supplied code.

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond

    If my app vendor put hints in their SQL and their SQL is not performing correctly, then I can do two things. 1) leverage Plan Stability or 2) send the performance issue back to the vendor.

  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge

    What do you think the

    "_optimizer_ignore_hints" parameter is used for? No need for that idea. Besides, you can always overrule any hint of a developer being a DBA.