Find the most used indexes

At a customer I was asked to check for missing indexes and add them. Some days later the application automatically dropped them. The customer come back to me and asked to check which indexes were the most important.

As maybe not all my added indexes were needed and used, instead of just sending (again) the document with their definition, I checked which indexes were actively used since adding the new indexes.

I come up with this query:

select distinct object_owner,object_name from dba_hist_sql_plan where plan_hash_value in (select plan_hash_value from 
/* Day after adding new indexes */
with snapshot as (select min(snap_id) snap_id from dba_hist_snapshot where begin_interval_time>=to_date('27-MAY-2021','DD-MON-YYYY')),
/* Plans using new indexes */
plans as (select plan_hash_value from dba_hist_sql_plan where object_name like '%\_ANJO\_%' escape '\')
/* Plans using new indexes after my intervention */
select plan_hash_value, sum(executions_delta) execs from dba_hist_sqlstat where snap_id> (select snap_id from snapshot)
  and plan_hash_value in (select plan_hash_value from plans)
  group by plan_hash_value
  having sum(executions_delta)>10
and object_name like '%\_ANJO\_%' escape '\' 
order by 1,2;

I’ve limited the search to the indexes I added (having _ANJO_ in their name).

Surprise, all of the indexes I added were actively used. Hopefully they will add them again for good.

Explore multiple optimizer features and fixes with SQLT Xplore

Yesterday I described how I come to SQLT Xplore and it helped me to find out, on Oracle, that optimizer_features_enable=8.1.3 decreased the parsing time of a query from 5 seconds to 0.2 seconds. Today I show how to use SQLT Xplore.

What is SQLT Xplore?

SQL Xplore automatises the test of almost 2’000 optimizer parameters and bug fixes control against one query, allowing to discover which parameter was eventually the reason of a performance regression.


Long parsing on Oracle 12.2 and the discovery of SQLT Xplore

Today I discovered a fantastic free tool from Oracle: SQLT Xplore!

At a client I had one query that took long time parsing. The tkprof result of the 10046 trace showed it took 5 seconds to parse:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      5.17       5.30          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0        665          0          26
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      5.17       5.30          0        665          0          26

Looking around I’ve noticed that disabling the cost based transformation:

ALTER SESSIONS SET "_optimizer_cost_based_transformation"=off;  

…the query was much faster to parse: only 0,5 seconds.


GoldenGate replicat performance

Today at a client I had a old abended replicat process that I decided to give life to. So I solved the problem and start it.

The environment is using GoldenGate against Oracle database with ASM, on AIX. The replicat process replicates all operations of a 700+ tables schema. There were other replicats running at the same time writing to other schemas.

Then I decided to see the performance of GoldenGate. With the query:


You get the detailed statistics of operations per second since the start of the process, since the beginning of the day and last hour for all tables replicated by that process. For instance:

*** Hourly statistics since 2012-04-16 13:00:00 ***
        Total inserts/second:                     1397.76
        Total updates/second:                     1307.46
        Total deletes/second:                      991.50
        Total discards/second:                       0.00
        Total operations/second:                  3696.71

So here we can see it is doing a bit more than 3500 operations per second, divided quite evenly between inserts, updates and deletes.

As usually the GoldenGate is used for realtime replication and there are no big operations, the client does not use performance related parameters. But this time I decided to play with them.

After adding both: BATCHSQL and INSERTAPPEND to the parameter file of the replicat process, the results were the following (after more than 10 minutes running) and performance is still increasing:

*** Hourly statistics since 2012-04-16 13:43:17 ***
        Total inserts/second:                     2174.92
        Total updates/second:                     2540.20
        Total deletes/second:                     1636.16
        Total discards/second:                       0.00
        Total operations/second:                  6351.28

We see the performance increased by 90% !

I got interested to see if the BATCHSQL parameter only by itself could make the difference. So I removed the INSERTAPPEND parameter (which only influences the inserts anyway). Here are the results after more than 10 minutes.

*** Hourly statistics since 2012-04-16 14:00:00 ***
        Total inserts/second:                     2402.21
        Total updates/second:                     2185.24
        Total deletes/second:                     1742.43
        Total discards/second:                       0.00
        Total operations/second:                  6329.88

Yep, seems the system of my client in certain situations benefits mostly of the BATCHSQL parameter.

For those who don’t know, “in BATCHSQL mode, Replicat organizes similar SQL statements into batches within a memory queue, and then it applies each batch in one database operation. A batch contains SQL statements that affect the same table, operation type (insert, update, or delete), and column list.” (in GoldenGate Reference Guide).