OraNA :: Oracle News Aggregator

Surveying MySQL’s Popular Storage Engines

Sean Hull — Thu, 11 Mar 2010 05:00:35 PST

In this month’s Database Journal piece we look at the spectrum of MySQL storage engines available, and examine what some of their strengths and weaknesses are.

View the article here: Survey of MySQL Storage Engines

Jersey 1.1.5.1 released

Gerard Davison — Thu, 11 Mar 2010 04:35:12 PST

There is a minor point release of Jersey that includes a pair of significant bug fixes for weblogic and JDeveloper users. Worth using in preference to 1.1.5. Thanks a lot to Paul for putting this one together.

New projects – OIF

Espen Barroso-Gomez — Thu, 11 Mar 2010 02:27:43 PST

Periods of silence shouldn’t last more than a few weeks..

Anyway, I’m busy planning aspects of a Oracle Identity Federation implementation, so that’s the current status. Further out, I’ll supply more details of how OIF is to work with, but if someone has specific OIF related questions, I’d be glad to try to help.

Version used in our project, by the way, will be 11.1.1.

Our Oracle Recruitment Team is Growing - Multiple Job Opportunities in Bangalore, India

david.talamelli — Thu, 11 Mar 2010 01:14:34 PST

DON"T GET STUCK IN THE MATRIX

SEE YOUR FUTURE

VISIT THE ORACLE

The position(s): CORPORATE RECRUITING RESEARCH ANALYST(S)

ABOUT ORACLE

Oracle's business is information--how to manage it, use it, share it, protect it. For three decades, Oracle, the world's largest enterprise software company, has provided the software and services that allow organizations to get the most up-to-date and accurate information from their business systems.

Only Oracle powers the information-driven enterprise by offering a complete, integrated solution for every segment of the process industry. When you run Oracle applications on Oracle technology, you speed implementation, optimize performance, and maximize ROI.
Great hiring doesn't happen by accident; it's the culmination of a series of thoughtfully planned and well executed events. At the core of any hiring process is a sourcing strategy. This is where you come in...

Do you want to be a part of a world-class recruiting organization that's on the cutting edge of technology? Would you like to experience a rewarding work environment that allows you to further develop your skills, while giving you the opportunity to develop new skills? If you answered yes, you've taken your first step towards a future with Oracle. We are building a Research Team to support our North America Recruitment Team, and we need creative, smart, and ambitious individuals to help us drive our research department forward.

Oracle has a track record for employing and developing the very best in the industry. We invest generously in employee development, training and resources. Be a part of the most progressive internal recruiting team in the industry. For more information about Oracle, please visit our Web site at http://www.oracle.com

Escape the hum drum job world matrix, visit the Oracle and be a part of a winning team, apply today.

POSITION: Corporate Recruiting Research Analyst

LOCATION: Bangalore, India

RESPONSIBILITIES:

•Develop candidate pipeline using Web 2.0 sourcing strategies and advanced Boolean Search techniques to support U.S. Recruiting Team for various job functions and levels.

•Engage with assigned recruiters to understand the supported business as well as the recruiting requirements; partner with recruiters to meet expectations and deliver a qualified pipeline of candidates.

•Source candidates to include both active and passive job seekers to provide a strong pipeline of qualified candidates for each recruiter; exercise creativity to find candidates using Oracle's advanced sourcing tools/techniques.

•Fully evaluate candidate's background against the requirements provided by recruiter, and process leads using ATS (Applicant Tracking System).

•Manage your efforts efficiently; maintain the highest levels of client satisfaction as well as strong operations and reporting of research activities.

PREFERRED QUALIFICATIONS:

•Fluent in English, with excellent written and oral communication skills.
•Undergraduate degree required, MBA or Masters preferred.
•Proficiency with Boolean Search techniques desired.
•Ability to learn new software applications quickly.
•Must be able to accommodate some U.S. evening hours.
•Strong organization and attention to detail skills.
•Prior HR or corporate in-house recruiting experiences a plus.
•The fire in the belly to learn new ideas and succeed.
•Ability to work in team and individual environments.

This is an excellent opportunity to join Oracle in our Bangalore Offices. Interested applicants can send their resume to david.talamelli@oracle.com or contact David on +61 3 8616 3364

20. - 21. May EMEA Harmony Conference in Tallinn

ilmar.kerm@gmail.com (Ilmar Kerm) — Wed, 10 Mar 2010 23:03:00 PST

An excellent joint conference for Estonian, Finnish, Latvian and Russian Oracle User Groups in Tallinn, 20. - 21. May 2010.
Speakers also include Tom Kyte, Tanel Põder, Chris J. Date and Steven Feuerstein.

Read the agenda here and register in OUGF home page (250€+VAT registration fee).

In addition, just before the conference, 17.-18. May, Chris Date will perform his "How to Write Correct SQL and Know It: A Relational Approach to SQL" seminar in Helsinki. More info here and more detailed information here.

What does ‘extended’ mean when referring to RAC clusters

John Hallas — Thu, 11 Mar 2010 00:12:19 PST

Yesterday I attended and spoke at the Scotland DBA SIG. It was an enjoyable day with not one but two presentation from Julian Dyke. The second one was an overview of HA options and it in Julian mentioned extended RAC. Now I have worked at several sites which have used or wanted to use extended RAC and I have heard a number of views on what exactly the term extended means.

From MoS Note 220970.1 we get the definitive statement on the maximum distance for extended RAC

The high impact of latency create practical limitations as to where this architecture can be deployed. While there is not fixed distance limitation, the additional latency on round trip on I/O and a one way cache fusion will have an affect on performance as distance increases. For example tests at 100km showed a 3-4 ms impact on I/O and 1 ms impact on cache fusion, thus the farther distance is the greater the impact on performance. This architecture fits best where the 2 datacenters are relatively close (<~25km) and the impact is negligible. Most customers implement under this distance w/ only a handful above and the farthest known example is at 100km. Largest distances than the commonly implemented may want to estimate or measure the performance hit on their application before implementing. Do ensure a proper setup of SAN buffer credits to limit the impact of distance at the I/O layer.

So we now have figures that 100Km is technically possible but probably won’t work well and most sites use less than 25Km. Co-incidentally in the paragraph above that quotation it refers to SE RAC (10g) as being limited to having all nodes located in the same room. So in two lines of text we have just gone from a 100ft to 100KMs.

Yesterday Julian’s answer when I asked him the question was that he thought extended was defined as a couple of kilometres or above which is reasonable. However I am thinking that a better definition of extended is not to focus on distance of nodes apart but on what connects the nodes. My thoughts are that that if fibre channel or DWDM is involved and the nodes are not in the same building then that would count as an extended RAC cluster despite potentially only being a 100 yards apart. Another view would be that if some form of array based mirroring is in place and RAC technology is in place then that again would define the system as ‘extended’ .

Now that all fits with a view I have heard from a well-known RAC consultant from Oracle who defines any RAC system where nodes are in different rooms as extended. I have never subscribed to that theory but I can see that the definition I provided in the previous paragraph would lead to that conclusion.

Now we just need to consolidate on the word ‘extended’ rather than the use of ’stretched’ which I see occasionally.

I really do hope I get some comments on this piece as I am sure it is worthy of debate and comment and anything that helps to create a shared view on the matter can only be good.

Configuring Lost Password management in Oracle Access Manager

Mahendra — Wed, 10 Mar 2010 22:40:08 PST

If you want to configure Lost password policy management using Oracle Access Manager for your application, then here it is how you can do. In real time scenario, once the user clicks on Lost Password link, the user will be challenged with questions registered during first login, followed by a change password.

How can we do this?

1. Create an LDIF schema file with a new auxiliary object class and two new attributes as shown below. Here only 1 challenge attribute is used, if your requirement demands, you can add many more.

dn: cn=schema
changetype: modify
add: attributetypes
attributetypes: ( 1.3.6.1.4.1.9999.1.1094.204 NAME ‘Challenge2′ SYNTAX 1.3.6.1.4.1.1466.115.121.1.15 )

dn: cn=schema
changetype: modify
add: attributetypes
attributetypes: ( 1.3.6.1.4.1.9999.1.1094.205 NAME ‘Response2′ SYNTAX 1.3.6.1.4.1.1466.115.121.1.15 )

dn: cn=schema
changetype: modify
add: objectclasses
objectclasses: ( 1.3.6.1.4.1.9999.1.1094.206 NAME ‘oblixAuxPerson4LPM’ DESC ‘User defined objectclass’ SUP top AUXILIARY MAY ( Challenge2 $ Response2 ) )

2. Import the LDIF file into the LDAP where OAM stores user data.

3. Now we have to configure this new object class in the OAM. Goto the Identity System Console and click on Common Configuration tab. Click on object classes in left pane and Add the new class by selecting the type as Person object class.

4. Goto Identity System Console -> System Configuration. Click password policy.

5. Enter the URL for Lost Password Redirect URL. Please remember to enable both checkboxes of Successful Attempts Attribute and Failed Attempts Attribute with attributes oblastSuccessFulLogin and oblastFailedLogin respectively.

6. Click on Lost Password Policy.

7. Enter the name. You can specify the challenge phrases to be user defined or pre-defined or both. If you wish to have pre-defined, then Enter the challenge phrase in the text box and click Add.

8. Enter the values for Minimum Challenges to be configured which specifies the no. of challenge phrases that will appear.

9. Enter values for Challenge Response Minimum Length and Allow Duplicate Responses appropriately.

10. Enter value for Minimum Challenges to be Answered which specifies the no. of challenges that user has to answer.

11. Select value for Challenge Pose Type. All at Once allows all the challenge phrases to appear at the same time. One after the other allows the challenges to appear after the user answers the first question.

12. Enable Send Email after password change if you want to configure SMTP stuff.

13. In the end, enable check box of password policy.

In your custom application, you can insert the Lost Password link as shown below.

http://machinename:portnumber/identity/oblix/apps/lost_pwd_mgmt/bin/lost_pwd_mgmt.cgi?program=passwordChallengeResponse&login=%scheme1_uid_parameter_value%%scheme2_uid_parameter_value%%schemeN_uid_parameter_value%&target=top

14. To enable the password policies to the resources protected by the OAM, then modify the authentication scheme that protects those resources. In the validate_password plugin of your Authentication Scheme, add this obReadPasswdMode=”LDAP”,obWritePasswdMode=”LDAP” and the new validate_password plugin appears like this

obCredentialPassword=”password”,obReadPasswdMode=”LDAP”, obWritePasswdMode=”LDAP”

For more information, check this.

References:

Oracle Documentation

WebCenter Spaces 11g - UI Customization

john.brunswick — Wed, 10 Mar 2010 20:59:58 PST

When developing on top of a portal platform to support an intranet or extranet, a portion of the development time is spent adjusting the out-of-box user templates to adjust the look and feel of the platform for your organization. Generally your deployment will not need to look like anything like the sites posted on http://cssremix.com/ or http://www.webcreme.com/, but will meet business needs by adjusting basic elements like navigation, color palate and logo placement.

After spending some time doing custom UI development with WebCenter Spaces 11G I have gathered a few tips that I hope can help to speed anyone's efforts to quickly "skin" a WebCenter Spaces deployment. A detailed white paper was released that outlines a technique to quickly update the UI during runtime - http://www.oracle.com/technology/products/webcenter/pdf/owcs_r11120_cust_skins_runtime_wp.pdf. Customizing at "runtime" means using CSS and images to adjust the page layout and feel, which when creatively done can change the pages drastically.

WebCenter also allows for detailed templates to manage the placement of major page elements like menus, sidebar, etc, but by adjusting only images and CSS we can end up with something like the custom solution shown below.

view large image

Let's dive right in and take a look at some tools to make our efforts more efficient.

Select Statement Causing an ORA-00001?

Charles Hooper — Wed, 10 Mar 2010 22:00:24 PST

March 11, 2010

Sometimes I receive seemingly interesting emails showing Oracle errors – leaving me to ponder… certainly, that can’t cause an error, can it? Here is one that I received a year ago (paraphrased):

The commercially developed application that we are using displayed an error message identifying a SELECT statement as the source of an ORA-00001 error. What is the source of the Oracle constraint error? The error message displayed by the application is as follows:
select account_period
from PROJECT_SUMMARY
where project_id = :m_saProjSumProjectID[nProjSumIndex]
and id =:m_saProjSumSavedID[nProjSumIndex]
into :nACCOUNT_PERIOD

ORA-00001: unique constraint (TESTUSER.SYS_C006354) violated

This transaction has resulted in violating an Oracle defined constraint.
Constraints are enforced by the database manager.  This transaction has
been rolled back.

My first thought at the sight of this error was that the commercially developed application was actually displaying one of the SQL statements that was executed after the SQL statement which triggered the primary key violation. It is easy to let a runtime error slide through for a period of time before the program notices that an error happened – maybe it is just a sign of sloppy programming (I hope not, because this has happened in some of my custom-developed programs too).

How would we start to troubleshoot this error message? The “SYS_C” portion of the constraint name indicates that the constraint is most likely a system generated constraint name, probably intended to enforce a uniqueness requirement for a primary key column. Exporting the data from the database using Datapump export (or the legacy exp utility) and importing the data into a new database could cause the number following “SYS_C” to change, and it is likely that constraint SYS_C006354 in my database (that is used by the same application) is very different from that of the person who posed the question to me.

Let’s see if we are able to find the answer by working the problem in a circular fashion. For example, let’s find the name of the index that is used to enforce the primary key constraint on of one the application’s tables:

SELECT
  INDEX_NAME
FROM
  DBA_INDEXES
WHERE
  INDEX_NAME LIKE 'SYS%'
  AND TABLE_NAME='INVENTORY_TRANS';

INDEX_NAME
-----------
SYS_C005168

Now that we know that the index is named SYS_C005168, we could do something like this:

SELECT
  DC.OWNER,
  DC.CONSTRAINT_NAME,
  DC.CONSTRAINT_TYPE,
  DC.TABLE_NAME,
  DC.STATUS,
  SUBSTR(DCC.COLUMN_NAME,1,30) COLUMN_NAME
FROM
  DBA_CONSTRAINTS DC,
  DBA_CONS_COLUMNS DCC
WHERE
  DC.CONSTRAINT_NAME='SYS_C005168'
  AND DC.OWNER='TESTUSER'
  AND DC.OWNER=DCC.OWNER
  AND DC.CONSTRAINT_NAME=DCC.CONSTRAINT_NAME
ORDER BY
  DCC.POSITION;

OWNER   CONSTRAINT_NAME  CONSTRAINT_TYPE  TABLE_NAME       STATUS   COLUMN_NAME
------  ---------------  ---------------  ---------------  -------  --------------
SYSADM  SYS_C005168      P                INVENTORY_TRANS  ENABLED  TRANSACTION_ID

The above output shows that the primary key constraint SYS_C005168 enforces the uniqueness of the primary key (TRANSACTION_ID) column in the table INVENTORY_TRANS. We just demonstrated that we now know what we already mostly knew.

In the case of the person who sent the email to me, the table name was not known. So, we could take the last of the above SQL statements and substitute SYS_C006354 in place of SYS_C005168 to find the table name and primary key column that was violated. If the SQL statement failed to return usable information the next step might be to enable a 10046 trace at level 4 for one of the affected sessions, and try to reproduce the problem. A 10046 trace will list the sequence of events that led up to the error message appearing in the client application.

Lesson learned today

Jeffrey Kemp — Wed, 10 Mar 2010 21:35:39 PST

Comment out all "DROP TABLE" commands in my scripts.(I accidentally hit F5 when the focus was in the wrong window - which happened to contain a "DROP TABLE / CREATE TABLE" script - my Toad session goes and happily drops the table that I'd been gradually accumulating statistics into for the past 3 days - and no, there's no flashback table in 9i)At least I kept all my scripts - rerunning them all

ODTUG Kaleidoscope Conference 2010

Optimizer Development Group — Wed, 10 Mar 2010 19:57:40 PST

ODTUG Kaleidoscope 2010, June 27 - July 1st Washington DC is a great conference for Oracle developers and architects, offering the best content by renowned experts. We will have the privilege of delivering two Optimizer sessions this year, 'Explaining the Explain plan' and 'Finally Plan Stability during Database Upgrade with SQL Plan Management'. In the Explain the Explain plan session we will discuss each aspect of an execution plan (from selectivity to parallel execution), explain what information you should be getting from the plan, and how it will affects the execution. While in the SQL Plan Management session, we will will give detailed instructions on how to capture your existing execution plans before you upgrade to 11g, as well as an in-depth discussion on what to expect from the Optimizer after you upgrade to 11g.
ODTUG is a great conference where you can learn lots in a fun and casual atmosphere. Looking forward to seeing some of you there!

Greetings!!!!

alex.blyth@oracle.com — Wed, 10 Mar 2010 19:24:41 PST

Greetings everyone!

If you're reading this, hopefully it's because you have been following our series of webcasts on Oracle 11gR2 that we've been hosting on Wordpress. If you found us some other way, well that's even better - the more the merrier as they say.

In either case, welcome to our new blog!!! Over the next few days, Ill move the old posts from wordpress to here its all in the one location.

Right! Who are we? The authors of this blog are the ANZ Inside Consulting Team.

Currently, this is made of of:

Tom Jurcic
Yasin Mohammed
Andrew Clarke
Rene Poels and
me - Alex Blyth

Basically, our role in Oracle is to help users of our technologies get the most of their existing investments as well as what's new, old, blue, what have you...

Ideally, this is all going to be technical in nature and not of a marketing nature (we'll leave the marketing up to others).

For now, there's obviously not much here. But that won't last too long. In the mean time, those who are interested can find replays and slides of our previous webcasts on the "Oracle 11g Webcasts" page.

Till next time

Alex

Clustering Factor: Row Migration’s Victim

Markus Winand — Tue, 09 Mar 2010 02:15:46 PST

This article describes the effects of a high row migration rate on the clustering factor and the optimizer’s ability to select the best execution plan.

In my previous article—Row Migration and Row Movement—I have demonstrated that the “insert empty, update everything” anti-pattern can lead to 100% row migration. This article continues the research on row migration and unveils surprising effects on the clustering factor. To be precise, the clustering factor can become completely bogus in presence of a very high row migration rate. Once the clustering factor is “wrong”, it’s just a finger exercise to construct an optimizer trap and proof that row migration can affect the query plan.

To start off, I create a table similar to the one in the previous article. However, I add one more column and populate it with random values from 0 to 9. I will need this column to build my optimizer trap later on.

CREATE TABLE row_mig1 (
  a CHAR(2000),
  b CHAR(2000),
  c CHAR(2000),
  x NUMBER      NOT NULL,
  filter NUMBER NOT NULL,
  CONSTRAINT row_mig1_pk PRIMARY KEY (x)
) ENABLE ROW MOVEMENT;

BEGIN
   FOR i IN 1..100000 LOOP
      INSERT INTO row_mig1
                    (x, filter)
             VALUES (i, trunc(dbms_random.value(0, 10)));

      UPDATE row_mig1
         SET a ='a', b = 'b', c = 'c'
       WHERE x=i;
   END LOOP;
END;
/
COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(null, 'ROW_MIG1', CASCADE=>TRUE);

Up till now everything is very similar to the last article, except that I used DBMS_STATS instead of ANALYZE TABLE. Although we know exactly that almost every row was migrated, DBMS_STATS doesn’t care about that:

SQL> SELECT num_rows, chain_cnt
       FROM user_tables
      WHERE table_name='ROW_MIG1';

  NUM_ROWS  CHAIN_CNT
---------- ----------
    100000          0

SQL>

However, let’s have a look at the index:

SQL> SELECT num_rows, leaf_blocks, clustering_factor
       FROM user_indexes
      WHERE index_name = 'ROW_MIG1_PK';

  NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
---------- ----------- -----------------
    100000         187               918

SQL>

Well, that’s an excellent clustering factor. A low clustering factor indicates that the index is in the same sequence as the table. That’s true in that case because the index column corresponds to the order in which the rows were inserted to the table. However, 918, how can this be? The clustering factor is supposed to be between the number of table blocks—which indicates a good clustering factor—and the number of index rows—which is the worst case. So, let’s look at the table size:

SQL> SELECT t.blocks             table_blocks
          , i.clustering_factor  index_clustering_factor
          , i.num_rows           index_rows
       FROM user_indexes i
       JOIN user_tables t USING (table_name)
      WHERE index_name = 'ROW_MIG1_PK';

TABLE_BLOCKS INDEX_CLUSTERING_FACTOR INDEX_ROWS
------------ ----------------------- ----------
      100877                     918     100000

SQL>

According to that, the lower bound for the clustering factor is higher than the upper bound. Hmm, let’s investigate the clustering factor manually and verify the distribution of the rows across the table blocks:

SQL> SELECT * FROM (
       SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) block_number
            , COUNT(*) rows_in_block
         FROM row_mig1
        GROUP BY DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)
     ) WHERE ROWNUM <=10;

BLOCK_NUMBER ROWS_IN_BLOCK
------------ -------------
         523           109
         524           113
         525           109
         526           109
         527           110
         536           109
         537           110
         538           109
         539           109
         540           110

10 rows selected.

SQL>

There are 109 different ROWIDs that refer to the block number 523. But we know that each record has about 6k. It is impossible to fit 109 rows into a single block of 8k. However, the “insert empty, update everything” anti-pattern makes it possible. The game goes like this:

The very first row is inserted into a new block.
The first row is updated, and fits into the same block. The free space in that particular block is still more than a kilobyte.
The second row is inserted into the very same block, because there is enough free space available in that block.
The update of the second row triggers the migration of that row to a different block.

The row migration changes neither the ROWID nor the index entry. That means that the forwarding address—that is, somehow, the new ROWID—is stored in the original block so that the index can find the row.
The third row is inserted into the very first table block, again.

Because the second row was moved into a different block, the very first block has still free space. There is only the first row—as a whole—and one forwarding address stored in that block. So, the insert of the third row can take place there.
And so on. Until the forwarding addresses fill the block—up to PCTFREE.

That’s a good one, hmm?

We can even verify that:

SQL> SELECT * FROM (
       SELECT MIN(X)                               min_x
            , MAX(x)                               max_x
            , MAX(x) - MIN(x) + 1                  diff_x
            , DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) block_number
            , COUNT(*)            rows_in_block
         FROM row_mig1
        GROUP BY DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)
        ORDER BY min_x
     ) WHERE ROWNUM <=10;

     MIN_X      MAX_X     DIFF_X BLOCK_NUMBER ROWS_IN_BLOCK
---------- ---------- ---------- ------------ -------------
         1        113        113          524           113
       114        222        109          523           109
       223        332        110          537           110
       333        441        109          538           109
       442        550        109          539           109
       551        660        110          540           110
       661        769        109          541           109
       770        878        109          542           109
       879        987        109          543           109
       988       1096        109          525           109

10 rows selected.

SQL>

You see that the first row was inserted into block number 524. All subsequent rows up to the 113^th were put into the same block. When that block was finally filled up—with one row and 112 forwarding addresses—the game starts over in the next block. All the INSERT statements took place in just 918 distinct blocks. Because the ROWID is assigned during the INSERT, the subsequent migration of the row due to the UPDATE is not reflected in the ROWID.

Neither DBMS_STATS nor ANALYSE TABLE look into the table to check if the row is really there or if the particular block it is just an accumulation of forwarding addresses. From their perspective, all the rows are in the same block—this is how the clustering factor is calculated. Although correctly calculated—technically—and up to date, the clustering factor of this index does not reflect the real situation.

The “correct” value—in that sense that it reflects the data distribution correctly—for the clustering factor would be 100.000; that is, the number of rows. If the clustering factor equals the number of rows in the index—which is the worst possible case—it means that there are no two adjacent index entries that refer to the same table block. This is actually the case because no 8k block can contain two complete 6k rows.

The Clustering Factor is Wrong, So What?

So, what’s the problem if the clustering factor is wrong?

The problem is that the Cost Based Optimizer (CBO) uses the clustering factor in its cost calculation for an INDEX RANGE SCAN (see Fallacies of the Cost Based Optimizer [pdf]). That means, the cost of the INDEX RANGE SCAN will be too low, because the clustering factor is way too low. Luckily there is an effect that makes all that less problematic; all indexes on that table are affected.

Let’s make a second index to verify that:

SQL> CREATE INDEX row_mig1_idx ON row_mig1(filter);

Index created.

SQL> BEGIN
       DBMS_STATS.GATHER_TABLE_STATS(null, 'ROW_MIG1',
                                     CASCADE => TRUE);
     END;
     /

PL/SQL procedure successfully completed.

SQL> SELECT i.index_name         index_name
          , t.blocks             table_blocks
          , i.clustering_factor  index_clustering_factor
          , i.num_rows           index_rows
       FROM user_indexes i
       JOIN user_tables t USING (table_name)
      WHERE table_name = 'ROW_MIG1';

INDEX_NAME   TABLE_BLOCKS INDEX_CLUSTERING_FACTOR INDEX_ROWS
------------ ------------ ----------------------- ----------
ROW_MIG1_PK        100877                     918     100000
ROW_MIG1_IDX       100877                    9180     100000

SQL>

The clustering factor of the new index is bigger than the one of the original index, but still way off its real value. The “correct” clustering factor for the new index would be 100.000 because there are no two adjacent index entries that refer to the table block. Well, they actually do, but there is nothing more than the forwarding address in those blocks.

Ten Times as High

The clustering factor is ten times as high because the filter column has ten distinct value. That means that the adjacent index entries will refer to ten blocks at least, because those entries were not inserted in the same order as they are stored in the index. On the other hand, the index on the filter column is not only sorted by the filter value, but also by the ROWID—as every nonunique index in Oracle—so that the clustering factor is kept at a minimum. Finally, the clustering factor is ten times as high because it refers to ten times as many table blocks, but does not grow above that because the index order keeps the clustering factor at a minimum.

Although the clustering factor does not correctly reflect the efforts to fetch the table rows, it correctly reflects the relation between the two indexes. The primary key index has a lower clustering factor because it has the same sequence as the table itself. On the other side, the index on the filter column has a different order, thus the value is higher. The side note explains why it is Ten Times as High.

If all indexes are affected, there is hardly any real problem I can see for a single table access. Even queries that can be executed with two different indexes, the CBO will most likely not do wrong because both clustering factors are misleading.

The Join Trap

If it’s not possible to confuse the optimizer with a single table, let’s use more of them. So, I try to build a trap where the optimizer’s decision of the join order is influenced by the phony clustering factor so that the optimizer takes the less efficient execution plan.

For that purpose, I build a second table very similar to the first one. There are only two differences:

I don’t follow the “insert empty, update everything” anti-pattern—there will be no row migration.
The selectivity of the filter columns is slightly increased (about 6% instead of 10%).

Here is the overall script:

CREATE TABLE row_mig2 (
  a CHAR(2000),
  b CHAR(2000),
  c CHAR(2000),
  x NUMBER      NOT NULL,
  filter NUMBER NOT NULL,
  CONSTRAINT row_mig2_pk PRIMARY KEY (x)
) ENABLE ROW MOVEMENT;

INSERT INTO row_mig2 (x, filter, a, b, c)
     SELECT level, trunc(dbms_random.value(0,17)), 'a', 'b', 'c'
       FROM dual CONNECT BY level <= 100000;
COMMIT;

CREATE INDEX row_mig2_idx ON row_mig2(filter);

EXEC DBMS_STATS.GATHER_TABLE_STATS(null, 'ROW_MIG2', CASCADE=>TRUE);

Now let’s check the statistics:

SQL> SELECT i.index_name         index_name
          , t.blocks             table_blocks
          , i.clustering_factor  index_clustering_factor
          , i.num_rows           index_rows
       FROM user_indexes i
       JOIN user_tables t USING (table_name)
      WHERE table_name = 'ROW_MIG2';

INDEX_NAME   TABLE_BLOCKS INDEX_CLUSTERING_FACTOR INDEX_ROWS
------------ ------------ ----------------------- ----------
ROW_MIG2_PK        100749                  100000     100000
ROW_MIG2_IDX       100749                  100000     100000

SQL>

Please note that the clustering factor is at the upper bound of the expected range; that means, it indicates that there are no two adjacent index entries referring to the same table block. That’s somehow logical, if we consider that every row is in its own table block.

After that preparation, I can present my “trapQL”:

SELECT *
  FROM row_mig1 d1
  JOIN row_mig2 d2 ON (d1.x = d2.x)
 WHERE d1.filter = 0
   AND d2.filter = 0;

It is a rather trivial join on the primary keys. Additionally each table is filtered on the filter column for one particular value. The trap works because a NESTED LOOPS join is possible in both ways. Either by filtering the first table by an INDEX RANGE SCAN on filter and then fetch the corresponding entry from the second by a primary key lookup, or vice versa. However, because we—as well as the optimizer—know that the second table is more selective than the first one, the more efficient way to execute that query is to first perform the INDEX RANGE SCAN on the second table and then join in the first one. In that way, the number of primary key lookups is reduced and the overall performance will be better. Considering that the first table suffers from heavy row migration, that effect becomes even more relevant. However, it is of course the purpose of the discussion to proof that the optimizer is doing “wrong”:

SET AUTOTRACE TRACEONLY;
SET TIMING ON;

SELECT /* original clustering factor */ *
  FROM row_mig1 d1
  JOIN row_mig2 d2 ON (d1.x = d2.x)
 WHERE d1.filter = 0
   AND d2.filter = 0;

SET TIMING OFF
SET AUTOTRACE OFF

And the result is:

577 rows selected.

Elapsed: 00:01:08.35

Execution Plan
----------------------------------------------------------
Plan hash value: 4162018446

---------------------------------------------------------------------
| Id | Operation                     | Name         | Rows  | Cost  |
---------------------------------------------------------------------
|  0 | SELECT STATEMENT              |              |  5882 | 10941 |
|  1 |  NESTED LOOPS                 |              |       |       |
|  2 |   NESTED LOOPS                |              |  5882 | 10941 |
|  3 |    TABLE ACCESS BY INDEX ROWID| ROW_MIG1     | 10000 |   938 |
|* 4 |     INDEX RANGE SCAN          | ROW_MIG1_IDX | 10000 |    20 |
|* 5 |    INDEX UNIQUE SCAN          | ROW_MIG2_PK  |     1 |     0 |
|* 6 |   TABLE ACCESS BY INDEX ROWID | ROW_MIG2     |     1 |     1 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("D1"."FILTER"=0)
   5 - access("D1"."X"="D2"."X")
   6 - filter("D2"."FILTER"=0)

Statistics
----------------------------------------------------------
        913  recursive calls
          0  db block gets
      35584  consistent gets
      21027  physical reads
          0  redo size
      39012  bytes sent via SQL*Net to client
        837  bytes received via SQL*Net from client
         40  SQL*Net roundtrips to/from client
         12  sorts (memory)
          0  sorts (disk)
        577  rows processed

The optimizer has chosen to perform the INDEX RANGE SCAN on ROW_MIG1_IDX first. The optimizer is well aware of the fact that the INDEX RANGE SCAN will return about 10000 rows; still it was preferred over the alternative execution plan.

So let’s check what happens if we tell the optimizer the truth about that table’s indexes?

BEGIN
  DBMS_STATS.SET_INDEX_STATS(null, 'ROW_MIG1_PK', clstfct=>100000);
  DBMS_STATS.SET_INDEX_STATS(null, 'ROW_MIG1_IDX',clstfct=>100000);
END;
/

SET AUTOTRACE TRACEONLY;
SET TIMING ON;

SELECT /* updated clustering factor */ *
  FROM row_mig1 d1
  JOIN row_mig2 d2 ON (d1.x = d2.x)
 WHERE d1.filter = 0
   AND d2.filter = 0;

SET TIMING OFF
SET AUTOTRACE OFF

The only change is that the statistics have been manually updated to the “more correct” clustering factor of 100.000. Unfortunately neither DBMS_STATS nor ANALYZE TABLE can be used for that purpose, so I did it manually. Please note that the table itself was not changed; most of the rows are still migrated.

And the result is:

577 rows selected.

Elapsed: 00:00:59.91

Execution Plan
----------------------------------------------------------
Plan hash value: 3004301745

---------------------------------------------------------------------
| Id | Operation                     | Name         | Rows  | Cost  |
---------------------------------------------------------------------
|  0 | SELECT STATEMENT              |              |  5882 | 11780 |
|  1 |  NESTED LOOPS                 |              |       |       |
|  2 |   NESTED LOOPS                |              |  5882 | 11780 |
|  3 |    TABLE ACCESS BY INDEX ROWID| ROW_MIG2     |  5882 |  5896 |
|* 4 |     INDEX RANGE SCAN          | ROW_MIG2_IDX |  5882 |    12 |
|* 5 |    INDEX UNIQUE SCAN          | ROW_MIG1_PK  |     1 |     0 |
|* 6 |   TABLE ACCESS BY INDEX ROWID | ROW_MIG1     |     1 |     1 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("D2"."FILTER"=0)
   5 - access("D1"."X"="D2"."X")
   6 - filter("D1"."FILTER"=0)

Statistics
----------------------------------------------------------
        913  recursive calls
          0  db block gets
      20695  consistent gets
      12817  physical reads
          0  redo size
      39012  bytes sent via SQL*Net to client
        837  bytes received via SQL*Net from client
         40  SQL*Net roundtrips to/from client
         12  sorts (memory)
          0  sorts (disk)
        577  rows processed

The more representative clustering factor makes the optimizer take the expected plan. The filtering takes place on the more selective table first, which matches about 5900 rows. The other table is joined in later. The execution is about 13% faster, the logical and physical gets dropped by about 40% both. That makes quite a difference.

The cost of the TABLE ACCESS BY INDEX ROWID that follows the INDEX RANGE SCAN reflects the clustering factor’s impact. The second query plan has a cost of about 5900, that actually means that each fetched row will need a block read. The original execution plan had a cost value of 938 for that step, so that the overall cost value was lower.

After that I must remind the reader that the rows in table ROW_MIG1 are still migrated. The performance difference is not cause by the row migration per se, but by the misleading statistics that result out of the row migration.

Correcting the Row Migration

To complete the exercise, I will correct the row migration, run the statement again, and compare the performance improvement:

SQL> ALTER TABLE row_mig1 MOVE;

Table altered.

SQL> ALTER INDEX row_mig1_pk REBUILD;

Index altered.

SQL> ALTER INDEX row_mig1_idx REBUILD;

Index altered.

SQL> BEGIN
       DBMS_STATS.GATHER_TABLE_STATS(null, 'ROW_MIG1',CASCADE=>TRUE);
     END;
     /

PL/SQL procedure successfully completed.

SQL> SELECT i.index_name         index_name
          , t.blocks             table_blocks
          , i.clustering_factor  index_clustering_factor
          , i.num_rows           index_rows
       FROM user_indexes i
       JOIN user_tables t USING (table_name)
      WHERE table_name = 'ROW_MIG1';

INDEX_NAME     TABLE_BLOCKS INDEX_CLUSTERING_FACTOR INDEX_ROWS
-------------- ------------ ----------------------- ----------
ROW_MIG1_PK          100506                  100000     100000
ROW_MIG1_IDX         100506                  100000     100000

SQL>

Then execute the statement again:

SET AUTOTRACE TRACEONLY;
SET TIMING ON;

SELECT /* no row migration */ *
  FROM row_mig1 d1
  JOIN row_mig2 d2 ON (d1.x = d2.x)
 WHERE d1.filter = 0
   AND d2.filter = 0;

SET TIMING OFF
SET AUTOTRACE OFF

Because the statistics are almost identical, the plan doesn’t change, nor does the cost. What does changed is the execution time as well as the number of logical gets:

577 rows selected.

Elapsed: 00:00:30.90

Execution Plan
----------------------------------------------------------
Plan hash value: 3004301745

---------------------------------------------------------------------
| Id | Operation                     | Name         | Rows  | Cost  |
---------------------------------------------------------------------
|  0 | SELECT STATEMENT              |              |  5882 | 11780 |
|  1 |  NESTED LOOPS                 |              |       |       |
|  2 |   NESTED LOOPS                |              |  5882 | 11780 |
|  3 |    TABLE ACCESS BY INDEX ROWID| ROW_MIG2     |  5882 |  5896 |
|* 4 |     INDEX RANGE SCAN          | ROW_MIG2_IDX |  5882 |    12 |
|* 5 |    INDEX UNIQUE SCAN          | ROW_MIG1_PK  |     1 |     0 |
|* 6 |   TABLE ACCESS BY INDEX ROWID | ROW_MIG1     |     1 |     1 |
---------------------------------------------------------------------            

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("D2"."FILTER"=0)
   5 - access("D1"."X"="D2"."X")
   6 - filter("D1"."FILTER"=0)

Statistics
----------------------------------------------------------
        925  recursive calls
          0  db block gets
      14271  consistent gets
      11952  physical reads
          0  redo size
      39012  bytes sent via SQL*Net to client
        837  bytes received via SQL*Net from client
         40  SQL*Net roundtrips to/from client
         13  sorts (memory)
          0  sorts (disk)
        577  rows processed

Summary

The article investigates the effects of row migration on the clustering factor and the optimizer. A “proof of concept” SQL demonstrates that row migration can affect the optimizer. The lessons from this article are:

The “insert empty, update everything” pattern can lead to a very high row migration rate.
DBMS_STATS doesn’t populate the CHAIN_CNT.
A high row migration rate can cut the clustering factor, even below its theoretic minimum.
A wrong clustering factor can affect the optimizer and result in an suboptimal plan.

Disclaimer

Please note that this trap was intentionally built, just to proof that row migration can potentially influence the optimizer. There are better ways to tune that particular SQL, foremost a better indexing approach.

I have put many adjectives in quotation marks because they are not in line with the technical definition of the respective noun.

The “insert empty, update everything“ anti-pattern was used to create a very high row migration rate. Although that anti-pattern can lead to 100% migration rate, it does not always.

I have successfully verified my results on Oracle 10gR1, 11gR1 and 11gR2.

Thanks

Thanks to the guys at 25th-floor who verified my results on Oracle 11gR1.

Row Migration and Row Movement

Markus Winand — Tue, 23 Feb 2010 02:52:34 PST

The Oracle database knows three distinct processes that are easily mixed up: Row Chaining, Row Migration and Row Movement.

Luckily all three are well described in excellent articles: The Secrets of Oracle Row Chaining and Migration and Row Movement in Oracle.

For the impatient, I provide some very short definitions:

Row Chaining: Distribution of a single table row across multiple data blocks.
Row Migration: Relocation of an entire table row to a new place, without updating the indexes.
Row Movement: Relocation of an entire table row to a new place and updating the indexes.

This article was inspired by the question if Oracle 11r2 performs Row Movement instead of Row Migration for ordinary UPDATE statements—that is, in absence of partitions. The short answer is: no, it doesn’t. The long answer is the rest of this article.

The difference between Row Chaining and Row Migration is somehow understandable: If the row doesn’t fit into a single data block, it must be chained; otherwise it can be migrated as a whole. The limitation of the Row Migration is that it does not update the indexes on the table. That means, the ROWID that is stored in the index still refers to the old location of the row. An additional block, the new location of the row, must be read to fetch the required data.

The more modern Row Movement is different as it updates the corresponding indexes—the ROWID actually changes. This has benefits on the long run, because the additional block read can be avoided in the TABLE ACCESS BY INDEX ROWID operation. On the short run, the actual UPDATE operation is much more complex. I suppose this is the reason why a regular update does not (yet) trigger a Row Movement.

The Row Movement was introduced to support an UPDATE on a partition key—so that a table row is moved from one table partition to another one. In the meanwhile it is also used for flashback and space management—as described in the above-mentioned article.

As of Oracle Release 11g2, Row Movement is optional and disabled per default. It can be enabled per table with a very trivial ALTER TABLE statement. The usual reason to enable it is that one of the features which require Row Movement is used; partition key update, table shrink and flashback. There is hardly any reason not to enable row movement—the only side effect is that ROWID’s might change; that should not affect well designed applications.

Row Migration has some considerable problems, as pointed out in the later in this article. On the other hand, Row Movement has also a drawback; that is, the update of all the affected indexes can be very expansive. It’s a trade off between read and write performance. While Row Movement is more expansive for UPDATES, it maintains best index performance. In contrast, the Row Migration favors UPDATE performance over index speed. Although not true for all cases, I believe that most data is read more often than it is written—especially in our modern society where nobody every deletes data—so that Row Movement is generally the better choice.

The article examines how to get 100% migrated rows with the “insert empty, update everything” anti-pattern, why DBMS_STATS isn’t a perfect substitute for ANALYZE TABLE, the immunity of migrated rows to alter table ... shrink space and why PCTFREE is still the only rescue from DBAs perspective.

Just to show what I mean, I repeat a modified version of the test originally created by Tom Kyte and reused by Martin Zahn:

CREATE TABLE row_migration_demo (
  a CHAR(2000),
  b CHAR(2000),
  c CHAR(2000),
  d CHAR(2000),
  e CHAR(2000),
  x int,
  constraint row_migration_pk primary key (x)
) enable row movement
/

I have changed the size of the CHAR field to 2k because my block size is 8k whereas it was 4k in the original example. I have also put the x column to the end for a better verification of Row Migration versus Row Chaining. Finally I enable Row Movement, which does actually not change anything but allows me to perform an alter table ... shrink space.

The next step is a classical insert empty, update everything sequence:

INSERT INTO row_migration_demo (x) values (1);
UPDATE row_migration_demo set a = 'a', b='b', c='c' where x=1;
COMMIT;

Up till now everything is perfectly fine and the entire row is in a single block:

SQL> select * from row_migration_demo where x=1;

         X
----------
         1

SQL> SELECT a.name, b.value
       FROM v$statname a, v$mystat b
      WHERE a.statistic# = b.statistic#
        AND lower(a.name) = 'table fetch continued row';

NAME                             VALUE
--------------------------- ----------
table fetch continued row            0

So, lets do it again and insert a second row:

INSERT INTO row_migration_demo (x) values (2);
UPDATE row_migration_demo set a = 'a', b='b', c='c' where x=2;
COMMIT;

And select it again:

SQL> select * from row_migration_demo where x=2;

         X
----------
         1

SQL> SELECT a.name, b.value
       FROM v$statname a, v$mystat b
      WHERE a.statistic# = b.statistic#
        AND lower(a.name) = 'table fetch continued row';

NAME                             VALUE
--------------------------- ----------
table fetch continued row            1

Hooray, the row is migrated:

SQL> ANALYZE TABLE row_migration_demo COMPUTE STATISTICS;

PL/SQL procedure successfully completed.

SQL> select num_rows, chain_cnt
       from user_tables
      where table_name='ROW_MIGRATION_DEMO';

  NUM_ROWS  CHAIN_CNT
---------- ----------
         2          1

Note: ANALYZE is deprecated. Yup, I know, but DMBS_STATS.GATHER_TABLE_STATS does not propagate the CHAIN_CNT column. Ask Tom!

It’s hard to figure out if the row is chained or migrated—I am actually not sure is there any difference in the data structure. Because the additional table fetch continued row occurs for the very first column, I believe the row is migrated:

SQL>  select length(a) from row_migration_demo where x=2;

 LENGTH(A)
----------
      2000

SQL> SELECT a.name, b.value
       FROM v$statname a, v$mystat b
      WHERE a.statistic# = b.statistic#
        AND lower(a.name) = 'table fetch continued row';

NAME                             VALUE
--------------------------- ----------
table fetch continued row            2

To be on the safe side, I will double verify:

INSERT INTO row_migration_demo (x, a) values (3, 'a');
UPDATE row_migration_demo
   SET b = 'b', c = 'c', d = 'd', e = 'e'
 WHERE x=3;

Indeed, the a column is at the place where the row was inserted while the e column is somewhere else:

SQL>  select length(a) from row_migration_demo where x=3;

 LENGTH(A)
----------
      2000

SQL> SELECT a.name, b.value
       FROM v$statname a, v$mystat b
      WHERE a.statistic# = b.statistic#
        AND lower(a.name) = 'table fetch continued row';

NAME                             VALUE
--------------------------- ----------
table fetch continued row            2

SQL>  select length(e) from row_migration_demo where x=3;

 LENGTH(A)
----------
      2000

SQL> SELECT a.name, b.value
       FROM v$statname a, v$mystat b
      WHERE a.statistic# = b.statistic#
        AND lower(a.name) = 'table fetch continued row';

NAME                             VALUE
--------------------------- ----------
table fetch continued row            3

To make things even worse, single rows can be migrated and chained. If we insert two more records, it becomes visible:

INSERT INTO row_migration_demo (x) values (4);
UPDATE row_migration_demo
   SET a='a', b='b', c='c', d='d', e='e'
 WHERE x=4;

INSERT INTO row_migration_demo (x) values (5);
UPDATE row_migration_demo
   SET a='a', b='b', c='c', d='d', e='e'
 WHERE x=5;

COMMIT;

Each of those rows must be chained, because they need more space than a single block has. However, due to migration, the row is actually distributed to three blocks:

SQL>  select length(a) from row_migration_demo where x=5;

 LENGTH(A)
----------
      2000

SQL> SELECT a.name, b.value
       FROM v$statname a, v$mystat b
      WHERE a.statistic# = b.statistic#
        AND lower(a.name) = 'table fetch continued row';

NAME                             VALUE
--------------------------- ----------
table fetch continued row            4

SQL>  select length(b) from row_migration_demo where x=5;

 LENGTH(B)
----------
      2000

SQL> SELECT a.name, b.value
       FROM v$statname a, v$mystat b
      WHERE a.statistic# = b.statistic#
        AND lower(a.name) = 'table fetch continued row';

NAME                             VALUE
--------------------------- ----------
table fetch continued row            5

SQL>  select length(c) from row_migration_demo where x=5;

 LENGTH(C)
----------
      2000

SQL> SELECT a.name, b.value
       FROM v$statname a, v$mystat b
      WHERE a.statistic# = b.statistic#
        AND lower(a.name) = 'table fetch continued row';

NAME                             VALUE
--------------------------- ----------
table fetch continued row            7

Please note that the “continued fetch” increased by two for the select on column c . The statistics show that all but one row is chained (the very first row):

SQL> ANALYZE TABLE row_migration_demo COMPUTE STATISTICS;

PL/SQL procedure successfully completed.

SQL> select num_rows, chain_cnt
       from user_tables
      where table_name='ROW_MIGRATION_DEMO';

  NUM_ROWS  CHAIN_CNT
---------- ----------
         5          4

If we continue the game and continue to insert data in this way, we get an astonishing chain count:

begin
   for i in 10..1000 loop
      INSERT INTO row_migration_demo (x) values (i);
      UPDATE row_migration_demo set a ='a', b = 'b', c = 'c' where x=i;
   end loop;
end;
/

SQL> ANALYZE TABLE row_migration_demo COMPUTE STATISTICS;

PL/SQL procedure successfully completed.

SQL> select num_rows, chain_cnt
       from user_tables
      where table_name='ROW_MIGRATION_DEMO';

  NUM_ROWS  CHAIN_CNT
---------- ----------
       996        995

All the inserted rows are chained.

The only way to clean up the mess is to move the table. Shrinking doesn’t help to much, especially not if there was no DELETE or UPDATE that has freed some space:

SQL> alter table row_migration_demo shrink space;

Table altered.

SQL> analyze table row_migration_demo compute statistics;

Table analyzed.

SQL> select num_rows, chain_cnt
       from user_tables
      where table_name='ROW_MIGRATION_DEMO';

  NUM_ROWS  CHAIN_CNT
---------- ----------
       996        995

Even if some space is freed and rows are moved, the chaining is not notably reduced:

SQL> delete from row_migration_demo where x < 200;

195 rows deleted.

SQL> analyze table row_migration_demo compute statistics;

Table analyzed.

SQL> select num_rows, chain_cnt
       from user_tables
      where table_name='ROW_MIGRATION_DEMO';

  NUM_ROWS  CHAIN_CNT
---------- ----------
       801        801

SQL> alter table row_migration_demo shrink space;

Table altered.

SQL> analyze table row_migration_demo compute statistics;

Table analyzed.

SQL> select num_rows, chain_cnt
       from user_tables
      where table_name='ROW_MIGRATION_DEMO';

  NUM_ROWS  CHAIN_CNT
---------- ----------
       801        800

Please note that the shrink has corrected one chained row. That proofs that a Row Movement assigns a new ROWID and updates the index. However, one “unchained” row isn’t really the correction I would like to see. The only way to correct all chains, is to mote the table:

SQL> alter table row_migration_demo move;

Table altered.

SQL> alter index row_migration_pk rebuild;

Index altered.

SQL> analyze table row_migration_demo compute statistics;

Table analyzed.

SQL> select num_rows, chain_cnt
       from user_tables
      where table_name='ROW_MIGRATION_DEMO';

  NUM_ROWS  CHAIN_CNT
---------- ----------
       801          0

No chaining anymore.

There is actually another way to correct row migration. The Oracle documentation suggest to delete and re-insert all affected rows. Watch out for your triggers.

From administrators‛ perspective, the only way to prevent row migration in the first place is to increase PCTFREE. The complete re-execution of this test with PCTFREE increased to 50% “solves” the problem and no chained rows occur anymore:

drop table row_migration_demo;
CREATE TABLE row_migration_demo (
  a CHAR(2000),
  b CHAR(2000),
  c CHAR(2000),
  d CHAR(2000),
  e CHAR(2000),
  x int,
  constraint row_migration_pk primary key (x)
) enable row movement pctfree 50
/
begin
   for i in 1..1000 loop
      INSERT INTO row_migration_demo (x) values (i);
      UPDATE row_migration_demo set a ='a', b = 'b', c = 'c' where x=i;
   end loop;
end;
/
commit;
analyze table row_migration_demo compute statistics;
select num_rows, chain_cnt
       from user_tables
      where table_name='ROW_MIGRATION_DEMO';

  NUM_ROWS  CHAIN_CNT
---------- ----------
      1000          0

If this is not acceptable, there is one very last option: change the application.

However, it would be very nice if an operation—similar to shrink—would move migrated rows. It would be even more compelling if a table option would allow to disable Row Migration in favor of Row Movement.

Appendix

After some years of professional experience, I always wonder why a particular idea of mine should be unique. So I suspected that this feature might be there, somewhere hidden in Oracle. So I checked for hidden parameters (thanks to a little documentation on that topic):

SQL> select a.ksppinm parameter, b.kspftctxvl
       from x$ksppi a join x$ksppcv2 b on (a.INDX+1= b.kspftctxpn)
      where a.KSPPDESC like '%movement%';

PARAMETER                                KSPFTCTXVL
---------------------------------------- ----------
_disable_implicit_row_movement           FALSE

SQL> alter session set "_disable_implicit_row_movement" = true;

Session altered.

SQL> select a.ksppinm parameter, b.kspftctxvl
       from x$ksppi a join x$ksppcv2 b on (a.INDX+1= b.kspftctxpn)
      where a.KSPPDESC like '%movement%';

PARAMETER                                KSPFTCTXVL
---------------------------------------- ----------
_disable_implicit_row_movement           TRUE

SQL>

As suggested by the name of the parameter, this doesn’t change anything in the direction I would like.

Oracle Trace File Rotation

Markus Winand — Mon, 01 Feb 2010 09:30:11 PST

Under very rare circumstances, I need Oracle SQL trace files from a long period of time. Because trace files usually grow large—especially over several days—there is the need to rotate the trace file during that time so that they can be compressed and put away. The problem is that there is no “rotate tracefile“ button in Oracle. However, I have found a “undocumented feature“ that does exactly that—without disabling tracing.

My procedure uses the close_trace call of oradebug. This call closes the currently written trace file for a session. Alex Gorbachev has used this to delete big trace files that are still open. My procedure goes a little bit further and exposes one more side effect of oradebug close_trace.

Warning: The technique described here uses the undocumented and unsupported oradebug facility. Although I am not aware of any negative side effects of this procedure, the use of this method takes place at your own risk.

The oradebug utility can be used from the SQL*Plus prompt. If you have an account with sufficient rights (sigh) you can attach to any session using the oradebug setorapid command. To obtain the required Oracle PID, just query the v$process view:

SQL> SELECT s.sid, s.serial#, p.pid 
       FROM v$session s, v$process p
      WHERE s.paddr=p.addr;

       SID    SERIAL#        PID
---------- ---------- ----------
         1          1          2
[... skipped ...]
        20       3126         21

26 rows selected.

SQL> oradebug setorapid 21
Oracle pid: 21, Unix process pid: 145, image: oracle@test.fatalmind.com
SQL>

Once attached to a specific process, every oradebug operation is applied to that particular process. The trick to rotate the trace file is very simple and exploits a “feature” of UNIX file systems; that is, that the name of the file is used only at the moment the file is opened. Once the file is open, all access is managed with the so called inode and the name is not relevant for the read and write operations anymore. The inode system has some more implications, one of them is frequently used to rotate log files without data loss. Another implications makes it hard to delete open files, as Alex Gorbachev explained.

The log rotation trick is to rename the open file and then cause the writing process to re-open that file. All write operations to the renamed file will be written properly to new file name—more correctly expressed: to the file which can be accessed under the new name. If the writing process re-opens the file under its original name, it will create a new file because there is no file with that name anymore. Every subsequent write operation goes to the new file. The original file will not grow anymore and can be taken away safely.

Although the oradebug command name close_trace doesn’t suggest that the trace file is reopened, it is—if SQL tracing is enabled. So we have everything to rotate a trace file. Consider the following example:

$ ls -lrt TEST_ora_14457.trc*
-rw-r----- 1 oracle oinstall 14758723 2010-02-01 TEST_ora_14457.trc
$ mv  TEST_ora_14457.trc  TEST_ora_14457.trc.old

After renaming the file, the file still grows as the SQL trace is written. You can now use oradebug close_trace to actually rotate the file:

SQL> oradebug close_trace
Statement processed.
SQL>

The verification in the file system shows that the ‘old’ file became bigger, and the new file is also filling up:

$ ls -lrt TEST_ora_14457.trc*
-rw-r----- 1 oracle oinstall 18878704 2010-02-01 TEST_ora_14457.trc.old
-rw-r----- 1 oracle oinstall  1555972 2010-02-01 TEST_ora_14457.trc
$

Because I often need to trace many sessions, I wrote a very tiny script (switch_traces.sql) that creates another script to re-open all trace files:

set feedback off
set pages 0
set heading off
set echo off

spool oradebug_close_traces.sql

select cmd from (
select pid, 1 o, 'oradebug setorapid ' || pid cmd from v$process
UNION ALL
select pid, 2 o, 'oradebug flush' from v$process
UNION ALL
select pid, 3 o, 'oradebug close_trace' from v$process) order by pid,o;

spool off
set echo on
set feedback on
set pages 1000
set heading 0n

prompt
prompt c&p >>> @oradebug_close_traces.sql <<< to switch traces

This script will close all traces in all sessions and continue tracing to new files if SQL tracing is enabled. You might want to add additional where clauses to limit your scope—e.g., to skip background processes. The script will also issue a flush before the close_trace. I don’t know if this is required or not, it doesn’t seem to make any harm. The use of the script is very simple:

SQL> @switch_traces.sql
SQL> set feedback off
SQL> set pages 0
SQL> set heading off
SQL> set echo off
oradebug setorapid 1
oradebug flush
oradebug close_trace

[... skipped ...]

oradebug setorapid 29
oradebug flush
oradebug close_trace
SQL> set feedback on
SQL> set pages 1000
SQL> set heading on
SQL>
SQL> prompt

SQL> prompt c&p >>> @oradebug_close_traces.sql <<< to switch traces
c&p >>> @oradebug_close_traces.sql <<< to switch traces
SQL> @oradebug_close_traces.sql
SQL> oradebug setorapid 1
ORA-00072: process "Unix process pid: 0, image: PSEUDO" is not active
SQL> oradebug flush
Statement processed.
SQL> oradebug close_trace
Statement processed.
SQL> oradebug setorapid 2
Oracle pid: 2, Unix process pid: 137, image: oracle@test.fatalmind.com
SQL> oradebug flush
Statement processed.
SQL> oradebug close_trace
Statement processed.

[... skipped ...]

Oracle pid: 29, Unix process pid: 138, image: oracle@test.fatalmind.com
SQL> oradebug flush
Statement processed.
SQL> oradebug close_trace
Statement processed.
SQL>

In case you have not forgotten to rename your trace files you will see the new files growing now.

Read also my previous article To Trace or Not to Trace for information about fine grained SQL tracing.

Oracle JDBC PreFetch Portability

Markus Winand — Fri, 29 Jan 2010 06:23:11 PST

In a previous article about (network) latencies, I have presented the OracleStatement.setRowPrefetch() method to reduce round trips for SELECT statements. I must admit that I was a little bit wrong. Not in the essence; increasing the PreFetch size is still a great (read: simple) way to reduce latencies. However, there is a better approach to set the PreFetch size.

Since the raise of Java, many portability issues have vanished. There are most definitely new ones—think of mobile devices—and some others are still eminent—think of Internet Explorer versus all other browsers. Nevertheless, Java improved the situation quite notable—most credible: Unicode support from the beginning.

Still there are some people like me who just cast the portable JDBC class to whatever they believe it is, and do some cool things with that. Every now and then there is the need to use proprietary functions, but I should have checked the portable API first. Shame on me. In fact there is the standard JDBC method Statement.setFetchSize() that does exactly the same. Even more interesting, it’s there since ages (JDBC 2.1 at least).

Although the documentation seems to be exactly what I was looking for, I wrote a little test program to verify. Just because I know that Oracle can profit from an increased PreFetch size and the standard provides a method to set it, it doesn’t mean that it works. Especially not if the standards says that the method is a hint to the JDBC driver. So let’s double check.

import java.sql.*;
import oracle.jdbc.*;

class JDBCFetchSize {
  public static void main(String[] argv) throws Exception {
    if (argv.length != 4) {
      throw new RuntimeException("JDBCFetchSize <jdbc-connect url> "
                               + "<username> <password> <FetchSize>");
    }
    Connection con = connect(argv[0], argv[1], argv[2]);
    int roundtripoffset = getRoundTripOffset(con);

    PreparedStatement ps = con.prepareStatement(seqsql);

    setFetchSize(ps, argv[3]);

    int start = getCurrentRoundTripCount(con);
    long startts = System.currentTimeMillis();

    int rowsfetched = executeTest(ps);

    int end    = getCurrentRoundTripCount(con) - roundtripoffset;
    long endts = System.currentTimeMillis();

    ps.close();

    System.out.println(rowsfetched + " rows fetched in "
                       + (end-start) + " server round trips and "
                       + (endts-startts) + " ms");
  }

  // oracle SQL to fetch round trip counter
  final static String rtsql =
      " select value "
    + "   from v$mystat ms, v$statname sn"
    + "  where ms.value > 0"
    + "    and ms.statistic#=sn.statistic#"
    + "    and sn.name IN ('SQL*Net roundtrips to/from client')";

  // sequence generator
  final static String seqsql =
      " select level from dual connect by level <= ?";

  static PreparedStatement rtstm;

  private static Connection
    connect (String url, String user, String pass)
    throws ClassNotFoundException, SQLException
  {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    return DriverManager.getConnection(url, user, pass);
  }

  private static int
    getRoundTripOffset(Connection con)
    throws SQLException
  {
    // The very first time we call it, it is not prepared.
    // so we don't consider that at all.
    getCurrentRoundTripCount(con);

    int first = getCurrentRoundTripCount(con);
    return getCurrentRoundTripCount(con) - first;
  }

  static void
    setFetchSize(Statement stm, String size)
    throws SQLException
  {
    System.out.println("getFetchSize(): " + stm.getFetchSize());

    stm.setFetchSize(Integer.parseInt(size));

    // uncomment the not portable OracleStatement
    // variant for verification
    // ((OracleStatement)stm).setRowPrefetch(Integer.parseInt(size));

    System.out.println("getFetchSize(): " + stm.getFetchSize());
  }

  static int
    executeTest(PreparedStatement ps)
    throws SQLException
  {
    int cnt = 0;
    ps.setInt(1, 100000);
    ResultSet rs = ps.executeQuery();
    while (rs.next()) {
      ++cnt;
    }
    rs.close();
    return cnt;
  }

  static int
    getCurrentRoundTripCount(Connection con)
    throws SQLException
  {
    if (rtstm == null) {
      rtstm = con.prepareStatement(rtsql);
    }
    ResultSet rs = rtstm.executeQuery();
    rs.next();
    int rv = rs.getInt(1);
    rs.close();

    return rv;
  }
}

The result proofs that the portable JDBC setFetchSize() is the better choice over Oracle’s proprietary setRowPrefetch():

$ java JDBCFetchSize jdbc:oracle:thin:@//host/service user password 10
getFetchSize(): 10
getFetchSize(): 10
100000 rows fetched in 10002 server round trips and 521 ms
$ java JDBCFetchSize jdbc:oracle:thin:@//host/service user password 20
getFetchSize(): 10
getFetchSize(): 20
100000 rows fetched in 5002 server round trips and 324 ms
$ java JDBCFetchSize jdbc:oracle:thin:@//host/service user password 50
getFetchSize(): 10
getFetchSize(): 50
100000 rows fetched in 2002 server round trips and 191 ms
$

Calling setRowPrefetch() affects the number of server round trips as expected.

And because consistency (=Quality) matters, I will right away update the previous article.

Recent oracle docs list some limitations.

After all, there is still some room for portability pitfalls. If you want to change the default value from 10 to some other value, Oracle has two solutions for that. Read again: Oracle has two solutions for that. JDBC has none (correct me if I am wrong).

When passing Properties to the DriverManager.getConnection() method, Oracle allows to set the defaultRowPrefetch property. The other way is to cast the JDBC Connection to OracleConnection and use the setDefaultRowPrefetch() method (hurray — cast again!). Disclaimer: I did not test any of those.

Please note that my tests were based on Oracle 11r2, other release might behave differently. During my research I found some Oracle 8.1.6 docs which also mention the portable setFetchSize() method, that makes me believe it works since then.

Latency: Security vs. Performance

Markus Winand — Tue, 22 Dec 2009 00:01:53 PST

At a client’s Christmas party, I have witnessed a very short talk between a network engineer and a top level manager. The network guy explained that the firewall adds about 0.2 milliseconds latency to each round trip between the application server and the database, which adds up to some hours in one particular case. So the manager asked what could be done and the network guy quickly provided two solutions:

Change the application to make less round trips
Accept the security risk and don’t put a firewall in between those two tiers

Funny enough the network guy explained that the second option needs the managers signature because it bypasses the corporate security guidelines and somebody must take the responsibility for that.

Consider you are the manager, would you sign such a paper?

I guess I would not, and I also guess my client didn’t (otherwise it was against my advice ).

In this article I will explain the term latency, give some insight why it is so easy to build latency bandit applications and show some ways to reduce latencies.

Weekend Shopping

The best explanation for latency I ever came across is from the guys over at RoughSea. There is a video that describes shopping like that (simplified):

Drive to the super market
Locate what’s needed (e.g., milk)
Pay
Store the item in the car
Drive back home
Store the item (e.g., fridge)
Then start again for the next item on the shopping list (e.g., corn flakes).

The whole 20 minutes video is a great watch and covers many other frequent issues as well. If you just want to watch that particular scene, here is a direct youtube link.

When watching this video it’s hard not to D’oh! The video makes it very obvious that most of the time is spent to drive. The actual shopping takes much less time than all the overhead for driving, parking, paying,….

Latency in software has very similar causes, most prominently that the application asks the database for many items individually.

How Can This Happen?

This question has actually two sides:

How can somebody implement software that way?
How can a latency of 0.2 milliseconds make any difference?

First of all let’s look at the 0.2 milliseconds statement again. This value was provided by the network engineer as additional latency because of the new firewall. That’s on top of the latency the network anyway, even without firewall. A typical value for a switched LAN, without firewall, is around 0.1 – 0.2 milliseconds. If a firewall adds 0.2ms to that, the overall latency raises to about 0.4ms per round trip. In other words 2500 round trips take a second. Although this sounds a lot, it’s actually not so much if you consider how many round trips take place for an average operation. A average latency of 0.4 milliseconds is still very reasonable and will cause problems only in extreme cases. Nevertheless latencies can grow because of increased network complexity or during peak load.

The situation becomes more relevant because one round trip is hardly enough to perform any reasonable operation. Realistically, an application issues many different SQLs to accomplish a single task. A single SQL statement might also need multiple round trips to complete; a SELECT statement against an Oracle database needs typically two round trips, as explained farther down the article. An application that executes 10 SELECTs has already 8ms of latencies. More complex applications might execute 100 SQL’s and reach 80 milliseconds of latencies. I have seen batch jobs that issue 50 million SQLs so that the round trip latency sums up to 10 hours or more!

So, why! Why is it done that way? Because it is easier to implement. Therefore faster to implement and less error prone. Consider the shopping example again. The algorithm is very simple; just repeat all steps until the shopping list is done. This works irrespective of the size of the shopping list.

An improved variant of the algorithm, that is latency aware, might look like this:

Drive to the super market
FOR EACH ITEM: Locate item (milk, corn flakes)
Pay
FOR EACH ITEM: Store into the car
Drive back home
FOR EACH ITEM: Store the item (e.g., fridge, larder)

Please note that what has been one loop over the complete algorithm before, was turned into three loops somewhere inside the algorithm. The complexity has grown. Even worse, there are more complex error scenarios with the new algorithm. E.g., a very long shopping list might exceed the capacity of the car to transport all the items. Such scenarios need special error handling. Alternatively the algorithm could prevent that scenario by splitting up large purchases into multiple smaller parts. The additional complexity requires more time to implement and introduces more scenarios to test. The efforts quickly explode, especially since the overflow scenarios are hard to test.

One more word of warning: The case that your shopping doesn’t fit into your car might look very unlikely, and perhaps it is. However, in computer programs, there a less natural limits than in real life—e.g., what your family can eat. From personal experience I would say chances are 2:1 to hit this error scenario within the first year that implementation is used.

After all, there is a trade off between software complexity and software performance. From another point of view, it is even a trade off between software development speed and software runtime speed. If you are pushed to deliver working software, better sooner than later, which algorithm would you implement?

Finally, there is also a reason why latency bastard applications can be produced by accident:

But it works in development/test environment

Very often, development takes place on desktop hardware where everything is installed on the same machine. Obviously, there is no firewall in place nor is there any network involved. The involved latency in such an environment is only a fraction (e.g., a 10th) of the latency involved in an n-tier production deployment. In the shopping example, this would mean that the super market is in the room next to the kitchen. Item-by-item shopping might work fine in that case. Unfortunately this type of shopping infrastructure is not yet commonplace.

How to Avoid it

So let’s look into the more technical part of the story. Which techniques can be used to reduce the number of database round trips? The following list gives the three most effective ways to avoid unneeded latency:

Use Joins
Use array/batch functionality
Use advanced techniques (open cursors, fetch buffers, bulk/implicit commit, …)

Join

The most important technique to avoid latencies is to tell the database which data is needed at once. A very problematic implementation is to query one table and issue another select within the fetch loop of the first statement. I refer to this method as nested select as opposed to a nested loop join. Both methods implement the same idea while the nested select involves additional round trips. With SQL, it is always possible to construct a join that delivers the same result within the same time—but usually much faster.

The nested select anti-pattern can be applied to any extend. They can be nested several levels deep, or kept at one level that triggers more than just one nested select. I have mentioned the 50 million SQL batch job that took more than 10 hours, just for latency. This particular job has queried some 10 additional child tables for each record fetched from the main query. The main query has returned some million records so that the latency became the most dominant performance factor. In that particular case, the developers explained that the coding guidelines force them to use the existing functions instead of writing new SQL. Another difficulty was that the nested statements were depended on the data from the main table so that a trivial join doesn’t work (conditional join). In fact there were only three different ways to join the tables, therefore the correct solution is to make three different SQLs. In case a single result set is needed (e.g., because of a order clause), a UNION can be used. After all, 10 out of 12 hours overall run time was saved just by using joins.

Batch Execution

The second major latency reduction technique is to properly use batch execution that is sometimes also called array execution. Many databases provide a way to execute more than one statement in one round trip. Not all APIs make this functionality available to the client, but JDBC does. However, there are some pitfalls:

Error handling is terrible.
Although the API defines methods to identify which statement failed, the API is very cumbersome to use. To make things worse it’s not even defined if the execution of the batch stops on errors. My Best Current Practice is to set a Savepoint prior array execution. In case an error occurs, a rollback to the Savepoint can be done.
Very large batches.
Very large batches can have their own problems. As with the shopping example, every implementation must be prepared to use multiple batches in case the number of executions becomes very high.
Does not always work faster.
The actual performance gain is vendor specific and, more importantly, depends on the method used. For example, Oracle benefits from batching only if PreparedStatements are used. Batching Statement objects doesn’t give any performance gain with Oracle. On the other side, MySQL can also benefit from Statement batches. As always, the advice is to use PreparedStatements whenever possible. This will almost always result in major performance improvements.
The JDBC API is optional.
Prior using that API you must check if the driver supports it. Realistically I would suggest to build an abstraction that takes care of this and executes the statements individually if batch execution is not support. Ideally the JDBC driver would do so.

These are important issues, that cause considerable efforts, to take care of when using batch execution. Still all the efforts can easily pay off. In many cases the execution of a PreparedStatement batch with two elements takes no longer than the execution of a single statement, so that the execution is effectively twice as fast. For trivial SQL statements, a speed improvement of factor 10 is within easy reach. That justifies some efforts.

Advanced Techniques

Besides the two most powerful techniques explained above, there are many more ways to reduce latency. They can be classified in two different types: SQL improvements or API improvements.

Let’s start with SQL improvements.

Join.
Again, just as a reminder. The most powerful way to reduce latencies.
IN clauses
Consider to use an IN clause to fetch multiple records form the same table in one run. Unfortunately, the SQL language doesn’t guarantee that the order of the result sets corresponds to the IN clause order. An additional layer (e.g., HashMap) might be used to handle that issue. Another potential problem is that the number of items in the IN list has a maximum—1000 in Oracle: ORA-01795. However, if the list can grow that big, chances are good that the list itself was retrieved from the database. In that case, a join might be the better solution—just to mention it once more.
UNION
If data from two different tables is needed that have the same structure, you can use a UNION to fetch both at once. For example a CURRENT and a HISTORY table might contain the life-cycle of a row. To obtain the current and all past versions, both tables must be queried. Another examples are pre-calculated aggregates; one table contains all the aggregates as of yesterday and a second table all the new records since then. To get the total figure—as of “now”—both tables must be queried.
RETURNING clause.
If you use a surrogate primary key that is generated from a sequence, you must usually perform two steps to fetch the surrogate key. The least troublesome solution is to first SELECT the new ID and the perform the INSERT with that ID. The Oracle answer to this problem is the RETURNING clause of the INSERT statement. Although the JDBC API acknowledges the problem since version 3.0 the Oracle implementation is not very useful—it returns the ROWID. A workaround is available.
1×1 Cartesian product or CROSS join

The so called Cartesian production (everything with everything) is sometimes used accidentally and results in huge result sets. That’s the reason why SQL-92 introduced a distinct keyword for it: CROSS. On the other side, the Cartesian product of two single row statements is, again, a single row:

SELECT a.*, b.* FROM a CROSS JOIN b WHERE a.id=1 AND b.id=2

This technique can be used to combine multiple single-row statements into a single serve round trip. Please note that this works only if both statements return exactly one row, not more, not less. This is more often a last resort option than a first choice.

These are the typical SQL based techniques to reduce the number of individual SQLs executed that in turn reduces the number of round trips. Another strategy to reduce the number of round trips is to use the API differently:

Batch execution.
Sorry for the repetition, but it’s the most important technique.
Open cursors
When executing the same PreparedStatement multiple times in a row–with different bind values—the same PreparedStatement instance should be used for all executions. The first execution of a Statement involves an additional round trip to open the cursor. This additional round trip can be avoided when the same PreparedStatement instance is used again. As a side effect, PARSING overhead is also reduced.
Increased PreFetch
The Oracle network layer utilizes a technique called PreFetch to reduce round trips for SELECT statements. Instead of retrieving every single row individually from the server, a certain number or rows is transferred in one round trip. The Oracle client library handles this PreFetch transparently and still provides one row after each other to the application software. For SELECT statements, PreFetch is almost as powerful as batch execution for INSERT/UPDATE/DELETE statements and can reduce the latencies by an order of magnitude for large result sets. The current default for the Oracle JDBC driver is to fetch at most 10 rows in each server round trip. The JDBC standard provides a way manipulate this value: Statement.setFetchSize(20); If your application typically fetches 20 records, you might set the value accordingly. There is no (notable) harm for statements that return less than 20 records.
UPDATE 2010-01-29: This paragraph was updated to reflect my later findings regarding Oracle JDBC PreFetch Portability.

The following chart shows various select executions with different PreFetch sizes. The first pair is a mass select to retrieve 40000 rows from the database. Fetching twice as many records in one round trip, cuts the overall time down to the half. The other bars show the execution of a statement that returns 20 rows. To get reasonable values those statements were executed 1000 times in a row. The repeated execution allows the open cursor optimization to be applied as well. The middle pair of bars shows the execution performance with a PreparedStatement that is closed after each execution. The last bars show the same but the PreparedStatement was opened only once and used then for all 1000 executions.

The latency difference between 10 records PreFetch with a “closed cursor” to the 20 records PreFetch with an “open cursor” is 70%.

Please note that the statement used for this statistics was very trivial and can be assumed to be executed instantly. The improvements visible in the chart above do therefore indicate the maximum possible improvement.

The Bottom Line

Increasing network complexity introduces additional latencies that might affect applications performance.

There are many ways to reduce server round trips to make an application less sensitive to network latencies. Most of them involve revised process flows and increase application complexity.

“Firewall qualified” applications already have an advantage in competition and attest awareness for complex production deployments.

It’s not fatal to open your mind to new approaches.

How Internet Works

There is another excellent cartoon that explains latency in context of the Internet.

To Trace or Not to Trace

Markus Winand — Tue, 24 Nov 2009 09:49:44 PST

I have always been a fan of the oracle SQL trace facility. It’s a very powerful method to analyze performance problems, especially if you don’t know the application very well. If you don’t know oracle SQL tracing yet, you will find some info at orafaq and some more details at orable-base. Obviously, Oracle has also some documenation about it.

In short, the Oracle SQL tracing facility causes the database server to write all executed SQL statements to a file for later analysis. One of the main issues with SQL tracing is the huge size of the trace files; every statement execution is logged, potentially with additional data about the execution plan, wait events, and bind values.

This article introduces the new possibilities of Oracle 10g to enable tracing on a very fine level. With a properly instrumented application, tracing can be enabled for specific users or program modules only. With such a fine control over the tracing, the generated data volume can be kept small and SQL tracing becomes a much more useful tool.

The first part of the article explains how a Java application can be instrumented to allow fine-grained tracing while the second part shows the administrative interface to enable tracing for specific modules.

Instrumenting the Application

First things first. Before the database can selectively trace for specific users or modules, the application must tell the database to which user or module an actual SQL statement belongs.

Luckily we don’t have to explicitly specify this for each individual statement. It’s more like a session property that can be set at any time. Once set, it affects all SQLs until a different value is set.

The three values that affect tracing are CLIENT_IDENTIFIER, MODULE and ACTION. Those must be properly set in order to allow selective tracing. With properly I mean two aspects:

Allow meaningful filtering
CLIENT_IDENTIFIER can be used freely. MODULE and ACTION depend on each other; it’s not possible to trace a specific action in every module. In other words, a MODULE is a container for ACTIONS.
Don’t introduce a performance impact

The Oracle database provides a way to set the CLIENT_IDENTIFIER, MODULE and ACTION without doing an additional server round-trip. The information is sent piggyback with the next round-trip. You must take care to use the proper implementation to not introduce an additional server round-trip that would introduce a major performance impact.

UPDATE: The issue about server round tips is also the topic of later article: Latency: Security vs. Performance.

The Required JDBC API

Since all of the described functionality is an oracle specific extension, the JDBC standard doesn’t provide an API to set the required attributes. To access that functionality you must cast the standard JDBC Connection to an OracleConnection object first. That’s all, the OracleConnection class provides the two methods we need:

The naming might confuse in the first place, but when you consider that the actual identifier for a real human being is intended to be put into the CLIENT_IDENTIFER field, it might become clear that this is really for end-to-end measurements.

The actual data is passed as an String array that contains an entry for the properties we are interested in —along with others. You can get all of them and provide new ones. Remember that neither getEndToEndMetrics() nor setEndToEndMetrics() causes a server round trip. In case you change any property, the change is sent piggyback with the next server round-trip. I have used the following class for my tests:

class OracleEndToEnd {
 String oldData[];
 OracleConnection ocon;

 public
   OracleEndToEnd(Connection con, String module,
                  String client , String action)
   throws SQLException
 {
   if (con instanceof OracleConnection) {
     ocon = (OracleConnection)con;
     oldData = ocon.getEndToEndMetrics();
     if (oldData == null) {
       oldData =
          new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
     }

     String[] metrics =
        new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];

     int minidx = (metrics.length < oldData.length)
                                  ?
                   metrics.length : oldData.length;
     System.arraycopy(oldData, 0, metrics, 0, minidx);

     if (action != null) {
       metrics[OracleConnection.END_TO_END_ACTION_INDEX] = action;
     }
     if (client != null) {
       metrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = client;
     }
     if (module != null) {
       metrics[OracleConnection.END_TO_END_MODULE_INDEX] = module;
     }
     ocon.setEndToEndMetrics(metrics, (short) 0);
   }
 }

 public void
 endSection()
 throws SQLException
 {
   if (ocon != null) {
     ocon.setEndToEndMetrics(oldData, (short) 0);
   }
 }
}

This class is intended to be used as wrapper around a specific section of your code (not to say module or action) like this:

OracleEndToEnd oee = new OracleEndToEnd(con, "MOD1", null, "ACT1");
try {
   selectIt(con);
} finally {
   oee.endSection();
}

When done carefully, a hierarchical structure of MODULES and ACTIONS can be build. This code was of course only my proof-of-concept code.

To instrument your existing application, you should look out for central places where you can add the code as shown above. In example, many applications use a database connection pool explicitly. By instrumenting the pool and passing additional parameters—like CLIENT_IDENTIFER and MODULE—to the get()/borrow()/whatever() method, much of the overhead can be centralized.

The best way to instrument your application depends to the frameworks in use. That’s the time to mention that I am consultant, let me know if I can help you

Performance Comparison

I have written an example Java implementation—on basis of the class presented above—to check the performance impact of a properly instrumented application. To make the long story short: in a networked environment there isn’t any considerable impact when properly instrumenting a Java application. In case the application and the database are hosted on the same node, if no network involved, the impact might become noticeable (8% in my experiment), but this was still an extreme case. Generally I believe that a proper instrumentation does not impact performance in a noticeable way.

For my performance experiment, I used a program that issued the same SQL statement many thousand times. To keep the execution time of the statement low, I have used a very trivial statement:

select 1 from dual

I have executed the program with and without instrumentation for the following scenarios:

Without any tracing enabled
With full tracing enabled (trace everything)
Trace only every second SQL execution (only with the instrumented code)
Trace only 1% of the executed statements (only with the instrumented code).

The results are in the chart in a networked environment—application and database hosted on different nodes:

There is NO measurable impact when tracing is disabled. This is the most important aspect. You can just add the needed CLIENT_IDENTIFIER, MODULE and ACTION and it will not harm performance—when done properly. When tracing is enabled, there actually IS a performance drawback. I am not sure where this comes from; I could imagine that the additional data written into the trace file—module name and so on—cause the regression . However, that impact is still minor, 5% in my extreme test scenario. For test case 3 and 4 there is no direct comparison because partial tracing requires the instrumented code. The checked bars are the results of the code without instrumentation but full tracing enabled—the only way to gather similar SQL trace files. You see that the performance impact relates to the traced volume. When tracing only a very small fraction of the overall code the impact is hardly measurable.

All of this requires a proper instrumentation of the application not to cause additional round trips.

The Administrative Interface

With Oracle 10g, yet another way to enable tracing is provided in form of the DBMS_MONITOR package. This package adds some great tools to perform SQL Tracing in a real world scenario.

Instead of

ALTER SESSION SET SQL_TRACE=TRUE;

ALTER SESSION SET EVENT='10046 trace name context forever, level 4'

we can now use

exec DBMS_MONITOR.SESSION_TRACE_ENABLE(binds=>true);

Which is actually the same in pink—except the less cryptic syntax—no benefit. Very often we want to enable tracing from outside the current session, which is also possible with the same procedure:

exec DBMS_MONITOR.SESSION_TRACE_ENABLE(sid, serial#, binds=>true);

Again, a nicer way to do what was possible before; with DBMS_SYSTEM.SET_EV. But now, you have only one package to remember: DMBS_MONITOR.

Sometimes we want to trace as session that will be opened somewhat later. For example, a batch job that runs at night. For this case the traditional solution was to create a SYSTEM LOGON trigger. The body of the trigger typically checks some conditions to decide to trace or not to trace. Very often, the decision is based on the user-name that is fetched via SYS_CONTEXT/USERENV or information from v$session. Many times, applied guesswork is added to the trigger. As a last resort, if it’s not even possible to trace all session that are opened during a specific time frame, it’s also possible to trace much more than needed and filter afterwards.

In a real world scenario, tracing everything is often not feasible because of the huge data volume created. Many people also argue that there is a considerable performance hit. Although I admit, and demonstrate below, that there IS a performance hit, it is usually negligible if neither waits nor binds are traced. The reason that tracing can’t be enabled globally on a production system, is the disk space requirement.

The DBMS_MONITOR Killer Features

The real power of DBMS_MONTOR isn’t that it provides a more convenient interface to enable tracing. The real power comes from

the ability to persistently configure tracing
the ability to perform fine grained, selective tracing

With DBMS_MONITOR, the need for LOGON triggers is gone. E.g., to enable tracing for all active and all future session for a specific service—like SERVICE_NAME in the connect descriptor—use the following:

exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('SERVICE_NAME');

This will enable tracing for all current and future sessions with the specified SERVICE_NAME.

The second killer feature is the ability to perform selective tracing based on

client identifier
module
action

These are session properties can be set the the application. When done properly, the application can change those properties at a very high frequency—for every statement—without introducing a considerable performance hit.

For example, if the application sets the CLIENT_IDENTIFIER to reflect the user ID of a websites user, tracing can be enabled for that particular user only. Similarly selective tracing on MODULE and ACTION level is possible. Consider the following activities happening in a session:

Module	Action	statement
MOD1	ACT1	select * from …
MOD1	ACT2	insert into …
MOD2	ACT1	update…
MOD2	ACT3	delete …

With DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE we can enable tracing for MOD1 only if you like:

exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('SERVICE_NAME', 'MOD1');

The effect is as expected: only the statements attributed to MOD1 will be traced. Consequently you can drill down to trace only ACT1 within MOD1:

exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('SERVICE_NAME', 'MOD1',
                                  'ACT1', waits=>true, binds=>true);

This will trace only the insert statement, with wait and bind data. Similar possibilities exist for the CLIENT_IDENTIFIER:

exec DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('CLIENT_IDENTIFIER',
                                         waits=>false, binds=>true);

Remember that all of this is persistent, even across database restarts. So we need to disable it again. Of course we will forget what was enabled, so there is a table to query for that:

SQL> SELECT trace_type, primary_id,
            qualifier_id1 ID1, qualifier_id2 ID2,
            waits, binds
       FROM dba_enabled_traces;

TRACE_TYPE             PRIMARY_ID        ID1    ID2   WAITS BINDS
---------------------- ----------------- ------ ----- ----- -----
SERVICE_MODULE_ACTION  SERVICE_NAME      MOD1   ACT1  TRUE  TRUE
SERVICE                SERVICE_NAME                   TRUE  FALSE
SERVICE_MODULE         SERVICE_NAME      MOD1         TRUE  FALSE
CLIENT_ID              CLIENT_IDENTIFIER              FALSE TRUE

With these methods we can enable tracing on a very fine granularity in a very convenient way. Since the tracing can be focused, space and performance issues are less problematic.

Résumé

Once an application is instrumented, fine-grained tracing can be applied very straight. Because of the reduced overhead and disk space requirements, SQL Tracing might become a more frequently used tool to troubleshoot poor performance.

You can choose the effort you put into instrumentation by yourself. For example, you might choose to set the MODULE only for one particular module that you need to trace right now, and don’t adopt other modules. In a step-by-step manner you can extend the instrumentation as needed.

During my experiments for this article I have found some traps you should be aware of:

The ACTION seems to consider only 8 characters
During my tests I noticed that the significant length of the ACTION seems to be only 8 characters (although documented as 32 bytes). However, any attempt to enable tracing for an ACTION longer than 8 bytes failed on my 10g test machine.
A wrong implementation might introduce a major performance penalty
Double check your implementation for that.
SERVICE_NAME might be set to an unexpected value
Depending on the way the application connects, SERVICE_NAME might not be present so that it defaults to SYS$USER. If it doesn’t work on the first try, verify that the sessions you want to trace has the expected SERVICE_NAME (select serivce_name from v$session....). Use the correct connect URL with the thin driver.
When using DBMS_MONITOR, wait event tracing is TRUE per default

It’s not fatal to open your mind to new approaches.

Pipelined Functions: Better Than DBMS_OUTPUT

Markus Winand — Wed, 11 Nov 2009 07:36:06 PST

Every now and then I need some PL/SQL that prints something to the terminal. The traditional solution for this is DBMS_OUTPUT.PUT_LINE. If you used it before, you probably know that there are some obstacles:

Don’t forget to set serveroutput on
Don’t forget to set an appropriate buffer size (but there is still a absolute maximum)
Don’t wonder about missing blanks
No way to flush the output

In case you don’t know yet, the first three can be taken care of by issuing the following in SQL*Plus:

set serveroutput on size 1000000 format wrapped;

However, the absolute buffer size limit remains. The years have passed, and the limitations were accepted. Until I noticed that there is an alternative.

With release 9iR2, Oracle delivered a package to format the PLAN_TABLE’s content: DBMS_XPLAN. A very nifty tool which finally rendered the SQLs to format the explain plan useless. But explain plan is not today’s topic; lets just focus in how the formatted plan reaches our screen:

select * from table(DBMS_XPLAN.DISPLAY);

The easiest way to use the package is shown above. It’s obvious that the data is actually fetched by regular select statement. You will also notice this by SQL*Plus artifacts like the feedback line (8 rows selected):

SQL> explain plan for select * from dual;

Explained.

SQL>  select * from table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
Plan hash value: 272002086

---------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      |    1 |     2 |     2   (0)| 00:01 |
|  1 |  TABLE ACCESS FULL| DUAL |    1 |     2 |     2   (0)| 00:01 |
---------------------------------------------------------------------

8 rows selected.

SQL>

The table you see isn’t actually a table (as the database knows it), it’s just formatted output. When using DBMS_XPLAN, I always noted to myself to investigate this. Some years later, the time has come.

Pipelined Table Functions

Oracle’s (pipelined) table functions are PL/SQL functions that return data as (more or less) regular tables that can be accessed with regular SQL. The pipelined feature makes it possible to fetch the first records while the function is still running.

It seems that the primary target of this feature are custom analytic functions and data re-arranging like pivot. However, I use it to get rid of DBMS_OUTPUT.

Hello World

The following is the most simplistic example how to use a pipelined function to return arbitrary output:

create or replace type piped_output_table as table of varchar2(4000);
/

create or replace
 function hello_world return piped_output_table pipelined IS
 text varchar2(4000);
begin
 text := 'Hello world';
 pipe row(text);
 RETURN;
END;
/

The hello_world function must be wrapped in a table() expression to access it like a regular table in SQL:

SQL>  select * from table(hello_world);
COLUMN_VALUE
---------------------------------------------------------------------
Hello world

1 row selected.

SQL>

We have already mastered everything important to replace DBMS_OUTPUT:

Define a table type to be used as return type of the function
Add the pipelined keyword
The PIPE ROW() statement to actually produce a row.
The RETURN statement without an argument.

However, there are two more issues to consider:

You might want to have a different column name (instead of COLUMN_VALUE)
You might need to produce output from nested functions

Custom Column Names

The first issue is rather simple; to name the returned column(s) you must create a object type and base the table type upon this object type:

create or replace type piped_output is object (output varchar2(4000));
/

create or replace type piped_output_table is table of piped_output;
/

create or replace function
   hello_world return piped_output_table pipelined IS
   rec piped_output := piped_output(NULL);
begin
   rec.output := 'Hello world';
   pipe row(rec);
   RETURN;
END;
/

The column header can have an arbitrary column name. Since it is a column name, all respective limitations apply (e.g., no spaces).

Nested Functions

The final requirement to replace DBMS_OUTPUT is produce output from nested function calls. With DBMS_OUTPUT this is rather straight:

CREATE OR REPLACE PACKAGE dbms_output_nested IS
   PROCEDURE hello_world;
END;
/

CREATE OR REPLACE PACKAGE body dbms_output_nested IS
   PROCEDURE nested_procedure IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE('  hello world from nested function') ;
   END nested_procedure;

   PROCEDURE hello_world IS
   BEGIN
      DBMS_OUTPUT.ENABLE();
      DBMS_OUTPUT.PUT_LINE('hello world') ;
      nested_procedure;
   END hello_world;
END dbms_output_nested;
/

Unfortunately, I am not aware of an easy way to accomplish this with pipelined table functions. It seems that there is no way to directly pipe one functions output into the output pipe of another function. So far, the only solution I found is to explicitly copy the records over:

CREATE OR REPLACE PACKAGE piped_nested IS
   TYPE piped_output IS RECORD (output VARCHAR2(4000));
   TYPE piped_output_table IS TABLE OF piped_output;

   FUNCTION hello_world
     RETURN piped_nested.piped_output_table PIPELINED;
   FUNCTION nested_procedure
     RETURN piped_nested.piped_output_table PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BODY piped_nested IS
   FUNCTION nested_procedure
     RETURN piped_nested.piped_output_table PIPELINED IS
      rec piped_nested.piped_output;
   BEGIN
      rec.output := '   hello world from nested function';
      PIPE ROW(rec);
      RETURN;
   END;

   FUNCTION hello_world
     RETURN piped_nested.piped_output_table PIPELINED IS
      rec piped_nested.piped_output;
   BEGIN
      rec.output := 'Hello World';
      PIPE ROW(rec);

      FOR r IN (select * from table(nested_procedure)) LOOP
         rec.output := r.output;
         PIPE ROW(rec);
      END LOOP;

      RETURN;
   END;
END piped_nested;
/

You see that there are two major drawbacks:

The nested_procedure is public
The copy bloats the hello_world function

As mentioned above, I don’t have a good solution for this (let me know if you find something).

Mixing DBMS_OUTPUT with Pipelined Functions

If you have legacy PL/SQL code that uses DBMS_OUTPUT and would like to migrate to pipelined functions, you might consider the following wrapper:

CREATE OR REPLACE PACKAGE dbms_output_table IS
   TYPE piped_output IS RECORD (dbms_output VARCHAR2(4000));
   TYPE piped_output_table IS TABLE OF piped_output;
   FUNCTION display RETURN piped_output_table PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BODY dbms_output_table IS
   FUNCTION display RETURN piped_output_table PIPELINED IS
      rec piped_output;
      p_status NUMBER;
   BEGIN
      DBMS_OUTPUT.GET_LINE(rec.dbms_output, p_status);
      WHILE p_status = 0 LOOP
         PIPE ROW(rec);
         DBMS_OUTPUT.GET_LINE(rec.dbms_output, p_status);
      END LOOP;
      RETURN;
   END;
END;
/

With this function you can retrieve the current content of the DBMS_OUTPUT buffer via a pipelined table function. Please note that the producer of the DBMS_OUTPUT must enable the DBMS_OUTPUT facility and SQL*Plus must be set serverout off (otherwise SQL*Plus would retrieve it):

SQL>  exec dbms_output_nested.hello_world;
hello world
hello world from nested function

PL/SQL procedure successfully completed.

SQL> set serverout off
SQL>  exec dbms_output_nested.hello_world;

PL/SQL procedure successfully completed.

SQL> select * from table(dbms_output_table.display);

DBMS_OUTPUT
---------------------------------------------------------------------
hello world
 hello world from nested function

SQL>

The Best of Both Worlds?

This—and similar methods—can be used so that nested functions still use the DBMS_OUTPUT interface, while the front end function returns its output as pipelined table. A mixed approach allows you to hide the nested function—no need to have them in the public package declaration—but reapplies some of the DBMS_OUTPUT limitations; the maximum buffer size (although at smaller level), no flush.

CREATE OR REPLACE PACKAGE mixed_package IS
   TYPE piped_output IS RECORD (output VARCHAR2(4000));
   TYPE piped_output_table IS TABLE OF piped_output;
   FUNCTION hello_world RETURN piped_output_table PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BODY mixed_package IS
   PROCEDURE prepare_dbms_output IS
   BEGIN
      DBMS_OUTPUT.DISABLE; -- removes any stale data
      DBMS_OUTPUT.ENABLE(1000000);
   END;

   PROCEDURE nested_function IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE('   Hello world from nested function');
   END;

   FUNCTION hello_world RETURN piped_output_table PIPELINED IS
      rec piped_output;
      p_status NUMBER;
   BEGIN
      prepare_dbms_output;

      rec.output := 'Hello World';
      PIPE ROW(rec);

      nested_function;

      DBMS_OUTPUT.GET_LINE(rec.output, p_status);
      WHILE p_status = 0 LOOP
         PIPE ROW(rec);
         DBMS_OUTPUT.GET_LINE(rec.output, p_status);
      END LOOP;

      RETURN;
   END;
END;
/

You can also use other methods to return row data from nested functions and pass it on to the PIPE ROW statement in the topmost stack frame. No matter in which way pass rows from nested functions, you must be aware that they are passed as a bulk when the nested function finishes. That means, the are kind of flushed once for each nested function call. If all you data is returned from a nested function, you might effectively disable the pipelined feature.

Final Comparison and Critique

The user interface of pipelined table functions is much better than the well established DBMS_OUTPUT for several reasons:

No separate API needed to access the returned data
Most programming languages don’t have a convenient way to access the serveroutput. Very often the only way to fetch the buffer is to manually call DBMS_OUTPUT.GET_LINES. On a quick view, only the perl DBI/DBD::Oracle framework has addressed the problem (a little bit):
But wait, your scripts are anyways intended for SQL*Plus usage only? Besides the fact that you never know in which context your scripts are used, there are also other user interfaces than SQL*Plus to access an Oracle database. There are many IDE that support database access. Most of those have support for DBMS_OUTPUT in the meanwhile but it’s different in each implementation. You can avoid a great deal of questions where to find the output of your script in tool XYZ by using table functions.
No size limitation
But wait, your script isn’t returning much data? Yes, you usually know how much data your script will return. With a call to DBMS_OUTPUT.ENABLE you can make the buffer big enough. But if you are wrong? You are lucky if the user knows how to enlarge the buffer. You are even more happy if the hard limit of the buffer size is not reached (1,000,000 bytes). I know of a case where the user was required to change a script to use UTL_FILE because the output was a few megabytes (luckily they had access to the database back-end, where the file was written).
Pipelined
Self-explaining? No major usability benefit, I guess. However, it is a benefit to see that something is happening while the function is running. It is definitely a benefit not to buffer everything in memory until the end—that is probably the reason why there is a hard limit on the buffer size.

I guess the article gave you insight to the major drawback of the pipelined table function method as well:

Producing output from nested function calls is a pain in the a**
It is possible, but inconvenient—if not delusive because it exploits internal functions in the public interface. The use of collections in the nested function makes it possible to maintain a clean public interface. However, copying the data over and over again is inconvenient, if not problematic, because it might reintroduce the size issue (when using DBMS_OUTPUT the already mentioned limit of 1,000,000 bytes per nested function call apply).
DML in the table function cannot participate in the caller’s transaction
UPDATE 2010-03-11: Gary commented that
```
select * from table(hello_world)
```
works only if the function does not perform any DML. Gary is of course right; any attempt to do so results in ORA-14551. The only possibility to perform DML in the function is to use an autonomous transaction.

My personal Best Current Practice is to use other methods (collections or even DBMS_OUTPUT) in the private functions, but use a pipelined function interface for the user. This is sometimes very inconvenient to code but the improved user experience is worth that effort.

Of course I would have some wishes how the situation could be improved. The most powerful way would be to allow something like this:

PIPE ROW(nested_pipelined_function);

Where nested_pipelined_function doesn’t need to be part of the public package interface. Each PIPE ROW() in the nested function should directly output as if was written in the calling function. On the other hand, it would also be great to have something to pipe a complete collection at once:

PIPE ROWS(collection);

I am well aware that the usage of pipelined table functions as demonstrated in this article might not reflect the intended usage of that feature, thus its fitness for that purpose is limited.

It’s not fatal to open your mind to new approaches.

More on Single-Action UIs

John Russell — Wed, 10 Mar 2010 18:49:53 PST

In my post Deconstructing the iPod Shuffle UI, I talked a bit about the notion of a limited UI where you really only do one thing -- in that case, click the button on the headphones.Every now and then, I rediscover the infrared remote that goes with my iMac, and realize that for many mundane tasks, the remote does everything I need. (Mira lets me assign different actions for the remote button for