jump to navigation

Presenting at UKOUG Techfest19 Conference in Brighton, UK September 13, 2019

Posted by Richard Foote in Techfest19.
add a comment

I’m very excited to be attending my 3rd UKOUG Conference, this year re-badged as Techfest19. The fact it’s being held in Brighton is a little disconcerting for a Crystal Palace fan, but really looking forward nonetheless to what has always been one of the very best Oracle conferences on the yearly calendar.

I have a number of presentations and Roundtable sessions that I’ll be participating in (follow the links to find the session abstracts):

 

I was very honoured after my 2nd attendance in Birmingham to have been awarded a “Lifetime Achievement Award” by the UKOUG, so I feel the duty bound to make sure my presentations are “top-shelf”.

Really looking forward to catching up with all my UK/European Oracle friends, please say hello if you see me (I’ll be the one wearing the Crystal Palace football jersey) ūüôā

 

 

 

 

Announcement: Australia/NZ “Let’s Talk Database” Events October 2019 !! September 12, 2019

Posted by Richard Foote in 19c, Automatic Indexing, Exadata X8, Let's Talk Database.
add a comment

I’ve very excited to announce the next series of Oracle “Let’s Talk Database” events to be run throughout Australia and New Zealand in October 2019.

I’ll be discussing two exciting topics this series, “Oracle Database 19c New Features” and “Oracle Exadata X8“.¬†As always, these sessions run between 9am-1pm, include a networking lunch and are free, but you MUST register to attend.

Dates, locations and registration links are as follows (Note the Sydney location is NOT the Oracle office in North Ryde):

Canberra:     22 October РRegistration Link (Oracle Canberra Office)

Sydney:       23 October РRegistration Link (Stone and Chalk, York St)

Melbourne: 24 October – Registration Link (Oracle Melbourne Office)

Brisbane:     29 October РRegistration Link (Oracle Brisbane Office)

Auckland:    30 October РRegistration Link (Oracle Auckland Office)

Wellington: 31 October – Registration Link (Oracle Wellington Office)

 

Session details as follows:

Oracle Database 19c New Features

The latest Oracle Database Release 19c has introduced many exciting new features and enhanced capabilities that will be of much interest to both DBAs and Developers. This session will discuss in some detail a number of these new features with practical examples on how they can assist Oracle professionals maximize the benefits of the Oracle Database, especially in relation to Oracle Cloud and Oracle Engineered Systems deployments. New features discussed include Hybrid Table Partitions, Active Standby DML Redirect, Real Time Statistics, High-Frequency Automatic Optimizer Statistics Collection, SQL Quarantine,  new JSON Enhancements, DISTINCT option for LISTAGG aggregate and the most exciting new feature for some time, Automatic Indexing.

 

Oracle Exadata X8

The Oracle Exadata X8 Database Machine is the latest release in Oracle’s engineered systems platform designed specifically to deliver dramatically better performance, cost effectiveness, and availability for Oracle databases. This session will discuss various expanded and new capabilities introduced with Oracle Exadata X8 such as the new Exadata Extended Storage server, automated CPU, Memory and Network monitoring, advanced intrusion detection and Docker support. The session also examines why the Exadata Platform is so critical for the Autonomous Database Cloud Services and the unique Oracle Database19c capabilities such as Memoptimized Rowstore, Automatic Indexing, Real-Time Statistics and SQL Quarantine that are supported on Exadata.

 

So plenty of exciting Oracle database stuff to discuss. Hope to catch you at one of these events !!

Oracle Database 19c Automatic Indexing: Default Index Column Order Part II (Future Legend) September 11, 2019

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Clustering Factor, Index Column Order.
add a comment

In Part I, we explored some options that Oracle might adopt when ordering the columns within an Automatic Index by default, in the absence of other factors where there is only the one SQL statement to be concerned with.

A point worth making is that if all columns of an index are specified within SQL equality predicates, then the ordering of columns within an index is of little consequence. I’ve discussed this point a number of times previously.

Let’s explore if perhaps the resultant Clustering Factor of an index might be a factor in the default Automatic Index column order.

I begin by creating a table that has two columns of interest, CODE1 which is poorly clustered and CODE2 which is very well clustered:

SQL> create table muse (id number, code2 number, code1 number, name varchar2(42));

Table created.

SQL> create sequence muse_seq;

Sequence created.

SQL> create or replace procedure pop_muse as
begin
  for code1_value in 1..10000 loop
     for i in 1..100 loop
        insert into muse values (muse_seq.nextval, ceil(dbms_random.value(0,100)), code1_value, 'Back Holes');
     end loop;
   end loop;
   commit;
end;
/

Procedure created.

SQL> exec pop_muse

PL/SQL procedure successfully completed.

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

 

We then run the following query in which to hopefully create an Automatic Index on both CODE1 and CODE2 columns:

 

SQL> select * from muse where code1=406 and code2=83;

15 rows selected.

 

If we wait for the Automatic Index to be created and check out the Automatic Index report:

 

INDEX DETAILS
-------------------------------------------------------------------------------
  1 The following indexes were created:
*: invisible

----------------------------------------------------------------------------
| Owner | Table | Index                | Key         | Type   | Properties |
----------------------------------------------------------------------------
| BOWIE | MUSE  | SYS_AI_c1m8fkukj1368 | CODE2,CODE1 | B-TREE | NONE       |
----------------------------------------------------------------------------

VERIFICATION DETAILS
-------------------------------------------------------------------------------
1 The performance of the following statements improved:
-------------------------------------------------------------------------------

Parsing Schema Name  : BOWIE

SQL ID               : 0pdqsvpggupnz

SQL Text             : select * from muse where code1=406 and code2=83

Improvement Factor   : 4092.8x

 

SQL> select index_name, column_name, column_position from user_ind_columns
     where table_name='MUSE' order by index_name, column_position;

INDEX_NAME             COLUMN_NAME          COLUMN_POSITION
---------------------- -------------------- ---------------
SYS_AI_c1m8fkukj1368   CODE2                              1
SYS_AI_c1m8fkukj1368   CODE1                              2

 

We notice the index is created in CODE2, CODE1 column order.

If we create a manual index with the column order reversed:

 

SQL> create index muse_code1_code2_i on muse(code1, code2);

Index created.

SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor
     from user_indexes where table_name='MUSE';

INDEX_NAME             AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
---------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------
SYS_AI_c1m8fkukj1368   YES NO  VISIBLE   DISABLED      VALID       1000000        2506            362900
MUSE_CODE1_CODE2_I     NO  NO  VISIBLE   DISABLED      VALID       1000000        2510            129878

 

We notice that the manual index has the better resultant Clustering Factor. So the Clustering Factor doesn’t appear to be a factor in Automatic Index column order (no pun intended).

If we re-create the initial table in Part I, but this time with the columns defined in the table in reverse order:

 

SQL> create table major_tom3 (id number, code3 number, code2 number, code1 number, name varchar2(42));

Table created.

SQL> insert into major_tom3 select rownum, mod(rownum, 1000)+1, ceil(dbms_random.value(0, 100)), ceil(dbms_random.value(0, 10)),
'David Bowie' from dual connect by level  commit;

Commit complete.

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

PL/SQL procedure successfully completed.

 

If we again run the following query:

 

SQL> select * from major_tom3 where code3=4 and code2=42 and code1=42

...

 

And wait for the Automatic Index to be created and look at the resultant report:

 

INDEX DETAILS

-------------------------------------------------------------------------------
   1 The following indexes were created:
---------------------------------------------------------------------------------------
| Owner | Table      | Index                | Key               | Type   | Properties |
---------------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM3 | SYS_AI_g6sw030tg5ba9 | CODE3,CODE2,CODE1 | B-TREE | NONE       |
---------------------------------------------------------------------------------------

VERIFICATION DETAILS
-------------------------------------------------------------------------------
   1 The performance of the following statements improved:
-------------------------------------------------------------------------------

Parsing Schema Name  : BOWIE

SQL ID               : 22kts3uwj7kma

SQL Text             : select * from major_tom3 where code3=4 and code2=42 and code1=42

Improvement Factor   : 45854.1x

 

SQL> select i.index_name, i.column_name, i.column_position, t.num_distinct
from user_ind_columns i, user_tab_columns t
where i.table_name = t.table_name and i.column_name = t.column_name and i.table_name='MAJOR_TOM3'
order by i.index_name, i.column_position;

INDEX_NAME           COLUMN_NAME     COLUMN_POSITION NUM_DISTINCT
-------------------- --------------- --------------- ------------
SYS_AI_g6sw030tg5ba9 CODE3                         1         1000
SYS_AI_g6sw030tg5ba9 CODE2                         2          100
SYS_AI_g6sw030tg5ba9 CODE1                         3           10

 

We notice that the resultant Automatic Index has been created in CODE3, CODE2, CODE1 order.

After creating many many Automatic Indexes under all sorts of different scenarios, the DEFAULT behaviour is for Oracle to create Automatic Indexes in Column ID order (the order in which they are defined in the table definition).

Of course as we’ll see in future posts, if there are several conflicting SQL predicates, there are various other factors that govern a more appropriate Automatic Index order, but the fact that Oracle creates Automatic Indexes in Column ID order in the absence of other factors is useful to know.

As I said previously, if all indexed columns are specified in SQL equality predicates, index column order has little consequence. But as we’ll see in the next post, there are scenarios where index column order can be very important and this default index column order may not be the most optimal…

London March 2020: “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Seminars !! September 3, 2019

Posted by Richard Foote in Oracle Index Seminar, Oracle Indexes, Oracle Performance Diagnostics and Tuning Seminar.
add a comment

seminar photo

It’s with great excitement that I announce I’ll finally be returning to London, UK in March 2020 to run both of my highly acclaimed seminars. The dates and registration links are as follows:

23-24 March 2020: “Oracle Indexing Internals and Best Practices” seminar – Tickets and Registration Link

25-26 March 2020: “Oracle Performance Diagnostics and Tuning” Seminar – Tickets and Registration Link

You can also purchase tickets to both seminars at a special 20% combo discount:

23-26 March 2020:¬† Both “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Seminars – Tickets and Registration Link

The cost for each individual seminar is:

  • Early Bird Rate¬†(enrollments prior to 31 January 2020)¬†¬£990.00 (+ VAT)
  • General Rate¬† (enrollments post 31 January 2020)¬†¬£1190 (+VAT)

The cost for the seminar combo is:

  • Early Bird Rate¬†(enrollments prior to 31 January 2020)¬†¬£1550.00 (+ VAT)
  • General Rate¬† (enrollments post 31 January 2020)¬†¬£1900 (+VAT)

 

The venue is the rather nice Hilton London Kensington.

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

Both seminars are very highly acclaimed, with past attendees universally applauding the quality and educational outcomes of the training.¬† They’re both aimed at Oracle Professionals (DBAs and Developers) who are interested in Performance Tuning and how to maximise the performance of both Oracle Databases and associated applications.

All the details of the Oracle Indexing Internals and Best Practices Seminar.

All the details of the Oracle Performance Diagnostic and Tuning Seminar.

Both seminars have strictly limited places to ensure a quality event for all attendees with venues booked with only small classes in mind. So I recommend booking early (as it’s cheaper) and to avoid possible disappointment. I don’t get to run these kind of events in the UK very often (it would be over 2 years since I last run seminars in London) so do 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 or both of these seminars next year !!

Oracle Database 19c Automatic Indexing: Default Index Column Order Part I (Anyway Anyhow Anywhere) September 2, 2019

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Index Column Order, Oracle Indexes.
1 comment so far

pin ups

The next thing I was curious about regarding Automatic Indexing was in which order would Oracle by default order the columns within an index. This can be a crucial decision with respect to the effectiveness of the index (but then again, may not be so crucial as well). Certainly one would expect the index column order be dependent on the SQL predicates running in the database and I’ll discuss all that in future posts, but what is the default behaviour here with regard index column order based (for now) on a single SQL predicate.

I could come up with a number of possible options that Oracle might adopt when determining the default index column order such as:

  • Column Name Order
  • Column ID Order
  • (Reverse) Column Cardinality Order
  • Best Clustering Factor
  • Other (Random even)

So to investigate this, I started with a basic table with 3 columns (CODE1, CODE2, CODE3) that had differing levels of cardinality:

SQL> create table major_tom (id number, code1 number, code2 number, code3 number, name varchar2(42));

Table created.

SQL> insert into major_tom select rownum, mod(rownum, 10)+1, ceil(dbms_random.value(0, 100)), ceil(dbms_random.value(0, 1000)), 'David Bowie' from dual connect by level  commit;

Commit complete.

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

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct, density from user_tab_columns where table_name='MAJOR_TOM';

COLUMN_NAME          NUM_DISTINCT    DENSITY
-------------------- ------------ ----------
ID                        9914368 1.0086E-07
CODE1                          10  .00000005
CODE2                         100  .00000005
CODE3                        1000       .001
NAME                            1          1

I then ran the following query with a predicate based on the 3 columns CODE1, CODE2 and CODE3:

SQL> select * from major_tom where code3=42 and code2=42 and code1=4;

15 rows selected.

Execution Plan
------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |    10 |   280 |  7354   (7)| 00:00:01 |
|   1 |  PX COORDINATOR              |           |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)        | :TQ10000  |    10 |   280 |  7354   (7)| 00:00:01 |
|   3 |    PX BLOCK ITERATOR         |           |    10 |   280 |  7354   (7)| 00:00:01 |
|*  4 |     TABLE ACCESS STORAGE FULL| MAJOR_TOM |    10 |   280 |  7354   (7)| 00:00:01 |
------------------------------------------------------------------------------------------

If we look at the resultant Automatic Index:

INDEX DETAILS

-------------------------------------------------------------------------------
1 . The following indexes were created:
--------------------------------------------------------------------------------------
| Owner | Table     | Index                | Key               | Type   | Properties |
--------------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM | SYS_AI_9mrs058nrg9d5 | CODE1,CODE2,CODE3 | B-TREE | NONE       |
--------------------------------------------------------------------------------------

 

SQL> select i.index_name, i.column_name, i.column_position, t.num_distinct
from user_ind_columns i, user_tab_columns t
where i.table_name = t.table_name and i.column_name = t.column_name and i.table_name='MAJOR_TOM'
order by i.index_name, i.column_position;

INDEX_NAME           COLUMN_NAME     COLUMN_POSITION NUM_DISTINCT
-------------------- --------------- --------------- ------------
SYS_AI_9mrs058nrg9d5 CODE1                         1           10
SYS_AI_9mrs058nrg9d5 CODE2                         2          100
SYS_AI_9mrs058nrg9d5 CODE3                         3         1000

 

We notice that the Automatic Index is in CODE1, CODE2, CODE3 order.

If we create a similar table, but this time have the columns with a different order of cardinality:

SQL> create table major_tom2 (id number, code1 number, code2 number, code3 number, name varchar2(42));

Table created.

SQL> insert into major_tom2 select rownum, mod(rownum, 1000)+1, ceil(dbms_random.value(0, 100)), ceil(dbms_random.value(0, 10)),
'David Bowie' from dual connect by level;

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

SQL> select * from major_tom where code3=42 and code2=42 and code1=4;

15 rows selected.

 

We notice that the resultant automatic index is still in the same CODE1, CODE2 and CODE3 order:

INDEX DETAILS

-------------------------------------------------------------------------------
1. The following indexes were created:
---------------------------------------------------------------------------------------
| Owner | Table      | Index                | Key               | Type   | Properties |
---------------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM2 | SYS_AI_7w9t3tt9u171r | CODE1,CODE2,CODE3 | B-TREE | NONE       |
---------------------------------------------------------------------------------------

 

SQL> select i.index_name, i.column_name, i.column_position, t.num_distinct
from user_ind_columns i, user_tab_columns t
where i.table_name = t.table_name and i.column_name = t.column_name and i.table_name='MAJOR_TOM2'
order by i.index_name, i.column_position;

INDEX_NAME           COLUMN_NAME     COLUMN_POSITION NUM_DISTINCT
-------------------- --------------- --------------- ------------
SYS_AI_7w9t3tt9u171r CODE1                         1         1000
SYS_AI_7w9t3tt9u171r CODE2                         2          100
SYS_AI_7w9t3tt9u171r CODE3                         3           10

 

So we can eliminate column cardinality as being a contributing factor in Oracle deciding in which manner to order the indexed columns.

Which is unfortunate as we’ll see in a future post when we decide to implement Oracle Index Compression with Automatic Indexing.

In the next post, we’ll explore further other considerations and confirm how Oracle does indeed decide to order columns within an Automatic Index by default.

Announcement: New “Oracle Indexing Internals and Best Practices” Webinar – 19-23 November 2019 in USA Friendly Time Zone September 2, 2019

Posted by Richard Foote in Indexing Webinar.
add a comment

I‚Äôm very excited to announce a new Webinar series for my highly acclaimed ‚ÄúOracle Indexing Internals and Best Practices‚ÄĚ training event, running between 19-23 November 2019¬†!!

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 sub-optimal and run inefficiently primarily because an inappropriate indexing strategy has been implemented.

This webinar 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 webinar 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.

For details of all the extensive content covered in the webinars, please visit my Indexing Seminar page.

The webinars will run for 4 hours each day, spanning a full week period (Monday to Friday) in a USA friendly time zone (it will actually be running Tuesday-Saturday in Australian time zones).

So that’s 15+ hours of extensive and practical content that will be 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.

The webinar series is scheduled as follows:

  • 19 ‚Äď 23 November 2019 (7am – 11am AEDT)

Note: Because of time zone differences, this will actually run between Monday 18 – Friday 22 November in the USA. The USA local running times will be between 3pm-7pm on the Eastern Coast and between 12pm-4pm on the Western Coast.

The cost of the 5 x day series will be $1500.00 Australian Dollars (+GST if applicable and attending from within Australia).

Note: Numbers are strictly limited to ensure the smooth running of these events and enable the opportunity for all attendees to ask questions. Some of my previous webinars have  officially been FULL, so please register early to avoid disappointment as webinars are not scheduled too regularly. 

Booking and Payment Instructions

You can pay for these webinars directly here if NOT attending from Australia:

Webinar Series: 19-23 November 2019 (7am AEDT – 11am AEDT): Buy Now Button

 

Alternatively if you’re attending from Australia or require an invoice, please email me at richard@richardfooteconsulting.comand I will send you an invoice with payment instructions. You can pay either by credit card via PayPal (you do not need a PayPal account for this), via a PayPal account or via direct bank transfer. Note: payment must be received before being registered for the webinar.

Once registered, you will be sent a unique link for each booking with instructions on how to attend the webinar. Prior to the webinar, you will also be sent a soft copy of the webinar materials, with 850+ pages of amazing content, that includes many useful tips and strategies to maximise the benefits of indexes on application/database performance and scalability.

Up to date details and terms and conditions can be found at my Indexing Webinar web page.

If you have any questions, please don’t hesitate to contact me.

Hopefully you can join us for what is always a rewarding training experience ūüôā