My Oracle Support Banner

How to list Optimizer Environment Settings and Parameters for Queries and Their Child Cursors (Doc ID 2371861.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Information in this document applies to any platform.

Goal

 

SQL Tuning XTRACT is one stop diagnostics and used as standard sql tuning diagnostics to be captured for reviewing SQL Tuning Issues. However there are times when SQLT installation does take some time and in some cases we are unable to get it installed successfully. Situations like this warrants us to have access or knowledge of such queries which are used by SQLT to capture relevant data needed.  One such data is Optimizer Session Environment for the application session which ran the query. SQLT provides CBO environment, but it can be different for different child cursors and SQLT does not show you different optimizer settings or environment based on Child cursors. Below example show how to capture this important information.

 

 

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.