CODEWITHSUNDEEP
sqloracle-database
user619
user619

Reputation: 159

How to improve my oracle query?

I have a query in Oracle which takes 3 or more seconds for a single request but, in my case, I execute it multiple times to process multiple records, so overall it takes more time.
To improve query performance I have done the following action.

I have executed SQL tuning Advisory and created suggested index, but suggested index does not work in execution plan, it shows the same previous primary key index, what I will do for it.

My query is:

Select NVL(Min(MACIDREFNO),0)
from   TACID_MST
Where  MacIdStatus = 2
And    Status = 0 
And    UsedAs = 1
And    IsCodeSigned = 0
And    SiteRefNo = 0;

And the Execution Plan is:
Execution plan

And result of SQL tuning Advisory is:

GENERAL INFORMATION SECTION `Result of Tuning Advisory`
-------------------------------------------------------------------------------
Tuning Task Name                  : staName75534
Tuning Task Owner                 : TESTUSER
Tuning Task ID                    : 385915
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 1800
Completion Status                 : COMPLETED
Started at                        : 01/24/2018 15:46:15
Completed at                      : 01/24/2018 15:46:17
Number of Index Findings          : 1

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- Index Finding (`see explain plans section below`)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.

  Recommendation (estimated benefit: 100%)
  ----------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.

        create index TESTUSER.IDX$$_5E37B0001 on
        TESTUSER.TACID_MST('STATUS','USEDAS','MACIDSTATUS','SITEREFNO','ISCODESIGNED');

  Rationale
  ---------
    Creating the recommended indices significantly improves the execution plan
    of this statement. However, it might be preferable to run "Access Advisor"
    using a representative SQL workload as opposed to a single statement. This
    will allow to get comprehensive index recommendations which takes into
    account index maintenance overhead and additional space consumption.

Upvotes: 1

Views: 1032

Answers (1)

hekko
hekko

Reputation: 292

The simplest ways:

  • rebuild index sys_iot_top_147733 (if not IOT) or if possible move(rebuild) IOT
  • use binding variables
  • gather statistics on table TACID_MST
  • change column order in new index (put the most restrictive column value first (the column with the highest unique values))
  • use hint for force index use: select /*+ INDEX( TACID_MST your_index_name )*/ NVL(Min(MACIDREFNO),0) from

Upvotes: 1

Related Questions