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):
- Monday, 2 December 15:45-16:30 – Optimizer Roundtable (with Jonathan Lewis, Christian Antognini, Maria Colgan and Nigel Bayliss)
- Tuesday, 3 December 11:00-11:45 – “Oracle Database 19c New Features“
- Tuesday, 3 December 15:45-16:30 – “Oracle Indexing Q&A with Richard Foote“
- Wednesday, 4 December 14:45-15:30 – “10 Things You Might Not Know But Really Should About Oracle Indexes“
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 (Level 13, AMP Centre, 29 Customs Street West)
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.4 comments
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 very well clustered and CODE2 which is poorly 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
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.3 comments
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): 
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 🙂
Richard Foote's Oracle Blog 