jump to navigation

Trivadis Performance Days 2018 March 8, 2018

Posted by Richard Foote in Oracle Indexes.
add a comment

tri_wuerfel_trainingsdisplay_0

I’m very excited and privileged to be asked to present the opening and closing sessions at this year’s Trivadis Performance Training Days in Zurich, Switzerland on 19-20 September 2018. It’s one of the premier Oracle training events in Europe, one which I’ve always wanted to attend. This year has a fantastic lineup, so I’m really looking forward to it all. Other speakers include:

  • Christian Antognini
  • Jonathan Lewis
  • Kamil Stawiarski
  • Mauro Pagano
  • Roger MacNicol
  • Nigel Bayliss
  • Ludovico Caldara

For details of all the presentations and on how to enrol: https://www.trivadis.com/en/training/performance-days-2018-tvdpdays2018

Hope to see some of you there !!

Advertisements

My New David Bowie Blog March 7, 2018

Posted by Richard Foote in David Bowie.
add a comment

db-albums

Just a short note to say I’ve started a new blog that we focus on the one and only David Bowiehttps://davidbowiewordpresscom.wordpress.com/

Those of you that only vaguely know me, know I’m a huge David Bowie fan, so I thought it might be a bit of fun to write about his extraordinary body of work.

I’m starting off with a series that will look at each of his studio albums, where I’ll rank them from worst to best. It was a really tough job, picking out which superb album is better than others, but I think I’ve finally settled on my final order.

Join me in what will no doubt be a controversial journey as I initially discuss and explore his amazing 28 studio albums. I’ll publish my views on the first album, that which I consider to be his worst, very soon.

Oracle Database 18c: Introduction to Memoptimized RowStore (Memory of a Free Festival) March 5, 2018

Posted by Richard Foote in 18c, 18c New Features, Memoptimized RowStore, Oracle Indexes.
1 comment so far

memory of a free festival

One of the potentially cool new features introduced in Oracle Database 18c is the Memoptimized RowStore, designed to improve the performance and scalability of key-value based queries.

A new pool in the SGA called the Memoptimize Pool can be configured to store specific heap tables that you may wish to optimize, in a manner not dissimilar to how you may wish to store specific tables in the Keep Pool. The difference with this new pool however is that not only are the table blocks store in the memory pool, but 25% of the pool is configured to automatically create and store a hash index based on the Primary Key (PK) of the table.

Accesses to the table via an equality predicate on the PK can then use the in-memory hash index to very efficiently access the required row, eliminating any physical IOs. In addition, Exadata Exadirect Oracle Net Protocol can leverage Remote Direct Memory Access (RDMA) to allow clients to directly access the required memory regions over the network, avoiding both CPU and OS overheads. The result is potentially very fast and very scalable key-value based queries.

A new parameter MEMOPTIMIZE_POOL_SIZE is used to size the pool as required. Note this pool is part of the SGA and so needs to be carefully considered when sizing the overall SGA. Currently with the first release of 18c, the MEMOPTIMIZE_POOL_SIZE can not be dynamically changed and is not automatically sized and tuned.

Once you’ve set the MEMOPTIMIZE_POOL_SIZE accordingly, you can then create or alter tables to take advantage of the new pool.

The following demo was run on Oracle Live SQL:

I first attempt to create a new table using the new MEMOPTIMIZE FOR READ clause to allocate the table to this new pool:

create table bowie (id number, code number, name varchar2(42))
memoptimize for read;

ORA-62156: MEMOPTIMIZE FOR READ feature not allowed on segment with deferred storage

So a new table can not be created with the default setting of segment with deferred storage. Let’s try again:

create table bowie (id number, code number, name varchar2(42))
segment creation immediate memoptimize for read;

ORA-62142: MEMOPTIMIZE FOR READ feature requires NOT DEFERRABLE PRIMARY KEY constraint on the table

So to use the Memoptimized KeyStore, a table must have a PK. These are the columns by which the automatic in-memory hash index is based.

create table bowie (id number constraint bowie_pk primary key, code number, name varchar2(42))
segment creation immediate memoptimize for read;

Table created.

Success. So this table has been created to use the Memoptimized RowStore.

You can also alter existing tables to make use of the Memoptimized RowStore:

create table bowie2 (id number constraint bowie2_pk primary key, code number, name varchar2(42));

Table created.
insert into bowie2 select rownum, mod(rownum,1000), 'DAVID BOWIE' from dual connect by level <=100000;

100000 row(s) inserted.

commit;

Statement processed.

alter table bowie2 memoptimize for read;

Table altered.

For tables that have just been altered to use the Memoptimized RowStore, you can load the table data into the pool via the new DBMS_MEMOPTIMIZE package:

exec dbms_memoptimize.populate(schema_name=>'SQL_GLGTFSMUBLHEUOFHPRXBRHITF', table_name=>'BOWIE');

ORA-62138: MEMOPTIMIZE memory area does not exist. ORA-06512: at "SYS.DBMS_MEMOPTIMIZE", line 10
ORA-06512: at "SYS.DBMS_MEMOPTIMIZE", line 113
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SQL", line 1721

So it appears from the error message that the Memoptimized RowStore is not currently enabled on Oracle Live SQL.

Which makes some sense as it would be very difficult  to size the MEMOPTIMIZE_POOL_SIZE parameter appropriately if any old bugger can create objects in the pool.

So it means we can’t use the Oracle Live SQL environment to test and play around with this new feature, but it doesn’t stop us from exploring a little in how this feature would work.

Let’s now populate the original table:

insert into bowie select rownum, mod(rownum,1000), 'DAVID BOWIE' from dual connect by level <=100000;

100000 row(s) inserted.

commit;

Statement processed.

 

And collects statistics:

exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE');

Statement processed.

In much as the same way as the Oracle Database In-Memory option can’t guarantee all objects assigned to the InMemory pool can fit in the allocated pool set by  INMEMORY_SIZE, the same goes for the Memoptimized RowStore. Therefore the CBO can only but assume the tables reside in the memory structures in which they’re allocated and only at run time decide if it might need to access the required data elsewhere (e.g. via the Buffer Cache, via a database index rather than the in-memory hash index, etc.).

Note both the commands to create a table and to alter a table to use the Memoptimised RowStore worked even though the necessary pool doesn’t appear to have been allocated in the database.

So lets investigate what the CBO does with a table assigned to the Memoptimized RowStore.

As the PK will automatically have an inmemory hash index created within the Memoptimized RowStore, does Oracle still automatically create a database index as well:

select index_name, blevel, leaf_blocks from user_indexes where table_name='BOWIE';

INDEX_NAME BLEVEL LEAF_BLOCKS

BOWIE_PK        1         187

So yes, an index is still automatically created to police the PK constraint if a table is assigned to the Memoptimized RowStore.

So let’s have a look at the execution plan for a query based on a PK equality predicate:

select * from bowie where id=42;

---------------------------------------------------------------------------------------------------
| Id | Operation                              | Name     | Rows | Bytes | Cost (%CPU) | Time      |
---------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                       |          |    1 |    20 |       2 (0) |  00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID READ OPTIM | BOWIE    |    1 |    20 |       2 (0) |  00:00:01 |
|* 2 | INDEX UNIQUE SCAN READ OPTIM           | BOWIE_PK |    1 |       |       1 (0) |  00:00:01 |
---------------------------------------------------------------------------------------------------

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

2 - access("ID"=42)

So we have a couple of new execution plan steps here. INDEX UNIQUE SCAN READ OPTIM which states the in-memory Hash Index will be accessed to find the location of the required row and TABLE ACCESS BY INDEX ROWID READ OPTIM which states the table will then accessed via the rowid obtained from the in-memory Hash Index.

If we run a query that uses a BETWEEN predicate on the PK column:

select * from bowie where id between 42 and 50;

------------------------------------------------------------------------------------------------
| Id | Operation                           | Name     | Rows | Bytes | Cost (%CPU) | Time      |
------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |          |   10 |   200 |       3 (0) |  00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE    |   10 |   200 |       3 (0) |  00:00:01 |
|* 2 | INDEX RANGE SCAN                    | BOWIE_PK |   10 |       |       2 (0) |  00:00:01 |
------------------------------------------------------------------------------------------------

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

2 - access("ID">=42 AND "ID"<=50)

We notice the Memoptimized RowStore is not used, just the database PK index via a standard Index Range Scan.

If we run a query that the CBO knows will only retrieve 2 rows via an IN clause:

select * from bowie where id in (42, 442);

-----------------------------------------------------------------------------------------
| Id | Operation                   | Name     | Rows | Bytes | Cost (%CPU) | Time      |
-----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |          |    2 |    40 |       3 (0) |  00:00:01 |
|  1 | INLIST ITERATOR             |          |      |       |             |           |
|  2 | TABLE ACCESS BY INDEX ROWID | BOWIE    |    2 |    40 |       3 (0) |  00:00:01 |
|* 3 | INDEX UNIQUE SCAN           | BOWIE_PK |    2 |       |       2 (0) |  00:00:01 |
-----------------------------------------------------------------------------------------

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

3 - access("ID"=42 OR "ID"=442)

Again, the Memoptimized RowStore is not used, just the database PK index.

The Memoptimized RowStore is only used on queries with a single equality predicate on the table. Even a query with an equality predicate in combination with another predicate on the same table (odd as that may be), will result in the Memopimized RowStore not being used:

select * from bowie where id=42 and name='DAVID BOWIE';

----------------------------------------------------------------------------------------
| Id | Operation                   | Name     | Rows | Bytes | Cost (%CPU) | Time     |
----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |          |    1 |    21 |       2 (0) | 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID | BOWIE    |    1 |    21 |       2 (0) | 00:00:01 |
|* 2 | INDEX UNIQUE SCAN           | BOWIE_PK |    1 |       |       1 (0) | 00:00:01 |
----------------------------------------------------------------------------------------

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

1 - filter("NAME"='DAVID BOWIE')
2 - access("ID"=42)

 

So the Memoptimized RowStore is only considered by the CBO, for queries based on a singular PK=value predicate on a table.

More to come once I have my own database to play in 🙂

Oracle Database 18c Indexing Related New Features (New Angels of Promise) February 27, 2018

Posted by Richard Foote in Oracle Indexes.
2 comments

new angels of promise

Although the recently released Oracle 18c Database is really just 12.2.0.2 under the covers, there are a few little features and enhancements that are of interest from an indexing perspective. These include:

  • Memory Optimized Rowstore
  • Scalable Sequences
  • Oracle Text indexing enhancements, such as automatic background index maintenance and new optimize index options
  • JSON Search Index key name limit increased from 64 to 255 characters
  • Spatial Index support with Oracle Database Sharding
  • Golden Gate automatic conflict detect and resolution (CDR) support on tables with just Unique Constraints and/or Unique Indexes (previously required a Primary Key)
  • Modifying the partitioning strategy using a single, online DML, which can be extremely useful with regard modifying related indexing strategies
  • Online Merging of partitions and subpartitions (and impact on associated indexes)

 

The complete list of all that’s new with the Oracle 18c Database, check out the online manuals.

If you don’t have an Oracle Cloud account or an Oracle Engineered System to play around with the 18c database, you can have a play on Oracle Live SQL.

I’ll be blogging about these new features and enhancements (and various others) in the coming weeks so keep and eye out.

European Indexing Internals Seminar Events: Please Help Me Select (Station To Station) February 20, 2018

Posted by Richard Foote in Oracle Indexes.
add a comment

station to station

I’m currently determining which European countries to initially target for a series of my 2 day Oracle Indexing Internals and Best Practices seminars tentatively scheduled for the late May / early June time frame.

The cost for the 2 day seminar is to be confirmed but will be approximately 1200 Euros (plus any local taxes), which includes a guaranteed small class setting, lunch and both hard and soft copy of the exclusive 800+ page seminar materials.

To help me decide which 4 countries to initially target, can you please leave me a comment or contact me directly (richard@richardfooteconsulting.com) if you would be interested in attending a seminar in a particular country.

This is a must attend seminar of benefit to not only DBAs, but also to Developers, Solution Architects and anyone else interested in designing, developing or maintaining high performance Oracle-based applications. It’s a fun, but intense, content rich seminar that is suitable for people of all experiences (from beginners to seasoned Oracle experts).

I’ve now run this seminar in some 20 countries to critical acclaim. The last 30 people that have attended the seminar have scored the seminar a straight 5 out of 5.

For full details on seminar content, see my Index Seminar page.

The more people who express an interest in attending the seminar in a particular country, the more likely I’ll select the country as part of the initial May/June series.

Don’t hesitate to contact me if you have any questions regarding the seminar.

“The European cannon is here” !!

Index Skip Scan: Potential Use Case or Maybe Not ? (Shine On You Crazy Diamond) January 30, 2018

Posted by Richard Foote in Oracle Indexes.
5 comments

shine-on-you-crazy-diamond

While answering a recent question on a LinkedIn forum, it got me thinking whether there’s a potential use case for using an INDEX SKIP SCAN I hadn’t previously considered.

I’ve discussed Index Skip Scans previously (as I did here), a feature introduced around Oracle9i that allows an index to be considered by the CBO even if the leading column of the index is not included in a query predicate. However, the index is only going to be used by the CBO if there are relatively few distinct values in the missing leading column, as Oracle has to effectively scan the index multiple times for each potential leading column value. If there are too many distinct values, each scan might not result in sufficient index leaf blocks being “skipped” thereby making the INDEX SKIP SCAN access path too inefficient.

But it occurred to me that the strategy of using an index skip scan could also potentially be applied when performing a sort-based aggregate function. Or maybe not.

So a little test case to find out.

I begin by creating a table with 1M rows. The key here though is that the CODE column only has 3 distinct values, so any aggregation based on CODE will only return 3 or fewer rows.

SQL> create table bowie (id number not null, code number not null, sales number not null, name varchar2(42));

Table created.

SQL> insert into bowie select rownum, mod(rownum,3), ceil(dbms_random.value(0,10000)), 'David Bowie' from dual connect by level  commit;

Commit complete.

I now create an index based on the CODE and SALES columns, with CODE the leading column:

SQL> create index bowie_i on bowie(code, sales);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE', method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

As CODE only has very few distinct values, the index is a candidate for an INDEX SKIP SCAN if CODE is not specified in a predicate. For example:

SQL> select code, sales from bowie where sales=1;

100 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3861840421

----------------------------------------------------------------------------
| Id | Operation        | Name    | Rows | Bytes | Cost (%CPU) | Time     |
----------------------------------------------------------------------------
| 0  | SELECT STATEMENT |         |  100 |   700 |       5 (0) | 00:00:01 |
|* 1 | INDEX SKIP SCAN  | BOWIE_I |  100 |   700 |       5 (0) | 00:00:01 |
----------------------------------------------------------------------------

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

1 - access("SALES"=1)
    filter("SALES"=1)

Statistics
----------------------------------------------------------
   0 recursive calls
   0 db block gets
  31 consistent gets
   0 physical reads
   0 redo size
2305 bytes sent via SQL*Net to client
 674 bytes received via SQL*Net from client
   8 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
 100 rows processed

So an INDEX SKIP SCAN has indeed been used by the CBO and at just 31 consistent gets, not a bad result when fetching the 100 rows of interest. With so few distinct values of CODE, Oracle only has to perform a relatively few number of scans of the index to retrieve all possible SALES of interest across each og the (3) CODE values. At a cost of just 5, the CBO has estimated that relatively few index leaf blocks indeed need to be accessed here.

It’s also worth mentioning at this point that Oracle can also use the index very effectively to return just the MIN (or potentially MAX) SALES column for each CODE value of interest, as it only has to read the first CODE index entry to subsequently determine the associated minimum SALES value:

SQL> select min(sales) from bowie where code=1;

MIN(SALES)
----------
1

Execution Plan
----------------------------------------------------------
Plan hash value: 2634611566

----------------------------------------------------------------------------------------
| Id | Operation                  | Name    | Rows | Bytes | Cost (%CPU) | Time      |
----------------------------------------------------------------------------------------
| 0  | SELECT STATEMENT           |         |    1 |     7 |       3 (0) |  00:00:01 |
| 1  | SORT AGGREGATE             |         |    1 |     7 |             |           |
| 2  | FIRST ROW                  |         |    1 |     7 |       3 (0) |  00:00:01 |
|* 3 | INDEX RANGE SCAN (MIN/MAX) | BOWIE_I |    1 |     7 |       3 (0) |  00:00:01 |
----------------------------------------------------------------------------------------

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

3 - access("CODE"=1)

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

At just 3 consistent gets, Oracle has only had to read the first CODE=1 index entry to immediately determine the minimum associated SALES value.

In theory, Oracle could use the same strategies when processing a GROUP BY aggregate query to very quickly and efficiently determine the minimum SALES value for each distinct CODE in my data. Oracle knows there are only 3 (few) distinct key values and with a combination of using the index to quickly access the minimum (first) SALES of each CODE value and an INDEX SKIP SCAN to quickly re-scan the index to get to the next CODE index entry, an index could be used to very quickly and efficiently find and retrieve the necessary data set in the following query:

SQL> select code, min(sales) from bowie group by code;

CODE MIN(SALES)
---------- ----------
1 1
2 1
0 1

Execution Plan
----------------------------------------------------------
Plan hash value: 2387048003

---------------------------------------------------------------------------------
| Id | Operation            | Name    | Rows  | Bytes | Cost (%CPU) | Time     |
---------------------------------------------------------------------------------
| 0  | SELECT STATEMENT     |         |     3 |    21 |   1079 (43) | 00:00:01 |
| 1  | HASH GROUP BY        |         |     3 |    21 |   1079 (43) | 00:00:01 |
| 2  | INDEX FAST FULL SCAN | BOWIE_I | 1000K | 6835K |    677 (10) | 00:00:01 |
---------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
   0 recursive calls
   0 db block gets
2477 consistent gets
   0 physical reads
   0 redo size
 677 bytes sent via SQL*Net to client
 608 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
   3 rows processed

However, only an INDEX FAST FULL SCAN is used at a relatively expensive 2477 consistent gets. Note that Oracle is using a HASH GROUP BY for its aggregation by default (and not some form of sort/group type aggregation). As a result, the index is not considered here and note also that the final result set is NOT in CODE order (the data is returned in 1,2,0 CODE order).

We could try to force the use of an INDEX SKIP SCAN via a hint:

SQL> select /*+ index_ss (bowie, bowie_i) */ code, min(sales) from bowie group by code;

CODE MIN(SALES)
---------- ----------
0 1
1 1
2 1

Execution Plan
----------------------------------------------------------
Plan hash value: 944722262

--------------------------------------------------------------------------------
| Id | Operation            | Name    | Rows  | Bytes | Cost (%CPU) | Time     |
--------------------------------------------------------------------------------
| 0  | SELECT STATEMENT     |         |     3 |    21 |    2558 (4) | 00:00:01 |
| 1  | SORT GROUP BY NOSORT |         |     3 |    21 |    2558 (4) | 00:00:01 |
| 2  | INDEX SKIP SCAN      | BOWIE_I | 1000K | 6835K |    2558 (4) | 00:00:01 |
--------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
   0 recursive calls
   0 db block gets
2469 consistent gets
   0 physical reads
   0 redo size
 677 bytes sent via SQL*Net to client
 608 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
   3 rows processed

It’s now using an INDEX SKIP SCAN in the plan. But it’s not being “clever” in its use of the INDEX SKIP SCAN with 2469 consistent gets suggesting Oracle is not effectively making use of the (MIN/MAX) scan capability per access of distinct CODE and is unnecessarily reading most of the index leaf blocks.

Oracle however is now using a sort/group based process when performing it’s aggregation (as evidenced in the second step of the plan), resulting in the data now being returned in CODE order.

Even if we take the aggregation out of the equation with a simple MIN based query accessing more than one CODE value:

SQL> select min(sales) from bowie where code in (1,2);

Execution Plan
----------------------------------------------------------
Plan hash value: 1467404054

------------------------------------------------------------------------------
| Id | Operation        | Name    | Rows | Bytes | Cost (%CPU) | Time      |
------------------------------------------------------------------------------
| 0  | SELECT STATEMENT |         |    1 |     7 |       3 (0) |  00:00:01 |
| 1  | SORT AGGREGATE   |         |    1 |     7 |             |           |
| 2  | INLIST ITERATOR  |         |      |       |             |           |
|* 3 | INDEX RANGE SCAN | BOWIE_I |    1 |     7 |       3 (0) |  00:00:01 |
------------------------------------------------------------------------------

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

3 - access("CODE"=1 OR "CODE"=2)

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

The CBO still doesn’t consider INDEX SKIP SCAN type of processing in this scenario and is using a relatively inefficient INDEX RANGE SCAN instead.

So unfortunately, although I have an existing index in place that if only used effectively could potentially return the GROUP BY result set very efficiently, the CBO is not using the index. The CBO doesn’t appear to be able use an INDEX SKIP SCAN in combination with multiple MIN/MAX scans in scenarios when it perhaps could.

Announcement: Oracle Indexing Internals Seminars Coming to New Zealand in March 2018 (Shipyards of New Zealand) January 22, 2018

Posted by Richard Foote in Oracle Indexes.
add a comment

Richard Let's Talk Database Nov 2015

I’m very pleased to announce I’ve now finalised some dates in New Zealand for my popular and highly acclaimed “Oracle Indexing Internals and Best Practices” seminar. They are:

Wellington 12-13 March 2018 (Auldhouse: Level 8 Lumley House, 11 Hunter Street): Tickets and Registration Link

Auckland 15-16 March 2018 (Karstens: Level 4, 205 Queen Street): Tickets and Registration Link

As usual, numbers will be strictly limited due to the small class nature of the seminars, so if interested I advise booking early to avoid disappointment as seminars will obviously not be scheduled too regularly in NZ.

Early bird rates are available until 23 March or until the seminars are full. Venues have now been finalised and are listed above.

I hope to catch up with some of my many NZ friends at one of these events 🙂

Early Bird Extension – UK February Dates: “Oracle Indexing Internals and Best Practices” Seminar December 20, 2017

Posted by Richard Foote in Oracle Indexes.
add a comment

RichardFoote2

As a Christmas present to those in the UK looking at attending my “Oracle Indexing Internals and Best Practices” seminar in February next year, the Early Bird rates are now available until 19th January 2018.

Take this opportunity to attend this highly acclaimed seminar that is packed full of information designed to significantly improve the performance and maintainability of your applications and Oracle databases.

Location, dates and registration for these events are as follows:

London 12-13 February 2018: (Hilton London Kensington). Tickets & Registration Link.

Birmingham 15-16 February 2018: (Novotel Birmingham Centre). Tickets & Registration Link.

Manchester 19-20 February 2018: (Novotel Manchester City Hotel). Tickets & Registration Link.

Glasgow 22-23 February 2018: (Glasgow Training Rooms). Tickets and Registration Link.

Belfast 26-27 February 2018: (Ormeau Business Park). Tickets and Registration Link.

 

May I take this opportunity to wish everyone a wonderful Christmas and a prosperous, happy and David Bowie filled New Year !!

Great Britain and Northern Ireland February 2018 Dates: “Oracle Indexing Internals and Best Practices” Seminar (Battle For Britain) November 17, 2017

Posted by Richard Foote in Index Internals Seminar, Oracle Indexes, Richard Foote Consulting, Richard Presentations.
1 comment so far

seminar photo

UPDATE: ALL TICKETS ARE NOW AVAILABLE FOR PURCHASE !!

Attention Oracle Professionals in the United Kingdom !!

I have now finalised all the dates and venues for a series of my popular and critically acclaimed “Oracle Indexing Internals and Best Practices” seminar I’ll be running in the UK in February 2018.

I’m extremely excited as this will be the first time I’ve delivered this seminar in my old homeland, despite having now delivered versions of this seminar in some 18 countries.

This is a must attend seminar of benefit to not only DBAs, but also to Developers, Solution Architects and anyone else interested in designing, developing or maintaining high performance Oracle-based applications/databases. It’s a fun, but intense, content rich seminar that is suitable for people of all experiences (from beginners to seasoned Oracle experts). Full details of the 2 day seminar content can be found here.

Location, dates and registration for these events have been finalised as follows:

London 12-13 February 2018: (Hilton London Kensington). Tickets & Registration Link.

Birmingham 15-16 February 2018: (Novotel Birmingham Centre). Tickets & Registration Link.

Manchester 19-20 February 2018: (Novotel Manchester City Hotel). Tickets & Registration Link.

Glasgow 22-23 February 2018: (Glasgow Training Rooms). Tickets and Registration Link.

Belfast 26-27 February 2018: (Ormeau Business Park). Tickets and Registration Link.

 

UPDATE: ALL TICKETS ARE NOW AVAILABLE FOR PURCHASE !!

Early Bird Rate (enrolments prior to 12 January 2018) £990.00 (+ VAT)

General Rate  (enrolments post 12 January 2018) £1200 (+VAT)

Prices include attendance to the seminar, both soft and hard copy of the extensive seminar materials, lunch and morning/afternoon tea/coffee.

All seminars will have strictly limited places to ensure a quality event for all attendees and all venues are booked with only small classes in mind. So I recommend booking early (as it’s cheaper) and to avoid possible disappointment. I won’t get to run these kind of events in the UK very often so take advantage of attending what will be a unique training opportunity while you can.

If you have any questions, please leave a comment or contact me at richard@richardfooteconsulting.com.

Hope to see you at one of these events next year !!

 

Indexing Seminar: Why Small Classes? (A Small Plot Of Land) October 5, 2017

Posted by Richard Foote in Index Internals, Index Internals Seminar, Oracle Indexes, Richard Foote Consulting, Richard's Musings.
2 comments

outside2

Firstly, a huge thank-you to everyone who has enroled in one of my “Indexing Internals and Best Practices” Seminars running soon is Australia. The feedback and overall reaction has been extremely positive and I can’t wait to present all the material I’ve been putting together these past few weeks.

There are currently limited places still available at each of the 6 venues, but places are selling out fast. All enrolment details can be found here: https://www.eventbrite.com.au/o/richard-foote-consulting-pty-ltd-15076769762

A question I’ve been asked a couple of times is why the insistence on only running small classes? I’ve previously run similar events overseas to large audiences, I’ve had classes of 40+ a number of times in Europe and I’ve run a one day version of this at Hotsos last year to more people than I could count (I’m guessing 80+). And while they were all highly successful, I will be restricting classes to much smaller class sizes in this Australia tour and into the future.  There are a number of reasons for this, with the key points being:

  • The seminar has been fully revised and updated and is packed with heaps of new content. I want to be able to cover as much material as is practical and this is only possible with small classes. The more I cover, the more attendees have the opportunity to learn.
  • I really want these seminars to be interactive events, with attendees having plenty of opportunity to ask questions. This makes it a more valuable experience as any ambiguities, doubts, environment specific questions, important bits that people don’t get first time, etc. etc. can clarified and addressed.
  • The seminar goes for 2 days, but if I had 50 people and each person asked 15 mins of questions that needed answering, that’s almost the entire time gone. So larger class sizes makes it impractical to have any meaningful Q&A
  • Attendees bring vast amounts of experiences and solutions to Oracle Indexing related scenarios as all Oracle databases out there utilise indexes. Even on Exadata. Even in future autonomous databases. Small classes gives me the opportunity to tap into this.
  • I want to learn just as much as everybody else and if I do all the talking, I’ll learn nothing new. Small classes gives me the opportunity to learn, based on questions asked and experiences shared.
  • All attendees have their own requirements and expectations from attending the seminar. If someone has specific issues at their environments, what a wonderful opportunity to learn and perhaps get these issues addressed by attending the seminar. Again only practical in small class environments.
  • I will be contacting all attendees BEFORE the seminars to determine what their individual expectations are from the seminars. I will then ensure that any appropriate material is fully covered to meet those expectations. This again is only practical with small class sizes.
  • Finally, you’re spending 2 days with the one and only Richard Foote 🙂 As well as learning more about indexes than you thought possible, as well as learning how an appropriate indexing strategy can dramatically improve database/application performance, as well as discovering an appreciations for the works of David Bowie, it’s also an opportunity to have a sense of 1 on 1 with myself  and that I’m there to directly assist “you” with truly understanding how Oracle indexes should be properly utilised. There is little sense of 1 on 1 with large class sizes.

 

So these seminars are designed very intentionally to be “premium” events, offering a training experience that really is second to none. These seminars have always been very well received so I’m truly excited that I’ll be running these events with the best content I’ve ever compiled. Small class settings gives all attendees the very best opportunity to absorb it all in and apply directly what they’re learnt to improving the performance of their database/application environments..

Like I said, there are only limited places now remaining for the Australian Tour so get in early or else risk missing out.

Events in other parts of our beautiful planet are currently being planned and will be announced in the near future. If you have any questions or requests on where to run these seminars, just ask: richard@richardfooteconsulting.com

Oracle Indexing Myths (Telling Lies) September 26, 2017

Posted by Richard Foote in Oracle Indexes.
2 comments

DavidBowie-TellingLies-CD

I’m currently hard at work fully revising and updating my “Indexing Internals and Best Practices” seminar in time for a series of events I’ll be running in Australia in October/November. This has been a highly popular and acclaimed 2 day seminar that is a must for any DBA, Developer, Solutions Architect or anyone else interested in designing, developing or maintaining high performance Oracle-based databases/applications.

For a number of reasons, there are many myths in relation to how Oracle Indexes function, how they should be maintained and how and when indexes should be implemented. One of the goals of my seminar is to go through and dispel many of these myths and demonstrate how these misconceptions can unfortunately often result in inappropriate and inefficient indexing strategies being deployed. I have investigated numerous customer databases (including large scale Exadata sites) where the database is running in a severely sub-optimal manner as a direct result of  inappropriate indexing implementations based on these myths.

Some of these misconceptions are just plain wrong, some are true in other databases but not Oracle, some might have once been true but not anymore, some might be mostly true but not always, some might be mostly false but not always and it’s often these exceptions that can make such an important difference in how indexes could/should be deployed in some environments. Words such as “never” ,”always”, “all” and “none” are dangerous to use when discussing technology as exceptions, caveats and limitations can impact/distort the truth and how such technology should be implemented.

My use of the term “Best Practices” in the seminar title is actually a deliberate, somewhat cynical reuse of a term that actually gets many customers in strife as what might be a so-called “Best Practice” in one environment might be totally inappropriate in another environments.

In no particular order, the following Oracle Indexing “myths” (or misleading generalisations) are discussed throughout my seminar and are still prevalent out there in the Oracle universe:

  • Oracle B-Tree indexes become “unbalanced” over time and need to be rebuilt
  • Deleted space in index is “deadwood” and over time requires index to be rebuilt
  • If index height greater than ‘x’, it becomes inefficient and needs to be rebuilt
  • If index grows to 2x its height, it is 2x more costly to use
  • PCTFREE enables space for index entries to grow within current leaf block
  • If index has a poor (very high) Clustering Factor, rebuild the index
  • To improve the Clustering Factor, you have to rebuild the underling table
  • Clustering Factor “as good as it gets” after gathering 100% estimate index statistics
  • To improve performance, regularly rebuild indexes
  • You never have to rebuild an index to improve performance
  • Statistics from INDEX_STATS provides reliable metrics on when to rebuild an index
  • If delete rows as reported in INDEX_STATS > x%, rebuild
  • If delete rows as reported in INDEX_STATS < x%, don’t rebuild
  • Analyze Index Validate Structure is a safe method of collecting index metrics
  • Index rebuilds are inexpensive and unobtrusive
  • Primary/Unique Key constraints require a unique index
  • Drop/disable a constraint, unique index policing index is automatically dropped
  • All Foreign Key constraints must be indexed
  • Indexes should eliminate sorting
  • Only indexed columns require statistics
  • Bitmap Indexes only useful with low cardinality columns
  • Bitmap Index will be used when a B-tree is not for low cardinality columns
  • Null values are not indexed
  • Small tables (say < 100 rows) don’t benefit from indexing
  • Separating indexes from tables in tablespaces improves performance
  • Range scans not possible with Reverse Key indexes
  • Local indexes improve performance
  • Put most discriminating column first in concatenated indexes
  • If SQL references all columns in index, index column order is irrelevant
  • If leading column of index is not referenced in SQL, index not considered by CBO
  • Monitoring Indexes will highlight which indexes can be safely dropped
  • Indexing Tracking will highlight which indexes can be safely dropped
  • Index Compression make indexes smaller
  • B-Tree Index only useful with high cardinality columns
  • Pointless indexing a column with one distinct value
  • If more than x% rows returned, index is inappropriate, where x% between 0 & 100
  • Full Table Scan more efficient than index range scan with table access, when returning 100% of data
  • The CBO cost an internal value of no practical use for tuning/comparison purposes
  • Index is best solution to return 0% of data
  • You don’t need indexes in Exadata
  • Indexes less important in Exadata, as Storage Indexes can take over if database index is missing

It’s important to note it potentially only takes just the one bad row inserted in a table, one inaccurate statistic, one index being used inappropriately, one missing index not created during a application upgrade, to bring down a whole Oracle database, RAC nodes running on an Exadata included…

If you want to learn why the above statements are misleading and lots, lots, lots more, come and attend my Indexing Seminar. Tickets for the Australian events are on sale here: https://www.eventbrite.com.au/o/richard-foote-consulting-pty-ltd-15076769762

Dates for other regions will be announced in the near future: https://richardfooteconsulting.com/seminardates/

If you wish to express an interest in attending a seminar near you, please email: richard@richardfooteconsulting.com.

 

 

 

“Oracle Indexing Internals & Best Practices” Seminar: Australia 2017 Tour (Stage) September 19, 2017

Posted by Richard Foote in Index Internals Seminar, Oracle Indexes, Richard Foote Consulting.
1 comment so far

seminar photo

I’ll be running a fully revised and updated version of my acclaimed “Oracle Indexing Internals and Best Practices” seminar throughout Australia in Oct/Nov 2017. Previous versions of this seminar have been hugely popular and run in some 18 countries. This updated version will feature lots of new material including 12c related indexing capabilities and recommendations.

Indexes are fundamental to every Oracle database and are crucial for optimal performance. However, there’s an incredible amount of misconception, misunderstanding and pure myth regarding how Oracle indexes function and should be maintained. Many applications and databases are suboptimal and run inefficiently primarily because an inappropriate indexing strategy has been implemented.

This seminar examines most available Oracle index structures/options and discusses in considerable detail how indexes function, how/when they should be used and how they should be maintained. A key component of the seminar is how indexes are costed and evaluated by the Cost Based Optimizer (CBO) and how appropriate data management practices are vital for an effective indexing strategy.  It also covers many useful tips and strategies to maximise the benefits of indexes on application/database performance and scalability, as well as in maximising Oracle database investments. Much of the material is exclusive to this seminar and is not generally available in Oracle documentation or in Oracle University courses.

This is a must attend seminar of benefit to DBAs, Developers, Solution Architects and anyone else interested in designing, developing or maintaining high performance Oracle-based applications. It’s a fun, but intense, content rich seminar that is suitable for people of all experiences (from beginners to seasoned Oracle experts). All seminars are small class environments, with plenty of opportunity for attendees to ask questions specific to their particular environment.

Full seminar details can be found on my new website: https://richardfooteconsulting.com/seminardates

The full Australian itinerary and enrolment details as follows:

Perth 30-31 October 2017: For full details and how to enrol (enrolments close on 22 October or when event is full): https://www.eventbrite.com.au/e/oracle-indexing-internals-and-best-practices-seminar-with-richard-foote-tickets-37783577698

Adelaide 1-2 November 2017: For full details and how to enrol (enrolments close on 22 October or when event is full): https://www.eventbrite.com.au/e/oracle-indexing-internals-and-best-practices-seminar-with-richard-foote-tickets-37793217531

Canberra 6-7 November 2017: For full details and how to enrol (enrolments close on 28 October or when event is full): https://www.eventbrite.com.au/e/oracle-indexing-internals-and-best-practices-seminar-with-richard-foote-tickets-37793625752

Brisbane 8-9 November 2017: For full details and how to enrol (enrolments close on 1 November or when event is full): https://www.eventbrite.com.au/e/oracle-indexing-internals-and-best-practices-seminar-with-richard-foote-tickets-37793828358

Melbourne 13-14 November 2017: For full details and how to enrol (enrolments close on 4 November or when event is full): https://www.eventbrite.com.au/e/oracle-indexing-internals-and-best-practices-seminar-with-richard-foote-tickets-37794100171

Sydney 15-16 November 2017: For full details and how to enrol (enrolments close on 7 November or when event is full): https://www.eventbrite.com.au/e/oracle-indexing-internals-and-best-practices-seminar-with-richard-foote-tickets-37794680908

Note: Numbers are strictly limited due to the small class nature of the seminars. Please book early to avoid disappointment as seminars are not scheduled regularly.

 

For additional information on seminar content, upcoming seminars or training requests, contact me at richard@richardfooteconsulting.com.

 

 

Richard Foote Consulting Has Arrived (New Angels Of Promise) September 18, 2017

Posted by Richard Foote in Index Internals Seminar, Oracle Indexes, Richard Foote Consulting.
6 comments

Today is the official launch of Richard Foote Consulting, my new independent company in which I’ll be providing specialist Oracle Database consulting and training services, focusing on database performance tuning, database problem resolutions and database health check assessments (at least until everyone moves across to the new self-tuning Oracle Cloud database). Based on all the interest I’ve already received, this journey should be a blast.

For all the details, please visit the new website: richardfooteconsulting.com.

I will also be running training seminar events, starting with a fully revised and updated version of the very popular “Oracle Indexing Internals and Best Practices” seminar. The first series will be an Australian Tour in October/November. For all currently scheduled seminars, see my Seminar Dates Page or just check out the Blog slide bar for a list of upcoming seminars.

And most important of all, I might now have time to post blog articles more regularly 🙂

For information on upcoming seminars, consulting services, rates or availability, please email richard@richardfooteconsulting.com.

Presenting at UKOUG Tech17 Conference in December (Here Come The Warm Jets) August 30, 2017

Posted by Richard Foote in Tech17.
1 comment so far

I presented at the UKOUG Tech14 conference in Liverpool and thoroughly enjoyed the experience. It was one of the better User Group conferences I’ve attended and winning both the Best New Oracle Speaker and Best Oracle Speaker awards made it that bit more special.

So it’s with real excitement that I have the opportunity to again present at the UKOUG Tech17 conference in Birmingham 4-6 December 2017.

I have 4 sessions scheduled:

Real World Examples Of Using AWR Reports To Solve Performance Issues (Double Session): Monday, 4th December 16:55-18:40

CBO Panel: Tuesday, 5th December 9:00-9:50

New Indexing Features Introduced in Oracle Database 12c Release 2: Tuesday, 5th December 12:25-13:15

Indexing Q&A With Richard Foote: Tuesday, 5th December 15:40-16:30

 

Looking forward to catching up with lots of my Oracle friends on what is shaping up to be another fabulous conference. See you in sunny and extremely warm Birmingham, I hope !!

 

 

Goodbye Oracle Corporation (Modern Love) August 14, 2017

Posted by Richard Foote in Richard's Musings.
8 comments

 

It’s with a mixture of excitement, trepidation and a touch of sorrow that I today handed in my resignation after 6 years at Oracle Corporation.

My plans at this stage are to take a bit of time-off initially, before going back to providing Oracle Database consulting and training services as I’ve done in the past. Stay tuned for some exciting offerings !!

It’s truly been a great pleasure and honour to have worked with such a fantastic team at Oracle Australia. I sincerely wish Oracle continued success into the future.