Hi All,
Thanks for visiting,reading and giving suggestion regarding my blog,I wish you
all very HAPPY AND PROSPEROUS NEW YEAR 2012.
Last year(2011) was interesting for me,as I learn new technologies Oracle SOA(Service Oriented Architecture) and ODI(Oracle Data Integrator).Apart from these
I stepped forward to learn Oracle 11g RAC,Dataguard and Oracle EBS 12.1.1 and later versions.
Going forward this year(2012) I'm interested in extending my skillsets and implement
these skillsets more in my work to gain perfection.It won't be easy,but I will be
trying my best to maintain these skillsets by reading and practise.
MY GOALS FOR THE YEAR 2012::
1)Practise and Master the skillsets Oracle 11g RAC,Dataguard.
2)Practise and Master the skillsets Oracle EBS R12.1.1 & later versions.
3)Practise and Master the skillsets Oracle SOA 11g.
4)Practise and Master the skillsets Oracle Data Integrator 11g.
5)In order to get more perfection I need to take Oracle 11g RAC,Dataguard training
from Oracle which will also act as base for my prepartion of OCM Exam.
6)Practise and Complete PMP Certification.
7)Bring Excellence to my work by adopting best work practises in terms of technology
and work practises.
Oracle and DBA Market Survey as per me:
DBA Market Demanded the following major skillsets:
1)RAC: RAC skillset I found ruled the DBA market in the year 2011(last year).It makes sense as it is used by lot of financial and highly critical Databases.
2)Dataguard: Dataguard skillset was the second major skillset which I found ruled the DBA market.As many companies grow their data,they worry for protecting it from Disaster.
ASM,RMAN,Performance tuning were not behind and were also in demand as always.
Apps DBA Market Survery:
Apps DBA market Demanded the follwing skillset:
1)Clonning and Patching:
The most repeated task of the Apps DBA again ruled the last year(2011).It can be called as refreshing the Database and Application and Clonning
in other sense.Patching is again the repeated task for the Apps DBA
2)SHARED APPL_TOP ,LOAD BALANCING AND PCP:
As experience grows the expectation of companies for you will also.I found few companies specifically asking the combination of RAC and Apps for
senior Apps consultant/Consultant positions on a large scale.
DMZ configuration,Apps DBA Administraion(AD Utilities) and Workflow configuration skillsets were also not behind.
3)Oracle SOA market survey:
Oracle SOA 11g market was hot last year.I found many companies
asking for Oracle SOA Developer/Admins.Along with these,Oracle Weblogic server skillsets were in demand.
4)ODI market survey:
Oracle Data Integrator 11g(ODI 11g) was the hot ETL tool last year as
many companies were moving Data from one source to many targets.
5)Oracle Fusion Apps DBA(Fusion DBA) market survey:
I found a new job role emerged in 2011,It is the combination of Oracle Apps DBA and Fusion Middleware Administration(SOA Administration + Weblogic Administration).It is a kind of profile where company is asking too much from one candidate,considering future demand of Oracle Weblogic(BEA product).Some companies
were specific on hiring Oracle Fusion Middleware Administrators.
Note:The above survey is just based on my personal experience and should not be considered for any publicity or stratergy.
I will be writing my next survey in next year beginning.All the best.Have a bright and skillfull year 2012.
Enjoy and Happy Oracle learning.
Sunday, January 1, 2012
Monday, December 26, 2011
Manually Installing Oracle Data Mining in Oracle 11g
Hi,
If you compare Oracle 11g and previous versions there are lot of changes in terms of installing Data Mining component,
For installing check the MY ORACLE SUPPORT note :
How To Manually Install Data Mining In Oracle 11g? [ID 818314.1].
Information about data mining:
The brief information about Oracle Data mining can be obtained from the below link:
http://docs.oracle.com/html/B14339_01/1intro.htm
In short,If you do the Oracle installation with Enterprise Edition installation type ,it selects Oracle Data Mining option by default.In Oracle Database 11g,the Data Mining metadata is created with 'SYS' metadata when you select the Create Database option.
For verifying the installation of Oracle Data Mining:
Check if the parameter DATA MINING is set to TRUE,if it is than Oracle Data Mining component is already installed in the Database.The V$OPTION is very useful for checking the various functionality exist in your Database or not as seen from the below results:
SQL>SET LINESIZE 1000
SQL>SELECT *FROM V$OPTION;
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Partitioning TRUE
Objects TRUE
Real Application Clusters FALSE
Advanced replication TRUE
Bit-mapped indexes TRUE
Connection multiplexing TRUE
Connection pooling TRUE
Database queuing TRUE
Incremental backup and recovery TRUE
Instead-of triggers TRUE
Parallel backup and recovery TRUE
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Parallel execution TRUE
Parallel load TRUE
Point-in-time tablespace recovery TRUE
Fine-grained access control TRUE
Proxy authentication/authorization TRUE
Change Data Capture TRUE
Plan Stability TRUE
Online Index Build TRUE
Coalesce Index TRUE
Managed Standby TRUE
Materialized view rewrite TRUE
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Materialized view warehouse refresh TRUE
Database resource manager TRUE
Spatial TRUE
Automatic Storage Management FALSE
Export transportable tablespaces TRUE
Transparent Application Failover TRUE
Fast-Start Fault Recovery TRUE
Sample Scan TRUE
Duplexed backups TRUE
Java TRUE
OLAP Window Functions TRUE
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Block Media Recovery TRUE
Fine-grained Auditing TRUE
Application Role TRUE
Enterprise User Security TRUE
Oracle Data Guard TRUE
Oracle Label Security FALSE
OLAP TRUE
Basic Compression TRUE
Join index TRUE
Trial Recovery TRUE
Data Mining TRUE
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Online Redefinition TRUE
Streams Capture TRUE
File Mapping TRUE
Block Change Tracking TRUE
Flashback Table TRUE
Flashback Database TRUE
Transparent Data Encryption TRUE
Backup Encryption TRUE
Unused Block Compression TRUE
Oracle Database Vault FALSE
Result Cache TRUE
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
SQL Plan Management TRUE
SecureFiles Encryption TRUE
Real Application Testing TRUE
Flashback Data Archive TRUE
DICOM TRUE
Active Data Guard TRUE
Server Flash Cache TRUE
Advanced Compression TRUE
XStream TRUE
Deferred Segment Creation TRUE
PRIVILEGE REQUIRED FOR DATA MINING SCHEMA:
Let us say,If you want to use Data Mining feature to a specific user,than we have to grant CREATE MINING MODEL privilege to that user.
SQL> conn dmuser
Enter password:
Connected.
SQL> select *from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
CREATE MINING MODEL
11 rows selected.
Happy Oracle DBA tasks...
Best regards,
Rafi.
If you compare Oracle 11g and previous versions there are lot of changes in terms of installing Data Mining component,
For installing check the MY ORACLE SUPPORT note :
How To Manually Install Data Mining In Oracle 11g? [ID 818314.1].
Information about data mining:
The brief information about Oracle Data mining can be obtained from the below link:
http://docs.oracle.com/html/B14339_01/1intro.htm
In short,If you do the Oracle installation with Enterprise Edition installation type ,it selects Oracle Data Mining option by default.In Oracle Database 11g,the Data Mining metadata is created with 'SYS' metadata when you select the Create Database option.
For verifying the installation of Oracle Data Mining:
Check if the parameter DATA MINING is set to TRUE,if it is than Oracle Data Mining component is already installed in the Database.The V$OPTION is very useful for checking the various functionality exist in your Database or not as seen from the below results:
SQL>SET LINESIZE 1000
SQL>SELECT *FROM V$OPTION;
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Partitioning TRUE
Objects TRUE
Real Application Clusters FALSE
Advanced replication TRUE
Bit-mapped indexes TRUE
Connection multiplexing TRUE
Connection pooling TRUE
Database queuing TRUE
Incremental backup and recovery TRUE
Instead-of triggers TRUE
Parallel backup and recovery TRUE
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Parallel execution TRUE
Parallel load TRUE
Point-in-time tablespace recovery TRUE
Fine-grained access control TRUE
Proxy authentication/authorization TRUE
Change Data Capture TRUE
Plan Stability TRUE
Online Index Build TRUE
Coalesce Index TRUE
Managed Standby TRUE
Materialized view rewrite TRUE
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Materialized view warehouse refresh TRUE
Database resource manager TRUE
Spatial TRUE
Automatic Storage Management FALSE
Export transportable tablespaces TRUE
Transparent Application Failover TRUE
Fast-Start Fault Recovery TRUE
Sample Scan TRUE
Duplexed backups TRUE
Java TRUE
OLAP Window Functions TRUE
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Block Media Recovery TRUE
Fine-grained Auditing TRUE
Application Role TRUE
Enterprise User Security TRUE
Oracle Data Guard TRUE
Oracle Label Security FALSE
OLAP TRUE
Basic Compression TRUE
Join index TRUE
Trial Recovery TRUE
Data Mining TRUE
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Online Redefinition TRUE
Streams Capture TRUE
File Mapping TRUE
Block Change Tracking TRUE
Flashback Table TRUE
Flashback Database TRUE
Transparent Data Encryption TRUE
Backup Encryption TRUE
Unused Block Compression TRUE
Oracle Database Vault FALSE
Result Cache TRUE
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
SQL Plan Management TRUE
SecureFiles Encryption TRUE
Real Application Testing TRUE
Flashback Data Archive TRUE
DICOM TRUE
Active Data Guard TRUE
Server Flash Cache TRUE
Advanced Compression TRUE
XStream TRUE
Deferred Segment Creation TRUE
PRIVILEGE REQUIRED FOR DATA MINING SCHEMA:
Let us say,If you want to use Data Mining feature to a specific user,than we have to grant CREATE MINING MODEL privilege to that user.
SQL> conn dmuser
Enter password:
Connected.
SQL> select *from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
CREATE MINING MODEL
11 rows selected.
Happy Oracle DBA tasks...
Best regards,
Rafi.
Wednesday, December 21, 2011
Step by steps Oracle 10g Application Server installation steps
Hi,
Below are the steps for Deinstallation,Installation of Oracle 10g Application Server:
Application server Deinstallation process:
Go to
cd D:\Apps_soft\application_server\Disk1
Launch setup.exe
Click Deinstall products
As seen in above screenshot,click all the products we need to deinstall
And then Click Remove
=>Application server 10g Database
=>Application server Infrastructure
=>Application server Middle Tier
Click Yes and than click OK each time it prompts ,which will all the products of the Oracle Application Server and hence Application server will be deinstall successfully.
Installing Oracle 10g Application Server:
Oracle Application Server (10g Release 2) is installed as follows:
Operating System:Windows XP(32-bit)
Version: 10.1.2.0.2
Size on disk required:20 GB
Step 1:
Browse to :D:\Apps_soft\application_server\Disk1
Step 2:
Specify the name of the Application server and Home location where we will be keeping Infra and Middle Tier home components
Step 3:
Gives 3 options:
1)Installation of Middle Tier
2)Installation of Infra Tier
3)Installation of Developer kits 10g
Select option 2 for Installation of Infrastructure Tier.
<span style="font-weight:bold;">Step 4:
Select option 1:It will install Identity Management and Metadata Repository
Step 5:
We will get message as this software is not certified ignored it and proceed by clicking ok and Check the Administrator privileges which will confirm you have administrative rights to install this software.
Click next
Click next
Click Install
Exit and Come out
II)Middle tier Installation:
Select option 1 For installation of Application Server Middle Tier
Click Next,
Select option 3 For installation of BI and Forms.
Click OK and proceed.
Click Next,
Go to D:\Apps_server_10g_home\install.
And open portlist.ini file to see the port number for Oracle Internet Directory(OID).
Enter your machine name and port for OID. Usually 389 for windows as in our case.
Specify: Instance name:Middle Tier instance and ias_admin password
Click Install
Exit and Come out:
Verification:
U:\>cd D:\Apps_server_10g_home\opmn\bin
U:\>d:
D:\Apps_server_10g_home\opmn\bin>opmnctl status
Processes in Instance: APPS_INFRA_INSTANCE.node1.abc.comp.com
-------------------+--------------------+---------+---------
ias-component | process-type | pid | status
-------------------+--------------------+---------+---------
DSA | DSA | N/A | Down
LogLoader | logloaderd | N/A | Down
dcm-daemon | dcm-daemon | 3740 | Alive
OC4J | OC4J_SECURITY | 4324 | Alive
HTTP_Server | HTTP_Server | 4184 | Alive
OID | OID | 1992 | Alive
D:\Apps_server_10g_home\opmn\bin>cd D:\Apps_server_10g_home\MidTier\opmn\b
D:\Apps_server_10g_home\MidTier\opmn\bin>opmmctl status
'opmmctl' is not recognized as an internal or external command,
operable program or batch file.
D:\Apps_server_10g_home\MidTier\opmn\bin>opmnctl startall
opmnctl: starting opmn and all managed processes...
D:\Apps_server_10g_home\MidTier\opmn\bin>opmnctl status
Processes in Instance: Middle_Tier_instance_node1.abc.comp.com
-------------------+--------------------+---------+---------
ias-component | process-type | pid | status
-------------------+--------------------+---------+---------
DSA | DSA | N/A | Down
LogLoader | logloaderd | N/A | Down
dcm-daemon | dcm-daemon | 7272 | Alive
OC4J | home | 6572 | Alive
OC4J | OC4J_BI_Forms | 7920 | Stop
WebCache | WebCache | 6384 | Alive
WebCache | WebCacheAdmin | 1996 | Alive
HTTP_Server | HTTP_Server | 6360 | Alive
Happy Oracle Apps learning...
Best regards,
Rafi.
Wednesday, December 14, 2011
Completed Oracle SOA Suite 11g Certification
Hi,
I added one more credential to my profile portfolio,this time it is my this year favourite concept Oracle SOA 11g.I've completed Oracle SOA suite 11g foundation practitioner certificate last Saturday.Now,I'm Oracle SOA certified expert.Working on the competency servers and giving tech talk on Oracle SOA in my organization were key factors which helped me achieving this credentials.
In addition to this I installed and configured Oracle SOA suite 11g(version 11.1.0.5.0) in my personal laptop also which is having Windows 7 Operating system helped me a lot.
I passed my Oracle SOA Foundation Practitioner Certification by following below tips:
Practise needed:
Practise On Developing and Deploying composite application by starting with basic Hello BPEL task can be very much useful,We need to be aware of each and every service components like their functioning and usage.Work with Adapters and Integration practises will also comes handy.For getting Started with Oracle SOA below link helped lot:
http://docs.oracle.com/cd/E21764_01/integration.1111/e10223/toc.htm
Once this is done you can develop and deploy various composite application for your practise which will be advantageous.
Books you can follow:
Oracle SOA Suite 11g Development guide:http://docs.oracle.com/cd/E21764_01/integration.1111/e10224/toc.htm
Oracle SOA Suite 11g Handbook written by Lucas Jellema.
Links you can follow:
http://docs.oracle.com/cd/E21764_01/soa.htm
Blog from Ahmed :http://blog.ipnweb.com/
Blog from Abhishek:http://abhishek-soablog.blogspot.com/
Oracle SOA Suite Forum:https://forums.oracle.com/forums/forum.jspa?forumID=320
Details about Oracle SOA Foundation Practitioner certification:
Check the below link for Oracle SOA Suite 11g foundation practitioner Certificate syllabus,training and Exam Details:
http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_org_id=1080544&lang=US&p_exam_id=1Z0_451
All the best for Oracle SOA 11g exam...
Happy Oracle SOA learning...
Best regards,
Rafi.
Monday, December 12, 2011
My Favourite Book for Oracle SOA suite 11g
Hi,
This year I devoted myself in learning and practicing Oracle SOA suite 11g.I started learning the concepts of Oracle SOA 11g and various Components involved.
For initial learning of mine,I focused on the below link:
http://docs.oracle.com/cd/E21764_01/integration.1111/e10223/toc.htm
http://rafioracledba.blogspot.com/2011/11/oracle-soa-essential-for-soa.html
After clearing my concepts I went with installing and Configuring Oracle Suite 11g on Windows and Linux Operating systems which led the foundation of my SOA learning.
For installation and Configuration refer my below links:
http://rafioracledba.blogspot.com/2011/08/step-by-step-oracle-soa-suite-11g.html
But,One book that inspired me a lot is Oracle SOA Suite 11g Handbook written by Lucas Jellema.
Brief about Author: Lucas Jellema is an Oracle ACE Director for Oracle Fusion Middleware.He is the SOA specialist based in Nieuwegein,The Netherlands.He is an author at the AMIS Technology Blog for Oracle Technology Network,and for international magazines.
Brief about Book:
This books covers various the following topics which will help you work in Oracle SOA suite 11g:
1)Introduction to SOA with an Example of how it is adopted.
2)Developing Composite Applications which covers the Components of Oracle SOA Suite 11g and how this components work together by using SCA
(Service Component Architecture which is new from Oracle SOA 11g).
3)How we can Administer,Secure and Govern Oracle SOA components.
4)Use of CEP(Complex Event Processing) and BAM (Business Activity Monitoring)
5)How we can migrate from Oracle SOA Suite 10g to 11g.
What else,A SOA learner and expert can expect,really love to read this book several times to understand more better and Work in Oracle SOA suite 11g.All the best SOA learners.
Happy Oracle SOA learning...
Best regards,
Rafi.
Happy Oracle SOA
This year I devoted myself in learning and practicing Oracle SOA suite 11g.I started learning the concepts of Oracle SOA 11g and various Components involved.
For initial learning of mine,I focused on the below link:
http://docs.oracle.com/cd/E21764_01/integration.1111/e10223/toc.htm
http://rafioracledba.blogspot.com/2011/11/oracle-soa-essential-for-soa.html
After clearing my concepts I went with installing and Configuring Oracle Suite 11g on Windows and Linux Operating systems which led the foundation of my SOA learning.
For installation and Configuration refer my below links:
http://rafioracledba.blogspot.com/2011/08/step-by-step-oracle-soa-suite-11g.html
But,One book that inspired me a lot is Oracle SOA Suite 11g Handbook written by Lucas Jellema.
Brief about Author: Lucas Jellema is an Oracle ACE Director for Oracle Fusion Middleware.He is the SOA specialist based in Nieuwegein,The Netherlands.He is an author at the AMIS Technology Blog for Oracle Technology Network,and for international magazines.
Brief about Book:
This books covers various the following topics which will help you work in Oracle SOA suite 11g:
1)Introduction to SOA with an Example of how it is adopted.
2)Developing Composite Applications which covers the Components of Oracle SOA Suite 11g and how this components work together by using SCA
(Service Component Architecture which is new from Oracle SOA 11g).
3)How we can Administer,Secure and Govern Oracle SOA components.
4)Use of CEP(Complex Event Processing) and BAM (Business Activity Monitoring)
5)How we can migrate from Oracle SOA Suite 10g to 11g.
What else,A SOA learner and expert can expect,really love to read this book several times to understand more better and Work in Oracle SOA suite 11g.All the best SOA learners.
Happy Oracle SOA learning...
Best regards,
Rafi.
Happy Oracle SOA
Tuesday, November 29, 2011
Oracle SOA essential for SOA Administrators and Developers
Hi,
Oracle SOA 11g essential which can ge useful for SOA Administrators and Developers
can be listed as given below:
Understanding web services:
Useful links are:
http://www.w3schools.com/wsdl/wsdl_intro.asp
http://www.w3schools.com/wsdl/wsdl_uddi.asp
http://www.oasis-open.org/committees/download.php/3343/oasis-200304-wsrp-specification-1.0.pdf
http://www.oracle.com/technetwork/middleware/bpa/overview/index.html
http://www.w3schools.com/xsl/
Understand ESB:
An "Enterprise Service Bus" (ESB) is a system to which all services are connected.
Through the enterprise service bus all connected services can also be accessed.
Here is an illustration of an enterprise service bus which acts as central "bridge" or "gateway" to all applications exposing services
underneath it.
Understand BPEL:
BPEL (Business Process Execution Language) for Web services is an XML-based language designed to enable task-sharing
for a distributed computing or grid computing environment which can be use evenly across multiple organizations and using a combination of Web service.
BPEL is the most feature rich component in SOA suite.
BPEL is use in large extent to orchestrate the web services.
Understanding synchronous and Asynchronous BPEL process:
Link which will be useful.
http://suryaveer-chauhan.blogspot.com/2009/06/asynchronous-process-vs.html
Understand BAM:
Oracle Business Activity Monitoring (Oracle BAM) gives business executives the ability to monitor their business services and processes in the enterprise, to correlate KPIs down to the actual business process themselves, and most important,
to change business processes quickly or to take corrective action if the business environment changes.
Note: Oracle BAM is a component of both SOA Suite and BPM Suite.
when we install Oracle SOA suite 11g,we can install BAM component if we have enough memory in our Operating system(Usully required 8 GB of RAM
for good functioning) as I implemented in RHEL 5.3 (64-bit) machine.It is good practise to involve the latest version of Oracle SOA suite
always.I've installed 11.1.1.5.0 on Windows and Unix-based machines.
Understand OWSM:
Oracle Web Services Manager(OWSM) offers a comprehensive and easy-to-use solution for policy management and security of service infrastructure. It provides visibility and control of the policies through a centralized administration
interface offered by Oracle Enterprise Manager.
We will get OEM console link when we configure the weblogic domain.When we see the Home page after login after login with Oracle Fusion Middleware
11g OEM console.The admin console is also useful for lot of SOA admin tasks as well as OEM console.
Understanding ODI:
*ODI stands for Oracle Data Integrator.This application is useful in processing large amount of data.
*It works on the principle of Estraction,Loading and Transformation(ELT) unlike ETL tools where we have extract transform and load.
*In short ODI can be called as 'ELT tool of SOA'
Understanding OBR:
*OBR stands for Oracle Business Rules is a tool for writing the Business rules seperately from the code.
*This becomes very vital when you want to understand the Business rules and code of development separately.
*We can see Business rules with the help of User Interface provided by this tool.
Understanding SR:
*SR stands for Service Registry.It is used to keep track of all the services that are present in my portfolio(Which consists of past,present and future services).
Oracle JDeveloper:
*As the name says it stands for Oracle Java Developer where you integrate Oracle,java,web,web services and XML services.
*Oracle JDeveloper integrates development features for Java, SOA, Web 2.0,Database, XML and Web services into a single development tool.
*Oracle JDeveloper integrates the full set of features needed for developing composite applications with specific features that address the technologies like Database,java,web,web services and XML.
*Oracle JDeveloper integrates the full set of features needed for developing composite applications with specific features that address the technologies like Database,java,web,web services and XML
*Oracle JDeveloper is the development environment for Oracle SOA Suite and the Oracle WebCenter Suite.
Understanding Oracle WebLogic Server:
*An enterprise-ready Java application server.
*Supports the deployment of mission-critical applications in a robust,secure,highly available and scalable environment.
*Oracle WebLogic Server is an ideal foundation for building
applications based on service-oriented architecture (SOA).
Understanding Domain,Admin server and Managed servers in Weblogic Server:
Domain:
*Logically related group of Java components.
Administration Server:
*Special WebLogic Server instance which is the central point from which you
configure and manage all resources in the domain.
Managed Servers:
*Additional WebLogic Server instances where we deploy Java components,
such as Web applications, EJBs, and Web services.
*For production environments that require increased application performance,throughput, or high availability, you can configure
two or more Managed Servers to operate as a cluster.
*Oracle instance contains one or more system components
Understanding Oracle SOA Suite:
*Complete set of service infrastructure components for designing,
deploying, and managing composite applications.
*Enables services to be created, managed, and orchestrated into
composite applications and business processes.
*Composites enable you to easily assemble multiple technology
components into one SOA composite application.
Understanding SOA Development Challenge:
*There are various standards such as BPEL, WSDL, JCA,
Human workflow, Rules etc in the SOA solution
*As number of technologies increase, so does number of tools a
and skills required to build, deploy and manage them.
*Because each tool throws its own data, it can be
frustrating trying to make all of them work together.
Understanding What is needed?:
*There should be a “standardized way” by which disparate SOA components like BPEL, OSB, Adapters, Human workflow, Rules etc can work together.
*They should be managed from a single console.
*Logically related components should be deployable together,if possible.
*This “standardized way” is a framework called SCA.
Understanding SCA:
*Stands for Service Component Architecture
*SCA proposes the concept of “Composite”
*SCA is actually the platform on which the components come together,
and create a composite.It is technology and vendor neutral.
*SCA Programming Model provides the following
->Creating service components written with a wide range of technologies,
including programming languages such as Java, BPEL, C++, and declarative languages such as XSLT.
->The use of specific programming languages and technologies (including web services)is not required with SCA.
->Assembling the service components into a SOA composite application.
In the SCA environment,service components are the building blocks of applications.
Note: Oracle SOA Suite 11g leverages SCA
Understanding How Oracle SOA Suite 11g leverages SCA:
SCA simplified the entire lifecycle:
1)Development
->JDeveloper can be used to develop all components.
2)Deployment
->All components are grouped together in a composite and
during compilation,we get a single file called SAR.
->SAR can be versioned as one, deployed as one and managed as one
3)Monitoring
->EM allows to trace the entire flow,giving ability to drill down into
components of a composite.
Understanding Service Components:
*Service components are the building blocks that you use to
construct a SOA composite application.
The following service components are available:
*BPEL- provide process orchestration.
*Human Task =>provide workflow modeling.
*Business Rules =>enable designing a business decision based on rules.
*Mediators =>route events (messages) between different components.
*Spring =>enables you to integrate Java interfaces into SOA composite applications
->There is a corresponding service engine of the same name for each service component.
->All service engines can interact in a single composite.
Understanding Binding Components:
*Binding components establish a connection between a SOA composite and the external world.
There are two types of binding components
The following service components are available:
1)Services provide the outside world with an entry point to the SOA composite application.
2)References enable messages to be sent from the SOA composite application to
external services in the outside world
*There is a corresponding service engine of the same name for each service component.
*All service engines can interact in a single composite.
Examples of Binding Components are:
1)Web service SOAP over Http:Use for connecting to standards-based services using SOAP
2)JCA Adapters:Use for integrationg services and references with technologies(like databases,file systems ftp servers)
3)B2B Binding component:Use for browsing B2B metadata in the MDS repository and selecting document defintions
4)ADF -BC Service:Use forconnecting Oracle Application Development Framework(ADF) applications using SDO(Service Data Object) with SOA platform.
5)Oracle Applications:Use for integrating Oracle Application Adapter with Oracle Applications.
Like these se have lot of Binding components.We have to explore each and every component in SOA Suite 11g,Design various composite applications,
Deploy them on weblogic by using Oracle JDeveloper or Weblogic server.
SR(Service Registry) can be useful to keep track of all the web services and orchestrating this web services using BPEL.
Happy SOA learning...
Best regards,
Rafi.
Oracle SOA 11g essential which can ge useful for SOA Administrators and Developers
can be listed as given below:
Understanding web services:
Useful links are:
http://www.w3schools.com/wsdl/wsdl_intro.asp
http://www.w3schools.com/wsdl/wsdl_uddi.asp
http://www.oasis-open.org/committees/download.php/3343/oasis-200304-wsrp-specification-1.0.pdf
http://www.oracle.com/technetwork/middleware/bpa/overview/index.html
http://www.w3schools.com/xsl/
Understand ESB:
An "Enterprise Service Bus" (ESB) is a system to which all services are connected.
Through the enterprise service bus all connected services can also be accessed.
Here is an illustration of an enterprise service bus which acts as central "bridge" or "gateway" to all applications exposing services
underneath it.
Understand BPEL:
BPEL (Business Process Execution Language) for Web services is an XML-based language designed to enable task-sharing
for a distributed computing or grid computing environment which can be use evenly across multiple organizations and using a combination of Web service.
BPEL is the most feature rich component in SOA suite.
BPEL is use in large extent to orchestrate the web services.
Understanding synchronous and Asynchronous BPEL process:
Link which will be useful.
http://suryaveer-chauhan.blogspot.com/2009/06/asynchronous-process-vs.html
Understand BAM:
Oracle Business Activity Monitoring (Oracle BAM) gives business executives the ability to monitor their business services and processes in the enterprise, to correlate KPIs down to the actual business process themselves, and most important,
to change business processes quickly or to take corrective action if the business environment changes.
Note: Oracle BAM is a component of both SOA Suite and BPM Suite.
when we install Oracle SOA suite 11g,we can install BAM component if we have enough memory in our Operating system(Usully required 8 GB of RAM
for good functioning) as I implemented in RHEL 5.3 (64-bit) machine.It is good practise to involve the latest version of Oracle SOA suite
always.I've installed 11.1.1.5.0 on Windows and Unix-based machines.
Understand OWSM:
Oracle Web Services Manager(OWSM) offers a comprehensive and easy-to-use solution for policy management and security of service infrastructure. It provides visibility and control of the policies through a centralized administration
interface offered by Oracle Enterprise Manager.
We will get OEM console link when we configure the weblogic domain.When we see the Home page after login after login with Oracle Fusion Middleware
11g OEM console.The admin console is also useful for lot of SOA admin tasks as well as OEM console.
Understanding ODI:
*ODI stands for Oracle Data Integrator.This application is useful in processing large amount of data.
*It works on the principle of Estraction,Loading and Transformation(ELT) unlike ETL tools where we have extract transform and load.
*In short ODI can be called as 'ELT tool of SOA'
Understanding OBR:
*OBR stands for Oracle Business Rules is a tool for writing the Business rules seperately from the code.
*This becomes very vital when you want to understand the Business rules and code of development separately.
*We can see Business rules with the help of User Interface provided by this tool.
Understanding SR:
*SR stands for Service Registry.It is used to keep track of all the services that are present in my portfolio(Which consists of past,present and future services).
Oracle JDeveloper:
*As the name says it stands for Oracle Java Developer where you integrate Oracle,java,web,web services and XML services.
*Oracle JDeveloper integrates development features for Java, SOA, Web 2.0,Database, XML and Web services into a single development tool.
*Oracle JDeveloper integrates the full set of features needed for developing composite applications with specific features that address the technologies like Database,java,web,web services and XML.
*Oracle JDeveloper integrates the full set of features needed for developing composite applications with specific features that address the technologies like Database,java,web,web services and XML
*Oracle JDeveloper is the development environment for Oracle SOA Suite and the Oracle WebCenter Suite.
Understanding Oracle WebLogic Server:
*An enterprise-ready Java application server.
*Supports the deployment of mission-critical applications in a robust,secure,highly available and scalable environment.
*Oracle WebLogic Server is an ideal foundation for building
applications based on service-oriented architecture (SOA).
Understanding Domain,Admin server and Managed servers in Weblogic Server:
Domain:
*Logically related group of Java components.
Administration Server:
*Special WebLogic Server instance which is the central point from which you
configure and manage all resources in the domain.
Managed Servers:
*Additional WebLogic Server instances where we deploy Java components,
such as Web applications, EJBs, and Web services.
*For production environments that require increased application performance,throughput, or high availability, you can configure
two or more Managed Servers to operate as a cluster.
*Oracle instance contains one or more system components
Understanding Oracle SOA Suite:
*Complete set of service infrastructure components for designing,
deploying, and managing composite applications.
*Enables services to be created, managed, and orchestrated into
composite applications and business processes.
*Composites enable you to easily assemble multiple technology
components into one SOA composite application.
Understanding SOA Development Challenge:
*There are various standards such as BPEL, WSDL, JCA,
Human workflow, Rules etc in the SOA solution
*As number of technologies increase, so does number of tools a
and skills required to build, deploy and manage them.
*Because each tool throws its own data, it can be
frustrating trying to make all of them work together.
Understanding What is needed?:
*There should be a “standardized way” by which disparate SOA components like BPEL, OSB, Adapters, Human workflow, Rules etc can work together.
*They should be managed from a single console.
*Logically related components should be deployable together,if possible.
*This “standardized way” is a framework called SCA.
Understanding SCA:
*Stands for Service Component Architecture
*SCA proposes the concept of “Composite”
*SCA is actually the platform on which the components come together,
and create a composite.It is technology and vendor neutral.
*SCA Programming Model provides the following
->Creating service components written with a wide range of technologies,
including programming languages such as Java, BPEL, C++, and declarative languages such as XSLT.
->The use of specific programming languages and technologies (including web services)is not required with SCA.
->Assembling the service components into a SOA composite application.
In the SCA environment,service components are the building blocks of applications.
Note: Oracle SOA Suite 11g leverages SCA
Understanding How Oracle SOA Suite 11g leverages SCA:
SCA simplified the entire lifecycle:
1)Development
->JDeveloper can be used to develop all components.
2)Deployment
->All components are grouped together in a composite and
during compilation,we get a single file called SAR.
->SAR can be versioned as one, deployed as one and managed as one
3)Monitoring
->EM allows to trace the entire flow,giving ability to drill down into
components of a composite.
Understanding Service Components:
*Service components are the building blocks that you use to
construct a SOA composite application.
The following service components are available:
*BPEL- provide process orchestration.
*Human Task =>provide workflow modeling.
*Business Rules =>enable designing a business decision based on rules.
*Mediators =>route events (messages) between different components.
*Spring =>enables you to integrate Java interfaces into SOA composite applications
->There is a corresponding service engine of the same name for each service component.
->All service engines can interact in a single composite.
Understanding Binding Components:
*Binding components establish a connection between a SOA composite and the external world.
There are two types of binding components
The following service components are available:
1)Services provide the outside world with an entry point to the SOA composite application.
2)References enable messages to be sent from the SOA composite application to
external services in the outside world
*There is a corresponding service engine of the same name for each service component.
*All service engines can interact in a single composite.
Examples of Binding Components are:
1)Web service SOAP over Http:Use for connecting to standards-based services using SOAP
2)JCA Adapters:Use for integrationg services and references with technologies(like databases,file systems ftp servers)
3)B2B Binding component:Use for browsing B2B metadata in the MDS repository and selecting document defintions
4)ADF -BC Service:Use forconnecting Oracle Application Development Framework(ADF) applications using SDO(Service Data Object) with SOA platform.
5)Oracle Applications:Use for integrating Oracle Application Adapter with Oracle Applications.
Like these se have lot of Binding components.We have to explore each and every component in SOA Suite 11g,Design various composite applications,
Deploy them on weblogic by using Oracle JDeveloper or Weblogic server.
SR(Service Registry) can be useful to keep track of all the web services and orchestrating this web services using BPEL.
Happy SOA learning...
Best regards,
Rafi.
Friday, November 25, 2011
How to tune oracle Database?
Collecting and analyzing the AWR reports for Oracle Database:
Hi,
Tuning the Database is very much essential not only for better execution of SQL statement but also for applications running well using that Database.AWR(Automatic workload repository) report gives us clear picture in Deciding and tuning Database as well as SQL statement.As Enterprise Manager we can see it easily but Enterprise manager needs license(additional cost) so some companies does not want to use Oracle Enterprise Manager.Oracle gives us flexible option in which we can generated awr report in html and plain format.
It is better to generate in html format for seeing it clearly by using any web browser.Since performance tuning is a deep ocean I will be updating this thread based on my problems faced and the method which work fine for tuning purpose.
Collecting awr report from SQL prompt:
Login to the Database as 'sys' user(SYSDBA) and make sure Database is up & running and Oracle Environment for the particular Database
is set.we can gather the awr report using 'awrrpt.sql'.
Note:If we want the awr report in Oracle RAC environment than we have to use 'awrgrpt.sql' script,as there you have to gather the report for multiple instances running on various nodes.
SQL> select name from v$database;
NAME
---------
TESTDB
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> @?/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
3628069655 TESTDB 1 TESTDB
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 3628069655 1 TESTDB TESTDB TESTNODE1.comp.com
Using 3628069655 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
TESTDB TESTDB 5590 24 Nov 2011 00:30 1
5591 24 Nov 2011 01:30 1
5592 24 Nov 2011 02:30 1
5593 24 Nov 2011 03:30 1
5594 24 Nov 2011 04:30 1
5595 24 Nov 2011 05:30 1
5596 24 Nov 2011 06:30 1
5597 24 Nov 2011 07:30 1
5598 24 Nov 2011 08:30 1
5599 24 Nov 2011 09:30 1
5600 24 Nov 2011 10:30 1
5601 24 Nov 2011 11:30 1
5602 24 Nov 2011 12:30 1
5603 24 Nov 2011 13:30 1
5604 24 Nov 2011 14:30 1
5605 24 Nov 2011 15:30 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 5604
Begin Snapshot Id specified: 5604
Enter value for end_snap: 5605
End Snapshot Id specified: 5605
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_5604_5605.html. To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name: awrrpt_NOV24_2011_2_30_3_30_PM.html
SQL> exit
We will see the html format of the awr report in the current operating system path.
[oracle@TESTNODE1 ~]$ ls -altr awr*
-rw-r--r-- 1 oracle dba 458371 Nov 24 14:02 awrrpt_1_5590_5603.html
-rw-r--r-- 1 oracle dba 390564 Nov 24 16:31 awrrpt_NOV24_2011_2_30_3_30_PM.html
We can copy this html file using copying tool(winscp or ftp) to our machine and review using web browsers(mozilla or IE supported
versions).
Analzing the awr report and suggesting possible recommendations:
Once we obtain the awr report our main motive is to analyze the awr report and come up with possible recommendations.Depending
on the size of our Production Database we can come up with possible recommendations.This recommendation should be first implemented in
test environment and after successful results should be adopted in production environments.
1) Redo logs:
We need to make sure our redo logs are large enough.Check the number of log switches, one every twenty minutes is ideal, more than this is too high and you should make them larger to reduce the number of switches.
We can find the log switches in the Instance Activity Stats part of the awr report.
Example:
Instance Activity Stats - Thread Activity
* Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic Total per Hour
log switches (derived) 2 2.00
We can see in this system there are 2 log swtiches per hourly basis,which is good.So this tells us the redo logs are large enough.
2)Parsing:
Check the hard parsing amount.It should be zero.If it is not, this indicates that our SGA is probably too small,increase the size of SGA and test again.
Hard parsing is caused by use of literals in SQL (as opposed to bind variables).If the queries in question are our own,we should change them to use bind variables.
We can find this information on the first page.
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
...
Parses: 33.9 7.2
Hard parses: 0.5 0.1
...
We can see in this system the hard parses is almost zero, which is good.
Now coming to the SGA we can focus on the below considerations:
3)Buffer hit and Library hit percentages:
Check the buffer hit and library hit percentages. We want them to be 100%, if not we should increase the size of SGA.This is also on the first page:
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.82 Redo NoWait %: 100.00
Buffer Hit %: 99.52 In-memory Sort %: 100.00
Library Hit %: 98.63 Soft Parse %: 98.60
Execute to Parse %: 50.96 Latch Hit %: 98.16
Parse CPU to Parse Elapsd %: 66.67 % Non-Parse CPU: 97.75
In this case they are also good.
4)Top 5 Timed Foreground Events:
Check the average wait times.Anything over 5ms indicates a problem.If we see database CPU events in the Top 5, this indicates that SGA is too small.We may also be missing indexes.Check the optimizer statistics.
Here are the Top 5 from my environment:
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU 15 59.9
log file sync 1,592 8 5 32.3 Commit
sort segment request 1 1 1001 4.0 Configurat
db file sequential read 216 1 4 3.6 User I/O
db file scattered read 64 0 6 1.5 User I/O
we can see here that the major issue is DB CPU, which generally indicates SGA is too small. However, in this case it is high because this report was run on a VM with the database and BPM sharing the CPU and disk.
Database file sequential/scattered read
These indicate time spent doing table scans and index scans (respectively).If these are high (over 5ms),We should consider moving your data files to reduce disk I/O contention, or move them to faster disks.
5)Enqueue high watermark:
This indicates hardware contention that occurs when there are multiple users inserting into LOB segments at once while the database is trying to reclaim unused space.
We should consider enabling secure files to improve LOB performance (SECURE_FILES=ALWAYS).
We cannot see these in my example report, because this was not a problem in my environment, so it did not make it into the Top 5.If it did, you would see an event called:enq: HW - contention
Other things to be aware of…
We will also check our database configuration.
6)MEMORY_TARGET:
Do not use this setting.We should have our DBA tune the memory manually instead.This will result in a better tuned database.We should start with 60% of physical memory allocated to SGA and 20% to PGA.
7)AUDIT_TRAIL:
Usually we do not use this setting much for tuning.But auditing on Database level
can be overhead to the Database.
Happy Tuning Oracle Databases ...
Best regards,
Rafi.
Hi,
Tuning the Database is very much essential not only for better execution of SQL statement but also for applications running well using that Database.AWR(Automatic workload repository) report gives us clear picture in Deciding and tuning Database as well as SQL statement.As Enterprise Manager we can see it easily but Enterprise manager needs license(additional cost) so some companies does not want to use Oracle Enterprise Manager.Oracle gives us flexible option in which we can generated awr report in html and plain format.
It is better to generate in html format for seeing it clearly by using any web browser.Since performance tuning is a deep ocean I will be updating this thread based on my problems faced and the method which work fine for tuning purpose.
Collecting awr report from SQL prompt:
Login to the Database as 'sys' user(SYSDBA) and make sure Database is up & running and Oracle Environment for the particular Database
is set.we can gather the awr report using 'awrrpt.sql'.
Note:If we want the awr report in Oracle RAC environment than we have to use 'awrgrpt.sql' script,as there you have to gather the report for multiple instances running on various nodes.
SQL> select name from v$database;
NAME
---------
TESTDB
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> @?/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
3628069655 TESTDB 1 TESTDB
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 3628069655 1 TESTDB TESTDB TESTNODE1.comp.com
Using 3628069655 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
TESTDB TESTDB 5590 24 Nov 2011 00:30 1
5591 24 Nov 2011 01:30 1
5592 24 Nov 2011 02:30 1
5593 24 Nov 2011 03:30 1
5594 24 Nov 2011 04:30 1
5595 24 Nov 2011 05:30 1
5596 24 Nov 2011 06:30 1
5597 24 Nov 2011 07:30 1
5598 24 Nov 2011 08:30 1
5599 24 Nov 2011 09:30 1
5600 24 Nov 2011 10:30 1
5601 24 Nov 2011 11:30 1
5602 24 Nov 2011 12:30 1
5603 24 Nov 2011 13:30 1
5604 24 Nov 2011 14:30 1
5605 24 Nov 2011 15:30 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 5604
Begin Snapshot Id specified: 5604
Enter value for end_snap: 5605
End Snapshot Id specified: 5605
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_5604_5605.html. To use this name,
press
Enter value for report_name: awrrpt_NOV24_2011_2_30_3_30_PM.html
SQL> exit
We will see the html format of the awr report in the current operating system path.
[oracle@TESTNODE1 ~]$ ls -altr awr*
-rw-r--r-- 1 oracle dba 458371 Nov 24 14:02 awrrpt_1_5590_5603.html
-rw-r--r-- 1 oracle dba 390564 Nov 24 16:31 awrrpt_NOV24_2011_2_30_3_30_PM.html
We can copy this html file using copying tool(winscp or ftp) to our machine and review using web browsers(mozilla or IE supported
versions).
Analzing the awr report and suggesting possible recommendations:
Once we obtain the awr report our main motive is to analyze the awr report and come up with possible recommendations.Depending
on the size of our Production Database we can come up with possible recommendations.This recommendation should be first implemented in
test environment and after successful results should be adopted in production environments.
1) Redo logs:
We need to make sure our redo logs are large enough.Check the number of log switches, one every twenty minutes is ideal, more than this is too high and you should make them larger to reduce the number of switches.
We can find the log switches in the Instance Activity Stats part of the awr report.
Example:
Instance Activity Stats - Thread Activity
* Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic Total per Hour
log switches (derived) 2 2.00
We can see in this system there are 2 log swtiches per hourly basis,which is good.So this tells us the redo logs are large enough.
2)Parsing:
Check the hard parsing amount.It should be zero.If it is not, this indicates that our SGA is probably too small,increase the size of SGA and test again.
Hard parsing is caused by use of literals in SQL (as opposed to bind variables).If the queries in question are our own,we should change them to use bind variables.
We can find this information on the first page.
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
...
Parses: 33.9 7.2
Hard parses: 0.5 0.1
...
We can see in this system the hard parses is almost zero, which is good.
Now coming to the SGA we can focus on the below considerations:
3)Buffer hit and Library hit percentages:
Check the buffer hit and library hit percentages. We want them to be 100%, if not we should increase the size of SGA.This is also on the first page:
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.82 Redo NoWait %: 100.00
Buffer Hit %: 99.52 In-memory Sort %: 100.00
Library Hit %: 98.63 Soft Parse %: 98.60
Execute to Parse %: 50.96 Latch Hit %: 98.16
Parse CPU to Parse Elapsd %: 66.67 % Non-Parse CPU: 97.75
In this case they are also good.
4)Top 5 Timed Foreground Events:
Check the average wait times.Anything over 5ms indicates a problem.If we see database CPU events in the Top 5, this indicates that SGA is too small.We may also be missing indexes.Check the optimizer statistics.
Here are the Top 5 from my environment:
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU 15 59.9
log file sync 1,592 8 5 32.3 Commit
sort segment request 1 1 1001 4.0 Configurat
db file sequential read 216 1 4 3.6 User I/O
db file scattered read 64 0 6 1.5 User I/O
we can see here that the major issue is DB CPU, which generally indicates SGA is too small. However, in this case it is high because this report was run on a VM with the database and BPM sharing the CPU and disk.
Database file sequential/scattered read
These indicate time spent doing table scans and index scans (respectively).If these are high (over 5ms),We should consider moving your data files to reduce disk I/O contention, or move them to faster disks.
5)Enqueue high watermark:
This indicates hardware contention that occurs when there are multiple users inserting into LOB segments at once while the database is trying to reclaim unused space.
We should consider enabling secure files to improve LOB performance (SECURE_FILES=ALWAYS).
We cannot see these in my example report, because this was not a problem in my environment, so it did not make it into the Top 5.If it did, you would see an event called:enq: HW - contention
Other things to be aware of…
We will also check our database configuration.
6)MEMORY_TARGET:
Do not use this setting.We should have our DBA tune the memory manually instead.This will result in a better tuned database.We should start with 60% of physical memory allocated to SGA and 20% to PGA.
7)AUDIT_TRAIL:
Usually we do not use this setting much for tuning.But auditing on Database level
can be overhead to the Database.
Happy Tuning Oracle Databases ...
Best regards,
Rafi.
Subscribe to:
Posts (Atom)