dbaStreet

On any database server, there is often only a limited amount of resources (read cpu, memory, i/o). Oracle database servers are often subjected to highly concurrent, resource consuming, queries issued by database client programs. This often leads to contention for cpu, memory and i/o resources on a server. Once you have way more processes on a system actively executing, compared to the number of cpu cores you have, at some point the scheduling of these processes becomes inefficient at the operating system level.

The oracle Database resource manager can be used to throttle the Number of oracle database sessions actively running on a database, or to limit the amount of cpu resources a session gets, or to limit the degree of parallelism each session gets among other things.

Below are the steps to setup a resource plan that

Limits, maximum Degree of Parallelism each session can have, at 64.

Limits the maximum number of Concurrent active sessions that can be present for a user to 2. (The other sessions get queued).

Create a Consumer Group

Login to Enterprise Manager, click on the “Server” tab.

Under the “Resource Manager” section, click on “Consumer Groups”

Click on the “Create” Button

Enter a Name and Description for the Consumer group

Click on the “Add” button to add a database user to the consumer group

Check the box to the right of the user you want to add and click on “Select”

Now click on the “Database Instance” bread crumb to return to the “Server” tab

Click on “Consumer Group Mappings”

Click the radio button on the row that has the value “Oracle User” in the “View” column.

Click on “Add Rule for Selected Type”

In the drop down list “Selected consumer group”, choose “DOP_GROUP”.

In the “Available Oracle User” list, click on the user you want to add to this “DOP_GROUP” group and click on “Move”

Click Ok.

Click on Apply

Click on the “Database Instance” Breadcrump to return to the “Server Tab”

Click on “Resource Plans”

Click on “Create” to create a new resource plan

From the table “Available Group/Subplans” choose “DOP_GROUP” and click on Move. Click Ok.

Click on the tab named “Parallelism”

For the DOP_GROUP set the maximum degree of parallelism to be 64

Click on the tab named “Session Pool”

Set the number of active sessions to “2″ (Or any number you want (In the screen i have used 12))

Click “Ok”

In the “Resource Plans” screen, choose the DOP_PLAN, In the actions drop down list, choose “Activate” and click on Go.

Now the resource plan “DOP_PLAN” is active.

Let us say now we submit a bunch of queries (Let us say 20) simultaneously (That use parallel query)as the database user HR then

Only 2 of them will be allowed to run at the same time (The rest are queued)

Each session gets a maximum Degree of parallelism of 64

There are two new awr reports in 11gr2, which will be helpful to dba’s in Real Application Clusters Environments (RAC).

awrgrpt.sql

This is a cluster wide awr report, so you can see a lot of the information from all the nodes in the same section, and you can also see aggregated statistics from all the instances at the same time (You can see totals, averages and standard deviations).

awrgdrpt.sql

This is a cluster wide stats diff report (like you had awrddrpt.sql in 11gr1), comparing the stats differences between two different snapshot intervals, across all nodes in the cluster.

These are huge additions to the awr reports, that enable understanding the database performance in real application clusters environments.

The National Institute of Standards and technology has a good, concise  definition of cloud computing. Sushil kumar of Oracle, was using the same language to define cloud computing in an article the current release of the oracle magazine.

Essential Charachteristics

• On Demand Self-Service
• Broad Network Access
• Resource Pooling
• Rapid Elasticity
• Measured Service

Service Models

• Cloud Software as a Service (SaaS)
• Cloud Platform as a Service (PaaS)
• Cloud Infrastructure as a Service (IaaS)

Deployment Models

• Private Cloud
• Community Cloud
• Public Cloud
• Hybrid Cloud

If you use perl with oracle 11g databases, you should consider using database resident connection pooling to reduce the overheads associated with connecting and disconnecting from oracle. Much has been written about how Php applications benefit by using database resident connection pooling (Because Php does not have a connection pooling mechanism of its own, unlike Java). Similar benefits can be derived by Perl Applications Too.

Mostly perl 5 applications will be using DBI and DBD – Oracle to interact with oracle databases. Since DBD – Oracle uses OCI to communicate with the oracle database, it can benefit by using database resident connection pooling.

When the database is configured for database resident connection pooling, the oracle database creates and maintains a pool of database connections. These connections are then shared by applications connecting to the oracle database. The advantage of this is that the connections are already created, so you do not incur the overhead of establishing a brand new connection to the database. You are just reusing an existing one. This is especially helpful if you have an application that establishes connections and disconnects from the oracle database very rapidly/frequently.

A connection pool can be configured and started in the database as follows

SQL> execute dbms_connection_pool.configure_pool(null,minsize=>2,maxsize=>4);

SQL> execute dbms_connection_pool.start_pool;

A connect string can be configured in the tnsnames.ora to connect to this connection pool using the following syntax

RK01POOL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rramdas-us)(PORT = 1521))
(CONNECT_DATA =
(SERVER = POOLED)
(SERVICE_NAME = RK01)
)
)

The perl program can then establish the connection to the database using this connect string in tnsnames.ora

#!/usr/bin/perl

use strict;
use DBI;
my $dbh = DBI->connect( ‘dbi:Oracle:RK01POOL‘,
‘scott’,
‘tiger’,
) || die “Database connection not made: $DBI::errstr”;

Thats all it takes, and now you can reap the benefits of using oracle database resident connection pooling with Perl.

You can use dbms_connection_pool.stop_pool to stop the connection pool in the database.

You can use the data dictionary views dba_cpool_info, and dynamic views v$cpool_cc_info, v$cpool_cc_stats, v$cpool_stats to monitor database resident connection pools.

Oracle introduced “Parallel Query” in version 7.1 in 1994. A decade and a half later, it still trips some unsuspecting customers up, when they

• Turn parallelism on via a hint in all queries

Or

• Turn parallelism on at every table and index level

Doing this, coupled with high concurrent usage of the database, can lead to a lot of query server processes being spawned, leading to very high cpu usage, high load average, high number of processes waiting on the cpu run queue and high amount of i/o requests. Such situations may result in overall degraded performance. Below are some concepts to note when using parallel query. Sometimes Too much of a good thing is indeed a Bad thing.

Parallel query (and/or parallel DML) is a mechanism to levarage the cpu and i/o resources available on an SMP system to break down a database operation (query, insert , update, delete,merge) into multiple small operations, execute the small operations concurrently on the system, completing the database operation significantly faster than if it would have executed in a serial fashion. Database operations that include large table scans, large index scans or large joins usually benefit highly from using parallel query.

Oracle does not turn this feature on by default for all queries. There are specific steps to perform to turn this on as discussed in my previous post.

The total number of parallel query server’s that can be running at any given time is determined by the initialization parameter parallel_max_servers. This parameter defaults to cpu_count*parallel_threads_per_cpu*(2 if pga_aggregate_target > 0, else 1)*5

Oracle’s recommendation is that you set parallel_max_servers to 2 * Degree of Parallelism * Number of concurrent users

The reason for the recommendation 2 * DOP is that, if the operation can use “Inter Operation” parallelism, it could end up using 2 sets of Query Server Processes at the same time.

“Intra Operation” parallelism means that one operation is executed in parallel using multiple Query Server Processes (eg: Full table scan). “Inter Operation” parallelism means that the output of one parallel operation is being pipelined to another parallel operation (eg: When there are two tables being full table scanned followed by a hash join, so here the first set of query servers could be scanning one table and the second set consuming those rows and building the hash table). So there are circumstances where the operation ends up using two sets of Query Server Processes. Hence the recommendation of 2*DOP.

You ideally want to keep the CPU usage under 70% with your load averages equal to your number of cpu’s and average run queues as close to 0 as possible. The idea is to work the server efficiently but not work it to death.

Keep in mind that on systems like sun’s t2000 and t5240 systems, each thread shows up as a cpu in cpu_count .

You can control the use of parallel query server’s by setting the initialization parameter parallel_adaptive_multiuser=true (It defaults to true). When enabled this parameter causes oracle to enable an adaptive algorithm that reduces the degree of parallelism used by queries based on the database load.

One another common mistake is that for statements like insert /*+ append */ ….select…..  users forget to enable parallel dml. When running normally the select part of the insert gets parallelized (as long as dop is set) but the insert runs in a serial fashion. Once you enable parallel dml even the insert is parallelized.

You have to explicitly issue the command ‘alter session enable parallel dml’ to enable parallel dml. You should avoid turning parallel dml ON  for all sessions and all transactions. Turn it selectively on for the sessions you need parallel dml. There are some restrictions for using parallel dml.

Consider increasing parallel_execution_message size to 4k or 8k from the default of 2k, for improved parallel execution performance.

Determining the best degree of parallelism for tables is a tough problem. You need to consider the following factors.

• What is the required query response time by the end user
• How many parallel query servers does it take to achieve that query response time
• Haw many of such large queries will concurrently execute on my server (Consider your application generated queries and ad hoc queries)
• How many CPU’s do i have on the system.
• How much i/o volumes can the system handle

Once you have the above information you need to determine a degree of parallelism that gives a reasonable query response time, when your average number of large queries execute concurrently, without clobbering your CPU, memory and i/o subsystems (Alternatively you could just determine the number of cpu’s, amount of memory, i/o channels and physical disks you need to sustain the above workload and go buy and use that hardware ).

Also see my previous post on enabling and monitoring parallel query for more parallel query info.

We have been doing some performance testing in the recent days. We were running some workload using an application, capturing awr snapshots every 15 minutes on a 8 node rac cluster. I needed to generate the awr reports between each of the snapshots for each of the nodes. Running awrrpt.sql for each 15 minute intervals for each one of the 8 nodes would have been a tedious task. So i wrote a bash shell script that generates those awr reports.

It takes the database id, instance number, start snapshot id and end snapshot id as arguments (you can query dba_hist_snapshot to find out the start and end snapshot id’s you want to use). The directory in which it generates the reports is hardcoded in the script. The script has to be run as the oracle user  and it logs in as sys to generate the reports (You can modify as you need if you want it to be run as a different user). I have tested it only on Linux.

You can run it as ./genawrs.sh dbid  instancenumber beginsnap endsnap, to generate the reports.  This means that you have to run it once for each instance you need the report for. The script can be found below.

#!/bin/bash

if [ $# != 4 ]
then
echo “Syntax  genawrs.sh dbid instanceId startsnapid endsnapid”
exit 1
fi

l_dbid=$1
l_instid=$2
l_start_snapid=$3
let l_end_snapid=$4-1

# For all snapshot id’s
# Set the next snapshot id as current snapshot id + 1
# Spool a log file
# Log into sqlplus and call dbms_workload_repository.awr_report_text
# To generate the awr

for i in `seq $l_start_snapid $l_end_snapid`
do
let l_next_snapid=$i+1;
l_awr_log_file=”/awrs/out/awrrpt_${2}_${i}_${l_next_snapid}.log”

sqlplus -s / as sysdba << EOC
set head off
set pages 0
set lines 132
set echo off
set feedback off
spool $l_awr_log_file
SELECT
output
FROM
TABLE
(dbms_workload_repository.awr_report_text
($l_dbid,$l_instid,$i,$l_next_snapid)
);
spool off
EOC
done

This is a simple calculator (Written using Javascript), that can be used to estimate the potential cost savings (As a result of reducing your total storage costs)  of using a Information Lifecycle Management (ILM) Strategy to store and maintain your oracle data.
When architecting your ILM strategy you may decide to use the following different types of storage

• High performance tier where all the important and frequently accessed data is stored (Smaller faster disks).
• Low cost storage tier where the less frequently accessed data is stored (Larger slower, ATA disks).
• Online archive storage tier where all the data that is hardly or never accessed is stored (Low cost ATA disks).

Once you decide your classifications you could partition your tables accordingly (And then place different tablespaces on different type of storage) and then use Oracle ILM Assistant to maintain the tables and their data.

This calculator can be used to estimate the cost savings you might get by implementing such a strategy. (Remember this is only a very rough first estimate. Use values that you have received from your hardware vendor to use as inputs for cost per Gb of different tiers of storage).

Click Here to Access the ILM Calculator.

All comments and suggestions, for corrections and improvements  are welcome.

From time to time, anyone who is an Oracle Database Administrator, would get summoned to troubleshoot a “database performance” issue. Once he/she has properly qualified and defined the performance issue, he/she settles down at his/her  laptop/desktop/netbook, accesses  ssh, sqlplus, enterprise manager and gets on their merry way trying to identify the root cause(s) of the performance issue and rectifying  the issue (At the same time twittering and posting facebook wall messages) (Maybe log a tar for the issue too).

In this process we often resort to using the numerous tools/concepts/techniques that are at our disposal to troubleshoot the issue. Over the years, i have had the privilege of using most of the tools/concepts/techniques that i have outlined below. These are just some of the tools and techniques that get you the best results quickly. And i know that the list is by no means exhaustive. Just wanted to give a shout out to all the DBA’s out there who carry this enormous amounts of information in their heads (And share a lot of it in blogs, collaborate,user group and openworld presentations) and apply the appropriate tools to resolve oracle performance issues.

Tools/Techniques

- AWR (Automatic workload repository)
- ADDM (Automatic database diagnostic monitor)
- ASH (Active session history)
- Real Time Sql Monitoring
- Optimizer statistics (dbms_stats)
- Extended Statistics
- Time Model Statistics
- Histograms
- Explain plan (dbms_xplan,gather_plan_statistics)
- event 10046 trace
- Tkprof
- event 10053 trace
- Oracle diagnostics pack
- Oracle tuning pack
- Sql profiles
- Sql Plan Baselines
- Stored outlines
- Optimizer Hints
- Oracle Real Applications testing (Database Replay, Sql Performance Analyzer)
- Oracle Advanced Compression (Reduces Number of blocks read)
- Oracle parallel query
- Indexes
- Partitioning
- Index Organized tables
- Clusters
- SGA Sizing/configuration
- Initialization parameters
- Cursor sharing
- SQLTXPLAIN (Metalink script)
- TRCANLZR  (Metalink script)
- OLAP cubes
- Materialized views
- Automatic storage management (ASM)
- Server Results Cache
- OCI Consistent Client Cache
- Logical/Standby , Active Dataguard (Offload queries)
- Asynchronous I/O, Direct I/O

- Table/Index Rebuild

- Patches for performance bugs

Concepts

- Database Non Idle Wait Events
- v$ views
- x$ tables
- SQL query structure/rewrite
- Sql tuning sets
- Star Schema Design

Databases grow in sizes over time, there are many reasons for this. Some of them are
1) Detailed web usage (click tracking) capture, to be used for analytics.
2) User created content in web 2.0 applications
3) Rarely used (But needed) historical data that does not get purged (maybe for compliance).
4) Application consolidation.

This means that customers needs to procure and maintain large amounts of disk storage (Read Storage Array Networks, or Network attached storage). I am not a storage expert, but i am sure that the cost of procuring and maintaining highly performant, high end storage (cost per gb per year) is very high.

With the Oracle advanced compression option, customers can reduce the space used by oracle database tables. In oracle 9i oracle introduced the ability to compress data that is bulk loaded into the database (direct path loads and create table as select statements). In oracle 11g this was expanded to compressing all data that is loaded into oracle tables.

The benefits of compression are manyfold.
1) Reduction of disk space used for storage
2) Reduction in I/O bandwidth requirements
3) Faster full table scans
4) Lower server memory usage.

You can enable compression on a table by specifying the “compress for” clause of a create table statement.

eg: create table emp (emp_id number, first_name varchar2(128), last_name varchar2(128) ) compress for all operations.

Oracle enables compression by storing a symbol table of the repeating column values in a block and then, references are stored in the rows to point to the entry in the symbol table.

Oracle compresses a block in batch mode, rather than compressing data every single time a write operation takes place. All inserts into a newly initialized block are uncompressed (So there is no difference here, while insert’s are taking place into this block). When a transaction causes the data in the block to reach the pctfree limit, the contents of the block gets compressed (Note that this is the only transaction that incurs the overhead for compression). Therefore a majority of OLTP transactions on compressed blocks will have exactly the same performance as they would with uncompressed blocks.

The compression ratio that you are going to get varies with the contents of the table. So you should use the compression advisor to determine how much storage you are going to save by using advanced compression on the table.

You can download the compression advisor from http://www.oracle.com/technology/products/database/compression/download.html. You can install it by logging in as SYS to the database and running dbmscomp.sql and prvtcomp.plb.

The compression advisor can be invoked by using a command similar to the one below.

SQL>  exec DBMS_COMPRESSION.getratio(‘SH’,'SALES’,10);

Sampling table: SH.SALES
Sampling percentage: 10%
Expected Compression ratio with Advanced Compression Option: 2.96

PL/SQL procedure successfully completed.