OracleAppsDBAKK1

Customer Applied solution from:

XML Publisher Report Bursting Program (XDOBURSTREP) Performance Poor While Initializing ( Doc ID 2300674.1 )
SOLUTION: Replace random with urandom by modifying the Concurrent Program Define Execution Option
--All 5 reports completed in acceptable period of time.

This solution may work to resolve the issue.
However customer should still work on cleaning the out and log files as this situation will result in further issues.
Customer will discuss internally to decide if the purging / moving of the out and log (FSS directory) will occur tonight or
be put on hold until the weekend

-------------------- ACTION PLAN DETAILS BELOW---------------------

Customer Applied solution from:
XML Publisher Report Bursting Program (XDOBURSTREP) Performance Poor While Initializing ( Doc ID 2300674.1 )
SOLUTION: Replace random with urandom by modifying the Concurrent Program Define Execution Option
--All 5 reports completed in acceptable period of time.

(1) Responsibility: System Administrator
(2) Navigate to: Concurrent > Program > Define
(3) Query report
(4) Under "Executable" it shows Name = XDODTEXE
and (Options = NULL) or (Options = -Xms512M -Xmx512M)
(5) Update Options to = -Xms512M -Xmx512M -Djava.security.egd=file:/dev/./urandom
* note the space after -Xmx512M and before -Djava.security
(6) Save.
(7) Test using report that was updated

Action Plan From Here:
====================
Customer Actions:
1. Customer working on cleaning the out and log
Per Doc ID 2603009.1 - Concurrent Processing R12.2 : Too Many Concurrent Requests In Pending Status After Migrating To OCI
-- Log and Out files from concurrent processing in FSS directory needs to be restricted to 50000 records only

* Customer discussing internally to decide if the purging / moving of the out and log (FSS directory) will occur tonight or
be put on hold until the weekend

2. If performance is still unacceptable, please provide the AWR and analyzer, per earlier action plan as follows
2.1> Generate AWR reports for intervals 20-Sep 13:15:to 13:30 , 13:30 to 13:45 . Generate instance level reports from all RAC instances using awrrpt.sql and addmrpt.sql scripts
2.2> Also run analyzer to check settings :
EBS Database Performance and Statistics Analyzer( Doc ID 2126712.1 )
NOTE: It is important that you review saved output file before uploading.
If you ran the Analyzer as a Concurrent Program, make sure to choose 'Web Page, HTML only' for the 'Save as type'.
Do not save as 'Webpage, complete (*.htm,*.html)' as it will be rejected when uploaded to Support. This applies to all Web browsers.

Amazon RDS supports Oracle SQLTXPLAIN (SQLT) through the use of the SQLT option.

The Oracle EXPLAIN PLAN statement can determine the execution plan of a SQL statement. It can verify whether the Oracle optimizer chooses a certain execution plan, such as a nested loops join. It also helps you understand the optimizer's decisions, such as why it chose a nested loops join over a hash join. So EXPLAIN PLAN helps you understand the statement's performance.

SQLT is an Oracle utility that produces a report. The report includes object statistics, object metadata, optimizer-related initialization parameters, and other information that a database administrator can use to tune a SQL statement for optimal performance. SQLT produces an HTML report with hyperlinks to all of the sections in the report.

Unlike Automatic Workload Repository or Statspack reports, SQLT works on individual SQL statements. SQLT is a collection of SQL, PL/SQL, and SQL*Plus files that collect, store, and display performance data.

Following are the supported Oracle versions for each SQLT version.

SQLT version	Oracle Database 19c	Oracle Database 18c	Oracle Database 12c Release 2 (12.2)	Oracle Database 12c Release 1 (12.1)
12.2.180725	Supported	Supported	Supported	Supported
12.2.180331	Not supported	Supported	Supported	Supported
12.1.160429	Not supported	Not supported	Supported	Supported

To download SQLT and access instructions for using it:

Log in to your My Oracle Support account, and open the following documents:
To download SQLT: Document 215187.1
For SQLT usage instructions: Document 1614107.1
For frequently asked questions about SQLT: Document 1454160.1
For information about reading SQLT output: Document 1456176.1
For interpreting the Main report: Document 1922234.1

You can use SQLT with any edition of the following Oracle Database versions:

Oracle Database 19c (19.0.0.0)
Oracle Database 18c (18.0.0.0)
Oracle Database 12c Release 2 (12.2.0.1)
Oracle Database 12c Release 1 (12.1.0.2_

Amazon RDS does not support the following SQLT methods:

XPLORE
XHUME

Prerequisites for SQLT

The following are prerequisites for using SQLT:

You must remove users and roles that are required by SQLT, if they exist.
The SQLT option creates the following users and roles on a DB instance:
- SQLTXPLAIN user
- SQLTXADMIN user
- SQLT_USER_ROLE role
If your DB instance has any of these users or roles, log in to the DB instance using a SQL client, and drop them using the following statements:
```
DROP USER SQLTXPLAIN CASCADE;
DROP USER SQLTXADMIN CASCADE;   
DROP ROLE SQLT_USER_ROLE CASCADE;
                
```
You must remove tablespaces that are required by SQLT, if they exist.
The SQLT option creates the following tablespaces on a DB instance:
- RDS_SQLT_TS
- RDS_TEMP_SQLT_TS
If your DB instance has these tablespaces, log in to the DB instance using a SQL client, and drop them.

SQLT option settings

SQLT can work with licensed features that are provided by the Oracle Tuning Pack and the Oracle Diagnostics Pack. The Oracle Tuning Pack includes the SQL Tuning Advisor, and the Oracle Diagnostics Pack includes the Automatic Workload Repository. The SQLT settings enable or disable access to these features from SQLT.

Amazon RDS supports the following settings for the SQLT option.

Option setting	Valid values	Default value	Description
`LICENSE_PACK`	`T`, `D`, `N`	`N`	The Oracle Management Packs that you want to access with SQLT. Enter one of the following values: `T` indicates that you have a license for the Oracle Tuning Pack and the Oracle Diagnostics Pack, and you want to access the SQL Tuning Advisor and Automatic Workload Repository from SQLT. `D` indicates that you have a license for the Oracle Diagnostics Pack, and you want to access the Automatic Workload Repository from SQLT. `N` indicates that you don't have a license for the Oracle Tuning Pack and the Oracle Diagnostics Pack, or that you have a license for one or both of them, but you don't want SQLT to access them. Note Amazon RDS does not provide licenses for these Oracle Management Packs. If you indicate that you want to use a pack that is not included in your DB instance, you can use SQLT with the DB instance. However, SQLT can't access the pack, and the SQLT report doesn't include the data for the pack. For example, if you specify `T`, but the DB instance doesn't include the Oracle Tuning Pack, SQLT works on the DB instance, but the report it generates doesn't contain data related to the Oracle Tuning Pack.
`VERSION`	`2016-04-29.v1`, `2018-03-31.v1`, `2018-07-25.v1`	`2016-04-29.v1`	The version of SQLT that you want to install. Note For Oracle Database 19c (19.0.0.0), the only supported version is `2018-07-25.v1`. This version is also the default for Oracle Database 19c.

Adding the SQLT option

The following is the general process for adding the SQLT option to a DB instance:

Create a new option group, or copy or modify an existing option group.
Add the SQLT option to the option group.
Associate the option group with the DB instance.

After you add the SQLT option, as soon as the option group is active, SQLT is active.

To add the SQLT option to a DB instance

Determine the option group that you want to use. You can create a new option group or use an existing option group. If you want to use an existing option group, skip to the next step. Otherwise, create a custom DB option group with the following settings:
1. For Engine, choose the Oracle edition that you want to use. The SQLT option is supported on all editions.
2. For Major engine version, choose the version of your DB instance.
For more information, see Creating an option group.
Add the SQLT option to the option group. For more information about adding options, see Adding an option to an option group.
Apply the option group to a new or existing DB instance:
- For a new DB instance, you apply the option group when you launch the instance. For more information, see Creating an Amazon RDS DB instance.
- For an existing DB instance, you apply the option group by modifying the instance and attaching the new option group. For more information, see Modifying an Amazon RDS DB instance.
(Optional) Verify the SQLT installation on each DB instance with the SQLT option.
1. Use a SQL client to connect to the DB instance as the master user.
  For information about connecting to an Oracle DB instance using a SQL client, see Connecting to your Oracle DB instance.
2. Run the following query:
```
SELECT sqltxplain.sqlt$a.get_param('tool_version') sqlt_version FROM DUAL;                        
                    
```
  The query returns the current version of the SQLT option on Amazon RDS. 12.1.160429 is an example of a version of SQLT that is available on Amazon RDS.

Change the passwords of the users that are created by the SQLT option.

Use a SQL client to connect to the DB instance as the master user.

Run the following SQL statement to change the password for the SQLTXADMIN user:



ALTER USER SQLTXADMIN IDENTIFIED BY new_password ACCOUNT UNLOCK;

Run the following SQL statement to change the password for the SQLTXPLAIN user:



ALTER USER SQLTXPLAIN IDENTIFIED BY new_password ACCOUNT UNLOCK;

Note

Upgrading SQLT requires uninstalling an older version of SQLT and then installing the new version. So, all SQLT metadata can be lost when you upgrade SQLT. A major version upgrade of a database also uninstalls and re-installs SQLT. An example of a major version upgrade is an upgrade from Oracle Database 18c to Oracle Database 19c.

Using SQLT

SQLT works with the Oracle SQL*Plus utility.

To use SQLT

Download the SQLT .zip file from Document 215187.1 on the My Oracle Support site.
Note
You can't download SQLT 12.1.160429 from the My Oracle Support site. Oracle has deprecated this older version.
Unzip the SQLT .zip file.
From a command prompt, change to the sqlt/run directory on your file system.
From the command prompt, open SQL*Plus, and connect to the DB instance as the master user.
For information about connecting to a DB instance using SQL*Plus, see Connecting to your Oracle DB instance.

Get the SQL ID of a SQL statement:



SELECT SQL_ID FROM V$SQL WHERE SQL_TEXT='sql_statement';

Your output is similar to the following:



SQL_ID
-------------
chvsmttqjzjkn

Analyze a SQL statement with SQLT:



START sqltxtract.sql sql_id sqltxplain_user_password

For example, for the SQL ID chvsmttqjzjkn, enter the following:



START sqltxtract.sql chvsmttqjzjkn sqltxplain_user_password

SQLT generates the HTML report and related resources as a .zip file in the directory from which the SQLT command was run.

(Optional) To enable application users to diagnose SQL statements with SQLT, grant SQLT_USER_ROLE to each application user with the following statement:
```
GRANT SQLT_USER_ROLE TO application_user_name;                
            
```
Note
Oracle does not recommend running SQLT with the SYS user or with users that have the DBA role. It is a best practice to run SQLT diagnostics using the application user's account, by granting SQLT_USER_ROLE to the application user.

Upgrading the SQLT option

With Amazon RDS for Oracle, you can upgrade the SQLT option from your existing version to a higher version. To upgrade the SQLT option, complete steps 1–3 in Using SQLT for the new version of SQLT. Also, if you granted privileges for the previous version of SQLT in step 7 of that section, grant the privileges again for the new SQLT version.

Upgrading the SQLT option results in the loss of the older SQLT version's metadata. The older SQLT version's schema and related objects are dropped, and the newer version of SQLT is installed. For more information about the changes in the latest SQLT version, see Document 1614201.1 on the My Oracle Support site.

Note

Version downgrades are not supported.

Modifying SQLT settings

After you enable SQLT, you can modify the LICENSE_PACK and VERSION settings for the option.

For more information about how to modify option settings, see Modifying an option setting. For more information about each setting, see SQLT option settings.

Removing the SQLT option

You can remove SQLT from a DB instance.

To remove SQLT from a DB instance, do one of the following:

To remove SQLT from multiple DB instances, remove the SQLT option from the option group to which the DB instances belong. This change affects all DB instances that use the option group. For more information, see Removing an option from an option group.
To remove SQLT from a single DB instance, modify the DB instance and specify a different option group that doesn't include the SQLT option. You can specify the default (empty) option group or a different custom option group. For more information, see Modifying an Amazon RDS DB instance.

https://docs.amazonaws.cn/en_us/AmazonRDS/latest/UserGuide/Oracle.Options.SQLT.html

OracleAppsDBAKK1

Tuesday, September 21, 2021

OCI Listener timeout

XML Publisher Report Bursting Program taking time to start

Thursday, July 22, 2021

Oracle-Provided Diagnostic Tools

Friday, July 9, 2021

Performance Reading blogs

Thursday, July 8, 2021

AWS Database Migration Service Step-by-Step Walkthroughs

Using an Oracle database as a source for AWS DMS

Security in AWS Database Migration Service