Limiting the Degree of Parallelism via Resource Manager and a gotcha

This might be something very obvious for the reader but I had an interesting revelation recently when implementing parallel_degree_limit_p1 in a resource consumer group. My aim was to prevent users mapped to a resource consumer group from executing any query in parallel. The environment is fictional, but let’s assume that it is possible that maintenance operations for example leave indexes and tables decorated with a parallel x attribute. Another common case is the restriction of PQ resource to users to prevent them from using all the machine’s resources.

This can happen when you perform an index rebuild for example in parallel to speed the operation up. However the DOP will stay the same with the index after the maintenance operation, and you have to explicitly set it back:

SQL> alter index I_T1$ID1 rebuild parallel 4;

Index altered.

SQL> select index_name,degree from ind where index_name = 'I_T1$ID1';

INDEX_NAME		       DEGREE
------------------------------ ----------------------------------------
I_T1$ID1		       4

SQL>

Pimp my collectl-advanced system monitoring using collect-utils part I

I have recently written about collectl, a truly superb troubleshooting utility, in a previous post. After comments from Mark Seeger (the author) and Kevin Closson (who has used it extensively and really loves it), I have decided to elaborate a bit more about what you can do with collectl.

Even though it’s hard to believe, collectl’s functionality can be extended by using the collectl-utilities from sourceforge, available here: http://collectl-utils.sourceforge.net/

Like collectl, you can either download a source tgz file or a noarch-RPM. Collectl-utils consist of three major tools, out of which I’d like to introduce the first one: colplot. When finding time I’ll create a post about the other part, most likely about colmux first.

colplot

I mentioned in said previous post that you can use the “-P” option to generate output in a plot format. This in turn can be fed to your favourite spreadsheet application, or alternatively into gnuplot. When chosing to use a spreadsheet application, it’s your responsibility to decide what to do with the raw data, each time you load a plotfile. Maybe, one day I’ll write a collectl-analyzer which does similar things to nmon-analyzer, but that has to wait for now. So if you are lazy like me, you need another alternative, and it comes easily accessible in the form of gnuplot.

An introduction to collectl

Some of you may have seen on twitter that I was working on understanding collectl. So why did I start with this? First of all, I was after a tool that records a lot of information on a Linux box. It can also play information back, but this is out of scope of this introduction.

In the past I have used nmon to do similar things, and still love it for what it does. Especially in conjunction with the nmon-analyzer, an Excel plug in it can create very impressive reports. How does collectl compare?

Getting collectl

Getting collectl is quite easy-get it from sourceforge: http://sourceforge.net/projects/collectl/

The project website including very good documentation is available from sourceforge as well, but uses a slightly different URL: http://collectl.sourceforge.net/

I suggest you get the archive-independent RPM and install it on your system. This is all you need to get started! The impatient could type “collectl” at the command prompt now to get some information. Let’s have a look at the output:

$ collectl
waiting for 1 second sample...
#<--------CPU--------><----------Disks-----------><----------Network---------->
#cpu sys inter  ctxsw KBRead  Reads KBWrit Writes   KBIn  PktIn  KBOut  PktOut
1   0  1163  10496    113     14     18      4      8     55      5      19
0   0  1046  10544      0      0      2      3    164    195     30      60
0   0  1279  10603    144      9    746    148     20     67     11      19
3   0  1168  10615    144      9    414     69     14     69      5      20
1   0  1121  10416    362     28    225     19     11     71      8      35
Ouch!

The “ouch” has been caused by my CTRL-c to stop the execution.

Collectl is organised to work by subsystems, the standard option is to print CPU, disk and network subsystem, aggregated.

Getting up and running with Universal Connection Pool – Take 2

In yesterday’s post (which is actually didn’t want to post that day) I wrote about the Universal Connection Pool feature. You should be able to get started with the information I gave you, but it didn’t include any hints on how to have a look under the covers of UCP. This can be changed …Oracle includes very fine-grained logging information with UCP, but experiment show that you have to either use log level FINE or FINEST to get to the real information of what’s going on.

Getting up and running with Universal Connection Pool

Oracle’s next generation connection pooling solution, Universal Connection Pool, can be a bit tricky to set up. This is especially true when a JNDI data source is to be used-most example don’t assume such a scenario. A lot of information is out there on the net, but no one seems to have given the full picture. During the research for chapter 11 of “Pro Oracle Database 11g RAC on Linux” I learned this the hard way. Since the book has been published, a few minor changes changes have been made to the software I used at the time, and those merit an update. Please note that this article’s emphasis is to get  this example running-it is by no means meant to be secure enough for a production release! You need to harden  the setup considerably for production, but it serves well for demonstration purposes (only).

THE SETUP

I have used a four node 11.2.0.2 RAC system as the source for my data. A 2 node cluster database with service “TAFTEST” runs on nodes 1 and 2. It’s administrator-managed and the service has both nodes set aside as “preferred” nodes. The database nodes run Oracle Enterprise Linux 5.564bit with RAC 11.2.0.2. For the sake of simplicity, I used my Windows laptop to host the Tomcat instance, which is now updated to version 6.0.30. I am using apache Ant to build the application. The current stable ant build is 1.8.2. My JDK is also upgraded to the latest and greatest, version 1.6.0_23. I am using the 32bit 11.2.0.2 client package to supply me with ons.jar, ojdbc6.jar and ucp.jar.

Viewing Runtime Load Balancing Events

Yesterday I have run a benchmark on a 2 node RAC cluster (ProLiant BL685c G6 with 4 Six-Core AMD Opteron 8431) and 32G RAM each. It’s running Oracle Grid Infrastructure 11.2.0.2 as well as an Oracle 11.2.0.2 database on Oracle Enterprise Linux 5.5 64bit and device-mapper-multipath.

I was testing how the system would react under load but also wanted to see if the Runtime Load Balancing was working. The easiest way to check this is to view the AQ events that are generated for a service if AQ HA notifications is set to true. They can either be dequeued from the database as described in chapter 11 of Pro Oracle Database 11g RAC on Linux or alternatively queried from the database. The latter is the quicker method and this article will focus on it.

Before you can make use of Runtime Load Balancing you need to set at least 2 properties in your service:

• Connection Load Balancing Goal (either SHORT or LONG)
• Runtime Load Balancing Goal (SERVICE_TIME or THROUGHPUT)

.Net applications require AQ HA notifications to be set to true as these can’t directly make use of Fast Application Notification (FAN) events as said in the introduction. My JDBC application is fully capable of using the FAN events, however as you will see later I am using the AQ notifications anyway to view the events.

Incrementally gathering statistics in 10g

One of the cool new features of the Oracle 11g database is that you can copy statistics and incrementally gather global stats for rlt (really large tables). I wanted to do some testing around these features in 10.2.0.4 but ran into a number of problems. So this is the story…. I have decided to split the blog post into two parts, the first which you are currently reading will detail the installation of the patch on a single instance 10.2.0.4 database with PSU 1 installed, the second post will go through an example where I made use of the new feature.

Restore table statistics

This can be a common problem for production DBAs. Let’s assume that query performance has deteriorated after gathering of statistics. Since Oracle 10g the stats are automatically preserved (check select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual to find out for how long) and can be restored. In 11.1 and later, it’s even better in the way that you can defer statistics publishing (see this post for more information).

This is actually dead easy! Assume the following, very very basic setup:

SQL> create table s (id number);

Table created.

Let’s gather statistics for it, the table is empty.

SQL> exec dbms_stats.gather_table_stats(user,'s')

PL/SQL procedure successfully completed.

The view dba_optstat_operations lists the stats gathering operations against targets. As an interesting side effect, if you specify the table/index name in dbms_stats in lower case, the target column will list it in lower case as well.

Explain plan doesn’t peek at variables

I was trying to experiment with the 11g SQL Plan Management feature when stumbing across this. You will most likely go “ohh-of course it doesn’t” but I didn’t know it for sure until now. It’s another reason the expected explain plan differs from the actual explain plan, as discussed on the oracle-l mailing list recently.

My test setup is 11.2.0.1 on RHEL 5.2 32bit, running para-virtualised on my laptop; your system might be different but I assume the general principle is still the same. So here we go:

SQL> desc test2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER

SQL> var i number;
SQL> exec :i := 100

PL/SQL procedure successfully completed.

SQL> alter session set events '10053 trace name context forever,level 1';

Session altered.

SQL> explain plan for select id from test2 where id = :i;

Explained.

In the trace file (which again changed quite a lot from 10.2 I use in production), you find the bind variable section:

*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
 Bind#0
 oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
 oacflg=00 fl2=1010000 frm=01 csi=46 siz=32 off=0
 kxsbbbfp=00e62024  bln=32  avl=00  flg=05

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "TEST2"."ID" "ID" FROM "MARTIN"."TEST2" "TEST2" WHERE "TEST2"."ID"=TO_NUMBER(:B1)
kkoqbc: optimizing query block SEL$1 (#0)

You will notice the absence of the value of the bind variable. When I did my testing I randomly executed quieries with the explan plan option and without so this hasn’t become apparent at first. Re-executing the same statement without “explan plan” prefix yields the following result:

*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
 Bind#0
 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
 oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0
 kxsbbbfp=007cca70  bln=22  avl=02  flg=05
 value=10

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "TEST2"."ID" "ID" FROM "MARTIN"."TEST2" "TEST2" WHERE "TEST2"."ID"=:B1
kkoqbc: optimizing query block SEL$1 (#0)

Here we have the value in the relevant column. By the way, finding your trace file has become trivial in 11.1 and later:

SQL> select value from v$diag_info where name = 'Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3291.trc

Right, I agree this isn’t shaking the Oracle world but it cost me half an hour when developing my test case so I thought I’d share this with you. Bear in mind that your actual execution plan most likely will be different from the explain plan (with bind variables) so testing ACS and other new features will need to make use of dbms_xplan.display_cursor to fetch the plan from the v$ views.

Selectively purging the shared pool

Another very useful thread has developed on the oracle-l mailing list. On a side note, this is really _the_ list to be subscribed to-there are so many experienced Oracle DBAs out that it’s pure joy.

But I digress, back to the problem I often face when performance troubleshooting systems is to purge a SQL statement from the shared pool. Most often, bind variable peeking in conjunction with an empty partition (or otherwise inappropriate stats) caused the optimiser to choose a really inadequate plan for subsequent executions of the same SQL ID.

Oracle 11.1 extended DBMS_SHARED_POOL to include a “purge” function which can be used to manage the shared pool. It’s actually a lot more powerful than just purging SQL plans but that’s for another blog entry :) Anyway, if you are interested, additional documentation can be found in metalink notes 457309.1 and 751876.1. If anyone can shed more light on the various heaps you can pass as an argument, please comment here!

The new version of the package has been back ported and included in the 10.2.0.4 patch set (where it is event protected) and can also be downloaded for 10.2.0.2 and 10.2.0.3-search for patch 5614566 on metalink.

Prior to this, I usually used the “comment on table <owner>.<table_name> is ‘<some comment>’ to force an invalidation of the statement in v$sql. Next time the statement is executed you’d hope that you have escaped the bind variable peeking disaster.

Kerry Osborne, a great source for inspiration has posted an easy way to using it. The following is shamelessly taken from the mailing list, I won’t take any credit for it (but I’ll use it whenever I can!)

Beginning the quote, slightly amended to better fit into context:

Heap 0 flushes the whole statement, heap 6 flushes the plans (although I’ve never had a reason to flush just the plans. By the way, when heap=6, plans for all children are flushed.

The backport to 10.2.0.4 seems to work fine as well, but it doesn’t appear to work in versions prior to 10.2.0.4 (10.2.0.3 for example).

Here’s the script I use (because I don’t like the funky “address,hash_value” format that the purge procedure requires:

-- flush_sql.sql
DECLARE
 name varchar2(50);
 version varchar2(3);
BEGIN
 select regexp_replace(version,'\..*') into version from v$instance;

 if version = '10' then
 execute immediate
 q'[alter session set events '5614566 trace name context forever']'; -- bug fix for 10.2.0.4 backport
 end if;

 select address||','||hash_value into name
 from v$sqlarea
 where sql_id like '&sql_id';

 dbms_shared_pool.purge(name,'C',1);

END;
/

If you want to have the flexibility to select the heap – change the call to purge like this:

dbms_shared_pool.purge(name,’C',power(2,&heap));

Then just put in 0 or 6 when prompted for the heap.

End qoute. Credit where credit is due:

Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com <http://kerryosborne.oracle-guy.com&gt;