1 2 Previous Next 16 Replies Latest reply: Feb 27, 2012 12:55 PM by Jonathan Lewis Go to original post RSS
      • 15. Re: V$SQL_SHARED_MEMORY don't return rows
        rp0428
        Jonathan wrote
        >
        At present, the following hinting is probably going to give you what you want if you simply query v$sql_shared_memory. Be VERY careful that you get the query block name reference right.


        select
             /*+
                  leading(@sel$5c160134 c@sel$3 h@sel$3 )     
                  use_nl(@sel$5c160134 h@sel$3)
             */
             *
        from
             v$sql_shared_memory
        ;
        >
        Ok - I waited a while to see if any of the bigger fish would take the bait but I guess they're full already.
        So since I've never been one to be shy asking about something I don't understand would you mind expounding on that just a tad bit more? I, for one, don't understand that hint structure at all.
        • 16. Re: V$SQL_SHARED_MEMORY don't return rows
          Jonathan Lewis
          rp0428 wrote:

          So since I've never been one to be shy asking about something I don't understand would you mind expounding on that just a tad bit more? I, for one, don't understand that hint structure at all.
          As from 10g a fully qualified hint includes the query block name of the location where it should be applied. This is an improvement on the older "global hint" approach that Oracle had for passing hints into view with a more generic application since it allows you to specify hints for query blocks that have appeared due to optimizer transformations of your query.

          Check the Performance Tuning Guide and Reference for hints, especially the qb_name hint. Also see http://jonathanlewis.wordpress.com/2007/06/25/qb_name/ for an example.

          Regards
          Jonathan Lewis
          http://jonathanlewis.wordpress.com
          Author: <b><em>Oracle Core</em></b>
          1 2 Previous Next