“Oracle Performance Diagnostics and Tuning” Seminar: Berlin, Germany 31 March – 1 April 2020 January 14, 2020
Posted by Richard Foote in Oracle Performance Diagnostics and Tuning Seminar.add a comment
I’m very excited to be running my high acclaimed “Oracle Performance Diagnostics and Tuning” Seminar in Berlin, Germany on 31 March – 1 April 2020 in conjunction with DOUG. If you enjoyed my “Oracle Indexing Internals and Best Practices” Seminar, you will simply love this seminar 🙂
This seminar details how to maximise the performance of both Oracle databases and associated applications and how to diagnose and address any performance issues as quickly and effectively as possible.
When an application suddenly runs “slow” or when people start complaining about the “poor performance” of the database, there’s often some uncertainty in how to most quickly and most accurately determine the “root” cause of any such slowdown and effectively address any associated issues. In this seminar, we explore a Tuning Methodology that helps Oracle professionals to both quickly and reliably determine the actual causes of performance issues and so ensure the effectiveness of any applied resolutions.
Looking at a number of real-world scenarios and numerous actual examples and test cases, this seminar will show participants how to confidently and reliably diagnose performance issues. The seminar explores in much detail the various diagnostics tools and reports available in Oracle to assist in determining any database performance issue and importantly WHEN and HOW to effectively use each approach. Additionally, participants are also invited to share their own database AWR reports where we can apply the principles learnt in diagnosing the performance of their actual databases/applications.
One of the more common reasons for poor Oracle performance is inefficient or poorly running SQL. This seminar explores in much detail how SQL is executed within the Oracle database, the various issues and related concepts important in understanding why SQL might be inefficient and the many capabilities and features Oracle has in helping to both resolve SQL performance issues and to maintain the stability and reliability of SQL execution.
It’s a fun, but intense, content rich seminar that is suitable for people of all experiences (from beginners to seasoned Oracle experts).
For all the details regarding seminar content, venue, costs and how to register, please visit: https://www.doag.org/de/eventdetails?tx_doagevents_single[id]=589552
Places are already filling up so please book early to avoid disappointment !!
Merry Christmas and Happy New Year !! (“Heroes”) December 24, 2019
Posted by Richard Foote in Oracle Indexes.4 comments
I would like to take this opportunity to wish all my readers a very Merry Christmas and a most happy, peaceful and prosperous New Year.
My gift this year is not David Bowie and Bing Crosby doing their famous Christmas duet but a performance by Bowie of his classic “Heroes” as featured on the same Bing Crosby Christmas Special in 1977.
Enjoy 🙂
London March 2020: “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Seminars December 19, 2019
Posted by Richard Foote in Richard Foote Consulting, Richard Foote Seminars.add a comment
Places are filling up, but there are still some available at both of my acclaimed seminars that I’ll be running in London, UK in March 2020. 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 – Indexed Column Reorder (What Shall We Do Now?) December 18, 2019
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Index Column Order, Index Column Reorder.2 comments
I previously discussed how the default column order of an Automatic Index (in the absence of other factors) is based on the Column ID, the order in which the columns are defined in the table.
But what if there are “other factors” based on new workloads and the original index column order is no longer optimal or appropriate ?
I’ll begin by creating a table, with the following key column defined in CODE1, CODE2, CODE3 order:
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 <= 10000000; 10000000 rows created. SQL> 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
If I now run the following query with predicates based on these three columns:
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 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage("CODE3"=42 AND "CODE2"=42 AND "CODE1"=4)
filter("CODE3"=42 AND "CODE2"=42 AND "CODE1"=4)
Statistics
----------------------------------------------------------
34 recursive calls
5 db block gets
45861 consistent gets
0 physical reads
1044 redo size
1087 bytes sent via SQL*Net to client
588 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
15 rows processed
After the default 15 minutes period in which the Automatic Index task is run, if we look at what Automatic Index has been created:
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 |
--------------------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID : ayuj12hggwrvc
SQL Text : select * from major_tom where code3=42 and code2=42 and code1=4
Improvement Factor : 45853.8x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 3103394 946
CPU Time (s): 3092860 1017
Buffer Gets: 596102 18
Optimizer Cost: 7354 18
Disk Reads: 0 2
Direct Writes: 0 0
Rows Processed: 195 15
Executions: 13 1
We can see Oracle has indeed created an Automatic Index (SYS_AI_9mrs058nrg9d5) in the default CODE1, CODE2, CODE3 order.
But if we now run a new query, based on a predicate on just the CODE3 column:
SQL> select * from major_tom where code3=42;
9961 rows selected.
Execution Plan
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 273K| 7354 (7)| 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 10000 | 273K| 7354 (7)| 00:00:01 |
| 3 | PX BLOCK ITERATOR | | 10000 | 273K| 7354 (7)| 00:00:01 |
|* 4 | TABLE ACCESS STORAGE FULL| MAJOR_TOM | 10000 | 273K| 7354 (7)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage("CODE3"=42)
filter("CODE3"=42)
Statistics
----------------------------------------------------------
8 recursive calls
4 db block gets
45853 consistent gets
0 physical reads
0 redo size
166137 bytes sent via SQL*Net to client
599 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9961 rows processed
We can see the CBO has NOT used the index, as the leading column of the existing index is not mentioned in the SQL predicate and the CBO deems an Index Skip Scan as too expensive:
If we now run an SQL with predicates based on just the CODE2 and CODE3 columns:
SQL> select * from major_tom where code3=42 and code2=42;
101 rows selected.
Execution Plan
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2800 | 7354 (7)| 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 100 | 2800 | 7354 (7)| 00:00:01 |
| 3 | PX BLOCK ITERATOR | | 100 | 2800 | 7354 (7)| 00:00:01 |
|* 4 | TABLE ACCESS STORAGE FULL| MAJOR_TOM | 100 | 2800 | 7354 (7)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage("CODE3"=42 AND "CODE2"=42)
filter("CODE3"=42 AND "CODE2"=42)
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
45853 consistent gets
0 physical reads
0 redo size
2281 bytes sent via SQL*Net to client
588 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
101 rows processed
The existing Automatic Index is again not used as the important CODE1 column which is the leading column of the index is not mentioned in the SQL predicates and the CBO deems an Index Skip Scan as too expensive.
I know from experience many DBAs would simply create a new index with CODE3, CODE2 as the leading columns. But what does Automatic Indexing do in the scenario?
SQL> select dbms_auto_index.report_last_activity() report from dual; REPORT -------------------------------------------------------------------------------- GENERAL INFORMATION ------------------------------------------------------------------------------- Activity start : 21-JUN-2019 02:39:32 Activity end : 21-JUN-2019 02:40:18 Executions completed : 1 Executions interrupted : 0 Executions with fatal error : 0 ------------------------------------------------------------------------------- SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 1 Indexes created (visible / invisible) : 1 (1 / 0) Space used (visible / invisible) : 243.27 MB (243.27 MB / 0 B) Indexes dropped (space reclaimed) : 1 (243.27 MB) SQL statements verified : 1 SQL statements improved : 0 SQL plan baselines created : 0 Overall improvement factor : 0x ------------------------------------------------------------------------------- SUMMARY (MANUAL INDEXES) ------------------------------------------------------------------------------- Unused indexes : 0 Space used : 0 B Unusable indexes : 0
We notice in the Automatic Indexing report it’s stating that one new index has been created BUT that one index has also been dropped.
INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
--------------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM | SYS_AI_00hxxxkgb821n | CODE3,CODE2,CODE1 | B-TREE | NONE |
--------------------------------------------------------------------------------------
1. The following indexes were dropped:
-------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
--------------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM | SYS_AI_9mrs058nrg9d5 | CODE1,CODE2,CODE3 | B-TREE | NONE |
--------------------------------------------------------------------------------------
SQL> select index_name, column_name, column_position from user_ind_columns where table_name='MAJOR_TOM' order by column_position;
INDEX_NAME COLUMN_NAME COLUMN_POSITION
-------------------- --------------- ---------------
SYS_AI_00hxxxkgb821n CODE3 1
SYS_AI_00hxxxkgb821n CODE2 2
SYS_AI_00hxxxkgb821n CODE1 3
Automatic Indexing has created a new index (SYS_AI_00hxxxkgb821n), with the columns in CODE3, CODE2, CODE1 order, as this index is able to service all currently known SQL predicate combinations:
WHERE CODE3 = x AND CODE2 = y AND CODE1= z
WHERE CODE3 = x AND CODE2 = y
WHERE CODE3 = x
as the leading column in the index is listed in all three current scenarios.
This means the previous index in CODE1, CODE2, CODE3 order is now redundant as the new index can fully service all know SQL predicate combinations. As a result, Automatic Indexing drops the redundant index.
This is a really nice capability of Automatic Indexing, the ability to effectively reorder the columns within an index based on new workloads.
But what if subsequent new SQL workloads means the new index is not able on its own to service all such workloads. I’ll discuss this scenario in my next post.
Oracle Database 19c Automatic Indexing: Index Compression (Ghosteen) December 9, 2019
Posted by Richard Foote in 19c, 19c New Features, Advanced Index Compression, Automatic Indexing, AUTO_INDEX_COMPRESSION, Index Column Order, Index Compression, Index Internals.2 comments
In my previous post on Automatic Indexing, I discussed how the default index column order (in absence of other factors) is column id, the order in which the columns are defined in the table. In this post, I’ll explore if this changes if index compression is also implemented.
By default, Automatic Indexing does NOT use index compression. However, if you have access to the Advanced Compression option, you have the choice to turn on index compression in the following manner:
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_COMPRESSION','ON');
PL/SQL procedure successfully completed.
Note: the AUTO_INDEX_COMPRESSION parameter is not actually documented, which could be a documentation bug or that Oracle is not yet ready to release this capability. The above will enable Automatic Indexes to be created with Compress Advanced Low, which is the “no-brain” index compression option as it will compress (deduplicate) safely with negligible CPU overheads. However, index column order is still critical to ensure effective compression as we shall see…
We begin by creating a simple table, that has four columns of interest, CODE1, CODE2, CODE3 and STATUS. They are defined in this order within the table, but CODE1 has the most number of distinct values (5000000 distinct values), then CODE2 (1000), then CODE3 (10) and finally STATUS which only has the 1 distinct value.
SQL> create table bowie_compress (id number, code1 number, code2 number, code3 number, status varchar2(42), name varchar2(42)); Table created. SQL> insert into bowie_compress select rownum, mod(rownum, 5000000)+1, mod(rownum, 1000)+1, mod(rownum, 10)+1, 'COMPLETED’, 'David Bowie' from dual connect by level <= 10000000; 10000000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE_COMPRESS'); PL/SQL procedure successfully completed.
In terms of being the most efficient from a compression perspective, it would be better to have the index defined in STATUS, CODE3, CODE2, CODE1 order, so that the leading columns in the index have the most duplicated values that enable effective deduplication and hence index compression. I’ve discussed the importance of index column for effective index compression a number of times previously. Arguably, it would be better not to actually index the STATUS column at all as with just 1 distinct value, provides no effective filtering benefits.
Having the CODE1 column as the leading column however with so many distinct values would effectively make the index non-compressible (with LOW compression), as the leading column would have too many distinct values to benefit much from compression.
So how does Automatic Indexing handle this scenario? Does it keep the same default index column order or does it alter the index column order to provide better index compression benefits?
Let’s run the following SQL with all four columns in the predicates and see what index Automatic Indexing creates…
SQL> select * from bowie_compress where code1=42 and code2=42 and code3=2 and status='COMPLETED';
Execution Plan
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 9998 (5)| 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 41 | 9998 (5)| 00:00:01 |
| 3 | PX BLOCK ITERATOR | | 1 | 41 | 9998 (5)| 00:00:01 |
|* 4 | TABLE ACCESS STORAGE FULL| BOWIE_COMPRESS | 1 | 41 | 9998 (5)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage("CODE1"=42 AND "CODE2"=42 AND "CODE3"=2 AND "STATUS"='COMPLETED')
filter("CODE1"=42 AND "CODE2"=42 AND "CODE3"=2 AND "STATUS"='COMPLETED')
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
63562 consistent gets
0 physical reads
0 redo size
1038 bytes sent via SQL*Net to client
588 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
If we look at the Automatic Indexing report for the period in which the above SQL was run:
SQL> select dbms_auto_index.report_last_activity() report from dual; REPORT -------------------------------------------------------------------------------- GENERAL INFORMATION ------------------------------------------------------------------------------- Activity start : 18-JUL-2019 00:18:35 Activity end : 18-JUL-2019 00:19:58 Executions completed : 1 Executions interrupted : 0 Executions with fatal error : 0 ------------------------------------------------------------------------------- SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 1 Indexes created (visible / invisible) : 1 (1 / 0) Space used (visible / invisible) : 293.6 MB (293.6 MB / 0 B) Indexes dropped : 0 SQL statements verified : 1 SQL statements improved (improvement factor) : 1 (63563.9x) SQL plan baselines created : 0 Overall improvement factor : 63563.9x ------------------------------------------------------------------------------- SUMMARY (MANUAL INDEXES) ------------------------------------------------------------------------------- Unused indexes : 0 Space used : 0 B Unusable indexes : 0 ------------------------------------------------------------------------------- INDEX DETAILS ------------------------------------------------------------------------------- The following indexes were created: -------------------------------------------------------------------------------------------------- | Owner | Table | Index | Key | Type | Properties | -------------------------------------------------------------------------------------------------- | BOWIE | BOWIE_COMPRESS | SYS_AI_bkdhrsd29vd4f | CODE1,CODE2,CODE3,STATUS | B-TREE | NONE | --------------------------------------------------------------------------------------------------
We see that Automatic Index has created the index with all four columns from the SQL predicate in again the default column order as the column order as defined in the table (CODE1, CODE2, CODE3, STATUS). Even though Automatic Index Compression was enabled, it hasn’t considered the column cardinalities in its determination of best index column order.
Automatic Indexing has the tendency to index ALL columns specified in SQL predicates, regardless of whether all such columns provide filtering benefits AND does not consider the best column order from a compression perspective when determining index column order. So definitely room for improvement here methinks.
If we look at the definition and size of the resultant Automatic Index:
SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='BOWIE_COMPRESS'; INDEX_NAME AUT CON VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ---------------------------- --- --- --------- ------------- -------- ---------- ----------- ----------------- SYS_AI_bkdhrsd29vd4f YES NO VISIBLE ADVANCED LOW VALID 10000000 35451 10000000 SQL> select index_name, column_name, column_position from user_ind_columns where table_name='BOWIE_COMPRESS' order by index_name, column_position; INDEX_NAME COLUMN_NAME COLUMN_POSITION ---------------------------- --------------- --------------- SYS_AI_bkdhrsd29vd4f CODE1 1 SYS_AI_bkdhrsd29vd4f CODE2 2 SYS_AI_bkdhrsd29vd4f CODE3 3 SYS_AI_bkdhrsd29vd4f STATUS 4
We note the index has 35451 leaf blocks.
If we were to create the index manully in a more appropriate manner from a compression perspective, with the index columns defined in reverse order and also with another index without the redundant STATUS column:
SQL> create index bowie_compress_best_order_i on bowie_compress(status, code3, code2, code1) compress advanced low; Index created. SQL> create index bowie_compress_best_order2_i on bowie_compress(code3, code2, code1) compress advanced low; Index created. SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='BOWIE_COMPRESS'; INDEX_NAME AUT CON VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ---------------------------- --- --- --------- ------------- -------- ---------- ----------- ----------------- SYS_AI_bkdhrsd29vd4f YES NO VISIBLE ADVANCED LOW VALID 10000000 35451 10000000 BOWIE_COMPRESS_BEST_ORDER_I NO NO VISIBLE ADVANCED LOW VALID 10000000 23509 10000000 BOWIE_COMPRESS_BEST_ORDER2_I NO NO VISIBLE ADVANCED LOW VALID 10000000 23462 10000000
We notice the resultant indexes are substantially smaller, at just 23509 and 23462 leaf blocks respectively.
It could well be that Index Compression is not yet documented because Oracle appreciates that more work yet needs to be done to create indexes optimally from a compression perspective…
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 🙂
Oracle 19c Automatic Indexing: How Many Executions Does It Take? (One Shot) August 29, 2019
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Oracle Indexes.3 comments
One of the first questions I asked when playing with the new Oracle Database 19c Automatic Indexing feature was how many executions of an SQL does it take for a new index to be considered?
To find out, I create the following table:
SQL> create table bowie_one (id number constraint bowie_one_pk primary key, code number, name varchar2(42)); Table created. SQL> insert into bowie_one select rownum, mod(rownum, 1000000)+1, 'David Bowie' from dual connect by level SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE_ONE'); PL/SQL procedure successfully completed.
I then ran the following query just once and checked to see if the Automatic Indexing task would pick this execution up and consider building a new index:
SQL> select * from bowie_one where code=42;
10 rows selected.
Execution Plan
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 230 | 6208 (7)| 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 10 | 230 | 6208 (7)| 00:00:01 |
| 3 | PX BLOCK ITERATOR | | 10 | 230 | 6208 (7)| 00:00:01 |
|* 4 | TABLE ACCESS STORAGE FULL| BOWIE_ONE | 10 | 230 | 6208 (7)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage("CODE"=42)
filter("CODE"=42)
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
39000 consistent gets
0 physical reads
132 redo size
867 bytes sent via SQL*Net to client
588 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
The following Automatic Indexing report detailed the following:
SQL> select dbms_auto_index.report_last_activity() report from dual; REPORT -------------------------------------------------------------------------------- GENERAL INFORMATION ------------------------------------------------------------------------------- Activity start : 26-JUN-2019 13:03:30 Activity end : 26-JUN-2019 21:13:06 Executions completed : 24 Executions interrupted : 0 Executions with fatal error : 0 ------------------------------------------------------------------------------- SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 1 Indexes created (visible / invisible) : 1 (1 / 0) Space used (visible / invisible) : 184.55 MB (184.55 MB / 0 B) Indexes dropped : 0 SQL statements verified : 3 SQL statements improved (improvement factor) : 1 (19500x) SQL plan baselines created : 0 Overall improvement factor : 6.9x ------------------------------------------------------------------------------- SUMMARY (MANUAL INDEXES) ------------------------------------------------------------------------------- Unused indexes : 0 Space used : 0 B Unusable indexes : 0
So an index was indeed created. Later in the report:
INDEX DETAILS
-------------------------------------------------------------------------------
The following indexes were created:
-------------------------------------------------------------------------
-------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
-------------------------------------------------------------------------
| BOWIE | BOWIE_ONE | SYS_AI_5tabfu6wtkbdh | CODE | B-TREE | NONE |
-------------------------------------------------------------------------
-------------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID : 9n89axkwrvw4b
SQL Text : select * from bowie_one where code=42
Improvement Factor : 19500x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 198342 961
CPU Time (s): 187768 1112
Buffer Gets: 39000 13
Optimizer Cost: 6208 14
Disk Reads: 0 2
Direct Writes: 0 0
Rows Processed: 10 10
Executions: 1 1
So the above details that an index on the CODE column of the BOWIE_ONE table was indeed created after just 1 execution.
For those wondering, yes the Elaspsed and CPU times are actually in Microseconds (1 millionth of a second) and not in seconds as stated…
The final section of the report details:
PLANS SECTION
---------------------------------------------------------------------------------------------
- Original
-----------------------------
Plan Hash Value : 227986582
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6208 | |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 10 | 230 | 6208 | 00:00:01 |
| 3 | PX BLOCK ITERATOR | | 10 | 230 | 6208 | 00:00:01 |
| 4 | TABLE ACCESS STORAGE FULL | BOWIE_ONE | 10 | 230 | 6208 | 00:00:01 |
------------------------------------------------------------------------------------
Notes
-----
- dop_op_reason = scan of object BOWIE.BOWIE_ONE
- dop = 2
- px_in_memory_imc = no
- px_in_memory = no
- With Auto Indexes
-----------------------------
Plan Hash Value : 2734060610
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 230 | 14 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE_ONE | 10 | 230 | 14 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | SYS_AI_5tabfu6wtkbdh | 10 | | 3 | 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("CODE"=42)
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
It details that indeed, a new plan using the newly Automatic Index would be substantially more efficient.
If we look at details of the new Automatic Index:
SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='BOWIE_ONE'; INDEX_NAME AUT CON VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ---------------------- --- --- --------- ------------- -------- ---------- ----------- ----------------- BOWIE_ONE_PK NO YES VISIBLE DISABLED VALID 10000000 19642 57523 SYS_AI_5tabfu6wtkbdh YES NO VISIBLE DISABLED VALID 10000000 22285 10000000 SQL> select index_name, column_name, column_position from user_ind_columns where table_name='BOWIE_ONE' order by index_name, column_position; INDEX_NAME COLUMN_NAME COLUMN_POSITION ---------------------- --------------- --------------- BOWIE_ONE_PK ID 1 SYS_AI_5tabfu6wtkbdh CODE 1
The newly created Automatic Index is both Valid and Visible and so can be used globally within the database.
If I now re-run the original query:
SQL> select * from bowie_one where code=42;
10 rows selected.
Execution Plan
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 230 | 13 (0)| 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 10 | 230 | 13 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE_ONE | 10 | 230 | 13 (0)| 00:00:01 |
| 4 | BUFFER SORT | | | | | |
| 5 | PX RECEIVE | | 10 | | 3 (0)| 00:00:01 |
| 6 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 10 | | 3 (0)| 00:00:01 |
| 7 | PX SELECTOR | | | | | |
|* 8 | INDEX RANGE SCAN | SYS_AI_5tabfu6wtkbdh | 10 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("CODE"=42)
Statistics
---------------------------------------------------------
12 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
867 bytes sent via SQL*Net to client
588 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10 rows processed
The CBO now uses the newly created Automatic Index.
So it only potentially takes just the one execution of an SQL statement for an Automatic Index to be created.
Therefore some caution needs to be exercised in environments where there may be a very large number of ad-hoc queries where specific indexes may not be necessary for once only executed predicate combinations.
That said, the Automatic Indexing process is highly efficient in building only the bare minimum of column indexed combinations to cater for all known SQL predicates.
More on this in a future post.
Speaking at Trivadis Performance Days 2019 August 28, 2019
Posted by Richard Foote in Oracle Indexes, Performance Days 2019.add a comment
I’ll again be speaking at the wonderful Trivadis Performance Days 2019 conference in Zurich, Switzerland on 26-27 September.
There’s again another fantastic lineup of speakers, including:
- CHRISTIAN ANTOGNINI
- IVICA ARSOV
- MARK ASHDOWN
- SHASANK CHAVAN
- EMILIANO FUSAGLIA
- STEPHAN KÖHLER
- JONATHAN LEWIS
- FRANCK PACHOT
- TANEL PODER
- DANI SCHNIDER
I’ll be presenting two papers:
- “Oracle 18c and Oracle 19c New Indexing Features“
- “Improving Performance with Indexing and Partitioning“
For all the details, including the agenda and how to register: https://m.trivadis.com/performance-days-en
I can’t recommend this conference enough, one that focuses and specialises on improving performance in Oracle Database environments.
Oh, and the beer served is excellent as well.
Speaking at Oracle OpenWorld 2019 August 22, 2019
Posted by Richard Foote in OOW19, Oracle Indexes.add a comment
It’s been remarkably 9 years since I’ve been to Oracle OpenWorld, but will finally get the opportunity to present there again this year (with many thanks to the Oracle ACE Director program for making this possible).
Details of my presentation are as follows:
Conference: Oracle OpenWorld
Session Type: Conference Session
Session ID: CON1432
Session Title: Oracle Database 19c: In-Depth Look into the New Automatic Indexing Feature
Room: Moscone South – Room 152A
Date: 09/17/19
Start Time: 11:15:00 AM
End Time: 12:00:00 PM
I promise it to be a fast-paced, content rich, action-packed presentation that will leave you much wiser about the cool capabilities of Oracle Database 19c Automatic Indexing.
The current allocated room is almost full with pre-registrations so if you’re interested in learning how Oracle’s new Automatic Indexing feature works, I recommend registering for the session ASAP (or to keep following my blog).
I’m really looking forward to catching with many of my Oracle friends again, so please do stop by to say hi if you see me loitering around 🙂
For the record, I’ve hardly changed a bit in the intervening last 9 years 🙂
AUSOUG Connect 2019 Conference Series August 21, 2019
Posted by Richard Foote in AUSOUG, Connect 2019, Oracle Indexes.add a comment
AUSOUG will again be running their excellent CONNECT 2019 conference series this year at the following great venues:
- Monday 14th October – Rendezvous Hotel In Melbourne
- Wednesday 16th October – Mercure Hotel in Perth
As usual, there’s a wonderful lineup of speakers from both Australia and overseas including:
- Connor McDonald
- Scott Wesley
- Guy Harrison
- Jeffrey Kemp
- Francisco Munoz Alvarez
- Gavin Soorma
- Douglas Hood
- Charles Kim
- Craig Shallahamer
- David Peake
- Patrick Barel
- Christopher Jones
to name but a few. You can see the full list of speakers here.
I’ll will also be presenting my fully updated and revised “10 Things You Might Not Know About Oracle Indexes But Really Should” presentation, that covers the more important and recent indexing capabilities that are not so widely known or understood but can be critical for optimal database/application performance.
If you’re an Oracle professional based in Australia, this is definitely the Oracle conference for you. Hopefully, I’ll get to meet many of you at either Melbourne or Perth 🙂
For more information, including how to register, visit the Connect 2019 site here.
Richard Foote's Oracle Blog 