Oakies Blog Aggregator

The “Do What You Love” Mirage

I am inspired by having read an article called “Do what you love mirage” by Denis Basaric. It begins...

“Do what you love” is advice I hear exclusively from financially secure people. And it rings hollow to me. When you need money to survive, you do any work that is available, love does not play into that choice. Desperation does.

Please read it before you go on.

Welcome back.

This article puts a very important cycle within my life into words. I believe, as Denis says, that a lot of times, we get the cause-effect relationship mixed up when we think about loving what we do.

I love what I do. Well, a lot of it. But Denis is right: I didn’t choose what I do out of love. I chose what I love out of doing. Some examples:

  • I love mathematics. But I most assuredly did not always love it. I learned to love it through working hard at it.
  • It’s the same thing with writing. I love it, but I didn’t always. At first, writing was unrewarding drudgery, which is how most people I meet seem to feel about it.
  • I love public speaking, but I sure didn’t love it when my speech class made me sick to my stomach three mornings a week for a whole semester my freshman year.
  • I love being an Oracle performance specialist, but I sure didn’t love being airlifted into crisis after crisis throughout the early 1990s.

I could go on. The point is, my life would be unrecognizably different if not for several really painful situations that I decided to endure with the resolve to get really good at what I hated. Until I loved it.

In retrospect, I seem to have been very lucky in many important situations. Of course, I have. But you make your own luck. Although I believe deeply in the idea of, “The harder I work, the luckier I get,” that is not what I’m talking about here. I’m talking about the power that you have to define for yourself whether something that happened was lucky for you or not. Your situations do not define your life. You create your life based on how you regard your situations.

I could have rebelled against Jimmy Harkey and hated math for the rest of my life. Lots of kids did. I could have rebelled against Lewis Parkhill and never become a writer. I could have refused Craig Newberger’s advice to take his second speech course and never become comfortable in front of an audience. I could have left Oracle in 1991 and found a job where they had more mature products....

One of the most important questions that I ever asked my wife before our engagement was this:

If you were forced to wash cars for 12 hours a day, just to make a living, could you enjoy it?

This is a “soulmate” kind of question for me. My wife’s attitude about it is, for our children and me, possibly the most valuable gift in our lives.

Loving what you do can be difficult. I think Denis hits the nail on the head by suggesting that,

By doing good work, you just might find out that what you are doing, is what you are supposed to do. And if you don’t, quality work will get you to where you want to be.

I hope you will find love in what you do today. Do it well, and it’ll definitely improve your odds.

Hinting Sub-Queries on Oracle

This posting is a purely Oracle RDBMS discussion about how to correctly apply hints to sub-queries. However, it is particularly relevant to PeopleSoft, which it makes extensive use of correlated sub-queries. The point of this story is not the particular hints that I applied, but where I placed the hints, and how I scoped them to the sub-queries.

The following SQL extract is from the delivered Global Payroll GPGB_EDI process (although I have already made some minor changes, and PS_GP_RSLT_PIN is partitioned). Notice that each sub-query joins two tables together. PS_GP_RSLT_PIN is second largest of Global Payroll result tables. PS_GP_PIN is a look-up table, and the criterion on PIN_CODE will only return a single row.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
UPDATE Table(GPGB_EDIE_TMP) X
SET X.GPGB_WK53_IND = (
SELECT %TrimSubstr(%Sql(FUNCLIB_HR_CHAR,A.CALC_RSLT_VAL),1,2)
FROM PS_GP_RSLT_PIN A
,PS_GP_PIN B

WHERE A.EMPLID = %Table(GPGB_EDIE_TMP).EMPLID
...
AND A.PIN_NUM = B.PIN_NUM 
AND B.PIN_CODE = 'TAX VR PERIOD GBR'
AND A.SLICE_BGN_DT = (
SELECT MAX(D.SLICE_BGN_DT)
FROM PS_GP_RSLT_PIN D
WHERE D.EMPLID = %Table(GPGB_EDIE_TMP).EMPLID
...
AND D.INSTANCE = A.INSTANCE
AND D.PIN_NUM = B.PIN_NUM)
...
)
WHERE EXISTS (
...
)
AND PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)

This is part of the initial SQL execution plan. The problem is that the sub-query starts by scanning through the PS_GP_RSLT_PIN table, 4 times because there are three correlated sub-queries, and only at the very end does it look up the PIN_CODE by the PIN_NUM.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart|
---------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 65 | 113M (93)| 39:44:51 | |
| 1 | UPDATE | PS_GPGB_EDIE_TMP4 | | | | | |
|* 2 | FILTER | | | | | | |
|* 3 | TABLE ACCESS FULL | PS_GPGB_EDIE_TMP4 | 673K| 41M| 9967 (6)| 00:00:13 |
| 4 | NESTED LOOPS | | 1 | 108 | 4 (0)| 00:00:01 | |
| 5 | PARTITION RANGE SINGLE | | 1 | 83 | 3 (0)| 00:00:01 | KEY |
| 6 | PARTITION LIST SINGLE | | 1 | 83 | 3 (0)| 00:00:01 | KEY |
|* 7 | INDEX RANGE SCAN | PS_GP_RSLT_PIN | 1 | 83 | 3 (0)| 00:00:01 | KEY |
| 8 | SORT AGGREGATE | | 1 | 72 | | | |
| 9 | PARTITION RANGE SINGLE | | 1 | 72 | 3 (0)| 00:00:01 | KEY |
| 10 | PARTITION LIST SINGLE | | 1 | 72 | 3 (0)| 00:00:01 | KEY |
|* 11 | INDEX RANGE SCAN | PS_GP_RSLT_PIN | 1 | 72 | 3 (0)| 00:00:01 | KEY |
| 12 | SORT AGGREGATE | | 1 | 83 | | | |
| 13 | PARTITION RANGE SINGLE | | 1 | 83 | 3 (0)| 00:00:01 | KEY |
| 14 | PARTITION LIST SINGLE | | 1 | 83 | 3 (0)| 00:00:01 | KEY |
| 15 | FIRST ROW | | 1 | 83 | 3 (0)| 00:00:01 | |
|* 16 | INDEX RANGE SCAN (MIN/MAX) | PS_GP_RSLT_PIN | 1 | 83 | 3 (0)| 00:00:01 |
| 17 | SORT AGGREGATE | | 1 | 83 | | | |
| 18 | PARTITION RANGE SINGLE | | 1 | 83 | 158 (99)| 00:00:01 | KEY |
| 19 | PARTITION LIST SINGLE | | 1 | 83 | 158 (99)| 00:00:01 | KEY |
| 20 | FIRST ROW | | 1 | 83 | 158 (99)| 00:00:01 | |
|* 21 | INDEX RANGE SCAN (MIN/MAX)| PS_GP_RSLT_PIN | 1 | 83 | 158 (99)| 00:00:01 | KEY |
|* 22 | INDEX RANGE SCAN | PSAGP_PIN | 1 | 25 | 1 (0)| 00:00:01 | |
...
---------------------------------------------------------------------------------------------------------------------------

It would be much better if we started with the PS_GP_PIN table, looked up the PIN_NUM with the PIN_CODE (there is a suitable index on this column) and used the PIN_NUM value as a part of the lookup on PS_GP_RSLT_PIN (again PIN_CODE is in the unique index on that table).

It is tempting to add LEADING hints to the sub-queries, but such a hint does not work because the hint is not scoped to the sub-query and is considered to be invalid because the entire query cannot start at this point.

The only supported place in the query to add a LEADING hint would be the first query, in this case after the UPDATE keyword.

In this case, I have named the query blocks in the sub-queries with the QB_NAME hint. It is valid to put this hint into the sub-query. Then I added LEADING hints for each sub-query after the UPDATE keyword, but I specified their scope using the name of the sub-query specified in the QB_NAME hint. Each sub-query must now start with the PS_GP_PIN table.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
UPDATE /*+LEADING(@SUB1 B@SUB1) LEADING(@SUB2 B@SUB2)*/ %Table(GPGB_EDIE_TMP) X
SET X.GPGB_WK53_IND = (
SELECT /*+QB_NAME(SUB1)*/ %TrimSubstr(%Sql(FUNCLIB_HR_CHAR,A.CALC_RSLT_VAL),1,2)
FROM PS_GP_RSLT_PIN A
,PS_GP_PIN B
WHERE A.EMPLID = %Table(GPGB_EDIE_TMP).EMPLID
...
AND A.PIN_NUM = B.PIN_NUM
AND B.PIN_CODE = 'TAX VR PERIOD GBR'
AND A.SLICE_BGN_DT = (
SELECT MAX(D.SLICE_BGN_DT)
FROM PS_GP_RSLT_PIN D
WHERE D.EMPLID = %Table(GPGB_EDIE_TMP).EMPLID
...
AND D.INSTANCE = A.INSTANCE
AND D.PIN_NUM = B.PIN_NUM)
...
)
WHERE EXISTS (
SELECT /*+QB_NAME(SUB2)*/ 'X'
FROM PS_GP_RSLT_PIN A1
,PS_GP_PIN B1
WHERE A1.EMPLID = %Table(GPGB_EDIE_TMP).EMPLID
...
AND A1.PIN_NUM = B1.PIN_NUM
AND B1.PIN_CODE = 'TAX VR PERIOD GBR'
...
)
AND PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)

This is the new execution plan. The sub-query starts with an access of index PSAGP_PIN (which leads on PIN_CODE) on PS_GP_PIN, and then does the lookups on PS_GP_RSLT_PIN. The cost is the same, but the execution time was considerably reduced.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart|
----------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 65 | 113M (93)| 39:44:51 | |
| 1 | UPDATE | PS_GPGB_EDIE_TMP4 | | | | | |
|* 2 | FILTER | | | | | | |
|* 3 | TABLE ACCESS FULL | PS_GPGB_EDIE_TMP4 | 673K| 41M| 9967 (6)| 00:00:13 | |
| 4 | NESTED LOOPS | | 1 | 108 | 4 (0)| 00:00:01 | |
|* 5 | INDEX RANGE SCAN | PSAGP_PIN | 1 | 25 | 2 (0)| 00:00:01 | |
| 6 | PARTITION RANGE SINGLE | | 1 | 83 | 2 (0)| 00:00:01 | KEY |
| 7 | PARTITION LIST SINGLE | | 1 | 83 | 2 (0)| 00:00:01 | KEY |
|* 8 | INDEX RANGE SCAN | PS_GP_RSLT_PIN | 1 | 83 | 2 (0)| 00:00:01 | KEY |
| 9 | SORT AGGREGATE | | 1 | 72 | | | |
| 10 | PARTITION RANGE SINGLE | | 1 | 72 | 3 (0)| 00:00:01 | KEY |
| 11 | PARTITION LIST SINGLE | | 1 | 72 | 3 (0)| 00:00:01 | KEY |
|* 12 | INDEX RANGE SCAN | PS_GP_RSLT_PIN | 1 | 72 | 3 (0)| 00:00:01 | KEY |
| 13 | SORT AGGREGATE | | 1 | 83 | | | |
| 14 | PARTITION RANGE SINGLE | | 1 | 83 | 3 (0)| 00:00:01 | KEY |
| 15 | PARTITION LIST SINGLE | | 1 | 83 | 3 (0)| 00:00:01 | KEY |
| 16 | FIRST ROW | | 1 | 83 | 3 (0)| 00:00:01 | |
|* 17 | INDEX RANGE SCAN (MIN/MAX) | PS_GP_RSLT_PIN | 1 | 83 | 3 (0)| 00:00:01 | |
| 18 | SORT AGGREGATE | | 1 | 83 | | | |
| 19 | PARTITION RANGE SINGLE | | 1 | 83 | 158 (99)| 00:00:01 | KEY |
| 20 | PARTITION LIST SINGLE | | 1 | 83 | 158 (99)| 00:00:01 | KEY |
| 21 | FIRST ROW | | 1 | 83 | 158 (99)| 00:00:01 | |
|* 22 | INDEX RANGE SCAN (MIN/MAX)| PS_GP_RSLT_PIN | 1 | 83 | 158 (99)| 00:00:01 | KEY |
| 23 | TABLE ACCESS BY LOCAL INDEX ROWID | PS_GP_RSLT_PIN | 1 | 86 | 3 (0)| 00:00:01 | |
...
---------------------------------------------------------------------------------------------------------------------------

Hinting Sub-Queries on Oracle

This posting is a purely Oracle RDBMS discussion about how to correctly apply hints to sub-queries. However, it is particularly relevant to PeopleSoft, which it makes extensive use of correlated sub-queries. The point of this story is not the particular hints that I applied, but where I placed the hints, and how I scoped them to the sub-queries.

The following SQL extract is from the delivered Global Payroll GPGB_EDI process (although I have already made some minor changes, and PS_GP_RSLT_PIN is partitioned). Notice that each sub-query joins two tables together. PS_GP_RSLT_PIN is second largest of Global Payroll result tables. PS_GP_PIN is a look-up table, and the criterion on PIN_CODE will only return a single row.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
UPDATE Table(GPGB_EDIE_TMP) X
SET X.GPGB_WK53_IND = (
SELECT %TrimSubstr(%Sql(FUNCLIB_HR_CHAR,A.CALC_RSLT_VAL),1,2)
FROM PS_GP_RSLT_PIN A
,PS_GP_PIN B

WHERE A.EMPLID = %Table(GPGB_EDIE_TMP).EMPLID
...
AND A.PIN_NUM = B.PIN_NUM 
AND B.PIN_CODE = 'TAX VR PERIOD GBR'
AND A.SLICE_BGN_DT = (
SELECT MAX(D.SLICE_BGN_DT)
FROM PS_GP_RSLT_PIN D
WHERE D.EMPLID = %Table(GPGB_EDIE_TMP).EMPLID
...
AND D.INSTANCE = A.INSTANCE
AND D.PIN_NUM = B.PIN_NUM)
...
)
WHERE EXISTS (
...
)
AND PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)

This is part of the initial SQL execution plan. The problem is that the sub-query starts by scanning through the PS_GP_RSLT_PIN table, 4 times because there are three correlated sub-queries, and only at the very end does it look up the PIN_CODE by the PIN_NUM.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart|
---------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 65 | 113M (93)| 39:44:51 | |
| 1 | UPDATE | PS_GPGB_EDIE_TMP4 | | | | | |
|* 2 | FILTER | | | | | | |
|* 3 | TABLE ACCESS FULL | PS_GPGB_EDIE_TMP4 | 673K| 41M| 9967 (6)| 00:00:13 |
| 4 | NESTED LOOPS | | 1 | 108 | 4 (0)| 00:00:01 | |
| 5 | PARTITION RANGE SINGLE | | 1 | 83 | 3 (0)| 00:00:01 | KEY |
| 6 | PARTITION LIST SINGLE | | 1 | 83 | 3 (0)| 00:00:01 | KEY |
|* 7 | INDEX RANGE SCAN | PS_GP_RSLT_PIN | 1 | 83 | 3 (0)| 00:00:01 | KEY |
| 8 | SORT AGGREGATE | | 1 | 72 | | | |
| 9 | PARTITION RANGE SINGLE | | 1 | 72 | 3 (0)| 00:00:01 | KEY |
| 10 | PARTITION LIST SINGLE | | 1 | 72 | 3 (0)| 00:00:01 | KEY |
|* 11 | INDEX RANGE SCAN | PS_GP_RSLT_PIN | 1 | 72 | 3 (0)| 00:00:01 | KEY |
| 12 | SORT AGGREGATE | | 1 | 83 | | | |
| 13 | PARTITION RANGE SINGLE | | 1 | 83 | 3 (0)| 00:00:01 | KEY |
| 14 | PARTITION LIST SINGLE | | 1 | 83 | 3 (0)| 00:00:01 | KEY |
| 15 | FIRST ROW | | 1 | 83 | 3 (0)| 00:00:01 | |
|* 16 | INDEX RANGE SCAN (MIN/MAX) | PS_GP_RSLT_PIN | 1 | 83 | 3 (0)| 00:00:01 |
| 17 | SORT AGGREGATE | | 1 | 83 | | | |
| 18 | PARTITION RANGE SINGLE | | 1 | 83 | 158 (99)| 00:00:01 | KEY |
| 19 | PARTITION LIST SINGLE | | 1 | 83 | 158 (99)| 00:00:01 | KEY |
| 20 | FIRST ROW | | 1 | 83 | 158 (99)| 00:00:01 | |
|* 21 | INDEX RANGE SCAN (MIN/MAX)| PS_GP_RSLT_PIN | 1 | 83 | 158 (99)| 00:00:01 | KEY |
|* 22 | INDEX RANGE SCAN | PSAGP_PIN | 1 | 25 | 1 (0)| 00:00:01 | |
...
---------------------------------------------------------------------------------------------------------------------------

It would be much better if we started with the PS_GP_PIN table, looked up the PIN_NUM with the PIN_CODE (there is a suitable index on this column) and used the PIN_NUM value as a part of the lookup on PS_GP_RSLT_PIN (again PIN_CODE is in the unique index on that table).

It is tempting to add LEADING hints to the sub-queries, but such a hint does not work because the hint is not scoped to the sub-query and is considered to be invalid because the entire query cannot start at this point.

The only supported place in the query to add a LEADING hint would be the first query, in this case after the UPDATE keyword.

In this case, I have named the query blocks in the sub-queries with the QB_NAME hint. It is valid to put this hint into the sub-query. Then I added LEADING hints for each sub-query after the UPDATE keyword, but I specified their scope using the name of the sub-query specified in the QB_NAME hint. Each sub-query must now start with the PS_GP_PIN table.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
UPDATE /*+LEADING(@SUB1 B@SUB1) LEADING(@SUB2 B@SUB2)*/ %Table(GPGB_EDIE_TMP) X
SET X.GPGB_WK53_IND = (
SELECT /*+QB_NAME(SUB1)*/ %TrimSubstr(%Sql(FUNCLIB_HR_CHAR,A.CALC_RSLT_VAL),1,2)
FROM PS_GP_RSLT_PIN A
,PS_GP_PIN B
WHERE A.EMPLID = %Table(GPGB_EDIE_TMP).EMPLID
...
AND A.PIN_NUM = B.PIN_NUM
AND B.PIN_CODE = 'TAX VR PERIOD GBR'
AND A.SLICE_BGN_DT = (
SELECT MAX(D.SLICE_BGN_DT)
FROM PS_GP_RSLT_PIN D
WHERE D.EMPLID = %Table(GPGB_EDIE_TMP).EMPLID
...
AND D.INSTANCE = A.INSTANCE
AND D.PIN_NUM = B.PIN_NUM)
...
)
WHERE EXISTS (
SELECT /*+QB_NAME(SUB2)*/ 'X'
FROM PS_GP_RSLT_PIN A1
,PS_GP_PIN B1
WHERE A1.EMPLID = %Table(GPGB_EDIE_TMP).EMPLID
...
AND A1.PIN_NUM = B1.PIN_NUM
AND B1.PIN_CODE = 'TAX VR PERIOD GBR'
...
)
AND PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)

This is the new execution plan. The sub-query starts with an access of index PSAGP_PIN (which leads on PIN_CODE) on PS_GP_PIN, and then does the lookups on PS_GP_RSLT_PIN. The cost is the same, but the execution time was considerably reduced.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart|
----------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 65 | 113M (93)| 39:44:51 | |
| 1 | UPDATE | PS_GPGB_EDIE_TMP4 | | | | | |
|* 2 | FILTER | | | | | | |
|* 3 | TABLE ACCESS FULL | PS_GPGB_EDIE_TMP4 | 673K| 41M| 9967 (6)| 00:00:13 | |
| 4 | NESTED LOOPS | | 1 | 108 | 4 (0)| 00:00:01 | |
|* 5 | INDEX RANGE SCAN | PSAGP_PIN | 1 | 25 | 2 (0)| 00:00:01 | |
| 6 | PARTITION RANGE SINGLE | | 1 | 83 | 2 (0)| 00:00:01 | KEY |
| 7 | PARTITION LIST SINGLE | | 1 | 83 | 2 (0)| 00:00:01 | KEY |
|* 8 | INDEX RANGE SCAN | PS_GP_RSLT_PIN | 1 | 83 | 2 (0)| 00:00:01 | KEY |
| 9 | SORT AGGREGATE | | 1 | 72 | | | |
| 10 | PARTITION RANGE SINGLE | | 1 | 72 | 3 (0)| 00:00:01 | KEY |
| 11 | PARTITION LIST SINGLE | | 1 | 72 | 3 (0)| 00:00:01 | KEY |
|* 12 | INDEX RANGE SCAN | PS_GP_RSLT_PIN | 1 | 72 | 3 (0)| 00:00:01 | KEY |
| 13 | SORT AGGREGATE | | 1 | 83 | | | |
| 14 | PARTITION RANGE SINGLE | | 1 | 83 | 3 (0)| 00:00:01 | KEY |
| 15 | PARTITION LIST SINGLE | | 1 | 83 | 3 (0)| 00:00:01 | KEY |
| 16 | FIRST ROW | | 1 | 83 | 3 (0)| 00:00:01 | |
|* 17 | INDEX RANGE SCAN (MIN/MAX) | PS_GP_RSLT_PIN | 1 | 83 | 3 (0)| 00:00:01 | |
| 18 | SORT AGGREGATE | | 1 | 83 | | | |
| 19 | PARTITION RANGE SINGLE | | 1 | 83 | 158 (99)| 00:00:01 | KEY |
| 20 | PARTITION LIST SINGLE | | 1 | 83 | 158 (99)| 00:00:01 | KEY |
| 21 | FIRST ROW | | 1 | 83 | 158 (99)| 00:00:01 | |
|* 22 | INDEX RANGE SCAN (MIN/MAX)| PS_GP_RSLT_PIN | 1 | 83 | 158 (99)| 00:00:01 | KEY |
| 23 | TABLE ACCESS BY LOCAL INDEX ROWID | PS_GP_RSLT_PIN | 1 | 86 | 3 (0)| 00:00:01 | |
...
---------------------------------------------------------------------------------------------------------------------------

RAC performance tuning: Understanding Global cache performance

Global cache performance metrics are not correctly measured. It is not understood clearly either. There are even few blogs and web pages disseminating incorrect information. This blog entry is an attempt to offer few methods and scripts to understand global cache performance.

Always review all instances

It is very important to review the performance metrics from all instances in that RAC cluster, not just one instance that you are connected. If you have access to AWR reports, then it is critical to generate AWR reports (or statspack reports) from all instances. But, the problem is that, DBAs tend to generate AWR reports after logging in to each instance iteratively, enter couple of parameters and then reports are generated. Not exactly a convenient practice.

  REM connect to each instance separately, type in the beginning snap_id and ending snap_id for each node etc..
   sqlplus mydba@proddb1
   @$ORACLE_HOME/rdbms/admin/awrrpt.sql
   exit;
   sqlplus mydba@proddb2
   @$ORACLE_HOME/rdbms/admin/awrrpt.sql
   exit;
   sqlplus mydba@proddb3
   @$ORACLE_HOME/rdbms/admin/awrrpt.sql
   exit;

There are few issues with this approach. It is a cumbersome practice if the instance count is higher. In addition to that, all of AWR reports are, in turn, accessing underlying AWR tables. Physically, rows from all instances are together in the same block and so, by executing these reports connecting to various instances, Global cache traffic is increased. If the database is suffering from Global cache (GC) performance issues then generating reports connecting to various instances is probably not a grand idea.

I have created few basic scripts to generate AWR reports from all instances in a single step. Script awrrpt_all_gen.sql queries AWR tables and gv$instance to access last snap_id and various instance_ids. Then this script generates AWR reports for all instances in that cluster in the current directory, for the last AWR snap. This script is quite handy while debugging RAC performance issues. Now, we have AWR reports from all instances in one directory and we can easily grep these files to understand metrics in various instances.

@awrrpt_all_gen.sql

awrrpt_all_gen.sql v1.00 by Riyaj Shamsudeen @orainternals.com

To generate AWR Report from all RAC instances concurrently.
Creates reports using last two snap_ids.

...Generating awrrpt_all.sql script.... Please wait....
...Completed script generation.

Executing awrrpt_all.sql to generate AWR reports.
...Generates AWR reports with file name format awrrpt___.txt for each instance.
...Please wait for few minutes...

...AWR report created for instance 1. Please wait..
...AWR report created for instance 2. Please wait..
...AWR report created for instance 3. Please wait..
...AWR report created for instance 4. Please wait..

AWR reports created.

If you would like ability to choose the snap_ids, then use the script awrrpt_all_range_gen.sql

Use granular approach to GC measurements

It is important to measure Global cache performance metrics correctly, especially those damned averages. Incorrect measurements of averages can lead to faulty analysis. For example, following query is retrieving average global cache CR receive time from gv$sysstat view. Problem with this SQL statement is that this statement is retrieving average global cache receive time from the restart of the instances. Global cache performance is quite sensitive to workload and querying over a wide time range, as in this query, can lead to faulty analysis.


REM This is really a bad idea!!

select b1.inst_id, b2.value "RECEIVED",
b1.value "RECEIVE TIME",
((b1.value / b2.value) * 10) "AVG RECEIVE TIME (ms)"
from gv$sysstat b1, gv$sysstat b2
where b1.name = 'gc current block receive time' and
b2.name = 'gc current blocks received' and b1.inst_id = b2.inst_id;

As an analogy, above query is equivalent to a patient-Doctor conversation:

Patient: Doctor, I don’t feel good. I feel that I am having fever.
Doctor : Nope. Your average body temperature from the beginning of this year is around 98. So, you are doing excellent.

It is essential to review the global cache performance metrics at a granular level. Use AWR reports or statspack reports for a short period of time. Better yet, if you want to quickly review GC performance metrics, you can use my script gc_traffic_print.sql. This script captures various statistics relevant to Global cache performance in to a PL/SQL table, sleeps for N seconds (default N=60) and re-captures the statistics after the sleep. Then the script prints all the relevant details in easily readable tabular format. Very useful in quickly reviewing the Global cache performance metrics.

SQL> @gc_traffic_print.sql

gc_traffic_print.sql v1.20 by Riyaj Shamsudeen @orainternals.com

...Calculating GC Rx and Tx timing and blocks..
...Default collection period is 60 seconds.... Please wait for at least 60 seconds...
Enter value for sleep: 30
---------|--------------|---------|----------------|----------|---------------|---------------|-------------|
Inst     | CR blocks Rx | CR time |  CUR blocks Rx | CUR time |  CR blocks Tx | CUR blocks Tx |Tot blocks   |
---------|--------------|---------|----------------|----------|---------------|---------------|-------------|
1        |         29288|    12.17|          14914|      4.58|          27059|           17439|        88700|
2        |         21201|     5.75|          12835|      5.19|          26831|           16741|        77608|
3        |         22942|     4.64|          11751|      3.82|          23918|            9205|        67816|
4        |         32075|     4.78|          19260|      4.55|          31046|           19331|       101712|
---------|--------------|---------|----------------|----------|---------------|---------------|-------------|

PL/SQL procedure successfully completed.

Column key for the above script is:
CR block RX : GC CR blocks received
CR time : Average GC CR receive time
CUR blocks RX: GC CUR blocks received
CUR time: Average GC CuR receive time
CR blocks TX: GC CR blocks transmitted
CUR blocks TX: GC CUR blocks transmitted
tot blocks: Sum of transmitted + received for both CR and CUR traffic

Breakup: global cache performance

Global cache processing performance can be broken down to its sub components ( excluding wire latency, but that can be calculated). Breakup of this metrics must be done at a granular level also. We can use yet another script gc_traffic_processing.sql to review the performance metrics for the past N seconds. For example, in the output printed below, Global cache CR processing time is broken in to various sub-components of global cache processing. Remember that these performance metrics are in the SEND side of Global cache performance metrics. Meaning, inefficient global cache processing in one instance affects other instances' global cache receive time, but not the self. For example, in the table above, GC CR receive performance was at 12.1ms for instance 1. That is due to another instance (probably node 4 in this case) suffering from high GC processing time (more specifically high 'CR block flush time' in node 4). It is far more accurate to review global cache performance issues in a granular basis. Caution though, try not to specify at least a minute so that these numbers are consistent.

@gc_traffic_procssing.sql
gc_traffic_processing.sql v1.00 by Riyaj Shamsudeen @orainternals.com

...Prints various timing related information for the past N seconds
...Default collection period is 60 seconds.... Please wait for at least 60 seconds...
Enter value for sleep:

---------|-----------|---------|-----------|----------|------------|------------|------------|----------|
Inst     | CR blk Tx | CR bld  | CR fls tm | CR snd tm| CUR blk TX | CUR pin tm | CUR fls tm |CUR snd tm|
---------|-----------|---------|-----------|----------|------------|------------|------------|----------|
1        |      62732|       .2|       1.25|       .21|       48920|        .96|         .22|        .22|
2        |      46037|      .13|        .76|        .2|       42019|        .68|         .17|         .2|
3        |      72237|      .63|        .79|       .19|       22697|        .44|         .29|        .23|
4        |     113816|      .13|       2.04|       .15|       51020|        .87|         .12|        .13|
--------------------------------------------------------------------------------------------------------

PL/SQL procedure successfully completed.

Here is the column definitions for the script output:
CR blk TX: GC CR blocks served
CR bld : Average GC CR build time/CR block served
CR fls tm: Average GC CR flush time/CR block served
CR snd tm: Average GC CR send time/CR block served
CUR blk TX: GC CUR blocks served
CUR pin tm: Average GC Current pin time /CUR block served
CUR fls tm : Average GC Current flush time/CUR block served
CUR snd tm: Average GC current send time/CUR block served

Review histograms for GC events

Averages can be misleading. Revealing performance issues underneath the average is a very important task to do. For example, we see that 'gc cr block receive time' average is high. But, let's review the histogram for the gc events related to Global cache receive time. My script gc_event_histogram.sql is quite handy to review event histograms. This script provides a difference in performance statistics between two samples and prints them in a readable format. In this case, node 1 is has 8578 waits for the event 'gc cr block 2-way' under 2ms and the sessions in instance 1 had 9851 waits between 2-4ms. Only few waits over 16ms.

@gc_event_histogram.sql

gc_event_histogram.sql v1.0 by Riyaj Shamsudeen @orainternals.com

Default collection period is sleep seconds. Please wait..
Enter value for event: gc cr block 2-way
Enter value for sleep: 60
---------|-----------------------|----------------|----------|
Inst id  | Event                 |wait time milli |wait cnt  |
---------|-----------------------|----------------|----------|
1        |gc cr block 2-way      |               1|        98|
1        |gc cr block 2-way      |               2|      8578|
1        |gc cr block 2-way      |               4|      9851|
1        |gc cr block 2-way      |               8|      2291|
1        |gc cr block 2-way      |              16|       117|
1        |gc cr block 2-way      |              32|         6|
1        |gc cr block 2-way      |              64|         1|
1        |gc cr block 2-way      |             128|         2|
1        |gc cr block 2-way      |             256|         0|
1        |gc cr block 2-way      |             512|         0|
1        |gc cr block 2-way      |            1024|         0|
1        |gc cr block 2-way      |            2048|         0|
1        |gc cr block 2-way      |            4096|         0|
1        |gc cr block 2-way      |            8192|         0|
1        |gc cr block 2-way      |           16384|         0|
2        |gc cr block 2-way      |               1|       155|
2        |gc cr block 2-way      |               2|     10792|
2        |gc cr block 2-way      |               4|     14201|
2        |gc cr block 2-way      |               8|      3887|
2        |gc cr block 2-way      |              16|       204|
...

But, we also need to see 3-way waits. Let's review 3-way waits with the above script. We will simply supply 'gc cr block 3-way' as the wait event. In this case, we see that there are 12,062 waits over between 4-8ms. Of course, this must be improved.


@gc_event_histogram.sql

gc_event_histogram.sql v1.0 by Riyaj Shamsudeen @orainternals.com

Default collection period is sleep seconds. Please wait..
Enter value for event: gc cr block 3-way
Enter value for sleep: 60
---------|-----------------------|----------------|----------|
Inst id  | Event                 |wait time milli |wait cnt  |
---------|-----------------------|----------------|----------|
1        |gc cr block 3-way      |               1|         3|
1        |gc cr block 3-way      |               2|       320|
1        |gc cr block 3-way      |               4|     17017|
1        |gc cr block 3-way      |               8|     12062|
1        |gc cr block 3-way      |              16|      1725|
1        |gc cr block 3-way      |              32|       113|
1        |gc cr block 3-way      |              64|         0|
1        |gc cr block 3-way      |             128|         0|
1        |gc cr block 3-way      |             256|         0|
1        |gc cr block 3-way      |             512|         0|
1        |gc cr block 3-way      |            1024|         0|
1        |gc cr block 3-way      |            2048|         0|
1        |gc cr block 3-way      |            4096|         0|
1        |gc cr block 3-way      |            8192|         0|
...

In essence, these four scripts will be useful in debugging Global cache performance issues. Please let me know if you encounter errors in these scripts. We will use these scripts in future discussions to show how we resolved few RAC performance issues.

Merry Christmas (Peace On Earth)

Wow, yet another year almost behind us and what looks like being yet another busy year ahead. Can you believe that The Noughties are nearly all gone, I remember being all excited at the dawn of The Eighties !! I must say I’m really looking forward to finally saying “Twenty Ten” rather than the more long-winded “Two [...]

The Core Performance Fundamentals Of Oracle Data Warehousing – Balanced Hardware Configuration

[back to Introduction] If you want to build a house that will stand the test of time, you need to build on a solid foundation. The same goes for architecting computer systems that run databases. If the underlying hardware is not sized appropriately it will likely lead to people blaming software. All too often I [...]

Oracle Database 11g Release 2 for HP-UX Itanium and AIX (PPC64) Now Available

The HP-UX Itanium and AIX (PPC64) ports of Oracle Database 11g Release 2 can now be downloaded from OTN. Happy Holidays!!! Tweet This Post

My Whole System Is Slow. Now What?

At CMG'09 a couple of weeks ago, I presented "Measuring Response Times of Code on Oracle Systems." The paper for this presentation was a subset of "For Developers: Making Friends with the Oracle Database." In the presentation, I spent a few minutes talking about why to measure response times in Oracle, and then I spent a lot of minutes talking about how. As usual, I focused heavily on the importance of measuring response times of individual business tasks executed by individual end users.

At the end of the talk, a group of people came to the podium to ask questions (always a good sign). The first question was the question that a lot of people ask. It was:

My whole system is slow. That's all my users will tell me. So then, how do I begin to do what you're describing?

Here's the answer:

Ask your users to show you what they're doing. Just go look at it.

The results of this simple advice are routinely spectacular. Just go look at it: I'm surprised whenever someone doesn't think of doing that, but I shouldn't be. That's because I didn't do it either, for the longest time. I had to learn to. And that's the story I want to tell you here.

In the early 1990s, I was a consultant with Oracle Corporation visiting clients with performance problems at a pace of more than 30 per year. Back then, I did Oracle performance work the old fashioned way: I checked everything I knew how to check, and then I fixed everything I knew how to fix. All billable by the hour. (Note: When I was doing it this way, I had not yet been taught by Dave Ensor, who changed me forever.)

On weeks when I was lucky, I'd be finished checking and fixing by sometime Wednesday, leaving a couple of days to find out what people thought of my work. If I were lucky again (that's two "lucky"s now), everyone would be thrilled with the results. I'd get my hug (so to speak), and I'd catch my flight.

But I wasn't always lucky. Some weeks, I wouldn't find anything suspicious in my checking and fixing. Some weeks, I'd find plenty, but still not everyone would be thrilled with the work. Having people be less than thrilled with my work caused pain for me, which motivated me to figure out how to take more control of my consulting engagements, to drive luck out of the equation.

The most important thing I figured out was...

People knew before I came on-site how they were going to measure on Thursday whether they liked the results of my work.

And...

They were willing to tell me on Monday.

All I had to do was be honest, like this:

On the day I'm done working here, I'd imagine you're going to want to run something that will demonstrate whether I accomplished what you were hoping for while I was here. Would you mind telling me about that now? Maybe even showing me?

I could ask that on Monday, and people were glad to tell me. I'd watch the things run and record how long they ran, and then I'd know how to prioritize my time on site. I'd record how long they ran so at the end of my engagement, I'd be able to show very clearly what improvements I had made.

Sometimes, there would be thirty different things that people would expect to measure on Thursday. If I might not have time to fix them all, then I needed to make sure that I knew the priority of the things I was being asked to fix.

That one step alone—knowing on Monday that prioritized list of what tasks needed to be fast by Thursday—drastically reduced my reliance on luck as a success factor in my job at these sites. Knowing that list on Monday is just like when your teacher in school tells you exactly what's going to be on your next test. It allows you to focus your attention on exactly what you need to do to optimize your reward for the week. (Note to fellow education enthusiasts: Please don't interpret this paragraph as my advocating the idea that GPA should be a student's sole—or even dominant—optimization constraint.)

So, what I learned is that the very first step of any good performance optimization method is necessarily this:

1. Identify the task that's the most important to you.

When I say "task," think "program" or "click" or "batch job" if you want to. What I mean is "a useful unit of work that makes sense to the business." ...Something that a business user would show you if you just went and watched her work for a few minutes.

Then comes step two:

2. Measure its response time (R). In detail.

Why is response time so important? Because that's what's important to the person who'll be watching it run on Thursday, assessing whether she thinks you've done a good job or not. That person's going to click and then wait. Happiness will be inversely proportional to how long the wait is. That's it. That's what "performance" means at 99% of sites I've ever visited.

(If you're interested in the other 1% of sites I've visited, they're interested in throughput, which I've written about in another blog post.)

Measuring response time is vital. You must be able to measure response time if you're going to nail that test on Thursday.

The key is to understand that the term response time doesn't even have a definition except in the context of a task. You can't measure response time if you don't first decide what task you're going to measure. In other words, you cannot do step 2 before you do step 1. With Oracle, for example, you can collect ASH data (if you're licensed to use it) or even trace data for a whole bunch of Oracle processes, but you won't have a single response time until you define which tasks buried within that data are the ones you want to extract and pay attention to.

You get that by visiting a user and watching what she does.

There are lots of excuses for not watching your users. Like these...

  • "I don't know my users." I know. But you should. You'd do your job better if you did. And your users would, too.
  • "My users aren't here." I know. They're on the web. They're in Chicago and Singapore and Istanbul, buying plane tickets or baseball caps or stock shares. But if you can't watch at least a simulation of the things those users actually do with the system you help manage, then I can't imagine how you would possibly succeed at providing good performance to them.
  • "I'm supposed to be able to manage performance with my dashboard." I know. I was supposed to have a hover car by the year 2000.

The longer you stay mired in excuses like these, the longer it's going to be before you can get the benefit of my point here. Your users are running something, and whatever that is that they're running is your version of my Thursday test. You can check and fix all you want, but unless you get lucky and fix the exact tooth that's hurting, your efforts aren't going to be perceived as "helpful." Checking and fixing everything you can think of is far less efficient and effective than targeting exactly what your user needs you to target.

Lots of performance analysts (DBAs, developers, architects, sysadmins, and so on) assume that when someone says, "The whole system is slow," it means there must be a single parameter somewhere in the bowels of the system that needs adjustment, and if you can just make that adjustment, everything is going to be ok. It might mean that, but in my experience, the overwhelming majority of cases are not that way. (Pages 25–29 of Optimizing Oracle Performance has more information about this.)

The great thing about measuring response time is that no matter what the problem is, you'll see it. If the program you're watching is poorly written, you'll see it. If some other program is hogging too much of a resource that your program needs, you'll see it. If you have a bad disk controller, you'll see it. If some parameter needs adjusting, you'll see it.

Realize that when a business user says "system," she doesn't mean what you would mean if you said "system." She means that the thing she runs is slow. Look at that thing. Maybe there are seventeen of them. And sure, maybe all seventeen suffer from the same root cause. If that's the case, then fantastic, because fixing the first problem will magically fix the other sixteen, too. If it's not, then fantastic anyway, because now all of them are on your prioritized list of tasks to optimize, and you'll probably surprise yourself how quickly you'll be able to pick them all off when you focus on one task at a time.

Compression to the rescue

We've had issues getting enough storage space allocated on the development server our project is intended to use. For this project, our team doesn't have full DBA rights on the box and have very limited privileges. Even before I joined the team, a request had been made for a little over 3.5 TB of space to be used for the development of a proof of concept datamart. The first project deliverable is due this week and we've only been allocated 200 GB so far. As you can imagine, it's difficult to get needed data loaded when there is less than 10% of the space we need available.

The data is supposed to span a 2 year period. A set of base transaction tables have to be extracted from various sources and loaded, then that data will be flattened out into a model that will represent the final design. With only 200 GB currently available, my initial loads for 5 (out of 50) tables took nearly all of what we had available. So, I had to do something to try and fit as much data as possible into the space we had available. Compression to the rescue!

I decided to compress the tables I had extracted using basic compression (just add COMPRESS to the table definition). But first, in order to try and get the most optimal compression possible, I collected statistics on the tables I wanted to compress and reviewed the num_distinct column statistic. Using this statistic, I sorted the data into my new compressed table using a column order from lowest to highest number of distinct column values.

When the new compressed tables were ready, I had achieved 86% compression! Using this method, I was able to load the majority of the key tables I needed to allow progress to continue for our initial project deliverable. I still haven't been able to get everything, but at least I got enough to allow our statisticians to produce some initial findings (the project is intended to create a predictive analytical model).

An additional 2 TB was allocated to us on Friday but of course it was on a different server so the initial data has to be exported and imported into the new location (sigh...). But the lack of available space gave me an opportunity to effectively use a feature I probably wouldn't have used if I had all the space I needed to start with.

The CPU Costing Model: A Few Thoughts Part III (Bang Bang)

One of the advantages of system statistics and the CPU costing model is in how the CBO deals with multiblock reads and the impact of the db_file_multiblock_read_count parameter. When performing a full table (or fast full index) scan, the CBO needs to determine just how many multiblock read operations are likely to be performed so the associated operation can [...]