Installing Oracle 11gR1 on a Fedora Core 10 64 bit VM

Just a note, to myself more than anything, about what extra packages are required by a 64 bit installation of Fedora Core 10, when trying to install Oracle 11gR1.

The installation I undertook was on a FC10 64 bit VM running under VMWare Server 2.0 running on top of FC10 64 bit OS.

Tim, as usual, has a lovely guide which told me almost everything I needed to know, however the guide says “If you are performing the 64-bit installation, make sure both the 32-bit and 64-bit libraries are installed.” rather than explicitly stating the packages for a 64 bit install. Until I tried to install Oracle 11gR1, I didn’t know what these were. The Oracle installer for 11g soon told me in the pre install checks it does, so I went about installing the following packages, in order:

glibc-2.9-3.i686.rpm
libaio-0.3.107-4.fc10.i386.rpm
libgcc-4.3.2-7.i386.rpm
glibc-devel-2.9-3.i386.rpm
compat-libstdc++-33-3.2.3-64.x86_64.rpm
compat-libstdc++-33-3.2.3-64.i386.rpm
libstdc++-4.3.2-7.i386.rpm

That got me past the pre install checks of the Oracle installer and on to a successful install.

I’ve added the list to the comments on the guide Tim produced as well.

11g IO Calibration tool

After reading how inadequate Doug was feeling over his IO subsystem, I decided to see how quick mine was…not that we’re getting into a “mine is better than yours” game, but rather to see how mine stacks up against Doug’s, bearing in mind his is a 5 disk stripe natively attached to his machine (I’m assuming) and mine is a logical disk attached to a VMWare machine…although admittedly, the PC underneath this logical disk is running, motherboard based, RAID striping, over two physical SATA disks…I just figured it would be interesting to compare.

Obviously, any experiment that goes flawlessly according to a preconceived plan is:

1. Boring
2. Less educational
3. Not normally one I’ve done – mine always have problems it seems!

I ran the calibration on my VMWare based OpenSuse 10 linux with Oracle 11g and it immediately came up with a problem:

SQL> @io
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2    lat  INTEGER;
3    iops INTEGER;
4    mbps INTEGER;
5  BEGIN
6  -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (, , iops, mbps, lat);
7     DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
8
9     DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
10     DBMS_OUTPUT.PUT_LINE ('latency  = ' || lat);
11     dbms_output.put_line('max_mbps = ' || mbps);
12  end;
13  /
DECLARE
*
ERROR at line 1:
ORA-56708: Could not find any datafiles with asynchronous i/o capability
ORA-06512: at "SYS.DBMS_RMIN", line 453
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 1153
ORA-06512: at line 7

Of course, consulting the manual led me to run this query:

SELECT name, asynch_io
FROM v$datafile f,v$iostat_file i
WHERE f.file#        = i.file_no
AND   filetype_name  = 'Data File'
/

which gave:

SQL> /

NAME                                               ASYNCH_IO
-------------------------------------------------- ---------
/home/oracle/oradata/su11/system01.dbf             ASYNC_OFF
/home/oracle/oradata/su11/sysaux01.dbf             ASYNC_OFF
/home/oracle/oradata/su11/undotbs01.dbf            ASYNC_OFF
/home/oracle/oradata/su11/users01.dbf              ASYNC_OFF
/home/oracle/oradata/su11/example01.dbf            ASYNC_OFF

…or in other words no asynchronous IO available – as the error message had said.

After altering the filesystemio_options parameter to “set_all” and bouncing the instance, a second run of the calibration process seemed to work fine…

SQL> @io
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2    lat  INTEGER;
3    iops INTEGER;
4    mbps INTEGER;
5  BEGIN
6  -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (, , iops, mbps, lat);
7     DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
8
9     DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
10     DBMS_OUTPUT.PUT_LINE ('latency  = ' || lat);
11     dbms_output.put_line('max_mbps = ' || mbps);
12  end;
13  /
max_iops = 72
latency  = 13
max_mbps = 26

PL/SQL procedure successfully completed.

So,my figures are considerably lower than those Doug achieved:

max_iops = 112
latency  = 8
max_mbps = 32

…but not too bad I guess considering the fact that mine is a VM and the hardware I’m running is more humble…no seriously, size does not matter!

ORA-07455 and EXPLAIN PLAN…and statements which, perhaps, shouldn’t run

I encountered a scenario today which I thought was strange in a number of ways…hence, irresistible to a quick blog post.

The scenario started with an end user of my warehouse emailing me a query that was returning an error message dialog box, warning the user before they ran the query, that they had insufficient resources to run said query – ORA-07455 to be precise.

I figured, either the query is one requiring significant resources – more resources than the user has access to, or the query has a suboptimal plan, whereby it thinks it will require more resources than they have access to.

To try and determine which, I logged into the same Oracle user as my end user and tried to get an explain plan of the query – so I could perhaps gauge whether there were any problems with the choice of execution path and whether the query was one which would indeed require significant resources.

The result was that it came back with the same error – which quite surprised me at first.

In using EXPLAIN PLAN, I wasn’t asking the database to actually run the query – merely to tell me what the likely execution path was for the query and yet, it appears to still do the checks on resource usage. At first, that seemed strange to me, in the sense that I wouldn’t be requiring those resources since, I’m not actually executing the statement, yet perhaps it does makes sense – or at least is consistent, because, for example, you don’t need access to all the objects in the query if you’re not going to actually execute it, yet quite rightly, the optimizer does checks as to whether you have the appropriate access permissions to each object as part of the EXPLAIN PLAN process.

That was educational point number one for me.

After logging in as another user with unlimited resource usage, I then reran the EXPLAIN PLAN and the statement was accepted and the plan returned…indicating an unpleasant rewrite of the query, and a very high anticipated cost – in excess of the limit for that end user.

That explained why the ORA-07455 was appearing for them, but highlighted an altogether different issue which perplexed me further. There follows a simple reconstruction of the query and explain plan results:

First the obligatory test script…

 

SET TIMING OFF
DROP TABLE tab1 PURGE
/
CREATE TABLE tab1
(col1 VARCHAR2(1))
/
DROP TABLE tab2 PURGE
/
CREATE TABLE tab2
(col2 VARCHAR2(1))
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER
                            ,tabname => 'TAB1'
                            );
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER
                            ,tabname => 'TAB2'
                            );
END;
/
INSERT INTO tab1 VALUES('A')
/
INSERT INTO tab1 VALUES('B')
/
INSERT INTO tab1 VALUES('A')
/
INSERT INTO tab1 VALUES('B')
/
INSERT INTO tab2 VALUES('C')
/
INSERT INTO tab2 VALUES('D')
/
COMMIT
/
SET AUTOTRACE ON
SELECT *
FROM tab1
WHERE col1 IN (SELECT col1 FROM tab2)
/
SET AUTOTRACE OFF

 

Now the results…

 

Table dropped.

Connected.

Table dropped.


Table created.


Table dropped.


Table created.


PL/SQL procedure successfully completed.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.


C
-
A
B
A
B

4 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4220095845

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     2 |     4   (0)|00:00:01  |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL | TAB1 |     1 |     2 |     2   (0)|00:00:01  |
|*  3 |   FILTER            |      |       |       |            |          |
|   4 |    TABLE ACCESS FULL| TAB2 |     1 |       |     2   (0)|00:00:01  |
----------------------------------------------------------------------------

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

1 - filter( EXISTS (SELECT /*+ */ 0 FROM "TAB2" "TAB2" WHERE
           :B1=:B2))
3 - filter(:B1=:B2)


Statistics
----------------------------------------------------------
       1  recursive calls
       0  db block gets
      18  consistent gets
       0  physical reads
       0  redo size
     458  bytes sent via SQL*Net to client
     381  bytes received via SQL*Net from client
       2  SQL*Net roundtrips to/from client
       0  sorts (memory)
       0  sorts (disk)
       4  rows processed

 

Now, when I first saw the query I thought, hang on a minute, COL1 does not exist in table TAB2 so this query should not even execute…but it does! I don’t think it should execute personally but according to the documentation, “Oracle resolves unqualified columns in the subquery by looking in the tables named in the subquery and then in the tables named in the parent statement.“, so it is operating as described in the manuals- even if, in my view, it’s a little odd since without a rewrite, the query is incapable of executing.

The query has been rewritten with an EXISTS approach – note the first FILTER statement in the “Predicate Information” section of the autotrace output. A bit like this:

 

SELECT a.*
FROM   tab1 a
WHERE EXISTS (SELECT 0
           FROM   tab2 b
           WHERE  a.col1 = a.col1
          )
/

 

The subquery is always going to return a row, hence, for any row we select in the containing query, we will always get that row back, because the EXISTS will always find a match – it’s a bit like saying “WHERE TRUE” I guess.

Interestingly, my friend Jon first brought this scenario to my attention last week in various discussions with him and another of my colleagues, who is far more experienced than myself. To be fair, the experienced colleague is the source of a number of my blogging posts, but he’s painfully shy and will therefore remain nameless.

I was educated during that discussion, that this functionality is as advertised in the manuals – even if it doesn’t sit well with me. My closing line to my fellow debaters at the time, was that nobody would ever write SQL like that and if they did I’d tell them to rewrite it using aliases and so that it made sense – as is often the case in life though, the very next week, a real life user comes up with exactly that scenario – at least I was prepared!

Changing the subpartition template and pre-existing partitions

My colleague Tank could not insert some records into a subpartitioned table the other day as he kept getting an ORA-14400 error and couldn’t work out why – nor could I straight away until he mentioned the possibility that the subpartition template had changed on the table…which it had.

Lets try and work through the problem…first lets create some test tables…

DROP TABLE j4134_test1 PURGE
/
CREATE TABLE j4134_test1
(
col1  NUMBER        NOT NULL
,col2  NUMBER        NOT NULL
,col3  VARCHAR2(254) NOT NULL
)
PARTITION BY RANGE (col1)
SUBPARTITION BY LIST(col2)
SUBPARTITION TEMPLATE
(SUBPARTITION s1 VALUES (1),
 SUBPARTITION s2 VALUES (2),
 SUBPARTITION s3 VALUES (3)
)
(
PARTITION p1 VALUES LESS THAN (1000)
,PARTITION p2 VALUES LESS THAN (2000)
,PARTITION p3 VALUES LESS THAN (3000)
,PARTITION p4 VALUES LESS THAN (4000)
)
/
ALTER TABLE j4134_test1 SET SUBPARTITION TEMPLATE
(SUBPARTITION s1 VALUES (1),
 SUBPARTITION s2 VALUES (2)
)
/
ALTER TABLE j4134_test1 ADD PARTITION p5 VALUES LESS THAN(5000)
/
DROP TABLE j4134_test2 PURGE
/
CREATE TABLE j4134_test2 (
col1  NUMBER        NOT NULL
,col2  NUMBER        NOT NULL
,col3  VARCHAR2(254) NOT NULL
)
PARTITION BY LIST (col2)
(
PARTITION s1 VALUES (1)
,PARTITION s2 VALUES (2)
,PARTITION s3 VALUES (3)
)
/

OK, that’s everything set up…now lets just have a look at what partitions and subpartitions we have:

ae_aml[522/313]@AED52> SELECT partition_name
2  ,      subpartition_name
3  FROM   user_tab_subpartitions
4  WHERE  table_name='J4134_TEST1'
5  /

Partition            Sub Part
Name                 Name
-------------------- ------------
P1                   P1_S1
P1                   P1_S2
P1                   P1_S3
P2                   P2_S1
P2                   P2_S2
P2                   P2_S3
P3                   P3_S1
P3                   P3_S2
P3                   P3_S3
P4                   P4_S1
P4                   P4_S2
P4                   P4_S3
P5                   P5_S1
P5                   P5_S2

14 rows selected.

Notice that Partition P5 has only two subpartitions whilst the other partitions all have three subpartitions.

Now lets do a couple of tests…

First lets try and replicate the original problem…

ae_aml[522/313]@AED52> SET ECHO ON
ae_aml[522/313]@AED52> INSERT INTO j4134_test1(col1,col2,col3)
2  VALUES(3500,3,'TEST')
3  /

1 row created.

This worked – because the subpartition template used for the Partition P4 where the COL1 value 3500 would be inserted, included a subpartition for COL2 with values of 3 – no problem.

Lets try again…

ae_aml[522/313]@AED52> INSERT INTO j4134_test1(col1,col2,col3)
2  VALUES(4500,3,'TEST')
3  /
INSERT INTO j4134_test1(col1,col2,col3)
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

Aha – now it fails…because the COL1 value of 4500 would result in Partition P5 being used – but this was created after the subpartition template was changed to only have subpartitions for values 1 and 2 – the value of 3 for COL2 does not have a “home” to go to, so the statement fails.

This is interesting because it means that Oracle allows us to have Partitions setup with different subpartition layouts under them depending on what the template was at the time of partition creation.

When I first thought about this I figured it wasn’t possible/correct to do this and that the subpartitions had to be uniform across the partitions, but when you think about that, it would make life difficult to change the template – what should Oracle do with the “missing” or “extra” or “modified”
subpartitions in the pre-existing partitions? Create/drop/modify them? Ignore them? If it tried to create missing ones there would be all sorts of questions like which tablespace to use and what storage characteristics to set.

As the documentation says, it leaves what exists already as is and just uses the new template for partitions created in the future…which brings me to another point…that piece of code I put up on this post needed updating because I didn’t know about this subpartition template issue…now the code will check to ensure that if we are exchanging a partitioned table for a partition of a composite partitioned table that we need to ensure the source table is partitioned in the same way
(LIST/HASH) as the subpartitioning on the target partition and that there are the same number of partitions in the source table and the target table partition – if not the process fails…like this:

ae_aml[522/313]@AED52> ALTER TABLE j4134_test1 EXCHANGE PARTITION p1 WITH TABLE j4134_test2
2  /

Table altered.

ae_aml[522/313]@AED52> REM Now put it back...
ae_aml[522/313]@AED52> ALTER TABLE j4134_test1 EXCHANGE PARTITION p1 WITH TABLE j4134_test2
2  /

Table altered.

ae_aml[522/313]@AED52> REM now try with the mismatched one - fails!
ae_aml[522/313]@AED52> ALTER TABLE j4134_test1 EXCHANGE PARTITION p5 WITH TABLE j4134_test2
2  /
ALTER TABLE j4134_test1 EXCHANGE PARTITION p5 WITH TABLE j4134_test2
                                                       * ERROR at line 1:
ORA-14294: Number of partitions does not match number of subpartitions

I’ve updated the code to check for this scenario and it now reports it.

Oracle 10gR1 on HP-UX – tracefile errors and DBMS_SCHEDULER

A couple of interesting Oracle 10gR1 on HP-UX issues I’ve come across in the past couple of days – ably assisted by a few of the Eon (Powergen) Team members (Phil Bridges, Tahir Mahmood and Zaheer Mahmood):

Firstly, we were seeing a line in the trace files on our databases which looked like:

Ioctl ASYNC_CONFIG error, errno = 1

This turns out to be a configuration issue on HP-UX whereby the OSDBA group (dba) must have the MLOCK privilege…which ours didn’t.

After following these instructions we managed to fix this one although it’s interesting to note that the reason we got onto this was by looking at the 9iR2 documentation (after a search hit on google) and when we looked at the 10gR1 docs it’s not mentioned as a potential issue…but the fix works nonetheless.

Next came an issue with running jobs via the DBMS_SCHEDULER package which seemed not to be working for a simple test case.

We kept getting this error:

ORA-27370: job slave failed to launch a job of type EXECUTABLE
ORA-27300: OS system dependent operation:accessing execution agent failed with status: 2
ORA-27301: OS failure message: No such file or directory
ORA-27302: failure occurred at: sjsec 3

A little research and support from Tahir led to this article which after following the instructions allowed us to get past this and then a few more permissions problems. Now, I can schedule stuff to run on the box during the evening nice and easily (i.e. without worrying about the fact that I don’t have access to cron or any other OS scheduling facility)…here come the benchmark runs!
Speaking of benchmarks, I’ve been a little quiet on the blog recently as I’ve been running some parallel execution tests in light of Doug Burns’ recent presentation at Hotsos here. It’s something we’ve been wondering about on our data warehouse – the appropriate Degree Of Parallelism to use – this presentation was a kick start into thinking about it some more and indeed running some tests courtesy of the scripts and paper Doug has put together. I’m finding some similarities with Doug but also some differences which he and I are still email ping ponging over…I’m sure there is another paper in there Doug…