Oracle 11g ADRCI Command-Line Utility

ADR Command Interpreter – ADRCI

The ADR Command Interpreter (ADRCI) is a utility that enables you to investigate problems, view health check reports, and package for upload and upload first-failure diagnostic data to Oracle Support. This is all done within a new command-line environment that Oracle delivers with Oracle 11g called ADRCI – ADR Command Interpreter. ADRCI provides you with the ability to view the names of the trace files in the ADR and to view the alert logs with XML tags stripped out and without content filtering.

ADR Structure, Location and Overview of the ADR Contents

ADR – Automatic Diagnostic Repository (ADR), is a directory structure that resides on your filesystems outside of the Oracle Database, hence, it can be viewed while the Database is down. Basically it is a container for alert logs, trace logs, audit logs and the same files in XML format that reside in a structured directory organization. The Oracle Initialization Parameter – DIAGNOSTIC_DEST determines the location of the ADR Base. If you set ORACLE_BASE as most everyone does then DIAGNOSTIC_DEST is set to the directory designated by ORACEL_BASE. If you do not set the environment variable ORACLE_BASE then Oracle defaults the location of the ADR to ORACLE_HOME/log.

Within the ADR Base, there can be multiple ADR homes, where each ADR home is the root directory for all diagnostic data – traces, dumps, alert log, …. for a particular instance for one particular Oracle product or component. For example, if you use Oracle Real Application Clusters, the environment for ASM, each database instance and each ASM instance has an ADR home. All ADR homes share the same hierarchical directory structure.

The location of an ADR home is given by the following path, which starts at the base directory of the ADR structure:

diag/product_type/product_id/instance_id

where:

product_type can be rdbms, asm, client
product_id can be database name
instance_id is the SID

ADR Home Subdirectories

Diagnostic data is stored within various subdirectories with the ADR structure. Keep in mind that the ADR Base can be shared by other products and other Oracle databases (multiple SIDs).

The subdirectory alert contains the XML format of the alert log, cdump still contains the core dump files, incident (new) contains multiple subdirectories which are named for each particular incident or problem and each subdirectory contains the dumps for that specific problem. The trace subdirectory contains the background and server process trace files as well as any SQL trace files. There are other subdirectories which contain information such as the health check reports, monitor reports, etc.

ADR for RAC (Real Application Clusters)

… for another day

Oracle 11g Database Fault Diagnostic Infrastructure

ADR – Automatic Diagnostic Repository (ADR)

Oracle 11g introduces a new directory structure for organizing the flat file administrative objects such as the alert log, trace files, dumps, audit logs, etc.

The Structure looks like the following screenshot:

Oracle has introduced not only a new directory template for organizing the various Oracle Administrative files – alert log, audit logs, trace files, core dumps, etc. but a entirely new methodology for Managing Diagnostic Data. It is a comprehensive infrastructure for collecting and managing diagnostic data. Diagnostic data in Oracle’s terms includes the trace files, dumps and core files that exist in previous releases along with new types of diagnostic data that enables Oracle users as well as Oracle’s Support Group to identify, investigate, trace and resolve problems.

Now when a critical error occurs, it is automatically assigned an incident number and diagnostic data for that error (such as its associated trace files) are captured and and tagged with this incident number. The data is then stored in the Automatic Diagnostic Repository (ADR) – a flat file based repository outside the database that looks like the above picture. This data can later be retrieved by referencing the incident number and analyzed.

The motivating factors behind this from Oracle’s perspective are (taken from Oracle’s Database Administrator’s Guide 11g Release 1 11.1):

* First-failure diagnosis
* Problem prevention
* Limiting damage and interruptions after a problem is detected
* Reducing problem diagnostic time
* Reducing problem resolution time
* Simplifying customer interaction with Oracle Support

In order to accomplish these goals Oracle focused on automating the capture of diagnostic data and storing it outside of the database in the new ADR structure, standardizing the trace formats, health checks – the DBA can invoke these manually as well as automating these health checks, Data Recovery Advisor – integrates with the database health checks and RMAN to display data corruption problems, assess the extent of each problem and classify the problem (critical, high priority, low priority), description of the impact, recommendations, an automating the repair process, SQL Test Case Builder – for many SQL-related problems, obtaining a reproducible test case is key to resolving or diagnosing the issue accurately – this tool automates the sometimes difficult and time-consuming process of gathering as much information as possible and the environment in which it occurred – you can upload this information to Oracle Support to enable their support personnel to easily and accurately reproduce the problem – that is the concept according to Oracle and the introduction of Incident Packaging Service (IPS) and Incident Packages – is the key to Oracle’s automation of the whole diagnostic methodology and infrastructure. The IPS enables the gathering of diagnostic data – traces, dumps, health check reports, … – pertaining to a critical error and package this information into a zip file for transmission to Oracle Support.

Since the key to managing all of this information is the Incident Number this number is tagged to all of the related files which enables searching through all of this information and selecting all of the files with a specific Incident Number for addition to a zip file for transmission to Oracle Support. The Incident Packaging Service identifies the required files automatically during the first phase of the collection process – IPS collects these files and stores them in an intermediate logical structure call and incident package (package). Packages are stored in the Automatic Diagnostic Repository (ADR). If you choose to, you can access this intermediate logical structure, view and modify its contents, add or remove additional diagnostic data at any time and when you are ready you can create or recreate the zip file and transmit the file to Oracle Support.

Incidents and Problems

The new Infrastructure introduces two concepts for Oracle Database: problems and incidents.

A problem is a critical error in the database. Critical errors manifest as internal errors, such as ORA-00600, or other severe errors, such as ORA-07445 (operating system exception) or ORA-04031 (out of memory in the shared pool). Problems are tracked in the ADR. Each problem has a *problem key*, which is a text string that describes the problem. It includes an error code (such as ORA 600) and in some cases, one or more error parameters.

An Incident is a single occurrence of a problem. When a problem (critical error) occurs multiple times, an incident is created for each occurrence. Incidents are time stamped and tracked in the Automatic Diagnostic Repository (ADR). Each incident is identified by a numeric incident ID, which is unique within the ADR. When an incident occurs, the database:

* Makes and entry in the alert log
* Sends an incident alert to Oracle Enterprise Manager (Enterprise Manager)
* Gathers first-failure diagnostic data about the incident in the form of dump files (incident dumps)
* Tags the incident dumps with the Incident ID
* Stores the incident dumps in and ADR subdirectory created for that incident

Diagnosis and resolution of a critical error usually begins with an initial incident alert and is displayed on the Enterprise Manager Database Home page. You can then drill down and pull up its associated details.

Incident Flood Control

Given the fact that Oracle is being allowed to generate diagnostic data you could be wondering what is to prevent runaway incident generation that could result in consuming too much space and possibly bring your Oracle instances to a halt because the filesystem where the ADR is located at becomes full and the OS can no longer write to the alert logs. To address this potential hazard Oracle applies what it terms flood-control to incident generation after certain thresholds are reached. A flood-controlled incident is an incident that generates and alert log entry, that is recorded in the ADR, but it does not generate incident dumps. Flood-controlled incidents provide a method for informing you that a critical erros is occurring repeatedly but Oracle essentially prevents itself from adding to the problem by controlling the amount of data generated. You can choose to view or hide flood-controlled incidents when viewing incidents with Enterprise Manager or the ADR utility ADRCI.

Basic Threshold Levels for flood-control are predetermined and cannot be changed. The are defined as follows:

After five incidents occur for the same problem in one hour, any subsequent incidents for the same exact problem key are flood-controlled. Normal (non-flood-controlled) recording of incidents for that problem key begin again after an hour has expired.

After twenty-five (25) incidents occur for the same problem in one day, subsequent incidents for this problem key are flood-controlled. Normal recording of incidents for that problem key will resume after the 24 hour window has expired.

In addition, after fifty (50) incidents for the same problem key occur in one hour, two hundred fifty (250) incidents for the same problem key occur in one day, subsequent incidents for this problem key are not recorded at all in the ADR. In these cases, the database writes a message to the alert log indicating that no further incidents will be recorded. As long as incidents continue to be generated for this problem key, this message is added to the alert log every ten minues until the hour or the day expires. Upon expiration of the hour or day, normal recording of incidents for that problem key begin again.

DBA Scheduler Jobs – Hanging

DBA Scheduler Jobs

We’ve had a situation for the last 2 to 3 days where our daily exports for one of our PeopleSoft environments would hang while processing one of the last set objects, actually for Oracle 11g full exports its the the fourth set of objects from the end – exporting post-schema procedural objects and actions. This set of objects is followed by user history table, default and system auditing options and lastly the statistics.

I tried to compile the stored procedures using utlrp.sql as well as the parallelized version – utlrcmp.sql. According to Oracle parallel recompilation can exploit multiple CPUs to reduce the time taken to recompile invalid objects. The degree of parallelism is specified by the first argument to UTL_RECOMP.RECOMP_PARALLEL(). If the specified degree of parallelism is NULL, 0 or negative, RECOMP_PARALLEL computes a degree of parallelism as the product of the Oracle parameters “cpu_count” and “parallel_threads_per_cpu”. For RAC, the degree of parallelism is the sum of the individual settings on each node in the cluster.

Another side note, whenever you recompile your invalid objects Oracle writes a fairly significant amount of data to its system tables and is fairly I/O intensive. Here are some examples:

1. Recompile all objects sequentially:
execute utl_recomp.recomp_serial();
2. Recompile objects in schema SYSADM sequentially:
execute utl_recomp.recomp_serial(‘SYSADM’);
3. Recompile all objects using 4 parallel threads:
execute utl_recomp.recomp_parallel(4);
4. Recompile all objects in schema SYSADM using the default degree of parallelism:
execute utl_recomp.recomp_parallel(NULL,’SYSADM’);

To get back to my original problem. My exports weren’t finishing and I couldn’t recompile all of the invalid objects using either one of the two procedures. I then started looking at what would cause this problem. I queried the database to see what Enterprise Manager jobs may be running:

select owner, job_name, session_id, running_instance, elapsed_time, cpu_used from dba_scheduler_running_jobs;

I saw there were two jobs running and essentially just spinning doing nothing. Then I looked at the Blocking Tree in OEM (rather than use my scripts, it was 4 AM Pacific Time, I live in Eastern time and I was lazy) EM showed no blocking locks but there were hanging locks (EM Hang Analysis) and then I dug a little deeper and saw that there were several sessions blocked by the PURGE_LOG Scheduled Job so I killed it, ran utlrcmp.sql to make sure my stored procedures were clean and produced a full export of this particular database. In addition to the export task many of our monitoring jobs were hanging or failing due to this problem.

I’ve also had similar issues as a result of running my custom stat jobs in cron while leaving the delivered stats job enabled. I had situations where the crontab job may start before the EM stats job has completed resulting in hangs. That is why in an earlier post I showed an example of disabling the EM Scheduler Job so it wouldn’t interfere with our crontab jobs.

select owner, job_name, job_class, enabled from dba_scheduler_jobs;

THe above query will show the jobs and their status – Enabled or Disabled, what class they belong to and owner. The jobs that belong to Resource Plan – DEFAULT_MAINTENANCE_PLAN will execute based on the definition for that Resource Plan. See screenshot below:

Picture 33.pdf

Screenshot of Purge_Log Job and you can see this job has been waiting for 5 days (look in the Wait section for Wait Duration). During the 5 days I travelled to San Francisco to attend Oracle OpenWorld, that’s my excuse for leaving this unresolved for 5 days).

Picture 22.pdf

Screenshot of Scheduler Jobs:

Picture 55.pdf

Oracle 10046 Trace for New Users

Executing a 10046 Level 8 Trace

Say you have a batch job, report, SQL statement, etc. that is running slow and you would like to get some further information on it because upon your initial cursory review of the Explain Plan it looks fine. So what do you do? You can stop guessing about what is causing the problem and generate a more detailed trace by executing what is known as a 10046 Level 8 Trace.

You can generate this trace by following these steps:

Tracing Your Own Session

Using SQL*Plus
1. show parameter user_dump_dest (the path show in the output is where your trace file will be placed)
2. alter system set timed_statistics = true (in case this isn’t your default)
3. alter system set max_dump_file_size = unlimited (make sure your trace file isn’t prematurely truncated)
4. alter session set events ‘10046 trace name context forever, level 8′
5. execute your SQL statement, report, etc.
6. exit SQL*Plus
7. at the OS command prompt change your current directory to the location (value) indicated by the output of step 1. Locate the trace file with most current time stamp
8. you are now going to be using the Oracle TKPROF command-line tool:
tkprof trace_file_name output_file_namesort=prsela,exeela,fchela

where:

trace_file_name = the name of the trace file you generated in step 5
output_file_name = the name of the file you supplied to TKPROF to write it’s output results to

9. view the output file, Unix more output_file_name
10. check out the Row Source Operation section. Does this look the same as your explain plan you generated prior to using TKPROF? The output generated by TKPROF is the real execution plan.

To Trace Someone Else’s Session

Using SQL*Plus
1. show parameter user_dump_dest (same as above) to locate the output of your trace files
2. find the SID and SERIAL# of the session you want to trace for, you can attempt to query v$session for this purpose – look for something you know, terminal, osuser, oracle user, etc. to help you identify the Oracle Session Identifier and Oracle Serial # of the session running the SQL statement, Batch Job, Report, etc.
3. alter system set timed_statistics=true (not necessary if enabled at the instance level in initSID.ora or spfileSID.ora), show parameter timed_statistics will show you the default setting
4. alter system set max_dump_file_size = unlimited
5. exec sys.dbms_system.set_ev(sid,serial#,100468,8,”)
6. if at all possible let the job, SQL, report, whatever the item is run to completion if you can and exec sys.dbms_system.set_ev(sid,serial#,10046,0,”)
7. change current directory to the user_dump_dest directory and locate the .trc file for the session you just traced
8. At the OS prompt execute the following statement:
tkprof trace_file_name output_file_name sort=prsela,exeela,fchela
9. view the output
10. Check out the Missing Row Source Operation information. If you happened to stop the trace before the cursors were closed then try exiting the application to stop the trace instead of implicitly stopping the trace by executing the statement in step 6. Meaning, start over again but don’t execute step 6 until either you know the program or SQL statement has finished its execution, that way you will help to ensure that the open cursors were closed and you should have the information you were looking for.

Oracle Scheduler – Chains

Using Chains – Oracle Scheduler

Oracle 10g R2 and 11g now have the ability to specify a chain that can be used to tie a group of programs together. In a nutshell, a chain is similar to a simple decision tree. Each step in the chain is managed by a set of rules.

In its most simple form to create a chain and use it:

1. Create a Chain Object using the procedure – CREATE_CHAIN
2. Define the steps in the Chain using the procedure – DEFINE_CHAIN_STEP
3. Add the Rules for the chain using the procedure – DEFINE_CHAIN_RULES
4. Enable the Chain using the procedure – ENABLE
5. Create a job that points to the chain using the procedure – CREATE_JOB

Example:

I have a series of shell scripts that generate various sets of Statistics. I can create a Chain to accomplish this instead of creating individual crontab entries as follows:

Create the Chain

BEGIN
DBMS_SCHEDULER.CREATE_CHAIN (
CHAIN_NAME => ’stats_job1′
);
END;
/

Now Define the Steps for the Chain

BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
CHAIN_NAME => ’stats_job1′,
STEP_NAME => ’step1′,
PROGRAM_NAME => ’stan_gather_stats’;
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
CHAIN_NAME => ’stats_job1′,
STEP_NAME => ’step2′,
PROGRAM_NAME => ’stan_dict_stats’;
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
CHAIN_NAME => ’stats_job1′,
STEP_NAME => ’step3′,
PROGRAM_NAME => ‘del_tree_stats’,
);
END;
/

Define the Corresponding Rules for the Chain

BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
CHAIN_NAME => ’stats_job1′,
CONDITION => ‘TRUE’,
ACTION => ‘START step1′;
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
CHAIN_NAME => ’stats_job1′,
CONDITION => ’step1 SUCCEEDED’,
ACTION => ‘START step2′;
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
CHAIN_NAME => ’stats_job1′,
CONDITION => ’step1 COMPLETED and step1 NOT SUCCEEDED’,
ACTION => ‘START step3′;
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
CHAIN_NAME => ’stats_job1′,
CONDITION => ’step2 COMPLETED OR step3 COMPLETED’,
ACTION => ‘END’;
);
END;
/

Chains can be altered even if they are executing. Chains may be nested inside other chains and chains can be executed immediately for testing purposes. Chains, chain steps and chain rules may be dropped.

Audit Sys Database Activity

Audit Sys Database Activity for 10g+

Users connecting to any production database as SYSDBA is always a key item for scrunity during Internal and Outside Auditors whether your organization is private or public. They frown upon any shell script, crontab process, batch process, etc. where a connection is made to a database where SYSDBA is a role that is invoked. Even if a non-generic user account has been created this is still a source of attention.

The ability to audit activity performed while a session has been instantiated and the SYSDBA role invoked will go a long way in easing their concerns only if you can produce audit reports detailing the actions performed, etc.

The Oracle database provides functionality to keep a record of activities performed by a user by offering the AUDIT command to provide that function. Prior to version 9.2, users connected as SYS were not audited. In Oracle 9.2, 10g and 11g a new database parameter was introduced to configure the database to audit activity by users connected as SYS. When you set ‘AUDIT_SYS_OPERATIONS’ = TRUE, each SYS user connection creates a text file in the ‘AUDIT_FILE_DEST’ directory. The file is created with the ‘.aud’ extension and will contain each SQL statement executed during each session.

Many legal and compliance statures and regulations require all user activity to be tracked, especially operations performed by system administrators– Unix, DBA, etc. You can use this parameter to attempt to comply with these regulations. In order to determine your degree of compliance it is advisable to meet with the governing bodies to review the specific requirements for your installation and discuss your existing state of compliance, identify the variances and come up with an action plan to address and decencies. It looks much better if you take a proactive approach and demonstrate that you are making an effort to either illustrate compliance and/or identify and areas where action may be required or called for in order to remediate any weaknesses, etc.

However, by enabling this parameter will go a long way with demonstrating willingness to comply. In addition to generating the audit files there may be a requirement to collect, archive, report on, etc. Depending on your environment there may be a very large number of audit files produced on a daily basis and of course even though they are relatively small files there is a disk space requirement that will have to be taken into consideration. The majority of the factors pertaining to Audit and Compliance are site specific and the requirements for one organization could be very different from another organization. Whether your company is private or public will be a considerable factor in determining the overall requirements.

Even if you have Oracle options active in the database, these do not record in the audit trail actions taken by users connecting as SYSDBA. With Oracle 10g+ the SQL statements executed by users connected as SYSDBA are now recorded in the audit files generated when SYSDBA is used. A review of these generated files will let you see what actions are performed by users connecting as SYSDBA.

You can send the audit trace files to a remote sever or possibly email them to maintain a copy that is off-loaded from the server to prevent deletion either intentionally or by maintenance scripts that purge trace files by date or other filters.

Oracle Flashback

Flashback Query for Quick Recovery

Check to see if the Database is in Flashback mode:

SQL> select flashback_on from v$database;

Put a database into Flashback mode if it isn’t already:

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database flashback on;
SQL> alter database open;

RMAN can be used to flashback a database:

RMAN> flashback database to SCN xxxx;
RMAN> flashback database to timestamp (sydate-1/24);
RMAN> flashback database to timestamp to_timestamp(‘2008-06-01 16:00:00′,’YYYY-MM-DD HH24:MI:SS’);
RMAN> flashback database to restore point “before_batch”;
RMAN> database to before resetlogs;

If an intrusion occurs or some other potential crisis:

SQL> select * from emp as of timestamp(sysdate – 1/48);

SQL> flashback table emp to before drop;
SQL> drop rename to emp_save;

To review the contents of your recyclebin:

SQL> select * from user_recyclebin;

You can check on the longevity or duration of the Flashback in a database by querying the V$FLASHBACK_DATABASE_LOG view.

As an alternative to forensically viewing data as it was can be accomplished by deploying the Oracle Workspace Manager, which became available with the Oracle 9i release. The DBA can create multiple versions of table data; isolate collections of changes to table data for long transactions lasting days or weeks; and keep a persistent history of changes.

Technorati Tags:
Oracle, RMAN, PeopleTools

Introduction To Perl – Part II

Double-Quoted Strings Continued

Construct Meaning
========= ==========================================
\n                     Newline
\r                     Return
\t                     Tab
\f                     Formfeed
\b                     Backspace
\v                     Vertical tab
\a                     Bell
\e                     Escape
07                     Any octal ASCII value (here, 007 = bell)
\x7f                   Any hex ASCII value (here, 7f = delete)
\cC                   Any control character (here, control C)
\\                     Backslash
\”                     Double quote
\l                     Lowercase next character
\L                     Lowercase all following letters until \E
\u                     Uppercase next letter
\U                     Uppercase all following letters until \E
\E                     Terminate \L or \U

Double-quoted strings are variable interpolated which means that some variable names within the strings will be replaced with their current values when the string is used in a program.

Operators

An operator produces a new value (or result) from one or more operands (values). An example of an operator is the ‘+’ sign which represents the plus operator because it takes two values (numbers) and sums them together producing a new value or result.

Perl’s operators and expressions are a superset of those provided in other ALGOL/Pascal type programming languages of which C and C++ are examples. An operator expects either numeric or string operators to operate on (you can use a combination as well). Perl performs a conversion of the operand using internal rules which are described later on.

Operators for Numbers

As would be expected Perl provides the typical operators for arithmetic – addition, subtraction, multiplication and division. In keeping with the earlier Fortran analogy Perl provides a FORTRAN-like exponentiation operator which previously wasn’t available in C (it may be now but i have looked at the C or C++ standard in several years so I don’t know if it is or isn’t at this time), this operator uses the double asterisk operator **, for example, 3**3 equates to 3 to the third power or 3*3*3 which equals 27 I believe. If the result or value of the operation can not fit into a double-precision floating-point number (remember our earlier note that Perl treats all numbers as double-precision floating point numbers) such as a negative number to a non-integer exponent or a large enough number to an exponent whose result exceeds the boundaries of double-precision floating-point then Perl will issue a fatal error.

In addition to the exponential operator, Perl also provides the modulus operator as does C. An example of an operation using a modulus operator is: 20 % 3 which translates to english as the remainder of dividing 20 by 3 and on a further note if you use real numbers such as 20.5, 4.5, etc. the operands are converted to their integer values as the first step in the modulus operation which would translate to 20.5 / 4.5 -> 20 / 4 and in this case the result of the modulus operation would be 0 because there is no remainder when 20 is divided by 4. In the previous example 20 % 3 the remainder would be 3.

The logical comparison operators are the same as those found in the C programing language which are ( < <= == > != <=), and the comparison of two values numerically, returning a true of false value (result). To illustrate one of the operators we’ll use the greater than operator, for 10 > 12 Perl will return false because 10 is not greater than 12, while 10 != 10 will return false because 10 IS equal to 10. The representation of the return results (values) are in the same format as C uses, meaning Perl returns the value of zero equals False, one equals or equates to True.

String Operators
Perl offers the period “.” as the operator to concatenate string values making concatenation very easy and intuitive. An example would be: “hello world” . “world” which equates to “hello worldhello world” and another example would be: “hello world”.” “.”hello world”.”\n” translates to “hello world hello world\n”.

Whereas, other languages and unix shells offer variations on the concatenation theme in Perl you have to explicitly instruct Perl to perform the concatenation operation by specifying the concatenation character “.”.

The set of comparison operators for strings are Fortran-like too, as in lt for less-than, gt for greater-than, etc. The operators compare the ASCII values of the characters the string is comprised of.

PeopleSoft Query Issue / No Rows Returned – Oracle 11g

This SQL doesn’t return any rows in an Oracle 11g HP-UX 11i V3 Itanium database but did last week on Oracle 10g R2 on Itanium HP-UX 11i V2.

 select a.emplid,to_char(a.effdt,’DD-MON-YY HH24:MI:SS’),a.effseq from ps_job a where a.emplid = ‘140810′ and a.effdt = (select max(effdt) from ps_job where emplid = a.emplid and empl_rcd = a.empl_rcd and effdt <= sysdate) and a.effseq = (select max(effseq) from ps_job where emplid = a.emplid and empl_rcd = a.empl_rcd and effdt = a.effdt);

But this SQL does:

select a.emplid,to_char(a.effdt,’DD-MON-YY HH24:MI:SS’),a.effseq from ps_job a where a.emplid = ‘140810′ and a.effdt = (select max(effdt) from ps_job where emplid = a.emplid and empl_rcd = a.empl_rcd and effdt <= sysdate) and a.effseq = (select max(effseq) from ps_job where emplid = a.emplid and empl_rcd = a.empl_rcd and trunc(effdt) = a.effdt);

The only difference is the trunc(effdt) code. But if you run this SQL, it shows that’s not the case because it doesn’t return any rows:

select a.emplid,to_char(a.effdt,’DD-MON-YY HH24:MI:SS’),a.effseq from ps_job a where a.emplid = ‘140810′ and effdt <> trunc(effdt);

Impact of the move from Oracle 10g R2 to Oracle 11g and HP-UX 11i v3 on Itanium is a change in the way the CBO is generating explain plans and in order to fix this issue PeopleSoft suggests to force the   CBO to not generate plans to sort bitmap chains thereby forcing Oracle’s CBO to generate a different execution plan.

SQL> alter system set “_b_tree_bitmap_plans”=FALSE scope=both;

Oracle 11g enforces the above setting immediately without the need to bounce the database.

After further testing and researching PeopleSoft’s Customer Connection site we implemented the following Oracle 11g hidden parameters to eliminate our performance issues which allowed us to set the above hidden parameter back to its default value of TRUE:

*._awr_flush_threshold_metrics=TRUE

*._optimizer_cost_based_transformations=OFF

*._b_tree_bitmap_plans=TRUE

The combination of the above parameters resolved our issue. Please take care before setting any hidden parameters in your Oracle Initialization pfile or spfile. Unless you have clearly demonstrated that a SQL Performance problem exists and is eliminated with the introduction of one or more hidden parameters you should not implement these types of parameters unless Oracle recommends you do so or you have tested enough SQL cases to make a determination that performance is indeed improved. Introduction one of parameter may resolve a particular SQL execution issue but could possibly introduce performance issues with other SQL statements.

 

Tuning Oracle Database Connections for Performance

Some performance issues can be related to an excessive amount of database connections. The following areas can be checked to adjust the number of Oracle Database connections from E1. 

1- Using different server names for the same Oracle database TNS name (ERPPD). 

Using a different server name will cause additional, unnecessary connections.  The server name and TNS names must both match, in a case-sensitive compare, before JDB will re-use an existing connection. 

Within the “Work With Data Sources” application, if the “Data Source Use” is “DB” (not “SVR”), and the “Data Source Type” is “O” (for Oracle), then, for each occurrence of a specific “TNS/ODBC/JDBNET Data Source” value, it should repeat the same “Server Name” value. 

The easiest way accomplish this during setup, is to use the server name where the Oracle database is located.  If there are multiple server pathways to get to the same database, they should arbitrarily pick one, and use it consistently.  The EnterpriseOne code passes the TNS name to Oracle, but not the server name, so Oracle will use the server path that is configured in the Oracle TNSNAMES.ORA file (or Oracle Names Server, if used). 

The same TNS and server names should be used in the JDE.INI files, as those that are shown in the “Work With Data Sources” application.  Under “[DB SYSTEM SETTINGS]“, the .INI “Database=” is the same value as the “TNS/ODBC/JDBNET Data Source” value in the app, and the .INI “Server=” is the same value as the “Server Name” value in the app. 

2- OracleServerHandleReuse  - Jde.ini setting 

This JDE.INI setting is no longer used.  The Oracle server handles are no longer used for multiple connections.  Although it is supposed to be a performance benefit to re-use the server handles, the Oracle database does not do sufficient locking to prevent conflicts between connections on the same server handle.The conflicts can show up under very high database usage, and only from a few applications.  

In cases where the above has been looked at and still is considered that there are too many connections to the DB per user here are some recommendations: 

a) Timer Initiated Disconnect or Dead Connection Detection (SQL*Net release 2.1 and later only). 

Dead connection detection is a feature that allows SQL*Net to identify connections that have been left hanging by the abnormal termination of a client. On a connection with Dead Connection Detection enabled, a small probe packet is sent from server to client at a user-defined interval (usually several minutes). If the connection is invalid (usually due to the client process or machine being unreachable), the connection will be closed when an error is generated by the send operation, and the server process will exit. 

This feature minimizes the waste of resources by connections that are no longer valid. It also automatically forces a database rollback of uncommitted transactions and locks held by the user of the broken connection. 

Specify the SQLNET.EXPIRE_TIME=n parameter in your SQLNET.ORA file (usually in $ORACLE_HOME/network/admin). This parameter will instruct SQL*Net to send a probe through the network to the client every n minutes, if the client doesn’t respond, it will be killed. 

NOTE: This parameter is only useful on the database server side, specifying it on a client workstation will have no effect. 

b)  JDBJ.INI maxConnections.  JAS specific 

maxConnection parameter in the jdbj.ini 

This value is the total number of db connections for dbusers per data source. Additional database connection requests beyond this value will be queued for the next available connection. If this value is exceeded, the user request will fail generating JDBC errors in the jas.log.This value can also be monitored in SAW on the statistics tab, connection utilization. A connection pool hit ratio approaching 100% is optimum and indicates that most of the connections are serviced from the pool. Set the value to the maximum number of JDBC database connections you want to allow for the system.Default is 50. Begin with a ratio of 4 users per connection and adjust from there. User characteristics will influence this parameter. 

Sample of the jdbj.ini section 

[JDBj-CONNECTION POOL] 

minConnection=5 

maxConnection=50 

initialConnection=5 

poolGrowth=5 

c) Run a script on the database server, each node with incoming connections grouped by 

1) db user 

2) program 

3) machine name 

Scripts: 

select username, count(*) 

from v$session 

group by username; 

select machine count(*) 

from v$session 

group by machine; 

 

select program, count(*) 

from v$session 

group by program; 

this way we can find out whats the source of incoming connections and forward this to developers to take corrective steps if there’s a need for it. 

d) JDBJ.INI resultSet.  JAS specific resultSet parameter in the jdbj.ini 

Increasing the ResultSet timeout will cause the connection to stay dedicated to the given user, which means that connections are not able to be shared between users and it results in new connections to Database. 

In no case should the resultSet be more than 2 minutes, 

When the resultSet expires the user gets a warning that they have to press Find again to renew the Grid resultSet 

e) Number of Database Proxy Users and DataSources 

A Database Connection Pool is keyed off Proxy User and DataSource, so if we have a high number of Proxy user /Datasource combinations it will result in as many Database Connection Pools. Connections to Database cannot be shared across Connection Pools which results in higher number of Connections to Database.