Re: Is there a way to dump all the plans considered by optimizer into log?

[Rick Hillegas]

If we decide to make this part of the product, I would recommend using 
the new optional tools feature rather than adding yet another syscs_diag 
procedure. New optional tools are easier to add than new system 
procedures. Something like the following would work:

-- this installs optimizer tracing
call syscs_util.syscs_register_tool( 'optimizerTracing', true );
...

-- the following procedure, installed by the call above,
-- might have some optional string varargs specifying how the output
-- should be formatted and where it should be written.
-- we could add those args in a later rev when we have more
-- experience with this tool.
-- but a first rev wouldn't need to be that fancy.
call printOptimizerTrace();

-- this turns off optimizer tracing
call syscs_util.syscs_register_tool( 'optimizerTracing', false );

I would recommend creating a new JIRA where we can discuss the api for 
optimizer tracing.

Thanks,
-Rick


On 2/28/13 6:36 PM, Mamta Satoor wrote:
If we do decide to make this available as part of Derby release, the
work can go in as part of existing jira DERBY-837.

thanks,
Mamta

On Thu, Feb 28, 2013 at 5:41 PM, Bryan Pendleton
<bpendleton.de...@gmail.com>  wrote:
I didn't find any easy way to enable it (like setting a property). So
what I ended up with, was to define two stored procedures:

      public static void trace() {
          org.apache.derby.iapi.db.OptimizerTrace.setOptimizerTrace(true);
      }

      public static void printTrace() {

System.out.println(org.apache.derby.iapi.db.OptimizerTrace.getOptimizerTraceOutput());
      }

      s.execute("create procedure trace() language java parameter style
java external name '" + getClass().getName() + ".trace'");
      s.execute("create procedure print_trace() language java parameter
style java external name '" + getClass().getName() + ".printTrace'");

And then call trace() before the statement to trace, and printTrace()
after:

      s.execute("call trace()");
      s.execute("select 1 from sys.sysschemas natural join sys.systables");
      s.execute("call print_trace()");

Might we consider permanently including these, and putting them in, say,
the SYSCS_DIAG schema, so it would be something like:

         s.execute("call SYSCS_DIAG.OPTIMIZER_TRACE(1)");
         ...
         s.execute("call SYSCS_DIAG.OPTIMIZER_PRINT_TRACE()");
         s.execute("call SYSCS_DIAG.OPTIMIZER_TRACE(0)");

thanks,

bryan