Re: Optimizing performance

Sheilah Scheurich wrote:
>
> On 29 Apr 1997 10:10:00 GMT, "Luis Servan" <lservan_at_mx2.redestb.es>
> wrote:
>
> >I'd like to know how can I optimize the time response of database queries
> >from applications developed with Designer/2000
> >
>
> One way to determine if the query is optimized, it to isolate either
> the session, or the query itself and run through TKPROF. This will
> tell you if you are using the proper index, if the sql is efficient,
> etc.. To islolate the query, use the stored procedure:
> EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(A,B,TRUE);
>
> Where a is the sid (session id), and b is the serial number. This can
> be found in the v$session table.
>
> Sheilah Scheurich
> DBA
> scheuric_at_sprynet.com

This is ok. It shows you much information about all sql activity in the period the sql trace has been active. If you only want to inspect the
access path for some single sql statements there is also another way to do that.

Be sure the table PLAN_TABLE exists. You can create it with the utlxplan.sql script (rdbms/admin directory on your db server). Now you can execute (for example under SQL*Plus)

        explain plan set statement_id='1' for <your_sql_statement>

The access plan will be stored in the PLAN_TABLE table. You can get a pseudo graphically output via

	SELECT LPAD('+-',2*level,'+-') || OPERATION 
	|| DECODE(options,NULL,NULL, '('||options||')') operation
	,object_name || DECODE(object_type,NULL,NULL,
	'('object_type||')') || DECODE(object_instance,NULL,
	NULL,'-'||object_instance) object_name ,other
	FROM plan_table	CONNECT BY PRIOR ID=parent_id
	START WITH id=1	ORDER BY ID

You may get a little bit more comfort if you use a tool. We provide Hora. It has a graphical explain plan option and can also switch the SQL trace in a session on or off to produce tkprof trace files. See http://ourworld.compuserve.com/homepages/keeptool .

-- 
Holger Heidenbluth
KeepTool Software
Dr. Jan Dieckmann, Rolf Grünewald, Holger Heidenbluh GbR
Berlin. Germany