Tales From A Lazy Fat DBA

Loves all databases! – Its all about performance, troubleshooting & much more …. ¯\_(ツ)_/¯

  • Prashant Dixit is the 'FatDBA' ...
  • Follow me on Twitter

Archive for April, 2013

Me explaining PGA & UGA.

Posted by FatDBA on April 27, 2013

• Program global area (PGA)

PGA is memory specific to operating process that is not shared by other processes in the system. Because PGA is process specific, it is never allocated in the SGA. Access to the PGA is exclusive to the server process.

An analogy for a PGA is a temporary countertop workspace used by a file clerk. In this analogy, the file clerk is the server process doing work on behalf of the customer (client process). The clerk clears a section of the countertop, uses the workspace to store details about the customer request and to sort the folders requested by the customer, and then gives up the space when the work is done.

cncpt219

Not all of the PGA areas will exist in every case. PGA is subdivided into different areas.

– Session Memory: Also known as Stack Space (Session Memory).

– Private SQL Area:
This area holds information about a parsed SQL statement and other session specific information for processing for processing. When a server process executes SQL or PL/SQL code, the process uses the private SQL area to store bind variable values, query execution state information, and query execution work areas.

Private SQL Area is subdivided in to following parts:
Run-Time Area: This area contains query execution state information. For example, the run-time area tracks the number of rows retrieved so far in a full table scan.
Persistent Area: Area contains bind variable values.
– SQL Work Areas:
This area is a combination of Sort Area, Hash Area and Bitmap Merge Area. A sort operator uses the sort area to sort a set of rows. Similarly, a hash join operator uses a hash area to build a hash table from its left input, whereas a bitmap merge uses the bitmap merge area to merge data retrieved from scans of multiple bitmap indexes.
WORKAREA_SIZE_POLICY (AUTO | MANUAL) specifies the policy for sizing work areas. When set to Auto, Work areas used by memory-intensive operators (such as sort, group-by, hash-join, bitmap merge, &  bitmap create) are sized automatically.

PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance.

Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the WORKAREA_SIZE_POLICY parameter to AUTO. This means that SQL working areas used by memory-intensive SQL operators (such as sort, group-by, hash-join, bitmap merge, and bitmap create) will be automatically sized. A nonzero value for this parameter is the default since, unless you specify otherwise, Oracle sets it to 20% of the SGA or 10 MB, whichever is greater.

Setting PGA_AGGREGATE_TARGET to 0 automatically sets the WORKAREA_SIZE_POLICY parameter to MANUAL. This means that SQL workareas are sized using the *_AREA_SIZE parameters.
In below image, a pointer (Cursor) pointing towards ‘Private SQL Area’ to fetch information. The client process is responsible for managing private SQL areas.

 

cncpt324
* NOTE: For dedicated sessions, the UGA is a part of the RAM heap in the PGA that controls user sessions space for sorting and hash joins.  If you are forced to use shared servers (the Multi-threaded Server or MTS) the UGA is inside the SGA large_pool_size region).
In sum, when using a dedicated connection, the User Global Area (UGA) supplements the PGA with additional memory for the user’s session, such as private SQL areas and other session-specific information such as sorting and session message queues.

 

………………………………………………………….

 
• UGA (User Global Area)
The UGA is session memory, which is memory allocated for session variables, such as logon information, and other information required by a database session. Essentially, the UGA stores the session state.

NET8A_416
The UGA must be available to a database session for the life of the session. For this reason, the UGA cannot be stored in the PGA when using a shared server connection because the PGA is specific to a single process. Therefore, the UGA is stored in the SGA when using shared server connections, enabling any shared server process access to it. When using a dedicated server connection, the UGA is stored in the PGA.

 

UGA has following sections:
Session Variables
OLAP Pool – This sessions opens automatically whenever a user queries a dimensional object like CUBE.

 

Posted in Basics | Tagged: | Leave a Comment »

Database Upgrade (Oracle 10R2 or 11.2.0.1 to Oracle 11gR2(11.2.0.2/3)

Posted by FatDBA on April 19, 2013

Describing pre/post checklist and upgrade process details for upgrading a Oracle 10R2 or 11.2.0.1 to Oracle 11gR2(11.2.0.2/3)

Please check OS version as upgrade is only certified for RH 5.

cat /etc/red*
Red Hat Enterprise Linux Server release 5.4 (Tikanga)

**For 11.2.0.2.3, ensure RHEL is upgraded to 5.5 and above.

$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.5 (Tikanga)

**For 11.2.0.2.3, ensure ASM is upgraded to 11.2.0.3.  (Check dir $ORA_CRS_HOME then cd ..  then pwd)

Please ensure that 11.2.0.3.x86_64 is pushed on the box and tnsnames.ora and listener.ora are updated accordingly.

Verify all ET standards
* Index Placement
* DB User profile assignment
Need to install Oracle 11g using runinstaller.

Phase 1: PRE UPGRADE TASKS
Phase 2: UPGRADING THE DATABASE TO ORACLE 11G R2
Phase 3: POST UPGRADE TASKS.

Phase 1 : Pre upgrade tasks

Step 1 : Create the directory
mkdir  preupgrade_bkup

Step 2 : Disable all the below jobs :
– All batch and cron jobs.
– Backup
– Archive removal and rotation
– Application jobs
– Stats collection job
– standby job at standby server and make sure all arch has been applied at DR/STANDBY server side.
Step 3 : Run the gather stats job

a) Gathering System Stats
exec dbms_stats.gather_schema_stats(‘SYS’);

b) Gathering Dictionary Stats
exec dbms_stats.gather_dictionary_stats;

c) Gathering all fixed objects stats (dynamic performance tables)
exec dbms_stats.gather_fixed_objects_stats;

d) Gathering Database Stats
exec dbms_stats.gather_database_stats(options => ‘GATHER AUTO’);

e) Run the stats for all the Application schemas :
exec DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>’OWNER’, ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, DEGREE=>DBMS_STATS.AUTO_DEGREE, CASCADE=>TRUE, OPTIONS=>’GATHER STALE’);

exec  DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>’OWNER’, ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, DEGREE=>DBMS_STATS.AUTO_DEGREE, CASCADE=>TRUE);

Step 4 : Capture Database link information :

spool create_dblink.info

SELECT
‘create ‘||DECODE(U.NAME,’PUBLIC’,’public ‘)||’database link ‘||CHR(10)
||DECODE(U.NAME,’PUBLIC’,Null, U.NAME||’.’)|| L.NAME||chr(10)
||’connect to ‘ || L.USERID || ‘ identified by
||L.PASSWORD|| using  || L.host || ‘
||chr(10)||’;’ TEXT

FROM sys.link$ L, sys.user$ U WHERE L.OWNER# = U.USER# ;

spool off

Step 5 : Create Pre-Upgrade BackupSET.
Run the database backup, be it RMAN (full not incremental) or hot backup whatever is configured in the environment.
Step 6: Verify Disk free space for archive mount point, we should have 15-20 gigs of space for arch (command to verify the space : df –h .)
Step 7 : Ensure no files need media recovery:

SQL>  select * from v$recover_file;
no rows selected

*** Make sure currently no backup is running for the database, if yes then end backup using command.

SQL > Alter database end backup;
Database altered.

Step 7 : Verify DB version and installed options :

Ensure the properties for SYSTEM ,SYS user and database component status should be valid and default tablespace should be SYSTEM and SYSAUX respectively.

SQL> select username, default_tablespace from dba_users where username in (‘SYS’,’SYSTEM’);

=============================================================================================
SYS                            SYSTEM
SYSTEM                         SYSTEM

2 rows selected.

Database Components Information
select COMP_NAME, VERSION, status from dba_registry;

Database Patches Information
select ACTION_TIME, NAMESPACE, VERSION, COMMENTS, BUNDLE_SERIES from sys.registry$history Order by ACTION_TIME;

Database DST Information

col VERSION for 999
SELECT * FROM v$timezone_file;

Step 8: Purge the recyclebin :
PURGE DBA_RECYCLEBIN;

Step 9: Recompile invalid objects:
SQL>@?/rdbms/admin/utlrp.sql
Step 10 : Spool file before running upgrade tool :

SQL> spool pre_upgrade_database_details.log
SQL> @? /rdbms/admin/utlu112i.sql
SQL> spool off

Step 10: Stop the agent and listener :

emctl stop agent
lsnrctl stop <listener-name>

Step 11: Bring down the DB :
SQL>shutdown immediate;

Keeping in mind if the DB is an RAC change the CLUSTER_DATABASE=false
SQL> alter system set cluster_database = false scope = spfile;

Phase 2: Upgrading to Oracle Database 11g Release 2

[oracle]$ export ORACLE_HOME=/u01/app/oracle/oracle/product/product/11.1.0/db_1
[oracle]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle]$ export ORACLE_SID=orcl
[oracle]$ export TNS_ADMIN=$ORACLE_HOME/network/admin

connected to the database sys as sysdba
sqlplus “/ as sysdba” –> will be connected to idle instance

Step 1 : Create pfile (change compatible to 11.2.0.3) and start the instance using new pfile :
SQL > startup nomount;

Step 2: Create passwordfile
orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password={password} entries =80 force=y

Step 3 :  Upgrade the DB, upgrd.sql will take around 30 minutes to complete
SQL> spool $ET_INSTANCE_ROOT/bkup/upgrade/upgrade_to_11203.log
SQL> startup upgrade
SQL> @?/rdbms/admin/catupgrd.sql

SQL> startup

*** Can check the spool file for any errors …

Phase III: Post-Install Oracle Database 11gR2

Step 1: Verify Upgrade Status:

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

Oracle Database 11.2 Post-Upgrade Status Tool 01-28-2011 10:45:32 .
Component Status Version HH:MM:SS .
Oracle Server . VALID 11.2.0.3.0 00:10:43
Gathering Statistics . 00:00:40 Total Upgrade Time: 00:12:03

PL/SQL procedure successfully completed.

Steps 2: Need to verify the components after the upgrade :

SQL >select COMP_NAME, VERSION, status from dba_registry;
SQL>select ACTION_TIME, NAMESPACE, VERSION, COMMENTS, BUNDLE_SERIES from sys.registry$history Order by ACTION_TIME;
SQL>SELECT * FROM v$timezone_file;

Step 3: Need to upgrade the timezone to 14

SQL>conn / as sysdba
SQL>shutdown immediate;
SQL>startup upgrade;

SQL> purge dba_recyclebin;
DBA Recyclebin purged.

SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(11);
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

SQL> col value format a10
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value  FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE ‘DST_%’  ORDER BY PROPERTY_NAME;

PROPERTY_NAME                  VALUE
—————————— ———-
DST_PRIMARY_TT_VERSION         11
DST_SECONDARY_TT_VERSION       7
DST_UPGRADE_STATE              Upgrade

3 rows selected.

SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where    UPGRADE_IN_PROGRESS=’YES’;
no rows selected

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);
An upgrade window has been successfully started.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE ‘DST_%’ ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE —————————— ———- DST_PRIMARY_TT_VERSION 14 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE 3 rows selected.

SQL> select * from v$timezone_file;

FILENAME                VERSION
——————– ———-
timezlrg_14.dat              14

Step 4: Verify invalid objects and compile them :
SQL>@?/rdbms/admin/utlrp.sql

Step 4: Start the database, agent, listner cleanly:

SQL> shutdown immediate
SQL> startup mount
SQL> alter system set cluster_database=TRUE scope=spfile; <— Only required for RAC

SQL> startup (If RAC, do it on both nodes)
lsnrctl start <listener-name>

Step 5: Run the gather statistics job again :
Step 6: Enable all the jobs which we commented before upgrade activity
Step 7 : Verify the database parameters.

Posted in Advanced | Tagged: , | Leave a Comment »

EMCTL Error (OC4J Configuration Issue)

Posted by FatDBA on April 12, 2013

Noticed that some of my test databases monitored in OEM Grid Control started showing status of ‘Agent Unreachable’. This is what we are getting when we tried checking status of the Agent on the servers …

[oracle@prashant ~]$ emctl status dbconsole
OC4J Configuration issue. /u01/app/oracle/product/11.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_prashant_orcl not found.

Below provided steps are sequence of steps that we should adopt in such scenario and will help us to fix the issue most of the times.

  • Set the SID
  • Drop/Deconfig current EM repository.
  • Recreate EM repository for the same DB.
  • Re-check the Status using EMCTL commands for both Dbconsole & Agent

1. Set the SID
[oracle@prashant ~]$ echo $ORACLE_SID
orcl

2. Drop/Deconfig current EM repository.
This step will require you to feed deconfig process following details.
SID, Listener Port Number, SYS and SYSMAN Password.

[oracle@prashant ~]$ emca -deconfig dbcontrol db -repos drop;

STARTED EMCA at Apr 12, 2013 10:40:14 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: orcl
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: Y

Apr 12, 2013 10:41:14 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/REDANT/emca_2013_04_12_22_40_14.log.
Apr 12, 2013 10:41:14 PM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration
WARNING: EM is not configured for this database. No EM-specific actions can be performed.
Apr 12, 2013 10:41:14 PM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl
WARNING: Error initializing SQL connection. SQL operations cannot be performed
Apr 12, 2013 10:41:14 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) …

Apr 12, 2013 10:45:42 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 12, 2013 10:45:43 PM

3. Recreate EM repository for the same DB.
This step will require you to feed config process following details.
SID, Listener Port Number, Email address (Optional), SMTP server details (Optional) along with SYS and DBSNMP and SYSMAN Password.

[oracle@prashant ~]$ emca -config dbcontrol db -repos create;

STARTED EMCA at Apr 12, 2013 10:46:17 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: orcl
Listener port number: 1521
Listener ORACLE_HOME [ /u01/app/oracle/product/11.2.0/db_1 ]:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
—————————————————————–

You have specified the following settings

Database ORACLE_HOME ……………. /u01/app/oracle/product/11.2.0/db_1

Local hostname ……………. prashant
Listener ORACLE_HOME ……………. /u01/app/oracle/product/11.2.0/db_1
Listener port number ……………. 1521
Database SID ……………. orcl
Email address for notifications ……………
Outgoing Mail (SMTP) server for notifications ……………

—————————————————————–
Do you wish to continue? [yes(Y)/no(N)]:

Apr 12, 2013 10:46:59 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/REDANT/emca_2013_04_12_22_46_16.log.
Apr 12, 2013 10:47:00 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) …

Apr 12, 2013 10:54:15 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Apr 12, 2013 10:54:28 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) …
Apr 12, 2013 10:55:43 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Apr 12, 2013 10:55:47 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Apr 12, 2013 10:55:47 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives …
Apr 12, 2013 10:56:06 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Apr 12, 2013 10:56:06 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) …
Apr 12, 2013 10:56:40 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Apr 12, 2013 10:56:40 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) …

Apr 12, 2013 10:57:23 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Apr 12, 2013 10:57:23 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://prashant:5500/em <<<<<<<<<<<
Apr 12, 2013 10:57:32 PM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************  WARNING  ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.  The encryption key has been placed in the file: /u01/app/oracle/product/11.2.0/db_1/prashant_REDANT/sysman/config/emkey.ora.   Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************
Enterprise Manager configuration completed successfully

4. Re-check the Status using EMCTL commands for both Dbconsole & Agent.

[oracle@prashant ~]$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
https://prashant:5500/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
——————————————————————
Logs are generated in directory /u01/app/oracle/product/11.2.0/db_1/prashant_REDANT/sysman/log

[oracle@prashant ~]$ emctl status agent
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
—————————————————————
Agent Version     : 10.2.0.4.2
OMS Version       : 10.2.0.4.2
Protocol Version  : 10.2.0.4.2
Agent Home        : /u01/app/oracle/product/11.2.0/db_1/prashant_REDANT
Agent binaries    : /u01/app/oracle/product/11.2.0/db_1
Agent Process ID  : 26794
Parent Process ID : 26757
Agent URL         : https://prashant:3938/emd/main
Repository URL    : https://prashant:5500/em/upload/
Started at        : 2013-04-12 22:56:42
Started by user   : oracle
Last Reload       : 2013-04-12 22:56:42
Last successful upload                       : 2013-04-12 22:58:34
Total Megabytes of XML files uploaded so far :    10.04
Number of XML files pending upload           :        0
Size of XML files pending upload(MB)         :     0.00
Available disk space on upload filesystem    :    26.87%
Data channel upload directory                : /u01/app/oracle/product/11.2.0/db_1/prashant_REDANT/sysman/recv
Last successful heartbeat to OMS             : 2013-04-12 22:58:24
—————————————————————
Agent is Running and Ready

 

OEM Final screen is up!

Untitled

Posted in Advanced | Tagged: , | Leave a Comment »

ADDM Report Test Sample

Posted by FatDBA on April 4, 2013

Below provided is the sample ADDM (Automatic Database Diagnostic Monitor) from one of my Test Database. Before that i would like to explain what is this ADDM and it’s advantages:

To view in more efficient way, click the Link: My ADDM (Sample) Report.txt – Notepad

The Automatic Database Diagnostic Monitor (ADDM) analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks. For each of the identified issues it locates the root cause and provides recommendations for correcting the problem. An ADDM analysis task is performed and its findings and recommendations stored in the database every time an AWR snapshot is taken provided the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. The ADDM analysis includes the following.

  • CPU load
  • Memory usage
  • I/O usage
  • Resource intensive SQL
  • Resource intensive PL/SQL and Java
  • RAC issues
  • Application issues
  • Database configuration issues
  • Concurrency issues
  • Object contention

ADDM Report for Task ‘TASK_964’
——————————-

Analysis Period
—————
AWR snapshot range from 99 to 105.
Time period starts at 03-APR-13 07.08.18 PM
Time period ends at 04-APR-13 07.20.18 PM

Analysis Target
—————
Database ‘REDANT’ with DB ID 629811920.
Database version 11.2.0.1.0.
ADDM performed an analysis of instance orcl, numbered 1 and hosted at
prashant.

Activity During the Analysis Period
———————————–
Total database time was 452 seconds.
The average number of active sessions was .01.

Summary of Findings
——————-
Description                          Active Sessions      Recommendations
Percent of Activity
———————————–  ——————-  —————
1  I/O Throughput                       .01 | 100            3
2  Undersized instance memory           0 | 7.75             1
3  Hard Parse Due to Invalidations      0 | 5.3              1
4  “Other” Wait Class                   0 | 4.95             0
5  Commits and Rollbacks                0 | 4.14             1
6  PL/SQL Compilation                   0 | 4.05             1
7  Checkpoints Due to MTTR              0 | 3.44             1
8  Checkpoints Due to DROP or TRUNCATE  0 | 3                0
9  Hard Parse Due to Sharing Criteria   0 | 2.14             1

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Findings and Recommendations
—————————-

Finding 1: I/O Throughput
Impact is .01 active sessions, 100% of total activity.
——————————————————
The throughput of the I/O subsystem was significantly lower than expected.

Recommendation 1: Host Configuration
Estimated benefit is .01 active sessions, 100% of total activity.
—————————————————————–
Action
Consider increasing the throughput of the I/O subsystem. Oracle’s
recommended solution is to stripe all data files using the SAME
methodology. You might also need to increase the number of disks for
better performance.
Rationale
During the analysis period, the average data files’ I/O throughput was
14 K per second for reads and 3.2 K per second for writes. The average
response time for single block reads was 14 milliseconds.

Recommendation 2: Host Configuration
Estimated benefit is .01 active sessions, 100% of total activity.
—————————————————————–
Action
Consider slowing down RMAN or Data Pump activity, or scheduling these
jobs when user activity is lower.
Rationale
The I/O throughput on data and temp files was divided as follows: 0% by
RMAN, 29% by Data Pump, 0% by Recovery and 70% by all other activity.

Recommendation 3: Host Configuration
Estimated benefit is .01 active sessions, 100% of total activity.
—————————————————————–
Action
The performance of some data and temp files was significantly worse than
others. If striping all files using the SAME methodology is not
possible, consider striping these file over multiple disks.
Rationale
For file /u01/app/oracle/oradata/orcl/sysaux01.dbf, the average response
time for single block reads was 132 milliseconds, and the total excess
I/O wait was 479 seconds.
Related Object
Database file
“/u01/app/oracle/oradata/orcl/sysaux01.dbf”

Symptoms That Led to the Finding:
———————————
Wait class “User I/O” was consuming significant database time.
Impact is 0 active sessions, 19.25% of total activity.

 

Finding 2: Undersized instance memory
Impact is 0 active sessions, 7.75% of total activity.
—————————————————–
The Oracle instance memory (SGA and PGA) was inadequately sized, causing
additional I/O and CPU usage.
The value of parameter “memory_target” was “500 M” during the analysis period.

Recommendation 1: Database Configuration
Estimated benefit is 0 active sessions, 7.75% of total activity.
—————————————————————-
Action
Increase memory allocated to the instance by setting the parameter
“memory_target” to 875 M.

Symptoms That Led to the Finding:
———————————
Hard parsing of SQL statements was consuming significant database time.
Impact is 0 active sessions, 20.38% of total activity.
Contention for latches related to the shared pool was consuming
significant database time.
Impact is 0 active sessions, 2.69% of total activity.
Wait class “Concurrency” was consuming significant database time.
Impact is 0 active sessions, 2.94% of total activity.
Wait class “User I/O” was consuming significant database time.
Impact is 0 active sessions, 19.25% of total activity.

 

Finding 3: Hard Parse Due to In-validations
Impact is 0 active sessions, 5.3% of total activity.
—————————————————-
Cursors were getting invalidated due to DDL operations. This resulted in
additional hard parses which were consuming significant database time.

Recommendation 1: Application Analysis
Estimated benefit is 0 active sessions, 5.3% of total activity.
—————————————————————
Action
Investigate appropriateness of DDL operations.

Symptoms That Led to the Finding:
———————————
Hard parsing of SQL statements was consuming significant database time.
Impact is 0 active sessions, 20.38% of total activity.
Contention for latches related to the shared pool was consuming
significant database time.
Impact is 0 active sessions, 2.69% of total activity.
Wait class “Concurrency” was consuming significant database time.
Impact is 0 active sessions, 2.94% of total activity.

 

Finding 4: “Other” Wait Class
Impact is 0 active sessions, 4.95% of total activity.
—————————————————–
Wait class “Other” was consuming significant database time.
Database latches in the “Other” wait class were not consuming significant
database time.

No recommendations are available.

 

Finding 5: Commits and Rollbacks
Impact is 0 active sessions, 4.14% of total activity.
—————————————————–
Waits on event “log file sync” while performing COMMIT and ROLLBACK operations
were consuming significant database time.

Recommendation 1: Host Configuration
Estimated benefit is 0 active sessions, 4.14% of total activity.
—————————————————————-
Action
Investigate the possibility of improving the performance of I/O to the
online redo log files.
Rationale
The average size of writes to the online redo log files was 6 K and the
average time per write was 3 milliseconds.
Rationale
The total I/O throughput on redo log files was 1.3 K per second for
reads and 1.5 K per second for writes.
Rationale
The redo log I/O throughput was divided as follows: 0% by RMAN and
recovery, 52% by Log Writer, 0% by Archiver, 0% by Streams AQ and 47% by
all other activity.

Symptoms That Led to the Finding:
———————————
Wait class “Commit” was consuming significant database time.
Impact is 0 active sessions, 4.14% of total activity.

 

Finding 6: PL/SQL Compilation
Impact is 0 active sessions, 4.05% of total activity.
—————————————————–
PL/SQL compilation consumed significant database time.

Recommendation 1: Application Analysis
Estimated benefit is 0 active sessions, 4.05% of total activity.
—————————————————————-
Action
Investigate the appropriateness of PL/SQL compilation. PL/SQL
compilation can be caused by DDL on dependent objects.

 

Finding 7: Checkpoints Due to MTTR
Impact is 0 active sessions, 3.44% of total activity.
—————————————————–
Buffer cache writes due to setting of the obsolete parameters
“fast_start_io_target”, “log_checkpoint_interval” and “log_checkpoint_timeout”
were consuming significant database time.

Recommendation 1: Database Configuration
Estimated benefit is 0 active sessions, 3.44% of total activity.
—————————————————————-
Action
Oracle’s recommended solution is to control MTTR setting using the
“fast_start_mttr_target” parameter instead of the
“fast_start_io_target”, “log_checkpoint_interval” and
“log_checkpoint_timeout” parameters.

Symptoms That Led to the Finding:
———————————
The throughput of the I/O subsystem was significantly lower than
expected.
Impact is .01 active sessions, 100% of total activity.
Wait class “User I/O” was consuming significant database time.
Impact is 0 active sessions, 19.25% of total activity.

 

Finding 8: Checkpoints Due to DROP or TRUNCATE
Impact is 0 active sessions, 3% of total activity.
————————————————–
Buffer cache writes due to DROP and TRUNCATE operations had a significant
impact on the throughput of the I/O subsystem.

No recommendations are available.

Symptoms That Led to the Finding:
———————————
The throughput of the I/O subsystem was significantly lower than
expected.
Impact is .01 active sessions, 100% of total activity.
Wait class “User I/O” was consuming significant database time.
Impact is 0 active sessions, 19.25% of total activity.
Wait class “Application” was consuming significant database time.
Impact is 0 active sessions, 3.8% of total activity.

 

Finding 9: Hard Parse Due to Sharing Criteria
Impact is 0 active sessions, 2.14% of total activity.
—————————————————–
SQL statements with the same text were not shared because of cursor
environment mismatch. This resulted in additional hard parses which were
consuming significant database time.
Common causes of environment mismatch are session NLS settings, SQL trace
settings and optimizer parameters.

Recommendation 1: Application Analysis
Estimated benefit is 0 active sessions, 2.14% of total activity.
—————————————————————-
Action
Look for top reason for cursor environment mismatch in
V$SQL_SHARED_CURSOR.

Symptoms That Led to the Finding:
———————————
Hard parsing of SQL statements was consuming significant database time.
Impact is 0 active sessions, 20.38% of total activity.
Contention for latches related to the shared pool was consuming
significant database time.
Impact is 0 active sessions, 2.69% of total activity.
Wait class “Concurrency” was consuming significant database time.
Impact is 0 active sessions, 2.94% of total activity.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Additional Information
———————-

Miscellaneous Information
————————-
Wait class “Configuration” was not consuming significant database time.
CPU was not a bottleneck for the instance.
Wait class “Network” was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.

The database’s maintenance windows were active during 23% of the analysis
period.

End of Report
Report written to addmrpt_1_99_105.txt
SQL> !
[oracle@prashant ~]$ ls
addmrpt_1_99_105.txt  Desktop  on.lst
[oracle@prashant ~]$ vi addmrpt_1_99_105.txt
Wait class “Application” was consuming significant database time.
Impact is 0 active sessions, 3.8% of total activity.

 

Finding 9: Hard Parse Due to Sharing Criteria
Impact is 0 active sessions, 2.14% of total activity.
—————————————————–
SQL statements with the same text were not shared because of cursor
environment mismatch. This resulted in additional hard parses which were
consuming significant database time.
Common causes of environment mismatch are session NLS settings, SQL trace
settings and optimizer parameters.

Recommendation 1: Application Analysis
Estimated benefit is 0 active sessions, 2.14% of total activity.
—————————————————————-
Action
Look for top reason for cursor environment mismatch in
V$SQL_SHARED_CURSOR.

Symptoms That Led to the Finding:
———————————
Hard parsing of SQL statements was consuming significant database time.
Impact is 0 active sessions, 20.38% of total activity.
Contention for latches related to the shared pool was consuming
significant database time.
Impact is 0 active sessions, 2.69% of total activity.
Wait class “Concurrency” was consuming significant database time.
Impact is 0 active sessions, 2.94% of total activity.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Additional Information
———————-

Miscellaneous Information
————————-
Wait class “Configuration” was not consuming significant database time.
CPU was not a bottleneck for the instance.
Wait class “Network” was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.

The database’s maintenance windows were active during 23% of the analysis
period.

Posted in Advanced | Tagged: | 1 Comment »

 
%d bloggers like this: