Tuesday, June 12, 2018

Autoconfig post 12C upgrade from 11g failed with "ORA-01804: failure to initialize timezone information"

Autoconfig post 12C upgrade from 11g failed with "ORA-01804: failure to initialize timezone information"

Error

DB Autoconfig failed with below error:

[AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution.  Errors are grouped by directory and phase.
The report format is:
      <filename>  <phase>  <return code where appropriate>

  [PROFILE PHASE]
  AutoConfig could not successfully execute the following scripts:
    Directory: /s01/app/oracle/db_tech_st/12.1.0.2/appsutil/TEST1_linx32
      afdbprf.sh              INSTE8_PRF         1

  [APPLY PHASE]
  AutoConfig could not successfully execute the following scripts:
    Directory: /s01/app/oracle/db_tech_st/12.1.0.2/appsutil/TEST1_linx32
      adcrobj.sh              INSTE8_APPLY       1

AutoConfig is exiting with status 2


afdbprf.sh started at Sun Jun 10 00:00:26 EDT 2018


The environment settings are as follows ...

       ORACLE_HOME : /u01/app/orad1i/db/tech_st/12.1.0.2
        ORACLE_SID : TEST1

Executable : /u01/app/orad1i/db/tech_st/12.1.0.2/bin/sqlplus

ERROR:

ORA-01804: failure to initialize timezone information

Cause

It is not able to find the TimeZone parameter  value in the specified path

[oracle@linx32  ~]$ echo $ORA_TZFILE
/u01/app/orad1i/db/tech_st/12.1.0.2/oracore/zoneinfo/timezlrg.dat

[oracle@linx32 ~]$ ls -ltr /u01/app/orad1i/db/tech_st/12.1.0.2/oracore/zoneinfo/timezlrg.dat
ls: cannot access /u01/app/orad1i/db/tech_st/12.1.0.2/oracore/zoneinfo/timezlrg.dat.: No such file or directory

Solution

Create a soft link to existing Time zone file

oracle@linx32 ~]$ cd /u01/app/orad1i/db/tech_st/12.1.0.2/oracore/zoneinfo

[oracle@linx32 ~] ln -s timezlrg_25.dat timezlrg.dat

[orad1i@nchlamsdb01 zoneinfo]$ ls -ltr timezlrg.dat
lrwxrwxrwx 1 oracle dba 11 Jun 11 10:24 timezlrg.dat -> timezlrg_25.dat

Re-run  Autoconfig, it shoud work.




Monday, June 11, 2018

Difference between SQL Profile and SPM(SQL Plan Management) Baselines

SQL Profile

In Oracle a SQL Profile creates extra information about a particular SQL that the optimizer can use at run time to select the optimal plan to ensure best performance. In essence the SQL Profile enables dynamic behavior where the optimizer has multiple plans to choose from at run time based on run time bind variables etc. When you run the SQL Tuning Advisor for the list of recommendation you will see that the recommendation specifies whether a SQL can be improved by creating a SQL Profile or SQL Baseline. It is preferable to choose a SQL Profile simply because it allows the optimizer to pick best execution plans at run time.

SQL Baseline

SQL Baseline on the other hand is more of a brute force method, when you simply marry a particular SQL to stay with a specific SQL execution plan. So no matter what the run time bind variables are for a given SQL, the optimizer will always try to use the SQL Baseline plan. This may work fine for most cases but instances where data skew is high it is preferable that one pick more efficient plans based on bind variable values passed at run time instead of always picking the same plan as generated by the SQL Baseline.

In any case knowledge of the application data may make you prefer one over the other, in case you are clueless about this, then I would suggest you stick with the SQL Profile.

How Does SQL Profiles Work?

When profiling a SQL statement, SQL Tuning Advisor uses a specific set of bind values as input.

The advisor compares the optimizer estimate with values obtained by executing fragments of the statement on a data sample. When significant variances are found, SQL Tuning Advisor bundles corrective actions together in a SQL profile, and then recommends its acceptance.

The corrected statistics in a SQL profile can improve optimizer cardinality estimates, which in turn leads the optimizer to select better plans. SQL profiles provide the following benefits over other techniques for improving plans:

Unlike hints and stored outlines, SQL profiles do not tie the optimizer to a specific plan or subplan. SQL profiles fix incorrect estimates while giving the optimizer the flexibility to pick the best plan in different situations.

Unlike hints, no changes to application source code are necessary when using SQL profiles. The use of SQL profiles by the database is transparent to the user.

How Does SQL Plan Management Work?

When a SQL statement is hard parsed, the cost based optimizer produces several execution plans and selects the one with the lowest cost. If a SQL plan baseline is present, the optimizer compares the plan it just produced with the plans in the SQL plan baseline. If a matching plan is found that is flagged as accepted the plan is used. If the SQL plan baseline doesn't contain an accepted plan matching the one it just created, the optimizer evaluates the accepted plans in the SQL plan baseline and uses the one with the lowest cost. If the execution plan originally produced by the optimizer has a lower cost than those present in the SQL plan baseline, it is added to the baseline as a not-accepted plan, so it is not used until it is verified not to cause a reduction in performance. If a system change affects all existing accepted plans, so they are considered non-reproducible, the optimizer will use the plan with the lowest cost.

Oracle call this a "conservative plan selection strategy", as the optimizer preferentially uses a tried an tested execution plan, even if a new plan looks like it might perform better. Only when the newer plan is proved to perform well will it be accepted for use.

The use of SQL plan baselines is controlled by the OPTIMIZER_USE_SQL_PLAN_BASELINES parameter, which is set to TRUE by default. Access to the DBMS_SPM package requires the ADMINISTER SQL MANAGEMENT OBJECT privilege.

Source : perfluent.com

Migrate SQL Profiles from One Instance to Another Instance

1. Creating a staging table to store the SQL Profiles

SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name=>'SQL_PROFILES_MOVE',schema_name=>'SYSTEM');

2. Packing the SQL Profiles to the staging table

SQL> SELECT name FROM dba_sql_profiles;

NAME
---------
SYS_SQLPROF_612f26136ae78001
SYS_SQLPROF_712f260d3e1a1400
SYS_SQLPROF_812ed90a425b2303
SYS_SQLPROF_912f2622a4622103

SQL>

EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_MOVE',profile_name=>'SYS_SQLPROF_612f26136ae78001');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_MOVE',profile_name=>'SYS_SQLPROF_712f260d3e1a1400');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_MOVE',profile_name=>'SYS_SQLPROF_812ed90a425b2303');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_MOVE',profile_name=>'SYS_SQLPROF_912f2622a4622103');

3. Export table using exp tool from the source database

exp SYSTEM/password file=SQL_PROFILES.dmp tables=SQL_PROFILES_MOVE

4. Import table using imp tool into the target database

imp SYSTEM/password file=SQL_PROFILES.dmp full=y

5. Unpack the SQL Profiles in the target database

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(REPLACE => TRUE,staging_table_name => 'SQL_PROFILES_MOVE');

6. SQL profiles names available in the Target database.

SQL> SELECT name FROM dba_sql_profiles;

NAME
---------
SYS_SQLPROF_612f26136ae78001
SYS_SQLPROF_712f260d3e1a1400
SYS_SQLPROF_812ed90a425b2303
SYS_SQLPROF_912f2622a4622103

Improve performance of a query using SQL Profiles using Oracle Packages

- A SQL profile is a database object that contains auxiliary statistics specific to a SQL statement.

- Conceptually, a SQL profile is to a SQL statement what object-level statistics are to a table or index. SQL profiles are created when a DBA invokes SQL Tuning Advisor.

- When profiling a SQL statement, SQL Tuning Advisor uses a specific set of bind values as input.

- The advisor compares the optimizer estimate with values obtained by executing fragments of the statement on a data sample. When significant variances are found, SQL Tuning Advisor bundles corrective actions together in a SQL profile, and then recommends its acceptance.

- The corrected statistics in a SQL profile can improve optimizer cardinality estimates, which in turn leads the optimizer to select better plans. SQL profiles provide the following benefits over other techniques for improving plans:

- Unlike hints and stored outlines, SQL profiles do not tie the optimizer to a specific plan or subplan. SQL profiles fix incorrect estimates while giving the optimizer the flexibility to pick the best plan in different situations.


- Unlike hints, no changes to application source code are necessary when using SQL profiles. The use of SQL profiles by the database is transparent to the user.

Steps to implement SQL profile

+ First need to identify the SQL and SQL id of the query? 

1. If you have only SID an need to find out sql_id?

SQL> select sid,sql_id from v$session where sid='786';

 SID SQL_ID
----- -------------
 786 b6v53rrz9h1at

2. If we have already have SQL ID, cross check with as below

select sql_id,sql_text from v$sqltext_with_newlines where sql_id='b6v53rrz9h1at' order by piece;

SQL_TEXT
----------------------------------------------------------------
SELECT COUNT(1) FROM WF_NOTIFICATIONS WN, (SELECT WUR.ROLE_NAME
FROM WF_USER_ROLES WUR WHERE WUR.USER_NAME = :B3 AND WUR.USER_OR
IG_SYSTEM = :B2 AND WUR.USER_ORIG_SYSTEM_ID = :B1 ) WUR WHERE (
(WN.MORE_INFO_ROLE IS NULL AND WN.RECIPIENT_ROLE = WUR.ROLE_NAME
) OR (WN.MORE_INFO_ROLE = WUR.ROLE_NAME) ) AND WN.STATUS = 'OPEN'

3. If you don't have SID,SQL id then find SQL ID using sql txt

select sql_id,SQL_TEXT from v$sqltext_with_newlines where SQL_TEXT like '%SELECT COUNT(1) FROM WF_NOTIFICATIONS WN%'

sql_id, SQL_TEXT
------- --------------------------------------------------------
b6v53rrz9h1at SELECT COUNT(1) FROM WF_NOTIFICATIONS WN, (SELECT WUR.ROLE_NAME
FROM WF_USER_ROLES WUR WHERE WUR.USER_NAME = :B3 AND WUR.USER_OR
IG_SYSTEM = :B2 AND WUR.USER_ORIG_SYSTEM_ID = :B1 ) WUR WHERE (
(WN.MORE_INFO_ROLE IS NULL AND WN.RECIPIENT_ROLE = WUR.ROLE_NAME
) OR (WN.MORE_INFO_ROLE = WUR.ROLE_NAME) ) AND WN.STATUS = 'OPEN'

Note: you might get your own session in above statement, ignore it, you can cross check from v$mystat

SQL> select distinct sid from v$mystat;

       SID
----------
38

SQL> select sid,sql_id from v$session where sid='38';

       SID SQL_ID
---------- -------------
38 5fgv6fxdj350k

+ Use SQL Tuning Advisor to add SQL profile to your SQL ID

1. You need to create Tuning Task before you geneate SQL profile for your SQL ID.

SET SERVEROUTPUT ON

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => 'b6v53rrz9h1at',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'b6v53rrz9h1at_tuning_task',
                          description => 'Tuning task for statement b6v53rrz9h1at.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Execution:
----------

SQL> set serveroutput on
SQL> DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => 'b6v53rrz9h1at',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'b6v53rrz9h1at_tuning_task',
                          description => 'Tuning task for statement b6v53rrz9h1at.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

l_sql_tune_task_id: b6v53rrz9h1at_tuning_task

PL/SQL procedure successfully completed.

2. Execute the Tuning Task

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '&taskname');

Excution:
--------

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'b6v53rrz9h1at_tuning_task');

PL/SQL procedure successfully completed.


2. Monitor the Tuning Task status.

SELECT task_name, status FROM dba_advisor_log WHERE task_name ='&task_name';

Execution:
----------

SQL> select owner,task_name,status FROM dba_advisor_log where task_name='b6v53rrz9h1at_tuning_task';

OWNER        TASK_NAME       STATUS
------------------------------ ------------------------------ -----------
SYS        b6v53rrz9h1at_tuning_task    EXECUTING

SQL> select owner,task_name,status FROM dba_advisor_log where task_name='b6v53rrz9h1at_tuning_task';

OWNER        TASK_NAME       STATUS
------------------------------ ------------------------------ -----------
SYS        b6v53rrz9h1at_tuning_task    COMPLETED

3. Displaying the Results of a SQL Tuning Task

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('b6v53rrz9h1at_tuning_task5') AS recommendations FROM dual;
SET PAGESIZE 24


RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : b6v53rrz9h1at_tuning_task
Tuning Task Owner  : SYS
Workload Type    : Single SQL Statement
Scope    : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at    : 06/11/2018 03:02:43
Completed at    : 06/11/2018 03:03:03

-------------------------------------------------------------------------------
Schema Name: XXAR
SQL ID    : b6v53rrz9h1at
SQL Text   : SELECT COUNT(1) FROM WF_NOTIFICATIONS WN, (SELECT WUR.ROLE_NAME
FROM WF_USER_ROLES WUR WHERE WUR.USER_NAME = :B3 AND WUR.USER_OR
IG_SYSTEM = :B2 AND WUR.USER_ORIG_SYSTEM_ID = :B1 ) WUR WHERE (
(WN.MORE_INFO_ROLE IS NULL AND WN.RECIPIENT_ROLE = WUR.ROLE_NAME
) OR (WN.MORE_INFO_ROLE = WUR.ROLE_NAME) ) AND WN.STATUS = 'OPEN'

-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
SQL Profile Finding (see explain plans section below)
---------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 96.28%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile to use parallel execution
    for this statement.
    execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_46345',
            task_owner => 'SYS', replace => TRUE, profile_type =>
            DBMS_SQLTUNE.PX_PROFILE);


  Executing this query parallel with DOP 8 will improve its response time
  96.29% over the original plan. However, there is some cost in enabling
  parallel execution. It will increase the statement's resource consumption by
  an estimated 70.31% which may result in a reduction of system throughput.
  Also, because these resources are consumed over a much smaller duration, the
  response time of concurrent statements might be negatively impacted if
  sufficient hardware capacity is not available.

  The following data shows some sampled statistics for this SQL from the past
  week and projected weekly values when parallel execution is enabled.

4. Implementing SQL Profile to the SQLID

    execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_46345',
            task_owner => 'SYS', replace => TRUE, profile_type =>
            DBMS_SQLTUNE.PX_PROFILE);

5. List the SQL profile for the SQL id

select sql_id,sql_profile,sql_plan_baseline from v$sql where sql_id='b6v53rrz9h1at'

6. List the SQL profiles and its status

COLUMN category FORMAT a10
COLUMN sql_text FORMAT a20

SELECT NAME, SQL_TEXT, CATEGORY, STATUS FROM   DBA_SQL_PROFILES;

7. Disable SQL Profile

BEGIN
  DBMS_SQLTUNE.alter_sql_profile (
    name            => 'emp_dept_profile',
    attribute_name  => 'STATUS',
    value           => 'DISABLED');
END;
/

8. Drop the SQL profile

BEGIN
  DBMS_SQLTUNE.drop_sql_profile (
    name   => 'emp_dept_profile',
    ignore => TRUE);
END;
/

Other Monitoring Queries


1. Monitor the progress of the Tuning Task  in percentage

COL ADVISOR_NAME FORMAT a20
COL SOFAR FORMAT 999
COL TOTALWORK FORMAT 999

SELECT TASK_ID, ADVISOR_NAME, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM   V$ADVISOR_PROGRESS;

Implementing SQL profile with Oracle Script: (Aleternative Method)

1. Get SQL id

Use SQLs are given above to find out SQL ID

2. Run SQL Tuning Advisor for the SQL_ID

SQL> @?/rdbms/admin/sqltrpt.sql

In case the recommendation is for creation of SQL PROFILE, sqltrpt.sql will provide the command too as below.

Command to Create and Implement SQL Profile in Oracle for the SQL_ID:
--------------------------------------------------------------------
SQL> execute dbms_sqltune.accept_sql_profile(task_name => '<TASK_NAME>',task_owner => 'SYS', replace => TRUE, FORCE_MATCH => TRUE);

If successful, you should see the following:
PL/SQL procedure successfully completed.

SQL> SELECT name, created, LAST_MODIFIED FROM dba_sql_profiles ORDER BY created DESC;

Command to Drop SQL Profile in Oracle for the SQL_ID:
----------------------------------------------------
SQL> execute dbms_sqltune.drop_sql_profile('<SQL_PROFILE_NAME>');

If successful, you should see the following:
PL/SQL procedure successfully completed.

SQL> SELECT name, created FROM dba_sql_profiles ORDER BY created DESC;

Command to Alter SQL Profile in Oracle for the SQL_ID:
-----------------------------------------------------
SQL> EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('<SQL_PROFILE_NAME>','STATUS','DISABLED');

If successful, you should see the following:
PL/SQL procedure successfully completed.

SQL> SELECT name, created FROM dba_sql_profiles ORDER BY created DESC;

If you don't know the name of the SQL Profile then use the below query
SQL> select NAME,SQL_TEXT from DBA_SQL_PROFILES where SQL_TEXT like '%SELECT%TABLE%NAME%';

Query
-----
The SQL_ID is not stored with the profiles.
You can see if a statement is using a profile by querying v$sql where sql_profile is not null.

select sql_id, child_number, plan_hash_value plan_hash, sql_profile,
executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio,
sql_text
from v$sql s
where upper(sql_text) like upper(nvl('&sql_text',sql_text))
and sql_text not like '%from v$sql where sql_text like nvl(%'
and sql_id like nvl('&sql_id',sql_id)
and sql_profile like nvl('&sql_profile_name',sql_profile)
and sql_profile is not null
order by 1, 2, 3
/

Oracle EBS integration with Oracle IDCS for SSO

Oracle EBS integration with Oracle IDCS for SSO Oracle EBS SSO? Why is it so important? Oracle E-Business Suite is a widely used application...