Re: Tuning pack when application alters nls settings at logon

Jeffrey,

I assume ALL indexes in this db are created with those NLS variants, so OEM for this database would need to always match? If so, the comment from Jose R is a good approach (using a login trigger to set those).

If there are other applications in this db that DON'T use those settings and you only want to run the advisor against specific SQL_IDs you can use DBMS_SQLTUNE and set those parameters in a SQL session for the advisor task I believe.

Something like:

• begin --- alter session set nls_comp = LINGUISTIC; alter session set NLS_SORT = PUNCTUATION;

SET LONG 10000
SET PAGES 0
SET LINES 1000 SET ECHO ON BEGIN
    DBMS_SQLTUNE.drop_tuning_task ( task_name => 'MYTASK' ); END;
/

VARIABLE stmt_task VARCHAR2 ( 512 );

BEGIN
    :stmt_task :=

        DBMS_SQLTUNE.create_tuning_task ( sql_id  => '6mr3nxj1m0k69'
                                         ,plan_hash_value => 204790768
                                         ,time_limit => 360
                                         ,task_name => 'MYTASK'
                                         ,description => 'None' );

BEGIN
    DBMS_SQLTUNE.execute_tuning_task ( :stmt_task ); END;
/

Then you can check the recommendations from the Tuning Advisor page in OEM once it completes.

Chris

On Wed, Sep 19, 2018 at 12:36 PM Jeffrey Beckstrom <jbeckstrom_at_gcrta.org> wrote:

> We have an application that alters nls settings at connection time.
> Indexes on character columns are built with these revised nls settings. If
> we do an explain plan without setting the applicable nls settings, indexes
> are not used. The indexes are only selected by the explain plan if an alter
> session is first done to set the application settings. My question is, we
> use EM 13C with the diagnostic and tuning packs. How can we make EM 13C use
> the correct nls settings before running the tuning advisor? The nls
> settings dynamically set are:
> alter session set nls_comp = LINGUISTIC;
> alter session set NLS_SORT = PUNCTUATION;
>
> Jeffrey Beckstrom
> Lead Database Administrator
> Information Technology Department
> Greater Cleveland Regional Transit Authority
> 1240 W. 6th Street
> Cleveland, Ohio 44113
>
>

--
http://www.freelists.org/webpage/oracle-l