Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Truncate AWR tables (unsupported)

When WRH$ tables grow too large so that they cannot be purged

This is no supported, please look at the My Oracle Support notes for a supported way to purge AWR when going too big, like re-creating AWR (needs to start the database in restricted mode) or purging with the normal procedure (can be long as it runs a delete). And do not copy-paste my statements as this is just an example.

When some tables grow too large, the purge job does not work correctly (because some things like the partitioning are done at the end). Then SYSAUX grows. And worse: the next upgrade may take hours if it has to change something on the AWR tables.

Upgrade time

Here is an example of an upgrade from 11g to 19c which took hours. Here is how I open the upgrade logs with “less” for the Top-10 longest statement execution:

eval $( grep -n "^Elapsed:" catupgrd*.log | sort -rk2 | awk -F: 'NR<10{print "less +"$2" "$1";"}' )

Or this one to display them in order:

tac catupgrd*.log | awk '/^Elapsed:/{print x;x="";e=$2}e>"00:01:00"{x=e" "NR" "$0"\n"x}' | sort -r | more

Many hours were spend on updating AWR tables like a new index on WRH$_EVENT_HISTOGRAM, add a column on WRH$_SQLSTAT to count obsoletes, add In-memory columns in WRH$_SEG_STAT, and many new indexes. If AWR became too large, you should do something before the upgrade. For system.logmnrc_gtlo that will probably be another blog post.

Check AWR size

Here is how to check the size of AWR before the upgrade:

sqlplus / as sysdba @ ?/rdbms/admin/awrinfo

If the query never returns, then maybe it is too large…

Baselines

First I check that I didn’t explicitly create AWR baseline to keep old snapshots:

select * from dba_hist_baseline where baseline_type<>'MOVING_WINDOW';

If there are some, then what I’ll do will lose son information someone wanted to retain, so check before.

Create new partitions

For the partitioned ones, I can force the creation of a new partition so that I can, later, truncate only the old ones without losing the recent AWR snapshots:

alter session set "_swrf_test_action" = 72;

This is the workaround when the partitions were not created automatically.

Truncate all old partitions

After a while (run a few dbms_workload.create_snapshot) I truncate those old partitions. Here is how I generate the drop statement:

select 'alter table '||object_name||' truncate partition '||subobject_name||' update global indexes;'
from dba_objects where object_name like 'WRH$%' and object_type = 'TABLE PARTITION' and created;

I mention CREATED

But now remains a few big tables that cannot be partitioned.

Look at large tables in SYS for WRH$

If I am not sure about the statistics gathering, I run it explicitly in order to see the recent number of rows (but this can take long):

exec DBMS_STATS.GATHER_DICTIONARY_STATS;

I’m interested in those larger than 1 million rows. For the ones that are partitioned, I have truncated large partitions only. But for the non-partitioned ones, I’ll truncate the whole table — and then lose everything.

select owner,table_name,dbms_xplan.format_number(num_rows) num_rows,object_type,partition_name,(select count(*) from dba_tab_partitions p where s.owner=p.table_owner and s.table_name=p.table_name) part from dba_tab_statistics s where owner in ('SYS','SYSTEM') and table_name like 'WRH$%' and num_rows>1e6 order by s.num_rows;

Here is the result. Use the same query without the ‘WRH$’ pattern in order to see everything that may cause problems at upgrade time.

The column PART is the number of partitions. Those with 0 are not partitioned and then the truncate will remove all data.

Truncate large non-partitioned ones

Some tables are not partitioned and I truncate the largest ones (which I’ve identified from the query above). I prefer to limit them because:

  • Fresh snapshot information will be lost
  • Inconsistency (snapshots with no data)
-- this is my example, you may have different tables
--
truncate table WRH$_TABLESPACE_SPACE_USAGE update global indexes;
truncate table WRH$_EVENT_HISTOGRAM update global indexes;
truncate table WRH$_MUTEX_SLEEP update global indexes;
truncate table WRH$_ENQUEUE_STAT update global indexes;
truncate table WRH$_SYSMETRIC_SUMMARY update global indexes;
truncate table WRH$_BG_EVENT_SUMMARY update global indexes;
truncate table WRH$_SYSMETRIC_HISTORY update global indexes;
truncate table WRH$_SQL_BIND_METADATA update global indexes;
truncate table WRH$_SQL_PLAN update global indexes;

This is fast, but if you need to run this, better do it when there’s no snapshot gathering. And do not rely on my list: chose the largest ones you have.

Note that truncating WRH$_SQL_PLAN will remove all old SQL Plans. I usually rarely need to look at an old plan (better to tune the current one rather than looking at the past) but they may help sometimes to get the plan, with its outlines, that worked before. So, do not do that when you have performance instabilities. Or ensure that you have a SQL Tuning Set containing the critical queries.

Use regular purge

Now I want everything to be consistent. I determine the earliest snapshot I have that is fully consistent (gathered after my truncate table statements):

select dbid,min(snap_id) from WRH$_SQL_PLAN group by dbid;

I choose this table because it was the last one I truncated.

Then I run the supported purge procedure for those snapshots:

exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(0,177189,28047622);

This brings me back to a consistent set of data. And this should not take long because but I ensured that there is no more than one million rows in each table.

I split again to start clean:

alter session set "_swrf_test_action" = 72;

Maybe reduce the retention

As I had AWR growing too much, I reduced the retention:

exec DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE(8);
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>8*60*24);

I changed the baseline duration as it cannot be larger than the retention.

If you need larger retention, then maybe you should set up a Centralized AWR Warehouse.

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

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 […]

My OpenWorld!

Just a couple of weeks away, and my oh my, I am going to be busy at OpenWorld this year!

Of course, first cab off the block will be the OpenWorld swim! It it September and marvellously warm in San Fran, so what better year to come along and meet new friends, and get free coffee and donuts! Just grab a towel from the hotel and head down to the marina!

image

Register at the link below, so we know how many donuts to buy!!! Smile

https://www.facebook.com/events/2464419823596577/

 

Once we’re into the conference week, I’ve got some standard full length sessions, and the hot, quick, rapid-fire theatre style sessions for those with less patience! Here’s a peek at what I’ll be covering:

Ten Amazing SQL Features (session)

https://events.rainfocus.com/widget/oracle/oow19/catalogow19?search=dev4700

Sick and tired of writing thousands of lines of middle-tier code and still having performance problems? Let’s become fans once again of the database by being reintroduced to just how powerful the SQL language really is! Coding is great fun, but we do it to explore complex algorithms, build beautiful applications, and deliver fantastic solutions for our customers, not just to do boring data processing. By expanding our knowledge of SQL facilities, we can let all the boring work be handled via SQL rather than a lot of middle-tier code—and get performance benefits as an added bonus. This session highlights some SQL techniques for solving problems that would otherwise require a lot of complex coding.

The Best Database Feature Ever Invented (theatre)

https://events.rainfocus.com/widget/oracle/oow19/catalogow19?search=tht4798

For more than 20 years, the single feature that has set Oracle Database apart from all others is its “read consistency” implementation. We’ve all heard the mantra: Readers don’t block writers; writers don’t block readers. But such flexibility can lead developers down a rabbit hole of performance problems, incorrect code, and a corrupted data. This session peels back the covers on the most amazing feature that the database has ever had.

How to guarantee Slower and Less Secure Applications (theatre)

https://events.rainfocus.com/widget/oracle/oow19/catalogow19?search=tht4796

No matter how sophisticated the framework, at the very center of it, you are most probably doing data access with SQL. Do that right, and your application has a great chance of being a success. But what if you don’t like your job? What if you are looking for a change? This session covers the worst SQL processing approaches to make sure that you’ll be out of a job, and your company out of business in record time.

The Assassins Guide (theatre)

https://events.rainfocus.com/widget/oracle/oow19/catalogow19?search=tht4795

Kill someone’s session and they might lose vital data and/or their temper. But that’s not your fault right? After all, they were the ones slamming the server or locking other people out. But perhaps there are better methods at your disposal. This short session gives you a range of other options to harmlessly dispose of those pesky server killers.

Flashback – not just for DBAs (session)

https://events.rainfocus.com/widget/oracle/oow19/catalogow19?search=dev4702

Everyone thinks of Oracle Flashback as just for those “Oh no!” moments. But it is time for re-think. With the modern development paradigms of test-driven development, continuous integration and rapid deployment, Flashback might be the perfect technology for developers not just DBAs.

 

And of course, there will be fresh new look AskTOM stickers for your phone and laptop, so please come say “Hi” before or after sessions.

asktom_logo_new_template_fill

Ssshhhhh…. Bonus session coming!

Yes…it is still in planning, but with any luck, I will get even another session to share with you all!

See you at OpenWorld !!!

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

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 […]

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

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 […]

Little things worth knowing: keeping enq: TM enqueue at bay during direct path inserts

Direct path inserts are commonly found in processing where data are shifted from one source to another. There are many permutations of the theme, this post details the way SQL Loader (sqlldr) behaves.

I have previously written about sqlldr and concurrent direct path inserts. The behaviour for releases <= 12.1 is described in the first post, the most welcome changes in 12.2 went into the second post. Since the fastest way of doing something is not to do it at all, I thought it might be worth demonstrating a useful technique to keep the dreaded TM enqueue at bay. Please note that these do not only apply to sqlldr, inserts with the append hint for example behave the same way in my experience.

I used Oracle 19.4.0 on Oracle Linux 7 for this post, the content should apply to older database releases, too. The data model is once more provided by Swingbench, I’m using the SH benchmark this time. As I said on twitter yesterday, @TanelPoder scripts are simply too good to afford writing my own, so I’m using them in this post where possible.

Please note that I’m using partitioning in this post, which is a cost option on top of Enterprise Edition. Always ensure your system is properly licensed for features you use.

Preparations

Let’s assume for a moment that you would like to load another batch of records into the (Swingbench) sh.sales table. The table is range partitioned on time_id, and split into quarterly partitions – at least for the later ones. On my system I count 68 partitions, with the latest containing records up to January 1st 2014. I added a couple of partitions for the next 2 quarters to allow for the new data load.

SQL> select partition_name, partition_position,high_value 
  2  from user_tab_partitions where table_name = 'SALES' 
  3   and partition_position >= 68;

PARTITION_NAME     PARTITION_POSITION HIGH_VALUE
----------------- ------------------- -----------------------------------------------------------------------------------
SALES_Q4_2013                      68 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')   
SALES_Q1_2014                      69 TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')   
SALES_Q2_2014                      70 TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')   

I created CSV files of the data to be loaded by simply adding 3 and 6 months to the last populated partition.

spool /u01/app/oracle/admin/ORCL/sqlldr/sales_q1_2014.csv
select /*csv*/ 
 prod_id,
 cust_id,
 add_months(time_id,3) time_id,
 channel_id,
 promo_id,
 quantity_sold,
 seller,
 fulfillment_center,
 courier_org,
 tax_country,
 tax_region,
 amount_sold 
from sales partition (sales_q4_2013);

spool off
spool  /u01/app/oracle/admin/ORCL/sqlldr/sales_q2_2014.csv

select /*csv*/ 
 prod_id,
 cust_id,
 add_months(time_id,6) time_id,
 channel_id,
 promo_id,
 quantity_sold,
 seller,
 fulfillment_center,
 courier_org,
 tax_country,
 tax_region,
 amount_sold 
from sales partition (sales_q4_2013);

spool off
exit

Using the csv “hint” and executing this little script in sqlcl allows me to spool the files into the desired format (I made sure I have plenty of space available in /u01/app/oracle/admin). Now let’s try loading the data. As always, I use a control file although there might be better ways of doing this. It contains the following instructions:

options (direct=true,skip=1)
load data
append into table sales
fields terminated by ',' optionally enclosed by '"'
date format "dd-mon-yy"
trailing nullcols
(
  PROD_ID,                                                                        
  CUST_ID,                                                                        
  TIME_ID,                                                                        
  CHANNEL_ID,                                                                     
  PROMO_ID,                                                                       
  QUANTITY_SOLD,                                                                  
  SELLER,                                                                         
  FULFILLMENT_CENTER,                                                             
  COURIER_ORG,                                                                    
  TAX_COUNTRY,                                                                    
  TAX_REGION,                                                                     
  AMOUNT_SOLD
)

Test #1

Now let’s load the data! Since I’m not the most patient person I launched 2 sessions in parallel using a screen (1) session

$ sqlldr /@sh control=load_sales.ctl data=sales_q1_2014.csv log=load_q1.log bad=load_q1.bad direct_path_lock_wait=true

$ sqlldr /@sh control=load_sales.ctl data=sales_q2_2014.csv log=load_q2.log bad=load_q2.bad direct_path_lock_wait=true

While monitoring the load process I was surprised to see session 2 waiting:

SQL> select sid,seq#,event,state from v$session where program like '%sqlldr%';

       SID       SEQ# EVENT                                                            STATE
---------- ---------- ---------------------------------------------------------------- -------------------
      1228        578 SQL*Net more data from client                                    WAITED SHORT TIME
      1470         97 enq: TM - contention                                             WAITING

2 rows selected.

Each process should insert data into separate partitions, so why is there a wait? Maybe there is a clue in v$session_wait:

SQL> @sw 1470

    SID STATE   EVENT                                          SEQ# SEC_IN_WAIT P1                  P2                  P3                  P1TRANSL
------- ------- ---------------------------------------- ---------- ----------- ------------------- ------------------- ------------------- ------------------------------------------
   1470 WAITING enq: TM - contention                             98           6 name|mode=          object #= 78758     table/partition= 0  0x544D0006: TM mode 6
                                                                                0x00000000544D0006


1 row selected.

I noticed that p3 mentions a table or partition flag, which triggered my interest. After a little bit of research I found this section in the documentation: 12.3.2 Direct Path Load of a Partitioned or Subpartitioned Table which provides some clues. Object# 78758 is the table in my database:

SQL> @oid 78758

owner                     object_name                    object_type        SUBOBJECT_NAME                 CREATED             LAST_DDL_TIME       status    DATA_OBJECT_ID
------------------------- ------------------------------ ------------------ ------------------------------ ------------------- ------------------- --------- --------------
SH                        SALES                          TABLE                                             2019-08-29 15:14:53 2019-08-29 23:06:27 VALID

What if I tried to insert into a partition straight away?

Test #2

Let’s try that with the following 2 control files. The main differences are a) referencing the input file directly and b) the specification of the target partition in the append clause:

$ cat load_sales_q1_2014.ctl
options (skip=1,direct=true)
load data
infile  /u01/app/oracle/admin/ORCL/sqlldr/sales_q1_2014.csv
append into table sales partition (sales_q1_2014)
fields terminated by ',' optionally enclosed by '"'
date format "dd-mon-yy"
trailing nullcols
( 
  PROD_ID,
  CUST_ID,
  TIME_ID,
  CHANNEL_ID,
  PROMO_ID,
  QUANTITY_SOLD,
  SELLER,
  FULFILLMENT_CENTER,
  COURIER_ORG,
  TAX_COUNTRY,
  TAX_REGION,
  AMOUNT_SOLD
)

$ cat load_sales_q2_2014.ctl
options (skip=1,direct=true)
load data
infile  /u01/app/oracle/admin/ORCL/sqlldr/sales_q2_2014.csv
append into table sales partition (sales_q2_2014)
fields terminated by ',' optionally enclosed by '"'
date format "dd-mon-yy"
trailing nullcols
( 
  PROD_ID,
  CUST_ID,
  TIME_ID,
  CHANNEL_ID,
  PROMO_ID,
  QUANTITY_SOLD,
  SELLER,
  FULFILLMENT_CENTER,
  COURIER_ORG,
  TAX_COUNTRY,
  TAX_REGION,
  AMOUNT_SOLD
)

The slightly adjusted commands for sqlldr are as follows:

$ sqlldr /@sh control=load_sales_q1_2014.ctl log=load_q1.log bad=load_q1.bad direct_path_lock_wait=true

$ sqlldr /@sh control=load_sales_q2_2014.ctl log=load_q2.log bad=load_q2.bad direct_path_lock_wait=true

With the change in place I couldn’t notice any TM enqueues when running these in parallel:

SQL> select sid,seq#,event,state from v$session where program like '%sqlldr%';

no rows selected

SQL>
SQL> r
  1* select sid,seq#,event,state from v$session where program like '%sqlldr%'

       SID       SEQ# EVENT                                                            STATE
---------- ---------- ---------------------------------------------------------------- -------------------
      1251        627 direct path write temp                                           WAITED KNOWN TIME
      1488        475 PGA memory operation                                             WAITED SHORT TIME

SQL> r
  1* select sid,seq#,event,state from v$session where program like '%sqlldr%'

       SID       SEQ# EVENT                                                            STATE
---------- ---------- ---------------------------------------------------------------- -------------------
      1251        819 direct path write temp                                           WAITING
      1488        664 direct path write temp                                           WAITING

SQL> r
  1* select sid,seq#,event,state from v$session where program like '%sqlldr%'

       SID       SEQ# EVENT                                                            STATE
---------- ---------- ---------------------------------------------------------------- -------------------
      1251       1070 SQL*Net more data from client                                    WAITED SHORT TIME
      1488        906 direct path write temp                                           WAITED KNOWN TIME

SQL> r
  1* select sid,seq#,event,state from v$session where program like '%sqlldr%'

       SID       SEQ# EVENT                                                            STATE
---------- ---------- ---------------------------------------------------------------- -------------------
      1251       1332 db file sequential read                                          WAITED SHORT TIME
      1488       1143 library cache lock                                               WAITING

SQL> r
  1* select sid,seq#,event,state from v$session where program like '%sqlldr

       SID       SEQ# EVENT                                                            STATE
---------- ---------- ---------------------------------------------------------------- -------------------
      1251       1824 db file sequential read                                          WAITED SHORT TIME
      1488       1372 db file sequential read                                          WAITING

That looks ok, and switching back through both screen sessions I can see both invocations of sqlldr completed ok:

$ sqlldr /@sh control=load_sales_q1_2014.ctl log=load_q1.log bad=load_q1.bad direct_path_lock_wait=true

SQL*Loader: Release 19.0.0.0.0 - Production on Thu Aug 29 22:44:53 2019
Version 19.4.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct

Load completed - logical record count 652437.

Table SALES, partition SALES_Q1_2014:
  652437 Rows successfully loaded.

Check the log file:
  load_q1.log
for more information about the load.

$ sqlldr /@sh control=load_sales_q2_2014.ctl log=load_q2.log bad=load_q2.bad direct_path_lock_wait=true

SQL*Loader: Release 19.0.0.0.0 - Production on Thu Aug 29 22:44:54 2019
Version 19.4.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct

Load completed - logical record count 652437.

Table SALES, partition SALES_Q2_2014:
  652437 Rows successfully loaded.

Check the log file:
  load_q2.log
for more information about the load.

Summary

When you are trying to load data into a table concurrently using direct path inserts, you will have to wait on a TM enqueue unless your table is partitioned and you are specifying different partitions as the target in each process. More generally speaking, Oracle will hold a TM enqueue on the segment you are loading into, so 2 processes attempting a direct path insert into a (sub) partition will equally have to serialise.

NB: if you are using direct path mode for sqlldr and your target segment is indexed, make sure to read chapter 12 in the Database Utilities Guide to learn about the implications of direct path load and index maintenance.

Getting started with Hyper-V on Windows 10

Microsoft Windows 10 comes with its own virtualization software called Hyper-V. Not for the Windows 10 Home edition, though.

Check if you fulfill the requirements by opening a CMD shell and typing in systeminfo:

https://uhesse.files.wordpress.com/2019/08/systeminfo1.png?w=150&h=32 150w, https://uhesse.files.wordpress.com/2019/08/systeminfo1.png?w=300&h=64 300w, https://uhesse.files.wordpress.com/2019/08/systeminfo1.png?w=768&h=163 768w, https://uhesse.files.wordpress.com/2019/08/systeminfo1.png?w=1024&h=217 1024w, https://uhesse.files.wordpress.com/2019/08/systeminfo1.png 1070w" sizes="(max-width: 620px) 100vw, 620px" />

The below part of the output from systeminfo should look like this:

https://uhesse.files.wordpress.com/2019/08/systeminfo2.png?w=1240&h=168 1240w, https://uhesse.files.wordpress.com/2019/08/systeminfo2.png?w=150&h=20 150w, https://uhesse.files.wordpress.com/2019/08/systeminfo2.png?w=300&h=41 300w, https://uhesse.files.wordpress.com/2019/08/systeminfo2.png?w=768&h=104 768w, https://uhesse.files.wordpress.com/2019/08/systeminfo2.png?w=1024&h=138 1024w" sizes="(max-width: 620px) 100vw, 620px" />

If you see No there instead, you need to enable virtualization in your BIOS settings.

Next you go to Programms and Features and click on Turn Windows features on or off:

https://uhesse.files.wordpress.com/2019/08/turnfeatureson.png?w=1238&h=346 1238w, https://uhesse.files.wordpress.com/2019/08/turnfeatureson.png?w=150&h=42 150w, https://uhesse.files.wordpress.com/2019/08/turnfeatureson.png?w=300&h=84 300w, https://uhesse.files.wordpress.com/2019/08/turnfeatureson.png?w=768&h=215 768w, https://uhesse.files.wordpress.com/2019/08/turnfeatureson.png?w=1024&h=286 1024w" sizes="(max-width: 620px) 100vw, 620px" />

You need Administrator rights for that. Then tick the checkbox for Hyper-V:

https://uhesse.files.wordpress.com/2019/08/tickhyperv.png?w=150&h=135 150w, https://uhesse.files.wordpress.com/2019/08/tickhyperv.png?w=300&h=270 300w, https://uhesse.files.wordpress.com/2019/08/tickhyperv.png?w=768&h=692 768w, https://uhesse.files.wordpress.com/2019/08/tickhyperv.png?w=1024&h=923 1024w, https://uhesse.files.wordpress.com/2019/08/tickhyperv.png 1115w" sizes="(max-width: 620px) 100vw, 620px" />

That requires a restart at the end:

https://uhesse.files.wordpress.com/2019/08/restart.png?w=1240&h=980 1240w, https://uhesse.files.wordpress.com/2019/08/restart.png?w=150&h=119 150w, https://uhesse.files.wordpress.com/2019/08/restart.png?w=300&h=237 300w, https://uhesse.files.wordpress.com/2019/08/restart.png?w=768&h=607 768w, https://uhesse.files.wordpress.com/2019/08/restart.png?w=1024&h=809 1024w" sizes="(max-width: 620px) 100vw, 620px" />

Afterwards you can use the Hyper-V Manager:

https://uhesse.files.wordpress.com/2019/08/hypervmanager.png?w=1240&h=604 1240w, https://uhesse.files.wordpress.com/2019/08/hypervmanager.png?w=150&h=73 150w, https://uhesse.files.wordpress.com/2019/08/hypervmanager.png?w=300&h=146 300w, https://uhesse.files.wordpress.com/2019/08/hypervmanager.png?w=768&h=374 768w, https://uhesse.files.wordpress.com/2019/08/hypervmanager.png?w=1024&h=498 1024w" sizes="(max-width: 620px) 100vw, 620px" />

Hyper-V can do similar things than VMware or VirtualBox. It doesn’t play well together with VirtualBox in my experience, though: VirtualBox VMs refused to start with errors like “VT-x is not available” after I installed Hyper-V. I also found it a bit trickier to handle than VirtualBox, but that’s maybe just because of me being less familiar with it.

The reason I use it now is because one of our customers who wants to do an Exasol Administration training cannot use VirtualBox – but Hyper-V is okay for them. And now it looks like that’s also an option. My testing so far shows that our educational cluster installation and management labs work also with Hyper-V.

Oracle 19c Automatic Indexing: How Many Executions Does It Take? (One Shot)

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: I then ran the following query just once and checked to see […]

An Oracle Auto Index function to drop secondary indexes - what is a “secondary” index?

dbms_auto_index.drop_secondary_indexes

In 19.4 the Auto Index package has 4 procedure/functions:

  • CONFIGURE to set the Auto Index documented parameters
  • REPORT_ACTIVITY to get a CLOB about an Auto Index executions (statements analyzed, indexes created,…)
  • REPORT_LAST_ACTIVITY which calls the previous one for the last run only
  • DROP_SECONDARY_INDEX which is documented as “Deletes all the indexes, except the ones used for constraints, from a schema or a table.

What is a secondary index?

I have not used this term for a long time. First, I’ve learned it at school during my first databases classes. The course supposed that the table rows are stored ordered, on the primary key. Then, the index on the primary key is a bit special: it does not need to have an entry for each row. It can be “sparse”. A value between the two index entries will be physically between the two locations. This index on the primary key is the “primary” index. And of course, it is unique. The other indexes are called “secondary” and must be dense, with one entry for each row to address (scattered physically in the table) and can be unique or not.

Then I started to work on Oracle and forgot about all that: there’s no distinction between a “primary” and a “secondary” index. And there’s no need to create a “primary” one first. We can create many indexes, and later decide what will be the primary key. One of the indexes may be used to enforce the constraint if it starts with those columns, not even needed to be unique.

All indexes are “dense” with Oracle because there’s no physical order in a Heap Table. We can get an idea about “sparse” index when we think about the branches: they store only a min/max value for each leaf block. But they address a leaf block and not table blocks with full rows. Except when the leaves contain the full rows and there’s no additional Heap Table. This is the case with an Index Organized Table (IOT). The “sparse” branches and “dense” leaves are not the only analogy between Oracle IOT and the “primary” indexes. An IOT can be created only the primary key. It is a primary index. And the other indexes are secondary indexes: “dense”, addressing the IOT leaf blocks through its primary index.

There are other databases where the tables are clustered, must have a primary key defined at table creation time, enforced by the primary index. But Oracle, and all databases with heap tables, are different. Oracle does not constrain indexes to be primary or secondary, index definition is not dependent on physical storage. This is agility invented 40 years ago.

dbms_auto_index.drop_secondary_indexes

In the context of Auto Indexing, “secondary” indexes have a slightly different meaning. Of course, the index enforcing the primary key is not considered as “secondary”. But the idea of “secondary” goes further: all indexes that are not required by integrity constraints. This is the idea which started on Exadata where analytic workloads may not need indexes thanks to SmartScan. It continued with the Autonomous DataWarehouse cloud service, where the CREATE INDEX was not available — allowing only implicit indexes created by primary key or unique constraint. Now it goes to OLTP where indexes do not need to be created but the optimizer will create them automatically.

Here we can imagine that this Auto Index function is there to drop all the indexes created for performance reasons, so that the Auto Index feature can create the required ones only. But what about the indexes created on foreign key columns to avoid a table lock when the parent key is deleted? We don’t want to drop them, right? It is a secondary index, but can it be considered as “used for constraints” even if it is not referenced in the dictionary by DBA_CONSTRAINTS.INDEX_NAME?

I’ve created a few additional indexes on the SCOTT schema:

connect scott/tiger@//localhost/PDB1
create index EMP_HIREDATE on EMP(HIREDATE);
create index EMP_FK on EMP(DEPTNO);
create bitmap index EMP_BITMAP on EMP(JOB);
create index EMP_FKPLUS on EMP(DEPTNO,JOB);
create unique index EMP_UNIQUE on EMP(HIREDATE,ENAME);
set ddl segment_attributes off
set ddl storage off
select dbms_metadata.get_ddl('INDEX',index_name,user) from user_indexes;

Here I have unique and non-unique, regular and bitmap, covering foreign key only, or foreign key plus other columns:

I run the “drop secondary index” procedure:

exec sys.dbms_auto_index.drop_secondary_indexes('SCOTT','EMP');

Here is what remains:

  • All unique indexes are still there, not only the ones on the primary key
  • the index that covers exactly the foreign key remains
  • all others have been dropped

But the index that covers more than the foreign key (EMP_FKPLUS) has been dropped. And don’t think that there’s some intelligence that detected the other index on the foreign key (EMP_FK). If you run the same without EMP_FK, the EMP_FKPLUS is still dropped. So be careful if you use this: an index which was created to avoid lock will be considered “secondary” except if it was created with exactly the same definition as the foreign key columns. I have sql_trace’d the query used to find the indexes to drop:

Look at the LISTAGG: the comparison between the foreign key columns and the index column is too simple in my opinion: exactly the same columns and in the same position. The index to solve a “foreign key lock issue” can be more complex: it only needs to start with the foreign key columns, in whatever order.

In summary, what is considered as dropping “secondary” indexes here is basically dropping all indexes that are not enforcing primary key or unique constraints and not matching exactly the foreign key column definition. This Drop Secondary Indexes procedure is probably there only for testing: removing all indexes that may be created automatically and see what happens.

Speaking at Trivadis Performance Days 2019

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 […]