Purpose
This document is for performing Oracle 11gR2 performance tuning of both database operation and developer’s SQL code. We are using available Oracle management packs to facilitate the tuning. The Oracle System Global Area (SGA) and Program or Process Global Area (PGA) will both be automatically adjusted during runtime based on operational load requirements.
The application is programmed in Java (1.6.0_x) on Windows-based PCs.
This document captures specific tuning requirements and parameter values for
Operating System Kernel Modifications
The following values must be set by the root system user. The most common way to change these is by editing the /etc/sysctl.conf file and restarting the system after changes are made.
-
- # Controls the maximum shared segment size, in bytes
- # for Oracle11g – double the amount of installed RAM
- # for 8 GB system memory use this value
- kernel.shmmax = 17179869183
- # Controls the maximum number of shared memory segments, in pages
- # for Oracle11g – no changes required for 8/12/16 GB systems
- kernel.shmall = 4294967296
- # Maximum number of shared memory segments system wide
- # for Oracle11g – no changes required for 8/12/16 GB systems
- kernel.shmmni = 4096
The value for shmmax in decimal is two times the available RAM. Values for systems with 12 GB and 16 GB of RAM are provided for your convenience, as follows:
# for 12 GB system memory
kernel.shmmax = 25769803775
# for 16 GB system memory
kernel.shmmax = 34359738367
These values are two times RAM, in bytes, minus 1. Do not be tempted to round up by one since counting for this parameter started with zero.
The values for shmall and shmmni do not change with host system memory changes.
# semaphores: semmsl, semmns, semopm, semmni
# for Oracle11g with 300 processes (set in spfile)
kernel.sem = 310 39680 310 128
The rules for Linux semaphore settings for SEMMSL, SEMMNS, SEMOPM and SEMMNI are as follows:
- Set the SEMMSL parameter to 10 plus the largest PROCESSES parameter of any Oracle instance on the host. The
setting is currently 300, therefore the value for SEMMSL is 310.
- Set the SEMMNS parameter is the maximum number of semaphores that can be allocated on the system times SEMMNI. SEMMNI stays at 128, so set SEMMNS to 39680.
- Set the SEMOPM parameter to the value of SEMMSL. On the
database this value is 310.
- Set the SEMMNI parameter to 128 which was used in the calculation above for SEMMNS.
Operating System Swap Size (& Type)
Vendor documentation states the following swap sizes based on memory configuration:
1 to 2 GB systems -> set to 1.5 times the memory size
2 to 16 GB -> set to installed memory size
> 16 GB -> set to 16 GB
In order to determine current size, execute the following OS command:
grep SwapTotal /proc/meminfo
No changes are necessary on
# for 8 GB system memory
8,388,608
# for 12 GB system memory
12,582,912
# for 16 GB system memory
16,777,216
Instructions for adjusting these values are found in the
Werner Puschitz (http://www.puschitz.com) states that one should “never add a permanent swap file to the system due to the performance impact of the filesystem layer.”
The
Oracle Initialization Parameters
The
$ORACLE_HOME/dbs/spfile
Server parameter files let you make persistent changes to individual parameters. When you use a server parameter file, you can specify in an ALTER SYSTEM SET parameter statement that the new parameter value should be persistent. This means that the new value applies not only in the current instance, but also to any instances that are started up subsequently. Traditional plain-text parameter files do not let you make persistent changes to parameter values.
In order to view Oracle parameters from the OS there is a plain text version available in the same location named init
SQL> create pfile from spfile ;
The resulting file will then be current. The name and location of this file follows:
$ORACLE_HOME/dbs/init
Dynamic Parameters
The following parameters are created and values adjusted dynamically by Oracle and saved in the SPFILE. When moving to a new system from an identically configured system they may be included; however, if not included, they will be recreated from the new, operational system.
-
.__db_cache_size=738197504 .__java_pool_size=1677721 .__large_pool_size=1677721 .__oracle_base=’/u01/app/oracle’ #ORACLE_BASE set from environment .__pga_aggregate_target=98985574 .__sga_target=184549376 .__shared_io_pool_size= .__shared_pool_size=102341017 .__streams_pool_size=16777216
Each of these parameters is preceded with the instance name, a dot and double underscores (for example,
Oracle internal parameters are not to be used except under direction of Oracle support. They appear in the pfile (init.ora, spfile) beginning with a single underline (_).
*._trace_files_public=FALSE # tested on clone – removed
When updating an Oracle database, any existing hidden parameters (single underscore) should be re-evaluated and approved by Oracle specifically for the new version. Otherwise, they should be removed. The premise is that they were provided to work-around a particular issue and a new release would hopefully resolve that issue, making a work-around no longer necessary.
The balance of initialization parameters follow:
-
- *.audit_file_dest=’/u01/app/oracle/admin/
/adump’ - *.audit_trail=’db’
- *.compatible=’11.2.0.3.0′
- *.control_files=’/u02/app/oracle/product/11.2.0/oradata/
/control01.ctl’,’/u03/app/oracle/product/11.2.0/fast_recovery_area/ /control02.ctl’ - *.db_block_size=8192
- *.db_cache_size=0
- *.db_domain=”
- *.db_name='
’ - *.db_recovery_file_dest=’/u03/app/oracle/product/11.2.0/fast_recovery_area’
- *.db_recovery_file_dest_size=17179869184
- *.db_writer_processes=2
- *.dbwr_io_slaves=2
- *.diagnostic_dest=’/u01/app/oracle’
- *.disk_asynch_io=FALSE
- *.dispatchers='(PROTOCOL=TCP) (SERVICE=
XDB)’ - *.distributed_lock_timeout=240
- *.filesystemio_options=’SETALL’
- *.java_pool_size=0
- *.job_queue_processes=1000
- *.large_pool_size=0
- *.memory_max_target=2835349504
- *.memory_target=2831155200
- *.open_cursors=300
- *.optimizer_capture_sql_plan_baselines=TRUE
- *.optimizer_mode=’FIRST_ROWS_100′
- *.pga_aggregate_target=0
- *.processes=300
- *.query_rewrite_enabled=’TRUE’
- *.remote_listener=”
- *.remote_login_passwordfile=’EXCLUSIVE’
- *.remote_os_roles=FALSE
- *.resource_manager_plan=’MIXED_WORKLOAD_PLAN’
- *.sessions=335
- *.sga_target=0
- *.shared_pool_reserved_size=0
- *.shared_pool_size=0
- *.statistics_level=’ALL’
- *.streams_pool_size=0
- *.undo_tablespace=’UNDOTBS1′
- *.use_large_pages=’FALSE’
- *.audit_file_dest=’/u01/app/oracle/admin/
- restart the database from SQL*Plus using the following sequence and commands:
-
- shutdown immediate
- startup pfile=/u01/app/oracle/product/11.2.0/oraDB1/dbs/init
.ora - create spfile from pfile ;
- shutdown immediate
- startup
-
- disable
. _stats_job
- disable
Listener & Server-side TNSNAMES file Configuration
From MOS, ID 99136.1
Starting with 11g, there is no further need to configure the Listener for EXTPROC, but the capability still exists.
11g provides an extproc.ora file located in the $ORACLE_HOME/hs/admin directory.
To use the extproc.ora file and bypass the listener do the following:
1. comment out or remove any entry of “extproc_connection_data” from the tnsnames.ora on the server.
2. In file $ORACLE_HOME/hs/admin/extproc.ora add the line (or modify the existing value)
SET EXTPROC_DLLS=ANY
Use the following listener.ora file with no modifications:
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/oraDB1/network/admin/listener.ora
# Custom tailored by d.russell, 11/2012
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oratrunk)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))))
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/oraDB1)
(SID_NAME=))
(SID_DESC=
(SID_NAME=CLRExtProc)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/oraDB1)
(PROGRAM=extproc)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
SUBSCRIBE_FOR_NODE_DOWN_EVENT_Listener=OFF
- END OF FILE –
Use the following tnsnames.ora file with no modifications:
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/oraDB1/network/admin/tnsnames.ora
# Custom tailored by d.russell, 11/2012
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oratrunk)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = )
)
)
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
- END OF FILE –
Note: Please remember, this is a server-side only TNSNAMES.ORA file.
Also note that the comments on the second line of each file were modified to reflect “custom” and not generated by the listener configuration GUI. All comments in these files get sorted when you use the configuration utility, even if you do not complete it and save. It is a good idea to save copies of these two files, and/or remember this document in case they get changed.
Caveats
The
The top five SQL components executed belong to Oracle Label Security (OLS). While some aspects of this may be tuned, is was known going into this that OLS is a resource consumer… and this aspect of performance is accepted.
Some tuning of these modules is possible. Accepting a better execution plan for an OLS component is possible and permissible. Recommendations to increase fetch array size which must be implemented in code, connection or database session cannot so easily be done. The following recommendation is currently un-implementable:
Use bigger fetch arrays while fetching results from the SELECT statement with SQL_ID “6nb2tcsxgrbhw”
Disk space on trunk/trunk-clone is limited and we are not able to run in archive log mode for any length of time. Redo logs do appear to be rolling at an acceptable interval; but they are not being written to disk as archived redo logs.
No end-to-end tool is being used to separate network (VPN) delays from other delays.
Configuration and Procedural Recommendations
Recommendations in this section have not been implemented. Some will be over the course of tuning. Others are simply suggestions or best practice procedure provided. Step-by-step procedures, technique or values are not supplied in this section.
- Create an Oracle database user/schema, typically named Oracle, for the DBA
- grant DBA and SYSDBA to Oracle
- grant EXEMPT_ACCESS_POLICY to Oracle
- Do not do exports (or imports) as the sys user
- it is impossible for sys to get a consistent export
- also bad policy to use sys for anything except install/patch/update
- Critical Patch Updates are released on the Tuesday closest to the 17th day of January, April, July and October. Most recently released October 16, 2017.
The next four dates are:
- 15 January 2017
- 16 April 2017
- 16 July 2017
- 15 October 2017
For Oracle Java SE Critical Patch Updates, the next three dates are:
- 19 February 2017
- 18 June 2017
- 15 October 2017
- Enable archive log mode
- online redo logs appear to be rolling at proper intervals
- ideally, every 20 minutes under load
- Change archive log location (in flash or fast recovery area) to be one level up from where they are created by default. The archived redo logs are easier to apply in the event of a restore and recovery when the full range of files required are in one folder.
- Do implement backups and test restores
- RMAN for backups, exports are not backups
- Non-logged transactions (such as truncate) invalidate the ability to restore forward
- a full backup is always recommended after a non-logged transaction
- Do not do “full” database exports
- full schema is acceptable; but, full database is problematic
- do not attempt to import the sys, system schemas
- additional schemas which are restricted from export by design are, as follows:
- ‘ORDSYS’, ‘MDSYS’, ‘CTXSYS’, ‘ORDPLUGINS’, ‘LBACSYS’, ‘XDB’, ‘SI_INFORMTN_SCHEMA’, ‘DIP’, ‘DBSNMP’, ‘EXFSYS’, ‘WMSYS’, ‘ORACLE_OCM’, ‘ANONYMOUS’, ‘XS$NULL’, ‘APPQOSSYS’
- also, ‘DMSYS’ and ‘PUBLIC’ should not be exported
- additional schemas which are restricted from export by design are, as follows:
- Be careful on advice to “re-run the root.sh script” since there are many possible operations which can replace the original script which was created at installation with a completely different script since then. You may be telling someone to run something dangerous.
- When using DBCA, Step 9 of 11: Initialization Parameters, and creating a new database on a server (i.e., not desktop, but, dedicated server), use the slider to capture 80% of the memory. On most modern systems with lots of RAM, this will create a huge global area (SGA/PGA/UGA). Also check the box to use automatic memory management so that Oracle will control the allocations. At a certain point the SGA doesn’t need to be larger; but, the total PGA size is determined by number of users. Keep in mind that this does not apply when the init.ora is being replaced by the DBA since those memory specifications will override what was created in DBCA.
- Pre-positioning installation files in a folder under /tmp prior to beginning the installation process is a good idea. A better one would be to create a folder for compressed files, and another for staged files, and do the install from there. Instead of being under /tmp, where any file should be fair game to delete, they should be on a device that holds files which grow, where the DB will stop functioning when the device is full. The DBA can make a determination to delete them in order to quickly restore service… or have them immediately available in the event they are needed during the life of the system. Older versions of any product are sometimes hard to obtain. This has save me many times.
- Statistics are automatically collected for an index at creation. No compute statistics clause is required as part of the create index statement.
- Storage specification on objects in locally managed tablespaces are not implemented even though they are accepted (and visible in dictionary) they are over-ridden by Oracle with values required by the operating system. It does not hurt to include them; but, what you get may not be what you expect to see.
- Create /u01, /u02 and /u03 on separate devices
- Create swap partition (vs. using a swap file)
Installed Components Not Used
The usual recommendation is not to install them in the first place; however, you do not always have that option. Schemas which are not currently used are “expired and locked”. A password must be set when unlocked. This makes them relatively safe if not removed (i.e., uninstalled).
- Oracle OLAP (multidimensional analytic engine. schema: OLAPSYS)
- Data Mining (schema: DMSYS)
- Real Application Testing
Recommendation: remove the above three and modify installation procedures not to install.
- Oracle Partitioning – installed and used by SYS, may be used by
if data volume warrants
New features in 11g which may be installed by default follow:
- Oracle Application Express
- (schemas: APEX_030200, APEX_PUBLIC_USER, APPQOSSYS, FLOW_FILES)
- Oracle Configuration Manager
- (schema:
ORACLE_OCM)
- (schema:
- Oracle Database Vault
- (schema: unknown)
- Oracle SQL Developer
- (does not use a default schema)
- Oracle Warehouse Builder
- (schemas: OWBSYS, OWBSYS_AUDIT)
Oracle Application Express (APEX) is a rapid web application development tool for the Oracle database. Version 3.2 is installed. Version 4.1 is current, and 4.2 available in Beta.
Recommendation: Oracle APEX should be removed.
Oracle Configuration Manager (OCM) is integrated with Oracle Universal Installer (OUI). OCM is an optional component which may be installed with any 11g database; however, it requires configuration with MyOracleSupport (MOS, formerly Metalink), and one or more email addresses to be set up with OS ability to send/receive email.
Recommendation: OCM should be removed.
Oracle Database Vault realms act as a firewall around application data and block access from privileged users. Realms are easily defined and can be placed around an entire application or a set of tables quickly and easily.
Recommendation: Database Vault should not be installed
Recommendation: Real Application Testing should not be installed
Oracle SQL Developer is installed and may have been manually upgraded in same or different location. This is a useful tool for browsing the database. While not customary to install certain tools on database servers, this one may prove useful down the road.
Recommendation: Oracle Warehouse Builder is installed and should be removed.
The following components should be turned off or removed:
- flashback table (turn off)
- flashback database (turn off)
- Demo schemas (remove) (Scott present)
Tuning Oracle SQL Code on Server
With all configuration parameters in place, the database tuned, it is time to turn attention to the developer’s SQL code.
- Open Microsoft Internet Explorer
- Navigate to the DB Console
- typically, https://
:5500/em/console/logon/logon
- typically, https://
- Login as the sys user with sysdba privilege
- From the Performance tab, select SQL Tuning Sets
- Either select an existing set to tune, or create a new one
To create new SQL Tuning Set, do the following:
- Click on the “Create” button (top right of screen)
- Provide a new SQL Tuning Set Name (for example:
Tuesday AM) - Change the Owner from SYS to (or other schema you wish to collect)
- Enter a brief description such as “two hours with users on ESM” and hit “Next”
- Select a “Load Method”, for our example, select “incrementally, 2 hour duration, 5 minute frequency”, and hit “Next”
- Enter the “Parsing Schema Name” as “
” - Select “Module” from the drop-down and push the “Add a Filter or Column” button
- Enter “JDBC Thin Client” as the Module name, and press “Next”
- Schedule a new job to execute your tuning set, either immediate, or later
- enter a description if you like; but, make it short
- Hit “Next” and review the job options selected, if acceptable, hit “Submit”
If it worked, you will receive confirmation, similar to the following:
SQL tuning set
From here, it’s on its’ own until completion. Since we said two hours, wait. Chances are that it will only take a couple of minutes.
Once completed, review the details and modify the tuning set to only tune logical SQL. Note that Database Control (or DB Console) only shows 2000 statements. The number collected may be in the thousands. You may want to edit and delete statements, or reduce the time collected in the future, as you really do not want to be tuning statements you have not seen. You may sort the full number by any of the fields displayed, and eliminate illogical SQL from the tuning set.
You must next schedule the SQL Advisor by hitting the button so marked.
As with scheduling the collection (above) enter the missing information and identify when you want the set to run. For example, enter a short description, tell it the scope, total and depth of analysis. Then tell it to run now, or later, and when.
Hit Submit.
You may watch the progress on the next screen, or you may navigate away, even disconnect. Your results will be available upon completion when you return. You may notice the following tip: Closing the page/window does not cancel the process.
When the tuning is complete, you will be navigated to “Advisor Central” where results are displayed. There are charts and graphs that show various aspects of the execution.
Two aspects reported are “Statistics Finding Summary” and “Index Finding Summary”. In many cases it will identify missing statistics on “DR$…”, or other Oracle internal, objects.
These objects are NOT to have statistics, so ignore.
Individually examine SQL “with findings”, “without findings” and “Skipped Due to Errors”.
Do not make the mistake while examining of clicking on the SQL ID. That will only show you the SQL and not any results or recommendations. Use the radio button to determine which SQL you want to view, then select it with the “View Recommendations” button.
From this page you will view one or more recommendations which may be type “SQL Profile”, “Alternate Plans”, or “Restructure SQL”. The one example I used to write this document had ten recommendation, from which one will be selected.
Obviously, the one with the biggest “benefit %” should be looked at first. If a new plan is suggested, review it. It is easier to jump straight to “Compare Explain Plans” as the new plan is there, too.
Compare plans.
Some recommendations are just wrong. Review the graphs provided, and try to understand what is going on with the SQL, and/or why the recommendation has been made. If it does not make sense, and the example I am writing from does not make sense, review the alternate suggestions.
If you get odd results, consider whether it is because of
Review the other options presented, or move on to the next SQL.
If the best result has a recommendation of “Consider accepting the recommended SQL profile. No SQL profile currently exists for this recommendation.”, do so by pushing the “Implement” button.
When a plan is accepted, a profile is created that tells Oracle to never use a plan with a result (cost) worse than this one; but, to use another one if it is better. It is obviously not “better” today, or it would have been reported. Changing statistics over time may produce a new plan at any time.
It is an iterative process. It is probably not practical to “Implement All SQL Profiles” from any particular run; but, over time, as you gain confidence in its’ ability to create good plans, you may want to make this automatic.
Remember that Oracle is also, automatically running the tuning advisor on SQL it determines may need tuning. These plans and profiles may be accepted or rejected, as required.
Monitoring the set created above in this document, after an hour with nobody really on the system using ESM, the SQL count for the set remains at zero.
On the page where SQL Tuning Sets are listed you will notice buttons for “Export” and “Import”. We have not used this feature yet; but, it will be used as SQL Tuning Sets are moved from one instance of
There are many features and nuances of using this tool. This represents enough to get started. It is recommended to have a “snipping tool” installed to capture results to be reviewed or shared. Do remember, Internet Explorer, Not Firefox or Chrome.
Appendix A – Quick Configuration Bullet List
It is not recommended that you start with this list. The administrator should understand each task and the contents of files, commands and procedures being used.
Configuration can be accomplished by doing the following:
- as root, edit /etc/sysctl.conf file replacing line starting with kernel.sem and add comments, as follows:
# semaphores: semmsl, semmns, semopm, semmni
# for Oracle11g with 300 processes (set in spfile)
kernel.sem = 310 39680 310 128
- restart the Linux server to implement the changes
- shutdown -r -t 0 now
- edit $ORACLE_HOME/hs/admin/extproc.ora, add the line (or modify existing value)
- SET EXTPROC_DLLS=ANY
- place listener.ora and tnsnames.ora files in $ORACLE_HOME/network/admin
- using “lsnrctl” stop and start the listener
- no connections are lost doing this, only disallowed during the down time
- reload did not change extproc1521 to extproc when used on trunk
- place pfile (init
.ora) in $ORACLE_HOME/dbs
- restart the database from SQL*Plus using the following sequence and commands:
- shutdown immediate
- startup pfile=/u01/app/oracle/product/11.2.0/oraDB1/dbs/init.ora
- create spfile from pfile ;
- shutdown immediate
- startup
- disable removed.removed_stats_job
- END OF BULLETED LIST –
******************** End of Document *******************