Essai gratuit
Free Trial

Object search

by | Jun 26, 2024 | Non classifié(e)

Has an Oracle object been used during a period?

The Replay window summarizing Oracle activity provides, among other details, a list of SQL queries that consumed time (elapsed time) or resources (CPU, IOs) during the selected period.

The ‘Object search’ button opens a search window that helps answer the following question:
‘Has an object been used by Oracle during this time?’

Index usage

This feature searches within the execution plans observed during the selected period for the presence of the mentioned object.
It allows you to verify if:

  • A table has been accessed,
  • An index was chosen by the Oracle optimizer,
  • A materialized view was utilized (for example, through Oracle’s powerful Query Rewrite feature), during the execution of a process or within a specific period window.

If the searched object is found, Replay will display all the SQL queries that used it.

For instance, if you want to know whether the index named CUST_I was used during a process, you will see which SQL queries utilized this index:

You can even see the creation order of the searched index, as it falls within the selected period.
To view detailed information such as execution time, query duration, number of rows processed, resource consumption, etc., simply double-click on the relevant query.

Object usage

No matches may be found within the selected period.
For example, if you search for a materialized view named ‘MV_CUSTOM’ and no results are found, it means that neither this materialized view nor any object with that name appears in the execution plans for the period.
Since no execution plan during the period references this object, the following message will be displayed:

Object search

The SQL operator used for the search within the execution plans is a LIKE.
This allows you to search for an exact name, as seen in the previous examples with CUST_I and MV_CUSTOM, or to search for a set of names or similar names.
For example, if you want to know if objects whose name starts with ‘CUSTOM’ were used by Oracle during the activity period:

The Object search feature identifies that a table named CUSTOMERS and a table named CUSTOMERS_HIST (first SQL order) were used by Oracle in its execution plans during the period from 14:15 to 14:22.

Object search and filter

This object search within execution plans is different from searching within the query text.
These two features serve distinct purposes.
The filter is also available at the bottom of the Object search window to further refine the search results if needed, and its functionality is documented in another article, ‘Filters and Special Characters’.

USE CASE

The Object search feature can be useful, for example, when searching for unused indexes.
If an index is never used, it could and should likely be dropped to save space and improve update performance (for DML operations like INSERT, UPDATE, DELETE, and MERGE).
Similarly, this feature can be used, after creating an index, to confirm that it improves the targeted query, and potentially other queries, and to verify that it doesn’t degrade the performance of other processes.