mv sql profile and index
DESCRIPTION
SQL tuning using materialized view, SQL profile and indexTRANSCRIPT
The Case
Company ABC buys an application from Vendor XYZ a year ago without any support. Recently, DBA team
identified that there is degradation in 1 SQL, which run very frequently, and make overall system work
very slow.
This is a very simple SQL with hint on it.
SQL> select /*+ full(c1) full(c2) */ c1.owner, c1.object_name, c2.created
2 from cust1 c1, cust2 c2
3 where c1.object_id = c2.object_id
4 and c1.object_type = 'JOB'
5 and c2.object_type = 'JOB';
8 rows selected.
Elapsed: 00:00:00.18
Execution Plan
----------------------------------------------------------
------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 61 | 209 |
|* 1 | HASH JOIN | | 1 | 61 | 209 |
|* 2 | TABLE ACCESS FULL| CUST2 | 1 | 25 | 69 |
| 3 | TABLE ACCESS FULL| CUST1 | 370K| 12M| 138 |
------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1"."OBJECT_ID"="C2"."OBJECT_ID")
2 - filter("C2"."OBJECT_TYPE"='JOB')
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8156 consistent gets
0 physical reads
0 redo size
913 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
Management asks DBA team to provide solution for this issue immediately since Company revenue is
affected very badly. This is a little bit complex since Company do not buy support from the Vendor, they
do not have access to the application’s code at all.
DBA team starts work on it by collecting all the required information from database dictionary and data
distribution of CUST1 and CUST2 table.
They finally found below fact, row with OBJECT_TYPE = ‘JOB’ is very less compared to all table’s
population. And they come up with 2 solutions without changing anything in the application’s code.
What are the solutions from DBA team???
SQL> select count(*) from cust1;
COUNT(*)
----------
370374
SQL> select count(*) from cust1 where object_type='JOB';
COUNT(*)
----------
8
SQL> select count(*) from cust2;
COUNT(*)
----------
370374
SQL> select count(*) from cust2 where object_type='JOB';
COUNT(*)
----------
8
Solution 1
- Create index on OBJECT_TYPE column on both tables
- Create profile to remove /*+ full(c1) full(c2) */ hint from the SQL
SQL> create index cust1_idx on cust1(object_type);
Index created.
SQL> create index cust2_idx on cust2(object_type);
Index created.
SQL> exec dbms_stats.gather_table_stats(USER, 'CUST1');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(USER, 'CUST2');
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_sqltune.import_sql_profile(
3 name => 'custx_idx',
4 sql_text =>
5 'select /*+ full(c1) full(c2) */ c1.owner, c1.object_name, c2.created
6 from dbcheck.cust1 c1, dbcheck.cust2 c2
7 where c1.object_id = c2.object_id
8 and c1.object_type = ''JOB''
9 and c2.object_type = ''JOB''',
10 profile => sqlprof_attr(
11 'USE_NL(@"SEL$1" "C1"@"SEL$1")',
12 'LEADING(@"SEL$1" "C2"@"SEL$1" "C1"@"SEL$1")',
13 'INDEX(@"SEL$1" "C1"@"SEL$1" ("CUST1"."OBJECT_TYPE"))',
14 'INDEX_RS_ASC(@"SEL$1" "C2"@"SEL$1" ("CUST2"."OBJECT_TYPE"))',
15 'OUTLINE_LEAF(@"SEL$1")',
16 'IGNORE_OPTIM_EMBEDDED_HINTS'),
17 force_match => TRUE
18 );
19 end;
20 /
PL/SQL procedure successfully completed.
SQL> select /*+ full(c1) full(c2) */c1.owner, c1.object_name, c2.created
2 from cust1 c1, cust2 c2
3 where c1.object_id = c2.object_id
4 and c1.object_type = 'JOB'
5 and c2.object_type = 'JOB';
8 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 72 | 51 |
|* 1 | TABLE ACCESS BY INDEX ROWID | CUST1 | 1 | 47 | 50 |
| 2 | NESTED LOOPS | | 1 | 72 | 51 |
| 3 | TABLE ACCESS BY INDEX ROWID| CUST2 | 1 | 25 | 1 |
|* 4 | INDEX RANGE SCAN | CUST2_IDX | 1 | | 1 |
|* 5 | INDEX RANGE SCAN | CUST1_IDX | 13718 | | 5 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1"."OBJECT_ID"="C2"."OBJECT_ID")
4 - access("C2"."OBJECT_TYPE"='JOB')
5 - access("C1"."OBJECT_TYPE"='JOB')
Note
-----
- cpu costing is off (consider enabling it)
- SQL profile "custx_idx" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47 consistent gets
0 physical reads
0 redo size
821 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
Solution 2
- Create materialized view for each table and utilize query rewrite
SQL> create materialized view mv_cust1
2 never refresh
3 enable query rewrite
4 as
5 select * from cust1 where object_type = 'JOB';
Materialized view created.
SQL> create materialized view mv_cust2
2 never refresh
3 enable query rewrite
4 as
5 select * from cust2 where object_type = 'JOB';
Materialized view created.
SQL> exec dbms_stats.gather_table_stats(USER, 'MV_CUST1');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(USER, 'MV_CUST2');
PL/SQL procedure successfully completed.
SQL> select /*+ full(c1) full(c2) */ c1.owner, c1.object_name, c2.created
2 from cust1 c1, cust2 c2
3 where c1.object_id = c2.object_id
4 and c1.object_type = 'JOB'
5 and c2.object_type = 'JOB';
Elapsed: 00:00:00.12
Execution Plan
----------------------------------------------------------
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 296 | 5 |
|* 1 | HASH JOIN | | 8 | 296 | 5 |
| 2 | MAT_VIEW REWRITE ACCESS FULL| MV_CUST2 | 4 | 48 | 2 |
| 3 | MAT_VIEW REWRITE ACCESS FULL| MV_CUST1 | 8 | 200 | 2 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("MV_CUST1"."OBJECT_ID"="MV_CUST2"."OBJECT_ID")
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
913 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
Summary
When we do not have access to the application code/ change the SQL, we are still able to tune the SQL by creating below objects (can be combined or used separately):
1. Index 2. SQL profile 3. Materialized view
Happy Ending (but only in movie)
At the end of the project, all DBA team’s member got trip to Universal Studio Singapore as their bonus