Search

Top 60 Oracle Blogs

Recent comments

April 2009

Statistics Management for PeopleSoft Temporary Records in Application Engine Programs

Last year, I wrote about Oracle Optimizer Statistics and Optimizer Dynamic Sampling with PeopleSoft Temprorary Records. Earlier this year, I encountered a situation where Optimizer Dynamic Sampling was not sufficient, and I did need properly gathered statistics on an object. I modified my PL/SQL packaged procedure wrapper which can be called by the %UpdateStats PeopleCode macro via a customised DDL model to collects statistics.

I still recommend locking statistics on PeopleSoft Temporary Record, so that table is omitted from schema or database wide operations to refresh statistics. If the statistics on a table are locked, and it is not a Global Temporary Table, then the wrapper package will force collection and update of statistics on the table (previously it suppressed gathering of statistics on tables with locked statistics).

However, when the Application Engine program completes, any statistics collected on those temporary tables are no longer needed. Worse, the statistics refer to data that will be deleted and replaced by some future program, and if the table were not reanalysed, the statistics would be misleading, and could cause the database to produce an inappropriate execution plan. Some temporary records are shared by multiple programs, so you cannot guarantee that statistics will always be refreshed when the table is next used.

Statistics Management for PeopleSoft Temporary Records in Application Engine Programs

Last year, I wrote about Oracle Optimizer Statistics and Optimizer Dynamic Sampling with PeopleSoft Temprorary Records. Earlier this year, I encountered a situation where Optimizer Dynamic Sampling was not sufficient, and I did need properly gathered statistics on an object. I modified my PL/SQL packaged procedure wrapper which can be called by the %UpdateStats PeopleCode macro via a customised DDL model to collects statistics.

I still recommend locking statistics on PeopleSoft Temporary Record, so that table is omitted from schema or database wide operations to refresh statistics. If the statistics on a table are locked, and it is not a Global Temporary Table, then the wrapper package will force collection and update of statistics on the table (previously it suppressed gathering of statistics on tables with locked statistics).

However, when the Application Engine program completes, any statistics collected on those temporary tables are no longer needed. Worse, the statistics refer to data that will be deleted and replaced by some future program, and if the table were not reanalysed, the statistics would be misleading, and could cause the database to produce an inappropriate execution plan. Some temporary records are shared by multiple programs, so you cannot guarantee that statistics will always be refreshed when the table is next used.

OS Thread Startup

Recently I encountered a performance problem scenario where a simple sqlplus “/ as sysdba” took about 2minutes to finish, this is critical to the client’s business because they have a local C program that loads Call Detail Reports on the database making use of local authentication for most of its operations and Sql*Loader to load the data, so this “2minutes of waiting” when accumulated greatly consumes significant time on their operations and greatly impacts the business.

When I arrived on the client I first checked the alert logs of both ASM (they have a separate home for ASM) and RDBMS, there were no errors…

Then I checked on the server to see if there were any CPU, IO, memory, swap, and network bottlenecks going on

The CPU run queue was zero and most of the time 90% idle

The disks were also most of the time idle

The memory utilization was low with 430MB free

Issues with current trend

In my last post I have introduced the code classification used by the Helsinki declaration (as opposed to MVC used by JEE):User Interface (UI) code: all code that creates UI and responds to events in the UI, the same as JEE's View and ControlData Logic (DL) code: all code that maintains data integrity constraints, a well defined subset of JEE's ModelBusiness Logic (BL) code: all other code, the

Single Instance and RAC Kernel/OS upgrade

This document will serve as a guide for the Kernel and OS upgrade activities for

  1. Single Instance on ASM using raw devices
  2. RAC with ASM (using ASMlib) and OCFS2

Upgrading the Kernel and OS is easy and will just need some few commands. The critical part is the dependencies once the Kernel gets updated, so if you’re using ASMlib and OCFS2 you’ll notice that after the upgrade they’re not working anymore… you can’t startup the ASM, then if your OCR and Voting Disk are on OCFS2 the CRS stack wont start all because the RPMs of ASMlib and OCFS2 are kernel dependent, also there are similar components/softwares that are kernel dependent so you have to check them out and do a risk analysis before doing the upgrade.

Last call for C. J. Date course

Note added 3 April 2009: When I wrote this post, we were counting down toward 2 April as the date for our preliminary go/no-go decision. That date is now behind us, and we have made the preliminary decision to Go. We are accepting further enrollments. —Cary Millsap

Thursday 2 April 2009 is our last call for enrollment in C. J. Date's course, "How to write correct SQL, and know it: a relational approach to SQL." I'm looking forward to this course more eagerly than anything I've attended in the past ten years, ...maybe twenty.

SQL and I never really got along too well. When I first joined Oracle Corporation in 1989, I was new to relational databases. I had done one hierarchical database project in college. I enjoyed the project ok, but it wasn't something I ever wanted to do again. When I joined Oracle, I didn't know much about relational technology or SQL. In my formative first couple of years at Oracle, though, I just never learned to like the SQL language. Prior to my Oracle career, I designed languages and wrote compilers for a living. From a language design standpoint, it just seemed that SQL (at least "Oracle SQL") could have become something really cool, but it didn't. For Oracle to treat an empty string as NULL, for example, is a decision which I still can't believe made it into the light of day...

I had a lot of respect over the years for the people I met who knew how to make SQL do what they wanted it to do. Dominic Delmolino was one of the first people I ever met who could make SQL do things I had no idea it could do. I'm still amazed when I see the things that Tom Kyte can do with SQL. I was never one of the SQL people.

Automatically Granting Privileges on Newly Created Tables (continued)

Following this posting it was put to me that you could get Application Designer to build scripts with the commands to add the privilege by adding a second command to the create table DDL model, like this:

CREATE TABLE [TBNAME] ([TBCOLLIST]) TABLESPACE [TBSPCNAME] STORAGE (INITIAL **INIT** NEXT **NEXT** MAXEXTENTS **MAXEXT** PCTINCREASE **PCT**) PCTFREE **PCTFREE** PCTUSED **PCTUSED**;
GRANT SELECT ON [TBNAME] TO psreadall;

Yes, this does work when creating the table. The additional command is put into the create table script generated by Application Designer

DROP TABLE PS_PERSON
/
CREATE TABLE PS_PERSON (EMPLID VARCHAR2(11) NOT NULL,
BIRTHDATE DATE,
BIRTHPLACE VARCHAR2(30) NOT NULL,
BIRTHCOUNTRY VARCHAR2(3) NOT NULL,
BIRTHSTATE VARCHAR2(6) NOT NULL,
DT_OF_DEATH DATE,
LAST_CHILD_UPDDTM DATE) TABLESPACE HRLARGE STORAGE (INITIAL 40000
NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80
/

GRANT SELECT ON PS_PERSON TO PSREADALL
/

However, the second command does not appear in the alter script.

Automatically Granting Privileges on Newly Created Tables (continued)

Following this posting it was put to me that you could get Application Designer to build scripts with the commands to add the privilege by adding a second command to the create table DDL model, like this:

CREATE TABLE [TBNAME] ([TBCOLLIST]) TABLESPACE [TBSPCNAME] STORAGE (INITIAL **INIT** NEXT **NEXT** MAXEXTENTS **MAXEXT** PCTINCREASE **PCT**) PCTFREE **PCTFREE** PCTUSED **PCTUSED**;
GRANT SELECT ON [TBNAME] TO psreadall;

Yes, this does work when creating the table. The additional command is put into the create table script generated by Application Designer

DROP TABLE PS_PERSON
/
CREATE TABLE PS_PERSON (EMPLID VARCHAR2(11) NOT NULL,
BIRTHDATE DATE,
BIRTHPLACE VARCHAR2(30) NOT NULL,
BIRTHCOUNTRY VARCHAR2(3) NOT NULL,
BIRTHSTATE VARCHAR2(6) NOT NULL,
DT_OF_DEATH DATE,
LAST_CHILD_UPDDTM DATE) TABLESPACE HRLARGE STORAGE (INITIAL 40000
NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80
/

GRANT SELECT ON PS_PERSON TO PSREADALL
/

However, the second command does not appear in the alter script.

A unique opportunity...

Learn SQL from one of the original SQL'ers.  Chris Date is doing some a seminar in conjunction with Cary Millsap's company Method-R.  Unfortunately I cannot make it that week (I'd be there if I could) - but it is a rather unique and uncommon offering.  I've often said that if you want to be able to "tune" SQL, you need to understand what is happening underneath the covers, what is available.  You don't want a 10 step checklist, you want knowledge.  That is what this is all about.

This article by Cary sums up the seminar as well as why it will be good (in his humble opinion)...  Give it a look see...