Search

OakieTags

Who's online

There are currently 1 user and 37 guests online.

Online users

Recent comments

Oakies Blog Aggregator

Oracle Database Appliance (ODA) Patch Bundle 2.1.0.3.0 Fixes CPU Performance Issue

If you are using Oracle Database Appliance (ODA) then this patch is very important to apply. The patch bundle 2.1.0.3.0 has been published just this week and the most important fix from my perspective is the new BIOS version 12010304. Intel CPUs have a feature called Software Controlled Clock Modulation that allows programmatically control of [...]

Availability Infrastructure & Management SIG March 14th 2012

I am proud to be able to speak at the first instalment of the Availability, infrastructure and management SIG on March 14th in  the London City office.

The event is announced on the UKOUG website here:

http://www.ukoug.org/events/ukoug-availability-infrastructure-and-management-sig-meeting/

Unfortunately I will be between you and lunch! I hope that works out, and I don’t overrun.

I am going to demonstrate my (little) knowledge of Oracle Enterprise Manager 12.1. I looked at it for one of my customers and came to like it. As it is very different from the previous versions of the product, a more closely focused session seems appropriate. An Internet connection permitting, I am going to demonstrate navigation through the new interface, self update, target discovery and if time permits, I will patch a single instance HA environment (also known as Oracle Restart).

If all demos work this could be quite an entertaining sessions, questions are welcome!

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.

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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.

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
         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.

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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.

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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.

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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.

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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.

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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.

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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
 

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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.

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
UPDATE outln.ol$hints
SET hint_text = 'NO EXPAND'
WHERE ol_name = 'C3'
AND hint_text like 'USE_CONCAT(%)'
/

Testing Outlines 
Original Outline 

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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.

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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.

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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)

Add Constraint

Here’s a quirky little detail that may make you think carefully about how you define and load large tables.
I have a large table which I load with data and then apply the following:

alter table t_15400 modify (id not null, small_vc not null);

Would you really expect to find Oracle doing two tablescans on the table to enable these constraints ? This is what I found in a trace file (with a lot of db file scattered read waits and other stuff in between) when I ran the test recently on 11.2.0.3:

select /*+ all_rows ordered */ A.rowid, :1, :2, :3 from "SYS"."T_15400" A where( "ID" is null)
select /*+ all_rows ordered */ A.rowid, :1, :2, :3 from "SYS"."T_15400" A where( "SMALL_VC" is null)

It’s just a little difficult to come up with a good reason for this approach, rather than a single statement that validates both constaints at once.

Somewhere I think I’ve published a note that points out that when you add a primary key constraint Oracle first checks that the key column (or column set) is not null – which means that adding a primary key may also result in a tablescan for every column in the index before the index is created – but in that case you can’t see the SQL that checks each column, you have to infer the check from the number of tablescans and number of rows fetched by tablescan. The trace file is rather more helpful if all you’re doing is adding the not null constraints.

Recent Oracle 8-Socket Xeon E7 TPC-C Result. Big NUMA Box, No NUMA Parameters.

I’ve read through the full disclosure report from Oracle’s January 2012 TPC-C. I’ve found that the result was obtained without using any NUMA init.ora parameters (e.g., enable_NUMA_support). The storage was a collection of Sun x64 servers running COMSTAR to serve up F5100 flash storage. The storage connectivity was 8GFC fibre channel. This was a non-RAC result with 8s80c160t Xeon E7. The only things that stand out to me are:

  1. The settings of disk_async_io=TRUE. This was ASM on raw disk so I should think ASYNC would be the default. Interesting.
  2. Overriding the default number of DBWR processes by setting db_writer_processes. The default number of DBWR processes would be 20 so the benchmark team increased that 60%. Since sockets are NUMA “nodes” on this architecture the default of 20 would render 2.5 DBWR per “node.” In my experience it is beneficial to have DBWR processes an equal multiple of the number of sockets (NUMA nodes) so if the benchmark team was thinking the way I think they went with 4x socket count.

The FDR is here: http://c970058.r58.cf2.rackcdn.com/fdr/tpcc/Oracle_X4800-M2_TPCC_OL-UEK-FDR_011712.pdf

For more information about the missing enable_NUMA_support parameter see: Meet _enable_NUMA_support: The if-then-else Oracle Database 11g Release 2 Initialization Parameter.

For a lot more about NUMA as it pertains to Oracle, please visit: QPI-Based Systems Related Topics (e.g., Nehalem EP/EX, Westmere EP, etc)

On the topic of increasing DBWR processes I’d like to point out that doing so isn’t one of those “some is good so more must be better” situations. For more reading on that matter I recommend:

Over-Configuring DBWR Processes Part I

Over-Configuring DBWR Processes Part II

Over-Configuring DBWR Processes Part III

Over-Configuring DBWR Processes Part IV

The parameters:

Got A Big NUMA Box For Running Oracle? Take Care To Get Interrupt Handling Spread Across The Sockets Evenly
Page 310 of the FDR shows the following script used to arrange good affinity between the FC HBA device drivers and the sockets. I had to do the same sort of thing with the x4800 (aka Exadata X2-8) back before I left Oracle’s Exadata development organization. This sort of thing is standard but I wanted to bring the concept to your attention:


#!/bin/bash
 service irqbalance stop
 last_node=-1
 declare -i count=0
 declare -i cpu cpu1 cpu2 cpu3 cpu4
 for dir in /sys/bus/pci/drivers/qla2xxx/0000*
do
 node=`cat $dir/numa_node`
 irqs=`cat $dir/msi_irqs`
 if [ "`echo $irqs | wc -w`" != "2" ] ; then
 echo >&2 "script expects 2 interrupts per device"
 exit 1
 fi
first_cpu=`sed 's/-.*//' < $dir/local_cpulist` 
echo $node $irqs $first_cpu $dir done | sort | while read node irq1 irq2 cpu1 dir 
do 
cpu2=$cpu1+10 
cpu3=$cpu1+80 
cpu4=$cpu1+90 
if [ "$node" != "$last_node" ]
then 
count=1 cpu=$cpu1 
else 
count=$count+1 
case $count in 
2) cpu=$cpu2;; 
3) cpu=$cpu3;; 
4) cpu=$cpu4;; 
*) echo "more devices than expected on node $node" count=1 cpu=$cpu1;; 
esac 
fi 
last_node=$node 
echo "#$dir" 
echo "echo $cpu > /proc/irq/$irq1/smp_affinity_list"
 echo "echo $cpu > /proc/irq/$irq2/smp_affinity_list"
 echo
 echo $cpu > /proc/irq/$irq1/smp_affinity_list
 echo $cpu > /proc/irq/$irq2/smp_affinity_list
 done

Filed under: oracle

Vote Peter Robson!

The UKOUG Council elections are in progress and I think User Groups are a critical part of the community. Peter Robson is both a friend and the type of person I admire and want to represent me within the UKOUG but, sadly, the grumpy old sod is possibly the least Web 2.0 chap I know so, concerned that his campaigning efforts might be hindered, I asked him if he'd like to make his pitch here. Over to you, Peter ....


Thanks to Doug for giving me this impromptu platform for a bit more shouting about my candidacy for the Council of the UK Oracle Users Group. It’s only right, as it was he who first persuaded me to stand for the Board (as it then was) all those years ago.

So if you are not a member of the UKOUG, I suppose you’d better stop reading now, unless I can persuade you to join up! My pitch here is to first of all, persuade you, as a member of UKOUG, to at least vote, and then secondly, hopefully convince you that I should be one of your five votes.

I have been actively involved in the UKOUG for about eight years now, both as a director of the original Board, and as an active member of the Scottish User Group committee. Now that I’m retired, I have the time to spend on these things, and my word, it can eat up one’s time, make no mistake! But it is so worthwhile. Time after time we have found that the one thing which members value are the opportunities to get together and talk about their work. Just ask Doug – he always turns up to the Birmingham Conference. Indeed, he is so obsessed with networking that he will even travel to San Francisco for Oracle OpenWorld! We can’t rival OOW, but our conferences, and SIGs, do provide the best example for the Oracle crowd to network here in the UK.

Personally, I have been able to bring lots of experience to input towards the organisation of these events by virtue of having presented in most of the European User Group conferences, as well as a few in North America, not to mention the Chris Date seminars that I also organise across the UK and Europe. Close to my heart is the Scottish community, which languished for many years, but is now thriving. Our annual conference is June 13 – Tom Kyte is presenting – make a note and come up to Scotland!

You can read the usual huff and puff about me on the election pages, but now I want to say something about the sort of person that you should look to elect to the Council. Most of all we need passionate, committed individuals, and not people who think they might try it for the benefit of their CV. We need people with the time to devote to the business of UKOUG, people who REALLY have the time to give. This is so important, as not only do we have a large user base to offer a service to, but we have a large office staff of salaried people who depend on us for their livelihood. No way are the office staff coasting – they are all stretched, and give of their very best. Indeed, it has been an absolute pleasure to work with them over the past years. We can only do our very best for them, which is why I stress that we need committed members of Council with sufficient time to devote to the job. With such people, and the skills and expertise of the office staff, I see no reason why UKOUG cannot thrive and grow.

Thanks for reading this stuff – did I ask you to vote for me? Yeah, go on, I would really appreciate that!

Peter Robson

Join Views, ROWIDs And Query Transformations

Here is an odd little bug that was discussed a couple of weeks ago on the OTN forums.

It's about queries on join views by ROWID that fail with "ORA-01410: invalid ROWID" under certain circumstances. The bug can only be reproduced when using the 11.2 code base. In fact the same setup will cause an internal error in 11.1, but 10.2 will return correct results.

It's probably not a very common scenario but it is an interesting example of how features that work fine by themselves can cause problems when used together.

First of all (hopefully) some of you may ask: How is it possible to query from a join view by ROWID, since the view is based on multiple objects and hence doesn't have a simple one-to-one mapping to a ROWID of a single table?

The answer is: It requires "Key-Preserved Tables". This is a term that is frequently used by Oracle in their documentation, in particular in the context of updatable join views. This means that you cannot query every join view by ROWID - it has to meet certain prerequisites otherwise Oracle will throw an error "ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table".

Obviously Oracle needs to be able to determine if there is at least one table in the view for which it is guaranteed that its rows can show up at most once in the result set of the view, which means that there need to be a minimum set of unique or primary key constraints at least if the view involves more than a single table. Furthermore Oracle needs to be able to determine this automatically, and depending on the complexity and expressions used, this is not always the case. See this post by Jonathan Lewis about a discussion of possible improvements in this regard. Other database vendors seem to allow at least to some degree more complex queries to be recognized as what Oracle calls "key-preserved".

Let's start with a simple example that creates a simple join view on three tables, furthermore appropriate constraints are created. The sample generates more constraints than strictly necessary to get a key-preserved table. The advantage of these additional constraints is that Oracle can perform a table or join elimination transformation when querying data only from a subset of the tables joined in the view.

drop table t1;

purge table t1;

drop table t2;

purge table t2;

drop table t3;

purge table t3;

drop view v;

drop view v_t1;

create table t1
as
select
rownum as id
, mod(rownum, 1000) + 1 as fk_id
, rpad('x', 100) as filler
from
dual
connect by
level <= 1000
;

exec dbms_stats.gather_table_stats(null, 't1', method_opt => 'for all columns size 1')

create table t2
as
select
rownum as id
, mod(rownum, 100) + 1 as fk_id
, rpad('x', 100) as filler
from
dual
connect by
level <= 1000
;

exec dbms_stats.gather_table_stats(null, 't2', method_opt => 'for all columns size 1')

create table t3
as
select
rownum as id
, mod(rownum, 10) + 1 as fk_id
, rpad('x', 100) as filler
from
dual
connect by
level <= 100
;

exec dbms_stats.gather_table_stats(null, 't3', method_opt => 'for all columns size 1')

alter table t1 add constraint t1_pk primary key (id);

alter table t2 add constraint t2_pk primary key (id);

alter table t3 add constraint t3_pk primary key (id);

alter table t1 add constraint t1_t2_fk foreign key (fk_id) references t2 (id);

alter table t2 add constraint t2_t3_fk foreign key (fk_id) references t3 (id);

alter table t1 modify fk_id not null;

alter table t2 modify fk_id not null;

alter table t3 modify fk_id not null;

create or replace view
v
as
select
t1.id as t1_id
, t1.fk_id as t1_fk_id
, t1.filler as t1_filler
, t2.id as t2_id
, t2.fk_id as t2_fk_id
, t2.filler as t2_filler
, t3.id as t3_id
, t3.fk_id as t3_fk_id
, t3.filler as t3_filler
from
t1
, t2
, t3
where
t1.fk_id = t2.id
and t2.fk_id = t3.id
;

So this is a simple join, and given the current setup the key-preserved table is T1.

We can verify this by this simple query:

SQL> select object_name from user_objects where object_id = (select sys.dbms_rowid.rowid_object(rowid) from v where rownum <= 1);

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
T1

So Oracle picks automatically T1 as source for the ROWID.

Let's see the join table elimination feature in action by using the following sample:

SQL> column rid new_value rid
SQL>
SQL> select rowid as rid from v where rownum <= 1;

RID
------------------
AAAVTGAAIAAAAEBAAA

SQL>
SQL> select t1_id from v where rowid = '&rid';
old 1: select t1_id from v where rowid = '&rid'
new 1: select t1_id from v where rowid = 'AAAVTGAAIAAAAEBAAA'

T1_ID
----------
1

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID 5stwqhguqgjr0, child number 0
-------------------------------------
select t1_id from v where rowid = 'AAAVTGAAIAAAAEBAAA'

Plan hash value: 487051824

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY USER ROWID| T1 | 1 | 20 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------

13 rows selected.

SQL>
SQL> column rid clear

Oracle 11.2 reduces this query to the simplest possible form - the tables T2 and T3 do not even show up in the execution plan. The validated and non-deferrable foreign key constraints ensure that Oracle can safely ignore the join to these tables given that only data from T1 is accessed.

So the Query Transformation seems to work pretty well and everything is fine so far.

Now let's modify the setup a little bit. As you might have noticed I've crafted the T2 and T1 tables deliberately in such a way that I can define a 1:1 relationship between them: The T1.FK_ID column is also unique.

So let's declare a unique constraint on T1.FK_ID to tell the database about this 1:1 relationship. Furthermore I'll define the simplest possible view on top of T1 that does nothing else than simply a SELECT * FROM T1:

alter table t1 add constraint t1_uq unique (fk_id);

create or replace view v_t1 as select * from t1;

create or replace view
v
as
select
t1.id as t1_id
, t1.fk_id as t1_fk_id
, t1.filler as t1_filler
, t2.id as t2_id
, t2.fk_id as t2_fk_id
, t2.filler as t2_filler
, t3.id as t3_id
, t3.fk_id as t3_fk_id
, t3.filler as t3_filler
from
v_t1 t1
, t2
, t3
where
t1.fk_id = t2.id
and t2.fk_id = t3.id
;

Finally I've changed the view V to refer to the view V_T1 instead of the base table.

Let's repeat the check about the source of the ROWID now:

SQL> select object_name from user_objects where object_id = (select sys.dbms_rowid.rowid_object(rowid) from v where rownum <= 1);

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
T2

Oracle now has selected T2 as the key-preserved table which is fine since T2 is now also guaranteed to be unique in the result set of the view.

Finally, let's repeat the query that I used to demonstrate the join elimination query transformation:

SQL> column rid clear
SQL> set echo on verify on
SQL>
SQL> column rid new_value rid
SQL>
SQL> select rowid as rid from v where rownum <= 1;

RID
------------------
AAAVTHAAIAAAAGBAAA

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID 8ns6u01cr94xa, child number 0
-------------------------------------
select rowid as rid from v where rownum <= 1

Plan hash value: 1420877628

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | 1 | 20 | 2 (0)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| T1_UQ | 1000 | 4000 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | T2_PK | 1 | 16 | 0 (0)| |
--------------------------------------------------------------------------------

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

1 - filter(ROWNUM<=1)
4 - access("FK_ID"="T2"."ID")

22 rows selected.

SQL>
SQL> select t1_id from v where rowid = '&rid';
old 1: select t1_id from v where rowid = '&rid'
new 1: select t1_id from v where rowid = 'AAAVTHAAIAAAAGBAAA'
select t1_id from v where rowid = 'AAAVTHAAIAAAAGBAAA'
*
ERROR at line 1:
ORA-01410: invalid ROWID

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID cgvynnw6sthrw, child number 0
-------------------------------------
select t1_id from v where rowid = 'AAAVTHAAIAAAAGBAAA'

Plan hash value: 487051824

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY USER ROWID| T1 | 1 | 20 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------

13 rows selected.

SQL>
SQL> column rid clear

Oops, that doesn't look too good: Oracle applied the ROWID to the wrong table respectively eliminated T2 from the execution plan although it uses T2 to obtain the ROWID. As you can see from the execution plan of the initial query that fetches the first ROWID from the view, T2 is not eliminated in that case.

So the moral of the story: Simple View Merging, another Query Transformation together with Join Elimination causes Oracle 11.2 to apply the ROWID to the wrong table in case multiple possible candidates for key-preserved tables exist. You can see this from the optimizer trace file where suddenly a T1.ROWID = '...' predicate pops up.

When replacing the view with the base table the problem cannot be reproduced. Preventing one of the transformations (Join Elimination or View Merging) also prevents the issue.

Interestingly changing the optimizer features to something below 11g also allowed avoiding the bug in the OTN thread, but with the given test case here I can still reproduce the problem on 11.2.0.3 (but not 11.2.0.1 and 11.2.0.2) when setting the OPTIMIZER_FEATURES_ENABLE to 10.2.0.4 for example:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter session set optimizer_features_enable = '10.2.0.4';

Session altered.

SQL> select object_name from user_objects where object_id = (select sys.dbms_rowid.rowid_object(rowid) from v where rownum <= 1);

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
T2

SQL>
SQL> set echo on verify on
SQL>
SQL> column rid new_value rid
SQL>
SQL> select rowid as rid from v where rownum <= 1;

RID
------------------
AAAVAUAAEAAAEnTAAA

SQL>
SQL> select t1_id from v where rowid = '&rid';
old 1: select t1_id from v where rowid = '&rid'
new 1: select t1_id from v where rowid = 'AAAVAUAAEAAAEnTAAA'
select t1_id from v where rowid = 'AAAVAUAAEAAAEnTAAA'
*
ERROR at line 1:
ORA-01410: invalid ROWID

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 72yy78z1ggn75, child number 1
-------------------------------------
select t1_id from v where rowid = 'AAAVAUAAEAAAEnTAAA'

Plan hash value: 396691268

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | NESTED LOOPS | | 1 | 27 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY USER ROWID| T1 | 1 | 20 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | T2_PK | 1 | | 0 (0)| |
-------------------------------------------------------------------------------------

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

3 - access("FK_ID"="T2"."ID")

20 rows selected.

SQL>
SQL> column rid clear

Although it can be seen that the lower optimizer features setting resulted in a different plan where T2 wasn't eliminated, the ROWID predicate was still applied to the wrong table, which is clearly a change in behaviour compared to previous releases, of course in particular 10.2.0.4. So this is again one of the cases where setting OPTIMIZER_FEATURES_ENABLE doesn't reproduce exactly the same plan.

So my preliminary analysis in the OTN thread wasn't entirely correct - the new Outer Join elimination Query Transformation introduced in 11g wasn't causing the problems in the original case - these additional transformations were just triggering the side effect: It needs both a join elimination and a view merging transformation. That is the reason why the test case includes a third table. When omitting the third table, or for example querying columns from the third table that prevent the elimination of T3 the error doesn't reproduce either.

Footnote: There are more variations of similar bugs. See for example "Bug 10129357: Assorted errors referencing ROWID from a view with subquery" where other kinds of query transformations result in invalid SQL throwing ROWID related errors.

Advert: Oracle XML Training With Marco Gralike

I was asked by Jože Senegačnik, if I would be would be interested in doing a Masterclass/Seminar in Slovenia and, yes of course, I really liked the idea. So after having a quick look in my agenda, regarding my free time, we started to set things up. This 2 day seminar will take place the

Read More…

Missing Filter

I see that Christian Antognini posted a note about an interesting little defect in Enterprise Manager a little while ago - it doesn’t always know how to interpret execution plans. The problem appears in Christians’ example when a filter subquery predicate is applied during an index range scan – it’s a topic I wrote about a few months ago with the title “filter bug” because the plan shows (or, rather, fails to show) a “missing” filter operation, which has been subsumed into the predicate section of the thing that would otherwise have been the first child of the filter operation – the rule of recursive descent through the plan breaks, and the ordering that OEM gives for the operations goes wrong.

Christian’s example shows the filter hiding itself when it should be applied to an index scan – as does my example from a few months ago – but the operation can also go missing when it is supposed to apply to a table access (whether a full scan, or an access by rowid), and the recursive descent algorithm that OEM is (probably) using to work out the execution order breaks in that case too.

The easiest way to construct an example of the anomaly is to write a query with a subquery which is blocked from unnesting (I’ve done this by using an outer join in the correlated predicate, but you could simply use a no_unnest hint), and then pushed (push_subq) to execute as early as possible in the plan:

select	/*+
                qb_name(main)
                leading(t1@main t2@subq1 t3@main)
                push_subq(@subq1)
        */
        t1.v1
From
        t1, t3
Where
        t1.n2 = 15
and     exists (
                select --+ qb_name(subq1)
                        null
                from    t2
                where   t2.n1(+) = 15
                and     t2.id(+) = t1.id
        )
and     t3.n1 = t1.n1
and     t3.n2 = 15
;

select * from table(dbms_xplan.display(null,null,'basic rows predicate'));

-------------------------------------------------------
| Id  | Operation                     | Name  | Rows  |
-------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     9 |
|*  1 |  HASH JOIN                    |       |     9 |
|*  2 |   TABLE ACCESS FULL           | T1    |     8 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2    |     1 |
|*  4 |     INDEX UNIQUE SCAN         | T2_PK |     1 |
|*  5 |   TABLE ACCESS FULL           | T3    |   157 |
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T3"."N1"="T1"."N1")
   2 - filter("T1"."N2"=15 AND  EXISTS (SELECT /*+ PUSH_SUBQ QB_NAME
              ("SUBQ1") */ 0 FROM "T2" "T2" WHERE "T2"."ID"(+)=:B1 AND
              "T2"."N1"(+)=15))
   3 - filter("T2"."N1"(+)=15)
   4 - access("T2"."ID"(+)=:B1)
   5 - filter("T3"."N2"=15)

As you can see, there’s a filter predicate at line 2, but this doesn’t come from a filter operation; it’s simply a filter subquery applied to the tablescan. To make it easier to read complex cases I sometimes take a text editor to a plan like this and put back the missing filter operation – which means inserting one line where the filter predicate appears and moving the filtered operation (in this case the full tablescan) and any descendent(s) thereof one step to the right, as follows:

-------------------------------------------------------
| Id  | Operation                     | Name  | Rows  |
-------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     9 |
|*  1 |  HASH JOIN                    |       |     9 |
|*  2a|   FILTER                      |       |     9 |
|*  2b|    TABLE ACCESS FULL          | T1    |     8 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2    |     1 |
|*  4 |     INDEX UNIQUE SCAN         | T2_PK |     1 |
|*  5 |   TABLE ACCESS FULL           | T3    |   157 |
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T3"."N1"="T1"."N1")
   2a- filter("T1"."N2"=15 AND  EXISTS (SELECT /*+ PUSH_SUBQ QB_NAME
              ("SUBQ1") */ 0 FROM "T2" "T2" WHERE "T2"."ID"(+)=:B1 AND
              "T2"."N1"(+)=15))
   3 - filter("T2"."N1"(+)=15)
   4 - access("T2"."ID"(+)=:B1)
   5 - filter("T3"."N2"=15)

If I don’t push the subquery (i.e. allow Oracle to execute late) then the plan changes to the following – showing you the filter operation that you might normally expect on a filter subquery:

------------------------------------------------------
| Id  | Operation                    | Name  | Rows  |
------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |
|*  1 |  FILTER                      |       |       |
|*  2 |   HASH JOIN                  |       |   173 |
|*  3 |    TABLE ACCESS FULL         | T1    |   157 |
|*  4 |    TABLE ACCESS FULL         | T3    |   157 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| T2    |     1 |
|*  6 |    INDEX UNIQUE SCAN         | T2_PK |     1 |
------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT /*+ QB_NAME ("SUBQ1") */ 0 FROM "T2" "T2"
              WHERE "T2"."ID"(+)=:B1 AND "T2"."N1"(+)=15))
   2 - access("T3"."N1"="T1"."N1")
   3 - filter("T1"."N2"=15)
   4 - filter("T3"."N2"=15)
   5 - filter("T2"."N1"(+)=15)
   6 - access("T2"."ID"(+)=:B1)

Here’s an image captured from the OEM screen (11g) for the plan that actually appeared. The run-time action starts with the full tablescan of t1 at the third line of the plan, and for each row we acquire from t1 we run the subquery at the fourth and fifth lines (fifth first, then fourth) – but as you can see, OEM thinks the action starts with a unique scan of index t2_pk at the fifth line of the plan.

Moral: whenever you know there’s a filter subquery in the plan (and you’ll see it in the predicate section) make sure you think carefully about when it runs.

[Pythian Ads Network] Mastering Oracle Trace Data Online Class Reunion

This is a quick announcement that Method-R is organizing the online class reunion for the participants of their Mastering Oracle Trace Data classes. Cary Millsap and Ron Crisco will entertain us with stories and useful tips around processing and analyzing Oracle 10046 traces. Having Method-R done special training for Pythian about a year ago, I [...]