client access slow for weblogic forms / Minimizing Forms 11g Application’s First Time Startup overhead/ Forms access faster form client end

When  client accesses Forms application for the first time, java run-time classes bundled in JAR files are downloaded to client JRE cache.  These files include frmall.jar,frmwebutil.jar and jacob.jar. Cumulative size of these files is more than 2mb. If your application has some additional JAR files for example, JAR file containing icons, more time will be required to download these additional files. On a slow connection, it creates a significant difference. Remember these files are downloaded only once when the application is accessed for the first time.  JRE caches these files. when application is accessed next time, JRE reuses these files if no changes are made to these files on server. If JRE detects new version of JAR files on server, files are downloaded again.

We can reduce this download time by keeping a copy of these files on client side prior to accessing the application. If we place these file on the default CLASSPATH of JRE , JRE use these files form client side instead of downloading it from server. Suppose JRE is  installed  in C: drive, you can place these files in following directory,

C:\Program Files\Java\jre6\lib\ext

Now when you access Forms 11g application, JRE loads all required JAR files from  \ext directory.  This saves significant time on a slow connection.

You can get the jar files from Application server also from following location

/u01/oracle/middleware/Oracle_FRHome1/forms/java

Reference :

http://www.exploreoracle.com/2010/07/02/minimizing-forms-11g-application%E2%80%99s-first-time-startup-overhead/

Find Database Session Idle/Inactive more then 2 hours

### Find the Database sessions who are Idle more than two hours but connected through application###

select 'alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate ;',logon_time,machine,status,osuser,lockwait
from v$session
where machine  like 'SBLC%'
and status ='INACTIVE'
and logon_time

Note: Here machine name should be application servers name.

###Kill the found session###

alter system kill session '656,31665' immediate ;

alter system kill session '1011,2111' immediate ;

alter system kill session '3478,45629' immediate ;

alter system kill session '5979,43717' immediate ;

alter system kill session '6293,17609' immediate ;

If any issue just give me a buzz....cheers.... 

Specific Table/Schema Analyze in Oracle

--Specific Schema Analyze in Oracle---

EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15, cascade => TRUE);

--Specific Table Analyze in Oracle---
 
EXEC DBMS_STATS.gather_table_stats('STLBAS', 'STTRNDTL', estimate_percent => 25, cascade => TRUE);

EXEC DBMS_STATS.gather_table_stats('STLBAS', 'STFETRAN', estimate_percent => 25, cascade => TRUE);

EXEC DBMS_STATS.gather_schema_stats('STLBAS', estimate_percent => 15, cascade => TRUE);

Find Top 5 wait events in Database

Performance optimization of databases is one of the routine tasks of DBAs. This task becomes very easy when you have Oracle Enterprise Manager tool installed. You can generate various reports from AWR and analyze the health of the database and then tune it accordingly. This is normal in every DBA’s life.

But what if you don’t have the OEM installed? This is very much possible in a test environment. In this case, you need to generate the reports manually. You need to query the database to extract the data requried for analysis.

For such requirements, we have the below query to find out “Top 5 wait events in the database”. The query gives Date, Event_Name and Total_Waits.

Query to find Top 5 wait events in database

select Day, Event_name, Total_wait from (
select day, event_name, sum(event_time_waited) total_wait,
row_number() over (partition by day order by sum(event_time_waited) desc) rn from (
SELECT   to_date(to_char(begin_interval_time,'dd/mm/yyyy'),'dd/mm/yyyy') day,s.begin_interval_time, m.*
    FROM (SELECT ee.instance_number, ee.snap_id, ee.event_name,
                 ROUND (ee.event_time_waited / 1000000) event_time_waited,
                 ee.total_waits,
                 ROUND ((ee.event_time_waited * 100) / et.total_time_waited,
                        1
                       ) pct,
                 ROUND ((ee.event_time_waited / ee.total_waits) / 1000
                       ) avg_wait
            FROM (SELECT ee1.instance_number, ee1.snap_id, ee1.event_name,
                           ee1.time_waited_micro -ee2.time_waited_micro event_time_waited,
                         ee1.total_waits - ee2.total_waits total_waits
                    FROM dba_hist_system_event ee1 JOIN dba_hist_system_event ee2
                         ON ee1.snap_id = ee2.snap_id + 1
                       AND ee1.instance_number = ee2.instance_number
                       AND ee1.event_id = ee2.event_id
                       AND ee1.wait_class_id <> 2723168908
                       AND ee1.time_waited_micro - ee2.time_waited_micro > 0
                  UNION
                  SELECT st1.instance_number, st1.snap_id,
                         st1.stat_name event_name,
                         st1.VALUE - st2.VALUE event_time_waited,
                         1 total_waits
                    FROM dba_hist_sys_time_model st1 JOIN dba_hist_sys_time_model st2
                         ON st1.instance_number = st2.instance_number
                       AND st1.snap_id = st2.snap_id + 1
                       AND st1.stat_id = st2.stat_id
                       AND st1.stat_name ='DB CPU'
                       AND st1.VALUE -st2.VALUE > 0
                         ) ee
                 JOIN
                 (SELECT et1.instance_number, et1.snap_id,
                         et1.VALUE -et2.VALUE total_time_waited
                    FROM dba_hist_sys_time_model et1 JOIN dba_hist_sys_time_model et2
                         ON et1.snap_id = et2.snap_id + 1
                       AND et1.instance_number = et2.instance_number
                       AND et1.stat_id = et2.stat_id
                       AND et1.stat_name = 'DB time'
                       AND et1.VALUE - et2.VALUE > 0
                         ) et
                 ON ee.instance_number = et.instance_number
               AND ee.snap_id = et.snap_id
                 ) m
         JOIN
         dba_hist_snapshot s ON m.snap_id = s.snap_id
) group by day ,event_name
order by day desc, total_wait desc
)where rn < 6

Create Baseline for AWR Report

Baselines

A baseline is a pair of snapshots that represents a specific period of usage. Once baselines are defined they can be used to compare current performance against similar periods in the past. You may wish to create baseline to represent a period of batch processing.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_baseline (
    start_snap_id => 210, 
    end_snap_id   => 220,
    baseline_name => 'batch baseline');
END;
/

The pair of snapshots associated with a baseline are retained until the baseline is explicitly deleted.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_baseline (
    baseline_name => 'batch baseline',
    cascade       => FALSE); -- Deletes associated snapshots if TRUE.
END;
/

Baseline information can be queried from the DBA_HIST_BASELINE view.

How to create AWR report manually

Oracle database 10g

If you do not have Oracle Enterprise Manager tool installed then you can create the AWR reports manually using the following commands:

The following workload repository views are available:

• V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
• V$METRIC - Displays metric information.
• V$METRICNAME - Displays the metrics associated with each metric group.
• V$METRIC_HISTORY - Displays historical metrics.
• V$METRICGROUP - Displays all metrics groups.
• DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
• DBA_HIST_BASELINE - Displays baseline information.
• DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
• DBA_HIST_SNAPSHOT - Displays snapshot information.
• DBA_HIST_SQL_PLAN - Displays SQL execution plans.
• DBA_HIST_WR_CONTROL - Displays AWR settings.

1. Creating Snapshot
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/

The list of the snapshot Ids along with database Ids is availabe in the view DBA_HIST_SNAPSHOT.

2. Dropping a Range of Snapshots.Refer to the DBA_HIST_SNAPSHOT view column SNAP_ID to view available snapshots. To delete contain SNAP_ID from from 102 to 122,

BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 102, high_snap_id => 122, dbid => 8187786345);
END;
/

3. Modifying Snapshot SettingsIf you want to modify the retention period as 43200 minutes (30 days), the interval between each snapshot is specified as 30 minutes, and the number of Top SQL to flush for each SQL criteria as 100 then use following:

BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200,
interval => 30, topnsql => 100, dbid => 7123356265);
END;
/
The dbid is optional.

4. Extract the AWR DataThe awrextr.sql script extracts the AWR data for a range of snapshots from the database into a Data Pump export file. Once created, this dump file can be transported to another system where the extracted data can be loaded. To run the awrextr.sql script, you need to be connected to the database as the SYS user.

To extract AWR data at the SQL prompt, enter:

SQL> @$ORACLE_HOME/rdbms/admin/awrextr.sql

5. Load the AWR DataOnce the export dump file is transported to the target system, you can load the extracted AWR data using the awrload.sql script. The awrload.sql script will first create a staging schema where the snapshot data is transferred from the Data Pump file into the database. The data is then transferred from the staging schema into the appropriate AWR tables. To run the awrload.sql script, you need to be connected to the database as the SYS user.

To load AWR data at the SQL prompt, enter:

SQL> @$ORACLE_HOME/rdbms/admin/awrload.sql

6. Generate AWR ReportsThe awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids.
To generate an HTML or text report for a range of snapshot Ids, run the awrrpt.sql script at the SQL prompt:

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

First, you need to specify whether you want an HTML or a text report.

Enter value for report_type: text

Specify the number of days for which you want to list snapshot Ids.

Enter value for num_days: 2

After the list displays, you are prompted for the beginning and ending snapshot Id for the workload repository report.

Enter value for begin_snap: 95
Enter value for end_snap: 97

Next, accept the default report name or enter a report name. The default name is accepted in the following example:

Enter value for report_name:
Using the report name awrrpt_1_95_97

The workload repository report is generated.

awrrpt.sql
The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids.

awrrpti.sql
The awrrpti.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids on a specified database and instance.

awrsqrpt.sql
The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a SQL statement.

awrsqrpi.sql
The awrsqrpi.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids on a specified database and instance. Run this report to inspect or debug the performance of a SQL statement on a specific database and instance.

awrddrpt.sql
The awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods.

awrddrpi.sql
The awrddrpi.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.