Singapore: sending out an SOS

The Oracle community rocks! I mentioned previously that the beginning of the OTN APAC tour will take me to Singapore to meet Doug Burns again. Well, Doug took the opportunity to organize a little meetup of the local Singapore Oracle User community for Free Pizza, Free Beer and Free Oracle Presentations next monday, November 3rd. If you are in Singapore or can get there with reasonable effort, I would love to meet you there. See the flyer and rsvp to the email address if you want to come as seats are limited.
The speaker lineup sounds like a european takeover. You will have to listen to me talk about SQL Plan management first only to be rewarded with presentations by Doug Burns (Scotland) on Real Time SQL Monitoring and Morten Egan (Denmark) on Big Data.

on the road again: OTN APAC tour and rest of 2014

I should be packing right now. But as much as I love travelling, I yet have to grow fond of packing. So instead I am updating my blog which I have neglected a bit over the past months. But excuses are plenty and I have been quite busy and it won’t get any better anytime soon. But this is a good kind of busy since I get to present at a bunch of events and meet a lot of good friends around the world. I was lucky enough to be accepted on the OTN APAC tour again. Last year, I was “only” able to attend two stops in New Zealand and Australia but this year I will manage to visit China (twice) and Japan in addition to Australia. The trip starts this week with a client visit after which I will fly straight to Singapore for a long weekend to meet up with Doug Burns. After that these will be the tour stops:

After Beijing, I am leaving directly to Nuremberg for the annual DOAG conference and one more customer meeting the day after before finally heading home for a weekend. But that will not be the last trip of the year, the Oracle Midlands user group is having a “german night” with two of my talks and a stroll over the German christmas market in Birmingham on November 25th. And then finally the last event of the year will be the UKOUG tech14 conference in Liverpool from December 8th to 10th where I will get to co-present with Philippe Fierens.

Spreading Out Data With MINIMIZE RECORDS_PER_BLOCK

This post is part of a series on why sometimes we don’t need to pack data in, we need to spread it out. In the previous post I discussed some old-school methods to achieve this. This post talks about a technique that has been available for many Oracle versions so is also pretty old school but I thought it was worth a post all to itself. The technique uses the ALTER TABLE clause “MINIMIZE RECORDS_PER_BLOCK”.

Documentation

MINIMIZE RECORDS_PER_BLOCK

I have included a link to Oracle documentation above but one of the first things to highlight is this feature is light on official documentation. In fact the above page and a brief reference in the SQL Language Quick Reference are just about all there is. Here is a quote from the above link, the emphasis is mine.

“The records_per_block_clause lets you specify whether Oracle Database restricts the number of records that can be stored in a block. This clause ensures that any bitmap indexes subsequently created on the table will be as compressed as possible.”

There is no example in the official documentation but there is a very nice example on Richard Foote’s blog titled “Bitmap Indexes & Minimize Records_Per_Block (Little Wonder)”, and he works for Oracle so perhaps it counts as official documentation!

In the documentation above we have a mention of “restricts the number of records that can be stored in a block” but no clear guidance that it can be used for the reason we desire, to spread out data. If I can’t find what I want in the Oracle documentation my next port of call is My Oracle Support and there is a note describing how we might use this clause for our purpose.

Oracle Performance Diagnostic Guide (OPDG) [ID 390374.1]

In essence the note describes a “hot block” issue and a solution which is to spread data out. Suggested methods are to utilise PCTFREE or the table option, MINIMIZE RECORDS_PER_BLOCK. The note also goes through steps to highlight how to achieve our goal. Which leads us on to the next section.

How To [Mis]use MINIMIZE RECORDS_PER_BLOCK To Spread Out Data

The basic set of steps when using this clause to spread out data is:

  1. Temporarily remove data from the table, typically with CTAS or Datapump/Export followed by truncate or delete
  2. Insert the desired number of dummy records any data block should hold
  3. Restrict the number of records that can be stored in any block to the maximum number currently held
  4. Delete the dummy rows
  5. Reinstate the application data

Here is an example.

In the first post in the series I introduced the PROCSTATE table with all rows of data in a single block.

select dbms_rowid.rowid_block_number(rowid) blockno
,      count(*)
from procstate
group by dbms_rowid.rowid_block_number(rowid);
 
   BLOCKNO   COUNT(*)
---------- ----------
    239004         12

First let’s copy the data elsewhere and truncate the table.

SQL> create table procstate_store
  2  as select * from procstate;

Table created.

SQL> truncate table procstate;

Table truncated.

Next we insert a dummy row and restrict the number of rows any block will hold.

SQL> insert into procstate
  2  select * from procstate_store
  3  where rownum = 1;

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from procstate;

  COUNT(*)
----------
         1

SQL> alter table procstate minimize records_per_block;

Table altered.

Finally we delete the dummy row and re-insert the original data.

SQL> truncate table procstate;

Table truncated.

SQL> insert into procstate
  2  select * from procstate_store;

12 rows created.

SQL> commit;

Commit complete.

And we should have only one row per block right? Wrong!

SQL> select dbms_rowid.ROWID_BLOCK_NUMBER(rowid) blockno, count(*)
  2  from procstate group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid);

   BLOCKNO   COUNT(*)
---------- ----------
    278668          2
    278669          2
    278670          2
    278667          2
    278688          2
    278671          2

6 rows selected.

Two rows in each block… but that’s still a good result and using the same test as in the previous post I can see a huge reduction in contention when running my primitive test case. Original results on the left and new results with two rows per block on the right.

Contention  11g normal heap table chart-11g-mrpb

Under The Covers

In this section we’ll dig a bit deeper in to how this works and perhaps get some insight into why the example above resulted in two rows per block.

When MINIMIZE RECORDS_PER_BLOCK is used it manipulates a property in SYS.TAB$ in the SPARE1 column. This property is known as the Hakan Factor (no I don’t know why either but I do notice there is a Mr Hakan Jacobsson listed as an author of the Performance Tuning Guide and Data Warehousing Guide… who knows). Below is a query showing the Hakan Factor for a simple table stored in a tablespace using 8KB blocks.

select spare1 from sys.tab$ where obj# = 18013;

    SPARE1
----------
       736

The Hakan Factor is set by default for all heap tables, or more correctly, for all table segments of heap tables. Below is a table showing how it changes as block size changes. It makes sense that larger blocks can hold more rows.

BLOCK_SIZE  SPARE1
----------  ------
       4KB     364
       8KB     736
      16KB    1481
      32KB    2971

After a minimize operation with only a single row in a table it would be reasonable to expect SPARE1 to be set to “1”. So let’s check the value stored after a MINIMIZE operation on the PROCSTATE table.

    SPARE1
----------
     32769

This is because the MINIMIZE operation sets a flag in the 16th bit of the Hakan factor. We can see this using the BITAND SQL function in a query like the one below. This query uses BITAND to check if a specific bit is set in a number. So the increasing powers of 2 have been passed in. I have then used the LEAST() or GREATEST() functions to convert the result to a “1” or “0”.

select spare1
,      least(1,BITAND(spare1,32768)) c32k
,      least(1,BITAND(spare1,16384)) c16k
,      least(1,BITAND(spare1,8192)) c8k
,      least(1,BITAND(spare1,4096)) c4k
,      least(1,BITAND(spare1,2048)) c2k
,      least(1,BITAND(spare1,1024)) c1k
,      least(1,BITAND(spare1,512)) c512
,      least(1,BITAND(spare1,256)) c256
,      least(1,BITAND(spare1,128)) c128
,      least(1,BITAND(spare1,64)) c64
,      least(1,BITAND(spare1,32)) c32
,      least(1,BITAND(spare1,16)) c16
,      least(1,BITAND(spare1,8)) c8
,      least(1,BITAND(spare1,4)) c4
,      least(1,BITAND(spare1,2)) c2
,      greatest(0,BITAND(spare1,1)) c1
from sys.tab$
where obj# = (select obj# from sys.obj$
              where name = 'PROCSTATE');

-- After MINIMIZE with 1 row in PROCSTATE                                 ( decimal 1 )
SPARE1 C32K C16K  C8K  C4K  C2K  C1K C512 C256 C128  C64  C32  C16   C8   C4   C2   C1
------ ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
 32769    1    0    0    0    0    0    0    0    0    0    0    0    0    0    0    1

-- After MINIMIZE with 2 rows in PROCSTATE                                ( decimal 1 )
 32769    1    0    0    0    0    0    0    0    0    0    0    0    0    0    0    1

-- After MINIMIZE with 3 rows in PROCSTATE                                ( decimal 2 )
 32770    1    0    0    0    0    0    0    0    0    0    0    0    0    0    1    0

-- After MINIMIZE with 4 rows in PROCSTATE                                ( decimal 3 )
 32771    1    0    0    0    0    0    0    0    0    0    0    0    0    0    1    1

-- After MINIMIZE with 5 rows in PROCSTATE                                ( decimal 4 )
 32771    1    0    0    0    0    0    0    0    0    0    0    0    0    1    0    0

-- etc etc

Notice the 32K flag is set for all numbers and the lower bits specify the row limit. There is an anomaly for a single row and then the values lag one behind the desired row limit. For example with three rows in the table the Hakan factor has the MINIMIZE flag in the 16th bit and binary “10” (decimal 2) stored. So with a default Hakan factor of 736 the limit is actually set at 737 rows. I can only think that the anomaly where 1 row and 2 rows have the same value stored are down to this feature being in place to optimise bitmap indexes and the difference between one and two rows is irrelevant. Or perhaps storing a zero was ugly and a compromise was made, we’ll never know.

It is worth noting at this point that SPARE1 is a multi use column and a COMPRESS operation will set a flag in the 18th bit. I have not seen a case where the 13th – 15th bits are used. Perhaps they are left for future proofing MINIMIZE from block sizes above 32KB (should Oracle ever decide to implement them). Anyway, back on track…

When “MINIMIZE RECORDS_PER_BLOCK” is executed the Oracle process full scans the table and uses the undocumented SYS_OP_RPB() function to retrieve the row number of every row within its block. e.g.

select max(sys_op_rpb(rowid)) from procstate;

The SYS_OP_RPB() function returns the same result as the documented DBMS_ROWID.ROWID_ROW_NUMBER() function. Output from a table containing four records is below:

select rownum
, dbms_rowid.rowid_relative_fno(rowid) file_no
, dbms_rowid.rowid_block_number(rowid) blk_no
, dbms_rowid.rowid_row_number(rowid) row_no
, sys_op_rpb(rowid) row_rno2
from haktest;

    ROWNUM    FILE_NO     BLK_NO     ROW_NO   ROW_RNO2
---------- ---------- ---------- ---------- ----------
         1         11      18676          0          0
         2         11      18676          1          1
         3         11      18676          2          2
         4         11      18676          3          3

Notice the numbering starts at 0, this explains why the value stored in TAB$.SPARE1 is one less to our eyes than the actual limit. This is true of a block dump too, the numbering of rows in a data block starts at 0.

Finally, to return a table to its default setting the ALTER TABLE NOMINIMIZE clause is used.

Summary

In summary the MINIMIZE clause is not very well documented and definitely not very well exposed in the data dictionary. It is intended for use with bitmap indexes but can be also be used to spread data out in a similar way to PCTFREE but by managing the number of rows rather than amount of free space. I have used MINIMIZE in the past but now that I am older and less excitable I would probably stick to other methods of achieving my goals (e.g. PCTFREE or partitioning).

In the next post in the series we’ll look at some newer ways we can spread out data.

Spreading Out Data – Old School Methods

In the previous post in this series we introduced a crude test case in which we had data packed in to a single block which was causing severe buffer contention. To re-cap – here is a chart of ASH data demonstrating the issue, the large grey stripe is time wasted on global cache contention.

Contention  11g normal heap table

This post is to cover some “old school” methods that can be used to spread out the data and hopefully reduce contention. I have purposefully ignored the possibility of a design flaw in the code, for the purposes of this series we are assuming the code is in good shape.

Node Affinity

Nothing to do with spreading data out but an obvious remedy for global cache contention by managing where the workload runs. I’m not going to discuss this too much as it doesn’t protect us from “buffer busy waits” within a single instance, however in some cases node affinity would be the correct solution. I have included a chart from the same test case as before but usilising only a single RAC node. You can see that the global cache contention (grey) has gone but we do still have a little “buffer busy wait” contention (red).

Contention 11g nodeaffinity

PCTFREE

Oracle documentation: PCTFREE

This option doesn’t really need any explanation but here we create the test table with the clause “PCTFREE 99″ and reload the data.

create table procstate
(    
...
) pctfree 99;

And the data is now spread out.

select dbms_rowid.rowid_block_number(rowid) blockno
, count(*)
from procstate
group by dbms_rowid.rowid_block_number(rowid);

   BLOCKNO   COUNT(*)
---------- ----------
    239021          2
    239040          2
    239022          2
    239020          2
    239019          2
    239023          2

The test case row size is quite small, approximately 0.5% of the free space in a block, so we are left with two rows in each block. Looking at ASH data from the test case shows that contention is much reduced, a nice solution.

Contention 11g pctfree

Single Table Hash Cluster

Oracle documentation: Single Table Hash Cluster

This option didn’t occur to me initially. I tend to avoid Hash Clusters but when I presented on this topic at UKOUG Tech13 a leading light in the Oracle Community suggested this would also be a good solution. So here it is.

The test table has been created in a Hash Cluster stating the size of a cluster of records as 8000 bytes. This is a cheat to ensure each cluster of records ends up in its own block. We are clustering on the primary key column so each block will contain only a single row.

create cluster procstate_cluster
(proc_id number(3)) 
size 8000 single table hashkeys 100;

create table procstate
(
...
,	
)
cluster procstate_cluster(proc_id);

Before loading the data we already have 110 empty blocks. This is because we have configured the cluster to have 100 hash keys and, as stated above, ensured each key maps to a different block. Oracle then rounds up the number of cluster keys to the next prime number (109) and the extra block is the table header.

    BLOCKS
----------
       110

After loading the test data we see there is only a single row in each block.

   BLOCKNO   COUNT(*)
---------- ----------
    172398          1
    172361          1
    239067          1
    172402          1
    172365          1
    239079          1
    172389          1
    172352          1
    239075          1
    172356          1
    239071          1
    172394          1

Running the test case again shows the contention has vanished completely. All sessions spend their time on CPU doing useful work.

Contention 11g Hash Cluster

Another nice if somewhat obscure solution.

Minimize Records Per Block

Speaking of obscure solutions we come to the “MINMIZE RECORDS_PER_BLOCK” clause, we’ll save that for the next installment as I have quite a lot of material to cover.

Securing Oracle DB Accounts With Default Passwords

One view I didn’t know about until recently is DBA_USERS_WITH_DEFPWD. This view appeared in 11g but it obviously passed me by. The reason it cropped up recently was a requirement to ensure that the default accounts in an Oracle database were not left with default passwords, regardless of their account status. In order to achieve this I knocked up a quick snippet of PL/SQL which could be added to automation scripts and therefore tick another box on the audit checklist. The code specifically doesn’t output the passwords to avoid leaving them in log files. I thought it was worth sharing here.

set serveroutput on
begin
  for i in (	select 'alter user '||u.username||' identified by '
                     ||dbms_random.string('a', 10)||'_'||trunc(dbms_random.value(1,99)) cmd
                     , username
                from sys.dba_users_with_defpwd u
                where username <> 'XS$NULL')
  loop
    dbms_output.put_line('Securing '||i.username||'...');
    execute immediate i.cmd;
  end loop;
end;
/

And the output

SQL> set serveroutput on
SQL> begin
  2    for i in (    select 'alter user '||u.username||' identified by '
  3                       ||dbms_random.string('a', 10)||'_'||trunc(dbms_random.value(1,99)) cmd
  4                       , username
  5                  from sys.dba_users_with_defpwd u
  6                  where username <> 'XS$NULL')
  7    loop
  8      dbms_output.put_line('Securing '||i.username||'...');
  9      execute immediate i.cmd;
 10    end loop;
 11  end;
 12  /
Securing GSMUSER...
Securing MDSYS...
Securing OLAPSYS...
Securing LBACSYS...
Securing ORDDATA...
Securing ORDSYS...
Securing DVF...
Securing SYSDG...
Securing APPQOSSYS...
Securing WMSYS...
Securing GSMCATUSER...
Securing OJVMSYS...
Securing SYSTEM...
Securing XDB...
Securing SI_INFORMTN_SCHEMA...
Securing CTXSYS...
Securing ORACLE_OCM...
Securing MDDATA...
Securing ORDPLUGINS...
Securing SPATIAL_CSW_ADMIN_USR...
Securing SPATIAL_WFS_ADMIN_USR...
Securing DVSYS...
Securing DBSNMP...
Securing SYS...
Securing SYSKM...
Securing DIP...
Securing ANONYMOUS...
Securing AUDSYS...
Securing GSMADMIN_INTERNAL...
Securing SYSBACKUP...
Securing OUTLN...

PL/SQL procedure successfully completed.

And a second time, there is nothing to do

SQL> /

PL/SQL procedure successfully completed.

The snippet could be changed to add “ACCOUNT LOCK” if required. Though beware locking SYS on 11.2.0.4 and above:

ORA-28000: The Account Is Locked When Log In As SYS User Remotely While SYS User Was Locked (Doc ID 1601360.1)

Sometimes we don’t need to pack data in, we need to spread it out

I’ve gotten away with doing a presentation called “Contentious Small Tables” (download here) for the UKOUG three times now so I think it’s time to retire it from duty and serialise it here.

The planned instalments are below, I’ll change the items to links as the posts are published. The methods discussed in these posts are not exhaustive but hopefully cover most of the useful or interesting options.

  1. Sometimes we don’t need to pack data in, we need to spread it out (this post)
  2. Spreading out data – old school methods
  3. Spreading out data – with minimize records per block (also old school)
  4. Spreading out data – some partitioning methods
  5. Spreading out data – some modern methods

This post is the first instalment.

Sometimes we don’t need to pack data in, we need to spread it out

As data sets continue to grow there is more and more focus on packing data as tightly as we can. For performance reasons there are cases where we DBAs & developers need to turn this on its head and try to spread data out. An example is a small table with frequently updated/locked rows. There is no TX blocking but instead contention on the buffers containing the rows. This contention can manifest itself as “buffer busy waits”, one of the “gc” variants of “buffer busy waits” such as “gc buffer busy acquire” or “gc buffer busy release” or maybe on “latch: cache buffers chains”.

You’ll probably come at a problem of this nature from a session perspective via OEM, ASH data, v$session_event or SQL*Trace data. Taking ASH data as an example you can see below that for my exaggerated test, global cache busy waits dominate the time taken and, key for this series of posts, the “P1″ and “P2″ columns contain a small number of values.

column event format a35
select * from (
 select NVL(event,'CPU') event
 , count(*) waits
 , p1, p2
 from gv$active_session_history
 where sample_time between 
       to_date(to_char(sysdate,'DD-MON-YYYY')||' &from_time.','DD-MON-YYYY HH24:MI')
   and to_date(to_char(sysdate,'DD-MON-YYYY')||' &to_time.','DD-MON-YYYY HH24:MI')
 and module = 'NJTEST'
 group by NVL(event,'CPU'), p1, p2
 order by 2 desc
) where rownum <= 5;

EVENT                   WAITS    P1      P2
---------------------- ------ ----- -------
gc buffer busy acquire   1012     5  239004
gc buffer busy release    755     5  239004
gc current block busy     373     5  239004
CPU                        65     0       0

For the wait events above “P1″ and “P2″ have the following meanings:

select distinct parameter1, parameter2
from v$event_name
where name in ('gc buffer busy acquire'
              ,'gc buffer busy release'
              ,'gc current block busy');

PARAMETER1     PARAMETER2
-------------- --------------
file#          block#

So above we have contention on a single buffer. From ASH data we can also get the “SQL_ID” or “CURRENT_OBJ#” in order to identify the problem table. Below we show that all rows in the test table are in a single block – 239004.

select dbms_rowid.rowid_block_number(rowid) blockno
, count(*)
from procstate
group by dbms_rowid.rowid_block_number(rowid);

   BLOCKNO   COUNT(*)
---------- ----------
    239004         12

After discovering this information the first port of call is to look at the SQL statement, execution plan and supporting code but assuming the application is written as best it can be then perhaps it’s time to look at the segment and data organisation.

In order to help illustrate the point of this series here is a chart showing contention in ASH data from a crude test case. The huge grey stripe being “gc” busy waits and the thin red stripe being standard “buffer busy waits”.

Contention  11g normal heap table

The next post in this series will look at some “Old School” methods of reducing this contention.

SPM and schema virtualization

I received a lot of feedback and a fair number of good questions on my session on SQL Plan Management for the Virtual Technology Summit. Some of the questions deserved additional research and a more detailed answer than I could give in the live Q&A and I hope I can add most if not all of these issues to this blog eventually. A lot of you have also asked for the slides and i uploaded the pdf here in case you have trouble pulling them from the event site. Here is the first one:

Why do you say you need to have exactly the same objects in all schemas when doing schema level virtualization?

SPM prerequesite slideI made that point without much of an explanation. So here is how I discovered this issue: We host a number of different schemas of basically the same application in the same database in different schemas. Most of the tables will be the same but one schema may have more data than another and since we did not care about this very much when we first started this, not all tables have the same indexes. Most of them have indexes on the same columns but they may not have the same name in all schemas. So the “same” index may be called differently in different schemas. This happens easily if you don’t name your indexes, especially the ones created implicitly for primary keys or unique constraints.
SPM will be valid across all schemas. So if you collect and accept a baseline in one schema and another user issues the same SQL against their tables, they will use the baselines aswell. And this can be a good or a bad thing depending on how you see this. If you want to quarantee that SQL will use the same plans against all your schemas, this is something you want. BUT this breaks when a baseline uses an index that has a different name in a different schema. The plan hash value will be different in that case and the plan that the optimizer chose will look different from the one that is stored in the baseline. So it will fall back to one of the baseline plans that is acutally reproducable.

If my words do not make sense, here is an example. The first steps are exactly the same as described in the HOL post I wrote for OTN, except for the last step. Do not delete the baseline with the full scan, and this will leave us in a state where we have two accepted baselines for the example query. One uses a full scan, the other one uses an index range scan on the index called ‘T_IDX’. Now create another user/schema:

PDB1@ORCL> create user pmuser2 identified by oracle;

User created.

PDB1@ORCL> grant dba to pmuser2;

Grant succeeded.

Connect with that user and create a table and an index. We will give it the same name as in the original schema and observe that we will use the baseline SQL when querying the table.

[oracle@localhost ~]$ sqlplus pmuser2/oracle

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 10 12:24:44 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

PDB1@ORCL> create table t as select * from dba_objects;

Table created.

PDB1@ORCL> create index t_idx on t (owner);

Index created.

PDB1@ORCL> set autotrace on
PDB1@ORCL> variable var42 varchar2(42);
PDB1@ORCL> exec :var42 := 'PMUSER';

PL/SQL procedure successfully completed.

PDB1@ORCL> select count(*) from t where owner= :var42;

  COUNT(*)
----------
	 6


Execution Plan
----------------------------------------------------------
Plan hash value: 293504097

---------------------------------------------------------------------------
| Id  | Operation	  | Name  | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	  |	1 |	6 |	8   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |	  |	1 |	6 |	       |	  |
|*  2 |   INDEX RANGE SCAN| T_IDX |  3039 | 18234 |	8   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - access("OWNER"=:VAR42)

Note
-----
   - SQL plan baseline "SQL_PLAN_arrxanznkdmsaded8ae2f" used for this statement

So now let’s drop that index, recreate it with a different name and see what happens:

PDB1@ORCL> drop index t_idx;

Index dropped.

PDB1@ORCL> create index other_t_idx on t (owner);

Index created.

PDB1@ORCL> select count(*) from t where owner= :var42;

  COUNT(*)
----------
	 6


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1 |	6 |   427   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |	  |	1 |	6 |	       |	  |
|*  2 |   TABLE ACCESS FULL| T	  |  3039 | 18234 |   427   (1)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("OWNER"=:VAR42)

Note
-----
   - SQL plan baseline "SQL_PLAN_arrxanznkdmsa3fdbb376" used for this statement

So what happened here? The optimizer _wanted_ to use a plan that was doing a range scan on the new index other_t_idx but the hash value of that plan did not match with the ones in the accepted baselines. It then had to fall back to one of the accepted baselines of which only one was actually reproducable because the index T_IDX does not exist in this schema. The new plan with the range scan on OTHER_T_IDX was added as a new baseline but has a status of unaccapted. You should be aware of this. Of course you can evolve the new baseline, but before we try that I want to show you another little oddity. Let’s drop the baseline with the full scan because we really don’t want to use it anyway, then re-run the query and see what happens:

PDB1@ORCL> declare 
 drop_result pls_integer;
 begin 
 drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE( 
 sql_handle => 'SQL_abdfaaa7e926cf0a',  
 plan_name => 'SQL_PLAN_arrxanznkdmsa3fdbb376'); 
 dbms_output.put_line(drop_result);    
 end; 
/  

PL/SQL procedure successfully completed.

PDB1@ORCL> select count(*) from t where owner= :var42;

  COUNT(*)
----------
	 6


Execution Plan
----------------------------------------------------------
Plan hash value: 2948344740

--------------------------------------------------------------------------------
| Id  | Operation	  | Name	| Rows	| Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |		|     1 |     6 |     8   (0)| 00:00:01|
|   1 |  SORT AGGREGATE   |		|     1 |     6 |	     |         |
|*  2 |   INDEX RANGE SCAN| OTHER_T_IDX |  3039 | 18234 |     8   (0)| 00:00:01|
---------------------------------------------------------------------------------


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

   2 - access("OWNER"=:VAR42)

So in this case, no baseline was used at all even though there is an accepted baseline for this SQL. But since it cannot be used, the system falls back to not using a baseline at all.

PDB1@ORCL> set serveroutput on
declare evolve_out CLOB;
begin
evolve_out := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE ( SQL_HANDLE => 'SQL_abdfaaa7e926cf0a', COMMIT => 'YES' ); 
dbms_output.put_line(evolve_out); 
end; 
/

GENERAL INFORMATION
SECTION
------------------------------------------------------------------------


 Task Information:			       

--------------------------------------------- 
 Task Name	      : TASK_81
 Task Owner	      : PMUSER2 	    
 Execution Name       : EXEC_191
 Execution Type       : SPM EVOLVE	    
 Scope		      : COMPREHENSIVE	    
 Status 	      : COMPLETED	    
 Started              : 07/10/2014 12:36:44 
 Finished	      : 07/10/2014 12:36:45 
 Last Updated         : 07/10/2014 12:36:45 
 Global Time Limit    : 2147483646
 Per-Plan Time Limit  : UNUSED		    
 Number of Errors     : 0

--------------------------------------------------------------------------


SUMMARY SECTION
------------------------------------------------------------------------

  Number of plans processed  : 1  
  Number of findings         : 2  
  Number of recommendations  : 1  
  Number of errors	     : 0

------------------------------------------------------------------------


DETAILS
SECTION
------------------------------------------------------------------------

 Object ID	    : 2
 Test Plan Name     : SQL_PLAN_arrxanznkdmsae8cba257
 Base Plan Name     : Cost-based plan
 SQL Handle	    : SQL_abdfaaa7e926cf0a
 Parsing Schema     : PMUSER2
 Test Plan Creator  : PMUSER2
 SQL Text	    : select count(*) from t where owner= :var42


Bind Variables:
-----------------------------
 1  -  (VARCHAR2(128)):  PMUSER

FINDINGS
SECTION
--------------------------------------------------------------------------

Findings (2):
-----------------------------
 1. None of the accepted plans were reproducible.				    
 2. The plan was automatically accepted.

Recommendation:
-----------------------------
 Consider accepting the plan.

----------------------------------------------------------------------------

So since the baseline plan was not reproducible in this context (schema pmuser2), the new one was accepted automatically.

If this all sounds confusing, remember two things:
– If you have tables with the same names in different schemas, SPM will be used across those schemas for the same SQL
– If you want to save yourself from some extra SPM complexity, just name all indexes across the schemas the same

How are Oracle background processes renamed?

We all know that when an Oracle instance starts, the background processes are all (or perhaps mostly?) copies of the same executable – $ORACLE_HOME/bin/oracle.

We can see this using the “args” and “comm” format options of the Unix “ps” command. Below we can see the name of the SMON process (“args”) and the name of the source executable (“comm”):

$ ps -p 6971 -o args,comm
COMMAND                     COMMAND
ora_smon_orcl1              oracle

This is common knowledge and when in the past I’ve wondered how this works I’ve always just accepted this Oracle magic and moved on to more important thoughts such as “what’s for lunch?”. Well today my belly is full and I have time to let such thoughts develop!

It turns out this is a quite simple trick. If you have a basic knowledge of the C programming language you’ll be familiar with the argc and argv parameters. e.g.

int main (int argc, char *argv[])

argc – the number of command line arguments
argv – an array of pointers to the command line arguments with argv[0] being the process name.

Surprisingly the values of this array are modifiable. So we can manipulate the name of a running process with a simple sprintf() command (other options are available). e.g.

sprintf(argv[0], "renamed");

My description of this is high level and I’m sure there are risks in doing this, such as overflowing argv[0] into argv[1], but by way of a simple example here is a demo C program:

#include <stdio.h>
void main (int argc, char *argv[])
{
  int pid;
  pid = (int) getpid ();
  printf("My name is '%s'\n",argv[0]);
  printf("Sleeping for 10 seconds (quick - 'ps -p %d -o args,comm')...\n",pid);
  sleep(10);
  sprintf(argv[0], "renamed");
  printf("My name is now '%s'\n",argv[0]);
  printf("Sleeping for 10 seconds (quick - 'ps -p %d -o args,comm')...\n",pid);
  sleep(10);
}

This program sleeps so I can capture process details from another session using “ps”, renames the running process and then sleeps again so I can capture more details. So let’s compile the C program and see what happens when I run it from one session and capture ps details from another session:

Session 1

$ cc newname.c -o newname
$ ./newname
My name is './newname'
Sleeping for 10 seconds (quick - 'ps -p 7930 -o args,comm')...
My name is now 'renamed'
Sleeping for 10 seconds (quick - 'ps -p 7930 -o args,comm')...

Session 2

$ ps -p 7930 -o args,comm
COMMAND                     COMMAND
./newname                   newname
$ ps -p 7930 -o args,comm
COMMAND                     COMMAND
renamed e                   newname

Pretty cool. Notice how my renamed process is now called “renamed e”. The trailing “e” is left over from the original name of “./newname” thus proving it’s not quite as simple as I suggest and that my C skills are basic. None-the-less I think this is pretty cool.

OTN MENA tour final stop: Dubai, UAE

After a very relaxed day I followed the others virtually on their travel to Dubai. All but Osama were too tired to go out and opted for a quit night and early sleep. So after a quick run that left my soaked in sweat I met Osama for some catching up since I have not seen him since UKOUG Tech 13 back in december.

Dubai Marina at nightDubai is a very exciting destination. Two million people from all over the world are living in a rather compact space that has erupted from the desert in the past few decades. It has become the business hub of the golf region with many major banks and services companies occupying a stunning set of skyscrapers, all of which are overshadowed by the majestic Burj Khalifa, the world’s tallest building at 830 meters.

The event location was chosen wisely by Vishnu Vinnakota of prosec in the sizzling marina district at a very nice hotel which featured the largest projector screen I have had the honor of presenting on. There were no complaints of a too small font for some of the code examples this time.

We started with the proven Exadata performance expert panel which we have done at most of the stops before and all that practice made perfect, we even spiced things up a bit by letting someone else present “their” slides.

OTN MENA tour Dubai teamIt was the third time I saw Ed present his introduction to Big Data on this tour but he is so much fun to watch and the presentation flows so nicely that it still did not get boring for me.

Mike talked about IO performance and started with a review of how physical disc drives work and why they are limited to a maximum of 200-300 IOPS each. He then showed how this number can be increased by using RAID technology, compared this to modern flash based devices and showed ways how to benchmark and monitor the IO system. Great basic content that should be known by all DBAs.

speaking in dubaiAfter a lunch that had me go for seconds three times, I was scheduled to talk about flashback data archives. But I also had requests from the attendees to talk about SQL plan management and 12c new features. All three are full 45 to 60 minute presentations but I managed to get to the gist of each of them in about 15 minutes and was able to deliver all three topics.

Osama discussed high availability options for fusion middleware from self-written scripts to full blown cluster and provided many tricks to manage those things in the stack above the database that we dbas so often neglect and forget about.

I was excited to meet fellow ACE Director Joel Perez for the first time and he did not disappoint when he presented about how to upgrade from 11g single instance to 12c RAC with minimal downtime. In addition to this specific project there was also a lot of information about backup, restore and replication.

The final words once again belonged to Jim who wrapped it up in style while the rest of us once again admired his eloquence in speaking and presenting. Unfortunately, he had to cut his presentation short because we (the others) overran a bit on time which is a shame because I was very much looking forward to his examples on histograms. I saw him prepare the slides on the plane to Cairo and thought it was quite hilarious. I guess this will now premiere with another audience at another conference (and I will not give away what it is).

Overall, the tour was a huge success, especially given the fact that this was the first of its kind and these were all the first time we had done anything in these cities and did not really know what to expect. A big thank you to Tariq and Syed who masterminded this tour, the local event organizers who did their very best to provide the forum for us to present and reach out to the local users and of course to the Oracle Technology Network and ACE program for being the sponsor and enabling us do to this.

I very much hope that we can establish the tour as a solid event in the region and keep improving it year by year. I know that I and all of the other speakers would be proud and more than happy to be back again.

OTN MENA tour Stop 2: Saudi Arabia

As I am writing this I am just slowing down after very intense 36 hours in Riyadh, still in awe and slowly processing all the impressions I made here. We arrived at King Khalid airport at 9pm on Wednesday night but hat to test both our own and our generous hosts’ patience as we waited 4 hours in the immigration line. I was expecting that with a pre-approved visa (that process alone was another huge effort) this would be rather quick. Ironically, they are in the midst of upgrading their biometric database and are not able to run at full capacity right now.

OTN MENA tour 2014 RiyadhWith the late arrival we had to skip all dinner plans and were happy to fall into our hotel beds and sleep at least 4 hours until it was time again to get up for the event which was held in the conference center of a very nice hotel. And Esolutions, the local sponsor, together with the ARABOUG went out of their way and made a huge effort in organizing this. They had signage, a very nice stage, incredible arabic lunch and they used the intermissions to award to distinguished guests and raffle off a number of very nice prices among the more than 100 Oracle DBAs and professionals that showed up for this first of it’s kind event in Saudi Arabia. One of the VIPs that was awarded an award shared how he started his IT career as a junior DBA with one of Mike’s books. There was even a professional photographer and a TV crew to document the day.

OTN MENA tour Riyadh - Jim speakingAll presentations were spot-on and well received by a crowd that was hungry to expand their knowledge. HA and RAC seemed to be the topics of the biggest interest and we managed to cover a lot of ground, starting with the Exadata Tuning Expert Panel led by Tariq, my RAC management session, Mike, Inam and Jim talking about performance tuning and Syed and Ed rounding it all out with sessions on security and big data. In addition, I also enjoyed very much to chat with a great number of attendees and hopefully answered a number of very specific questions. One of the biggest pieces of advice I hope I could give is that if they enjoyed the event it is really up to the local oracle community to organize themselves and meet for informal roundtable-type events and share their knowledge rather than hoping and waiting for an OTN tour stop every year. With Syed and Inam Ullah Bukhari they already have at least two great local speakers there and I am sure there are more.

OTN MENA tour awardEverybody was very polite and I am sure I shook hands, said thanks and had my picture taken with every single attendee at least twice during the day. After many heartly good-byes it was time for us to go on a brief sightseeing tour of Riyadh. While it may have been brief, we still managed to see so very much of this wonderful city that is by no means a common tourist destination due to their very strict visa policies. Our hosts took us to the national museum Which showcased the history of the region from the creation of the universe to today and included impressive models of the holy mosques in Mecca and Medinah including the drape that covers the Kaaba. The museum was structured very concisely and was so huge that it was really a shame that we had to rush through it.

OTN MENA tour RiyadhThis was followed by a walk around of a nice park and what looked like the rebuild of old Riyadh with small alleys, open areas surrounded by palm trees and nicely decorated houses. We then had the chance to visit Kingdom Centre, Saudi Arabia’s highest building (until the Kingdom Tower in Jeddah is finished which should become the worlds tallest building), and zipped up the the 100th floor where you can enjoy magnificent views from a bridge that spans the two arches that give the tower a very distinct and unique appearance.

The night ended with a very generous dinner by the local sponsors and while the food satisfied the hunger, I wished the conversations would have gone on for another few days. But there was barely enough time for a very short two hour nap before at least Jim and I had to head out to the airport again which gave us the opportunity to see the sunrise. Tired but still excited, my mind was so preoccupied that I left my cell phone in the taxi but was able to get the driver’s number from the hotel and he turned around to return it. Just calling my own number did not even cross my mind until a few hours later.

The event in Jeddah was only scheduled for half a day, so I would not speak there and proceeded directly to Dubai. I was actually thankful for one day of rest on the beach. But I still followed the Jeddah event over the live stream and pictures on social media and it looks like that one was another success. Now I am waiting for the others including Joel and Osama to arrive in Dubai for the final event of the tour.