UKOUG PeopleSoft Roadshow 2015

Monday, August 24, 2015

When Is An Idle Wait Not An Idle Wait?

 In general, performance tuning is quite straightforward. You work out how much time is spent doing what, and follow the numbers to the things that are taking the most time. If you are fortunate to the have the Diagnostics Pack on Enterprise Edition of Oracle you Active Session History will report the active database sessions. If you can measure the overall response time of a batch or application server you have merely to deduct the DB reported in ASH, and difference is client/middleware time.
While that is an over-simplification, it is substantially true. The rest of this blog is about an exception to the general rule.

A War Story 

I've been working with a system that makes extensive, and probably excessive, use of database links. There is a constellation of 3 Oracle databases and there are database links between them. There is also a soon-to-be legacy DB2 database, and one of the Oracle database uses Oracle Transparent Gateway to create Heterogeneous Database Links over which the application running on Oracle references data in the DB2 database.
During a load test, nearly all of the 64 application server processes are reported as busy handling requests, yet ASH reports than usually less than 2 database sessions are concurrently busy. You would expect to see 60 application server processes consuming CPU, but CPU does not appear to be as highly utilised as we should expect.
Application Server Activity
Database Activity
An application SQL trace reports that while the majority of the time is indeed spent on client CPU, about 20% is spent on a few SQL statements that reference synonyms that refer to tables or views in a DB2 database. Why didn't these show up in ASH?

A Simple Test Script 

I wrote a simple test script that just executes the SQL statements that refer to the DB2 objects, but it collects AWR snapshots at the beginning and end of the script, and it also enables Oracle session trace. 
set verify on head on timi on feedback on lines 200 trimspool on
spool otg_db2

variable b1 VARCHAR2(10)
variable b2 VARCHAR2(10)

BEGIN
 :b1 := '123456789';
 :b2 := '42';
END;

ALTER SESSION SET tracefile_identifier='OTG_DB2';
ALTER SESSION SET current_schema=APPSCHEMA;

EXEC dbms_workload_repository.create_snapshot;
EXEC dbms_application_info.set_action('OTG_DB2');
EXEC dbms_monitor.session_trace_enable;

SELECT …
FROM db2_object1 a
WHERE a.field1 = :b1 AND a.field2 = :b2
/
SELECT …
FROM db2_object2 a
WHERE a.field1 = :b1 AND a.field2 = :b2
/
…

exec dbms_application_info.set_action('NONE');
exec dbms_monitor.session_Trace_disable;
exec dbms_workload_repository.create_snapshot;

select sample_id, sample_time, sql_id, sql_plan_hash_value, sql_plan_line_id, sql_exec_id, event
from v$active_session_history
where action = 'OTG_DB2';

spool off
The first interesting output is that although some of the queries on DB2 objects took several seconds, I didn't get any rows in the query on ASH buffer for this session.
So, now let's look at the trace file. This is the output for the longest SQL statement having profiled it in TKPROF. We can see that nearly all the time is spent on an event called HS message to agent.
SELECT …
FROM db2_object1 a
WHERE a.field1 = :b1 AND a.field2 = :b2

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.04          0          0          1           0
Fetch        1      0.00       2.73          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       2.77          0          0          1           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 35  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        1        0.00          0.00
  DFS lock handle                                 1        0.00          0.00
  rdbms ipc reply                                 2        0.00          0.00
  HS message to agent                             5        2.73          2.77
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
And this is the corresponding section of the raw trace. The execution took just under 42ms, and then the fetch took 2.73 seconds.
PARSING IN CURSOR #4579254328 len=171 dep=0 uid=35 oct=3 lid=0 tim=14984112395546 hv=1291470523 ad='700010b2204a518' sqlid='xxxxxxxxxxxxx'
SELECT …
FROM db2_object1 a
WHERE a.field1 = :b1 AND a.field2 = :b2
END OF STMT
PARSE #4579254328:c=106,e=172,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=14984112395545
WAIT #4579254328: nam='Disk file operations I/O' ela= 23 FileOperation=8 fileno=0 filetype=8 obj#=-1 tim=14984112395633
WAIT #4579254328: nam='DFS lock handle' ela= 5205 type|mode=1146617861 id1=3569868743 id2=0 obj#=-1 tim=14984112401137
WAIT #4579254328: nam='rdbms ipc reply' ela= 75 from_process=46 timeout=900 p3=0 obj#=-1 tim=14984112401291
WAIT #4579254328: nam='rdbms ipc reply' ela= 710 from_process=46 timeout=900 p3=0 obj#=-1 tim=14984112402047
WAIT #4579254328: nam='HS message to agent' ela= 3464 p1=0 p2=0 p3=0 obj#=-1 tim=14984112405590
WAIT #4579254328: nam='HS message to agent' ela= 38459 p1=0 p2=0 p3=0 obj#=-1 tim=14984112444724
WAIT #4579254328: nam='HS message to agent' ela= 17 p1=0 p2=0 p3=0 obj#=-1 tim=14984112444791
EXEC #4579254328:c=932,e=49215,p=0,cr=0,cu=1,mis=1,r=0,dep=0,og=1,plh=0,tim=14984112444865
WAIT #4579254328: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=14984112444958
WAIT #4579254328: nam='HS message to agent' ela= 2730045 p1=0 p2=0 p3=0 obj#=-1 tim=14984115175093
WAIT #4579254328: nam='HS message to agent' ela= 8 p1=0 p2=0 p3=0 obj#=-1 tim=14984115175165
FETCH #4579254328:c=206,e=2730295,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=14984115175285
WAIT #4579254328: nam='SQL*Net message from client' ela= 895 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=14984115176264
CLOSE #4579254328:c=10,e=16,dep=0,type=0,tim=14984115176343
Across the whole of my test I have 29 waits on this event totalling 5.3s. Why I am not seeing this event in ASH?
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        9      0.00       0.00          0          0          0           0
Execute     10      0.00       0.21          0          0          1           3
Fetch        7      0.00       5.10          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       26      0.01       5.32          0          0          1           3

Misses in library cache during parse: 3
Misses in library cache during execute: 4

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       9        0.00          0.00
  SQL*Net message from client                     9        0.00          0.00
  Disk file operations I/O                        9        0.00          0.00
  DFS lock handle                                 1        0.00          0.00
  rdbms ipc reply                                 2        0.00          0.00
  HS message to agent                            29        2.73          5.30
  library cache pin                               2        0.00          0.00
If I generate an ASH report on the two snapshots created in this test and look at the Foreground events I might start to see why. AWR reports 29 waits totalling 5 seconds, so that agrees. However, note that this is at the bottom of the report with 'SQL*Net message from client' and there is no '%DB Time'.
AWR Foreground Events

We (should) know that 'SQL*Net message from client' is an idle event. So we can understand that there are 806 seconds reported on this event in this AWR report because there were 151 sessions connect to the database, most of which were mostly idle for the 6 seconds between the AWR snapshots. That is perfectly normal.
However, HS message to agent is classed as an idle wait, although the user was not idle. During this event the application is not busy, it is waiting for the Oracle database to respond, which is in turn waiting for the remote non-Oracle database to respond.
 This is in contrast to Oracle-to-Oracle (homogeneous) database links which report time to 'SQL*Net message from dblink' which is not an idle wait, but is part of the 'Network' wait class. I think the difference is an anomaly.
The next question is how much time my application spent on this event. For that I must look at the AWR data, either by generating an AWR report that corresponds to the load test, or directly in dba_hist_system_event.

Waits from AWR snspshots

Conclusion

  1. I am not the first person to call this out, just the latest person to get caught by it. It may be an idle wait, but the user is waiting for the database to respond. 
    Database Link Wait Event Wait Class
    Heterogeneous HS message to agent Idle
    Homogeneous SQL*Net message from dblink Network
  2. I got as far as starting to write an e-mail to a colleague asking for help, but before sending it I realised that I could do more to find out what was happening. Perhaps just the act of try to explain the problem helped me to think more clearly about it.

Thursday, December 20, 2012

No DBA is an Island

Introduction

I gave an OakTalk at the UKOUG2012 conference. This article is based upon the notes for that talk. It is not a technical article, just an opinion piece.
I have always described myself as a DBA, although it has been many years since I had day-to-day responsibility for a production database.  My day job is about performance, helping people to get the best possible performance from their PeopleSoft systems.  That can involve anything from tuning a specific SQL statement, to changing how the database, middleware, or application has been configured, through to standing back and looking at the way the architecture has been put together.  Lots of people have to work together on any system, and I frequently work with people for whom the database is not their home
The concept of the division of labour is nothing new.  Plato suggested the ‘minimum state would consist of four or five men’.  Adam Smith (possibly inspired by Henri-Louis Duhamel du Monceau) famously took the manufacture of pins as his example.  Businesses must break tasks down into manageable units, and as they do each person’s job becomes progresively more specific and specialised. 
IT is no exception.  The modern IT department is broken down into teams of administrators.  We have desktop teams, network teams, application administrators, middleware teams, DBA, operating system admins, storage area network teams.  In some places, I have seen DBA teams broken down into a core database team and separate application DBA teams for each major application.  Every tier and technology has its team, each with specialist knowledge and skills. Such are the tribes of IT.
I have heard these groupings described as verticals, or stove-pipes, or silos.  The clue is in the name.  The problem that I perceive is that these groups are not always good at communicating with each other, and when something goes wrong, they are not always above blaming one another.

What is IT for? 

A modern company simply couldn’t function without IT.  The business systems that we, as IT professionals design, build and manage are fundamental to the companies in which we work.  There will be lots of different systems that do stuff.  Different companies will do slightly different stuff.  Absolute minimum, you need a PC that can connect to things.  There will always be e-mail. There will be a Finance system to track the money.  There will be some form of HR system to track employees.  There will be a payroll system to pay the employees.  If a company sells widgets, there will be some form stock control, and some form of logistics to manage its movement.  Those systems will communicate with each other. Lots of systems, and it doesn’t really matter what the system is.  The point is that every system will need the services provided by several of those IT silos. Each system is a chain of technology that is laid horizontally across those IT silos. At one end you have users, and at the other end you probably have physical spinning disks (for now).  Over time, those chains have come to exist of every more links.

The DBA’s view?

I described myself as a DBA, because that is the tribe I came from. However, I firmly believe, if I was still a day-to-day production DBA, that in order to do my job properly, I would occasionally need to look out from my silo and not just to look at the neighbouring silos along the chains of technology, but all the silos on the chains, and sometimes even outside IT department.
I believe that the DBA is well placed and appropriately skilled to asses most aspects of a system.  Let me illustrate this with 2 examples

Example 1: Two Temporary Tablespace

I have seen more than one HR system where the temporary tablespace has been allowed to grow hugely, sometimes hundreds of GB, in response to space errors. Each time, the root cause has been users running ad-hoc queries concurrently with critical batch processes. So, I have suggested that they use two temporary tablespaces.
•    One for regular processing – on-line transaction processing, regular and scheduled batch processes.
•    Another for ad-hoc queries whether submitted by ad-hoc database users or by the application’s ad-hoc query tool.
As a DBA, that seems like a straight forward technical decision.  It will protect the critical batches from out of space errors that occur when the temp space has been consumed by the ad-hoc queries.  It allows me to regulate bad queries by restricting their temporary tablespace consumption limiting their effect on the system while they run for hours on end while they copy data into the temporary tablespace (and before anybody asks, we are also thinking about Oracle resource manager).
But it isn’t as simple as that.  It requires a different relationship between the users and their application support team.  Now, when a query crashes with an out of space error we don’t just extended the temporary tablespace, we need to examine the query and consider how it can be improved. Meanwhile, the users have to understand that this approach is for the greater good of the system, and in time it will also improve their query performance.
It is no longer just a technical problem.  It has become political.  It needs to be sold to the users, and it needs a change in business process.  So the DBA needs to talk to people outside their silo, and sometimes even outside IT.

Example 2: Archiving

I work on an Time & Labor and Payroll system that has run for 4 years without any archiving.  We have 4 years of schedule data.  4 years of reported time.  4 years of payroll data. 4 years of audit data.  We now have a 4TB OLTP database. Archiving was de-scoped from the initial implementation.
How have we survived this long?  Many of the tables that hold this data have been partitioned, mostly on a time basis – that is to say that different periods of time are different partitions.  In many cases, but by no means all, the long tail of history is hidden by partition elimination.  Partitioning was introduced to improve performance, but the design of the partitioning always had an eye to future archiving.
With or without partitioning, keeping all the data for ever is not an option.  Quite apart from data security and performance, the size of the database is becoming a challenge.  Periodically, we have to copy the production database into various development and test environments.  We trim some history from some of those environments, but size is still a challenge.
So we had to look at archive and purge.  So the first step it to understand the application, and then work out with the business users at what point do they at least no longer need the data to operate their process, and at what point can we ultimately purge the data entirely from the database. 
We are about to deliver the first tranche of archiving into one functional area.  Conceptually our process is very simple.  Some data we can purge, often by dropping partitions.  Some data we have had to keep for ad-hoc query.  Where possible, we use partition exchange to exchange historical partitions into archive tables.  We have also been very successful in using table compression to further reduce the overall database size.
Archive and purge is going to be a regular, rolling process - at least monthly.  It is going to be done without any system outage, and it will as far as possible be automated.  Therefore, we have had to build some of these processes into the application not least so the application support team has visibility. The archive process is sensitive to system date and some application data. So it has had to involve developers.
We have created a meta-data driven PL/SQL package, effectively an API, to do all the partition handling and compression. The developers call that from their processes.
It takes someone who is, if not a DBA, then very database aware to design the concept (and you’ve probably guessed correctly that in this case that DBA is me). That person also needs to have some understanding of the application and how the application data fits together. I think it is also essential, at least for the largest and most critical systems, that the DBA understands how the business uses the application.  The DBA can’t do this hiding in their silo.

Conclusion

I have heard it said that some production DBAs think their job is to make sure nothing happens; by making sure that nothing happens. Business is not about maintaining status quo.  Businesses talk about bigger, better, cheaper, faster. You can track the pulse of a business by measuring its critical IT systems. A DBA’s job is not to hold the tide back, but to make sure that the systems we tend are fit for purpose, and continue so to be. We need to be proactive. Sometimes, we should go out and be consultants, look for improvements, initiate changes, and advise on developments.
Therefore, if you are a production DBA hiding in your silo, send not to know for whom the bell tolls, it tolls for thee.

Thursday, July 19, 2012

Gathering Aggregated Cost-Based Optimiser Statistics on Partitioned Objects

Recently, I have been looking into how to gather cost-based optimizer statistics on composite partitioned objects.  Collecting global statistics on a large partitioned object can be a time-consuming and resource intensive business as Oracle samples all the physical partitions or sub-partitions. Briefly, if you do not collect global statistics on a partitioned table, Oracle will aggegrate the statistics on the physical partitons or sub-partitions to calculate statistics on the logical table and partition segments.

Oracle 10g makes a number of mistakes in its calculation of these aggregated statistics.  In particular, the number of distinct values on columns by which the table is partitioned have impossibly low values.  This is can affect cardinality calculations and so lead the optimizer to choose the wrong execution plan.

I have now published the second of two documents on my website that examine aspects of statistics on partitioned, and in particular composite partitioned tables. The first document examines the problems in 10g, and proposes a procedure to 'correct' the aggregated statistics to at least minimum possible values.
The second document looks at the same issue in 11g and shows that while most of the issues are fixed, one problem remains.







Friday, March 02, 2012

Editing Hints in Stored Outlines

Introduction 
This note describes changing a hint in a SQL Outline by updating the OUTLN.OL$ and OUTLN.OL$HINTS tables directly. It shows that in the limited test case that it does affect the resulting execution plan.  

CAVEAT: This is a hack, so this is more of an oddity that something that I would recommend for use in production. I am only interested in it for the limited purpose of adding a NO_EXPAND hint to an outline, that would not otherwise be included if the outline was generated in the conventional manner.

Why Outlines? 
I am using outlines because I am working with Oracle 10g, so I don't have SQL Plan Management until Oracle 11g. I am not using SQL Profiles because I am working with PeopleSoft Global Payroll. I want complete stability of execution plans rather than have the optimizer produce 'better plans' with adjusted costing. Otherwise a single employee payroll calculation can put a different plan into the library cache which can then be used for a company-wide payroll calculation. I want to guarantee just one execution plan.

Preparation
I'll start by creating a table to use.
DROP TABLE t PURGE
/
CREATE TABLE t
(a NUMBER NOT NULL
,b NUMBER NOT NULL
,c VARCHAR2(100)
)
/
TRUNCATE TABLE t
/
INSERT INTO t (a,b,c)
SELECT row_number() over (order by level)
,      row_number() over (order by level desc)
,      TO_CHAR(TO_DATE(rownum,'J'),'Jsp')
FROM DUAL
CONNECT BY LEVEL <= 10000
/
BEGIN
 dbms_stats.gather_table_stats
 (ownname=>user
 ,tabname=>'T'
 ,cascade=>TRUE
 );
end;
/

Table T has 10000 rows. Column A is numbered 1 to 10000, B is numbered 10000 to 1, and there is a third column to provide some padding.

         A          B C
---------- ---------- ----------------------------------------
         1      10000 One
         2       9999 Two
         3       9998 Three
…
      9998          3 Nine Thousand Nine Hundred Ninety-Eight
      9999          2 Nine Thousand Nine Hundred Ninety-Nine
     10000          1 Ten Thousand

Collecting Outlines 
 So, the table doesn't have any indexes. I will disable autotrace in SQL*Plus, and dynamic sampling to prevent collection of additional SQL outlines.

DROP INDEX t1;
DROP INDEX t2;

SET AUTOTRACE OFF
CLEAR SCREEN
ALTER SESSION SET optimizer_dynamic_sampling=0;
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET create_stored_outlines=C1;

SELECT *
FROM t
WHERE a=42
OR    b=42
/

ALTER SESSION SET create_stored_outlines=FALSE;
ALTER SESSION SET use_stored_outlines=FALSE;

Without any indexes, Oracle can only do a full scan on the table.

Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    86 |    22   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     2 |    86 |    22   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("A"=42 OR "B"=42)

I will now add the indexes and create another stored outline for the same statement, but in a different category.

CREATE INDEX t1 ON t (a,b);
CREATE INDEX t2 ON t (b,a);
SET AUTOTRACE OFF 
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET create_stored_outlines=C2;

SELECT *
FROM t
WHERE a=42
OR    b=42
/
ALTER SESSION SET create_stored_outlines=FALSE;
ALTER SESSION SET use_stored_outlines=FALSE;

With the indexes the execution plan includes contenation of queries on each index.

Plan hash value: 277049827
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     2 |    86 |     6   (0)| 00:00:01 |
|   1 |  CONCATENATION               |      |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     1 |    43 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T2   |     1 |       |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T    |     1 |    43 |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | T1   |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("B"=42)
   5 - access("A"=42)
       filter(LNNVL("B"=42))

I will create a third stored outline in a third category so that I have two copies of the outline for comparison.

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET create_stored_outlines=C3;

SELECT *
FROM t
WHERE a=42
OR    b=42
/

ALTER SESSION SET create_stored_outlines=FALSE;

I am going to rename the outlines to match the category to make it easier to work in the rest of this example.

BEGIN
 FOR i IN (
  SELECT DISTINCT c1.category, c1.name name
  FROM user_outlines c1
  , user_outlines c2
  WHERE c1.category != c2.category 
  AND c1.signature = c2.signature
  AND c1.category != c1.name
 ) LOOP
  EXECUTE IMMEDIATE 'ALTER OUTLINE '||i.name||' RENAME TO '||i.category;
 END LOOP;
END;
/

Looking at Outlines
We now have three outlines

SET AUTOTRACE OFF PAGES 100 LINES 120
BREAK ON name SKIP 1
SELECT * FROM user_outlines ORDER BY 1
/

NAME       CATEGORY   USED   TIMESTAMP           VERSION
---------- ---------- ------ ------------------- ----------
SQL_TEXT
--------------------------------------------------------------------------------
SIGNATURE                        COMPATIBLE ENABLED  FORMAT     MIGRATED
-------------------------------- ---------- -------- ---------- ------------
C1         C1         UNUSED 14:49:10 01/03/2012 11.2.0.2.0
SELECT *
FROM t
WHERE a=42
OR    b=42
260D04C39979D97078EDCE9B6727C4A3 COMPATIBLE ENABLED  NORMAL     NOT-MIGRATED


C2         C2         UNUSED 14:49:10 01/03/2012 11.2.0.2.0
SELECT *
FROM t
WHERE a=42
OR    b=42
260D04C39979D97078EDCE9B6727C4A3 COMPATIBLE ENABLED  NORMAL     NOT-MIGRATED


C3         C3         UNUSED 14:48:33 01/03/2012 11.2.0.2.0
SELECT *
FROM t
WHERE a=42
OR    b=42
260D04C39979D97078EDCE9B6727C4A3 COMPATIBLE ENABLED  NORMAL     NOT-MIGRATED

And these are the hints in the outlines

SELECT  *
FROM user_outline_hints
ORDER BY 1,2,3
/

NAME        NODE  STAGE  JOIN_POS HINT
---------- ----- ------ --------- ----------------------------------------
C1             1      1         1 FULL(@"SEL$1" "T"@"SEL$1")
               1      1         0 OUTLINE_LEAF(@"SEL$1")
               1      1         0 ALL_ROWS
               1      1         0 OPT_PARAM('optimizer_dynamic_sampling' 0)
               1      1         0 DB_VERSION('11.2.0.2')
               1      1         0 OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
               1      1         0 IGNORE_OPTIM_EMBEDDED_HINTS

C2             1      1         1 INDEX_RS_ASC(@"SEL$1_2" "T"@"SEL$1_2" ("T"."A" "T"."B"))
               1      1         1 INDEX_RS_ASC(@"SEL$1_1" "T"@"SEL$1" ("T"."B" "T"."A"))
               1      1         0 OUTLINE(@"SEL$1")
               1      1         0 OUTLINE_LEAF(@"SEL$1_2")
               1      1         0 USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1))
               1      1         0 OUTLINE_LEAF(@"SEL$1_1")
               1      1         0 OUTLINE_LEAF(@"SEL$1")
               1      1         0 ALL_ROWS
               1      1         0 OPT_PARAM('optimizer_dynamic_sampling' 0)
               1      1         0 DB_VERSION('11.2.0.2')
               1      1         0 OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
               1      1         0 IGNORE_OPTIM_EMBEDDED_HINTS

C3             1      1         1 INDEX_RS_ASC(@"SEL$1_2" "T"@"SEL$1_2" ("T"."A" "T"."B"))
               1      1         1 INDEX_RS_ASC(@"SEL$1_1" "T"@"SEL$1" ("T"."B" "T"."A"))
               1      1         0 OUTLINE(@"SEL$1")
               1      1         0 OUTLINE_LEAF(@"SEL$1_2")
               1      1         0 USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1))
               1      1         0 OUTLINE_LEAF(@"SEL$1_1")
               1      1         0 OUTLINE_LEAF(@"SEL$1")
               1      1         0 ALL_ROWS
               1      1         0 OPT_PARAM('optimizer_dynamic_sampling' 0)
               1      1         0 DB_VERSION('11.2.0.2')
               1      1         0 OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
               1      1         0 IGNORE_OPTIM_EMBEDDED_HINTS

Or, I could have queried from OUTLN.OL$HINTS
 
SET LINES 110 PAGES 999
BREAK ON ol_name SKIP 1
SELECT ol_name,hint#,category,hint_type,hint_text
,stage#,node#,table_name,table_tin,table_pos
,ref_id,user_table_name,cost,cardinality,bytes
,hint_textoff,hint_textlen
FROM outln.ol$hints
WHERE category IN('C1','C2')
ORDER BY 1,2,3,4,5
/

OL_NAME    HINT# CATEGORY  HINT_TYPE HINT_TEXT                                                    STAGE# NODE#
---------- ----- -------- ---------- ------------------------------------------------------------ ------ -----
TABLE_NAME  TABLE_TIN  TABLE_POS     REF_ID USER_TABLE       COST CARDINALITY      BYTES HINT_TEXTOFF HINT_TEXTLEN
---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ------------ ------------
C1             1 C1                2 FULL(@"SEL$1" "T"@"SEL$1")                                        1     1
T                   1          1          0 SYSADM.T   22.1083368           2         86           15            1

               2 C1             1011 OUTLINE_LEAF(@"SEL$1")                                            1     1
                    0          0          0                     0           0          0            0        0

               3 C1             1013 ALL_ROWS                                                          1     1
                    0          0          0                     0           0          0            0        0

               4 C1             1012 OPT_PARAM('optimizer_dynamic_sampling' 0)                         1     1
                    0          0          0                     0           0          0            0        0

               5 C1               54 DB_VERSION('11.2.0.2')                                            1     1
                    0          0          0                     0           0          0            0        0

               6 C1             1009 OPTIMIZER_FEATURES_ENABLE('11.2.0.2')                             1     1
                    0          0          0                     0           0          0            0        0

               7 C1             1008 IGNORE_OPTIM_EMBEDDED_HINTS                                       1     1
                    0          0          0                     0           0          0            0        0

C2             1 C2               51 INDEX_RS_ASC(@"SEL$1_2" "T"@"SEL$1_2" ("T"."A" "T"."B"))          1     1
T                   1          1          0 SYSADM.T   3.00073364           1         43           15            1

               2 C2               51 INDEX_RS_ASC(@"SEL$1_1" "T"@"SEL$1" ("T"."B" "T"."A"))            1     1
                    1          1          0 SYSADM.T   3.00073196           1         43           15            1

               3 C2             1010 OUTLINE(@"SEL$1")                                                 1     1
                    0          0          0                     0           0          0            0        0

               4 C2             1011 OUTLINE_LEAF(@"SEL$1_2")                                          1     1
                    0          0          0                     0           0          0            0        0

               5 C2               38 USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1))                           1     1
                    0          0          0                     0           0          0            0        0

               6 C2             1011 OUTLINE_LEAF(@"SEL$1_1")                                          1     1
                    0          0          0                     0           0          0            0        0

               7 C2             1011 OUTLINE_LEAF(@"SEL$1")                                            1     1
                    0          0          0                     0           0          0            0        0

               8 C2             1013 ALL_ROWS                                                          1     1
                    0          0          0                     0           0          0            0        0

               9 C2             1012 OPT_PARAM('optimizer_dynamic_sampling' 0)                         1     1
                    0          0          0                     0           0          0            0        0

              10 C2               54 DB_VERSION('11.2.0.2')                                            1     1
                    0          0          0                     0           0          0            0        0

              11 C2             1009 OPTIMIZER_FEATURES_ENABLE('11.2.0.2')                             1     1
                    0          0          0                     0           0          0            0        0

              12 C2             1008 IGNORE_OPTIM_EMBEDDED_HINTS                                       1     1
                    0          0          0                     0           0          0            0        0
 
Editing Outlines 
Adding a Hint 
I can add a new hint, thus

REM Insert hint at positition 0
INSERT INTO outln.ol$hints
(ol_name,hint#,category,hint_type,hint_text
,stage#,node#,table_name,table_tin,table_pos
,ref_id,user_table_name,cost,cardinality,bytes
,hint_textoff,hint_textlen)
VALUES
('C2',0,'C2',42,'NO_EXPAND'
,1,1,NULL,0,0
,0,NULL,0,0,0
,0,0)
/
REM increment hint numbers if there is a hint at position 0
UPDATE outln.ol$hints x
SET hint#=hint#+1
WHERE EXISTS (select 'x'
 FROM outln.ol$hints y
 WHERE y.ol_name = x.ol_name
 AND y.hint#=0)
AND ol_name = 'C2'
/
REM update the hint count on the parent record
UPDATE outln.ol$ x
set hintcount = (
 SELECT count(*)
 FROM outln.ol$hints h
 where h.ol_name = h.ol_name)
where ol_name = 'C3'
/

Changing a Hint 
However, in this case I want to change an exisiting hint from USE_CONCAT to NO_EXPAND.

UPDATE outln.ol$hints
SET    hint_text = 'NO EXPAND'
WHERE  ol_name = 'C3'
AND    hint_text like 'USE_CONCAT(%)'
/

Testing Outlines 
Original Outline 

CLEAR SCREEN
SET AUTOTRACE OFF
ALTER SESSION SET statistics_level = ALL;
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET use_stored_outlines=C2;
SELECT *
FROM t
WHERE a=42
OR    b=42
/
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'ADVANCED'))
/

The unchanged outline C2 still produces the concatenation

Plan hash value: 277049827
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     2 |    86 |     6   (0)| 00:00:01 |
|   1 |  CONCATENATION               |      |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     1 |    43 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T2   |     1 |       |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T    |     1 |    43 |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | T1   |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("B"=42)
   5 - access("A"=42)
       filter(LNNVL("B"=42))

Editted Outline 
Now, let's try the outline that I updated directly.

ALTER SESSION SET use_stored_outlines=C3;
SELECT *
FROM t
WHERE a=42
OR    b=42
/
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'ADVANCED'))
/
ALTER SESSION SET use_stored_outlines=FALSE;

I get a different execution plan that doesn't do CONCATENATION. Note that the outline still doesn't contain the NO_EXPAND hint that I put into the outline.

Plan hash value: 4269684720
-----------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |      |       |       |     6 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID     | T    |     2 |    86 |     6  (34)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |      |       |       |            |          |
|   3 |    BITMAP OR                     |      |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|      |       |       |            |          |
|   5 |      SORT ORDER BY               |      |       |       |            |          |
|*  6 |       INDEX RANGE SCAN           | T1   |       |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      DB_VERSION('11.2.0.2')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      BITMAP_TREE(@"SEL$1" "T"@"SEL$1" OR(1 1 ("T"."A" "T"."B")))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("A"=42)
       filter("A"=42)

Wednesday, October 21, 2009

Partition Maintenance with Global Indexes

(updated 3.12.2009 to deal with UPDATE GLOBAL INDEXES option)
When I decide to partition a table, I also need to consider whether to partition the indexes, and if so how. The easiest option is to locally partition the indexes.  I try to avoid globally partitioned indexes because they can become invalid when you do partition maintenance. However, where an index leads on a column other than the partitioning key, then you might have to scan all partitions of a locally partitioned index if you do not query by the partitioning column.  Sometimes, it is necessary to partition an index differently to the table, or not at all. However, I have found that there are some partition management operations that do not invalidate global indexes.

In the case of PeopleSoft Global Payroll I range partition tables to match the Payroll 'stream' definitions. Thus each concurrent payroll process only updates one partition, and more importantly, each partition is updated by one, and only one, payroll process. Thus eliminating consistent read on these objects. The number of payroll processes is usually a function of the hardware configuration. This doesn't change often, so I don't expect to do partition maintenance. If I need to change the payroll stream definitions, then I will completely rebuild the partitioned tables.

In the case of Time & Labor, I create periodic partitions (weekly, fortnightly, or monthly) for the reported time table, partitioning on the 'date under reporting' column. This column is used by many of the queries in T&L processing, resulting in partition elimination. However, it is also necessary to periodically add new partitions.

The Oracle documentation says this about
"Management of Global Partitioned Indexes
Global partitioned indexes are harder to manage than local indexes:

When the data in an underlying table partition is moved or removed (SPLIT, MOVE, DROP, or TRUNCATE), all partitions of a global index are affected. Consequently global indexes do not support partition independence."

The key point is that the global index is invalidated if rows of data in a partition table are affected by DDL on a partition. Here is a little demonstration. I will create a table with a locally partitioned unique index, and a second non-partitioned index.

DROP TABLE t PURGE;

CREATE TABLE t
(a NUMBER NOT NULL
,b VARCHAR2(100) NOT NULL
)
PARTITION BY RANGE(a)
(PARTITION T1 VALUES LESS THAN (100)
,PARTITION T2 VALUES LESS THAN (200)
);

CREATE UNIQUE INDEX ia ON t (a)
LOCAL
(PARTITION i1
,PARTITION i2
);

CREATE INDEX ib ON t (b);

INSERT INTO t
SELECT ROWNUM, TO_CHAR(TO_DATE(ROWNUM,'j'),'Jsp')
FROM dba_objects
WHERE ROWNUM < 200;
COMMIT;

Now I will add and populate a third partition

ALTER TABLE t
ADD PARTITION t3 VALUES LESS THAN (300);

INSERT INTO t
SELECT ROWNUM+199, TO_CHAR(TO_DATE(ROWNUM+199,'j'),'Jsp')
FROM dba_objects
WHERE ROWNUM+199 < 300 ; COMMIT;

And now I will check on the indexes.

column table_name     format a5 heading 'Table|Name'
column index_name     format a5 heading 'Index|Name'
column partition_name format a4 heading 'Part|Name'
column partitioned              heading 'Part?#'
column status                   heading 'Index|Status'
SELECT table_name, index_name, partitioned, status
FROM   user_indexes where table_name = 'T' ORDER BY 1,2;
SELECT index_name, partition_name, status
FROM   user_ind_partitions WHERE index_name IN (SELECT index_name FROM user_indexes WHERE table_name = 'T') order by 1,2,3;

Note that the global index IB is still usable

Table Index Index
Name  Name  Part?# Status
----- ----- ------ ------
T     IA    YES    N/A
T     IB    NO     VALID

Index Part Index
Name  Name Status
----- ---- ------
IA    I1   USABLE
IA    I2   USABLE
IA    T3   USABLE

The interesting thing is that if I delete all the rows from a partition then I can drop it, and the index does not go unusable.

DELETE FROM t WHERE a>=200;
ALTER TABLE t DROP PARTITION t3;

Table Index Index
Name   Name Part?# Status
----- ----- ------ ------
T     IA    YES    N/A
T     IB    NO     VALID

Index Part Index
Name  Name Status
----- ---- ------
IA    I1   USABLE
IA    I2   USABLE

but if I didn't delete all rows from partition t3 before I drop it, then the global index goes unusable.

ALTER TABLE t DROP PARTITION t3;

Table Index Index
Name  Name  Part?# Status
----- ----- ------ --------
T     IA    YES    N/A
T     IB    NO     UNUSABLE

Index Part Index
Name Name Status
----- ---- --------
IA I1 USABLE
IA I2 USABLE

If I truncate the table such that I remove rows, then the index immediately becomes unusable

ALTER TABLE t TRUNCATE PARTITION t3;

Table Index Index
Name Name Part?# Status
----- ----- ------ --------
T IA YES N/A
T IB NO UNUSABLE

Index Part Index
Name Name Status
----- ---- --------
IA I1 USABLE
IA I2 USABLE

However, if I had deleted those rows first the truncate would not have invalidated the index.

Addendum As Randolf points out in his comment, it is possible to prevent the global index from being invalidated by specifying the UPDATE GLOBAL INDEXES clauses.

ALTER TABLE t DROP PARTITION t1 UPDATE GLOBAL INDEXES;

However, this comes at a cost.  Oracle now full scans the partition being dropped and removes the entries from the index.  This recursive SQL statement can be found in an Oracle trace.

insert /*+ RELATIONAL(T) PARALLEL(T,1) APPEND */ into "SYSADM"."T" partition ("T1")
select /*+ RELATIONAL(T) PARALLEL(T,1) */ * 
from "SYSADM"."T" partition ("T1") delete global indexes


Conclusion

There are some things that I can do without invalidating global indexes
  • I can add a new partition
  • I can delete all rows from an existing partition and drop it.
  • I can truncate a partition from which all the rows have been deleted.
  • I can drop a partition with rows if I specify the UPDATE GLOBAL INDEXES clause, but this could take time.
But
  • If I truncate a partition that does contain rows then I will immediately invalidate the index.
  • If I do any DDL that removes rows from a partitioned table, I will invalidate global indexes because they contain rowids that are invalid.
Ironically, one of the advantages of partitioning is that you can archive a whole partition with a quick DDL operation, rather than working through each row. But this would invalidate a global index. However, if I use something like the generic PeopleSoft archiving tool, which purges data with DML, then when the partition is empty I can drop the table partition without invalidating any global indexes!