There is multiple Oracle Report and Tools are available in oracle which help during performance issue and troubleshooting
1) Generate and Check ADDM report, implement findings, re-test
2) Gather Diagnostics
3) AWR report covering problem period
4) AWR report covering good period of similar load and duration for comparison
5) AWR Compare Periods report comparing the good and bad periods
6) Collect an ASH report for the same period
7) Collect OSWatcher data
8) Collect Alert log and traces covering the duration of the problem
9) Check the Alert log for the period when the issue occurred
10) Find any trace files referenced in the problem period
Some of Advance Oracle Tool and Test case for performance
1) RDA
2) SQLT/STA
3) AWR report
4) ADDM report
5) OS watcher
6) One case of performance tuning which you worked.
7) Various option of sql tracing & tkprof
8) Various option for explain plan generation
9) Test case of your analysis of SQLT, AWR report, explain plan analysis
10) Various OS commands for identify bottleneck
1) Remote Diagnostics Agent(RDA)
RDA is a utility, a set of shell scripts or a PERL script, that can be downloaded from Oracle Support to collect diagnostics information from an Oracle database and it's environment(RAC, ASM, Exadata).
This utility is focused at collecting information that will aid in program diagnosis when logging a call, Oracle support will often request that we install the RDA utility, run it and upload the output to Oracle Support for analysis.
It’s not only a great tool for troubleshooting but also very helpful for documenting an Oracle environment.
RDA offers lots of reporting options, is relatively unobtrusive and provides easy to read results. You can run it on just about any version of the Database or Oracle Applications or Operating System and it is smart enough to figure out where to go and what to gather.
Once installed and run rda.sh or rda.pl, you have to answer some questions and send it off to gather information about your environment. As result you will get a lot of TXT and HTML files. The simplest way of reviewing the output files is to launch a web browser on the same machine where rda.sh has run and open the file RDA__START.htm located in the RDA_Output directory. If you pull up the RDA__START.htm, you can browse through information about your database, server, Java, applications tier, forms and just about anything else you ever wanted to know. And it’s all nicely formatted in HTML with drill-down links.
[oracle@lab rda]$ ls
admin DISCLAIM.txt mesg RDA README_irda.txt sdboot.pl tools
collect engine modules rda.cmd README_Unix.txt sdci.cmd
Convert hcve output rda.com README_upgrade.txt sdci.pl
da IRDA output.cfg rda.pl README_VMS.txt sdci.sh
dfw irda.pl Pod rda.sh README_Windows.txt temp
[oracle@lab rda]$
[oracle@lab rda]$ sh rda.sh
------------------------------------------------------------------------------
RDA Data Collection Started 11-Jun-2016 00:57:32
------------------------------------------------------------------------------
Processing RDA.BEGIN module ...
Enter the password for "SYSTEM" at "cdb1":
Re-enter it to confirm:
Processing OS.PERF module ...
Processing RDA.CONFIG module ...
Processing SAMPLE.SAMPLE module ...
Processing OS.OS module …
Processing OS.PROF module ...
Processing OS.NET module ...
Processing OS.INST module ...
Processing DB.DBA module ...
Processing OFM.WREQ module ...
This can take time. Be patient ...
Processing OFM.IREQ module ...
Processing RDA.LOAD module ...
Processing RDA.END module ...
------------------------------------------------------------------------------
RDA Data Collection Ended 11-Jun-2016 00:58:26
[oracle@ibmlab rda]$ perl -V
Summary of my perl5 (revision 5 version 14 subversion 1) configuration:
Platform:
osname=linux, osvers=2.6.18-238.0.0.0.1.el5xen, archname=x86_64-linux-thread-multi
uname='linux localhost 2.6.18-238.0.0.0.1.el5xen #1 smp tue jan 4 09:38:01 est 2011 x86_64 x86_64 x86_64 gnulinux '
config_args='-Dprefix=/u01/app/oracle/product/12.1.0.2/db_1/perl -Dcc=icc -Doptimize=-O3 -Dusethreads -Duseithreads -Duserelocatableinc -Uusemymalloc -des -Acppflags=-DPERL_USE_SAFE_PUTENV -Accflags=-mp1 -fPIC -fp_port -Aldflags=-i-static -Alddlflags=-i-static -shared -O3 -L/usr/local/lib'
hint=recommended, useposix=true, d_sigaction=define
useithreads=define, usemultiplicity=define
useperlio=define, d_sfio=undef, uselargefiles=define, usesocks=undef
use64bitint=define, use64bitall=define, uselongdouble=undef
usemymalloc=n, bincompat5005=undef
Compiler:
cc='icc', ccflags ='-D_REENTRANT -D_GNU_SOURCE -mp1 -fPIC -fp_port -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64',
optimize='-O3',
cppflags='-DPERL_USE_SAFE_PUTENV -D_REENTRANT -D_GNU_SOURCE -mp1 -fPIC -fp_port -I/usr/local/include'
ccversion='', gccversion='', gccosandvers=''
intsize=4, longsize=8, ptrsize=8, doublesize=8, byteorder=12345678
d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=16
ivtype='long', ivsize=8, nvtype='double', nvsize=8, Off_t='off_t', lseeksize=8
alignbytes=8, prototype=define
Linker and Libraries:
ld='icc', ldflags =' -i-static -L/usr/local/lib'
libpth=/usr/local/lib /lib /usr/lib /lib64 /usr/lib64 /usr/local/lib64
libs=-lnsl -lgdbm -ldb -ldl -lm -lcrypt -lutil -lpthread -lc
perllibs=-lnsl -ldl -lm -lcrypt -lutil -lpthread -lc
libc=/lib/libc-2.5.so, so=so, useshrplib=false, libperl=libperl.a
gnulibc_version='2.5'
Dynamic Linking:
dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags='-Wl,-E'
cccdlflags=' ', lddlflags=' -i-static -shared -O3 -L/usr/local/lib'
Characteristics of this binary (from libperl):
Compile-time options: MULTIPLICITY PERL_DONT_CREATE_GVSV
PERL_IMPLICIT_CONTEXT PERL_MALLOC_WRAP
PERL_PRESERVE_IVUV USE_64_BIT_ALL USE_64_BIT_INT
USE_ITHREADS USE_LARGE_FILES USE_PERLIO USE_PERL_ATOF
USE_REENTRANT_API
Built under linux
Compiled at Jul 8 2011 15:06:01
@INC:
/home/oracle/app/product/12.1.0.2/db_1/perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi
/home/oracle/app/product/12.1.0.2/db_1/perl/lib/site_perl/5.14.1
/home/oracle/app/product/12.1.0.2/db_1/perl/lib/5.14.1/x86_64-linux-thread-multi
/home/oracle/app/product/12.1.0.2/db_1/perl/lib/5.14.1
.
[oracle@ibmlab rda]$
[oracle@ibmlab rda]$ sh rda.sh -s
------------------------------------------------------------------------------
RDA Data Collection Started 11-Jun-2016 01:04:10
------------------------------------------------------------------------------
Processing RDA.BEGIN module ...
Enter the password for "SYSTEM" at "cdb1":
Re-enter it to confirm:
Processing OS.PERF module ...
Processing RDA.CONFIG module ...
Processing SAMPLE.SAMPLE module ...
Processing OS.OS module ...
Processing OS.PROF module ...
Processing OS.NET module ...
Processing OS.INST module ...
Processing DB.DBA module ...
Processing OFM.WREQ module ...
This can take time. Be patient ...
Processing OFM.IREQ module ...
Processing RDA.LOAD module ...
Processing RDA.END module ...
------------------------------------------------------------------------------
RDA Data Collection Ended 11-Jun-2016 01:05:00
[oracle@lab rda]$ ./rda.sh -h
Usage: rda.pl [-bcdfilnvwxy] [-ABCDEGHIKLMPQRSTV] [-e edt] [-m dir] [-o out]
[-p nam] [-s nam] [-t lvl] arg ...
-A Authentify user through the result set definition file
-B Start background collection
-C Collect diagnostic information
-D Delete specified modules from the collection
-E Explain specified error numbers
-G Convert report files to XML format
-H Halt background collection
-I Regenerate the index
-K Kill background collection
-L List the available modules, profiles, and conversion groups
-M Display the related manual pages
-O Render output specifications from the standard input
-P Package the reports (tar or zip)
-Q Display the related setup questions
-R Generate specified reports
-S Setup specified modules
-T Execute test modules
-V Display component version numbers
-b Do not backup result set definition file before saving
-c Check the RDA installation and exit
-d Set debug mode
-e edt Specify a list of alternate setting definitions (var=val,...)
-f Set force mode
-g grp Specify the XML conversion group
-h Display the command usage and exit
-i Read settings from the standard input
-l Use a lock file to prevent concurrent usage of a definition file
-m dir Specify the module directory ('collect' by default)
-n Start a new data collection
-o out Specify the file for background collection output redirection
-p nam Specify the collection profile ('Default' by default)
-q Set quiet mode
-s nam Specify the result set name ('output' by default)
-t lvl Specify the trace level
-u lng Specify the language and character set for user interactions
-v Set verbose mode
-w Wait as long as the background collection daemon is active
-x Produce cross references
-y Accept all defaults and skip all pauses
[oracle@ibmlab rda]$
[oracle@lab rda]$ ./rda.sh -v
Collecting diagnostic data ...
------------------------------------------------------------------------------
RDA Data Collection Started 11-Jun-2016 01:10:01
------------------------------------------------------------------------------
Processing RDA.BEGIN module ...
Inside BEGIN module, testing the RDA engine code build
Inside BEGIN module, testing the report directory
Inside BEGIN module, testing the module targets
Inside BEGIN module, testing the database access
Inside BEGIN module, testing the database connection
Inside PERF module, about to execute high loads query
Inside PERF module, gathering the lock information
Inside PERF module, gathering the latch information
Inside PERF module, checking Automatic Gathering Statistics
Inside PERF module, getting the CBO statistics
Inside PERF module, retrieving snapshots for ADDM and AWR reports
- Retrieved snapshots are 5 and 6
Inside PERF module, generating the ADDM report
Inside PERF module, generating the AWR report
[oracle@lab rda]$ ./rda.sh -fv
Collecting diagnostic data ...
------------------------------------------------------------------------------
RDA Data Collection Started 11-Jun-2016 01:13:05
------------------------------------------------------------------------------
Processing RDA.BEGIN module ...
Inside BEGIN module, testing the RDA engine code build
Inside BEGIN module, testing the report directory
Inside BEGIN module, testing the module targets
Inside BEGIN module, testing the database access
Inside BEGIN module, testing the database connection
Enter the password for "SYSTEM" at "cdb1":
Re-enter it to confirm:
[oracle@lab rda]$ ls -ltr
total 5236
-r-xr-xr-x 1 oracle oinstall 9489 Mar 7 17:48 sdci.sh
-r-xr-xr-x 1 oracle oinstall 26981 Mar 7 17:48 sdci.pl
-r-xr-xr-x 1 oracle oinstall 8903 Mar 7 17:48 sdci.cmd
-r-xr-xr-x 1 oracle oinstall 25564 Mar 7 17:48 sdboot.pl
-r--r--r-- 1 oracle oinstall 16172 Mar 7 17:48 README_Windows.txt
-r--r--r-- 1 oracle oinstall 17555 Mar 7 17:48 README_VMS.txt
-r--r--r-- 1 oracle oinstall 5144 Mar 7 17:48 README_upgrade.txt
-r--r--r-- 1 oracle oinstall 16262 Mar 7 17:48 README_Unix.txt
-r--r--r-- 1 oracle oinstall 3346 Mar 7 17:48 README_irda.txt
-r-xr-xr-x 1 oracle oinstall 11698 Mar 7 17:48 rda.sh
-r-xr-xr-x 1 oracle oinstall 43807 Mar 7 17:48 rda.pl
-r-xr-xr-x 1 oracle oinstall 7754 Mar 7 17:48 rda.com
-r-xr-xr-x 1 oracle oinstall 9298 Mar 7 17:48 rda.cmd
-r-xr-xr-x 1 oracle oinstall 14688 Mar 7 17:48 irda.pl
-r--r--r-- 1 oracle oinstall 4106 Mar 7 17:48 DISCLAIM.txt
drwxr-xr-x 2 oracle oinstall 4096 Mar 8 18:13 tools
drwxr-xr-x 19 oracle oinstall 4096 Mar 8 18:13 RDA
drwxr-xr-x 3 oracle oinstall 4096 Mar 8 18:13 Pod
drwxr-xr-x 2 oracle oinstall 4096 Mar 8 18:13 modules
drwxr-xr-x 6 oracle oinstall 4096 Mar 8 18:13 mesg
drwxr-xr-x 3 oracle oinstall 4096 Mar 8 18:13 IRDA
drwxr-xr-x 9 oracle oinstall 4096 Mar 8 18:13 hcve
drwxr-xr-x 2 oracle oinstall 4096 Mar 8 18:13 engine
drwxr-xr-x 3 oracle oinstall 4096 Mar 8 18:13 dfw
drwxr-xr-x 8 oracle oinstall 4096 Mar 8 18:13 Convert
drwxr-xr-x 15 oracle oinstall 4096 Mar 8 18:13 collect
drwxr-xr-x 5 oracle oinstall 4096 Mar 8 18:13 admin
drwxr-xr-x 7 oracle oinstall 4096 Mar 8 18:13 da
drwxr-x--- 6 oracle oinstall 4096 Jun 11 01:03 temp
-rw-r----- 1 oracle oinstall 149989 Jun 11 01:03 set.bak
-rw-r----- 1 oracle oinstall 148687 Jun 11 01:05 set.cfg
drwxr-x--- 5 oracle oinstall 4096 Jun 11 01:05 set
-rw-r----- 1 oracle oinstall 2095445 Jun 11 01:05 RDA_set_ibmlab.zip
-rw-r----- 1 oracle oinstall 148490 Jun 11 01:10 output.bak
-rw-r--r-- 1 oracle oinstall 57998 Jun 11 01:10 output.txt
-rw-r----- 1 oracle oinstall 148669 Jun 11 01:14 output.cfg
drwxr-x--- 5 oracle oinstall 4096 Jun 11 01:14 output
-rw-r----- 1 oracle oinstall 2094613 Jun 11 01:14 RDA_output_ibmlab.zip
[oracle@ibmlab rda]$
2) SQLT/STA
STA is used to get advice for SQLtuning . It helps DBA to do the tuning in manners which actually required to improve bad query performance
Three ways to utilize STA:
Enterprise Manager Grid Control or Database Control
DBMS_SQLTUNE package
sqltrpt.sql script
1. STA Through EM
2) Gather Diagnostics
3) AWR report covering problem period
4) AWR report covering good period of similar load and duration for comparison
5) AWR Compare Periods report comparing the good and bad periods
6) Collect an ASH report for the same period
7) Collect OSWatcher data
8) Collect Alert log and traces covering the duration of the problem
9) Check the Alert log for the period when the issue occurred
10) Find any trace files referenced in the problem period
Some of Advance Oracle Tool and Test case for performance
1) RDA
2) SQLT/STA
3) AWR report
4) ADDM report
5) OS watcher
6) One case of performance tuning which you worked.
7) Various option of sql tracing & tkprof
8) Various option for explain plan generation
9) Test case of your analysis of SQLT, AWR report, explain plan analysis
10) Various OS commands for identify bottleneck
1) Remote Diagnostics Agent(RDA)
RDA is a utility, a set of shell scripts or a PERL script, that can be downloaded from Oracle Support to collect diagnostics information from an Oracle database and it's environment(RAC, ASM, Exadata).
This utility is focused at collecting information that will aid in program diagnosis when logging a call, Oracle support will often request that we install the RDA utility, run it and upload the output to Oracle Support for analysis.
It’s not only a great tool for troubleshooting but also very helpful for documenting an Oracle environment.
RDA offers lots of reporting options, is relatively unobtrusive and provides easy to read results. You can run it on just about any version of the Database or Oracle Applications or Operating System and it is smart enough to figure out where to go and what to gather.
Once installed and run rda.sh or rda.pl, you have to answer some questions and send it off to gather information about your environment. As result you will get a lot of TXT and HTML files. The simplest way of reviewing the output files is to launch a web browser on the same machine where rda.sh has run and open the file RDA__START.htm located in the RDA_Output directory. If you pull up the RDA__START.htm, you can browse through information about your database, server, Java, applications tier, forms and just about anything else you ever wanted to know. And it’s all nicely formatted in HTML with drill-down links.
[oracle@lab rda]$ ls
admin DISCLAIM.txt mesg RDA README_irda.txt sdboot.pl tools
collect engine modules rda.cmd README_Unix.txt sdci.cmd
Convert hcve output rda.com README_upgrade.txt sdci.pl
da IRDA output.cfg rda.pl README_VMS.txt sdci.sh
dfw irda.pl Pod rda.sh README_Windows.txt temp
[oracle@lab rda]$
[oracle@lab rda]$ sh rda.sh
------------------------------------------------------------------------------
RDA Data Collection Started 11-Jun-2016 00:57:32
------------------------------------------------------------------------------
Processing RDA.BEGIN module ...
Enter the password for "SYSTEM" at "cdb1":
Re-enter it to confirm:
Processing OS.PERF module ...
Processing RDA.CONFIG module ...
Processing SAMPLE.SAMPLE module ...
Processing OS.OS module …
Processing OS.PROF module ...
Processing OS.NET module ...
Processing OS.INST module ...
Processing DB.DBA module ...
Processing OFM.WREQ module ...
This can take time. Be patient ...
Processing OFM.IREQ module ...
Processing RDA.LOAD module ...
Processing RDA.END module ...
------------------------------------------------------------------------------
RDA Data Collection Ended 11-Jun-2016 00:58:26
[oracle@ibmlab rda]$ perl -V
Summary of my perl5 (revision 5 version 14 subversion 1) configuration:
Platform:
osname=linux, osvers=2.6.18-238.0.0.0.1.el5xen, archname=x86_64-linux-thread-multi
uname='linux localhost 2.6.18-238.0.0.0.1.el5xen #1 smp tue jan 4 09:38:01 est 2011 x86_64 x86_64 x86_64 gnulinux '
config_args='-Dprefix=/u01/app/oracle/product/12.1.0.2/db_1/perl -Dcc=icc -Doptimize=-O3 -Dusethreads -Duseithreads -Duserelocatableinc -Uusemymalloc -des -Acppflags=-DPERL_USE_SAFE_PUTENV -Accflags=-mp1 -fPIC -fp_port -Aldflags=-i-static -Alddlflags=-i-static -shared -O3 -L/usr/local/lib'
hint=recommended, useposix=true, d_sigaction=define
useithreads=define, usemultiplicity=define
useperlio=define, d_sfio=undef, uselargefiles=define, usesocks=undef
use64bitint=define, use64bitall=define, uselongdouble=undef
usemymalloc=n, bincompat5005=undef
Compiler:
cc='icc', ccflags ='-D_REENTRANT -D_GNU_SOURCE -mp1 -fPIC -fp_port -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64',
optimize='-O3',
cppflags='-DPERL_USE_SAFE_PUTENV -D_REENTRANT -D_GNU_SOURCE -mp1 -fPIC -fp_port -I/usr/local/include'
ccversion='', gccversion='', gccosandvers=''
intsize=4, longsize=8, ptrsize=8, doublesize=8, byteorder=12345678
d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=16
ivtype='long', ivsize=8, nvtype='double', nvsize=8, Off_t='off_t', lseeksize=8
alignbytes=8, prototype=define
Linker and Libraries:
ld='icc', ldflags =' -i-static -L/usr/local/lib'
libpth=/usr/local/lib /lib /usr/lib /lib64 /usr/lib64 /usr/local/lib64
libs=-lnsl -lgdbm -ldb -ldl -lm -lcrypt -lutil -lpthread -lc
perllibs=-lnsl -ldl -lm -lcrypt -lutil -lpthread -lc
libc=/lib/libc-2.5.so, so=so, useshrplib=false, libperl=libperl.a
gnulibc_version='2.5'
Dynamic Linking:
dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags='-Wl,-E'
cccdlflags=' ', lddlflags=' -i-static -shared -O3 -L/usr/local/lib'
Characteristics of this binary (from libperl):
Compile-time options: MULTIPLICITY PERL_DONT_CREATE_GVSV
PERL_IMPLICIT_CONTEXT PERL_MALLOC_WRAP
PERL_PRESERVE_IVUV USE_64_BIT_ALL USE_64_BIT_INT
USE_ITHREADS USE_LARGE_FILES USE_PERLIO USE_PERL_ATOF
USE_REENTRANT_API
Built under linux
Compiled at Jul 8 2011 15:06:01
@INC:
/home/oracle/app/product/12.1.0.2/db_1/perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi
/home/oracle/app/product/12.1.0.2/db_1/perl/lib/site_perl/5.14.1
/home/oracle/app/product/12.1.0.2/db_1/perl/lib/5.14.1/x86_64-linux-thread-multi
/home/oracle/app/product/12.1.0.2/db_1/perl/lib/5.14.1
.
[oracle@ibmlab rda]$
[oracle@ibmlab rda]$ sh rda.sh -s
------------------------------------------------------------------------------
RDA Data Collection Started 11-Jun-2016 01:04:10
------------------------------------------------------------------------------
Processing RDA.BEGIN module ...
Enter the password for "SYSTEM" at "cdb1":
Re-enter it to confirm:
Processing OS.PERF module ...
Processing RDA.CONFIG module ...
Processing SAMPLE.SAMPLE module ...
Processing OS.OS module ...
Processing OS.PROF module ...
Processing OS.NET module ...
Processing OS.INST module ...
Processing DB.DBA module ...
Processing OFM.WREQ module ...
This can take time. Be patient ...
Processing OFM.IREQ module ...
Processing RDA.LOAD module ...
Processing RDA.END module ...
------------------------------------------------------------------------------
RDA Data Collection Ended 11-Jun-2016 01:05:00
[oracle@lab rda]$ ./rda.sh -h
Usage: rda.pl [-bcdfilnvwxy] [-ABCDEGHIKLMPQRSTV] [-e edt] [-m dir] [-o out]
[-p nam] [-s nam] [-t lvl] arg ...
-A Authentify user through the result set definition file
-B Start background collection
-C Collect diagnostic information
-D Delete specified modules from the collection
-E Explain specified error numbers
-G Convert report files to XML format
-H Halt background collection
-I Regenerate the index
-K Kill background collection
-L List the available modules, profiles, and conversion groups
-M Display the related manual pages
-O Render output specifications from the standard input
-P Package the reports (tar or zip)
-Q Display the related setup questions
-R Generate specified reports
-S Setup specified modules
-T Execute test modules
-V Display component version numbers
-b Do not backup result set definition file before saving
-c Check the RDA installation and exit
-d Set debug mode
-e edt Specify a list of alternate setting definitions (var=val,...)
-f Set force mode
-g grp Specify the XML conversion group
-h Display the command usage and exit
-i Read settings from the standard input
-l Use a lock file to prevent concurrent usage of a definition file
-m dir Specify the module directory ('collect' by default)
-n Start a new data collection
-o out Specify the file for background collection output redirection
-p nam Specify the collection profile ('Default' by default)
-q Set quiet mode
-s nam Specify the result set name ('output' by default)
-t lvl Specify the trace level
-u lng Specify the language and character set for user interactions
-v Set verbose mode
-w Wait as long as the background collection daemon is active
-x Produce cross references
-y Accept all defaults and skip all pauses
[oracle@ibmlab rda]$
[oracle@lab rda]$ ./rda.sh -v
Collecting diagnostic data ...
------------------------------------------------------------------------------
RDA Data Collection Started 11-Jun-2016 01:10:01
------------------------------------------------------------------------------
Processing RDA.BEGIN module ...
Inside BEGIN module, testing the RDA engine code build
Inside BEGIN module, testing the report directory
Inside BEGIN module, testing the module targets
Inside BEGIN module, testing the database access
Inside BEGIN module, testing the database connection
Inside PERF module, about to execute high loads query
Inside PERF module, gathering the lock information
Inside PERF module, gathering the latch information
Inside PERF module, checking Automatic Gathering Statistics
Inside PERF module, getting the CBO statistics
Inside PERF module, retrieving snapshots for ADDM and AWR reports
- Retrieved snapshots are 5 and 6
Inside PERF module, generating the ADDM report
Inside PERF module, generating the AWR report
[oracle@lab rda]$ ./rda.sh -fv
Collecting diagnostic data ...
------------------------------------------------------------------------------
RDA Data Collection Started 11-Jun-2016 01:13:05
------------------------------------------------------------------------------
Processing RDA.BEGIN module ...
Inside BEGIN module, testing the RDA engine code build
Inside BEGIN module, testing the report directory
Inside BEGIN module, testing the module targets
Inside BEGIN module, testing the database access
Inside BEGIN module, testing the database connection
Enter the password for "SYSTEM" at "cdb1":
Re-enter it to confirm:
[oracle@lab rda]$ ls -ltr
total 5236
-r-xr-xr-x 1 oracle oinstall 9489 Mar 7 17:48 sdci.sh
-r-xr-xr-x 1 oracle oinstall 26981 Mar 7 17:48 sdci.pl
-r-xr-xr-x 1 oracle oinstall 8903 Mar 7 17:48 sdci.cmd
-r-xr-xr-x 1 oracle oinstall 25564 Mar 7 17:48 sdboot.pl
-r--r--r-- 1 oracle oinstall 16172 Mar 7 17:48 README_Windows.txt
-r--r--r-- 1 oracle oinstall 17555 Mar 7 17:48 README_VMS.txt
-r--r--r-- 1 oracle oinstall 5144 Mar 7 17:48 README_upgrade.txt
-r--r--r-- 1 oracle oinstall 16262 Mar 7 17:48 README_Unix.txt
-r--r--r-- 1 oracle oinstall 3346 Mar 7 17:48 README_irda.txt
-r-xr-xr-x 1 oracle oinstall 11698 Mar 7 17:48 rda.sh
-r-xr-xr-x 1 oracle oinstall 43807 Mar 7 17:48 rda.pl
-r-xr-xr-x 1 oracle oinstall 7754 Mar 7 17:48 rda.com
-r-xr-xr-x 1 oracle oinstall 9298 Mar 7 17:48 rda.cmd
-r-xr-xr-x 1 oracle oinstall 14688 Mar 7 17:48 irda.pl
-r--r--r-- 1 oracle oinstall 4106 Mar 7 17:48 DISCLAIM.txt
drwxr-xr-x 2 oracle oinstall 4096 Mar 8 18:13 tools
drwxr-xr-x 19 oracle oinstall 4096 Mar 8 18:13 RDA
drwxr-xr-x 3 oracle oinstall 4096 Mar 8 18:13 Pod
drwxr-xr-x 2 oracle oinstall 4096 Mar 8 18:13 modules
drwxr-xr-x 6 oracle oinstall 4096 Mar 8 18:13 mesg
drwxr-xr-x 3 oracle oinstall 4096 Mar 8 18:13 IRDA
drwxr-xr-x 9 oracle oinstall 4096 Mar 8 18:13 hcve
drwxr-xr-x 2 oracle oinstall 4096 Mar 8 18:13 engine
drwxr-xr-x 3 oracle oinstall 4096 Mar 8 18:13 dfw
drwxr-xr-x 8 oracle oinstall 4096 Mar 8 18:13 Convert
drwxr-xr-x 15 oracle oinstall 4096 Mar 8 18:13 collect
drwxr-xr-x 5 oracle oinstall 4096 Mar 8 18:13 admin
drwxr-xr-x 7 oracle oinstall 4096 Mar 8 18:13 da
drwxr-x--- 6 oracle oinstall 4096 Jun 11 01:03 temp
-rw-r----- 1 oracle oinstall 149989 Jun 11 01:03 set.bak
-rw-r----- 1 oracle oinstall 148687 Jun 11 01:05 set.cfg
drwxr-x--- 5 oracle oinstall 4096 Jun 11 01:05 set
-rw-r----- 1 oracle oinstall 2095445 Jun 11 01:05 RDA_set_ibmlab.zip
-rw-r----- 1 oracle oinstall 148490 Jun 11 01:10 output.bak
-rw-r--r-- 1 oracle oinstall 57998 Jun 11 01:10 output.txt
-rw-r----- 1 oracle oinstall 148669 Jun 11 01:14 output.cfg
drwxr-x--- 5 oracle oinstall 4096 Jun 11 01:14 output
-rw-r----- 1 oracle oinstall 2094613 Jun 11 01:14 RDA_output_ibmlab.zip
[oracle@ibmlab rda]$
2) SQLT/STA
STA is used to get advice for SQLtuning . It helps DBA to do the tuning in manners which actually required to improve bad query performance
Three ways to utilize STA:
Enterprise Manager Grid Control or Database Control
DBMS_SQLTUNE package
sqltrpt.sql script
1. STA Through EM
The user must have been granted the SELECT_CATALOG_ROLE role.
STA interface can be found through Performance Page > Advisor Central (Related Links) > SQL Tuning Advisor.
Through 'Top Activity' or 'Historical SQL (AWR)', you can choose Hot SQL that you want to tune. And then you can create tune sets and schedule sql tuning.
2. DBMS_SQLTUNE package
STA interface can be found through Performance Page > Advisor Central (Related Links) > SQL Tuning Advisor.
Through 'Top Activity' or 'Historical SQL (AWR)', you can choose Hot SQL that you want to tune. And then you can create tune sets and schedule sql tuning.
2. DBMS_SQLTUNE package
To use the APIs the user must have been granted the DBA role and the ADVISOR privilege.
Running SQL Tuning Advisor using DBMS_SQLTUNE package is a two-step process:
1) Create a SQL tuning task
2) Execute a SQL tuning task
3. sqltrpt.sql script
SQLT, also known as SQLTXPLAIN is a tool provided by Oracle Server Technologies Center of Expertise that can be used to diagnose why a particular SQL statement is performing poorly. It is not like AWR or Statspack which provide a system-wide view of performance. SQLT is very focused and works on one SQL statement at a time. SQLT inputs one SQL statement and outputs a set of diagnostics files. These files can be used to diagnose SQL statements performing poorly.
starting with Oracle 10.2 there is a script ORACLE_HOME/rdbms/admin/sqltrpt.sql which can be used for usage of SQL Tuning Advisor from the command line and it will give recommendation for problematic sql statements.
[oracle@lab ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 15 16:28:45 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> @?/rdbms/admin/sqltrpt.sql
15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
drnmr4vyabw0b 161.99 CREATE TABLE IDT_ML6121G TABLESPACE TSP_MEDIUM PARALLEL
1z1b4yqc1z9gb 151.69 DECLARE cnt NUMBER; bid NUMBER; eid
5f29fpdrtzuyf 117.71 SELECT /* DS_SVC */ /*+ cursor_sharing_exact dynamic_sa
7j5ktg7b0mvx5 92.60 BEGIN PACK_ML6121G02.PC_ADM_ML6121G02(8,10); END;
fghxzqnh282gw 92.48 BEGIN PACK_ML6121G02.PC_ADM_ML6121G02(5,10); END;
5kvqr7uqb08bb 92.11 BEGIN PACK_ML6121G02.PC_ADM_ML6121G02(10,10); END;
8bbt6aufcb7uc 91.73 BEGIN PACK_ML6121G02.PC_ADM_ML6121G02(9,10); END;
8w0uvhgnm8gp8 91.60 BEGIN PACK_ML6121G02.PC_ADM_ML6121G02(4,10); END;
8rtzswgtvf8d7 91.36 BEGIN PACK_ML6121G02.PC_ADM_ML6121G02(3,10); END;
2xmwfr767k2jq 91.27 BEGIN PACK_ML6121G02.PC_ADM_ML6121G02(2,10); END;
2m42sznmsxsz2 91.24 BEGIN PACK_ML6121G02.PC_ADM_ML6121G02(1,10); END;
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
7wutg9a2vqkuk 91.10 BEGIN PACK_ML6121G02.PC_ADM_ML6121G02(6,10); END;
g9pm3gr072wyf 90.64 BEGIN PACK_ML6121G02.PC_ADM_ML6121G02(7,10); END;
6hujqa78085jj 88.37 UPDATE IDT_ML6121G SET PROCESSING_STATUS = :B2 WHERE RO
98w5hha17yc4s 77.94 SELECT DC_REFERENCE, ACTUAL_STOCK_REMAINING_QTY, PERIOD
15 Most expensive SQL in the workload repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
gay2fcy4gd2ka ########## BEGIN pack_ml8704e.getTriadBatchData_ext(:1, :2, :3, :4
b6usrg82hwsa3 ########## call dbms_stats.gather_database_stats_job_proc ( )
9pask37fn7ktu ########## SELECT CREDIT_LIMIT_DECISION_SID, CRED_LIMIT_DECISION_T
bcdw1bfudc6u0 86,840.00 SELECT FUNDING_TYPE_CODE FROM CARD_TRANSACTION CT, FUND
as1c06b0zckuv 82,945.66 SELECT * FROM ( SELECT CLD.CREDIT_MONETARY_LIMIT, TCLS.
ckdusd1cb90wt 79,505.94 SELECT '1' FROM PAYMENT PAY, FUNDING FD WHERE PAY.CONTR
a57kh40ynz990 76,777.82 SELECT * FROM ( SELECT DATE_ORDERED FROM ORDER_REQUEST_
6cg8puc5mz1va 75,787.89 SELECT COUNT (*) COUNT_ANNOTATIONS FROM ANNOTATION_TYPE
47udq4fra5670 74,569.42 SELECT FALCON_SCORE, FRAUD_DATE_DECISION_MADE, MAX_FALC
4q80ff9g1fr6d 65,770.35 SELECT * FROM ( SELECT * FROM TC_ACCOUNTING_SNAPSHOT TC
bsstbb35td6dz 63,806.55 SELECT * FROM ( SELECT * FROM PAYMENT WHERE CONTRACT_NU
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
39azkgtgha1ca 62,167.25 Begin PACK_SCE_DATA_RETRIEVER.PC_RET_TC_DETAILS; PACK_
3kkqx1sjswfym 58,494.63 SELECT DATE_VALID_FROM FROM ANNOTATION WHERE CONTRACT_N
9j0m5v0h95qsj 37,381.88 SELECT SUM(TC_CUM_TOTAL_AMT_SIGNED) PAYMENTS_TOTAL, SUM
44ykbnssax0zz 30,276.44 SELECT * FROM ( SELECT CLD.CREDIT_MONETARY_LIMIT, TCLS.
Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: 6cg8puc5mz1va
Sql Id specified: 6cg8puc5mz1va
Tune the sql
~~~~~~~~~~~~
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_952340
Tuning Task Owner : SYS
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 05/28/2014 10:02:51
Completed at : 05/28/2014 10:03:01
Number of SQL Profile Findings : 1
-------------------------------------------------------------------------------
Schema Name: PILOT_DBA
SQL ID : 6cg8puc5mz1va
SQL Text : SELECT COUNT (*) COUNT_ANNOTATIONS FROM ANNOTATION_TYPE ANN_TY,
ANNOTATION ANN, TRIAD_CRED_STRAT_ANNO_ASSIGN TRD_CS_ANN_ASS
WHERE ANN.CONTRACT_NUMBER = :B4 AND ANN.ANNOTATION_TYPE_CODE =
ANN_TY.ANNOTATION_TYPE_CODE AND TRD_CS_ANN_ASS.CREDIT_STRATEGY_RE
FNO (+)= :B3 AND TRD_CS_ANN_ASS.ANNOTATION_TYPE_CODE (+)=
ANN.ANNOTATION_TYPE_CODE AND ( (ANN.DATE_VALID_UNTIL >= :B1 ) OR
( TRD_CS_ANN_ASS.CREDIT_STRATEGY_REFNO IS NULL AND (
ANN.DATE_VALID_UNTIL IS NULL OR ANN.DATE_VALID_UNTIL =
ANN.DATE_VALID_FROM ) AND ANN.DATE_VALID_FROM + :B2 >= :B1 ) OR
( TRD_CS_ANN_ASS.CREDIT_STRATEGY_REFNO IS NOT NULL AND (
ANN.DATE_VALID_UNTIL IS NULL OR ANN.DATE_VALID_UNTIL =
ANN.DATE_VALID_FROM ) AND ANN.DATE_VALID_FROM +
PACK_DATE_UTILS.FN_CONVERT_PERIOD (
TRD_CS_ANN_ASS.PERIOD_ANNOTATION_EFFECTIVE,
TRD_CS_ANN_ASS.PERIOD_UNIT_ANNOTATION_EFFECT, 'DY') >=:B1 ) )
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 19.73%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_952340',
replace => TRUE);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1889861245
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 10 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 41 | | |
| 2 | NESTED LOOPS | | 9 | 369 | 10 (0)| 00:00:01 |
|* 3 | FILTER | | | | | |
| 4 | NESTED LOOPS OUTER | | 9 | 333 | 10 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| ANNOTATION | 17 | 408 | 7 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | PF_ANN_01 | 3 | | 4 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| TRIAD_CRED_STRAT_ANNO_ASSIGN | 1 | 13 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_DIC_TRD_CS_ANN_ASS | 1 | | 0 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | PK_ANN_TY | 1 | 4 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ANN"."DATE_VALID_UNTIL">=:B1 OR "TRD_CS_ANN_ASS"."CREDIT_STRATEGY_REFNO" IS NULL AND
("ANN"."DATE_VALID_UNTIL" IS NULL OR "ANN"."DATE_VALID_UNTIL"="ANN"."DATE_VALID_FROM") AND
INTERNAL_FUNCTION("ANN"."DATE_VALID_FROM")+:B2>=:B1 OR ("ANN"."DATE_VALID_UNTIL" IS NULL OR
"ANN"."DATE_VALID_UNTIL"="ANN"."DATE_VALID_FROM") AND
INTERNAL_FUNCTION("ANN"."DATE_VALID_FROM")+"PACK_DATE_UTILS"."FN_CONVERT_PERIOD"("TRD_CS_ANN_ASS"."PERIO
D_ANNOTATION_EFFECTIVE","TRD_CS_ANN_ASS"."PERIOD_UNIT_ANNOTATION_EFFECT",'DY')>=:B1 AND
"TRD_CS_ANN_ASS"."CREDIT_STRATEGY_REFNO" IS NOT NULL)
6 - access("ANN"."CONTRACT_NUMBER"=:B4)
8 - access("TRD_CS_ANN_ASS"."CREDIT_STRATEGY_REFNO"(+)=:B3 AND
"TRD_CS_ANN_ASS"."ANNOTATION_TYPE_CODE"(+)="ANN"."ANNOTATION_TYPE_CODE")
9 - access("ANN"."ANNOTATION_TYPE_CODE"="ANN_TY"."ANNOTATION_TYPE_CODE")
2- Using SQL Profile
--------------------
Plan hash value: 1435743108
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 8 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 41 | | |
|* 2 | FILTER | | | | | |
| 3 | NESTED LOOPS OUTER | | 9 | 369 | 8 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 3 | 84 | 7 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| ANNOTATION | 17 | 408 | 7 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | PF_ANN_01 | 3 | | 4 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PK_ANN_TY | 1 | 4 | 0 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | TRIAD_CRED_STRAT_ANNO_ASSIGN | 1 | 13 | 1 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | PK_DIC_TRD_CS_ANN_ASS | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ANN"."DATE_VALID_UNTIL">=:B1 OR "TRD_CS_ANN_ASS"."CREDIT_STRATEGY_REFNO" IS NULL AND
("ANN"."DATE_VALID_UNTIL" IS NULL OR "ANN"."DATE_VALID_UNTIL"="ANN"."DATE_VALID_FROM") AND
INTERNAL_FUNCTION("ANN"."DATE_VALID_FROM")+:B2>=:B1 OR ("ANN"."DATE_VALID_UNTIL" IS NULL OR
"ANN"."DATE_VALID_UNTIL"="ANN"."DATE_VALID_FROM") AND
INTERNAL_FUNCTION("ANN"."DATE_VALID_FROM")+"PACK_DATE_UTILS"."FN_CONVERT_PERIOD"("TRD_CS_ANN_ASS"."PERIO
D_ANNOTATION_EFFECTIVE","TRD_CS_ANN_ASS"."PERIOD_UNIT_ANNOTATION_EFFECT",'DY')>=:B1 AND
"TRD_CS_ANN_ASS"."CREDIT_STRATEGY_REFNO" IS NOT NULL)
6 - access("ANN"."CONTRACT_NUMBER"=:B4)
7 - access("ANN"."ANNOTATION_TYPE_CODE"="ANN_TY"."ANNOTATION_TYPE_CODE")
9 - access("TRD_CS_ANN_ASS"."CREDIT_STRATEGY_REFNO"(+)=:B3 AND
"TRD_CS_ANN_ASS"."ANNOTATION_TYPE_CODE"(+)="ANN"."ANNOTATION_TYPE_CODE")
-------------------------------------------------------------------------------
This report has given recommendation for accepting SQL profile to improve performance of above query
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_952340',
replace => TRUE);
3) AWR Report:
Oracle have provided many performance gathering and reporting tools over the years. Originally the UTLBSTAT/UTLESTAT scripts were used to monitor performance metrics. Oracle8i introduced the Statspack functionality which Oracle9i extended. In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR).
AWR Features
Running SQL Tuning Advisor using DBMS_SQLTUNE package is a two-step process:
1) Create a SQL tuning task
2) Execute a SQL tuning task
3. sqltrpt.sql script
SQLT, also known as SQLTXPLAIN is a tool provided by Oracle Server Technologies Center of Expertise that can be used to diagnose why a particular SQL statement is performing poorly. It is not like AWR or Statspack which provide a system-wide view of performance. SQLT is very focused and works on one SQL statement at a time. SQLT inputs one SQL statement and outputs a set of diagnostics files. These files can be used to diagnose SQL statements performing poorly.
starting with Oracle 10.2 there is a script ORACLE_HOME/rdbms/admin/sqltrpt.sql which can be used for usage of SQL Tuning Advisor from the command line and it will give recommendation for problematic sql statements.
[oracle@lab ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 15 16:28:45 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> @?/rdbms/admin/sqltrpt.sql
15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
drnmr4vyabw0b 161.99 CREATE TABLE IDT_ML6121G TABLESPACE TSP_MEDIUM PARALLEL
1z1b4yqc1z9gb 151.69 DECLARE cnt NUMBER; bid NUMBER; eid
5f29fpdrtzuyf 117.71 SELECT /* DS_SVC */ /*+ cursor_sharing_exact dynamic_sa
7j5ktg7b0mvx5 92.60 BEGIN PACK_ML6121G02.PC_ADM_ML6121G02(8,10); END;
fghxzqnh282gw 92.48 BEGIN PACK_ML6121G02.PC_ADM_ML6121G02(5,10); END;
5kvqr7uqb08bb 92.11 BEGIN PACK_ML6121G02.PC_ADM_ML6121G02(10,10); END;
8bbt6aufcb7uc 91.73 BEGIN PACK_ML6121G02.PC_ADM_ML6121G02(9,10); END;
8w0uvhgnm8gp8 91.60 BEGIN PACK_ML6121G02.PC_ADM_ML6121G02(4,10); END;
8rtzswgtvf8d7 91.36 BEGIN PACK_ML6121G02.PC_ADM_ML6121G02(3,10); END;
2xmwfr767k2jq 91.27 BEGIN PACK_ML6121G02.PC_ADM_ML6121G02(2,10); END;
2m42sznmsxsz2 91.24 BEGIN PACK_ML6121G02.PC_ADM_ML6121G02(1,10); END;
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
7wutg9a2vqkuk 91.10 BEGIN PACK_ML6121G02.PC_ADM_ML6121G02(6,10); END;
g9pm3gr072wyf 90.64 BEGIN PACK_ML6121G02.PC_ADM_ML6121G02(7,10); END;
6hujqa78085jj 88.37 UPDATE IDT_ML6121G SET PROCESSING_STATUS = :B2 WHERE RO
98w5hha17yc4s 77.94 SELECT DC_REFERENCE, ACTUAL_STOCK_REMAINING_QTY, PERIOD
15 Most expensive SQL in the workload repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
gay2fcy4gd2ka ########## BEGIN pack_ml8704e.getTriadBatchData_ext(:1, :2, :3, :4
b6usrg82hwsa3 ########## call dbms_stats.gather_database_stats_job_proc ( )
9pask37fn7ktu ########## SELECT CREDIT_LIMIT_DECISION_SID, CRED_LIMIT_DECISION_T
bcdw1bfudc6u0 86,840.00 SELECT FUNDING_TYPE_CODE FROM CARD_TRANSACTION CT, FUND
as1c06b0zckuv 82,945.66 SELECT * FROM ( SELECT CLD.CREDIT_MONETARY_LIMIT, TCLS.
ckdusd1cb90wt 79,505.94 SELECT '1' FROM PAYMENT PAY, FUNDING FD WHERE PAY.CONTR
a57kh40ynz990 76,777.82 SELECT * FROM ( SELECT DATE_ORDERED FROM ORDER_REQUEST_
6cg8puc5mz1va 75,787.89 SELECT COUNT (*) COUNT_ANNOTATIONS FROM ANNOTATION_TYPE
47udq4fra5670 74,569.42 SELECT FALCON_SCORE, FRAUD_DATE_DECISION_MADE, MAX_FALC
4q80ff9g1fr6d 65,770.35 SELECT * FROM ( SELECT * FROM TC_ACCOUNTING_SNAPSHOT TC
bsstbb35td6dz 63,806.55 SELECT * FROM ( SELECT * FROM PAYMENT WHERE CONTRACT_NU
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
39azkgtgha1ca 62,167.25 Begin PACK_SCE_DATA_RETRIEVER.PC_RET_TC_DETAILS; PACK_
3kkqx1sjswfym 58,494.63 SELECT DATE_VALID_FROM FROM ANNOTATION WHERE CONTRACT_N
9j0m5v0h95qsj 37,381.88 SELECT SUM(TC_CUM_TOTAL_AMT_SIGNED) PAYMENTS_TOTAL, SUM
44ykbnssax0zz 30,276.44 SELECT * FROM ( SELECT CLD.CREDIT_MONETARY_LIMIT, TCLS.
Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: 6cg8puc5mz1va
Sql Id specified: 6cg8puc5mz1va
Tune the sql
~~~~~~~~~~~~
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_952340
Tuning Task Owner : SYS
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 05/28/2014 10:02:51
Completed at : 05/28/2014 10:03:01
Number of SQL Profile Findings : 1
-------------------------------------------------------------------------------
Schema Name: PILOT_DBA
SQL ID : 6cg8puc5mz1va
SQL Text : SELECT COUNT (*) COUNT_ANNOTATIONS FROM ANNOTATION_TYPE ANN_TY,
ANNOTATION ANN, TRIAD_CRED_STRAT_ANNO_ASSIGN TRD_CS_ANN_ASS
WHERE ANN.CONTRACT_NUMBER = :B4 AND ANN.ANNOTATION_TYPE_CODE =
ANN_TY.ANNOTATION_TYPE_CODE AND TRD_CS_ANN_ASS.CREDIT_STRATEGY_RE
FNO (+)= :B3 AND TRD_CS_ANN_ASS.ANNOTATION_TYPE_CODE (+)=
ANN.ANNOTATION_TYPE_CODE AND ( (ANN.DATE_VALID_UNTIL >= :B1 ) OR
( TRD_CS_ANN_ASS.CREDIT_STRATEGY_REFNO IS NULL AND (
ANN.DATE_VALID_UNTIL IS NULL OR ANN.DATE_VALID_UNTIL =
ANN.DATE_VALID_FROM ) AND ANN.DATE_VALID_FROM + :B2 >= :B1 ) OR
( TRD_CS_ANN_ASS.CREDIT_STRATEGY_REFNO IS NOT NULL AND (
ANN.DATE_VALID_UNTIL IS NULL OR ANN.DATE_VALID_UNTIL =
ANN.DATE_VALID_FROM ) AND ANN.DATE_VALID_FROM +
PACK_DATE_UTILS.FN_CONVERT_PERIOD (
TRD_CS_ANN_ASS.PERIOD_ANNOTATION_EFFECTIVE,
TRD_CS_ANN_ASS.PERIOD_UNIT_ANNOTATION_EFFECT, 'DY') >=:B1 ) )
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 19.73%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_952340',
replace => TRUE);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1889861245
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 10 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 41 | | |
| 2 | NESTED LOOPS | | 9 | 369 | 10 (0)| 00:00:01 |
|* 3 | FILTER | | | | | |
| 4 | NESTED LOOPS OUTER | | 9 | 333 | 10 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| ANNOTATION | 17 | 408 | 7 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | PF_ANN_01 | 3 | | 4 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| TRIAD_CRED_STRAT_ANNO_ASSIGN | 1 | 13 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_DIC_TRD_CS_ANN_ASS | 1 | | 0 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | PK_ANN_TY | 1 | 4 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ANN"."DATE_VALID_UNTIL">=:B1 OR "TRD_CS_ANN_ASS"."CREDIT_STRATEGY_REFNO" IS NULL AND
("ANN"."DATE_VALID_UNTIL" IS NULL OR "ANN"."DATE_VALID_UNTIL"="ANN"."DATE_VALID_FROM") AND
INTERNAL_FUNCTION("ANN"."DATE_VALID_FROM")+:B2>=:B1 OR ("ANN"."DATE_VALID_UNTIL" IS NULL OR
"ANN"."DATE_VALID_UNTIL"="ANN"."DATE_VALID_FROM") AND
INTERNAL_FUNCTION("ANN"."DATE_VALID_FROM")+"PACK_DATE_UTILS"."FN_CONVERT_PERIOD"("TRD_CS_ANN_ASS"."PERIO
D_ANNOTATION_EFFECTIVE","TRD_CS_ANN_ASS"."PERIOD_UNIT_ANNOTATION_EFFECT",'DY')>=:B1 AND
"TRD_CS_ANN_ASS"."CREDIT_STRATEGY_REFNO" IS NOT NULL)
6 - access("ANN"."CONTRACT_NUMBER"=:B4)
8 - access("TRD_CS_ANN_ASS"."CREDIT_STRATEGY_REFNO"(+)=:B3 AND
"TRD_CS_ANN_ASS"."ANNOTATION_TYPE_CODE"(+)="ANN"."ANNOTATION_TYPE_CODE")
9 - access("ANN"."ANNOTATION_TYPE_CODE"="ANN_TY"."ANNOTATION_TYPE_CODE")
2- Using SQL Profile
--------------------
Plan hash value: 1435743108
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 8 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 41 | | |
|* 2 | FILTER | | | | | |
| 3 | NESTED LOOPS OUTER | | 9 | 369 | 8 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 3 | 84 | 7 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| ANNOTATION | 17 | 408 | 7 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | PF_ANN_01 | 3 | | 4 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PK_ANN_TY | 1 | 4 | 0 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | TRIAD_CRED_STRAT_ANNO_ASSIGN | 1 | 13 | 1 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | PK_DIC_TRD_CS_ANN_ASS | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ANN"."DATE_VALID_UNTIL">=:B1 OR "TRD_CS_ANN_ASS"."CREDIT_STRATEGY_REFNO" IS NULL AND
("ANN"."DATE_VALID_UNTIL" IS NULL OR "ANN"."DATE_VALID_UNTIL"="ANN"."DATE_VALID_FROM") AND
INTERNAL_FUNCTION("ANN"."DATE_VALID_FROM")+:B2>=:B1 OR ("ANN"."DATE_VALID_UNTIL" IS NULL OR
"ANN"."DATE_VALID_UNTIL"="ANN"."DATE_VALID_FROM") AND
INTERNAL_FUNCTION("ANN"."DATE_VALID_FROM")+"PACK_DATE_UTILS"."FN_CONVERT_PERIOD"("TRD_CS_ANN_ASS"."PERIO
D_ANNOTATION_EFFECTIVE","TRD_CS_ANN_ASS"."PERIOD_UNIT_ANNOTATION_EFFECT",'DY')>=:B1 AND
"TRD_CS_ANN_ASS"."CREDIT_STRATEGY_REFNO" IS NOT NULL)
6 - access("ANN"."CONTRACT_NUMBER"=:B4)
7 - access("ANN"."ANNOTATION_TYPE_CODE"="ANN_TY"."ANNOTATION_TYPE_CODE")
9 - access("TRD_CS_ANN_ASS"."CREDIT_STRATEGY_REFNO"(+)=:B3 AND
"TRD_CS_ANN_ASS"."ANNOTATION_TYPE_CODE"(+)="ANN"."ANNOTATION_TYPE_CODE")
-------------------------------------------------------------------------------
This report has given recommendation for accepting SQL profile to improve performance of above query
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_952340',
replace => TRUE);
3) AWR Report:
Oracle have provided many performance gathering and reporting tools over the years. Originally the UTLBSTAT/UTLESTAT scripts were used to monitor performance metrics. Oracle8i introduced the Statspack functionality which Oracle9i extended. In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR).
AWR Features
The AWR is used to collect performance statistics including:
Wait events used to identify performance problems.
Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
Object usage statistics.
Resource intensive SQL statements.
Workload Repository Views
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.
Workload Repository Reports
Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows.
AWR report can be generated upon setting the parameter is called statistics_level=typical or all, If set to basic it will be disabled. We can get the value like below.
SQL> show parameter statistics_l
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
SQL>
1. Snapshots
Snapshots are sets of historical data for specific time periods that are used for performance comparisons by ADDM. By default, Oracle Database automatically generates snapshots of the performance data once every hour and retains the statistics in the workload repository for 8 days. You can also manually create snapshots, but this is usually not necessary. The data in the snapshot interval is then analyzed by the Automatic Database Diagnostic Monitor (ADDM).
2. Baselines
A baseline contains performance data from a specific time period that is preserved for comparison with other similar workload periods when performance problems occur. The snapshots contained in a baseline are excluded from the automatic AWR purging process and are retained indefinitely.
There are several types of available baselines in Oracle Database:
2.1. Fixed Baselines: - A fixed baseline corresponds to a fixed, contiguous time period in the past that you specify. Before creating a fixed baseline, carefully consider the time period you choose as a baseline, because the baseline should represent the system operating at an optimal level. In the future, you can compare the baseline with other baselines or snapshots captured during periods of poor performance to analyze performance degradation over time.
2.2. Moving Window Baseline: - A moving window baseline corresponds to all AWR data that exists within the AWR retention period. This is useful when using adaptive thresholds because the database can use AWR data in the entire AWR retention period to compute metric threshold values.
2.3. Baseline Templates: - You can also create baselines for a contiguous time period in the future using baseline templates. There are two types of baseline templates: single and repeating. You can use a single baseline template to create a baseline for a single contiguous time period in the future. This technique is useful if you know beforehand of a time period that you intend to capture in the future. For example, you may want to capture the AWR data during a system test that is scheduled for the upcoming weekend. In this case, you can create a single baseline template to automatically capture the time period when the test occurs. You can use a repeating baseline template to create and drop baselines based on a repeating time schedule. This is useful if you want Oracle Database to automatically capture a contiguous time period on an ongoing basis. For example, you may want to capture the AWR data during every Monday morning for a month. In this case, you can create a repeating baseline template to automatically create baselines on a repeating schedule for every Monday, and automatically remove older baselines after a specified expiration interval, such as one month.
3. Managing Snapshots:-
There are several types of available baselines in Oracle Database:
2.1. Fixed Baselines: - A fixed baseline corresponds to a fixed, contiguous time period in the past that you specify. Before creating a fixed baseline, carefully consider the time period you choose as a baseline, because the baseline should represent the system operating at an optimal level. In the future, you can compare the baseline with other baselines or snapshots captured during periods of poor performance to analyze performance degradation over time.
2.2. Moving Window Baseline: - A moving window baseline corresponds to all AWR data that exists within the AWR retention period. This is useful when using adaptive thresholds because the database can use AWR data in the entire AWR retention period to compute metric threshold values.
2.3. Baseline Templates: - You can also create baselines for a contiguous time period in the future using baseline templates. There are two types of baseline templates: single and repeating. You can use a single baseline template to create a baseline for a single contiguous time period in the future. This technique is useful if you know beforehand of a time period that you intend to capture in the future. For example, you may want to capture the AWR data during a system test that is scheduled for the upcoming weekend. In this case, you can create a single baseline template to automatically capture the time period when the test occurs. You can use a repeating baseline template to create and drop baselines based on a repeating time schedule. This is useful if you want Oracle Database to automatically capture a contiguous time period on an ongoing basis. For example, you may want to capture the AWR data during every Monday morning for a month. In this case, you can create a repeating baseline template to automatically create baselines on a repeating schedule for every Monday, and automatically remove older baselines after a specified expiration interval, such as one month.
3. Managing Snapshots:-
By default, Oracle Database generates snapshots once every hour, and retains the statistics in the workload repository for 8 days. When necessary, you can use DBMS_WORKLOAD_REPOSITORY procedures to manually create, drop, and modify the snapshots. To invoke these procedures, a user must be granted the DBA role.
3.1. Creating Snapshots :- You can manually create snapshots with the CREATE_SNAPSHOT procedure to capture statistics at times different than those of the automatically generated snapshots. For example:
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/
3.2. Dropping Snapshots:- You can drop a range of snapshots using the DROP_SNAPSHOT_RANGE procedure. To view a list of the snapshot IDs along with database IDs, check the DBA_HIST_SNAPSHOT view. For example, you can drop the following range of snapshots:
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => ,
high_snap_id => , dbid =>);
END;
/
3.3. Modifying Snapshot Settings:- You can adjust the interval, retention, and captured Top SQL of snapshot generation for a specified database ID, but note that this can affect the precision of the Oracle Database diagnostic tools. The INTERVAL setting affects how often the database automatically generates snapshots. The RETENTION setting affects how long the database stores snapshots in the workload repository. The TOPNSQL setting affects the number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, sharable Memory, and Version Count). The value for this setting is not affected by the statistics/flush level and will override the system default behavior for the AWR SQL collection. It is possible to set the value for this setting to MAXIMUM to capture the complete set of SQL in the shared SQL area, though by doing so (or by setting the value to a very high number) may lead to possible space and performance issues because there will more data to collect and store. To adjust the settings, use the MODIFY_SNAPSHOT_SETTINGS procedure. For example:
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200,
interval => 30, topnsql => 100, dbid => 3310949047);
END;
/
3.1. Creating Snapshots :- You can manually create snapshots with the CREATE_SNAPSHOT procedure to capture statistics at times different than those of the automatically generated snapshots. For example:
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/
3.2. Dropping Snapshots:- You can drop a range of snapshots using the DROP_SNAPSHOT_RANGE procedure. To view a list of the snapshot IDs along with database IDs, check the DBA_HIST_SNAPSHOT view. For example, you can drop the following range of snapshots:
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => ,
high_snap_id => , dbid =>);
END;
/
3.3. Modifying Snapshot Settings:- You can adjust the interval, retention, and captured Top SQL of snapshot generation for a specified database ID, but note that this can affect the precision of the Oracle Database diagnostic tools. The INTERVAL setting affects how often the database automatically generates snapshots. The RETENTION setting affects how long the database stores snapshots in the workload repository. The TOPNSQL setting affects the number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, sharable Memory, and Version Count). The value for this setting is not affected by the statistics/flush level and will override the system default behavior for the AWR SQL collection. It is possible to set the value for this setting to MAXIMUM to capture the complete set of SQL in the shared SQL area, though by doing so (or by setting the value to a very high number) may lead to possible space and performance issues because there will more data to collect and store. To adjust the settings, use the MODIFY_SNAPSHOT_SETTINGS procedure. For example:
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200,
interval => 30, topnsql => 100, dbid => 3310949047);
END;
/
4. Managing Baseline Templates
This section describes how to manage baseline templates. You can automatically create baselines to capture specified time periods in the future using baseline templates.
4.1. Creating a Single Baseline Template
4.1. Creating a Single Baseline Template
This section describes how to create a single baseline template. You can use a single baseline template to create a baseline during a single, fixed time interval in the future. For example, you can create a single baseline template to generate a baseline that is captured on April 2, 2009 from 5:00 p.m. to 8:00 p.m.To create a single baseline template, use the CREATE_BASELINE_TEMPLATE procedure:
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
start_time => '2009-04-02 17:00:00 PST',
end_time => '2009-04-02 20:00:00 PST',
baseline_name => 'baseline_090402',
template_name => 'template_090402', expiration => 30,
dbid => 3310949047);
END;
/
4.2. Creating a Repeating Baseline Template
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
start_time => '2009-04-02 17:00:00 PST',
end_time => '2009-04-02 20:00:00 PST',
baseline_name => 'baseline_090402',
template_name => 'template_090402', expiration => 30,
dbid => 3310949047);
END;
/
4.2. Creating a Repeating Baseline Template
This section describes how to create a repeating baseline template. A repeating baseline template can be used to automatically create baselines that repeat during a particular time interval over a specific period in the future. For example, you can create a repeating baseline template to generate a baseline that repeats every Monday from 5:00 p.m. to 8:00 p.m. for the year 2009.
To create a repeating baseline template, use the CREATE_BASELINE_TEMPLATE procedure:
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
day_of_week => 'monday', hour_in_day => 17,
duration => 3, expiration => 30,
start_time => '2009-04-02 17:00:00 PST',
end_time => '2009-12-31 20:00:00 PST',
baseline_name_prefix => 'baseline_2009_mondays_',
template_name => 'template_2009_mondays',
dbid => 3310949047);
END;
/
4.3. Dropping a Baseline Template
To create a repeating baseline template, use the CREATE_BASELINE_TEMPLATE procedure:
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
day_of_week => 'monday', hour_in_day => 17,
duration => 3, expiration => 30,
start_time => '2009-04-02 17:00:00 PST',
end_time => '2009-12-31 20:00:00 PST',
baseline_name_prefix => 'baseline_2009_mondays_',
template_name => 'template_2009_mondays',
dbid => 3310949047);
END;
/
4.3. Dropping a Baseline Template
This section describes how to drop an existing baseline template. Periodically, you may want to remove baselines templates that are no longer used to conserve disk space.
To drop a baseline template:
Review the existing baselines in the DBA_HIST_BASELINE_TEMPLATE view to determine the baseline template you want to drop.
Use the DROP_BASELINE_TEMPLATE procedure to drop the desired baseline template:
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE_TEMPLATE (
template_name => 'template_2009_mondays',
dbid => 3310949047);
END;
/
5. Generate AWR report:-
To drop a baseline template:
Review the existing baselines in the DBA_HIST_BASELINE_TEMPLATE view to determine the baseline template you want to drop.
Use the DROP_BASELINE_TEMPLATE procedure to drop the desired baseline template:
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE_TEMPLATE (
template_name => 'template_2009_mondays',
dbid => 3310949047);
END;
/
5. Generate AWR report:-
The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot IDs. Below is the example.
Generating an Oracle RAC AWR Report:-
The awrgrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot IDs using the current database identifier and all available database instances in an Oracle Real Application Clusters (Oracle RAC) environment.
Generate a report for specific Instance.
The awrrpti.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot IDs using a specific database and instance. This script enables you to specify a database identifier and instance for which the AWR report will be generated.
Important which need to address from AWR report.
Generating an Oracle RAC AWR Report:-
The awrgrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot IDs using the current database identifier and all available database instances in an Oracle Real Application Clusters (Oracle RAC) environment.
Generate a report for specific Instance.
The awrrpti.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot IDs using a specific database and instance. This script enables you to specify a database identifier and instance for which the AWR report will be generated.
Important which need to address from AWR report.
1) Load Profile
2) Instance Efficiency Target (100%)
3) Top 5/10 Events
4) Time Mode Statistics
The first section displayed on the report shows a summary of the snapshot window for your report
as well as a brief look at the elapsed time, which represents the snapshot window, and the DB time,
which represents activity on your database. If the DB time exceeds the elapsed time, it denotes a busy
database. If it is a lot higher than the elapsed time, it may mean that some sessions are waiting for resources.
The instance efficiency section gives you a very quick view to determine if things are running adequately on your database. Generally, most percentages within this section should be above 90%. The Parse CPU to Parse Elapsd metric shows how much time the CPU is spending parsing SQL statements.
The third place to get a quick glance at your database performance is the Top 5 Timed Events
section. This section gives you a quick look at exactly where the highest amount of resources are being consumed within your database for the snapshot period. Based on these results, it may show you that
there is an inordinate amount of time spent performing full-table scans, or getting data across a network database link.
Below is one example which shows main point related to of awr report which are checked and recommend to application team.
IN Top Wait Event is coming enq:TX - row lock contention its totally based on one Query which was being fired from App side.
It is also forcing us to go for the SQL Ordered part as sql execute to elapsed time is high close to 99% of DB time was being spent on it.
Let go on the Drill Down part now based on the above observation:-
DB file parallel write wait event is totally related to I/O Problem , I will show the artifacts of the same below as well.
Common Causes and Actions
The db file parallel write latency is normally a symptom of a slow I/O subsystem or poor I/O configurations. This includes poor layout of database files, bad mount point to I/O controller ratio, wrong stripe size and/or RAID level, and not enough disks (i.e. there are a few high capacity disks versus many lower capacity disks).
The DBA needs to look at the average I/O time.
Please find the Query which is Creating a problem in the DB as its running with FOR Update clause also its coming high on the Elapsed Time as well as on the CPU Time.
So request you please check this Query and its logic with App Team.
Here is the Artifacts of I/O Problem , Please check the Av RD(ms) column .
In a good I/O system it should not go above the 20 but in our case it is going much beyond that request you to take care this part asap..
Files Snapshot :-
The Table used in the above Highlighted Query i.e Network_Stocks table is the main object creating the Row Locks Waits as well.
Backup was also running during the Problematic time :-
Also when I checked the System I observed that All Application Session were going on the Node 2 only but today its looking good.
4) ADDM Report:
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
addmrpt.sql Script
The addmrpt.sql script can be used to create an ADDM report from SQL*Plus. The script is called as follows.
-- UNIX
@/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/addmrpt.sql
-- Windows
@d:\oracle\product\10.1.0\db_1\rdbms\admin\addmrpt.sql
SQL> @?/rdbms/admin/addmrpt.sql
Below is one test case for addm:-
I found row lock wait was too high so down the line found problematic query as well and shared that query with application team to check from their and it was rectified by them.
addmrpt_1_22542_22543.txt
ADDM Report for Task 'TASK_33939'
---------------------------------
Analysis Period
---------------
AWR snapshot range from 22542 to 22543.
Time period starts at 06-JUN-16 04.00.06 PM
Time period ends at 06-JUN-16 05.00.12 PM
Analysis Target
---------------
Database 'DBNAME' with DB ID 577252600.
Database version 11.2.0.3.0.
ADDM performed an analysis of instance PACSMG, numbered 1 and hosted at
XXXXXXXX.
Activity During the Analysis Period
-----------------------------------
Total database time was 36014 seconds.
The average number of active sessions was 9.99.
Summary of Findings
-------------------
Description Active Sessions Recommendation
s
Percent of Activity
---------------------------------------- ------------------- --------------
-
1 Top SQL Statements 6.59 | 66.02 6
2 Row Lock Waits 4.17 | 41.7 1
3 Undersized SGA .64 | 6.4 1
4 Top Segments by "User I/O" and "Cluster" .19 | 1.91 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Findings and Recommendations
----------------------------
Finding 1: Top SQL Statements
Impact is 6.59 active sessions, 66.02% of total activity.
---------------------------------------------------------
SQL statements consuming significant database time were found. These
statements offer a good opportunity for performance improvement.
Recommendation 1: SQL Tuning
Estimated benefit is 4.46 active sessions, 44.68% of total activity.
--------------------------------------------------------------------
Action
Investigate the SELECT statement with SQL_ID "8wfzx3nyvma8m" for
possible performance improvements. You can supplement the information
given here with an ASH report for this SQL_ID.
Related Object
SQL statement with SQL_ID 8wfzx3nyvma8m.
SELECT TRIM(MDL_PARAMETER_VALUE) FROM FDS_M_MODULE_PARAM WHERE
MOD_1_MOD_ID ='PROVS' AND MDL_PARAMETER_ID =:B1 FOR UPDATE OF
FDS_M_MODULE_PARAM.MDL_PARAMETER_VALUE
Rationale
The SQL spent only 0% of its database time on CPU, I/O and Cluster
waits. Therefore, the SQL Tuning Advisor is not applicable in this case.
Look at performance data for the SQL to find potential improvements.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "8wfzx3nyvma8m" was executed 412 times and had
an average elapsed time of 36 seconds.
Rationale
Waiting for event "enq: TX - row lock contention" in wait class
"Application" accounted for 100% of the database time spent in
processing the SQL statement with SQL_ID "8wfzx3nyvma8m".
Recommendation 2: SQL Tuning
Estimated benefit is .54 active sessions, 5.41% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the UPSERT statement with SQL_ID
"672j4rmvvnmru".
Related Object
SQL statement with SQL_ID 672j4rmvvnmru.
MERGE INTO SIMSWAP_REPROCESS X USING ( SELECT SYSDATE CREATION_DATE,
B.SER_SERVICE_ORDER_ID, A.SER_MSISDN, A.SER_IMSI_NO,
A.SER_SERVICE_VALUE, B.SER_RECIEVED_DT_TIME, A.SER_ERROR, 'PENDING'
STATUS, NULL HLR_IMSI, NULL REPROCESS_DATE, D.SIM_SIM_NO NEW_SIM_NO
FROM INCMS.CIN_M_SIM D, INCMS.CIN_T_SERVICE_ORDER_DETAIL A,
INCMS.CIN_T_SERVICE_ORDER B, ( SELECT SER_MSISDN,
MAX(SER_RECIEVED_DT_TIME) SER_RECIEVED_DT_TIME FROM
INCMS.CIN_T_SERVICE_ORDER_DETAIL WHERE SER_RECIEVED_DT_TIME >
SYSDATE-3 AND SER_SERVICE_ID IN ('SIMCH','SMCHE') GROUP BY SER_MSISDN
) C WHERE B.SER_SERVICE_ORDER_ID = A.SER_SERVICE_ORDER_ID AND
D.SIM_IMSI_NO = SUBSTR(A.SER_SERVICE_VALUE,1,15) AND B.SER_OPERATION
= 'MODS' AND B.SER_PROVISIONING_TYPE <> 'BULK' AND
A.SER_RECIEVED_DT_TIME > SYSDATE-3 AND A.SER_SERVICE_ID IN
('SIMCH','SMCHE') AND A.SER_MSISDN = C.SER_MSISDN AND
A.SER_RECIEVED_DT_TIME = C.SER_RECIEVED_DT_TIME AND A.SER_STATUS =
'FAIL' AND (A.SER_ERROR LIKE 'Unknown Error:RESP:18310%' OR
A.SER_ERROR = 'Ext. system communication link failure.Please contact
switch people.' OR A.SER_ERROR = 'Unknown Error:RESP:18304 recieved
from EMA.Please Contact switch people' OR A.SER_ERROR LIKE '%fail
Fail File of Zero Size' OR A.SER_ERROR LIKE 'Unknown
Error:RESP:18398%' ) ) Y ON (X.SER_SERVICE_ORDER_ID =
Y.SER_SERVICE_ORDER_ID) WHEN NOT MATCHED THEN INSERT(X.CREATION_DATE,
X.SER_SERVICE_ORDER_ID, X.SER_MSISDN, X.SER_IMSI_NO,
X.SER_SERVICE_VALUE, X.SER_RECIEVED_DT_TIME, X.SER_ERROR, X.STATUS)
VALUES(SYSDATE, Y.SER_SERVICE_ORDER_ID, Y.SER_MSISDN, Y.SER_IMSI_NO,
Y.SER_SERVICE_VALUE, Y.SER_RECIEVED_DT_TIME, Y.SER_ERROR, 'PENDING')
WHEN MATCHED THEN UPDATE SET X.CREATION_DATE = SYSDATE, X.STATUS =
'PENDING'
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "672j4rmvvnmru" was executed 109 times and had
an average elapsed time of 17 seconds.
Rationale
Top level calls to execute the PL/SQL statement with SQL_ID
"g2j4rw7vpbcbm" are responsible for 100% of the database time spent on
the UPSERT statement with SQL_ID "672j4rmvvnmru".
Related Object
SQL statement with SQL_ID g2j4rw7vpbcbm.
BEGIN provisioning.simswap_reprocess; END;
Recommendation 3: SQL Tuning
Estimated benefit is .53 active sessions, 5.26% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"52us6knb8uw7a".
Related Object
SQL statement with SQL_ID 52us6knb8uw7a.
SELECT TO_CHAR("A1"."START_TIME",:"SYS_B_00"),TO_CHAR("A1"."START_TIM
E",:"SYS_B_01"),TO_CHAR("A1"."START_TIME",:"SYS_B_02"),"A1"."APN_NI",
CASE "A1"."SGSN_ADDRESS" WHEN :"SYS_B_03" THEN :"SYS_B_04" ELSE CASE
WHEN ("A1"."ORIGINAL_CALL_TYPE"=:"SYS_B_05" AND "A1"."SGSN_ADDRESS"
IS NULL) THEN :"SYS_B_06" ELSE :"SYS_B_07" END END
,COUNT(*),SUM(NVL("A1"."DATA_UP",:"SYS_B_08")),SUM(NVL("A1"."DATA_DOW
N",:"SYS_B_09")),SUM(NVL("A1"."DATA_UP",:"SYS_B_10"))+SUM(NVL("A1"."D
ATA_DOWN",:"SYS_B_11")),SUM(NVL("A1"."DATA_UP",:"SYS_B_12"))/:"SYS_B_
13"+SUM(NVL("A1"."DATA_DOWN",:"SYS_B_14"))/:"SYS_B_15",:"SYS_B_16",CA
SE "A1"."SGSN_ADDRESS" WHEN :"SYS_B_17" THEN :"SYS_B_18" ELSE CASE
WHEN ("A1"."ORIGINAL_CALL_TYPE"=:"SYS_B_19" AND "A1"."SGSN_ADDRESS"
IS NULL) THEN :"SYS_B_20" ELSE :"SYS_B_21" END END FROM
"INCMS"."HBST_RAT_UNBILD_CALL" "A1" WHERE
"A1"."SERVICE_ID"=:"SYS_B_22" AND
TO_NUMBER(TO_CHAR("A1"."START_TIME",:"SYS_B_23"))=:"SYS_B_24" GROUP
BY TO_CHAR("A1"."START_TIME",:"SYS_B_25"),TO_CHAR("A1"."START_TIME",:
"SYS_B_26"),TO_CHAR("A1"."START_TIME",:"SYS_B_27"),"A1"."APN_NI","A1"
."SGSN_ADDRESS","A1"."ORIGINAL_CALL_TYPE"
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "52us6knb8uw7a" was executed 4 times and had
an average elapsed time of 446 seconds.
Recommendation 4: SQL Tuning
Estimated benefit is .41 active sessions, 4.1% of total activity.
-----------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"cxwc6bnrmapfh".
Related Object
SQL statement with SQL_ID cxwc6bnrmapfh.
select b.ser_service_order_id||:"SYS_B_0"||a.ser_msisdn||:"SYS_B_1"||
a.ser_service_value||:"SYS_B_2"||a.ser_error
from incms.CIN_T_SERVICE_ORDER_DETAIL a, incms.CIN_T_SERVICE_ORDER b,
incms.cms_m_customer c
where b.SER_SERVICE_ORDER_ID = a.SER_SERVICE_ORDER_ID
and a.ser_msisdn = c.cus_tel_no
and b.ser_provisioning_type = :"SYS_B_3"
and b.ser_operation in (:"SYS_B_4")
and a.SER_RECIEVED_DT_TIME > sysdate-:"SYS_B_5"
and a.SER_SERVICE_ID in (:"SYS_B_6",:"SYS_B_7")
and a.ser_status = :"SYS_B_8"
order by b.ser_service_order_id
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "cxwc6bnrmapfh" was executed 81 times and had
an average elapsed time of 17 seconds.
Recommendation 5: SQL Tuning
Estimated benefit is .41 active sessions, 4.07% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"an6y0xhff8hsc".
Related Object
SQL statement with SQL_ID an6y0xhff8hsc.
select account_no,invoice_date,os,invoice_Date+:"SYS_B_0",floor(sysda
te-(invoice_date+:"SYS_B_1")),unadjusted_amount,add_months(invoice_da
te,-:"SYS_B_2") from hbst_dunning_os
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "an6y0xhff8hsc" was executed 54 times and had
an average elapsed time of 25 seconds.
Rationale
At least 2 distinct execution plans were utilized for this SQL statement
during the analysis period.
Recommendation 6: SQL Tuning
Estimated benefit is .25 active sessions, 2.5% of total activity.
-----------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"5rs41gbgw5x10".
Related Object
SQL statement with SQL_ID 5rs41gbgw5x10.
SELECT ceq_msg_id, ceq_email_id, ceq_subject, ceq_msg_body,
CSQ_ATTACHMENT FROM cin_t_email_queue WHERE ceq_status = :"SYS_B_0"
and rownum< (select mdl_parameter_value from fds_m_module_param where
mod_1_mod_id=:"SYS_B_1" and mdl_parameter_id=:"SYS_B_2")
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "5rs41gbgw5x10" was executed 266 times and had
an average elapsed time of 3 seconds.
Rationale
Full scan of TABLE "INCMS.CIN_T_EMAIL_QUEUE" with object ID 83944
consumed 97% of the database time spent on this SQL statement.
Finding 2: Row Lock Waits
Impact is 4.17 active sessions, 41.7% of total activity.
--------------------------------------------------------
SQL statements were found waiting for row lock waits.
Recommendation 1: Application Analysis
Estimated benefit is 4.16 active sessions, 41.68% of total activity.
--------------------------------------------------------------------
Action
Significant row contention was detected in the TABLE
"INCMS.FDS_M_MODULE_PARAM" with object ID 84070. Trace the cause of row
contention in the application logic using the given blocked SQL.
Related Object
Database object with ID 84070.
Rationale
The SQL statement with SQL_ID "8wfzx3nyvma8m" was blocked on row locks.
Related Object
SQL statement with SQL_ID 8wfzx3nyvma8m.
SELECT TRIM(MDL_PARAMETER_VALUE) FROM FDS_M_MODULE_PARAM WHERE
MOD_1_MOD_ID ='PROVS' AND MDL_PARAMETER_ID =:B1 FOR UPDATE OF
FDS_M_MODULE_PARAM.MDL_PARAMETER_VALUE
Rationale
The session with ID 881 and serial number 40733 in instance number 1 was
the blocking session responsible for 57% of this recommendation's
benefit.
Rationale
The session with ID 94 and serial number 57631 in instance number 1 was
the blocking session responsible for 42% of this recommendation's
benefit.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Application" was consuming significant database time.
Impact is 4.25 active sessions, 42.58% of total activity.
Finding 3: Undersized SGA
Impact is .64 active sessions, 6.4% of total activity.
------------------------------------------------------
The SGA was inadequately sized, causing additional I/O or hard parses.
The value of parameter "sga_target" was "12288 M" during the analysis period.
Recommendation 1: Database Configuration
Estimated benefit is .32 active sessions, 3.22% of total activity.
------------------------------------------------------------------
Action
Increase the size of the SGA by setting the parameter "sga_target" to
13824 M.
Symptoms That Led to the Finding:
---------------------------------
Wait class "User I/O" was consuming significant database time.
Impact is 1.26 active sessions, 12.6% of total activity.
Finding 4: Top Segments by "User I/O" and "Cluster"
Impact is .19 active sessions, 1.91% of total activity.
-------------------------------------------------------
Individual database segments responsible for significant "User I/O" and
"Cluster" waits were found.
Recommendation 1: Segment Tuning
Estimated benefit is .19 active sessions, 1.91% of total activity.
------------------------------------------------------------------
Action
Run "Segment Advisor" on TABLE "INCMS.CIN_T_EMAIL_QUEUE" with object ID
83944.
Related Object
Database object with ID 83944.
Action
Investigate application logic involving I/O on TABLE
"INCMS.CIN_T_EMAIL_QUEUE" with object ID 83944.
Related Object
Database object with ID 83944.
Action
Look at the "Top SQL Statements" finding for SQL statements consuming
significant I/O on this segment. For example, the SELECT statement with
SQL_ID "5rs41gbgw5x10" is responsible for 100% of "User I/O" and
"Cluster" waits for this segment.
Rationale
The I/O usage statistics for the object are: 0 full object scans,
18116994 physical reads, 0 physical writes and 18116994 direct reads.
Symptoms That Led to the Finding:
---------------------------------
Wait class "User I/O" was consuming significant database time.
Impact is 1.26 active sessions, 12.6% of total activity.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
----------------------
Miscellaneous Information
-------------------------
Wait class "Commit" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
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.
Hard parsing of SQL statements was not consuming significant database time.
5: OS Watcher Installation Steps:-
OS Watcher is a series of shell scripts that collect specific kinds of data, using operating system diagnostic utilities. Control is passed to individually spawned operating system data collector processes, which in turn collect specific data, time stamp the data output, and append the data to pre-generated and named files. Each data collector will have its own file, created and named by the File Manager process. OSW invokes the distinct operating system utilities listed below as data collectors. OSW will not put any significant performance affecting load on the system. It will have the same impact as running the regular OS command like netstat, ps etc. These utilities will be supported, or their equivalents, as available for each supported target platform:
* ps
* top
* mpstat
* iostat
* netstat
* traceroute
* vmstat
The size of the archived files saved during the running of the OSW will be based on the user parameters set at the starting of OSW and the OS information. For example, if each file will contain an hour of data and the collection interval is 10 sec the amount of data will be bigger compared to collecting with an interval of 60 sec.
It is highly recommended that OSW be installed and run continuously on ALL cluster nodes, at all times Document 301137.1
Be sure to use separate directories per node for storing OSW output. When using OS Watcher in a RAC environment, each node must write its output files to a separate archive directory. Combining the output files under one archive (on shared storage) is not supported and causes the OSW tool to crash. Shared storage is fine, but each node needs a separate archive directory.
Document 301137.1 - OS Watcher User Guide
OSW for WINDOWS: OS Watcher for Windows is no longer supported. It has been replace by the Cluster Health Monitor. Please see Document ID: 736752.1 for more information, and how to download, the Cluster Health Monitor.
[oracle@ibmlab jb]$ pwd
/home/oracle/jb
[oracle@ibmlab jb]$ ls
oswbb733.tar
[oracle@ibmlab jb]$ tar -xvf oswbb733.tar
oswbb/
oswbb/docs/
oswbb/docs/The_Analyzer/
oswbb/docs/The_Analyzer/OSWatcherAnalyzerOverview.pdf
oswbb/docs/The_Analyzer/oswbbaUserGuide.pdf
oswbb/docs/The_Analyzer/oswbba_README.txt
oswbb/docs/OSWatcher/
oswbb/docs/OSWatcher/oswbb_README.txt
oswbb/docs/OSWatcher/OSWatcherUserGuide.pdf
oswbb/Exampleprivate.net
oswbb/nfssub.sh
oswbb/stopOSWbb.sh
oswbb/call_du.sh
oswbb/iosub.sh
oswbb/OSWatcherFM.sh
oswbb/ifconfigsub.sh
oswbb/ltop.sh
oswbb/mpsub.sh
oswbb/call_uptime.sh
oswbb/psmemsub.sh
oswbb/tar_up_partial_archive.sh
oswbb/oswnet.sh
oswbb/vmsub.sh
oswbb/call_sar.sh
oswbb/oswib.sh
oswbb/startOSWbb.sh
oswbb/Example_extras.txt
oswbb/oswsub.sh
oswbb/oswbba.jar
oswbb/OSWatcher.sh
oswbb/tarupfiles.sh
oswbb/xtop.sh
oswbb/src/
oswbb/src/Thumbs.db
oswbb/src/OSW_profile.htm
oswbb/src/tombody.gif
oswbb/src/missing_graphic.gif
oswbb/src/coe_logo.gif
oswbb/src/watch.gif
oswbb/src/oswbba_input.txt
oswbb/oswrds.sh
[oracle@ibmlab jb]$
Start OS watcher using the scripts as oracle user to capture data every 15 second:
nohup ./startOSWbb.sh 15 300 gzip &
[oracle@ibmlab oswbb]$ ps -ef |grep -i osw
oracle 21464 1 0 19:39 pts/1 00:00:00 /bin/sh ./OSWatcher.sh 15 300 gzip
oracle 21522 21464 0 19:39 pts/1 00:00:00 /bin/sh ./OSWatcherFM.sh 300 /home/oracle/jb/oswbb/archive
oracle 21943 21266 0 19:41 pts/1 00:00:00 grep -i osw
[oracle@ibmlab oswbb]$
To stop OSWatcher:
[oracle@ibmlab oswbb]$ ./stopOSWbb.sh
[oracle@ibmlab oswbb]$
To uninstall the OSWatche:
simply delete the oswatcher parent directory.
6) Three case of performance tuning which you worked:
Case 1.
2) Instance Efficiency Target (100%)
3) Top 5/10 Events
4) Time Mode Statistics
The first section displayed on the report shows a summary of the snapshot window for your report
as well as a brief look at the elapsed time, which represents the snapshot window, and the DB time,
which represents activity on your database. If the DB time exceeds the elapsed time, it denotes a busy
database. If it is a lot higher than the elapsed time, it may mean that some sessions are waiting for resources.
The instance efficiency section gives you a very quick view to determine if things are running adequately on your database. Generally, most percentages within this section should be above 90%. The Parse CPU to Parse Elapsd metric shows how much time the CPU is spending parsing SQL statements.
The third place to get a quick glance at your database performance is the Top 5 Timed Events
section. This section gives you a quick look at exactly where the highest amount of resources are being consumed within your database for the snapshot period. Based on these results, it may show you that
there is an inordinate amount of time spent performing full-table scans, or getting data across a network database link.
Below is one example which shows main point related to of awr report which are checked and recommend to application team.
IN Top Wait Event is coming enq:TX - row lock contention its totally based on one Query which was being fired from App side.
It is also forcing us to go for the SQL Ordered part as sql execute to elapsed time is high close to 99% of DB time was being spent on it.
Let go on the Drill Down part now based on the above observation:-
DB file parallel write wait event is totally related to I/O Problem , I will show the artifacts of the same below as well.
Common Causes and Actions
The db file parallel write latency is normally a symptom of a slow I/O subsystem or poor I/O configurations. This includes poor layout of database files, bad mount point to I/O controller ratio, wrong stripe size and/or RAID level, and not enough disks (i.e. there are a few high capacity disks versus many lower capacity disks).
The DBA needs to look at the average I/O time.
Please find the Query which is Creating a problem in the DB as its running with FOR Update clause also its coming high on the Elapsed Time as well as on the CPU Time.
So request you please check this Query and its logic with App Team.
Here is the Artifacts of I/O Problem , Please check the Av RD(ms) column .
In a good I/O system it should not go above the 20 but in our case it is going much beyond that request you to take care this part asap..
Files Snapshot :-
The Table used in the above Highlighted Query i.e Network_Stocks table is the main object creating the Row Locks Waits as well.
Backup was also running during the Problematic time :-
Also when I checked the System I observed that All Application Session were going on the Node 2 only but today its looking good.
4) ADDM Report:
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
addmrpt.sql Script
The addmrpt.sql script can be used to create an ADDM report from SQL*Plus. The script is called as follows.
-- UNIX
@/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/addmrpt.sql
-- Windows
@d:\oracle\product\10.1.0\db_1\rdbms\admin\addmrpt.sql
SQL> @?/rdbms/admin/addmrpt.sql
Below is one test case for addm:-
I found row lock wait was too high so down the line found problematic query as well and shared that query with application team to check from their and it was rectified by them.
addmrpt_1_22542_22543.txt
ADDM Report for Task 'TASK_33939'
---------------------------------
Analysis Period
---------------
AWR snapshot range from 22542 to 22543.
Time period starts at 06-JUN-16 04.00.06 PM
Time period ends at 06-JUN-16 05.00.12 PM
Analysis Target
---------------
Database 'DBNAME' with DB ID 577252600.
Database version 11.2.0.3.0.
ADDM performed an analysis of instance PACSMG, numbered 1 and hosted at
XXXXXXXX.
Activity During the Analysis Period
-----------------------------------
Total database time was 36014 seconds.
The average number of active sessions was 9.99.
Summary of Findings
-------------------
Description Active Sessions Recommendation
s
Percent of Activity
---------------------------------------- ------------------- --------------
-
1 Top SQL Statements 6.59 | 66.02 6
2 Row Lock Waits 4.17 | 41.7 1
3 Undersized SGA .64 | 6.4 1
4 Top Segments by "User I/O" and "Cluster" .19 | 1.91 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Findings and Recommendations
----------------------------
Finding 1: Top SQL Statements
Impact is 6.59 active sessions, 66.02% of total activity.
---------------------------------------------------------
SQL statements consuming significant database time were found. These
statements offer a good opportunity for performance improvement.
Recommendation 1: SQL Tuning
Estimated benefit is 4.46 active sessions, 44.68% of total activity.
--------------------------------------------------------------------
Action
Investigate the SELECT statement with SQL_ID "8wfzx3nyvma8m" for
possible performance improvements. You can supplement the information
given here with an ASH report for this SQL_ID.
Related Object
SQL statement with SQL_ID 8wfzx3nyvma8m.
SELECT TRIM(MDL_PARAMETER_VALUE) FROM FDS_M_MODULE_PARAM WHERE
MOD_1_MOD_ID ='PROVS' AND MDL_PARAMETER_ID =:B1 FOR UPDATE OF
FDS_M_MODULE_PARAM.MDL_PARAMETER_VALUE
Rationale
The SQL spent only 0% of its database time on CPU, I/O and Cluster
waits. Therefore, the SQL Tuning Advisor is not applicable in this case.
Look at performance data for the SQL to find potential improvements.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "8wfzx3nyvma8m" was executed 412 times and had
an average elapsed time of 36 seconds.
Rationale
Waiting for event "enq: TX - row lock contention" in wait class
"Application" accounted for 100% of the database time spent in
processing the SQL statement with SQL_ID "8wfzx3nyvma8m".
Recommendation 2: SQL Tuning
Estimated benefit is .54 active sessions, 5.41% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the UPSERT statement with SQL_ID
"672j4rmvvnmru".
Related Object
SQL statement with SQL_ID 672j4rmvvnmru.
MERGE INTO SIMSWAP_REPROCESS X USING ( SELECT SYSDATE CREATION_DATE,
B.SER_SERVICE_ORDER_ID, A.SER_MSISDN, A.SER_IMSI_NO,
A.SER_SERVICE_VALUE, B.SER_RECIEVED_DT_TIME, A.SER_ERROR, 'PENDING'
STATUS, NULL HLR_IMSI, NULL REPROCESS_DATE, D.SIM_SIM_NO NEW_SIM_NO
FROM INCMS.CIN_M_SIM D, INCMS.CIN_T_SERVICE_ORDER_DETAIL A,
INCMS.CIN_T_SERVICE_ORDER B, ( SELECT SER_MSISDN,
MAX(SER_RECIEVED_DT_TIME) SER_RECIEVED_DT_TIME FROM
INCMS.CIN_T_SERVICE_ORDER_DETAIL WHERE SER_RECIEVED_DT_TIME >
SYSDATE-3 AND SER_SERVICE_ID IN ('SIMCH','SMCHE') GROUP BY SER_MSISDN
) C WHERE B.SER_SERVICE_ORDER_ID = A.SER_SERVICE_ORDER_ID AND
D.SIM_IMSI_NO = SUBSTR(A.SER_SERVICE_VALUE,1,15) AND B.SER_OPERATION
= 'MODS' AND B.SER_PROVISIONING_TYPE <> 'BULK' AND
A.SER_RECIEVED_DT_TIME > SYSDATE-3 AND A.SER_SERVICE_ID IN
('SIMCH','SMCHE') AND A.SER_MSISDN = C.SER_MSISDN AND
A.SER_RECIEVED_DT_TIME = C.SER_RECIEVED_DT_TIME AND A.SER_STATUS =
'FAIL' AND (A.SER_ERROR LIKE 'Unknown Error:RESP:18310%' OR
A.SER_ERROR = 'Ext. system communication link failure.Please contact
switch people.' OR A.SER_ERROR = 'Unknown Error:RESP:18304 recieved
from EMA.Please Contact switch people' OR A.SER_ERROR LIKE '%fail
Fail File of Zero Size' OR A.SER_ERROR LIKE 'Unknown
Error:RESP:18398%' ) ) Y ON (X.SER_SERVICE_ORDER_ID =
Y.SER_SERVICE_ORDER_ID) WHEN NOT MATCHED THEN INSERT(X.CREATION_DATE,
X.SER_SERVICE_ORDER_ID, X.SER_MSISDN, X.SER_IMSI_NO,
X.SER_SERVICE_VALUE, X.SER_RECIEVED_DT_TIME, X.SER_ERROR, X.STATUS)
VALUES(SYSDATE, Y.SER_SERVICE_ORDER_ID, Y.SER_MSISDN, Y.SER_IMSI_NO,
Y.SER_SERVICE_VALUE, Y.SER_RECIEVED_DT_TIME, Y.SER_ERROR, 'PENDING')
WHEN MATCHED THEN UPDATE SET X.CREATION_DATE = SYSDATE, X.STATUS =
'PENDING'
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "672j4rmvvnmru" was executed 109 times and had
an average elapsed time of 17 seconds.
Rationale
Top level calls to execute the PL/SQL statement with SQL_ID
"g2j4rw7vpbcbm" are responsible for 100% of the database time spent on
the UPSERT statement with SQL_ID "672j4rmvvnmru".
Related Object
SQL statement with SQL_ID g2j4rw7vpbcbm.
BEGIN provisioning.simswap_reprocess; END;
Recommendation 3: SQL Tuning
Estimated benefit is .53 active sessions, 5.26% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"52us6knb8uw7a".
Related Object
SQL statement with SQL_ID 52us6knb8uw7a.
SELECT TO_CHAR("A1"."START_TIME",:"SYS_B_00"),TO_CHAR("A1"."START_TIM
E",:"SYS_B_01"),TO_CHAR("A1"."START_TIME",:"SYS_B_02"),"A1"."APN_NI",
CASE "A1"."SGSN_ADDRESS" WHEN :"SYS_B_03" THEN :"SYS_B_04" ELSE CASE
WHEN ("A1"."ORIGINAL_CALL_TYPE"=:"SYS_B_05" AND "A1"."SGSN_ADDRESS"
IS NULL) THEN :"SYS_B_06" ELSE :"SYS_B_07" END END
,COUNT(*),SUM(NVL("A1"."DATA_UP",:"SYS_B_08")),SUM(NVL("A1"."DATA_DOW
N",:"SYS_B_09")),SUM(NVL("A1"."DATA_UP",:"SYS_B_10"))+SUM(NVL("A1"."D
ATA_DOWN",:"SYS_B_11")),SUM(NVL("A1"."DATA_UP",:"SYS_B_12"))/:"SYS_B_
13"+SUM(NVL("A1"."DATA_DOWN",:"SYS_B_14"))/:"SYS_B_15",:"SYS_B_16",CA
SE "A1"."SGSN_ADDRESS" WHEN :"SYS_B_17" THEN :"SYS_B_18" ELSE CASE
WHEN ("A1"."ORIGINAL_CALL_TYPE"=:"SYS_B_19" AND "A1"."SGSN_ADDRESS"
IS NULL) THEN :"SYS_B_20" ELSE :"SYS_B_21" END END FROM
"INCMS"."HBST_RAT_UNBILD_CALL" "A1" WHERE
"A1"."SERVICE_ID"=:"SYS_B_22" AND
TO_NUMBER(TO_CHAR("A1"."START_TIME",:"SYS_B_23"))=:"SYS_B_24" GROUP
BY TO_CHAR("A1"."START_TIME",:"SYS_B_25"),TO_CHAR("A1"."START_TIME",:
"SYS_B_26"),TO_CHAR("A1"."START_TIME",:"SYS_B_27"),"A1"."APN_NI","A1"
."SGSN_ADDRESS","A1"."ORIGINAL_CALL_TYPE"
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "52us6knb8uw7a" was executed 4 times and had
an average elapsed time of 446 seconds.
Recommendation 4: SQL Tuning
Estimated benefit is .41 active sessions, 4.1% of total activity.
-----------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"cxwc6bnrmapfh".
Related Object
SQL statement with SQL_ID cxwc6bnrmapfh.
select b.ser_service_order_id||:"SYS_B_0"||a.ser_msisdn||:"SYS_B_1"||
a.ser_service_value||:"SYS_B_2"||a.ser_error
from incms.CIN_T_SERVICE_ORDER_DETAIL a, incms.CIN_T_SERVICE_ORDER b,
incms.cms_m_customer c
where b.SER_SERVICE_ORDER_ID = a.SER_SERVICE_ORDER_ID
and a.ser_msisdn = c.cus_tel_no
and b.ser_provisioning_type = :"SYS_B_3"
and b.ser_operation in (:"SYS_B_4")
and a.SER_RECIEVED_DT_TIME > sysdate-:"SYS_B_5"
and a.SER_SERVICE_ID in (:"SYS_B_6",:"SYS_B_7")
and a.ser_status = :"SYS_B_8"
order by b.ser_service_order_id
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "cxwc6bnrmapfh" was executed 81 times and had
an average elapsed time of 17 seconds.
Recommendation 5: SQL Tuning
Estimated benefit is .41 active sessions, 4.07% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"an6y0xhff8hsc".
Related Object
SQL statement with SQL_ID an6y0xhff8hsc.
select account_no,invoice_date,os,invoice_Date+:"SYS_B_0",floor(sysda
te-(invoice_date+:"SYS_B_1")),unadjusted_amount,add_months(invoice_da
te,-:"SYS_B_2") from hbst_dunning_os
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "an6y0xhff8hsc" was executed 54 times and had
an average elapsed time of 25 seconds.
Rationale
At least 2 distinct execution plans were utilized for this SQL statement
during the analysis period.
Recommendation 6: SQL Tuning
Estimated benefit is .25 active sessions, 2.5% of total activity.
-----------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"5rs41gbgw5x10".
Related Object
SQL statement with SQL_ID 5rs41gbgw5x10.
SELECT ceq_msg_id, ceq_email_id, ceq_subject, ceq_msg_body,
CSQ_ATTACHMENT FROM cin_t_email_queue WHERE ceq_status = :"SYS_B_0"
and rownum< (select mdl_parameter_value from fds_m_module_param where
mod_1_mod_id=:"SYS_B_1" and mdl_parameter_id=:"SYS_B_2")
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "5rs41gbgw5x10" was executed 266 times and had
an average elapsed time of 3 seconds.
Rationale
Full scan of TABLE "INCMS.CIN_T_EMAIL_QUEUE" with object ID 83944
consumed 97% of the database time spent on this SQL statement.
Finding 2: Row Lock Waits
Impact is 4.17 active sessions, 41.7% of total activity.
--------------------------------------------------------
SQL statements were found waiting for row lock waits.
Recommendation 1: Application Analysis
Estimated benefit is 4.16 active sessions, 41.68% of total activity.
--------------------------------------------------------------------
Action
Significant row contention was detected in the TABLE
"INCMS.FDS_M_MODULE_PARAM" with object ID 84070. Trace the cause of row
contention in the application logic using the given blocked SQL.
Related Object
Database object with ID 84070.
Rationale
The SQL statement with SQL_ID "8wfzx3nyvma8m" was blocked on row locks.
Related Object
SQL statement with SQL_ID 8wfzx3nyvma8m.
SELECT TRIM(MDL_PARAMETER_VALUE) FROM FDS_M_MODULE_PARAM WHERE
MOD_1_MOD_ID ='PROVS' AND MDL_PARAMETER_ID =:B1 FOR UPDATE OF
FDS_M_MODULE_PARAM.MDL_PARAMETER_VALUE
Rationale
The session with ID 881 and serial number 40733 in instance number 1 was
the blocking session responsible for 57% of this recommendation's
benefit.
Rationale
The session with ID 94 and serial number 57631 in instance number 1 was
the blocking session responsible for 42% of this recommendation's
benefit.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Application" was consuming significant database time.
Impact is 4.25 active sessions, 42.58% of total activity.
Finding 3: Undersized SGA
Impact is .64 active sessions, 6.4% of total activity.
------------------------------------------------------
The SGA was inadequately sized, causing additional I/O or hard parses.
The value of parameter "sga_target" was "12288 M" during the analysis period.
Recommendation 1: Database Configuration
Estimated benefit is .32 active sessions, 3.22% of total activity.
------------------------------------------------------------------
Action
Increase the size of the SGA by setting the parameter "sga_target" to
13824 M.
Symptoms That Led to the Finding:
---------------------------------
Wait class "User I/O" was consuming significant database time.
Impact is 1.26 active sessions, 12.6% of total activity.
Finding 4: Top Segments by "User I/O" and "Cluster"
Impact is .19 active sessions, 1.91% of total activity.
-------------------------------------------------------
Individual database segments responsible for significant "User I/O" and
"Cluster" waits were found.
Recommendation 1: Segment Tuning
Estimated benefit is .19 active sessions, 1.91% of total activity.
------------------------------------------------------------------
Action
Run "Segment Advisor" on TABLE "INCMS.CIN_T_EMAIL_QUEUE" with object ID
83944.
Related Object
Database object with ID 83944.
Action
Investigate application logic involving I/O on TABLE
"INCMS.CIN_T_EMAIL_QUEUE" with object ID 83944.
Related Object
Database object with ID 83944.
Action
Look at the "Top SQL Statements" finding for SQL statements consuming
significant I/O on this segment. For example, the SELECT statement with
SQL_ID "5rs41gbgw5x10" is responsible for 100% of "User I/O" and
"Cluster" waits for this segment.
Rationale
The I/O usage statistics for the object are: 0 full object scans,
18116994 physical reads, 0 physical writes and 18116994 direct reads.
Symptoms That Led to the Finding:
---------------------------------
Wait class "User I/O" was consuming significant database time.
Impact is 1.26 active sessions, 12.6% of total activity.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
----------------------
Miscellaneous Information
-------------------------
Wait class "Commit" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
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.
Hard parsing of SQL statements was not consuming significant database time.
5: OS Watcher Installation Steps:-
OS Watcher is a series of shell scripts that collect specific kinds of data, using operating system diagnostic utilities. Control is passed to individually spawned operating system data collector processes, which in turn collect specific data, time stamp the data output, and append the data to pre-generated and named files. Each data collector will have its own file, created and named by the File Manager process. OSW invokes the distinct operating system utilities listed below as data collectors. OSW will not put any significant performance affecting load on the system. It will have the same impact as running the regular OS command like netstat, ps etc. These utilities will be supported, or their equivalents, as available for each supported target platform:
* ps
* top
* mpstat
* iostat
* netstat
* traceroute
* vmstat
The size of the archived files saved during the running of the OSW will be based on the user parameters set at the starting of OSW and the OS information. For example, if each file will contain an hour of data and the collection interval is 10 sec the amount of data will be bigger compared to collecting with an interval of 60 sec.
It is highly recommended that OSW be installed and run continuously on ALL cluster nodes, at all times Document 301137.1
Be sure to use separate directories per node for storing OSW output. When using OS Watcher in a RAC environment, each node must write its output files to a separate archive directory. Combining the output files under one archive (on shared storage) is not supported and causes the OSW tool to crash. Shared storage is fine, but each node needs a separate archive directory.
Document 301137.1 - OS Watcher User Guide
OSW for WINDOWS: OS Watcher for Windows is no longer supported. It has been replace by the Cluster Health Monitor. Please see Document ID: 736752.1 for more information, and how to download, the Cluster Health Monitor.
[oracle@ibmlab jb]$ pwd
/home/oracle/jb
[oracle@ibmlab jb]$ ls
oswbb733.tar
[oracle@ibmlab jb]$ tar -xvf oswbb733.tar
oswbb/
oswbb/docs/
oswbb/docs/The_Analyzer/
oswbb/docs/The_Analyzer/OSWatcherAnalyzerOverview.pdf
oswbb/docs/The_Analyzer/oswbbaUserGuide.pdf
oswbb/docs/The_Analyzer/oswbba_README.txt
oswbb/docs/OSWatcher/
oswbb/docs/OSWatcher/oswbb_README.txt
oswbb/docs/OSWatcher/OSWatcherUserGuide.pdf
oswbb/Exampleprivate.net
oswbb/nfssub.sh
oswbb/stopOSWbb.sh
oswbb/call_du.sh
oswbb/iosub.sh
oswbb/OSWatcherFM.sh
oswbb/ifconfigsub.sh
oswbb/ltop.sh
oswbb/mpsub.sh
oswbb/call_uptime.sh
oswbb/psmemsub.sh
oswbb/tar_up_partial_archive.sh
oswbb/oswnet.sh
oswbb/vmsub.sh
oswbb/call_sar.sh
oswbb/oswib.sh
oswbb/startOSWbb.sh
oswbb/Example_extras.txt
oswbb/oswsub.sh
oswbb/oswbba.jar
oswbb/OSWatcher.sh
oswbb/tarupfiles.sh
oswbb/xtop.sh
oswbb/src/
oswbb/src/Thumbs.db
oswbb/src/OSW_profile.htm
oswbb/src/tombody.gif
oswbb/src/missing_graphic.gif
oswbb/src/coe_logo.gif
oswbb/src/watch.gif
oswbb/src/oswbba_input.txt
oswbb/oswrds.sh
[oracle@ibmlab jb]$
Start OS watcher using the scripts as oracle user to capture data every 15 second:
nohup ./startOSWbb.sh 15 300 gzip &
[oracle@ibmlab oswbb]$ ps -ef |grep -i osw
oracle 21464 1 0 19:39 pts/1 00:00:00 /bin/sh ./OSWatcher.sh 15 300 gzip
oracle 21522 21464 0 19:39 pts/1 00:00:00 /bin/sh ./OSWatcherFM.sh 300 /home/oracle/jb/oswbb/archive
oracle 21943 21266 0 19:41 pts/1 00:00:00 grep -i osw
[oracle@ibmlab oswbb]$
To stop OSWatcher:
[oracle@ibmlab oswbb]$ ./stopOSWbb.sh
[oracle@ibmlab oswbb]$
To uninstall the OSWatche:
simply delete the oswatcher parent directory.
6) Three case of performance tuning which you worked:
Case 1.
I got a call from the end user claiming that the user is not able to process the transaction. I have followed the below process to fix the issue.
I have logged on to the server and checked the user transaction information and status of the user and was looking for the locks on the database.
To show the information, I will take the scott schema as the example and explain the same. We need to log in to the database as two users, both would be modifying the same record which makes the lock on the record before it gets committed.
SQL> update emp set sal=1000 where deptno=10;
3 rows updated.
SQL> show user;
USER is "SCOTT"
SQL>
open the another session and try to modify the same record. Which will not complete the transaction as it's going on lock.
SQL> show user
USER is "SCOTT"
SQL> update emp set sal=1000 where deptno=10;
Use below query to find out who is blocking whoom.
SQL> select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2; 2 3 4 5
SID 'ISBLOCKING' SID
---------- ------------- ----------
37 IS BLOCKING 39
SQL>
Using below query we can find out the user info and the query which is blocking.
SQL> Select sid,program,action,username from v$session where sid in(37,39);
SID PROGRAM
---------- ------------------------------------------------
ACTION
----------------------------------------------------------------
USERNAME
------------------------------
37 sqlplus@ibmlab.localdomain (TNS V1-V3)
SCOTT
39 sqlplus@ibmlab.localdomain (TNS V1-V3)
SCOTT
SID PROGRAM
---------- ------------------------------------------------
ACTION
----------------------------------------------------------------
USERNAME
------------------------------
SQL> Select sql_text from v$sql s, v$session sess where s.sql_id=sess.sql_id and sess.sid=37;
no rows selected
SQL> Select sql_text from v$sql s, v$session sess where s.sql_id=sess.sql_id and sess.sid=39;
SQL_TEXT
--------------------------------------------------------------------------------
update emp set sal=1000 where deptno=10
SQL>
We can kill the 39 session to release the locking or we can request the user to go ahead and finish the commit. Most of the cases we need to kill the session after taking the approval from the customer. We will kill the blocking session by using session sid.
SQL> select sid,serial# from v$session where sid='37';
SID SERIAL#
---------- ----------
37 12144
SQL> alter system kill session '37,12144';
System altered.
SQL>
The movement we kill the session. Locks will be over the end user transaction will complete. Please see the below log of the end user session.
SQL> show user
USER is "SCOTT"
SQL> update emp set sal=1000 where deptno=10;
3 rows updated.
SQL>
Case 2
1) User complained about sudden poor performance of user job
Checked whether job was running or not
$ps -ef|grep -i udmd4090
oracle 44368126 43122766 0 15:41:48 pts/0 0:00 grep -i udmd4090
bprd001 10158450 1 0 15:40:40 - 0:00 /bin/ksh /prod/dmprod/jobs_exec/udmd4090.sh
and check database level performance issue and blocking session as well
--To check blocking session ---
SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
ORDER BY id1, request;
SQL> select count(1),event from v$session group by event;
COUNT(1) EVENT
---------- ----------------------------------------------------------------
10 PL/SQL lock timer
228 SQL*Net message from client
1 SQL*Net message from dblink
1 SQL*Net message to client
1 Streams AQ: qmn coordinator idle wait
1 Streams AQ: qmn slave idle wait
1 Streams AQ: waiting for time management or cleanup tasks
1 TCP Socket (KGAS)
1 db file scattered read
1 db file sequential read
2 pipe get
COUNT(1) EVENT
---------- ----------------------------------------------------------------
1 pmon timer
14 rdbms ipc message
1 smon timer
but did not found any major performance degradation database level
I have logged on to the server and checked the user transaction information and status of the user and was looking for the locks on the database.
To show the information, I will take the scott schema as the example and explain the same. We need to log in to the database as two users, both would be modifying the same record which makes the lock on the record before it gets committed.
SQL> update emp set sal=1000 where deptno=10;
3 rows updated.
SQL> show user;
USER is "SCOTT"
SQL>
open the another session and try to modify the same record. Which will not complete the transaction as it's going on lock.
SQL> show user
USER is "SCOTT"
SQL> update emp set sal=1000 where deptno=10;
Use below query to find out who is blocking whoom.
SQL> select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2; 2 3 4 5
SID 'ISBLOCKING' SID
---------- ------------- ----------
37 IS BLOCKING 39
SQL>
Using below query we can find out the user info and the query which is blocking.
SQL> Select sid,program,action,username from v$session where sid in(37,39);
SID PROGRAM
---------- ------------------------------------------------
ACTION
----------------------------------------------------------------
USERNAME
------------------------------
37 sqlplus@ibmlab.localdomain (TNS V1-V3)
SCOTT
39 sqlplus@ibmlab.localdomain (TNS V1-V3)
SCOTT
SID PROGRAM
---------- ------------------------------------------------
ACTION
----------------------------------------------------------------
USERNAME
------------------------------
SQL> Select sql_text from v$sql s, v$session sess where s.sql_id=sess.sql_id and sess.sid=37;
no rows selected
SQL> Select sql_text from v$sql s, v$session sess where s.sql_id=sess.sql_id and sess.sid=39;
SQL_TEXT
--------------------------------------------------------------------------------
update emp set sal=1000 where deptno=10
SQL>
We can kill the 39 session to release the locking or we can request the user to go ahead and finish the commit. Most of the cases we need to kill the session after taking the approval from the customer. We will kill the blocking session by using session sid.
SQL> select sid,serial# from v$session where sid='37';
SID SERIAL#
---------- ----------
37 12144
SQL> alter system kill session '37,12144';
System altered.
SQL>
The movement we kill the session. Locks will be over the end user transaction will complete. Please see the below log of the end user session.
SQL> show user
USER is "SCOTT"
SQL> update emp set sal=1000 where deptno=10;
3 rows updated.
SQL>
Case 2
1) User complained about sudden poor performance of user job
Checked whether job was running or not
$ps -ef|grep -i udmd4090
oracle 44368126 43122766 0 15:41:48 pts/0 0:00 grep -i udmd4090
bprd001 10158450 1 0 15:40:40 - 0:00 /bin/ksh /prod/dmprod/jobs_exec/udmd4090.sh
and check database level performance issue and blocking session as well
--To check blocking session ---
SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
ORDER BY id1, request;
SQL> select count(1),event from v$session group by event;
COUNT(1) EVENT
---------- ----------------------------------------------------------------
10 PL/SQL lock timer
228 SQL*Net message from client
1 SQL*Net message from dblink
1 SQL*Net message to client
1 Streams AQ: qmn coordinator idle wait
1 Streams AQ: qmn slave idle wait
1 Streams AQ: waiting for time management or cleanup tasks
1 TCP Socket (KGAS)
1 db file scattered read
1 db file sequential read
2 pipe get
COUNT(1) EVENT
---------- ----------------------------------------------------------------
1 pmon timer
14 rdbms ipc message
1 smon timer
but did not found any major performance degradation database level
Therefore for further analysis ,check from GRID and generated AWR and ASH and ADDM report for same time period when performance issue reported an
Grid output
Output from AWR
Output from ADDM report
SQLT recommendation
upon analysis ,We observed the creating and setting profile will help optimizer to choose correct plan and generated SQLT report as below
CREATE INDEX ATP_DBA.GA_DE_ST_IN_02 ON ATP_DBA.GAZ_DESTINATION_STATION
(FROM_AREA||FROM_DISTRICT||from_sector||FROM_STREET)
TABLESPACE TSPACEI01;
CREATE INDEX ATP_DBA.GA_DE_ST_IN_03 ON ATP_DBA.GAZ_DESTINATION_STATION
(TO_AREA||TO_DISTRICT||to_sector||TO_STREET)
TABLESPACE TSPACEI01;
Third case:
The Below Script is causing database performance degradation. Please see the same sqlid 4p0xrmd31t34c is running by multiple session. Those session is coming in Top session in GLANCE output
$ ps -ef|grep 1855
oracle 8093 27204 0 16:09:26 pts/7 0:00 grep 1855
oracle 1855 1 255 15:48:08 ? 17:12 ora_p010_PRDB0073
$ ps -ef|grep 23569
oracle 23569 1 252 15:24:21 ? 9:47 ora_p001_PRDB0073
oracle 8134 27204 0 16:09:52 pts/7 0:00 grep 23569
$ ps -ef|grep 1859
oracle 8510 27204 0 16:10:31 pts/7 0:00 grep 1859
oracle 1859 1 255 15:48:08 ? 18:11 ora_p012_PRDB0073
$ ps -ef|grep 1847
oracle 1847 1 16 15:48:08 ? 3:17 ora_p006_PRDB0073
oracle 8521 27204 0 16:10:39 pts/7 0:00 grep 1847
$ ps -ef|grep 1851
oracle 1851 1 254 15:48:08 ? 6:29 ora_p008_PRDB0073
oracle 8540 27204 0 16:10:49 pts/7 0:00 grep 1851
$ ps -ef|grep 1865
oracle 8599 27204 1 16:11:09 pts/7 0:00 grep 1865
oracle 1865 1 0 15:48:08 ? 10:12 ora_p015_PRDB0073
$
Enter value for 1: 1855
old 2: where p.addr=s.paddr and p.spid=&1
new 2: where p.addr=s.paddr and p.spid=1855
SQL_ID SQL_HASH_VALUE PREV_HASH_VALUE SID SERIAL# USERNAME EVENT TO_CHAR(S.LOGON_TIME,
------------- -------------- --------------- ---------- ---------- ------------------------------ ---------------------------------------------------------------- ---------------------
4p0xrmd31t34c 1176276108 0 145 18870 WHS_VIEWER direct path write temp 23-may:15:48
SQL> /
Enter value for 1: 23569
old 2: where p.addr=s.paddr and p.spid=&1
new 2: where p.addr=s.paddr and p.spid=23569
SQL_ID SQL_HASH_VALUE PREV_HASH_VALUE SID SERIAL# USERNAME EVENT TO_CHAR(S.LOGON_TIME,
------------- -------------- --------------- ---------- ---------- ------------------------------ ---------------------------------------------------------------- ---------------------
4p0xrmd31t34c 1176276108 0 301 17086 WHS_VIEWER PX Deq: Table Q Normal 23-may:15:48
SQL> /
Enter value for 1: 23573
old 2: where p.addr=s.paddr and p.spid=&1
new 2: where p.addr=s.paddr and p.spid=23573
SQL_ID SQL_HASH_VALUE PREV_HASH_VALUE SID SERIAL# USERNAME EVENT TO_CHAR(S.LOGON_TIME,
------------- -------------- --------------- ---------- ---------- ------------------------------ ---------------------------------------------------------------- ---------------------
4p0xrmd31t34c 1176276108 0 245 1984 WHS_VIEWER PX Deq: Table Q Normal 23-may:15:48
SQL> /
Enter value for 1: 1859
old 2: where p.addr=s.paddr and p.spid=&1
new 2: where p.addr=s.paddr and p.spid=1859
SQL_ID SQL_HASH_VALUE PREV_HASH_VALUE SID SERIAL# USERNAME EVENT TO_CHAR(S.LOGON_TIME,
------------- -------------- --------------- ---------- ---------- ------------------------------ ---------------------------------------------------------------- ---------------------
4p0xrmd31t34c 1176276108 0 227 42381 WHS_VIEWER direct path read temp 23-may:15:48
SQL> /
Enter value for 1: 1847
old 2: where p.addr=s.paddr and p.spid=&1
new 2: where p.addr=s.paddr and p.spid=1847
SQL_ID SQL_HASH_VALUE PREV_HASH_VALUE SID SERIAL# USERNAME EVENT TO_CHAR(S.LOGON_TIME,
------------- -------------- --------------- ---------- ---------- ------------------------------ ---------------------------------------------------------------- ---------------------
4p0xrmd31t34c 1176276108 0 248 8364 WHS_VIEWER PX Deq: Table Q Normal 23-may:15:48
SQL> /
Enter value for 1: 1851
old 2: where p.addr=s.paddr and p.spid=&1
new 2: where p.addr=s.paddr and p.spid=1851
SQL_ID SQL_HASH_VALUE PREV_HASH_VALUE SID SERIAL# USERNAME EVENT TO_CHAR(S.LOGON_TIME,
------------- -------------- --------------- ---------- ---------- ------------------------------ ---------------------------------------------------------------- ---------------------
4p0xrmd31t34c 1176276108 0 273 7054 WHS_VIEWER direct path write temp 23-may:15:48
SQL> /
Enter value for 1: 1865
old 2: where p.addr=s.paddr and p.spid=&1
new 2: where p.addr=s.paddr and p.spid=1865
SQL_ID SQL_HASH_VALUE PREV_HASH_VALUE SID SERIAL# USERNAME EVENT TO_CHAR(S.LOGON_TIME,
------------- -------------- --------------- ---------- ---------- ------------------------------ ---------------------------------------------------------------- ---------------------
4p0xrmd31t34c 1176276108 0 406 26464 WHS_VIEWER PX Deq: Table Q Normal 23-may:15:48
Complete Query
ADDM Recommendation
DETAILED ADDM REPORT FOR TASK 'TASK_8538' WITH ID 8538
------------------------------------------------------
Analysis Period: 23-MAY-2012 from 15:00:29 to 16:00:45
Database ID/Instance: 2524491379/1
Database/Instance Names: PRDB0073/PRDB0073
Host Name: itradwp1
Database Version: 10.2.0.4.0
Snapshot Range: from 9435 to 9436
Database Time: 47551 seconds
Average Database Load: 13.2 active sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FINDING 1: 63% impact (30173 seconds)
-------------------------------------
SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 33% benefit (15839 seconds)
ACTION: Investigate the SQL statement with SQL_ID "4p0xrmd31t34c" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID 4p0xrmd31t34c and
PLAN_HASH 2824768675
WITH "DIM_PROD_MV9" AS (SELECT "DIM_PROD_MV"."BE_ID" "BE_ID",
"DIM_PROD_MV"."ITM_OID" "ITM_OID",
"DIM_PROD_MV"."FG_TYP_NM" "FG_TYP_NM",
"DIM_PROD_MV"."FG_TYP_CD" "FG_TYP_CD",
"DIM_PROD_MV"."FG_CD" "FG_CD"
FROM "WHSUSR"."DIM_PROD_MV" "DIM_PROD_MV"
WHERE "DIM_PROD_MV"."END_DATE" > SYSDATE),
"DIM_CUST_MV10"
AS (SELECT "DIM_CUST_MV"."OBJECT_ID" "OBJECT_ID",
"DIM_CUST_MV"."CUST_CD" "CUST_CD",
"DIM_CUST_MV"."CUST_NM" "CUST_NM",
"DIM_CUST_MV"."CUST_ADDR_LN1_TXT" "CUST_ADDR_LN1_TXT",
"DIM_CUST_MV"."CUST_ADDR_LN2_TXT" "CUST_ADDR_LN2_TXT",
"DIM_CUST_MV"."CUST_ADDR_LN3_TXT" "CUST_ADDR_LN3_TXT",
"DIM_CUST_MV"."CUST_EMAIL_ADDR_TXT" "CUST_EMAIL_ADDR_TXT",
"DIM_CUST_MV"."CUST_CITY_NM" "CUST_CITY_NM",
"DIM_CUST_MV"."CUST_STT_CD" "CUST_STT_CD",
"DIM_CUST_MV"."CUST_TEL_NO" "CUST_TEL_NO",
"DIM_CUST_MV"."CUST_ZIP_CD" "CUST_ZIP_CD",
"DIM_CUST_MV"."COT_CD" "COT_CD",
"DIM_CUST_MV"."COT_NM" "COT_NM"
FROM "WHSUSR"."DIM_CUST_MV" "DIM_CUST_MV"
WHERE "DIM_CUST_MV"."CURRENT_FLG" = 'Y'),
"DIM_TIME_MV"
AS (SELECT "DIM_TIME_MV"."BE_ID" "BE_ID",
"DIM_TIME_MV"."DAY_STRT_PRD_OF_TM" "DAY_STRT_PRD_OF_TM"
FROM "WHSUSR"."DIM_TM_MV" "DIM_TIME_MV"
WHERE "DIM_TIME_MV"."END_DATE" > SYSDATE),
"EASI_Sales"
AS (SELECT "FACT_EASI_SLS_LINE_STD"."FY_YR_MO_CD" "FY_YR_MO_CD",
"FACT_EASI_SLS_LINE_STD"."FY_YR_MO_CD" "YR_MTH_CD",
"FACT_EASI_SLS_LINE_STD"."CUST_ACCT_BE_ID"
"CUST_ACCT_BE_ID",
"DIM_PROD_MV9"."FG_CD" "FG_CD",
"DIM_CUST_MV10"."CUST_CD" "CUST_CD",
"DIM_CUST_MV10"."CUST_NM" "CUST_NM"
FROM "TRANSDATA"."FACT_EASI_SLS_LINE_STD" "FACT_EASI_SLS_LINE_STD",
"DIM_PROD_MV9",
"DIM_CUST_MV10",
"DIM_TIME_MV"
WHERE "DIM_TIME_MV"."BE_ID" =
"FACT_EASI_SLS_LINE_STD"."SLS_POSTD_DT_BE_ID"
AND "DIM_CUST_MV10"."OBJECT_ID" =
"FACT_EASI_SLS_LINE_STD"."CUST_ACCT_BE_ID"
AND "DIM_PROD_MV9"."BE_ID" =
"FACT_EASI_SLS_LINE_STD"."SHIP_PROD_BE_ID"),
"DIM_PROD_MV12" AS (SELECT "DIM_PROD_MV"."BE_ID" "BE_ID",
"DIM_PROD_MV"."ITM_OID" "ITM_OID",
"DIM_PROD_MV"."FG_TYP_NM" "FG_TYP_NM",
"DIM_PROD_MV"."FG_TYP_CD" "FG_TYP_CD",
"DIM_PROD_MV"."FG_CD" "FG_CD"
FROM "WHSUSR"."DIM_PROD_MV" "DIM_PROD_MV"
WHERE "DIM_PROD_MV"."END_DATE" > SYSDATE),
"DIM_CUST_MV13"
AS (SELECT "DIM_CUST_MV"."OBJECT_ID" "OBJECT_ID",
"DIM_CUST_MV"."CUST_CD" "CUST_CD",
"DIM_CUST_MV"."CUST_NM" "CUST_NM",
"DIM_CUST_MV"."CUST_ADDR_LN1_TXT" "CUST_ADDR_LN1_TXT",
"DIM_CUST_MV"."CUST_ADDR_LN2_TXT" "CUST_ADDR_LN2_TXT",
"DIM_CUST_MV"."CUST_ADDR_LN3_TXT" "CUST_ADDR_LN3_TXT",
"DIM_CUST_MV"."CUST_EMAIL_ADDR_TXT" "CUST_EMAIL_ADDR_TXT",
"DIM_CUST_MV"."CUST_CITY_NM" "CUST_CITY_NM",
"DIM_CUST_MV"."CUST_STT_CD" "CUST_STT_CD",
"DIM_CUST_MV"."CUST_TEL_NO" "CUST_TEL_NO",
"DIM_CUST_MV"."CUST_ZIP_CD" "CUST_ZIP_CD",
"DIM_CUST_MV"."COT_CD" "COT_CD",
"DIM_CUST_MV"."COT_NM" "COT_NM"
FROM "WHSUSR"."DIM_CUST_MV" "DIM_CUST_MV"
WHERE "DIM_CUST_MV"."CURRENT_FLG" = 'Y'),
"Consumer_Sales"
AS (SELECT "FACT_CNSMR_SLS_STD"."FISC_MO_CD" "FISC_MO_CD",
"FACT_CNSMR_SLS_STD"."DISTR_BE_ID" "DISTR_BE_ID",
"DIM_PROD_MV12"."FG_CD" "FG_CD",
"DIM_PROD_MV12"."FG_CD" "KIT_CD",
"DIM_CUST_MV13"."CUST_CD" "CUST_CD",
"DIM_CUST_MV13"."CUST_NM" "CUST_NM",
"DIM_CUST_MV13"."CUST_ADDR_LN1_TXT" "CUST_ADDR_LN1_TXT",
"DIM_CUST_MV13"."CUST_CITY_NM" "CUST_CITY_NM",
"DIM_CUST_MV13"."CUST_STT_CD" "CUST_STT_CD",
"DIM_CUST_MV13"."CUST_ZIP_CD" "CUST_ZIP_CD",
"DIM_CUST_MV13"."COT_CD" "COT_CD",
"DIM_CUST_MV13"."COT_NM" "COT_NM",
"FACT_CNSMR_SLS_STD"."SS_QTY" "SS_QTY"
FROM "DIM_PROD_MV12"
LEFT OUTER JOIN
( "DIM_CUST_MV13"
INNER JOIN
"TRANSDATA"."FACT_CNSMR_SLS_STD" "FACT_CNSMR_SLS_STD"
ON "DIM_CUST_MV13"."OBJECT_ID" =
"FACT_CNSMR_SLS_STD"."CUST_BE_ID")
ON "DIM_PROD_MV12"."BE_ID" =
"FACT_CNSMR_SLS_STD"."KIT_BE_ID")
SELECT DISTINCT SUBSTR ("EASI_Sales"."YR_MTH_CD", 1, 6)
"E_YR_MTH_CD",
"EASI_Sales"."CUST_ACCT_BE_ID" "E_CUST_ACCT_BE_ID",
"EASI_Sales"."FG_CD" "E_FG_CD",
"EASI_Sales"."CUST_NM" "E_CUST_NM",
"Consumer_Sales"."FISC_MO_CD" "C_FISC_MO_CD",
"Consumer_Sales"."DISTR_BE_ID" "C_DISTR_BE_ID",
"Consumer_Sales"."FG_CD" "C_FG_CD",
"Consumer_Sales"."KIT_CD" "KIT_CD",
"Consumer_Sales"."CUST_NM" "C_CUST_NM",
"Consumer_Sales"."CUST_ADDR_LN1_TXT" "CUST_ADDR_LN1_TXT",
"Consumer_Sales"."CUST_CITY_NM" "CUST_CITY_NM",
"Consumer_Sales"."CUST_STT_CD" "CUST_STT_CD",
"Consumer_Sales"."CUST_ZIP_CD" "CUST_ZIP_CD",
"Consumer_Sales"."COT_CD" "COT_CD",
"Consumer_Sales"."COT_NM" "COT_NM",
"EASI_Sales"."FY_YR_MO_CD" "E_FY_YR_MO_CD",
'DIST. RPTD' "GROUP17",
"Consumer_Sales"."CUST_CD" "C_CUST_CD",
"EASI_Sales"."CUST_CD" "E_CUST_CD",
"Consumer_Sales"."SS_QTY" "SS_QTY"
FROM "EASI_Sales", "Consumer_Sales"
WHERE "EASI_Sales"."FY_YR_MO_CD" >= SUBSTR ('01012011', 1, 6)
AND "EASI_Sales"."YR_MTH_CD" <= "Consumer_Sales"."FISC_MO_CD"
AND ("EASI_Sales"."FG_CD" = "Consumer_Sales"."FG_CD"
OR "EASI_Sales"."FG_CD" = "Consumer_Sales"."KIT_CD")
AND "EASI_Sales"."CUST_ACCT_BE_ID" = "Consumer_Sales"."DISTR_BE_ID"
RATIONALE: SQL statement with SQL_ID "4p0xrmd31t34c" was executed 1
times and had an average elapsed time of 15838 seconds.
RATIONALE: Waiting for event "PX Deq Credit: send blkd" in wait class
"Other" accounted for 65% of the database time spent in processing
the SQL statement with SQL_ID "4p0xrmd31t34c".
Detail for Selected 5 Minute Interval
Start TimeNov 6, 2014 8:29:35 AM GMT
Top SQL
Actions [Go]
Select All | Select None
Select Activity (%) [Sorted in descending order] SQL ID SQL Type
[CPU (10.24%)] [Concurrency (4.01%)] [Other (.04%)] 14.29 57w71dgk5qbtx SELECT
[Concurrency (14.04%)] [Other (.06%)] [CPU (.00%)] 14.11 459f3z9u4fb3u SELECT
[Concurrency (3.65%)] [Other (.05%)] [CPU (.02%)] 3.72 3cj240n6v9znj PL/SQL EXECUTE
[Network (1.97%)] [CPU (.04%)] [User I/O (.00%)] 2.02 3h6gj58wp9u1y PL/SQL EXECUTE
[User I/O (1.02%)] [Concurrency (.42%)] [Other (.05%)] [CPU (.02%)] 1.51 bzhtzu2yf68jb SELECT
select * from table(dbms_xplan.display_cursor('&sql_id',null,'AdVanced ALLSTATS LAST'));
select * from table(dbms_xplan.display_awr('&sql_id',null,null,'advanced'));
3 - filter(("KGLHDNSP"=7 AND "KGLNAOBJ" LIKE 'ORA$ALERT$%' AND BITAND("KGLHDFLG",128)<>0))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) STRDEF[990]
2 - STRDEF[990]
3 - "KGLNAOBJ"[VARCHAR2,1000], "KGLHDNSP"[NUMBER,22], "KGLHDFLG"[NUMBER,22]
4 - "SID"[VARCHAR2,30]
Note
-----
- cardinality feedback used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
SQL_ID 57w71dgk5qbtx, child number 7
-------------------------------------
SELECT DISTINCT SUBSTR(KGLNAOBJ,11) SID FROM X$KGLOB WHERE KGLHDNSP = 7
AND KGLNAOBJ LIKE 'ORA$ALERT$%' AND BITAND(KGLHDFLG,128)!=0 UNION
SELECT DISTINCT SID FROM DBMS_ALERT_INFO
Plan hash value: 2001468060
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| | | | |
| 1 | SORT UNIQUE | | 183 | 2437 | 9 (45)| 00:00:01 | 43008 | 43008 |38912 (0)|
| 2 | UNION-ALL | | | | | | | | |
|* 3 | FIXED TABLE FULL | X$KGLOB | 1 | 71 | 2 (100)| 00:00:01 | | | |
| 4 | INDEX FAST FULL SCAN| SYS_C005421344 | 436 | 5668 | 5 (0)| 00:00:01 | | | |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$1
3 - SEL$1 / X$KGLOB@SEL$1
4 - SEL$2 / DBMS_ALERT_INFO@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SET$1")
INDEX_FFS(@"SEL$2" "DBMS_ALERT_INFO"@"SEL$2" ("DBMS_ALERT_INFO"."NAME" "DBMS_ALERT_INFO"."SID"))
FULL(@"SEL$1" "X$KGLOB"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("KGLHDNSP"=7 AND "KGLNAOBJ" LIKE 'ORA$ALERT$%' AND BITAND("KGLHDFLG",128)<>0))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) STRDEF[990]
2 - STRDEF[990]
3 - "KGLNAOBJ"[VARCHAR2,1000], "KGLHDNSP"[NUMBER,22], "KGLHDFLG"[NUMBER,22]
4 - "SID"[VARCHAR2,30]
Note
-----
- cardinality feedback used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
SQL_ID 57w71dgk5qbtx, child number 8
-------------------------------------
SELECT DISTINCT SUBSTR(KGLNAOBJ,11) SID FROM X$KGLOB WHERE KGLHDNSP = 7
AND KGLNAOBJ LIKE 'ORA$ALERT$%' AND BITAND(KGLHDFLG,128)!=0 UNION
SELECT DISTINCT SID FROM DBMS_ALERT_INFO
Plan hash value: 2001468060
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| | | | |
| 1 | SORT UNIQUE | | 183 | 2437 | 9 (45)| 00:00:01 | 43008 | 43008 |38912 (0)|
| 2 | UNION-ALL | | | | | | | | |
|* 3 | FIXED TABLE FULL | X$KGLOB | 1 | 71 | 2 (100)| 00:00:01 | | | |
| 4 | INDEX FAST FULL SCAN| SYS_C005421344 | 436 | 5668 | 5 (0)| 00:00:01 | | | |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$1
3 - SEL$1 / X$KGLOB@SEL$1
4 - SEL$2 / DBMS_ALERT_INFO@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SET$1")
INDEX_FFS(@"SEL$2" "DBMS_ALERT_INFO"@"SEL$2" ("DBMS_ALERT_INFO"."NAME" "DBMS_ALERT_INFO"."SID"))
FULL(@"SEL$1" "X$KGLOB"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
7) Various option of sql tracing & tkprof
There are several tracing methods available, Oracle now recommends that you use the
DBMS_MONITOR package for most types of tracing. TKPROF is a utility that lets you format any extended trace files that you generate with the event 10046 or
through the DBMS_MONITOR package.
In an Oracle 11.1or higher release, you can use the enhanced SQL tracing interface to trace one or more
SQL statements. Here are the steps to tracing a set of SQL statements.
1.
Issue the alter session set events statement, as shown here, to set up the
trace.
SQL> alter session set events 'sql_trace level 12';
Session altered.
SQL>
2.
Execute the SQL statements.
SQL> select count(*) from sales;
3.
Set tracing off.
SQL> alter session set events 'sql_trace off';
Session altered.
SQL>
Normal users can use the DBMS_SESSION package to trace their sessions, as shown in this example:
SQL>execute dbms_session.session_trace_enable(waits=>true, binds=> false);
To disable tracing, the user must execute the session_trace_disable procedure, as shown here:
SQL> execute dbms_session.session_trace_disable();
-- In SQL*Plus, obtain Data Pump process info:
CONNECT / as sysdba
set lines 150 pages 100 numwidth 7
col program for a38
col username for a10
col spid for a7
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid,
s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
from v$session s, v$process p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr;
SQL> conn / as sysdba
SQL> select s.sid,p.spid "ospid" , p.pid "orapid"
from v$process p, v$session s
where p.addr = s.paddr
and s.sid in (<sid_for_worker_process>,<sid_for_master_process>);
-- Get SQL_TRACE Worker process with level 8:
oradebug setospid <ospid_for_worker>
oradebug unlimit
oradebug event 10046 trace name context forever, level 8
oradebug tracefile_name --> trace file to be uploaded
-- Get SQL_TRACE Master process with level 8:
oradebug setospid <ospid_for_master>
oradebug unlimit
oradebug event 10046 trace name context forever, level 8
oradebug tracefile_name --> trace file to be uploaded
-- To stop the tracing:
oradebug event 10046 trace name context off
2)Get the tkprof formatted output
tkprof trcfile outfile waits=y sort=exeela
exec dbms_support.start_trace_in_session (4361,2072,binds=>true,waits=>true);
exec dbms_support.stop_trace_in_session (4361,2072);
exec dbms_system.set_sql_trace_in_session(1279,62705,true);
EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);
You want to examine a raw SQL trace file.
Solution
Open the trace file in a text editor to inspect the tracing information. Here are portions of a raw SQL
trace generated by executing the dbms_monitor.session_trace_enable procedure:
PARSING IN CURSOR #3 len=490 dep=1 uid=85 oct=3 lid=85 tim=269523043683 hv=672110367
ad='7ff18986250' sqlid='bqasjasn0z5sz'
PARSE #3:c=0,e=647,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=269523043680
EXEC #3:c=0,e=1749,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=3969568374,tim=269523045613
WAIT #3: nam='Disk file operations I/O' ela= 15833 FileOperation=2 fileno=4 filetype=2 obj#=-1
tim=269523061555
FETCH #3:c=0,e=19196,p=0,cr=46,cu=0,mis=0,r=1,dep=1,og=1,plh=3969568374,tim=269523064866
STAT #3 id=3 cnt=12 pid=2 pos=1 obj=0 op='HASH GROUP BY (cr=46 pr=0 pw=0 time=11 us cost=4
size=5317 card=409)'
STAT #3 id=4 cnt=3424 pid=3 pos=1 obj=89079 op='TABLE ACCESS FULL DEPT (cr=16 pr=0 pw=0
time=246 us cost=3 size=4251 card=327)'
As you can see from this excerpt of the raw trace file, you can glean useful information, such as
parse misses, waits, an
Formatting Trace Files with TKPROF
tkprof user_sql_001.trc user1.prf explain=hr/hr table=hr.temp_plan_table_a sys=no
sort=exeela,prsela,fchela
In the example shown here, the tkprof command takes the user_sql_001.trc trace file as input and
generates an output file named user1.prf. The “How it Works” section of this recipe explains key
optional arguments of the TKPROF utility.
tkprof rcc1_ora_3695308.trc rcc1_ora_3695308.prf SYS=NO SORT= EXECPU,FCHCPU
or
tkprof rcc2_ora_1294546.trc thirdnewelapsechela.txt waits=yes sys=no sort=exeela,fchela explain='system/'
TKPROF: Release 10.2.0.5.0 - Production on Tue May 10 11:40:37 2016
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Trace file: dmsprd_ora_58851386.trc
Sort options: execpu fchcpu
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
COMMIT
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 3994 0.77 9.98 0 0 3994 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3994 0.77 9.98 0 0 3994 0
Misses in library cache during parse: 0
Tracing Parallel Queries in a RAC System
Finding the trace files for the server (or thread or slave) processes is sometimes difficult in a RAC
environment, because you aren’t sure on which node or node(s) the database has created the trace files.
Here are the steps to follow to make it easier to find the trace files on the different nodes.
1. Set the px_trace with an alter session command, to help identify the trace
files, as shown here:
SQL> alter session set tracefile_identifier='10046';
SQL> alter session set "_px_trace" = low , messaging;
SQL> alter session set events '10046 trace name context forever,level 12';
2. Execute your parallel query.
SQL> alter table bigsales (parallel 4);
SQL> select count(*) from bigsales;
3. Turn all tracing off.
SQL> alter session set events '10046 trace name context off';
SQL> alter session set "_px_trace" = none;
Specifying px_trace will cause the query coordinator’s trace file to include information about the
slave processes that are part of the query, and the instance each slave process belongs to. You can then
retrieve the trace files from the instances listed in the query coordinator’s trace file.
Tracing Multiple Sessions
You can trace multiple sessions that belong to a user by using the client_id_trace_enable procedure
from the DBMS_MONITOR package. Before you can execute the dbms_monitor.client_id_trace_enable
procedure, you must set the client_identifier for the session by using the DBMS_SESSION package, as
shown here:
SQL> execute dbms_session.set_identifier('SH')
Once you set the client identifier as shown here, the client_identifier column in the V$SESSION
view is populated. You can confirm the value of the client_identifier column by executing the
following statement:
SQL> select sid, serial#,username from v$session where client_identifier='SH';
Now you can execute the dbms_monitor.client_id_trace_enable procedure:
SQL> execute dbms_monitor.client_id_trace_enable(client_id=>'SH', waits=>true, binds=>false);
You can disable the trace with the following command:
SQL> execute dbms_monitor.client_id_trace_disable(client_id=>'SH');
[oracle@ibmlab trace]$
8) Various option for explain plan generation:
The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to run the statement.
The row source tree is the core of the execution plan. It shows the following information:
An ordering of the tables referenced by the statement
An access method for each table mentioned in the statement
A join method for tables affected by join operations in the statement
Data operations like filter, sort, or aggregation
In addition to the row source tree, the plan table contains information about the following:
Optimization, such as the cost and cardinality of each operation
Partitioning, such as the set of accessed partitions
Parallel execution, such as the distribution method of join inputs
The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query.
1. EXPLAIN PLAN command
2. V$SQL_PLAN
3. Automatic Workload Repository (AWR)
4. SQL Tuning Set (STS)
5. SQL Plan Baseline (SPM)
SQL> set autotrace traceonly explain
SQL> select * from emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
SQL> select * from dept;
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 120 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
SQL> select count(*) from emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
SQL> select ename from emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 98 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
SQL>
9) Test case of your analysis of SQLT, AWR report, explain plan analysis:
SQLT, also known as SQLTXPLAIN is a tool provided by Oracle Server Technologies Center of Expertise that can be used to diagnose why a particular SQL statement is performing poorly. It is not like AWR or Statspack which provide a system-wide view of performance. SQLT is very focused and works on one SQL statement at a time. SQLT inputs one SQL statement and outputs a set of diagnostics files. These files can be used to diagnose SQL statements performing poorly.
starting with Oracle 10.2 there is a script ORACLE_HOME/rdbms/admin/sqltrpt.sql which can be used for usage of SQL Tuning Advisor from the command line and it will give recommendation for problematic sql statements.
[oracle@ibmlab ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 15 16:28:45 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> @?/rdbms/admin/sqltrpt.sql
15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
5yv7yvjgjxugg 196.57 select TIME_WAITED_MICRO from V$SYSTEM_EVENT where eve
22356bkgsdcnh 25.23 SELECT COUNT(*) FROM X$KSPPI A, X$KSPPCV2 B WHERE A.IND
c9umxngkc3byq 19.07 select sql_id, sql_exec_id, dbop_name, dbop_exec_id, to
1p5grz1gs7fjq 17.95 select obj#,type#,ctime,mtime,stime, status, dataobj#,
32qq8k1n8ynn9 16.27 Select BYTES, extents from dba_segments where OWNER =
04kug40zbu4dm 14.33 select policy#, action# from aud_object_opt$ where obje
772s25v1y0x8k 14.00 select shared_pool_size_for_estimate s, shared
aykvshm7zsabd 10.99 select size_for_estimate, size_fac
fhf8upax5cxsz 10.78 BEGIN sys.dbms_auto_report_internal.i_save_report (:rep
49s332uhbnsma 10.71 declare vsn varchar2(20); b
1fvsn5j51ugz3 10.28 begin dbms_rcvman.resetAll; end;
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
g92kfgtvgpakv 10.19 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,
0w26sk6t6gq98 7.08 SELECT XMLTYPE(DBMS_REPORT.GET_REPORT_WITH_SUMMARY(:B1
28bgqbzpa87xf 6.74 declare policy varchar2(512);
dfffkcnqfystw 6.52 WITH MONITOR_DATA AS (SELECT INST_ID, KEY, NVL2(PX_QCSI
15 Most expensive SQL in the workload repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR:
ORA-01427: single-row subquery returns more than one row
Specify the Sql id
~~~~~~~~~~~~~~~~~~
Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: 5yv7yvjgjxugg
Sql Id specified: 5yv7yvjgjxugg
Tune the sql
~~~~~~~~~~~~
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_178
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 06/15/2016 16:38:42
Completed at : 06/15/2016 16:38:43
-------------------------------------------------------------------------------
Schema Name : SYS
Container Name: CDB$ROOT
SQL ID : 5yv7yvjgjxugg
SQL Text : select TIME_WAITED_MICRO from V$SYSTEM_EVENT where event =
'Shared IO Pool Memory'
-------------------------------------------------------------------------------
There are no recommendations to improve the statement.
-------------------------------------------------------------------------------
SQL>
Explain plan analysis:
SQL> @?/rdbms/admin/sqltrpt.sql
15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
7x2gtaa25snk6 448.84 update emp set sal=1000 where deptno=10
5yv7yvjgjxugg 203.77 select TIME_WAITED_MICRO from V$SYSTEM_EVENT where eve
am70ur9qwgp3z 21.03 select l1.sid, ' IS BLOCKING ', l2.sid from v$lock l1
772s25v1y0x8k 14.53 select shared_pool_size_for_estimate s, shared
04kug40zbu4dm 14.41 select policy#, action# from aud_object_opt$ where obje
aykvshm7zsabd 11.39 select size_for_estimate, size_fac
fhf8upax5cxsz 7.85 BEGIN sys.dbms_auto_report_internal.i_save_report (:rep
8p447s6p0rv6b 4.22 select java_pool_size_for_estimate s, java_po
0w26sk6t6gq98 3.81 SELECT XMLTYPE(DBMS_REPORT.GET_REPORT_WITH_SUMMARY(:B1
5k5207588w9ry 3.50 SELECT DBMS_REPORT.GET_REPORT(:B1 ) FROM DUAL
dfffkcnqfystw 3.37 WITH MONITOR_DATA AS (SELECT INST_ID, KEY, NVL2(PX_QCSI
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
gd28w82ct6rva 3.11 select audit$ from tab$ where obj# = :1
6ajkhukk78nsr 1.33 begin prvt_hdm.auto_execute( :dbid, :inst_num , :end_sn
cgtc5gb7c4g07 0.94 select dbid, status_flag from wrm$_wr_control order by
grjtn5w5y8jfj 0.78 SELECT XMLELEMENT( "spot_addm", XMLPARSE(DOCUMENT DBMS_
15 Most expensive SQL in the workload repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR:
ORA-01427: single-row subquery returns more than one row
Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: 7x2gtaa25snk6
Sql Id specified: 7x2gtaa25snk6
Tune the sql
~~~~~~~~~~~~
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_192
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 06/15/2016 20:46:28
Completed at : 06/15/2016 20:46:29
-------------------------------------------------------------------------------
Schema Name : SCOTT
Container Name: PDB1
SQL ID : 7x2gtaa25snk6
SQL Text : update emp set sal=1000 where deptno=10
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "SCOTT"."EMP" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 1494045816
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 3 | 78 | 3 (0)| 00:00:01 |
| 1 | UPDATE | EMP | | | | |
|* 2 | TABLE ACCESS FULL| EMP | 3 | 78 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPTNO"=10)
-------------------------------------------------------------------------------
SQL>
10) Various OS commands for identify bottleneck:
df -h
We may ran out of space of the file system and the database will not even allow to login as it's not having space to generate the os level files. We can use the below commands to investigate and resolve the issue.
On the above screen shot we can clearly observed that the /opt is above 80% which is warning and we should check and remove the files which are no longer required.
VMSTAT(virtual memory statistics) to determine where the system is taking more resources.
/home/oracle $vmstat 5 5
System configuration: lcpu=32 mem=90112MB ent=2.00
kthr memory page faults cpu
----- ----------- ------------------------ ------------ -----------------------
r b avm fre re pi po fr sr cy in sy cs us sy id wa pc ec
5 0 20454530 211691 0 0 0 0 0 0 104 26418 1889 8 6 87 0 0.42 20.9
4 0 20454530 211691 0 0 0 0 0 0 1856 11360 3806 15 12 73 0 0.86 43.1
4 0 20454531 211687 0 0 0 0 0 0 418 21436 2441 11 7 82 0 0.58 29.0
4 0 20454531 211664 0 0 0 0 0 0 108 13954 1947 9 4 87 0 0.43 21.3
2 0 20456049 210136 0 0 0 0 0 0 81 18128 1872 8 5 88 0 0.40 19.8
The r column displays the number of processes waiting for access to a processor. The b column displays the number of processes in a sleep state. These values are usually zero.
If b(processes sleeping) and r are consistently greater than 0, then you may be using more CPU than available. .
If po (memory swapped out to disk) and pi (memory swapped in from disk) are consistently greater than 0, you may have a memory bottleneck. Paging and swapping occur when
there isn’t enough physical memory to accommodate the memory
server::/home/oracle $vmstat -Ivt 1 10
23068672 memory pages
22379248 lruable pages
227065 free pages
4 memory pools
3764502 pinned pages
80.0 maxpin percentage
3.0 minperm percentage
90.0 maxperm percentage
14.2 numperm percentage
3197046 file pages
0.0 compressed percentage
0 compressed pages
14.2 numclient percentage
90.0 maxclient percentage
3197046 client pages
0 remote pageouts scheduled
2028 pending disk I/Os blocked with no pbuf
828417 paging space I/Os blocked with no psbuf
2228 filesystem I/Os blocked with no fsbuf
487680 client filesystem I/Os blocked with no fsbuf
2913185 external pager filesystem I/Os blocked with no fsbuf
85.2 percentage of memory used for computational pages
server::/home/oracle
Server:/home/oracle $vmstat -Iwt 1 10
System configuration: lcpu=32 mem=90112MB ent=2.00
kthr memory page faults cpu time
----------- --------------------- ------------------------------------ ------------------ ----------------------- --------
r b p avm fre fi fo pi po fr sr in sy cs us sy id wa pc ec hr mi se
1 0 0 20439625 226401 11 11 0 0 0 0 61 7359 2813 8 2 90 0 0.33 16.3 08:52:32
0 0 0 20439628 226399 14 14 0 0 0 0 58 4262 1759 3 2 95 0 0.18 8.9 08:52:33
4 0 0 20439628 226399 20 20 0 0 0 0 52 4509 1752 4 2 94 0 0.19 9.3 08:52:34
11 0 0 20439628 226399 17 17 0 0 0 0 68 6426 1757 4 3 93 0 0.22 11.2 08:52:35
14 0 0 20439628 226398 10 10 0 0 0 0 63 29734 4792 10 10 80 0 0.59 29.7 08:52:36
1 0 0 20439612 226412 10 10 0 0 0 0 1257 10112 2854 9 9 81 0 0.59 29.4 08:52:37
11 0 0 20439612 226412 3 3 0 0 0 0 3214 14135 3904 17 19 65 0 1.06 52.8 08:52:38
2 0 0 20443728 222294 21 1144 0 0 0 0 2581 82473 4038 21 42 37 0 2.39 119.3 08:52:39
8 0 0 20439772 226251 18 511 0 0 0 0 3400 17234 5770 20 48 32 0 1.98 99.2 08:52:40
3 0 0 20440792 225229 11 10 0 0 0 0 894 8358 2894 12 8 80 0 0.65 32.7 08:52:41
Server[/home/fug44ppt]$ vmstat -s
95140704086 total address trans. faults
480621222 page ins
1113764123 page outs
3569604 paging space page ins
15652846 paging space page outs
0 total reclaims
44721429833 zero filled pages faults
844490468 executable filled pages faults
1183968453 pages examined by clock
9 revolutions of the clock hand
539831928 pages freed by the clock
1164769704 backtracks
402845 free frame waits
0 extend XPT waits
42845818 pending I/O waits
1426787329 start I/Os
590348784 iodones
162725462776 cpu context switches
9902082156 device interrupts
1745487474 software interrupts
72827743316 decrementer interrupts
2615139 mpc-sent interrupts
2850052 mpc-received interrupts
763948723 phantom interrupts
0 traps
367591277859 syscalls
Server[/home/fug44ppt]$
I/O Stats
Reports I/O statistics
The prstat utility is used to identify CPU resources utilization:
bash-2.05$ prstat 5
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
28195 root 71M 60M sleep 59 0 307:14:50 1.0% BESClient/4
25853 jthotaku 6624K 6464K cpu1 39 0 0:00:00 0.4% prstat/1
3796 root 232M 208M sleep 59 0 39:58:55 0.1% kuxagent/45
12 root 16M 12M sleep 59 0 3:12:36 0.1% vxconfigd/1
25832 root 5160K 3840K sleep 59 0 0:00:00 0.0% sshd/1
25846 jthotaku 2600K 2024K sleep 59 0 0:00:00 0.0% bash/1
25837 jthotaku 1936K 1512K sleep 59 0 0:00:00 0.0% ksh/1
1649 root 5296K 3288K sleep 59 0 0:00:00 0.0% automountd/2
1547 root 111M 82M sleep 59 0 0:00:50 0.0% vxsvc/18
25100 root 2400K 1968K sleep 59 0 0:00:02 0.0% inetd/1
1647 daemon 2520K 1744K sleep 59 0 0:00:00 0.0% statd/1
1507 root 3496K 1128K sleep 60 0 0:00:00 0.0% ebssdkd/2
1684 root 2152K 1320K sleep 100 - 0:00:02 0.0% xntpd/1
487 root 8904K 2832K sleep 59 0 0:00:01 0.0% vxesd/7
1527 root 13M 3152K sleep 59 0 0:00:10 0.0% pbx_exchange/1
Total: 1828 processes, 2071 lwps, load averages: 0.04, 0.05, 0.11
SAR:(System Activity Reporter) SAR is a legacy system monitoring tool which is used to report CPU activity, system loads average, memory/paging, LUN load, network activity.
bash-2.05$ sar -p 10 10
SunOS drsmgtp1 5.9 Generic_122300-31 sun4u 06/15/2016
11:31:07 atch/s pgin/s ppgin/s pflt/s vflt/s slock/s
11:31:17 8.00 2.40 6.00 6.40 18.30 0.00
11:31:27 0.70 2.80 2.80 0.00 0.40 0.00
11:31:37 0.40 1.50 1.50 0.10 0.00 0.00
11:31:47 4.30 4.40 4.40 5.90 16.10 0.00
11:31:57 0.70 2.80 2.80 0.00 0.00 0.00
11:32:07 0.40 1.60 1.60 0.70 0.00 0.00
11:32:17 3.50 1.20 1.20 5.90 16.10 0.00
11:32:27 0.40 1.90 5.70 0.10 0.00 0.00
11:32:37 1.20 0.00 0.00 0.00 0.00 0.00
11:32:47 4.40 0.00 0.00 5.90 16.20 0.00
Average 2.40 1.86 2.60 2.50 6.71 0.00
bash-2.05$
TOP is a command to get the load information along with memory and process utilization details.
PS: ps is a command to quickly identify the what are the process running on the Unix an Linux servers.
[oracle@ibmlab cdump]$ ps -ef |grep pmon
oracle 23212 22307 0 21:04 pts/3 00:00:00 grep pmon
oracle 27788 1 0 Jun10 ? 00:00:20 ora_pmon_cdb1
[oracle@ibmlab cdump]$
free -m: is a command to get the memory information on the linux operating system.
[oracle@ibmlab cdump]$ free -m
total used free shared buffers cached
Mem: 3962 3897 65 0 139 3170
-/+ buffers/cache: 587 3375
Swap: 10239 18 10221
[oracle@ibmlab cdump]$
4. The mpstat utility reports on system wide CPU statistics
mpstat 2 10
Reports processor related statistics.
5. Ipcs/ipcrm
6. NMON
Grid output
Output from AWR
Output from ADDM report
SQLT recommendation
upon analysis ,We observed the creating and setting profile will help optimizer to choose correct plan and generated SQLT report as below
CREATE INDEX ATP_DBA.GA_DE_ST_IN_02 ON ATP_DBA.GAZ_DESTINATION_STATION
(FROM_AREA||FROM_DISTRICT||from_sector||FROM_STREET)
TABLESPACE TSPACEI01;
CREATE INDEX ATP_DBA.GA_DE_ST_IN_03 ON ATP_DBA.GAZ_DESTINATION_STATION
(TO_AREA||TO_DISTRICT||to_sector||TO_STREET)
TABLESPACE TSPACEI01;
Third case:
The Below Script is causing database performance degradation. Please see the same sqlid 4p0xrmd31t34c is running by multiple session. Those session is coming in Top session in GLANCE output
$ ps -ef|grep 1855
oracle 8093 27204 0 16:09:26 pts/7 0:00 grep 1855
oracle 1855 1 255 15:48:08 ? 17:12 ora_p010_PRDB0073
$ ps -ef|grep 23569
oracle 23569 1 252 15:24:21 ? 9:47 ora_p001_PRDB0073
oracle 8134 27204 0 16:09:52 pts/7 0:00 grep 23569
$ ps -ef|grep 1859
oracle 8510 27204 0 16:10:31 pts/7 0:00 grep 1859
oracle 1859 1 255 15:48:08 ? 18:11 ora_p012_PRDB0073
$ ps -ef|grep 1847
oracle 1847 1 16 15:48:08 ? 3:17 ora_p006_PRDB0073
oracle 8521 27204 0 16:10:39 pts/7 0:00 grep 1847
$ ps -ef|grep 1851
oracle 1851 1 254 15:48:08 ? 6:29 ora_p008_PRDB0073
oracle 8540 27204 0 16:10:49 pts/7 0:00 grep 1851
$ ps -ef|grep 1865
oracle 8599 27204 1 16:11:09 pts/7 0:00 grep 1865
oracle 1865 1 0 15:48:08 ? 10:12 ora_p015_PRDB0073
$
Enter value for 1: 1855
old 2: where p.addr=s.paddr and p.spid=&1
new 2: where p.addr=s.paddr and p.spid=1855
SQL_ID SQL_HASH_VALUE PREV_HASH_VALUE SID SERIAL# USERNAME EVENT TO_CHAR(S.LOGON_TIME,
------------- -------------- --------------- ---------- ---------- ------------------------------ ---------------------------------------------------------------- ---------------------
4p0xrmd31t34c 1176276108 0 145 18870 WHS_VIEWER direct path write temp 23-may:15:48
SQL> /
Enter value for 1: 23569
old 2: where p.addr=s.paddr and p.spid=&1
new 2: where p.addr=s.paddr and p.spid=23569
SQL_ID SQL_HASH_VALUE PREV_HASH_VALUE SID SERIAL# USERNAME EVENT TO_CHAR(S.LOGON_TIME,
------------- -------------- --------------- ---------- ---------- ------------------------------ ---------------------------------------------------------------- ---------------------
4p0xrmd31t34c 1176276108 0 301 17086 WHS_VIEWER PX Deq: Table Q Normal 23-may:15:48
SQL> /
Enter value for 1: 23573
old 2: where p.addr=s.paddr and p.spid=&1
new 2: where p.addr=s.paddr and p.spid=23573
SQL_ID SQL_HASH_VALUE PREV_HASH_VALUE SID SERIAL# USERNAME EVENT TO_CHAR(S.LOGON_TIME,
------------- -------------- --------------- ---------- ---------- ------------------------------ ---------------------------------------------------------------- ---------------------
4p0xrmd31t34c 1176276108 0 245 1984 WHS_VIEWER PX Deq: Table Q Normal 23-may:15:48
SQL> /
Enter value for 1: 1859
old 2: where p.addr=s.paddr and p.spid=&1
new 2: where p.addr=s.paddr and p.spid=1859
SQL_ID SQL_HASH_VALUE PREV_HASH_VALUE SID SERIAL# USERNAME EVENT TO_CHAR(S.LOGON_TIME,
------------- -------------- --------------- ---------- ---------- ------------------------------ ---------------------------------------------------------------- ---------------------
4p0xrmd31t34c 1176276108 0 227 42381 WHS_VIEWER direct path read temp 23-may:15:48
SQL> /
Enter value for 1: 1847
old 2: where p.addr=s.paddr and p.spid=&1
new 2: where p.addr=s.paddr and p.spid=1847
SQL_ID SQL_HASH_VALUE PREV_HASH_VALUE SID SERIAL# USERNAME EVENT TO_CHAR(S.LOGON_TIME,
------------- -------------- --------------- ---------- ---------- ------------------------------ ---------------------------------------------------------------- ---------------------
4p0xrmd31t34c 1176276108 0 248 8364 WHS_VIEWER PX Deq: Table Q Normal 23-may:15:48
SQL> /
Enter value for 1: 1851
old 2: where p.addr=s.paddr and p.spid=&1
new 2: where p.addr=s.paddr and p.spid=1851
SQL_ID SQL_HASH_VALUE PREV_HASH_VALUE SID SERIAL# USERNAME EVENT TO_CHAR(S.LOGON_TIME,
------------- -------------- --------------- ---------- ---------- ------------------------------ ---------------------------------------------------------------- ---------------------
4p0xrmd31t34c 1176276108 0 273 7054 WHS_VIEWER direct path write temp 23-may:15:48
SQL> /
Enter value for 1: 1865
old 2: where p.addr=s.paddr and p.spid=&1
new 2: where p.addr=s.paddr and p.spid=1865
SQL_ID SQL_HASH_VALUE PREV_HASH_VALUE SID SERIAL# USERNAME EVENT TO_CHAR(S.LOGON_TIME,
------------- -------------- --------------- ---------- ---------- ------------------------------ ---------------------------------------------------------------- ---------------------
4p0xrmd31t34c 1176276108 0 406 26464 WHS_VIEWER PX Deq: Table Q Normal 23-may:15:48
Complete Query
ADDM Recommendation
DETAILED ADDM REPORT FOR TASK 'TASK_8538' WITH ID 8538
------------------------------------------------------
Analysis Period: 23-MAY-2012 from 15:00:29 to 16:00:45
Database ID/Instance: 2524491379/1
Database/Instance Names: PRDB0073/PRDB0073
Host Name: itradwp1
Database Version: 10.2.0.4.0
Snapshot Range: from 9435 to 9436
Database Time: 47551 seconds
Average Database Load: 13.2 active sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FINDING 1: 63% impact (30173 seconds)
-------------------------------------
SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 33% benefit (15839 seconds)
ACTION: Investigate the SQL statement with SQL_ID "4p0xrmd31t34c" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID 4p0xrmd31t34c and
PLAN_HASH 2824768675
WITH "DIM_PROD_MV9" AS (SELECT "DIM_PROD_MV"."BE_ID" "BE_ID",
"DIM_PROD_MV"."ITM_OID" "ITM_OID",
"DIM_PROD_MV"."FG_TYP_NM" "FG_TYP_NM",
"DIM_PROD_MV"."FG_TYP_CD" "FG_TYP_CD",
"DIM_PROD_MV"."FG_CD" "FG_CD"
FROM "WHSUSR"."DIM_PROD_MV" "DIM_PROD_MV"
WHERE "DIM_PROD_MV"."END_DATE" > SYSDATE),
"DIM_CUST_MV10"
AS (SELECT "DIM_CUST_MV"."OBJECT_ID" "OBJECT_ID",
"DIM_CUST_MV"."CUST_CD" "CUST_CD",
"DIM_CUST_MV"."CUST_NM" "CUST_NM",
"DIM_CUST_MV"."CUST_ADDR_LN1_TXT" "CUST_ADDR_LN1_TXT",
"DIM_CUST_MV"."CUST_ADDR_LN2_TXT" "CUST_ADDR_LN2_TXT",
"DIM_CUST_MV"."CUST_ADDR_LN3_TXT" "CUST_ADDR_LN3_TXT",
"DIM_CUST_MV"."CUST_EMAIL_ADDR_TXT" "CUST_EMAIL_ADDR_TXT",
"DIM_CUST_MV"."CUST_CITY_NM" "CUST_CITY_NM",
"DIM_CUST_MV"."CUST_STT_CD" "CUST_STT_CD",
"DIM_CUST_MV"."CUST_TEL_NO" "CUST_TEL_NO",
"DIM_CUST_MV"."CUST_ZIP_CD" "CUST_ZIP_CD",
"DIM_CUST_MV"."COT_CD" "COT_CD",
"DIM_CUST_MV"."COT_NM" "COT_NM"
FROM "WHSUSR"."DIM_CUST_MV" "DIM_CUST_MV"
WHERE "DIM_CUST_MV"."CURRENT_FLG" = 'Y'),
"DIM_TIME_MV"
AS (SELECT "DIM_TIME_MV"."BE_ID" "BE_ID",
"DIM_TIME_MV"."DAY_STRT_PRD_OF_TM" "DAY_STRT_PRD_OF_TM"
FROM "WHSUSR"."DIM_TM_MV" "DIM_TIME_MV"
WHERE "DIM_TIME_MV"."END_DATE" > SYSDATE),
"EASI_Sales"
AS (SELECT "FACT_EASI_SLS_LINE_STD"."FY_YR_MO_CD" "FY_YR_MO_CD",
"FACT_EASI_SLS_LINE_STD"."FY_YR_MO_CD" "YR_MTH_CD",
"FACT_EASI_SLS_LINE_STD"."CUST_ACCT_BE_ID"
"CUST_ACCT_BE_ID",
"DIM_PROD_MV9"."FG_CD" "FG_CD",
"DIM_CUST_MV10"."CUST_CD" "CUST_CD",
"DIM_CUST_MV10"."CUST_NM" "CUST_NM"
FROM "TRANSDATA"."FACT_EASI_SLS_LINE_STD" "FACT_EASI_SLS_LINE_STD",
"DIM_PROD_MV9",
"DIM_CUST_MV10",
"DIM_TIME_MV"
WHERE "DIM_TIME_MV"."BE_ID" =
"FACT_EASI_SLS_LINE_STD"."SLS_POSTD_DT_BE_ID"
AND "DIM_CUST_MV10"."OBJECT_ID" =
"FACT_EASI_SLS_LINE_STD"."CUST_ACCT_BE_ID"
AND "DIM_PROD_MV9"."BE_ID" =
"FACT_EASI_SLS_LINE_STD"."SHIP_PROD_BE_ID"),
"DIM_PROD_MV12" AS (SELECT "DIM_PROD_MV"."BE_ID" "BE_ID",
"DIM_PROD_MV"."ITM_OID" "ITM_OID",
"DIM_PROD_MV"."FG_TYP_NM" "FG_TYP_NM",
"DIM_PROD_MV"."FG_TYP_CD" "FG_TYP_CD",
"DIM_PROD_MV"."FG_CD" "FG_CD"
FROM "WHSUSR"."DIM_PROD_MV" "DIM_PROD_MV"
WHERE "DIM_PROD_MV"."END_DATE" > SYSDATE),
"DIM_CUST_MV13"
AS (SELECT "DIM_CUST_MV"."OBJECT_ID" "OBJECT_ID",
"DIM_CUST_MV"."CUST_CD" "CUST_CD",
"DIM_CUST_MV"."CUST_NM" "CUST_NM",
"DIM_CUST_MV"."CUST_ADDR_LN1_TXT" "CUST_ADDR_LN1_TXT",
"DIM_CUST_MV"."CUST_ADDR_LN2_TXT" "CUST_ADDR_LN2_TXT",
"DIM_CUST_MV"."CUST_ADDR_LN3_TXT" "CUST_ADDR_LN3_TXT",
"DIM_CUST_MV"."CUST_EMAIL_ADDR_TXT" "CUST_EMAIL_ADDR_TXT",
"DIM_CUST_MV"."CUST_CITY_NM" "CUST_CITY_NM",
"DIM_CUST_MV"."CUST_STT_CD" "CUST_STT_CD",
"DIM_CUST_MV"."CUST_TEL_NO" "CUST_TEL_NO",
"DIM_CUST_MV"."CUST_ZIP_CD" "CUST_ZIP_CD",
"DIM_CUST_MV"."COT_CD" "COT_CD",
"DIM_CUST_MV"."COT_NM" "COT_NM"
FROM "WHSUSR"."DIM_CUST_MV" "DIM_CUST_MV"
WHERE "DIM_CUST_MV"."CURRENT_FLG" = 'Y'),
"Consumer_Sales"
AS (SELECT "FACT_CNSMR_SLS_STD"."FISC_MO_CD" "FISC_MO_CD",
"FACT_CNSMR_SLS_STD"."DISTR_BE_ID" "DISTR_BE_ID",
"DIM_PROD_MV12"."FG_CD" "FG_CD",
"DIM_PROD_MV12"."FG_CD" "KIT_CD",
"DIM_CUST_MV13"."CUST_CD" "CUST_CD",
"DIM_CUST_MV13"."CUST_NM" "CUST_NM",
"DIM_CUST_MV13"."CUST_ADDR_LN1_TXT" "CUST_ADDR_LN1_TXT",
"DIM_CUST_MV13"."CUST_CITY_NM" "CUST_CITY_NM",
"DIM_CUST_MV13"."CUST_STT_CD" "CUST_STT_CD",
"DIM_CUST_MV13"."CUST_ZIP_CD" "CUST_ZIP_CD",
"DIM_CUST_MV13"."COT_CD" "COT_CD",
"DIM_CUST_MV13"."COT_NM" "COT_NM",
"FACT_CNSMR_SLS_STD"."SS_QTY" "SS_QTY"
FROM "DIM_PROD_MV12"
LEFT OUTER JOIN
( "DIM_CUST_MV13"
INNER JOIN
"TRANSDATA"."FACT_CNSMR_SLS_STD" "FACT_CNSMR_SLS_STD"
ON "DIM_CUST_MV13"."OBJECT_ID" =
"FACT_CNSMR_SLS_STD"."CUST_BE_ID")
ON "DIM_PROD_MV12"."BE_ID" =
"FACT_CNSMR_SLS_STD"."KIT_BE_ID")
SELECT DISTINCT SUBSTR ("EASI_Sales"."YR_MTH_CD", 1, 6)
"E_YR_MTH_CD",
"EASI_Sales"."CUST_ACCT_BE_ID" "E_CUST_ACCT_BE_ID",
"EASI_Sales"."FG_CD" "E_FG_CD",
"EASI_Sales"."CUST_NM" "E_CUST_NM",
"Consumer_Sales"."FISC_MO_CD" "C_FISC_MO_CD",
"Consumer_Sales"."DISTR_BE_ID" "C_DISTR_BE_ID",
"Consumer_Sales"."FG_CD" "C_FG_CD",
"Consumer_Sales"."KIT_CD" "KIT_CD",
"Consumer_Sales"."CUST_NM" "C_CUST_NM",
"Consumer_Sales"."CUST_ADDR_LN1_TXT" "CUST_ADDR_LN1_TXT",
"Consumer_Sales"."CUST_CITY_NM" "CUST_CITY_NM",
"Consumer_Sales"."CUST_STT_CD" "CUST_STT_CD",
"Consumer_Sales"."CUST_ZIP_CD" "CUST_ZIP_CD",
"Consumer_Sales"."COT_CD" "COT_CD",
"Consumer_Sales"."COT_NM" "COT_NM",
"EASI_Sales"."FY_YR_MO_CD" "E_FY_YR_MO_CD",
'DIST. RPTD' "GROUP17",
"Consumer_Sales"."CUST_CD" "C_CUST_CD",
"EASI_Sales"."CUST_CD" "E_CUST_CD",
"Consumer_Sales"."SS_QTY" "SS_QTY"
FROM "EASI_Sales", "Consumer_Sales"
WHERE "EASI_Sales"."FY_YR_MO_CD" >= SUBSTR ('01012011', 1, 6)
AND "EASI_Sales"."YR_MTH_CD" <= "Consumer_Sales"."FISC_MO_CD"
AND ("EASI_Sales"."FG_CD" = "Consumer_Sales"."FG_CD"
OR "EASI_Sales"."FG_CD" = "Consumer_Sales"."KIT_CD")
AND "EASI_Sales"."CUST_ACCT_BE_ID" = "Consumer_Sales"."DISTR_BE_ID"
RATIONALE: SQL statement with SQL_ID "4p0xrmd31t34c" was executed 1
times and had an average elapsed time of 15838 seconds.
RATIONALE: Waiting for event "PX Deq Credit: send blkd" in wait class
"Other" accounted for 65% of the database time spent in processing
the SQL statement with SQL_ID "4p0xrmd31t34c".
Detail for Selected 5 Minute Interval
Start TimeNov 6, 2014 8:29:35 AM GMT
Top SQL
Actions [Go]
Select All | Select None
Select Activity (%) [Sorted in descending order] SQL ID SQL Type
[CPU (10.24%)] [Concurrency (4.01%)] [Other (.04%)] 14.29 57w71dgk5qbtx SELECT
[Concurrency (14.04%)] [Other (.06%)] [CPU (.00%)] 14.11 459f3z9u4fb3u SELECT
[Concurrency (3.65%)] [Other (.05%)] [CPU (.02%)] 3.72 3cj240n6v9znj PL/SQL EXECUTE
[Network (1.97%)] [CPU (.04%)] [User I/O (.00%)] 2.02 3h6gj58wp9u1y PL/SQL EXECUTE
[User I/O (1.02%)] [Concurrency (.42%)] [Other (.05%)] [CPU (.02%)] 1.51 bzhtzu2yf68jb SELECT
select * from table(dbms_xplan.display_cursor('&sql_id',null,'AdVanced ALLSTATS LAST'));
select * from table(dbms_xplan.display_awr('&sql_id',null,null,'advanced'));
3 - filter(("KGLHDNSP"=7 AND "KGLNAOBJ" LIKE 'ORA$ALERT$%' AND BITAND("KGLHDFLG",128)<>0))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) STRDEF[990]
2 - STRDEF[990]
3 - "KGLNAOBJ"[VARCHAR2,1000], "KGLHDNSP"[NUMBER,22], "KGLHDFLG"[NUMBER,22]
4 - "SID"[VARCHAR2,30]
Note
-----
- cardinality feedback used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
SQL_ID 57w71dgk5qbtx, child number 7
-------------------------------------
SELECT DISTINCT SUBSTR(KGLNAOBJ,11) SID FROM X$KGLOB WHERE KGLHDNSP = 7
AND KGLNAOBJ LIKE 'ORA$ALERT$%' AND BITAND(KGLHDFLG,128)!=0 UNION
SELECT DISTINCT SID FROM DBMS_ALERT_INFO
Plan hash value: 2001468060
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| | | | |
| 1 | SORT UNIQUE | | 183 | 2437 | 9 (45)| 00:00:01 | 43008 | 43008 |38912 (0)|
| 2 | UNION-ALL | | | | | | | | |
|* 3 | FIXED TABLE FULL | X$KGLOB | 1 | 71 | 2 (100)| 00:00:01 | | | |
| 4 | INDEX FAST FULL SCAN| SYS_C005421344 | 436 | 5668 | 5 (0)| 00:00:01 | | | |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$1
3 - SEL$1 / X$KGLOB@SEL$1
4 - SEL$2 / DBMS_ALERT_INFO@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SET$1")
INDEX_FFS(@"SEL$2" "DBMS_ALERT_INFO"@"SEL$2" ("DBMS_ALERT_INFO"."NAME" "DBMS_ALERT_INFO"."SID"))
FULL(@"SEL$1" "X$KGLOB"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("KGLHDNSP"=7 AND "KGLNAOBJ" LIKE 'ORA$ALERT$%' AND BITAND("KGLHDFLG",128)<>0))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) STRDEF[990]
2 - STRDEF[990]
3 - "KGLNAOBJ"[VARCHAR2,1000], "KGLHDNSP"[NUMBER,22], "KGLHDFLG"[NUMBER,22]
4 - "SID"[VARCHAR2,30]
Note
-----
- cardinality feedback used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
SQL_ID 57w71dgk5qbtx, child number 8
-------------------------------------
SELECT DISTINCT SUBSTR(KGLNAOBJ,11) SID FROM X$KGLOB WHERE KGLHDNSP = 7
AND KGLNAOBJ LIKE 'ORA$ALERT$%' AND BITAND(KGLHDFLG,128)!=0 UNION
SELECT DISTINCT SID FROM DBMS_ALERT_INFO
Plan hash value: 2001468060
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| | | | |
| 1 | SORT UNIQUE | | 183 | 2437 | 9 (45)| 00:00:01 | 43008 | 43008 |38912 (0)|
| 2 | UNION-ALL | | | | | | | | |
|* 3 | FIXED TABLE FULL | X$KGLOB | 1 | 71 | 2 (100)| 00:00:01 | | | |
| 4 | INDEX FAST FULL SCAN| SYS_C005421344 | 436 | 5668 | 5 (0)| 00:00:01 | | | |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$1
3 - SEL$1 / X$KGLOB@SEL$1
4 - SEL$2 / DBMS_ALERT_INFO@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SET$1")
INDEX_FFS(@"SEL$2" "DBMS_ALERT_INFO"@"SEL$2" ("DBMS_ALERT_INFO"."NAME" "DBMS_ALERT_INFO"."SID"))
FULL(@"SEL$1" "X$KGLOB"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
7) Various option of sql tracing & tkprof
There are several tracing methods available, Oracle now recommends that you use the
DBMS_MONITOR package for most types of tracing. TKPROF is a utility that lets you format any extended trace files that you generate with the event 10046 or
through the DBMS_MONITOR package.
In an Oracle 11.1or higher release, you can use the enhanced SQL tracing interface to trace one or more
SQL statements. Here are the steps to tracing a set of SQL statements.
1.
Issue the alter session set events statement, as shown here, to set up the
trace.
SQL> alter session set events 'sql_trace level 12';
Session altered.
SQL>
2.
Execute the SQL statements.
SQL> select count(*) from sales;
3.
Set tracing off.
SQL> alter session set events 'sql_trace off';
Session altered.
SQL>
Normal users can use the DBMS_SESSION package to trace their sessions, as shown in this example:
SQL>execute dbms_session.session_trace_enable(waits=>true, binds=> false);
To disable tracing, the user must execute the session_trace_disable procedure, as shown here:
SQL> execute dbms_session.session_trace_disable();
-- In SQL*Plus, obtain Data Pump process info:
CONNECT / as sysdba
set lines 150 pages 100 numwidth 7
col program for a38
col username for a10
col spid for a7
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid,
s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
from v$session s, v$process p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr;
SQL> conn / as sysdba
SQL> select s.sid,p.spid "ospid" , p.pid "orapid"
from v$process p, v$session s
where p.addr = s.paddr
and s.sid in (<sid_for_worker_process>,<sid_for_master_process>);
-- Get SQL_TRACE Worker process with level 8:
oradebug setospid <ospid_for_worker>
oradebug unlimit
oradebug event 10046 trace name context forever, level 8
oradebug tracefile_name --> trace file to be uploaded
-- Get SQL_TRACE Master process with level 8:
oradebug setospid <ospid_for_master>
oradebug unlimit
oradebug event 10046 trace name context forever, level 8
oradebug tracefile_name --> trace file to be uploaded
-- To stop the tracing:
oradebug event 10046 trace name context off
2)Get the tkprof formatted output
tkprof trcfile outfile waits=y sort=exeela
exec dbms_support.start_trace_in_session (4361,2072,binds=>true,waits=>true);
exec dbms_support.stop_trace_in_session (4361,2072);
exec dbms_system.set_sql_trace_in_session(1279,62705,true);
EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);
You want to examine a raw SQL trace file.
Solution
Open the trace file in a text editor to inspect the tracing information. Here are portions of a raw SQL
trace generated by executing the dbms_monitor.session_trace_enable procedure:
PARSING IN CURSOR #3 len=490 dep=1 uid=85 oct=3 lid=85 tim=269523043683 hv=672110367
ad='7ff18986250' sqlid='bqasjasn0z5sz'
PARSE #3:c=0,e=647,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=269523043680
EXEC #3:c=0,e=1749,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=3969568374,tim=269523045613
WAIT #3: nam='Disk file operations I/O' ela= 15833 FileOperation=2 fileno=4 filetype=2 obj#=-1
tim=269523061555
FETCH #3:c=0,e=19196,p=0,cr=46,cu=0,mis=0,r=1,dep=1,og=1,plh=3969568374,tim=269523064866
STAT #3 id=3 cnt=12 pid=2 pos=1 obj=0 op='HASH GROUP BY (cr=46 pr=0 pw=0 time=11 us cost=4
size=5317 card=409)'
STAT #3 id=4 cnt=3424 pid=3 pos=1 obj=89079 op='TABLE ACCESS FULL DEPT (cr=16 pr=0 pw=0
time=246 us cost=3 size=4251 card=327)'
As you can see from this excerpt of the raw trace file, you can glean useful information, such as
parse misses, waits, an
Formatting Trace Files with TKPROF
tkprof user_sql_001.trc user1.prf explain=hr/hr table=hr.temp_plan_table_a sys=no
sort=exeela,prsela,fchela
In the example shown here, the tkprof command takes the user_sql_001.trc trace file as input and
generates an output file named user1.prf. The “How it Works” section of this recipe explains key
optional arguments of the TKPROF utility.
tkprof rcc1_ora_3695308.trc rcc1_ora_3695308.prf SYS=NO SORT= EXECPU,FCHCPU
or
tkprof rcc2_ora_1294546.trc thirdnewelapsechela.txt waits=yes sys=no sort=exeela,fchela explain='system/'
TKPROF: Release 10.2.0.5.0 - Production on Tue May 10 11:40:37 2016
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Trace file: dmsprd_ora_58851386.trc
Sort options: execpu fchcpu
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
COMMIT
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 3994 0.77 9.98 0 0 3994 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3994 0.77 9.98 0 0 3994 0
Misses in library cache during parse: 0
Tracing Parallel Queries in a RAC System
Finding the trace files for the server (or thread or slave) processes is sometimes difficult in a RAC
environment, because you aren’t sure on which node or node(s) the database has created the trace files.
Here are the steps to follow to make it easier to find the trace files on the different nodes.
1. Set the px_trace with an alter session command, to help identify the trace
files, as shown here:
SQL> alter session set tracefile_identifier='10046';
SQL> alter session set "_px_trace" = low , messaging;
SQL> alter session set events '10046 trace name context forever,level 12';
2. Execute your parallel query.
SQL> alter table bigsales (parallel 4);
SQL> select count(*) from bigsales;
3. Turn all tracing off.
SQL> alter session set events '10046 trace name context off';
SQL> alter session set "_px_trace" = none;
Specifying px_trace will cause the query coordinator’s trace file to include information about the
slave processes that are part of the query, and the instance each slave process belongs to. You can then
retrieve the trace files from the instances listed in the query coordinator’s trace file.
Tracing Multiple Sessions
You can trace multiple sessions that belong to a user by using the client_id_trace_enable procedure
from the DBMS_MONITOR package. Before you can execute the dbms_monitor.client_id_trace_enable
procedure, you must set the client_identifier for the session by using the DBMS_SESSION package, as
shown here:
SQL> execute dbms_session.set_identifier('SH')
Once you set the client identifier as shown here, the client_identifier column in the V$SESSION
view is populated. You can confirm the value of the client_identifier column by executing the
following statement:
SQL> select sid, serial#,username from v$session where client_identifier='SH';
Now you can execute the dbms_monitor.client_id_trace_enable procedure:
SQL> execute dbms_monitor.client_id_trace_enable(client_id=>'SH', waits=>true, binds=>false);
You can disable the trace with the following command:
SQL> execute dbms_monitor.client_id_trace_disable(client_id=>'SH');
[oracle@ibmlab trace]$
8) Various option for explain plan generation:
The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to run the statement.
The row source tree is the core of the execution plan. It shows the following information:
An ordering of the tables referenced by the statement
An access method for each table mentioned in the statement
A join method for tables affected by join operations in the statement
Data operations like filter, sort, or aggregation
In addition to the row source tree, the plan table contains information about the following:
Optimization, such as the cost and cardinality of each operation
Partitioning, such as the set of accessed partitions
Parallel execution, such as the distribution method of join inputs
The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query.
1. EXPLAIN PLAN command
2. V$SQL_PLAN
3. Automatic Workload Repository (AWR)
4. SQL Tuning Set (STS)
5. SQL Plan Baseline (SPM)
SQL> set autotrace traceonly explain
SQL> select * from emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
SQL> select * from dept;
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 120 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
SQL> select count(*) from emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
SQL> select ename from emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 98 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
SQL>
9) Test case of your analysis of SQLT, AWR report, explain plan analysis:
SQLT, also known as SQLTXPLAIN is a tool provided by Oracle Server Technologies Center of Expertise that can be used to diagnose why a particular SQL statement is performing poorly. It is not like AWR or Statspack which provide a system-wide view of performance. SQLT is very focused and works on one SQL statement at a time. SQLT inputs one SQL statement and outputs a set of diagnostics files. These files can be used to diagnose SQL statements performing poorly.
starting with Oracle 10.2 there is a script ORACLE_HOME/rdbms/admin/sqltrpt.sql which can be used for usage of SQL Tuning Advisor from the command line and it will give recommendation for problematic sql statements.
[oracle@ibmlab ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 15 16:28:45 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> @?/rdbms/admin/sqltrpt.sql
15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
5yv7yvjgjxugg 196.57 select TIME_WAITED_MICRO from V$SYSTEM_EVENT where eve
22356bkgsdcnh 25.23 SELECT COUNT(*) FROM X$KSPPI A, X$KSPPCV2 B WHERE A.IND
c9umxngkc3byq 19.07 select sql_id, sql_exec_id, dbop_name, dbop_exec_id, to
1p5grz1gs7fjq 17.95 select obj#,type#,ctime,mtime,stime, status, dataobj#,
32qq8k1n8ynn9 16.27 Select BYTES, extents from dba_segments where OWNER =
04kug40zbu4dm 14.33 select policy#, action# from aud_object_opt$ where obje
772s25v1y0x8k 14.00 select shared_pool_size_for_estimate s, shared
aykvshm7zsabd 10.99 select size_for_estimate, size_fac
fhf8upax5cxsz 10.78 BEGIN sys.dbms_auto_report_internal.i_save_report (:rep
49s332uhbnsma 10.71 declare vsn varchar2(20); b
1fvsn5j51ugz3 10.28 begin dbms_rcvman.resetAll; end;
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
g92kfgtvgpakv 10.19 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,
0w26sk6t6gq98 7.08 SELECT XMLTYPE(DBMS_REPORT.GET_REPORT_WITH_SUMMARY(:B1
28bgqbzpa87xf 6.74 declare policy varchar2(512);
dfffkcnqfystw 6.52 WITH MONITOR_DATA AS (SELECT INST_ID, KEY, NVL2(PX_QCSI
15 Most expensive SQL in the workload repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR:
ORA-01427: single-row subquery returns more than one row
Specify the Sql id
~~~~~~~~~~~~~~~~~~
Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: 5yv7yvjgjxugg
Sql Id specified: 5yv7yvjgjxugg
Tune the sql
~~~~~~~~~~~~
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_178
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 06/15/2016 16:38:42
Completed at : 06/15/2016 16:38:43
-------------------------------------------------------------------------------
Schema Name : SYS
Container Name: CDB$ROOT
SQL ID : 5yv7yvjgjxugg
SQL Text : select TIME_WAITED_MICRO from V$SYSTEM_EVENT where event =
'Shared IO Pool Memory'
-------------------------------------------------------------------------------
There are no recommendations to improve the statement.
-------------------------------------------------------------------------------
SQL>
Explain plan analysis:
SQL> @?/rdbms/admin/sqltrpt.sql
15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
7x2gtaa25snk6 448.84 update emp set sal=1000 where deptno=10
5yv7yvjgjxugg 203.77 select TIME_WAITED_MICRO from V$SYSTEM_EVENT where eve
am70ur9qwgp3z 21.03 select l1.sid, ' IS BLOCKING ', l2.sid from v$lock l1
772s25v1y0x8k 14.53 select shared_pool_size_for_estimate s, shared
04kug40zbu4dm 14.41 select policy#, action# from aud_object_opt$ where obje
aykvshm7zsabd 11.39 select size_for_estimate, size_fac
fhf8upax5cxsz 7.85 BEGIN sys.dbms_auto_report_internal.i_save_report (:rep
8p447s6p0rv6b 4.22 select java_pool_size_for_estimate s, java_po
0w26sk6t6gq98 3.81 SELECT XMLTYPE(DBMS_REPORT.GET_REPORT_WITH_SUMMARY(:B1
5k5207588w9ry 3.50 SELECT DBMS_REPORT.GET_REPORT(:B1 ) FROM DUAL
dfffkcnqfystw 3.37 WITH MONITOR_DATA AS (SELECT INST_ID, KEY, NVL2(PX_QCSI
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
gd28w82ct6rva 3.11 select audit$ from tab$ where obj# = :1
6ajkhukk78nsr 1.33 begin prvt_hdm.auto_execute( :dbid, :inst_num , :end_sn
cgtc5gb7c4g07 0.94 select dbid, status_flag from wrm$_wr_control order by
grjtn5w5y8jfj 0.78 SELECT XMLELEMENT( "spot_addm", XMLPARSE(DOCUMENT DBMS_
15 Most expensive SQL in the workload repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR:
ORA-01427: single-row subquery returns more than one row
Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: 7x2gtaa25snk6
Sql Id specified: 7x2gtaa25snk6
Tune the sql
~~~~~~~~~~~~
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_192
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 06/15/2016 20:46:28
Completed at : 06/15/2016 20:46:29
-------------------------------------------------------------------------------
Schema Name : SCOTT
Container Name: PDB1
SQL ID : 7x2gtaa25snk6
SQL Text : update emp set sal=1000 where deptno=10
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "SCOTT"."EMP" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 1494045816
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 3 | 78 | 3 (0)| 00:00:01 |
| 1 | UPDATE | EMP | | | | |
|* 2 | TABLE ACCESS FULL| EMP | 3 | 78 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPTNO"=10)
-------------------------------------------------------------------------------
SQL>
10) Various OS commands for identify bottleneck:
df -h
We may ran out of space of the file system and the database will not even allow to login as it's not having space to generate the os level files. We can use the below commands to investigate and resolve the issue.
On the above screen shot we can clearly observed that the /opt is above 80% which is warning and we should check and remove the files which are no longer required.
VMSTAT(virtual memory statistics) to determine where the system is taking more resources.
/home/oracle $vmstat 5 5
System configuration: lcpu=32 mem=90112MB ent=2.00
kthr memory page faults cpu
----- ----------- ------------------------ ------------ -----------------------
r b avm fre re pi po fr sr cy in sy cs us sy id wa pc ec
5 0 20454530 211691 0 0 0 0 0 0 104 26418 1889 8 6 87 0 0.42 20.9
4 0 20454530 211691 0 0 0 0 0 0 1856 11360 3806 15 12 73 0 0.86 43.1
4 0 20454531 211687 0 0 0 0 0 0 418 21436 2441 11 7 82 0 0.58 29.0
4 0 20454531 211664 0 0 0 0 0 0 108 13954 1947 9 4 87 0 0.43 21.3
2 0 20456049 210136 0 0 0 0 0 0 81 18128 1872 8 5 88 0 0.40 19.8
The r column displays the number of processes waiting for access to a processor. The b column displays the number of processes in a sleep state. These values are usually zero.
If b(processes sleeping) and r are consistently greater than 0, then you may be using more CPU than available. .
If po (memory swapped out to disk) and pi (memory swapped in from disk) are consistently greater than 0, you may have a memory bottleneck. Paging and swapping occur when
there isn’t enough physical memory to accommodate the memory
server::/home/oracle $vmstat -Ivt 1 10
23068672 memory pages
22379248 lruable pages
227065 free pages
4 memory pools
3764502 pinned pages
80.0 maxpin percentage
3.0 minperm percentage
90.0 maxperm percentage
14.2 numperm percentage
3197046 file pages
0.0 compressed percentage
0 compressed pages
14.2 numclient percentage
90.0 maxclient percentage
3197046 client pages
0 remote pageouts scheduled
2028 pending disk I/Os blocked with no pbuf
828417 paging space I/Os blocked with no psbuf
2228 filesystem I/Os blocked with no fsbuf
487680 client filesystem I/Os blocked with no fsbuf
2913185 external pager filesystem I/Os blocked with no fsbuf
85.2 percentage of memory used for computational pages
server::/home/oracle
Server:/home/oracle $vmstat -Iwt 1 10
System configuration: lcpu=32 mem=90112MB ent=2.00
kthr memory page faults cpu time
----------- --------------------- ------------------------------------ ------------------ ----------------------- --------
r b p avm fre fi fo pi po fr sr in sy cs us sy id wa pc ec hr mi se
1 0 0 20439625 226401 11 11 0 0 0 0 61 7359 2813 8 2 90 0 0.33 16.3 08:52:32
0 0 0 20439628 226399 14 14 0 0 0 0 58 4262 1759 3 2 95 0 0.18 8.9 08:52:33
4 0 0 20439628 226399 20 20 0 0 0 0 52 4509 1752 4 2 94 0 0.19 9.3 08:52:34
11 0 0 20439628 226399 17 17 0 0 0 0 68 6426 1757 4 3 93 0 0.22 11.2 08:52:35
14 0 0 20439628 226398 10 10 0 0 0 0 63 29734 4792 10 10 80 0 0.59 29.7 08:52:36
1 0 0 20439612 226412 10 10 0 0 0 0 1257 10112 2854 9 9 81 0 0.59 29.4 08:52:37
11 0 0 20439612 226412 3 3 0 0 0 0 3214 14135 3904 17 19 65 0 1.06 52.8 08:52:38
2 0 0 20443728 222294 21 1144 0 0 0 0 2581 82473 4038 21 42 37 0 2.39 119.3 08:52:39
8 0 0 20439772 226251 18 511 0 0 0 0 3400 17234 5770 20 48 32 0 1.98 99.2 08:52:40
3 0 0 20440792 225229 11 10 0 0 0 0 894 8358 2894 12 8 80 0 0.65 32.7 08:52:41
Server[/home/fug44ppt]$ vmstat -s
95140704086 total address trans. faults
480621222 page ins
1113764123 page outs
3569604 paging space page ins
15652846 paging space page outs
0 total reclaims
44721429833 zero filled pages faults
844490468 executable filled pages faults
1183968453 pages examined by clock
9 revolutions of the clock hand
539831928 pages freed by the clock
1164769704 backtracks
402845 free frame waits
0 extend XPT waits
42845818 pending I/O waits
1426787329 start I/Os
590348784 iodones
162725462776 cpu context switches
9902082156 device interrupts
1745487474 software interrupts
72827743316 decrementer interrupts
2615139 mpc-sent interrupts
2850052 mpc-received interrupts
763948723 phantom interrupts
0 traps
367591277859 syscalls
Server[/home/fug44ppt]$
I/O Stats
Reports I/O statistics
The prstat utility is used to identify CPU resources utilization:
bash-2.05$ prstat 5
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
28195 root 71M 60M sleep 59 0 307:14:50 1.0% BESClient/4
25853 jthotaku 6624K 6464K cpu1 39 0 0:00:00 0.4% prstat/1
3796 root 232M 208M sleep 59 0 39:58:55 0.1% kuxagent/45
12 root 16M 12M sleep 59 0 3:12:36 0.1% vxconfigd/1
25832 root 5160K 3840K sleep 59 0 0:00:00 0.0% sshd/1
25846 jthotaku 2600K 2024K sleep 59 0 0:00:00 0.0% bash/1
25837 jthotaku 1936K 1512K sleep 59 0 0:00:00 0.0% ksh/1
1649 root 5296K 3288K sleep 59 0 0:00:00 0.0% automountd/2
1547 root 111M 82M sleep 59 0 0:00:50 0.0% vxsvc/18
25100 root 2400K 1968K sleep 59 0 0:00:02 0.0% inetd/1
1647 daemon 2520K 1744K sleep 59 0 0:00:00 0.0% statd/1
1507 root 3496K 1128K sleep 60 0 0:00:00 0.0% ebssdkd/2
1684 root 2152K 1320K sleep 100 - 0:00:02 0.0% xntpd/1
487 root 8904K 2832K sleep 59 0 0:00:01 0.0% vxesd/7
1527 root 13M 3152K sleep 59 0 0:00:10 0.0% pbx_exchange/1
Total: 1828 processes, 2071 lwps, load averages: 0.04, 0.05, 0.11
SAR:(System Activity Reporter) SAR is a legacy system monitoring tool which is used to report CPU activity, system loads average, memory/paging, LUN load, network activity.
bash-2.05$ sar -p 10 10
SunOS drsmgtp1 5.9 Generic_122300-31 sun4u 06/15/2016
11:31:07 atch/s pgin/s ppgin/s pflt/s vflt/s slock/s
11:31:17 8.00 2.40 6.00 6.40 18.30 0.00
11:31:27 0.70 2.80 2.80 0.00 0.40 0.00
11:31:37 0.40 1.50 1.50 0.10 0.00 0.00
11:31:47 4.30 4.40 4.40 5.90 16.10 0.00
11:31:57 0.70 2.80 2.80 0.00 0.00 0.00
11:32:07 0.40 1.60 1.60 0.70 0.00 0.00
11:32:17 3.50 1.20 1.20 5.90 16.10 0.00
11:32:27 0.40 1.90 5.70 0.10 0.00 0.00
11:32:37 1.20 0.00 0.00 0.00 0.00 0.00
11:32:47 4.40 0.00 0.00 5.90 16.20 0.00
Average 2.40 1.86 2.60 2.50 6.71 0.00
bash-2.05$
TOP is a command to get the load information along with memory and process utilization details.
PS: ps is a command to quickly identify the what are the process running on the Unix an Linux servers.
[oracle@ibmlab cdump]$ ps -ef |grep pmon
oracle 23212 22307 0 21:04 pts/3 00:00:00 grep pmon
oracle 27788 1 0 Jun10 ? 00:00:20 ora_pmon_cdb1
[oracle@ibmlab cdump]$
free -m: is a command to get the memory information on the linux operating system.
[oracle@ibmlab cdump]$ free -m
total used free shared buffers cached
Mem: 3962 3897 65 0 139 3170
-/+ buffers/cache: 587 3375
Swap: 10239 18 10221
[oracle@ibmlab cdump]$
4. The mpstat utility reports on system wide CPU statistics
mpstat 2 10
Reports processor related statistics.
5. Ipcs/ipcrm
6. NMON
To display usages memory ,CPU ,Disk etc.
7. Paging info
8. Memory and paging in one output
TFA
* TFA from each node:
==> TFA Collector- The Preferred Tool for Automatic or ADHOC Diagnostic Gathering Across All Cluster Nodes ( Doc ID 1513912.1 ) <-- see Additional Info folder
Examples:
<gi_home>/bin/tfactl diagcollect -from "<MON/DD/YYYY HH:00:00>" -to "<MON/DD/YYYY HH:00:00>"
<gi_home>/bin/tfactl diagcollect -all -since 2d
<gi_home>/bin/tfactl diagcollect -for "<MON/DD/YYYY HH:00:00>"
tfactl diagcollect -from "APR/21/2017 14:00:00" -to "APR/21/2017 19:00:00"
* If you cannot use TFA, then please collect/upload the following from the problematic node:
This note will be obsolete in the future, it's strongly recommended to use TFA to prune and collect files from all nodes:
Reference: Document 1513912.1 TFA Collector - Tool for Enhanced Diagnostic Gathering
TFA Collector is installed in the GI HOME and comes with 11.2.0.4 GI and higher. For GI 11.2.0.3 or lower, install the TFA Collector by referring to Document 1513912.1 for instruction on downloading and installing TFA collector.
$GI_HOME/tfa/bin/tfactl diagcollect -from "MMM/dd/yyyy hh:mm:ss" -to "MMM/dd/yyyy hh:mm:ss"
Format example: "Jul/1/2014 21:00:00"
Specify the "from time" to be 4 hours before and the "to time" to be 4 hours after the time of error.
