Return-Path: X-Original-To: apmail-db-derby-commits-archive@www.apache.org Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id C7CBFFDBF for ; Tue, 2 Apr 2013 04:39:38 +0000 (UTC) Received: (qmail 65785 invoked by uid 500); 2 Apr 2013 04:39:38 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 65657 invoked by uid 500); 2 Apr 2013 04:39:34 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 65634 invoked by uid 99); 2 Apr 2013 04:39:33 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 02 Apr 2013 04:39:33 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=5.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.131] (HELO eos.apache.org) (140.211.11.131) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 02 Apr 2013 04:39:32 +0000 Received: from eos.apache.org (localhost [127.0.0.1]) by eos.apache.org (Postfix) with ESMTP id C7E4A7D3 for ; Tue, 2 Apr 2013 04:39:12 +0000 (UTC) MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable From: Apache Wiki To: Apache Wiki Date: Tue, 02 Apr 2013 04:39:12 -0000 Message-ID: <20130402043912.56944.14408@eos.apache.org> Subject: =?utf-8?q?=5BDb-derby_Wiki=5D_Update_of_=22OptimizerTracing=22_by_MamtaSa?= =?utf-8?q?toor?= Auto-Submitted: auto-generated X-Virus-Checked: Checked by ClamAV on apache.org Dear Wiki user, You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for = change notification. The "OptimizerTracing" page has been changed by MamtaSatoor: http://wiki.apache.org/db-derby/OptimizerTracing?action=3Ddiff&rev1=3D1&rev= 2=3D2 You can trace the optimizer's analysis, one statement at a time. Optimize= r tracing is enabled by this command: = - {{{ call syscs_util.syscs_register_tool( 'optimizerTracing', true ); }= }} + . {{{ call syscs_util.syscs_register_tool( 'optimizerTracing', true ); }= }} = After enabling tracing, issue the query you want to trace. E.g.: = - {{{ select columnname from sys.systables t, sys.syscolumns c where t.t= ableid =3D c.referenceid and t.tablename =3D 'SYSROLES'; }}} + . {{{ select columnname from sys.systables t, sys.syscolumns c where t.t= ableid =3D c.referenceid and t.tablename =3D 'SYSROLES'; }}} = Finally, turn tracing off and dump the trace. By default, the trace is pr= inted on standard out. However, you can also force the trace to go into a f= ile. Here's how you disable tracing and print the trace on standard out... = - {{{ call syscs_util.syscs_register_tool( 'optimizerTracing', false ); = }}} + . {{{ call syscs_util.syscs_register_tool( 'optimizerTracing', false ); = }}} = ...and here's how you disable tracing, printing the trace to the file z1.= txt: = - {{{ call syscs_util.syscs_register_tool( 'optimizerTracing', false, 'z= 1.txt' ); }}} + . {{{ call syscs_util.syscs_register_tool( 'optimizerTracing', false, 'z= 1.txt' ); }}} = + The tracing option explained above is available in 10.10 release. Prior t= o 10.10 release, one can use following undocumented way to enable optimizer= tracing. + = + Define two stored procedures in the user application. + = + . 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.getOptimiz= erTraceOutput()); + } s.execute("create procedure trace() language java parameter style java= external name '" + getClass().getName() + ".trace'"); s.execute("create pr= ocedure print_trace() language java parameter style java external name '" += getClass().getName() + ".printTrace'"); + = + And then surround the sql statement that needs to be traced with call tra= ce() before the statement to trace, and printTrace() after the statement to= trace as shown below: + = + . s.execute("call trace()"); s.execute("select 1 from sys.sysschemas nat= ural join sys.systables"); s.execute("call print_trace()"); +=20