Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

PowerPoint is the Enemy

I went to Starbucks about an hour ago to treat myself to a mocha (even though it was nonfat). And while waiting for my coffee to arrive I noticed the front page of The New York Times. Right smack in the middle of the page was a horrendous PowerPoint slide and the title of the article underneath read "We Have Met the Enemy and He Is PowerPoint".

Even though I knew I could get the story online when I got home, I just had to buy the paper and read it immediately.

I'm a huge believer in the concept of "death by PowerPoint". There certainly have been a multitude of presentations that I have suffered through that nearly killed me! I follow and attempt to learn from the presentation methods of Garr Reynolds, Nancy Duarte, Seth Godin and others who are out there promoting more effective ways to deliver presentations. While this article's title may paint with broader strokes than I would by saying "PowerPoint is the enemy", I mostly agree with the sentiment. PowerPoint itself may not the enemy, but surely the way it is commonly used is close to a criminal act.

Just look at that slide the article displays! Could you ever make any sense of that thing? It was shown to the General leading the American and NATO forces in Afghanistan last summer and he was quoted as saying, "When we understand that slide, we'll have won the war." Other high ranking military officers said things like "PowerPoint makes us stupid" and "It's dangerous because it can create the illusion of understanding and the illusion of control." One General even went so far as to ban the use of PowerPoint presentations!

In my experience, PowerPoint presentations have replaced meaningful documents (like white papers) and are used in an attempt to distill all the knowledge deemed pertinent on a topic into a few bullet points. The hope is that a busy person (i.e. usually an executive) can "get the idea" of what is being conveyed without having to spend much time reading something more detailed. But, the supposedly concise presentations I see most often have entire slides filled with sentences (all preceded by the infamous bullet point) that are so busily annoying that I can barely stand to look at them. A presenter is really not even a necessary component of the presentation anymore as most people intend for their slide decks to stand alone and tell the story without need for a person to deliver the message verbally. When these presentations are delivered, they are (more often than not) simply read by the presenter. On top of that, many presentations are not verbally delivered by those directly responsible for the content, but instead are delivered by a high level manager who only shows up on the day of the big presentation to lead the show. I don't know...that just seems odd to me. I'm not going to say there are no merits at all to this use of the tool and this particular approach, but I do find that what is delivered is rarely what I personally want to see/hear.

The article states that military commanders say that "slides impart less information than a five-page paper can hold, and that they relieve the briefer of the need to polish writing to convey an analytic, persuasive point." The technical presentations I see suffer from this in that they are not very polished nor persuasive. But even a "bad paper" that makes an attempt to provide detailed information on a topic is better than being bored to death with a slide presentation that either puts too much or too little meaningful content in front of me. But, the fact that the use of PowerPoint has become so commonplace, it is rare to ever get a detailed paper...you only get the slides. And even if you do get a paper, it is often full of grammatical issues and seems to have been written after the slides such that the paper doesn't offer that much more information but only slightly lengthens the bullet points from the slides.

There are a lot of people out there trying to change the way PowerPoint is used. They work to show that presentations can be used to support the speaker and the topic effectively. I think that most of these advocates separate presentation design and use from documents providing detailed information. I just wish there was a faster way to affect change in this area!

What happened to the days of speakers standing alone in front of the crowd? When was the last time you attended a session that didn't use PowerPoint? Maybe we all need to ban the use of PowerPoint (and all presentation software) for a while and return to a more simple way of sharing our message. Perhaps instead of propagating the use of PowerPoint, organizations could start hosting Toastmasters meetings and teach people how to become more effective speakers.

As I said earlier, I don't think it's necessarily the software that's the problem. But, it's sure an easy target on which to place the blame for the sad situation we find ourselves in every time we are forced to sit through another agonizingly boring presentation.

That old restart problem again...

Not something entirely learned new today - but rather a revisited "something I learned" coupled with an "I didn't necessarily expect it in this case".

It is the old "statement restart" and "evil triggers" issue. I had an asktom question asking why a row seemed to be getting deleted twice. You should read that link, I'll be referring to it here.

I immediately knew what the issue was (I was pretty sure). Before I even read the test case - I had already pointed them to three articles I wrote on the subject a while ago...

But when I looked at their test case and modified it slightly to be smaller and easier to read - I was frankly surprised at what was happening - but ok with the behavior.

It takes a series of bad things to happen for this issue the poster was seeing to have happen, you have to

a) use a trigger (evil)
b) do something non-transactional in that trigger - for example modify a package global variable
c) do slow by slow processing (if they did a big mass operation - they could still see a restart, but it would be less likely perhaps)

Here is a snippet of the code again:


ops$tkyte%ORA11GR2> create or replace PACKAGE pacepack
2 as
3 type array is table of number index by varchar2(40);
4 g_data array;
5 g_cnt number;
6 end pacepack;
7 /

Package created.

That package just has some global variables (evil global variables - evil evil - they didn't use globals in their API, they had getter/setter functions - but they behaved like globals as well). We'll create a row trigger to save the rowids of the rows our trigger has processed - even if Oracle rolls back our firing statement - modifications we've made in our trigger to the globals won't rollback.

Now, we'll code the trigger:


ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table delete_data
2 as
3 select owner, object_name
4 from dba_objects
5 where rownum < 53001;

Table created.




ops$tkyte%ORA11GR2> create or replace trigger delete_data_bt
2 before delete or update on delete_data
3 for each row
4 begin
5 if ( pacepack.g_data.exists(rowidtochar(:old.rowid)) )
6 then
7 dbms_output.put_line( 'doing "' || :old.rowid ||
'" again was called ' || pacepack.g_cnt );
8 else
9 pacepack.g_data(rowidtochar(:old.rowid)) := 1;
10 end if;
11 pacepack.g_cnt := pacepack.g_cnt + 1;
12 end;
13 /

Trigger created.

The trigger checks to see if the PLSQL global table variable has the rowid already entered in it - if so, it says "I already did this one", else it sets a flag showing it processed that one for the first time. We also increment a count (you know, to count the rows we've deleted or updated).

Now we process the data:


ops$tkyte%ORA11GR2> declare
2 CURSOR L_DELETE_CSR IS
3 SELECT *
4 FROM delete_data
5 for update ;
6 l_cnt number := 0;
7 BEGIN
8 pacepack.g_data.delete;
9 pacepack.g_cnt := 0;
10 for l_delete_row in l_delete_csr
11 loop
12 update delete_data
13 set owner = lower(owner)
14 where current of L_delete_CSR;
15 l_cnt := l_cnt + 1;
16 end loop;
17 dbms_output.put_line( 'trigger count = ' || pacepack.g_cnt ||
' local count = ' || l_cnt );
18 END;
19 /
doing "AAAYMdAAEAABFrnABH" again was called 8827
doing "AAAYMdAAEAABFwiAAx" again was called 20140
doing "AAAYMdAAEAABFyeACk" again was called 31405
doing "AAAYMdAAEAABFzaABE" again was called 42670
trigger count = 53004 local count = 53000

PL/SQL procedure successfully completed.

As you can see - our trigger was fired 53,004 times - for 53,000 rows. Our counts do not match and we can see the four updates that were restarted.

Why they were restarted - not really relevant - the fact is - they CAN be restarted, we can show that they can be restarted, it is documented that they can be. Any statement can be restarted - we do a savepoint before executing the statement and if we deem necessary (which we did obviously), we rollback to that savepoint and do it over.

So, expect the restart - and stop using triggers. If you do use triggers - be very very very careful to make sure they are not subject to issues with restarts (eg: NO autonomous transactions - NO modifications of global variables - NO API calls that have side effects that don't roll back (setter functions for example)!

Index ITL

Jonathan Lewis wrote a series of posts on topic of Index ITLs (interested transaction list), how and when they become huge and make index bigger in size – see this post with links to several other articles. As it turned out in a recent thread on SQL.ru forum, one of possible solutions to the issue [...]

WWOUG April meeting sponsored by Blue Gecko

Blue Gecko is proud to sponsor the venue and refreshments for the Western Washington Oracle Users Group (WWOUG) meeting this Thursday (4/29/2010) from 5:30 pm – 7:30 pm.

Guest speaker Tim Gorman, an author, Oracle ACE, and Oak Table member will present Scaling to Infinity: Partitioning Data Warehouses on Oracle.

Please register for this event if you plan to attend.

Location:
Seattle Public Library downtown branch, 1000 4th Ave, Seattle
Room 2, Level 4 (Wright/Ketcham Meeting Room)

Agenda:
5:30 – 6:00 Refreshments and WWOUG announcements
6:00 – 7:00 Tim Gorman: Scaling to Infinity
7:00 – 7:30 Open networking

Related posts:

  1. April Seattle Apps Tech Group Networking Event
  2. “Tech Café” – Blue Gecko Sponsored Event
  3. “Tech Café” – Blue Gecko Sponsored Event!

Execution plan Quiz: Shouldn’t these row sources be the other way around ;-)

Here’s a little trick question. Check out the execution plan below.
What the hell, shouldn’t the INDEX/TABLE access be the other way around?!
Also, how come it’s TABLE ACCESS FULL (and not by INDEX ROWID) in there?
This question is with a little gotcha, but can you come up with a query which produced such plan? ;-)
---------------------------------------------- | Id | Operation | Name | E-Rows | ---------------------------------------------- | 0 | SELECT STATEMENT | | | |* 1 | INDEX RANGE SCAN | PK_EMP | 1 | |* 2 | TABLE ACCESS FULL| EMP | 1 | ----------------------------------------------

Execution plan Quiz: Shouldn’t these row sources be the other way around ;-)

Here’s a little trick question. Check out the execution plan below.
What the hell, shouldn’t the INDEX/TABLE access be the other way around?!
Also, how come it’s TABLE ACCESS FULL (and not by INDEX ROWID) in there?
This question is with a little gotcha, but can you come up with a query which produced such plan? ;-)
---------------------------------------------- | Id | Operation | Name | E-Rows | ---------------------------------------------- | 0 | SELECT STATEMENT | | | |* 1 | INDEX RANGE SCAN | PK_EMP | 1 | |* 2 | TABLE ACCESS FULL| EMP | 1 | ----------------------------------------------

Execution plan Quiz: Shouldn’t these row sources be the other way around ;-)

Here’s a little trick question. Check out the execution plan below.

What the hell, shouldn’t the INDEX/TABLE access be the other way around?!

Also, how come it’s TABLE ACCESS FULL (and not by INDEX ROWID) in there?

This question is with a little gotcha, but can you come up with a query which produced such plan? ;-)

----------------------------------------------
| Id  | Operation          | Name   | E-Rows |
----------------------------------------------
|   0 | SELECT STATEMENT   |        |        |
|*  1 |  INDEX RANGE SCAN  | PK_EMP |      1 |
|*  2 |   TABLE ACCESS FULL| EMP    |      1 |
----------------------------------------------

Share/Bookmark

quotes from Stockholm ...

I spent an afternoon at the Nobel Museum in Stockholm and found myself writing notes furiously at the very first exhibit. "The mere formulation of a problem is often far more essential than it's solution, which may be merely a matter of mathematical or experimental skill. To raise a new question, new possibilities, to regard old problems from a new angle requires creative imagination and marks

Oracle Plan Stability (Stored Outlines) in PeopleSoft Global Payroll

A longer version of this posting, with experimental results, is available on my website.

In PeopleSoft for the Oracle DBA, I wrote a page (p. 291) explaining why stored outlines were not suitable for use in PeopleSoft.  Five years later, my view has not significantly changed.  Essentially, stored outlines work best with shared SQL, and there isn't much shared SQL in PeopleSoft, because a lot of it is dynamically generated.

  • Code generated by the component processor is dynamically generated.  At save time, only fields that have changed are updated.
  • PeopleCode can written in such a way that where clauses are dynamically assembled
  • nVision reports have variable numbers of criteria on literal tree node IDs in the queries.
  • By default in Application Engine, bind variables are converted to literals before the SQL is submitted to the database.  Even if this is overridden by enabling ReUseStatement in Application Engine or by using Cursor Sharing in the database, the code still wouldn’t be sharable.  Different instances of Application Engine executing the same program can use different instances on non-shared temporary records, so the tables in otherwise identical SQL statements are not the same.  You would get one version of the statement per temporary table instance.

However, there are very limited exceptions to this rule (otherwise I wouldn't have a story to tell).  The SQL in COBOL and SQR programs are more likely to be shareable.   Although some programs are coded to generate SQL dynamically, bind variables are passed through to SQL statements, and they use regular tables for working storage and not PeopleSoft temporary records.
A Global Payroll customer came to me with a problem where the payroll calculation (GPPDPRUN) would usually run well, but sometimes, the execution plan of a statement would change and the calculation would take additional several hours.  It is significant that the Global Payroll engine is written in COBOL.  My usual response to this sort of problem in Global Payroll is to add a hint to the stored statement.  Usually, I find that only a few statements that are affected.  However, after this happened a couple of times in production, it was clear that we couldn't continue to react to these problems. We needed to proactively stop this happening again.  This is exactly what stored outlines are designed to do.

Using Stored Outlines in the PeopleSoft GP Engine

Earlier I said that we could apply stored outlines to the Global Payroll calculation engine (GPPDPRUN) because it generally doesn’t use dynamic code with embedded literal values.  
While outlines are being created, the following privilege needs to be granted.  It can be revoked later.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
GRANT CREATE ANY OUTLINE TO SYSADM;

We can create a trigger to collect the stored outlines for a payroll calculation, thus:

  • The trigger fires when a payroll calculation process starts or finishes. 
  • At the start a payroll process it starts collecting stored outlines in a category called the same as the process; GPPDPRUN.
  • When the process finishes, outline collection is disabled by setting it back to false.
#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
CREATE OR REPLACE TRIGGER sysadm.gfc_create_stored_outlines
BEFORE UPDATE OF runstatus ON sysadm.psprcsrqst
FOR EACH ROW
WHEN (new.prcsname = 'GPPDPRUN' AND (new.runstatus = 7 OR old.runstatus = 7))
DECLARE
l_sql VARCHAR2(100);
BEGIN
l_sql := 'ALTER SESSION SET create_stored_outlines = ';
IF :new.runstatus = 7 THEN
EXECUTE IMMEDIATE l_sql||:new.prcsname;
ELSIF :old.runstatus = 7 THEN
EXECUTE IMMEDIATE l_sql||'FALSE';
END IF;
EXCEPTION WHEN OTHERS THEN NULL; --because I dont want to crash the process scheduler
END;
/

The exact number of outlines that are collected during this process will vary depending upon configuration, and which employees are processed as different payroll rules are invoked.
If no more outlines are to be collected the CREATE ANY OUTLINE privilege can be revoked.  This does not prevent the outlines from being used.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
REVOKE CREATE ANY OUTLINE FROM SYSADM;

Then, the category of outlines can be used in subsequent executions by replacing the trigger above with the one below, and the execution plans cannot change so long as the SQL doesn’t change.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
CREATE OR REPLACE TRIGGER sysadm.gfc_use_stored_outlines
BEFORE UPDATE OF runstatus ON sysadm.psprcsrqst
FOR EACH ROW
WHEN (new.prcsname = 'GPPDPRUN' AND (new.runstatus = 7 OR old.runstatus = 7))
DECLARE
l_sql VARCHAR2(100);
BEGIN
l_sql := 'ALTER SESSION SET use_stored_outlines = ';
IF :new.runstatus = 7 THEN
EXECUTE IMMEDIATE l_sql||:new.prcsname;
ELSIF :old.runstatus = 7 THEN
EXECUTE IMMEDIATE l_sql||'FALSE';
END IF;
EXCEPTION WHEN OTHERS THEN NULL; --because I dont want to crash the process scheduler
END;
/

After running an identify-and-calc and a cancel, we can see how many of the outlines are actually used.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
SELECT category, count(*) outlines
, sum(decode(used,'USED',1,0)) used
FROM user_outlines
GROUP BY category
ORDER BY 1
/

I have a large number of unused outlines because of additional recursive SQL generated because OPTIMIZER_DYNAMIC_SAMPLING was set 4. This does not occur if this parameter is set to the default of 2.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
CATEGORY                         OUTLINES       USED
------------------------------ ---------- ----------
GPPDPRUN 572 281

I can then remove the unused outlines.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
EXECUTE dbms_outln.drop_unused;

Used flags on the outlines can be reset, so we later we can see the outlines being used again.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
BEGIN
FOR i IN (SELECT * FROM user_outlines WHERE category = 'GPPDPRUN') LOOP
dbms_outln.clear_used(i.name);
END LOOP;
END;
/

If I want to go back running without outlines, I just disable the trigger

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
ALTER TRIGGER sysadm.stored_outlines DISABLE;

To re-enable outlines, just re-enable the trigger.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
ALTER TRIGGER sysadm.stored_outlines ENABLE;

Conclusions

Stored Outlines have very limited application in a PeopleSoft system.  However, they can easily be collected and used with the PeopleSoft Global Payroll engine.  It is just a matter of granting a privilege and using the database triggers on the process request table.
Testing that they actually have the desired effect is quite difficult, because you are trying to prove a negative.  I don’t think it is adequate simply to say that the outline has been used.

  • First you would need an environment where payroll calculation performs well, where you could collect outlines.
  • Then you would need a payroll calculation that performs poorly because the execution plan for at least one SQL statement is different
    • Either, on a second environment with exactly the same code.
    • Or in the same environment on a different set of data.
  • Then, it would be possible to demonstrate that applying the outline causes the execution plan to revert and restores the performance.  This can be confirmed by comparison of the ASH data for the various scenarios.

Even if you don’t want to use a stored outline immediately, it might be advantageous to collect them, and have them available when you do encounter a problem.

Analyzing a SQL Trace File with SQL Statements

As of Oracle Database 11g the DBMS_SQLTUNE package provides the SELECT_SQL_TRACE function. Its purpose is to load the content of a SQL trace file into a SQL tuning set. But, as it often happens, a feature can be (mis)used for another purpose. The aim of this post is to show how to take advantage of [...]