Tuesday, March 19, 2013

Handy Oracle Support Metalink Notes for Apps DBA




Handy Oracle Support Metalink Notes for Apps DBA

Useful Articles for the Oracle E-Business Suite Technology Stack for Technical Consultant / DBA [ID 828157.1]


Installation
----------------------------------------------------------------------------------------------------

note 761564.1 Oracle Applications Installation and Upgrade Notes Release 12 (12.1.1) for Linux x86
note 402310.1 Oracle Applications Installation and Upgrade notes Release 12 (12.0) for Linux (32-bit)
note 406982.1 Cloning Oracle Applications Release 12 with Rapid Clone
note 559518.1 Cloning Oracle E-Business Suite Release 12 RAC-Enabled Systems with Rapid Clone

note 421409.1 Unbreakable Linux enviroment check before R12 install
note 458533.1 How to Enable Enterprise Manager on the Oracle E-Business Suite Release 12
note 603716.1 Using AS10g AS Control with eBusiness Suite Rel 12
note 452120.1 How to locate the log files and troubleshoot RapidWiz for R12
note 316806.1 Oracle Applications Installation Update notes, Release 11i (11.5.10.2)
note:230672.1 Cloning Oracle Applications Release 11i with Rapid Clone

note 207296.1 How to Find out Linux Version Information?


Database
----------------------------------------------------------------------------------------------------

note:454750.1 Oracle Apps Release 12 with Oracle Database 10.2.0 interoperability notes
note 735276.1 Interoperability notes E-Business Suite R12 with Oracle Database 11gR1
note 388577.1 Configuring Oracle Applications Release 12 with 10g R2 RAC
note 466649.1 Using Oracle 11g Release 1 Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 12

note 216550.1 Oracle Applications Release 11i with Oracle9i Release 2 (9.2.0)
note 362203.1 Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0)
note 452783.1 Oracle Applications Release 11i with Oracle 11g Release 1 (11.1.0)
note 362135.1 Configuring Oracle Applications Release 11i with 10g R2 RAC and ASM

note 554539.1 Using Database Partitioning with Oracle E-Business Suite

SSO/OID
----------------------------------------------------------------------------------------------------

note 876539.1 Using the Latest Oracle Internet Directory 11gR1 Patchset with Oracle Single Sign-on and Oracle E-Business Suite
note 376811.1 Integrating Oracle E-Business Suite Release 12 with 10g AS Oracle Internet Directory and Oracle Single Sign-On
note 261914.1 Integrating Oracle E-Business Suite Release 11i with Oracle Internet Directory and Oracle Single Sign-On
note 444573.1 Basic checks for user integration when using Oracle E-Business Suite 11i with Oracle AS 10g



Performance
----------------------------------------------------------------------------------------------------

note 163208.1 bde_last_analyzed.sql - Verifies CBO Statistics
note 174605.1 bde_chk_cbo.sql - Reports Database Initialization Parameters related to an Apps 12 or 11i instance
note 396009.1 Database Initialization Parameters for Oracle Applications Release 12
note 216205.1 Database Initialization Parameters for Oracle Applications 11i
note 744143.1 Tuning performance on eBusiness suite
note 169935.1 Troubleshooting Oracle Applications Performance Issues
note 244040.1 Oracle E-Business Suite Recommended Performance Patches


Security
----------------------------------------------------------------------------------------------------

note 738923.1 Oracle E-Business Suite Releases 11i and 12 Critical Patch Update note (January 2009)
note 403537.1 Best Practices for Securing Oracle E-Business Suite Release 12
note 189367.1 Best Practices for Securing the E-Business Suite Release 11i


Ihelp
----------------------------------------------------------------------------------------------------

Note 740834.1 R12: Troubleshooting iHelp


Advanced Configurations
----------------------------------------------------------------------------------------------------

note 380483.1 Oracle E-Business Suite Release 12 Additional Configuration and Deployment Options
note 384248.1 Sharing The Application Tier File System in Oracle E-Business Suite Release 12
note 217368.1 Advanced Configurations and Topologies for Enterprise Deployments of E-Business Suite 11i
note 233428.1 Sharing the Application Tier File System in Oracle Applications 11i
note 233436.1 Installing Oracle Application Server 10g with Oracle E-Business Suite Release 11i
note 364439.1 Tips and Queries for Troubleshooting Advanced Topologies


DMZ
----------------------------------------------------------------------------------------------------

note 380490.1 Oracle E-Business Suite R12 Configuration in a DMZ
note 726953.1 Case History: Implementing a Reverse Proxy Alone in the DMZ Configuration - R12
note 287176.1 DMZ Configuration with Oracle E-Business Suite 11i
note 438744.1 Case History: Implementing a Reverse Proxy Alone in a DMZ Configuration - 11i
note 460564.1 Hints and Tips for Troubleshooting the URL Firewal


Loadbalancer
----------------------------------------------------------------------------------------------------

note 380489.1 Using Load-Balancers with Oracle E-Business Suite Release 12
note 727171.1 Implementing Load Balancing On Oracle E-Business Suite - Documentation For Specific Load Balancer Hardware
note 601694.1 How To Check Session Persistence On BigIP F5 And Cisco Ace Load Balancer Appliances
note 603325.1 Using Cisco ACE Series Application Control Engine with Oracle E-Business Suite Release 12


SSL
----------------------------------------------------------------------------------------------------

note 376700.1 Using SSL with Oracle E-Business Suite Release 12
Note 1330799.1 Login Page Fails To Render On SSL Terminated Application Tiers  
note 123718.1 11i A Guide to Understanding and Implementing SSL for Oracle Applications

Java
----------------------------------------------------------------------------------------------------

note 418664.1 Overview of Using Java with Oracle E-Business Suite Release 12
note 455492.1 Using Latest Update of Java 6.0 with Oracle E-Business Suite Release 12
note 384249.1 Using Latest Update of JDK 5.0 with Oracle E-Business Suite Release 12note 300482.1Overview of Using Java with Oracle E-Business Suite Release 11i
note 401561.1 Using J2SE Version 6 with Oracle E-Business Suite 11i
note 304099.1 Using J2SE Version 5.0 with Oracle E-Business Suite 11i, Release 11.5.10
note 246105.1 Upgrading to J2SE 1.4.2 with Oracle Applications 11
inote 130091.1 Upgrading Oracle Applications 11i to use JDK 1.3JRE/Jinitiator/EBS Client Software
----------------------------------------------------------------------------------------------------

note 389422.1 Recommended Browsers for Oracle E-Business Suite Release 12
note 393931.1 Deploying Sun JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite Release 12note 285218.1 Recommended Browsers for Oracle Applications 11
inote 290807.1 Deploying Sun JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite 11i
note 124606.1 Upgrading JInitiator with Oracle E-Business Suite 11inote 1155883.1 Oracle E-Business Suite Desktop Client Hardware and Software Requirements

Forms
----------------------------------------------------------------------------------------------------
note 437878.1 Upgrading OracleAS 10g Forms and Reports in Oracle E-Business Suite Release 12
note 750359.1 Upgrading to Previous Version of OracleAS 10g Forms and Reports in Oracle E-Business Suite Release 12
note 384241.1 Using Forms Socket Mode with Oracle E-Business Suite Release 12
note 438652.1 R12 Forms Runtime Diagnostics (FRD), Tracing And Logging For Forms In Oracle Applicationsnote 125767.1 Upgrading Developer 6i with Oracle Applications 11i
note 232313.1 Information on Previous Versions of Developer 6i Patchsets
note 201340.1 Using Forms Listener Servlet with Oracle Applications 11i
note 150168.1 Obtaining Forms Runtime Diagnostics (FRD) In Oracle Applications 11i
note 760250.1 Diagnosing Forms Mouse Focus Problems Using JRE in Release 11inote 1207184.1Enhanced Signing of Oracle E-Business Suite JAR Files

OracleAS
----------------------------------------------------------------------------------------------------

note 454811.1 Upgrading to the Latest OracleAS 10g 10.1.3.x Patch Set in Oracle E-Business Suite Release 12
note 743518.1 Starting up AS10g services in an EBusiness Suite Release 12 environment


JDBC
----------------------------------------------------------------------------------------------------

note 557194.1 monitor_jdbc_conn.sql - Script to monitor JDBC connections in Apps eBusiness Suite
note 427759.1 How To Prevent Inactive JDBC Connections In Oracle Applications


JVM
----------------------------------------------------------------------------------------------------

note 362851.1 Guidelines to setup the JVM in Apps Ebusiness Suite 11i and R12
note 567551.1 Configuring various JVM tuning parameters for Oracle E-Business suite 11i and R12
note 370583.1 Basic troubleshooting of JVM consuming cpu or too many JDBC connections in Apps 11i


Apache/mod_jserv
----------------------------------------------------------------------------------------------------

note 230688.1 11i Basic Apache/mod_jserv Troubleshooting with Hello.class


Debug/Trace
----------------------------------------------------------------------------------------------------

note 422419.1 R12 - How To Enable and Collect Debug for HTTP, OC4J and OPMN
Note 443671.1 R12 - Collecting Configuration Information for HTTP, OC4J and OPMN
note 427848.1 How to Enable Execution Context ID (ECID) in the R12 access_log
note 455154.1 R12: How To Obtain A Thread Dump When OC4J is shutdown
note 603390.1 How To Create a User Event Trace in R11i / R12


OA Framework
----------------------------------------------------------------------------------------------------

note 391554.1 Oracle Application Framework Documentation Resources, Release 12
note 275880.1 Oracle Application Framework Release 11i Documentation Road Map
note 275875.1 Oracle Application Framework Troubleshooting Release 11i (11.5.10)
note 357597.1 How To Generate A SQL Trace In OA Framework For Oracle Applications


WebCache
----------------------------------------------------------------------------------------------------

note 380486.1 Installing and Configuring Web Cache 10g and Oracle E-Business Suite 12
note 306653.1 Installing and Configuring Web Cache 10.1.2 and Oracle E-Business Suite 11i


JDeveloper
----------------------------------------------------------------------------------------------------

note 330236.1 Configuring JDeveloper For Use With Oracle Applications 11i and R12
note 357218.1 Troubleshooting JDeveloper setup for Oracle Applications
note 416708.1 How to find the correct version of JDeveloper to use with eBusiness Suite 11i or Release 12.x

Portal
----------------------------------------------------------------------------------------------------

note 380484.1 Using Oracle Portal 10g with Oracle E-Business Suite Release 12
note 305918.1 Using Oracle Portal 10g with Oracle E-Business Suite Release 11i



Orace Access Manager / AccessGate
----------------------------------------------------------------------------------------------------

note 975182.1 Integrating Oracle E-Business Suite with Oracle Access Manager using Oracle E-Business Suite AccessGate
note 1077460.1 Troubleshooting Oracle Access Manager and Oracle E-Business Suite AccessGate


Webcenter
----------------------------------------------------------------------------------------------------

note 1074345.1 Using WebCenter 11.1.1 with Oracle E-Business Suite Release 12 
note 557221.1 Oracle WebCenter 10g Application Creation and Deployment Guide for Oracle E-Business Suite Release 12


Secure Enterprise Search
----------------------------------------------------------------------------------------------------

note.566097.1 Oracle E-Business Suite Secure Enterprise Search Release Notes, Release 12.1.1
note.744820.1 Oracle E-Business Suite Secure Enterprise Search Best Practices, Release 12
note.462377.1 Installing Oracle E-Business Suite Secure Enterprise Search, Release 12
note.740499.1 Oracle E-Business Suite Secure Enterprise Search Release Notes, Release 12.0.6
note 726239.1 Oracle E-Business Suite Secure Enterprise Search Troubleshooting Guidelines, Release 12


SOA/BPEL/Webservices
----------------------------------------------------------------------------------------------------

note 556540.1 Installing Oracle E-Business Suite Integrated SOA Gateway, Release 12.1
note 565922.1 Oracle E-Business Suite Integrated SOA Gateway Release Notes, Release 12.1.1
note 815196.1 Oracle E-Business Suite Integrated SOA Gateway 12.1.1 Consolidated One-Off
note 726414.1 Oracle E-Business Suite Integrated SOA Gateway Troubleshooting Guide, Release 12
note 755067.1 Using Oracle BPEL 10g with E-Business Suite Release 12.1.1
Note 782455.1 How Can I Expose E-Business Suite Integration Interfaces as Web Services in Release 11i and R12.0


MWA
----------------------------------------------------------------------------------------------------

note 782162.1 MWA Troubleshooting Tips for Release 12
note 269991.1 MWA Troubleshooting Tips for Release 11i


Certified/Supported
----------------------------------------------------------------------------------------------------
http://blogs.oracle.com/stevenchan/certifications.html
note 363827.1 Rebaselined Oracle Applications Technology Components for Releases 11.5.7, 11.5.8, 11.5.9, and 11.5.10
note 883202.1 Minimum Baseline Patch Requirements for Extended Support on Oracle E-Business Suite 11.5.10 
note 1199724.1  E-Business Suite 11.5.10 Minimum Patch Level and Extended Support Information Center


WEBADI
----------------------------------------------------------------------------------------------------

note 417692.1 Installing, Configuring and Troubleshooting Web ADI (Web Applications Desktop Integrator)
note 452452.1 R12 Installing, Configuring and Troubleshooting Web ADI (Web Applications Desktop Integrator)
note 294739.1 How to enable tracing for WEBADI
note 726989.1 List of Patches in Web Applications Desktop Integrator (Web ADI) for Releases 11i and 12


Diagnostics
----------------------------------------------------------------------------------------------------

note 167000.1 E-Business Suite Diagnostics Installation Guide
note 421245.1 E-Business Suite Diagnostics References for R12
note 179661.1 E-Business Suite Diagnostics 11i Test Catalog

RAC:
-------

 USE: EBS Technology Stack Database and RAC [ID 1463237.2]
Click to add to Favorites                Using Oracle 11g Release 2 Real Application Clusters with Oracle E-Business Suite Release 12 [ID 823587.1]

Guidelines to setup the JVM in Apps Ebusiness Suite 11i and R12




Guidelines to setup the JVM in Apps Ebusiness Suite 11i and R12

The purpose of this document is to provide guidelines for setting up JVMs (Jserv groups/OC4J instances) for online applications in Apps 11i and R12.

This is supposed to be a generic rule of thumb or a starting point. This document is written based on Sun's JDK. Other platforms, like Linux and HP, can also use pretty much the same parameter. AIX  does not have all those parameters and some differences will be pointed. Although there are differences in the JDK parameters, the concepts presented here should apply to all platforms.


Although this has shown to be sufficient for most of the Apps 11i and R12 installations, some additional tuning may be necessary.
Scope and Application
Oracle Application 11i (11.5.9 and up) and R12
Guidelines to setup the JVM in Apps Ebusiness Suite 11i and R12
JDK Version:
It is recommended to use JDK 1.6, because of better performance and additional features. On Solaris and Linux, Sun's JVM will automatically detect whether the Hotspot Server Compiler or the Client Compiler should be used based on the number of CPUs and memory available on the machine.
It is recommended also to use always the latest version for the specific JDK release.
For information about upgrading the JDK, refer to:
Note 401561.1 Using J2SE Version 6.0 with Oracle E-Business Suite 11i
Note 455492.1 Using Latest Update of Java 6.0 with Oracle E-Business Suite Release 12. 
Note 418664.1 Overview of Using Java with Oracle E-Business Suite Release 12
Number of JVMs:
1) For OACoreGroup:
Assumption: eBusiness Suite  is running in a 2-tier environment (middle tier in a separate machine)
- No more than 100 active users per JVM/OC4J instance

- There should be no more than 1 active JVM/OC4J instance per CPU  (ideally, there should be 1 JVM per 2 CPUs)


- Watch out for available memory (make sure that you have enough memory to run all the configured JVMs/OC4J instances without swapping)
Note: Each 'core' in a multi-core system is considered to be a separate CPU. For example, a dual quad-core chip would be the same as stating 2 x 4 x CPU  or 8 CPUs.

In regards to hyper-threading cores, in theory the same thing applies, but in some cases it didn't equate to the exact same as a separate core. One could tune greater then a single CPU, but not treating the same as two CPUs.


2) For  
FormsGroup:
For the forms servlet, the actual JVM/servlet usage is minimal, and a single JVM can normally handle up to 100-250 forms users.
Note: that depends on the type of forms. For more complex forms, those numbers are likely to be substantially lower
Notes: 
a) The number of jvms (oc4j in R12) is configured by the autoconfig variables s_oacore_nprocs, s_disco_nprocs, s_forms_servlet_nprocs and s_xmlsvcs_nprocs. 


b) In 11i, those changes are made in the $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.conf file, while in R12 in the $ORA_CONFIG_HOME/10.1.3/opmn/conf/opmn.xml
Heap Configuration:
1) For OACore, start with the following configuration:
For R12.0 and 11i:
       -Xmx512M -Xms256M -XX:MaxPermSize=128M 
       -XX:NewRatio=2 -XX:+PrintGCTimeStamps
For R12.1 and higher:
      -Xmx1024M -Xms512M -XX:MaxPermSize=256M
      -XX:NewRatio=2 -XX:+PrintGCTimeStamps


     Also, ensure you add the following parameter to the DBC file:


     JDBC\:oracle.jdbc.maxCachedBufferSize=262144
    
Adjust the heap sizes accordingly to minimize garbage collection frequency and user pause times due to full garbage collections.
In JDK 1.6, the JVM detects that you have a server class machine (2 or more CPUs with 2GB or more memory), and will automatically enable Parallel Throughput Garbage Collector. The number of GC threads defaults to the number of CPUs on the machine. If you are running multiple JVMs on the same machine, or if your machine has more than 2 CPUs, to avoid the GC threads to be overly active you should reduce the number of GC threads by using:

      -XX:+UseParallelGC -XX:ParallelGCThreads=2


In most cases, using 2 GC threads should be sufficient. If you are using heap size of over 1GB, you can experiment with 4 GC threads and see if it gives you better performance.  


2) If using Forms in Servlet mode, the following configuration for FormsGroup should be enough for most of the cases:
      -Xmx256M -Xms128M -XX:MaxPermSize=128M -XX:NewRatio=2
Notes: 
a) The heap is configured by the following autoconfig variables:

11i: s_jvm_options, s_forms_jvm_options

R12: s_oacore_jvm_start_option, s_forms_jvm_start_options, 


b) In 11i, those changes are made in the $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.properties file, while in R12 in the $ORA_CONFIG_HOME/10.1.3/opmn/conf/opmn.xml
Additional Recommendations:

1) It is recommended to use Forms in socket mode which is more efficient than servlet mode, unless there is a requirement to use servlet (like, need for external users access).


2) Although the setup of those parameters can be temporarily done manually changing (jserv.conf and jserv.properties in 11i; opmn.xml in R12), the correct way to update those parameters is using the context editor, update the variables s_oacore_nprocs and s_forms_servlet_nprocs, and then run autoconfig.


3) Oracle Development recommends to be on the last ATG rollup patch, which is currently ATG-rollup7 (
Note.783600.1).

5) Make sure to have the following setting in the jserv.properties file:

wrapper.bin.parameters=-DLONG_RUNNING_JVM=true


If there is any entry in jserv.properties setting -DCACHEMODE, either comment it out or remove that entry:

#wrapper.bin.parameters=-DCACHEMODE=LOCAL


6) Check also recommended patches for Performance issues in:

Note 244040.1  - Oracle E-Business Suite Recommended Performance Patches
7) AIX accepts only the parameters "-Xmx" -Xms". The reminder of the parameters should not be used on those platforms. More 
More info about JDK parameters on AIX can be found at 

http://www.ibm.com/developerworks/ibm/library/i-garbage3.html 


8) A very good comparison of jdk parameters can be find at

http://blogs.sun.com/watt/resource/jvm-options-list.html




Garbage - Java object is considered garbage when it can no longer be reached from any pointer in the running program.
Generations – Memory in JVM is managed in terms of generation i.e. Young generation and tenured generation. Memory pool holding object of different ages like young, tenured. If a particular generation fills up, garbage collection occurs.
A. Young generation – Objects are initially allocated in Young generation (most of objects die here). When Young generation fills up, it causes Minor Garbage Collection. Any objects survived after Minor GC (Garbage Collection) are moved toTenured Generation.  Minor Garbage collection is quick as compared to Full/Major GC.
B. Tenured generation – Surviving objects (from young generation) after minor garbage collection are moved to area called tenured generation, When tenured generation fills up it causes major collection (aka Full GC or Full Garbage Collection). Major collection is slow as it involves all live objects.
Garbage Collection (GC) – is program which clears garbage(dead java objects). Garbage Collection work on fundamental principle that majority of java objects die young (quickly after arriving in JVM). There are two kind of Garbage CollectionMinor Garbage Collection and Major Garbage Collection (aka Full GC)
Example of Minor GC -  3824.164: [GC 196725K->141181K(209864K), 0.3295949 secs]
Example of Minor GC -  3841.051: [Full GC 150466K->87061K(217032K), 3.2626248 secs]
Pauses: is the time when application becomes unresponsive because garbage collection is occurring.

Friday, March 15, 2013

Oracle Performance Related Interview Questions/FAQs

1. What you’ll check whenever user complains that his session/database is slow?
2. What is the use of statistics?

3. How to generate explain plan?

4. How to check explain plan of already ran SQLs?

5. How to find out whether the query has ran with RBO or CBO?

6. What are top 5 wait events (in AWR report) and how you will resolve them?
http://satya-dba.blogspot.in/2012/10/wait-events-in-oracle-wait-events.html

db file sequential read  => tune indexing, tune SQL (to do less I/O), tune disks, increase buffer cache. This event is indicative of disk contention on index reads. Make sure all objects are analyzed. Redistribute I/O across disks. The wait that comes from the physical side of the database. It related to memory starvation and non selective index use. Sequential read is an index read followed by table read because it is doing index lookups which tells exactly which block to go to.
db file scattered read => disk contention on full table scans. Add indexes, tune SQL, tune disks, refresh statistics, and create materialized view. Caused due to full table scans may be because of insufficient indexes or unavailability of updated statistics.
db file parallel read => tune SQL, tune indexing, tune disk I/O, increase buffer cache. If you are doing a lot of partition activity then expect to see that wait even. It could be a table or index partition.
db file parallel write => if you are doing a lot of partition activity then expect to see that wait even. It could be a table or index partition.
db file single write => if you see this event than probably you have a lot of data files in your database.

control file sequential read
control file parallel write

log file sync  => committing too often, archive log generation is more. Tune applications to commit less, tune disks where redo logs exist, try using nologging/unrecoverable options, log buffer could be too large.
log file switch completion => May need more log files per group.
log file parallel write  => Deals with flushing out the redo log buffer to disk. Disks may be too slow or have an I/O bottleneck. Look for log file contention.
log buffer space  => Increase LOG_BUFFER parameter or move log files to faster disks. Tune application, use NOLOGGING, and look for poor behavior that updates an entire row when only a few columns change.
log file switch (checkpoint incomplete) => May indicate excessive db files or slow IO subsystem.
log file switch (archiving needed)    => Indicates archive files are written too slowly.
redo buffer allocation retries  => shows the number of times a user process waited for space in the redo log buffer. 
redo log space wait time => shows cumulative time (in 10s of milliseconds) waited by all processes waiting for space in the log buffer.

buffer busy waits/ read by other session  => Increase DB_CACHE_SIZE. Tune SQL, tune indexing, we often see this event along with full table scans, if the SQL is inserting data, consider increasing FREELISTS and/or INITRANS, if the waits are on segment header blocks, consider increasing extent sizes.
free buffer waits => insufficient buffers, process holding buffers too long or i/o subsystem is over loaded. Also check you db writes may be getting clogged up.
cache buffers lru chain  => Freelist issues, hot blocks.
no free buffers  => Insufficient buffers, dbwr contention.

latch free
latch: session allocation
latch: in memory undo latch  => If excessive could be bug, check for your version, may have to turn off in memory undo.
latch: cache buffer chains => check hot objects.
latch: cache buffer handles  => Freelist issues, hot blocks.
direct path write => You wont see them unless you are doing some appends or data loads.
direct Path reads => could happen if you are doing a lot of parallel query activity.
direct path read temp or direct path write temp => this wait event shows Temp file activity (sort,hashes,temp tables, bitmap) check pga parameter or sort area or hash area parameters. You might want to increase them.
library cache load lock
library cache pin => if many sessions are waiting, tune shared pool, if few sessions are waiting, lock is session specific.
library cache lock  => need to find the session holding the lock, look for DML manipulating an object being accessed, if the session is trying to recompile PL/SQL, look for other sessions executing the code.
undo segment extension  => If excessive, tune undo.
wait for a undo record  => Usually only during recovery of large transactions, look at turning off parallel undo recovery.

enque wait events => Look at V$ENQUEUE_STAT

SQL*Net message from client
SQL*Net message from dblink
SQL*Net more data from client
SQL*Net message to client
SQL*Net break/reset to client

7. What are the init parameters related to performance/optimizer?
optimizer_mode = choose
optimizer_index_caching = 90
optimizer_index_cost_adj = 25
optimizer_max_permutations = 100
optimizer_use_sql_plan_baselines=true
optimizer_capture_sql_plan_baselines=true
optimizer_use_pending_statistics = true;
optimizer_use_invisible_indexes=true
_optimizer_connect_by_cost_based=false
_optimizer_compute_index_stats= true;

8. What are the values of optimizer_mode init parameters and their meaning?
optimizer_mode = choose

9. What is the use of AWR, ADDM, ASH?

10. How to generate AWR report and what are the things you will check in the report?

11. How to generate ADDM report and what are the things you will check in the report?

12. How to generate ASH report and what are the things you will check in the report?

13. How to generate STATSPACK report and what are the things you will check in the report?

14. How to generate TKPROF report and what are the things you will check in the report?
The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. Use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.