SQLs slow performance solution

Issue #1:

 1.       SQLs 8thva2b1vp0pa, 31qxfvf20gaad are getting executed 1000+ times each at an average execution time of 1.5 seconds. Application scalability might become an issue if such SQLs’ performance issue is not addressed.

Analysis #1:

Based on below information Query is already taking more than 1.5 seconds in Prod DB and it is due to full table scan on some tables. When query is doing full table scan, execution time will be vary based on Cost/Load (cpu, memory and IO).

 Application scalability might become an issue à it will not only impact on application scalability but also on dependencies such as last time we had impact on golden gate in DB and later we found that issue occurred due to expensive query.

 It is better to test below query with creating new indexes in non-prod DB.

 8thva2b1vp0pa –> not exist in PROD DB.

 31qxfvf20gaad –> Exist in PROD DB and it has same execution plan.

 Query execution time history:  max time took from 3 seconds to 11 seconds.

Analysis #2:

 

8thva2b1vp0pa à this query is not only expensive but also consuming high memory because of items table repeated 3 times. Please look on query logic written based on functional design. Refer below flow diagram.

              There is full table scan on users table, but this table is not costing much, if you still want to create index and check query performance then you can create index on username column.

             Command:  create index <index_name> on <table_name> (<column_name>);

 

Note: there is a chance to reduce (3 times) calling items table by creating materialized view but this should check with developing team.

Example:

Step1: create materialized view

Step 2: select columns from materialized view for table

Step 3:  delete data from table à we can create materialized view but needs to check memory consumption will be high or not.

Step 4: select columns either using original table or materialized view.

Query logic:

 

Here items table was selected 3 times.

1.       Column selected based on table

2.       Column needs to delete of table

3.       Column will be filtered based on columns

Analysis #3:

 

8thva2b1vp0pa à As discussed yesterday, this sql_id consume more memory.

 

1.       sql_id is taking < 1 sec per execution

2.       No. of Executions and not constant every day. Max executions per day is > 30K

3.       Physical IO is very less.

31qxfvf20gaad à There are some indexes needs to create on columns.  You can create below indexes and test in non-prod.

 

1.        1. sql_id is taking < 2 sec per execution

2.       2. No. Executions and almost constant every day. Max executions per day is > 5K

3.       3. High Physical IO not seen much.

   

   

Analysis #3:

 

Based on below analysis, if you can provide non-prod DB then I will do another testing by creating indexes with different columns combinations. And I will compare all execution plans with different set of indexes. Whichever plan is good (use indexes and cost reduce as much as), 

 I will give those indexes so that you can test once again with given indexes in non-prod DB for better results.

 

Please find comparison analysis.

 

1.       Only 1 index were used for 31qxfvf20gaad. Please find below only differences.

2.       8thva2b1vp0pa –> given 1 index but it is not used. So, execution plan is same and nothing changed.

 

31qxfvf20gaad:

 

Cost is reduced for table from 9K to 4.

 

Before Index execution plan:

After index execution plan:

Before Indexes: Overall cost is more than 300K.

After index:  Overall cost is reduced below 300K (~50K reduced).

Please find below query history:

 

Query per execution time is not seen much difference (avg 0.03 seconds reduced difference observed and it is not gone beyond 1.68 seconds)

Total number of executions are almost same, but you can see overall time is reduced (less than 4000)

Analysis #4:

 

After running SQL Advisor.

 

we can try another set of indexes. It will improve SQL performance. Please find below my observations

 

1.       column does not require for tables.

2.       table have different index on columns (). Instead of separate indexes, here we can create single index on 3 columns.

 

Expected SQL improvement:

Solution:

After creating suggested indexes, SQL performance improved

k