Feed aggregator

PL/SQL Debugging OraMag Article

Christian Shay - Wed, 2006-09-13 19:56
Check out Mark Williams' latest article in Oracle Magazine which takes you step by step through PL/SQL Debugging in Visual Studio. It also includes some sample code which you can use to try out PL/SQL debugging yourself!

This sample code shows one of the most compelling uses of this debugger -- stepping seamlessly from .NET code into PL/SQL stored procedures or functions and back out to .NET code while examining real live data as it is being passed in and out. He uses an array parameter type in the stored procedure to show how powerful visual debugging can be -- no more DBMS_OUTPUT's of every element of a 1000 element array... you can just examine the array in the Visual Studio watch window!

Oracle Riddles: What's The Point?

Eric S. Emrick - Sun, 2006-09-10 09:11
I am frequently asked for directions. Sometimes I am not the best to ask and will just be a waste of your time and energy. Other times I am sought exclusively. I try to lead a balanced life. But, hey, I am not perfect. What exactly am I?

Don't Get Caught With Your GUI Down

Eric S. Emrick - Fri, 2006-09-08 22:47
In a recent wave of interviews I was amazed how little prospective DBA candidates knew about user-managed hot backups. Most could give the BEGIN and END backup stuff and convey that it causes more redo to be generated during this time. But, when asked to give a little more of their insight into the mechanics or performance implications, 9 in 10 had plenty to say - just nothing that was correct. 90% could not explain the significance of putting a tablespace in hot backup mode. That is, why do it? Why not just copy the file while the database is open and cooking? Of course, most understood that Oracle needs us to do this so that the backup is "good", but few knew how Oracle went about doing it. Moreover, few knew why the extra redo was generated. And most amazing, nearly all thought the data files were locked and changes were written to the redo logs and reapplied when the END BACKUP command was given. Where are DBA-types reading this? Irrespective, the DBA population is evolving.

I am not basing my opinion on one simple question concerning user-managed backups, but a host of other questions given as mental exercises. What are some of the Oracle wait events? What do they represent? How would you go about troubleshooting systemic response time degradation in your production database? What is extended SQL tracing and why use it? Time after time candidates struggled to give lucid, well thought out responses. A vast majority of responses could be summarized as, "I would go into OEM and check for A or B." I don't have a problem with using OEM, but usually the A’s and B’s had little relevance to the question.

The herd of available DBAs that are able to navigate the database using native SQL to get at critical performance diagnostic information has thinned dramatically. Sometimes I wonder what would happen to some of these shops being supported by some I interview if OEM, Database Control or Grid Control took the night off. When relegated to digging into the database and troubleshooting armed only with a SQL prompt, many appear to be lost. I certainly appreciate what the Oracle GUI database management tools bring to the table. I even like them. My point is, don't throw away your shovel just because you have a snow blower. The day will come when your GUI will fail you and it will be just you and your SQL prompt.

P.S.> Oracle does not lock the content of its data files during the course of a user-managed hot backup. Actually, Oracle only locks one thing, the master checkpoint SCN inside the file header. Some other constructs in the file header stay mutable. Blocks in data files being backed up can be modified as per normal database operation. The changes to blocks are indeed recorded in the redo, but they are not replayed when the END BACKUP is issued. More redo is possible because Oracle must accommodate the potential presence of fractured blocks.

Some updates

Siva Doe - Thu, 2006-09-07 16:55

It has been a long time. Lot of water has passed under the bridge. I have moved to a new team. Part of the Sun Grid team now.
On the localisation front, if you didnt know yet, Tamil is one of the first new locale that made it to OpenSolaris. This effort jumpstarted some colleagues, Raju Alluri and Kesari Mandyam to add locales for Telugu and Kannada. Very soon, we can see OpenSolaris in Indian languages.
I am running into some translation hurdles though. My home system still does not support Solaris (old network card, Compex). So, I thought I will do the translation in Linux (Ubuntu) using gtranslator. None of the Solaris .po file (untranslated) were opening. A 'msgfmt x.po' gave many syntax errors. So, it looks like I have to get my home system with Solaris, soon. (Siva- move your lazy a$$ and get a modern NIC card). Will post you on my progress later (Not so much later, I assure you).

SIG Meeting

Stephen Booth - Thu, 2006-09-07 08:38
I've just gotten the verbal OK from my line manager to attend the Management and Infrastructure SIG meeting on 4th October. Most of the opresentations look like they will be both interesting and useful. My employer is looking to introduce ITIL so the "Can it help me? What are the pitfalls?" presentation could be useful, similarly the DBA/Database ratio presentation is highly relevant to my Stephen Boothhttps://plus.google.com/107526053475064059763noreply@blogger.com0

My Agenda for UKOUG 2006

Stephen Booth - Wed, 2006-09-06 06:38
These are the sessions I'm planning on attending at this year's UKOUG conference:08:00 - 17:45 Registration Open09:15 - 09:30 Introduction by Ronan Miles, UKOUG Chairman 09:30 - 10:30 Opening Technical Keynote: "What's Coming Next?" by Tom Kyte, Oracle10:30 - 11:00 Coffee Break11:00 - 12:00 Accountability for System Performance (introducing Six Sigma quality in Oracle software Stephen Boothhttps://plus.google.com/107526053475064059763noreply@blogger.com0

SQL Gone Wild!

Eric S. Emrick - Mon, 2006-09-04 14:22
Ever see something so inefficient it evokes images of grape stomping to produce wine? I have, in Oracle 10g no less. A colleague of mine brought me a situation the other day that made me do a double-take, no triple-take, on a 10046 trace. The scenario involved a single row delete from a table using the index associated with the primary key on said table to delete the row, simple right? Well, the delete hung. The 10046 showed "db file sequential reads" spewing at a very rapid clip. The process was reading a child table that contained a column that referenced the primary key of the table being deleted. Okay, this is to be expected. We don't want to break our self-imposed business rules by orphaning child records. So what is my beef with this situation?

The child table had millions of rows that would have been orphaned had the delete succeeded. Keep in mind the constraint was NOT defined with ON DELETE CASCADE. Also, a single column index on the child table was associated with the child key. The stage was set for a swift and proper decline by Oracle to perform our delete. But this did not happen. Oracle was visiting ALL of the child rows then returning ORA-00292 "... - child record found." Yes, each and every child index entry was being visited. My colleague opened as SR with a very elegant little test case that reproduces the problem. Here it is. Try it for yourself and watch the trace with wonder and amazement. We have performed the test in 8i, 9i and 10g with the same results.

DROP TABLE CHILD;
DROP TABLE PARENT;
CREATE TABLE PARENT (COL1 NUMBER);
ALTER TABLE PARENT ADD CONSTRAINT PARENT_PK PRIMARY KEY (COL1);
CREATE TABLE CHILD (COL1 NUMBER);
CREATE INDEX CHILD_IX_01 ON CHILD (COL1);
ALTER TABLE CHILD ADD CONSTRAINT CHILD_FK_01 FOREIGN KEY (COL1) REFERENCES PARENT;
INSERT INTO PARENT VALUES (999999999999);
INSERT INTO CHILD VALUES (999999999999);

COMMIT;

-- Insert approximately 1 million records into CHILD
begin
for i in 1..20 loop
insert into child select * from child;
commit;
end loop;
end;
/


alter session set events '10046 trace name context forever, level 12';

DELETE FROM PARENT WHERE COL1 = 999999999999;

Why doesn't Oracle stop once it encounters the first index entry indicating a foreign key violation has just occurred? Isn't a single found entry sufficient to fail my statement? It seems a bit indulgent to check each and every child row irrespective of my barbaric attempt to break my own business rules. Is it a classic case of stupid is as stupid does? Nope. It is a good old fashioned Oracle bug.

By the way, the Oracle support analyst recommended putting the index associated with a child key in a read only tablespace as a workaround. Think about that for a second...

Random training related thought

Stephen Booth - Mon, 2006-08-28 15:50
A thought just crossed my mind. I wonder if there is a market for short (one day) task oriented training for the sort of things that aren't normally covered in general courses.For example I'm aware that there are training courses that mention standby databases and certainly there are courses that cover RMAN, at least for basic backup and recovery. I'm not aware of any that actually take you Stephen Boothhttps://plus.google.com/107526053475064059763noreply@blogger.com0

Oracle Develop

Denis Goddard - Wed, 2006-08-23 21:19
I've offically been a PHB for a long time -- long enough to "ossify" (as one of my old friends back at Cummins Electronics used to put it). I know this because I find myself realizing that it's been so long since I really developed anything in detail, I don't even remember what the new features are in Oracle 10g, let alone 10gR1 or R2... (though of course I'm really looking forward to 11gR2, but that discussion will have to wait!)

As it turns out, in the next week or two, I should be able to turn my attention back -- finally -- to ADE 3.2.3, where I'm doing some rather detailed work on PL/SQL and in the Perl/XS layer. The kind of thing where you have to spend a good hour just loading your brain's cache with the details... get into the zone... and stay there for a solid 10-hour block. What Tom Christiansen called Interface Zen
Ah yes, by god, I do so miss being a programmer!

Anyway, I'm seriously considering attending Oracle Develop 2006. First, Oracle keeps coming up with tools and technologies and I have to say, in my opinion we do a good job of being driven by customer demand. Plus, the Moscone Center expos are one of the few things I really miss about living on the SF Bay peninsula.

This could be seriously fun!

OpenWorld "Schedule Builder" - Get First Dibs On Your Favorite Sessions

Christian Shay - Wed, 2006-08-23 20:17
Shay Shmeltzer's recent blog post reminds me to alert you that things are first come first serve at OpenWorld this year! Use the new Schedule Builder tool to get first dibs on your favorite .NET Oracle Develop sessions!

Anyway, Shay said it so well, and has such a great first name, that I will just plagiarize from him shaymelessly:

"But the conference is two months out, why should I do it now?" - you might be asking.

Well the reason is that this is the only way you can guarantee you get to see the sessions you want to see. This year people who register for sessions ahead of time will get into the room before people who just show up. Considering the fact that this is going to be the biggest OOW ever in terms of number of attendees - you want to secure your place. Also, I know how it goes when you don't schedule, you start the conference spending the first hour of each day trying to figure out which session to go to - and by the time you end your daily planning you already missed the morning session. Scheduling your sessions now will also help us better plan rooms allocation for popular sessions.

So do us all a favor and start scheduling now.

Thank you, Shay!

Personalization and the MDS Database Repository

Andries Hanekom - Wed, 2006-08-23 04:04
All declarative User Interface components are stored either in XML files, in a format defined by MDS (Meta Data Services) Schemas, or in the MDS repository tables. When a personalization is created through the OA Personalization Framework it is added on top of the base product meta data. The personalization does not overwrite the existing base product UI and are therefore preserved during upgrades and patches. The MDS repository is supported by the JDR_UTILS PL/SQL package, used to query and maintain the repository:

SQL> set serveroutput on
SQL>
begin
jdr_utils.listcustomizations
('/oracle/apps/icx/por/req/webui/ShoppingCartPG');
end;
/
/oracle/apps/icx/por/req/webui/customizations/site/0/ShoppingCartPG
/oracle/apps/icx/por/req/webui/customizations/org/44/ShoppingCartPG

PL/SQL procedure successfully completed.
SQL>

The MDS database repository consists out of four tables:
  • JDR_PATHS: Stores documents, packages and there parent child relationship.
  • JDR_COMPONENTS – Stores document components.
  • JDR_ATTRIBUTES – Stores attributes of document components.
  • JDR_ATTRIBUTES_TRANS – Stores translated attribute values of document components.

Training Class (Final Day)

Eric S. Emrick - Tue, 2006-08-22 20:38
To round off the material covered in this class the following topics were covered today:
  1. Tuning Block Space Usage.
  2. Tuning I/O.
  3. Tuning PGA and Temporary Space.
  4. Performance Tuning: Summary.

I found the Tuning I/O lecture somewhat interesting. The first portion of the lecture focused on the advantages and disadvantages of the various forms of RAID protection. While informative, I could've spent 5 minutes on Google had I not already been armed with the knowledge of this technology. The remainder of this lecture focused on ASM (Automatic Storage Management). This rather non-trivial feature in 10g sounds very cool; define some Data disk group(s) , the relevant protection and striping granularity and let Oracle do the all of the I/O tuning. Of course, this is a severe over simplification of what it really does (or doesn't, as your mileage may vary). But, the point is, this feature is supposed to free the DBA from the often times laborious chore of tuning the I/O subsystem. Truthfully, I think the degree to which Oracle touts the hands-off nature of this feature is overstated; especially for busy production systems. I, nor anyone in the class, had worked with the product. Consequently, I feel there are probably very few shops out there migrating their production databases to ASM. Is it more of a political battle? After all, if DBAs will be able to someday create and manage the logical volumes/file systems this might make the System Administrators feel a little encroached upon. It is just a hunch, but widespread conversions to ASM will probably not happen anytime soon. Anyone reading this blog have any good/bad experience with ASM in a production environment? I am very interested in your feedback.

The most engaging lecture of the day was the Tuning Block Space Usage. I am really keen to the Automatic Segment Space Management (ASSM) feature. This feature warrants serious consideration given the upside: free list elimination and a considerably more robust approach to reusing blocks for inserts. As much as I liked the discussion on ASSM, the subsequent topic grabbed my utmost attention: segment shrinking. What a great (and might I add way overdue) feature. If one of my production environments was on 10g today I could see using this tool to reclaim vast amounts of space in some of my very large heap tables, index-organized tables and indexes. Oracle claims that the majority of the work can be done online. Moreover, the indexes associated with your heap tables are still usable even after the row movement inherent to the SHRINK has completed. I like the idea of having the freedom to perform these "online" activities, but I still prefer to perform these kinds of operations during quite periods. The course material gives a fantastic, albeit brief, description of the mechanics. Very nice Oracle! Once again, are there any readers of this blog that have experience with this feature and want to share your experiences?

The final two lectures, Tuning PGA and Temporary Space and Performance Tuning Summary, were good, but not great. The material seemed to belabor a few points.

In summary, if you are considering taking this course I think you are best served if you do not have much 10g experience in production environments. If your experience with 10g and some of the "tuning" features is even moderate, I recommend you not take the course. Your time would be better spent reading up on this material in the Oracle documentation set.

Eric's rating of the course: B+.

Training Class (Day 3)

Eric S. Emrick - Tue, 2006-08-22 20:36
Another day of training is in the books. What was on today's menu?
  1. Tuning the Shared Pool.
  2. Tuning the Buffer Cache.
  3. Automatic Shared Memory Management.
  4. Checkpoint and Redo Tuning.

Apparently, Oracle is migrating some of its serialization protection from latches to mutexes. For example, the structures previously protected by the Library Cache Pin latch are now protected by a mutex and evidenced by the cursor:pin S wait event. Actually there are several new mutexes and mutex related wait events new to 10g. For example:

- cursor:mutex indicates mutex waits on parent cursor operations and statistic block operations.

- cursor:pin events are waits for cursor pin operations (library cache pin now protected by mutex).

There are a couple interesting facts about Oracle and mutexes. A mutex get is about 30-35 instructions, compared to 150-200 instructions for a latch get. Also, a mutex is around 16 bytes in size, compared to 112 bytes for a latch in Release 10.2 (in prior releases, it was 200 bytes).

One of the appeals of the mutex, per the documentation, is the reduced potential for false contention. That is, a mutex can protect a single structure; often times stored with the structure it protects. However, latches often protect many structures (see cache buffers chain latch) and can yield what the documentation calls false contention. It is called false contention because "the contention is for the protection mechanism rather than the target object you are attempting to access." This all sounds really great, right? Well, maybe. If Oracle goes to more widespread use of mutexes instead of latches to protect target objects that would be a boatload more mutexes. I am sure the porters at Oracle are not intending to use mutexes exclusively in the future. But, I can see where contention in Oracle could be dramatically reduced at the cost of CPU cycles and memory. What would happen if Oracle protected each buffer with a mutex? While each mutex is less expensive with regard to memory and CPU than an individual latch, you will need considerably more mutexes for each replaced latch. 50 mutexes used to replace a single latch could run the CPU up considerably for the "same" application workload.

I have one final note on mutexes. As of version 10.2.0.2 a SELECT against V$SQLSTAT and searches of child cursor lists are mutex protected.

I found the Tuning the Buffer Cache discussion somewhat interesting. Unless you have been hiding under a rock the past 4-5 years, I am sure you have heard the Oracle experts preaching the notion that ratios are not very helpful in diagnosing the health of a database. In particular, the buffer cache hit ratio is frequently tagged as meaningless. A smile came to my face when I read the following excerpt from the course material:

"A badly tuned database can still have a hit ratio of 99% or better...hit ratio is only one part in determining tuning performance...hit ratio does not determine whether a database is optimally tuned..."

Oracle is finally teaching what the experts have been saying for years!

I have been to several Hotsos events/training classes. They often talk about the need to include the buffer is pinned count statistic in the tally for logical reads. These operations are simply latch-reduced logical reads. Why doesn't Oracle integrate this information into their course material or documentation set? They still only claim that db block gets and consistent gets constitute logical reads. I monitored a process recently in one of my production environments and noticed the process did 2 buffer is pinned count logical reads for every 1 (db block gets + consistent gets). That is a substantial percentage of work owed to operations not officially categorized as a measure of work by Oracle.

Lastly, the on-topic impromptu discussions were fruitful. That always makes the training session more interesting :)

SAOUG Conference 2006

Andries Hanekom - Thu, 2006-08-10 02:15

Just got some good news, I have been accepted as a speaker at the South African Oracle User Group Conference 2006. I will be presenting my paper - "Oracle Applications Framework Development - The New Frontier", which is an introduction to the world of OAF development, personalization and extension. My presentation will be a 45 minute session scheduled for 9:55 AM Wednesday 27 September, so don’t miss it.

The conference is held at Sun City and runs from the 25th to the 27th of September, if you are interested in attending visit the SAOUG website at: http://www.saoug.co.za


Search Terms

Stephen Booth - Mon, 2006-08-07 16:59
Doug Burns recently posted in his journal a list of common search terms that people have used where his journal appeared in the results. I figured I'd post mine (according to Statcounter.com). Fortunately (or unfortunately) mine are not anywhere near as exciting as his.Perc. Search Term5.80% ora-074454.35% orapwd invalid username password2.90% exception signal: 11 oracle2.90% alter user sys2.90Stephen Boothhttps://plus.google.com/107526053475064059763noreply@blogger.com0

Problem with PUTTY connection to SUSE9

Wijaya Kusumo - Fri, 2006-08-04 06:22
When I tried to connect to SUSE9 via Putty from my windows 2000, for some reasons I kept getting the Access denied message. Since connecting from Cygwin is ok, then the problem should be on the Putty. After searching for a while, here is how I fixed the connection issue: 1. Update my Putty to the latest version: 0.58. 2. Open Putty. Load the Saved Session to the SUSE server. 3. In the Category

Instructive Presentation on Logical I/O Mechanics

Eric S. Emrick - Tue, 2006-08-01 17:40
If a picture says a thousand words than a good animation can say ten thousand. Check out this offering by Julian Dyke. His presentations relating to Oracle mechanics still reign supreme in my book. Once you see a mechanical concept "in motion" you simply don't forget it. What a great didactic device. Anyway, I just wanted to pass this along. Enjoy.

Training Class (Day 2)

Eric S. Emrick - Wed, 2006-07-26 08:38
The second day of training was much better than the first. I suspected it would get better based on the material to be covered. The topic set de jour was:

  1. Metrics, Alerts and Baselines.
  2. Using Statspack.
  3. Using Automatic Workload Repository.
  4. Reactive Tuning.

Having limited exposure to 10g in any true production environment, I found 75% of these topics interesting (Statspack chapter was not valuable to me). I really like what Oracle has accomplished with 10g with regard to the gathering and reporting of statistics and metrics (the rates of changes for given statistics). About 5 years ago I wrote a utility for 9i that allowed me to compare Oracle-captured statistics and wait event durations to similar reference points. This utility, I dubbed AppSnap (written in PL/SQL), captured the statistics and wait event durations each hour and calculated and stored the deltas in a separate tablespace. This permitted me to compare what is considered "typical" load to current load and evaluate the deviations rather quickly. I wrote a Unix shell script reporting tool called Instance Health that reports each hour the deltas as they relate to what I call peer hours. For example, each hour a report is generated as a text file and stored in a log directory. The most previous delta is compared to the same hour of day for the past 30 days, the same hour of day and day of week for the past 12 weeks and against all hours for the past 30 days. This has proved to be very valuable for detecting systemic anomalies after application upgrades, etc.

Okay. Now Oracle has come along with 10g and provides the same functionality (albeit not free). I appreciate the graphical conveyance of this type of analysis provided by Enterprise Manager. Shoot, Oracle even calculates the variance within the sampled timeframe for each metric. This is really cool because you can easily write a query that can ascertain if some metric is statistically anomalous (i.e. +-3 standard deviations). At first glance, some of the AWR reports are not very intuitive. But, the more you stare at them the more sense they appear to make. The Active Session History reporting is also a very nice feature (once again, not free).

If you already have considerable work experience with AWR/ASH/ADDM then this class probably won't provide you much value. The course does go into the mechanics of the data capturing and touches rather superficially on the reporting capabilities. So there is a good chance you probably have more knowledge about these products than this class affords. However, if you are like me and have yet to dig in your heels on a 10g production environment this class could serve as a very nice primer.

Well, I am off to day 3 of this training class.

Oracle Workflow Notifications & OAF

Andries Hanekom - Wed, 2006-07-26 03:39

If you wanted to display HTML formatted content in an Oracle Workflow Notifications before the release of FWK.H you had to use one of the following options:

  • PLSQL Documents - 32K size limit
  • PLSQLCLOB Documents – CLOB

PL/SQL development of HTML notifications that contains tables and complex layouts can be difficult and very time consuming. It requires the developer to have a very good knowledge of HTML and makes the implementation of a standard “look and feel” very difficult.

With the implementation of FWK.H we are able to leverage the power of the OAF to create multifaceted Workflow notifications. Also called JRAD notifications, FWK embedded regions is nothing more then a normal OAF region displayed in the body of a Oracle Workflow notification.

Incorporating an embedded FWK region in a Workflow notification consists of the following steps:

  1. Create an OA Component Region containing the Headers, items and tables you want to display in the body of you notification. Developing and implementing this region is done in the exact same way you would when creating a region for an OAF custom page. Remember to create a dedicated Application Module (AM) for your region that will contain all the View Object (VO) required for your layout.
  2. In core applications, create a SSWA jsp function where the WEB HTML call is: OA.jsp?page=/companyabc/oracle/apps/xx/module /webui/XxRegionNameRN
  3. Create a new Workflow attribute:
WorkFlowAttribute

Value: JSP:/OA_HTML/OA.jsp?OAFunc=XX_PO_DOCUMENT_RN_NTFN &poHeaderId=-&PO_HEADER_ID-

XX_PO_DOCUMENT_RN_NTFN is the custom function created in core apps and &PO_HEADER_ID is a workflow attribute I am passing back as a parameter to the region's controller to initialize my VO.

4. Add the new attribute to your message:

WorkFlowAttribute

Save your workflow and initialize a new instance, the notification body should now be populated by the OAF region. No messy HTML coding, quick, easy and you can reuse the region in your OAF custom pages.


VNI-2002 File operation error when submiting jobs through Enterprise Manager

Neil Jarvis - Tue, 2006-07-25 09:39
If you’ve been able to submit jobs through Enterprise Manager and then find that they are failing with VNI-2002 file operation error check out $OH/network/agent and see if you have inadvertently created a temp directory. I did, to store some files for backup. Either remove this directory or rename it. Then restart your agent and you should find your jobs now run

Pages

Subscribe to Oracle FAQ aggregator