May 2009

on the importance of a good data model ...

An article written by Bert Scalzo was published in Information Management this week. The topic is 'Is Data Modeling Still Relavant?': it's short, to the point and well worth reading.The article doesn't recommend a specific tool, it simply recommends the practice of capturing a model of the data at rest (old school approach) in addition to the newer techniques that focus on capturing the data in

Manually Booting Tuxedo Application Server Processes in Parallel

Normally when an Application Server is booted, initialisation of each process completes before the next one is started. The ability to boot Application Server processes in parallel was added to the psadmin utility in PeopleTools 8.48. However, psadmin is merely a wrapper for the BEA Tuxedo tmadmin command line utility, and it has always been possible to do this manually in previous versions of PeopleTools via the tmadmin utility as follows.

1. Boot the Tuxedo Bulletin Board Liaison process.

#boot the Tuxedo administrative processes
boot -A

2. Boot the PeopleSoft Application Server processes and but specify the -w parameter so that they don't wait as they start

boot -g APPSRV -w

If you are running PUBSUB or other servers in other groups then you would also boot them here.

3. Boot the JREPSRV process (which maps Java Classes to Tuxedo Services).

boot -g JREPGRP

4. List the servers with print server so you know that the PeopleSoft servers are booted.

psr

5. When all the other processes have booted, boot the WSL and JSL processes.

boot -g BASE
boot -g JSLGRP

Manually Booting Tuxedo Application Server Processes in Parallel

Normally when an Application Server is booted, initialisation of each process completes before the next one is started. The ability to boot Application Server processes in parallel was added to the psadmin utility in PeopleTools 8.48. However, psadmin is merely a wrapper for the BEA Tuxedo tmadmin command line utility, and it has always been possible to do this manually in previous versions of PeopleTools via the tmadmin utility as follows.

1. Boot the Tuxedo Bulletin Board Liaison process.

#boot the Tuxedo administrative processes
boot -A

2. Boot the PeopleSoft Application Server processes and but specify the -w parameter so that they don't wait as they start

boot -g APPSRV -w

If you are running PUBSUB or other servers in other groups then you would also boot them here.

3. Boot the JREPSRV process (which maps Java Classes to Tuxedo Services).

boot -g JREPGRP

4. List the servers with print server so you know that the PeopleSoft servers are booted.

psr

5. When all the other processes have booted, boot the WSL and JSL processes.

boot -g BASE
boot -g JSLGRP

Resuming transmission...

I've been busy lately: preparing papers and presentations for the upcoming ODTUG, presenting at Hotsos-revisited, and presenting at the Dutch DBA-symposium. All spare time went into these activities, and the Helsinki blog just had to wait in line. But now I intend to resume transmission over here again.A couple of weeks ago I received following comment on this blog:toon,Suppose we have a

Collaborate09 thoughts...

I've been meaning to write up my thoughts from this years IOUG Collaborate event.  Ian Abramson, the president of the IOUG, has a nice series of write ups on the event itself - starting with day 1 through day 3 and then a wrap-up post.  He describes it as a three day event - but to me it is more of a four day thing.  For the last couple of years, I've done a university day at Collaborate - so once again I spent all day Sunday May 3rd in a conference center talking about Oracle for eight hours.  This year I talked about Encryption, all of the Flashback technologies, Read & Write Consistency (a really nice way to introduce the flashback stuff actually, sort of a pre-requisite) and Database Reorganizations (when to, when not to and when you have to - how to).

I also had a one hour "regular" session on Monday where I presented on "What's new in Oracle Application Development" where I touched on general database enhancements, ODP.Net, PHP/Ruby/Python, Java/JDBC, SQL Developer and APEX advances.  This is the session where I got most of the questions/comments this year.

New job, lots of exciting stuff

It’s been a week since I started my new job at Oracle Corporation. I’m a remote worker which means that the first day of work wasn’t the usual event since I just went to my home office and got on a concall with my new manager. After getting connectivity and accounts set up properly, I was able to pretty quickly work through the new hire checklist of forms and mandatory training.

My new Oracle-provided laptop arrived around mid-week and I realized that, at least for now, I’ll have to revert back to using the Windows-based laptop and (hopefully temporarily) put my MacBook Pro on the shelf. Actually, my wife is very excited since she’ll get the MBP to use now and we’ll do the usual “trickle down” to the kids so that the oldest computer in the “fleet” will get ditched.

User objects created in the SYS schema and the (cost based) optimizer

The answer - as already disclosed by Nicolas Gasparotto - to the question that I asked here in the "Weekend Quiz" is to run the script as SYS user, and then run the query shown against these objects in the SYS schema (tested against 10g XE, 10.2.0.4 and 11.1.0.7 on Win32).

Note: It's not recommended to create any non-SYS objects in the SYS schema and you should only perform this (if at all) in a test database.

All this came up in this recent OTN forum thread where it became obvious that the issue can only be reproduced if the objects are owned by SYS.

There are two interesting points to derive from this (apart from the obvious that one should not create any user objects in the SYS schema):

1. The optimizer seems to treat objects owned by SYS differently, in particular regarding the transformations applied. Note that the crucial point is not that the query is executed as SYS user, but that the objects are owned by the SYS user. Granting appropriate privileges to a non-SYS user on the objects owned by SYS allows to reproduce the issue even with a non-SYS user.

2. It's something to remind if there is the need to understand a performance issue with a recursive dictionary query performed on SYS-owned objects. Although you obviously can't influence the SQL generated by Oracle itself it might help to understand the issue and take appropriate steps to rectify the issue.

Oh, by the way, have I already mentioned that it's really a bad idea to create user objects in the SYS schema?

Scuba diving pre-ODTUG Kaleidoscope, Monterey, 21-June-2009

I’m very pleased to report that I will be able to meet up with ODTUG Kaleidoscope attendees at both the ODTUG Community Service Day (2nd Annual!) and my own scuba dive outing as well. If you can, I’d love for you to attend both events. If you’re not a certified scuba diver, then you can at least participate in the Community Service Day festivities and help out the local area while enjoying some California weather too!

Understanding the different modes of System Statistics aka. CPU Costing and the effects of multiple blocksizes - part 4

Back to part 3

Using objects residing in multiple blocksizes

I've already mentioned it several times on my blog but I would like to take the chance here again to stress the point that the cost based optimizer does a bad job when it comes to calculating costs for full table scans of objects residing in non-default block sizes. It really looks like that this feature has been introduced to support transportable tablespaces but it obviously hasn't been tested very thoroughly when it comes to cost calculation.

Each of the different modes has its deficiencies when dealing with objects in non-default blocksizes. The somehow odd thing is that the traditional I/O costing does the best job, and all system statistics based calculations are utterly wrong.

Traditional I/O based costing

The traditional I/O based costing simply scales the MBRC up or down according to the non-default blocksize to come to the same I/O read request size. So if you e.g. have a MBRC of 8 and a default blocksize of 8KB and now calculate the cost for an object residing in a 2KB tablespace, the MBRC will be multiplied 4, which results in a MBRC of 32. The I/O cost will be different although due to the different adjustment used with the higher MBRC setting. The adjusted MBRC for 32 is 16.39 whereas the adjusted MBRC for 8 is 6.59, so the calculated cost for the full table scan of the object residing in the 2KB tablespace will be higher. Likewise the same happens when using an object in a 16KB non-default tablespace. The MBRC will be reduced accordingly to 4 to get the same I/O read size again. Since adjusted MBRC for MBRC = 4 is 4.17, the cost calculated will actually be less for the object residing the 16KB tablespace.

Weekend quiz

Quiz questions seem to be in nowadays, so here's one for you regarding the cost based optimizer:

Given this simple script (Run this on any 10.2.x (including XE) or 11.1.0.6/7):

create table test1
as
select * from dba_objects;

create table test2
as
select * from dba_objects;

create index i_test1_1 on test1(object_name);

create index i_test1_2 on test1(object_id);

create index i_test2_1 on test2(object_name);

create index i_test2_2 on test2(object_id);

exec dbms_stats.gather_table_stats(null, 'TEST1', estimate_percent=>null, method_opt=>'for all columns size 1');

exec dbms_stats.gather_table_stats(null, 'TEST2', estimate_percent=>null, method_opt=>'for all columns size 1');

And this simple query:

explain plan for
select
*
from
test1
where
object_name='TEST1'
and object_id in (
select /*+ unnest */
object_id
from
test2
);

with this plan:

Plan 1 (the obvious one):

Plan hash value: 2107173885

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 212 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 2 | 212 | 6 (0)| 00:00:01 |