Mike Ault's thoughts on various topics, Oracle related and not. Note: I reserve the right to delete comments that are not contributing to the overall theme of the BLOG or are insulting or demeaning to anyone. The posts on this blog are provided “as is” with no warranties and confer no rights. The opinions expressed on this site are mine and mine alone, and do not necessarily represent those of my employer.

Monday, January 23, 2006

Adjusting Array Size in Oracle SQL*Plus

The default ARRAYSIZE in SQL*PLus is 15. This will show up during the analysis of the trace files by dividing the number of rows returned by the number of SQLNet roundtrips as the default size being using in the application. The ARRAYSIZE specifies the number of rows to return when returning a set of values to the application. Since this specifies the array size to return, it directly impacts the number of round trips required to satisfy a request for data.

I ran a series of select statements using the SQL*Plus interface to a development environment and varied the ARRAYSIZE starting at 5 and working up to 200 in increments of five. The following chart shows the results for the example select statement:

SELECT * FROM dba_objects;

I utilized the SET AUTOTRACE ON STATISTICS and SET TIMING ON commands to gather statistics on net round trips and time required to satisfy the select statement.




As you can see the benefits for this select statement fall off after an ARRAYSIZE of 50 is reached. A plot of the roundtrips verses ARRAYSIZE tells why. In the following chart of roundtrips verses ARRAYSIZE setting you see that at 50 the value for roundtrips is 147 having reduced from the high of 1606 by 90%, from array sizes 50 to 200 it only decreases to 42 a change of only 10% (based on the original 1606) fewer roundtrips.

This indicates we need to adjust the ARRAYSIZE being used to retrieve data from the Oracle database, by doing so we can dramatically reduce the number of network roundtrips. In this environment where our latency is very small, you can see the change of ARRAYSIZE from a default value of 15 to a size of 50 reduced the time it took to retrieve the data from the select statement from 6.03 seconds to 3.07 seconds, nearly a 50 percent decrease in elapsed time and a reduction in network roundtrips of over 90 percent. This was on a local database with 4 ms latency imagine what a difference it would make when latency is 50 to 100 times this latency?

Where this can be of real benefit is in a Java situation. To set the array size, use the setDefaultRowPrefetch method of the OracleConnection class. However, the size should be determined empirically by setting the size and running test runs because this is a case of diminishing returns as the array size gets larger. Also, the size of network buffers and packets need to be factored in when sizing the buffers in SQL.

Friday, January 20, 2006

Much Ado About Nothing

Well actually, a NULL is UNKOWN not the absence of a value. But how would “Much Ado About Unknown” sound as a title?

I had to explain to a fellow DBA the impact of null on processing where the columns that where null were also used in joining of tables. It took a while, but finally he caught on. It made me wonder how many folks are confused by this. The major issue is that nulls are not tracked in normal indexes. It is true that while bitmap indexes may be used to track null values, they have their own issues for OLTP environments and usually have very restricted use in OLTP environments.

I decided to develop a little test case just to show what is going on with NULLs. Let’s take a look at this test case:

SQL> create table test as select * from dba_objects;

Table created.

SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

SQL> create index test_ind on test(object_id);

Index created.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> set autotrace on
SQL> select count(*) from test where object_id is not null;

COUNT(*)
----------
33732


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'TEST_IND' (NON-UNIQUE) (Cost=
19 Card=33732 Bytes=134928)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
79 consistent gets
0 physical reads
0 redo size
381 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(*) from test where object_id is null;

COUNT(*)
----------
11


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=104 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TEST' (Cost=104 Card=11 Bytes=44
)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
464 consistent gets
0 physical reads
0 redo size
379 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> create index fbi_test on test(nvl(object_id,-1));

Index created.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select count(*) from test where nvl(object_id,-1)=-1;

COUNT(*)
----------
11


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'FBI_TEST' (NON-UNIQUE) (Cost=2 Ca
rd=12 Bytes=48)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
379 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> update test set object_id=-1 where object_id is null;

11 rows updated.


Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE (Cost=104 Card=11 Bytes=44
)

1 0 UPDATE OF 'TEST'
2 1 TABLE ACCESS (FULL) OF 'TEST' (Cost=104 Card=11 Bytes=44
)


Statistics
----------------------------------------------------------
2 recursive calls
78 db block gets
465 consistent gets
0 physical reads
9208 redo size
625 bytes sent via SQL*Net to client
548 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
11 rows processed

SQL> commit;

Commit complete.

SQL> select count(*) from test where object_id=-1;

COUNT(*)
----------
11


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'TEST_IND' (NON-UNIQUE) (Cost=2 Ca
rd=1 Bytes=4)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
379 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


So what have we done in the above SQL?

1. We create a test table with nulls in the column OBJECT_ID
2. We then created a normal index on the OBJECT_ID column
3. Using IS NULL and IS NOT NULL based WHERE clauses we determined:

a. The IS NOT NULL type WHERE clause will use an index
b. The IS NULL type WHERE doesn’t use an index
4. Next we create a function base index using the NVL function to convert the NULL values into a default value.
When we use the NVL function in our SELECT in place of the IS NULL we get index usage and much better performance, however, this may lead to improper relationships between tables.
5. As a final step, we replace the NULL values in TEST with a default value of -1, now using that in our select instead of IS NULL we get response virtually identical with the use of NVL function based index.

Of course the issues we are trying to prevent with all this is processing of IS NULL type queries forcing such items as full table scans and causing developers to have to use OUTER JOIN type syntax to resolve NULL conditions between tables. By eliminating these OUTER JOIN and IS NULL processing steps which result in large HASH and full table scans in many cases, we can dramatically reduce the execution times of queries. Notice in the example how we reduced cost figures from 104 for a full table scan to 2 for an index (either our normal or the function based index) scan.

So, what should we take from this? Several conclusions can be reached:

- Whenever possible avoid the use of NULLable columns for columns that will be joined.
- Use default values in place of NULL, note that to avoid outer joins, both parent and child tables must have a value, for example if we had a parent table the related to the OBJECT_ID in our example, we would have an entry of (-1, ‘NO ID’) in that parent to resolve the -1 entries in the child.
- If IS NULL is required, consider replacing it with a NVL call and a function based index, while this may not help with outer joins, it will help with IS NULL type selects.

Have fun!

Mike

Tuesday, January 03, 2006

New Years Day Dive

I got up this morning and built a tank rack. A tank rack holds scuba tanks so they don’t do major damage to you, your vehicle or themselves rolling wily-nily about the back of your vehicle. I did this feat of carpentry legerdemain because I was going to meet up the infamous Lake Lanier Loonies for their annual New Years Day dive and picnic.

So what you are probably saying. Well, even though we are in the “south” part of the states (Georgia) our nightly temperatures hover around freezing this time of year and even today it only reached a balmy 57 degrees Fahrenheit. The lake is undergoing turnover, which means there is no warm layer, it was a uniform 47 degrees from the top down to the depths we planned on diving. Now you can see where the Loony part of the name comes from.

Even when it is at its peak summer temperatures Lake Lanier rarely gets above 60 degrees below its thermocline. On a good day you use a 3mm wetsuit. For 47 degrees you use 5-7mm full wetsuits with hood and gloves, 5-7 mm shorties with hood, boots and gloves or a drysuit. I thought I had it covered with a 5mm drysuit I had been assured was a large and would fit me after I described myself (accurately) to the seller.

The night before I had decided, since this would be the first dive of the drysuit, that I had better learn how to put the thing on without making a big fool of myself. I put on my swimsuit and a lycra dive skin (a dive skin is a lightweight suit, think of a whole-body leotard). The next step involved unzipping the drysuit, the zipper goes from the middle back all the way around between the legs to the upper chest. You begin donning the drysuit by inserting your legs into the legs of the suit, think of 5 mm (one half inch) thick panty hose with a sealing ring at the ankle and you will get the idea. The next step is to simply (right) stick your arms into the arms (again, think of 5mm panty hose for your arms with seal rings at the wrists) and at the same time thrust your head up through the neck seal into the tight hood. The neck seal is like the tightest turtleneck sweater you can imagine.

Anyway, I got the arms in, got the head started and then realized I had run out of standup room in the suit. As I stood there hunched over with the neck seal tight around my forehead I glanced on one of the inside panels where I hadn’t had a chance to look before. I suddenly realized why I had run out of room and if I had succeeded in thrusting my head through the seal I would have instantly regretted it. All it took was one small word, “Medium” scrawled in permanent marker.

Since by design I am a extra-large type of guy, had I succeeded in thrusting my size 16 and half neck into the medium size neck seal I probably would have choked to death before a I could have got the thing off. Needless to say I was a bit upset. I had expected to get two 3mm wetsuits the previous week, one for my wife and one for me, which I could have used as a 6mm combo in a pinch, but alas, they fell prey to the holiday mails so here I was, the day before the dive, a holiday, no dry or wet suit capable of making the dive. I posted to the Scubaboard website my predicament. A fellow Looney came to my rescue, seems he had a 5mm “shorty”, a suit with no boots or hood, that he could let me use and was, as I, an XL kind of guy. So, here we are back at were I began the story.

After I built the tank rack I placed into it both my personal and a rental tank, both loaded to 3000 PSI of air, and strapped it down to the bed of my Montero. We then loaded up the other dive gear, some broiled chicken and warm drinks and drove the 20 some miles to the West Bank Park at Lake Lanier where we met, for the first time, with the other Loonies.

The Loonies are a diverse lot, from instructors and dive masters to the barely qualified, nine intrepid divers ready to face the challenges of Lake Lanier, some driving for over an hour and a half for the privilege. While on shore waited our wives, bravely smiling and laughing as we struggled into our dive gear, hiding their fears, at least I hope they were just hiding their fears, I noticed none of them were diving…

We finished donning our dive gear and marched proudly, well, as proudly as you can walking backward since you are wearing fins, into the water. The suit I was borrowing was a 5 mm wetsuit, as its name implies, it is designed to allow a small bit of water in next to your skin, your body heat warms it and it acts as an insulation layer, as long as there isn’t too much water flow. Needless to say, the first five-to-ten minutes of any dive in cold water with a wetsuit are always interesting as you warm the cold (in this case 47 degree) water up to body temperature. After the first ten minutes I was actually pretty comfortable except for my hands. Afraid I only had on what are known as “reef” gloves, they are used to protect your hands from rough reef surfaces, not to keep them warm.

We finally were able to turn around once we got to mid-chest level and could start to swim out to deeper water. I had on strap-on fins since I was wearing dive boots. Usually I use a pair of full-foot fins (Velocity model) that have a bit of flex to them. The strap-on ones find felt like a couple of boards since they were much stiffer than what I was used to. So what should have been a fairly easy swim was a bit more difficult, but manageable.

Finally we got out to about 15 feet of water. I pulled my mask up over my face (only rookies keep it up on the forehead, which is actually a distress signal) and just then a wave filled my mouth with wonderful Lake Lanier Water. After coughing and hacking and scaring my dive buddies, I finally got my breath back and placed my regulator into my mouth. After a look around and a synchronization of watches we held our BCD inflator hoses over our head and releasing the air that was holding us on the surface slid like German U-boats (well, more like distressed seals) beneath the cold water’s surface.

I would like to report that we had excellent visibility and it was grand, however, I don’t like to lie. Visibility was about 6-8 feet, this is not so good, even for Lake Lanier. Lake Lanier can have visibility of 10-15 feet above the thermocline and even more below the thermocline, however, when the lake is turning over, visibility gets pretty bad. After sinking though 8 feet of moss-green water we had to switch on our dive lights. I was using one of my Christmas presents, a small UK40 light that attaches to your mask strap for hands free operation. Unfortunately when you look at other divers straight on, you end up blinding them, something my dive buddy pointed out to me after the dive by telling me if I ever used that light with it hooked to my mask strap again he would mount somewhere the light don’t shine…wonder what he meant?

Anyway, our assignment was to proceed to a structure known as “the lumber yard” which was a pile of submerged timber at a heading of 45 degrees east of the main swim marker buoys and place a dive buoy over it so one of the other divers could do a laser sighting and range calculation for a diver’s map of the features. We proceeded down the underwater incline, 20 feet, 30 feet, 50 feet when suddenly I saw the dive leader stop and start messing with his dive computer. He signaled me to come over and show him mine, which I did. It seems his had stopped displaying. We found out later his battery was at the near-end-of-life level and would work fine topside, as soon as it cooled down the voltage would drop and the computer would switch off its display.

The lumber yard was supposedly at 75 feet. After messing around along the bottom for a while we came upon some large logs, not a bunch, but a few, we assumed this was the lumber yard so we placed the buoy and swam back on our reciprocal heading. Only once did the silt we kicked up from the bottom cause me to get disoriented and temporarily lose site of my dive partners, but I rose enough to get out of the brown-silt filled bottom water and up into the murky-green water and soon spotted their dive lights and with a prayer of thanks rejoined them. It would have been the height of embarrassment to be the new-kid and get lost from my dive buddies.

Since the dive leader’s computer was malfunctioning and we were all nearing less than 1000 PSI on our tanks he decided to halt the dive and return to shore. Giving the thumbs up signal which means “let’s surface” we all three went vertical, held our inflators over our heads and vented our BCDs, kicking for the surface. If you don’t vent the BCD's they can rapidly expand as you decrease depth and rocket you to the surface, if you return to the surface too fast you risk decompression sickness, commonly called the bends. Besides, your dive computer will yell at you with rather loud beeps if you ascend too quickly and let everyone know you are being bad.

We reached the surface safely after having reached a maximum depth of 67 feet and spending 26 minutes in the cold murky water and back pedaled to shallow water, where we once again walked backward out to the shore. Walking backward like that felt like we were in a movie someone had placed in reverse. I really prefer boat dives, it is just more dramatic to do a back roll from the gunwale or a giant stride from a dive platform into the water and forcefully pulling your way up a dive ladder than this trudging backward stuff.

Now came the real hard part, taking off the wet gear. As the dive suit is exposed to air the evaporative cooling from the air can chill you quickly. Again we had the snickers and smiles from the women folk to contend with as we struggled with our gear. Of course my Susan came over and helped me, sometimes it is rather tough to get the shoulders off in a front zip suit.

After everyone else returned from the dive we went up the hill to the barbecue grill and had a wonderful picnic. All in all it was a great day. Even though the dive was cold, dark, murky and we saw nothing but some old logs, suspended algae and silt and a lot of bottom mud, it still beat a day at the office. I’ll be back next year!

New Years Dive Profile Posted by Picasa