Poor index identify and resolve manually or using sql tuning advisor

Posted on January 22, 2018January 22, 2018 by debasis maity

1.Let us create a test case first:-

oracle@TST:TESTDB1[/home/oracle]$ sqlplus c##test/test

C##TEST@TESTDB1> create table mytst1 as select * from dba_objects;
Table created.

C##TEST@TESTDB1> create table mytst2 as select * from dba_objects where rownum<5000;
Table created.

2.Let us see what application executes SQL query and complaining poor performance

select a.owner,a.OBJECT_NAME,a.OBJECT_TYPE,b.STATUS from mytst1 a,mytst2 b where a.OBJECT_ID=b.OBJECT_ID and b.OBJECT_NAME=’DBA_TABLES’;

3.Now create an index

In table mytst1,I can see column owner,object_name and object_id are used.So let me create index on those column.
Though We also see object_type used in query,I expect the following index should be good as We included object_id which is there in where clause(filter condition)

C##TEST@TESTDB1> create index mytest1_i1 on mytst1(OWNER,OBJECT_NAME,OBJECT_ID);
Index created.

I can see for the table mytst2,I can see column object_id and object_name are used in where clause(filter condition) .So let me create index on those column.

C##TEST@TESTDB1> create index mytest2_i1 on mytst2(OBJECT_ID,OBJECT_NAME);
Index created.

C##TEST@TESTDB1> exec dbms_stats.gather_schema_stats(‘C##TEST’);

Let me check execution plan of the query

C##TEST@TESTDB1> set autotrace on;
C##TEST@TESTDB1> select /*+ gather_plan_statistics */ a.owner,a.OBJECT_NAME,a.OBJECT_TYPE,b.STATUS from mytst1 a,mytst2 b where a.OBJECT_ID=b.OBJECT_ID and b.OBJECT_NAME=’DBA_TABLES’;

OWNER
——————————————————————————————————————————–
OBJECT_NAME
——————————————————————————————————————————–
OBJECT_TYPE STATUS
———————– ——-
SYS
DBA_TABLES
VIEW VALID

PUBLIC
DBA_TABLES
SYNONYM VALID

The execution plan shows full table scan.It seems index is not being picked up.

Execution Plan
———————————————————-
Plan hash value: 3196922646
—————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 73 | 451 (1)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 73 | 451 (1)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| MYTST2 | 1 | 28 | 25 (0)| 00:00:01 |
|* 3 | INDEX SKIP SCAN | MYTEST2_I1 | 1 | | 24 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | MYTST1 | 91062 | 4001K| 426 (1)| 00:00:01 |
—————————————————————————————————

Predicate Information (identified by operation id):
—————————————————
1 – access(“A”.”OBJECT_ID”=”B”.”OBJECT_ID”)
3 – access(“B”.”OBJECT_NAME”=’DBA_TABLES’)
filter(“B”.”OBJECT_NAME”=’DBA_TABLES’)

6.Now let me re-look the indexes

I created earlier and found I had composite index with object_id last column in index.But this
is poor index and our filter condition either should be first column of index (not always true) or we need separate index exclusively on object_id column.
drop index mytest_i1 and create on object_id.The cost got improved.But this is not still best.It is just better.

C##TEST@TESTDB1> drop index mytest1_i1;
Index dropped.
C##TEST@TESTDB1> create index mytst1_i1 on mytst1(object_id);
Index created.
C##TEST@TESTDB1> select /*+ gather_plan_statistics */ a.owner,a.OBJECT_NAME,a.OBJECT_TYPE,b.STATUS from mytst1 a,mytst2 b where a.OBJECT_ID=b.OBJECT_ID and b.OBJECT_NAME=’DBA_TABLES’;
OWNER
——————————————————————————————————————————–
OBJECT_NAME
——————————————————————————————————————————–
OBJECT_TYPE STATUS
———————– ——-
SYS
DBA_TABLES
VIEW VALID

PUBLIC
DBA_TABLES
SYNONYM VALID

Execution Plan
———————————————————-
Plan hash value: 1970107425

—————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 73 | 27 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 73 | 27 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 73 | 27 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| MYTST2 | 1 | 28 | 25 (0)| 00:00:01 |
|* 4 | INDEX SKIP SCAN | MYTEST2_I1 | 1 | | 24 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | MYTST1_I1 | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | MYTST1 | 1 | 45 | 2 (0)| 00:00:01 |
—————————————————————————————————-

Predicate Information (identified by operation id):
—————————————————

4 – access(“B”.”OBJECT_NAME”=’DBA_TABLES’)
filter(“B”.”OBJECT_NAME”=’DBA_TABLES’)
5 – access(“A”.”OBJECT_ID”=”B”.”OBJECT_ID”)

7.Now creating exclusive index on object_id for mytst2 to avoid skip scan and drop old one.More cost improve far better.

C##TEST@TESTDB1> drop index MYTEST2_I1;
Index dropped.
C##TEST@TESTDB1> create index MYTEST2_I1 on MYTST2(OBJECT_NAME);
Index created.

C##TEST@TESTDB1> select /*+ gather_plan_statistics */ a.owner,a.OBJECT_NAME,a.OBJECT_TYPE,b.STATUS from mytst1 a,mytst2 b where a.OBJECT_ID=b.OBJECT_ID and b.OBJECT_NAME =’DBA_TABLES’;

PUBLIC
DBA_TABLES
SYNONYM VALID
Execution Plan
———————————————————-
Plan hash value: 929914574
—————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 73 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 73 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 73 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| MYTST2 | 1 | 28 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | MYTEST2_I1 | 1 | | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | MYTST1_I1 | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | MYTST1 | 1 | 45 | 2 (0)| 00:00:01 |
—————————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
4 – access(“B”.”OBJECT_NAME”=’DBA_TABLES’)
5 – access(“A”.”OBJECT_ID”=”B”.”OBJECT_ID”)

8.Now revisit case 3 and I will show just changing the sequence of column in the index can improve plan drastically.

I will put object_id column first in composite index.

C##TEST@TESTDB1> drop index MYTST1_I1;
Index dropped.
C##TEST@TESTDB1> create index mytest1_i1 on mytst1(OBJECT_ID,OWNER,OBJECT_NAME);
Index created.
C##TEST@TESTDB1> select /*+ gather_plan_statistics */ a.owner,a.OBJECT_NAME,a.OBJECT_TYPE,b.STATUS from mytst1 a,mytst2 b where a.OBJECT_ID=b.OBJECT_ID and b.OBJECT_NAME =’DBA_TABLES’;
OWNER
——————————————————————————————————————————–
OBJECT_NAME
——————————————————————————————————————————–
OBJECT_TYPE STATUS
———————– ——-
SYS
DBA_TABLES
VIEW VALID

PUBLIC
DBA_TABLES
SYNONYM VALID
Execution Plan
———————————————————-
Plan hash value: 1963133166

—————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 73 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 73 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 73 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| MYTST2 | 1 | 28 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | MYTEST2_I1 | 1 | | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | MYTEST1_I1 | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | MYTST1 | 1 | 45 | 2 (0)| 00:00:01 |
—————————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
4 – access(“B”.”OBJECT_NAME”=’DBA_TABLES’)
5 – access(“A”.”OBJECT_ID”=”B”.”OBJECT_ID”)

9.Let us see now if automatic sql tuning can provide us correct recommendation or not.

In my previous example I created some bad index first and Let sql tuning advisior identify it.

SYSTEM@TESTDB1> select a.owner,a.OBJECT_NAME,a.OBJECT_TYPE,b.STATUS from c##test.mytst1 a,c##test.mytst2 b where a.OBJECT_ID=b.OBJECT_ID and b.OBJECT_NAME=’DBA_TABLES’;
OWNER
——————————————————————————————————————————–
OBJECT_NAME
——————————————————————————————————————————–
OBJECT_TYPE STATUS
———————– ——-
SYS
DBA_TABLES
VIEW VALID

PUBLIC
DBA_TABLES
SYNONYM VALID

SYSTEM@TESTDB1> SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => ‘ADVANCED’)); 2

PLAN_TABLE_OUTPUT
———————————————————————————————————————————-
SQL_ID cpmdy46qwqckr, child number 0
————————————-
select a.owner,a.OBJECT_NAME,a.OBJECT_TYPE,b.STATUS from c##test.mytst1
a,c##test.mytst2 b where a.OBJECT_ID=b.OBJECT_ID and
b.OBJECT_NAME=’DBA_TABLES’
Plan hash value: 3196922646
—————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————————
| 0 | SELECT STATEMENT | | | | 451 (100)| |
|* 1 | HASH JOIN | | 1 | 73 | 451 (1)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| MYTST2 | 1 | 28 | 25 (0)| 00:00:01 |
|* 3 | INDEX SKIP SCAN | MYTEST2_I1 | 1 | | 24 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | MYTST1 | 91062 | 4001K| 426 (1)| 00:00:01 |
—————————————————————————————————

Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1
2 – SEL$1 / B@SEL$1
3 – SEL$1 / B@SEL$1
4 – SEL$1 / A@SEL$1
Outline Data
————-
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘12.1.0.2’)
DB_VERSION(‘12.1.0.2′)
ALL_ROWS
OUTLINE_LEAF(@”SEL$1″)
INDEX_SS(@”SEL$1″ “B”@”SEL$1” (“MYTST2″.”OBJECT_ID” “MYTST2″.”OBJECT_NAME”))
BATCH_TABLE_ACCESS_BY_ROWID(@”SEL$1″ “B”@”SEL$1″)
FULL(@”SEL$1” “A”@”SEL$1″)
LEADING(@”SEL$1” “B”@”SEL$1” “A”@”SEL$1″)
USE_HASH(@”SEL$1” “A”@”SEL$1”)
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
—————————————————

1 – access(“A”.”OBJECT_ID”=”B”.”OBJECT_ID”)
3 – access(“B”.”OBJECT_NAME”=’DBA_TABLES’)
filter(“B”.”OBJECT_NAME”=’DBA_TABLES’)

Column Projection Information (identified by operation id):
———————————————————–
1 – (#keys=1) “B”.”STATUS”[VARCHAR2,7], “A”.”OWNER”[VARCHAR2,128],
“A”.”OBJECT_NAME”[VARCHAR2,128], “A”.”OBJECT_TYPE”[VARCHAR2,23]
2 – “B”.”OBJECT_ID”[NUMBER,22], “B”.”STATUS”[VARCHAR2,7]
3 – “B”.ROWID[ROWID,10], “B”.”OBJECT_ID”[NUMBER,22]
4 – (rowset=200) “A”.”OWNER”[VARCHAR2,128], “A”.”OBJECT_NAME”[VARCHAR2,128],
“A”.”OBJECT_ID”[NUMBER,22], “A”.”OBJECT_TYPE”[VARCHAR2,23]

61 rows selected.

I will run tuning advisor now and tuning advisior correctly identified proper index to be created which I had identified from my experience

SYSTEM@TESTDB1> DECLARE
l_sql_tune_task_id VARCHAR2(100);
2 3 BEGIN
4 l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
5 sql_id => ‘cpmdy46qwqckr’,
6 scope => DBMS_SQLTUNE.scope_comprehensive,
7 time_limit => 120,
8 task_name => ‘sql3_tuning_task_cpmdy46qwqckr’,
9 description => ‘Tuning task for statement cpmdy46qwqckr.’);
10 DBMS_OUTPUT.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);
11 END;
12 /

PL/SQL procedure successfully completed.

SYSTEM@TESTDB1> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => ‘sql3_tuning_task_cpmdy46qwqckr’);

PL/SQL procedure successfully completed.

SYSTEM@TESTDB1> SET LINES 150
SET pages 50000
SET long 5000000
SET longc 5000000

SELECT DBMS_SQLTUNE.report_tuning_task(‘sql3_tuning_task_cpmdy46qwqckr’) AS recommendations FROM dual;
SYSTEM@TESTDB1> SYSTEM@TESTDB1> SYSTEM@TESTDB1> SYSTEM@TESTDB1> SYSTEM@TESTDB1>
RECOMMENDATIONS
——————————————————————————————————————————————————
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : sql3_tuning_task_cpmdy46qwqckr
Tuning Task Owner : SYSTEM
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 120
Completion Status : COMPLETED
Started at : 01/18/2018 03:42:21
Completed at : 01/18/2018 03:42:23

——————————————————————————-
Schema Name : SYSTEM
Container Name: CDB$ROOT
SQL ID : cpmdy46qwqckr
SQL Text : select a.owner,a.OBJECT_NAME,a.OBJECT_TYPE,b.STATUS from
c##test.mytst1 a,c##test.mytst2 b where
a.OBJECT_ID=b.OBJECT_ID and b.OBJECT_NAME=’DBA_TABLES’

——————————————————————————-
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: 99.11%)
——————————————
– Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index C##TEST.IDX$$_00F20001 on
C##TEST.MYTST2(“OBJECT_NAME”,”OBJECT_ID”,”STATUS”);

– Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index C##TEST.IDX$$_00F20002 on C##TEST.MYTST1(“OBJECT_ID”);

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.

——————————————————————————-
EXPLAIN PLANS SECTION
——————————————————————————-

1- Original
———–
Plan hash value: 3196922646

—————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 73 | 451 (1)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 73 | 451 (1)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| MYTST2 | 1 | 28 | 25 (0)| 00:00:01 |
|* 3 | INDEX SKIP SCAN | MYTEST2_I1 | 1 | | 24 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | MYTST1 | 91062 | 4001K| 426 (1)| 00:00:01 |
—————————————————————————————————

Predicate Information (identified by operation id):
—————————————————

1 – access(“A”.”OBJECT_ID”=”B”.”OBJECT_ID”)
3 – access(“B”.”OBJECT_NAME”=’DBA_TABLES’)
filter(“B”.”OBJECT_NAME”=’DBA_TABLES’)

2- Using New Indices
——————–
Plan hash value: 4293127463

———————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 73 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 73 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 73 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX$$_00F20001 | 1 | 28 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX$$_00F20002 | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| MYTST1 | 1 | 45 | 2 (0)| 00:00:01 |
———————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

3 – access(“B”.”OBJECT_NAME”=’DBA_TABLES’)
4 – access(“A”.”OBJECT_ID”=”B”.”OBJECT_ID”)

——————————————————————————-

Let me create index suggested by tuning advisor and it indeed worked.

SYSTEM@TESTDB1> create index C##TEST.IDX$$_00F20001 on
C##TEST.MYTST2(“OBJECT_NAME”,”OBJECT_ID”,”STATUS”);
2
Index created.

SYSTEM@TESTDB1> create index C##TEST.IDX$$_00F20002 on C##TEST.MYTST1(“OBJECT_ID”);

Index created.

SYSTEM@TESTDB1> select a.owner,a.OBJECT_NAME,a.OBJECT_TYPE,b.STATUS from c##test.mytst1 a,c##test.mytst2 b where a.OBJECT_ID=b.OBJECT_ID and b.OBJECT_NAME=’DBA_TABLES’;

PUBLIC
DBA_TABLES
SYNONYM VALID

Please check plan now.

SYSTEM@TESTDB1> SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => ‘ADVANCED’)); 2

PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————
SQL_ID cpmdy46qwqckr, child number 0
————————————-
select a.owner,a.OBJECT_NAME,a.OBJECT_TYPE,b.STATUS from c##test.mytst1
a,c##test.mytst2 b where a.OBJECT_ID=b.OBJECT_ID and
b.OBJECT_NAME=’DBA_TABLES’

Plan hash value: 4293127463

———————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————–
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | NESTED LOOPS | | 1 | 73 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 73 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX$$_00F20001 | 1 | 28 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX$$_00F20002 | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| MYTST1 | 1 | 45 | 2 (0)| 00:00:01 |
———————————————————————————————–

Query Block Name / Object Alias (identified by operation id):
————————————————————-

1 – SEL$1
3 – SEL$1 / B@SEL$1
4 – SEL$1 / A@SEL$1
5 – SEL$1 / A@SEL$1

Outline Data
————-

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘12.1.0.2’)
DB_VERSION(‘12.1.0.2′)
ALL_ROWS
OUTLINE_LEAF(@”SEL$1″)
INDEX(@”SEL$1″ “B”@”SEL$1” (“MYTST2″.”OBJECT_NAME” “MYTST2″.”OBJECT_ID”
“MYTST2″.”STATUS”))
INDEX(@”SEL$1″ “A”@”SEL$1” (“MYTST1″.”OBJECT_ID”))
LEADING(@”SEL$1″ “B”@”SEL$1” “A”@”SEL$1″)
USE_NL(@”SEL$1” “A”@”SEL$1″)
NLJ_BATCHING(@”SEL$1” “A”@”SEL$1”)
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
—————————————————

3 – access(“B”.”OBJECT_NAME”=’DBA_TABLES’)
4 – access(“A”.”OBJECT_ID”=”B”.”OBJECT_ID”)

Column Projection Information (identified by operation id):
———————————————————–

1 – “B”.”OBJECT_ID”[NUMBER,22], “B”.”STATUS”[VARCHAR2,7],
“A”.”OWNER”[VARCHAR2,128], “A”.”OBJECT_NAME”[VARCHAR2,128],
“A”.”OBJECT_TYPE”[VARCHAR2,23]
2 – “B”.”OBJECT_ID”[NUMBER,22], “B”.”STATUS”[VARCHAR2,7], “A”.ROWID[ROWID,10]
3 – “B”.”OBJECT_ID”[NUMBER,22], “B”.”STATUS”[VARCHAR2,7]
4 – “A”.ROWID[ROWID,10]
5 – “A”.”OWNER”[VARCHAR2,128], “A”.”OBJECT_NAME”[VARCHAR2,128],
“A”.”OBJECT_TYPE”[VARCHAR2,23]

Note
—–
– this is an adaptive plan

ORACLE OPTIMIZER COST CALCULATIONS BASIC OVERVIEW

Posted on December 8, 2017January 15, 2018 by debasis maity

Cost Calculation for Full table Scan

How FTS cost depends on system statistics

Gather statistics manually on load:

The following command will start to gather system statistics.
SQL> execute DBMS_STATS.GATHER_SYSTEM_STATS (‘START’);

Please wait for system warm up and stop gather system statistics after work load.

This is called workload statistics.
The MBRC will be calculated automatically depending on no. of FTS occurring on database.

SQL> execute DBMS_STATS.GATHER_SYSTEM_STATS (‘STOP’);

SQL> select * from aux_stats$;

SNAME PNAME PVAL1
—————————— —————————— ———-
PVAL2
——————————————————————————–
SYSSTATS_INFO STATUS AUTOGATHERING

SYSSTATS_INFO DSTART 01-15-2018 04:44

SYSSTATS_INFO DSTOP 01-15-2018 04:46

SYSSTATS_INFO FLAGS 0

SYSSTATS_TEMP SBLKRDS 46416

SYSSTATS_TEMP SBLKRDTIM 22465.055

SYSSTATS_TEMP MBLKRDS 3864

SYSSTATS_TEMP MBLKRDTIM 4291.249

SYSSTATS_TEMP CPUCYCLES 69720

SYSSTATS_TEMP CPUTIM 23054

SYSSTATS_TEMP JOB 14

SYSSTATS_TEMP CACHE_JOB 16

SYSSTATS_TEMP MBRTOTAL 65755

SYSSTATS_MAIN CPUSPEEDNW 3024

SYSSTATS_MAIN IOSEEKTIM 10

SYSSTATS_MAIN IOTFRSPEED 4096

SYSSTATS_MAIN SREADTIM

SYSSTATS_MAIN MREADTIM

SYSSTATS_MAIN CPUSPEED 3024

SYSSTATS_MAIN MBRC

SYSSTATS_MAIN MAXTHR

SYSSTATS_MAIN SLAVETHR

Sometimes all values may not be populated because of nature of workload.

Example of FTS Cost calculation (System
statistics gathered on load)

sreadtim : wait time to read single block, in milliseconds .
mreadtim : wait time to read a multi block, in milliseconds.
cpuspeed : cycles per second, in millions.
MRBC : average multi block read count for sequential read, in blocks .

CPU_COST and IO_COST in PLAN_TABLE table after gathering system statistics
on load:

C##TEST@TESTDB1> select count(1) from tab1;

Execution Plan
———————————————————-
Plan hash value: 1117438016

——————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————-
| 0 | SELECT STATEMENT | | 1 | 113K (2)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TAB1 | 1999K| 113K (2)| 00:00:05 |
——————————————————————-

Statistics
———————————————————-
0 recursive calls
0 db block gets
285857 consistent gets
0 physical reads
0 redo size
545 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

So CPU cost:-2% of Total COST IO cost:-98% of Total COST.

10053 trace generation :-

SYS@TESTDB1> alter session set events ‘10053 trace name context forever’;

Session altered.

SYS@TESTDB1> select count(1) from c##test.tab1;

COUNT(1)
———-
1999998

SYS@TESTDB1> alter session set events ‘10053 trace name context off’;

Session altered.

Cost parameters in 10053 trace:-

—————————–
SYSTEM STATISTICS INFORMATION
—————————–
Using dictionary system stats.
Using WORKLOAD Stats
CPUSPEED: 2942 millions instructions/sec
SREADTIM: 0.621000 milliseconds
MREADTIM: 0.974000 millisecons
MBRC: 4 blocks
MAXTHR: -1 bytes/sec
SLAVETHR: -1 bytes/sec

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TAB1 Alias: TAB1
#Rows: 1999998 SSZ: 0 LGR: 0 #Blks: 286592 AvgRowLen: 1012.00 NEB: 0 ChainCnt: 0.00 SPC: 0 RFL: 0 RNF: 0 CBK: 0 CHR: 0 KQDFLG: 1
#IMCUs: 0 IMCRowCnt: 0 IMCJournalRowCnt: 0 #IMCBlocks: 0 IMCQuotient: 0.000000
=======================================
SPD: BEGIN context at query block level

Access path analysis for TAB1
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TAB1[TAB1]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
Table: TAB1 Alias: TAB1
Card: Original: 1999998.000000 Rounded: 1999998 Computed: 1999998.000000 Non Adjusted: 1999998.000000
Scan IO Cost (Disk) = 112377.000000
Scan CPU Cost (Disk) = 2340947432.480000
Total Scan IO Cost = 112377.000000 (scan (Disk))
= 112377.000000
Total Scan CPU Cost = 2340947432.480000 (scan (Disk))
= 2340947432.480000
Access Path: TableScan
Cost: 113658.319374 Resp: 113658.319374 Degree: 0
Cost_io: 112377.000000 Cost_cpu: 2340947432
Resp_io: 112377.000000 Resp_cpu: 2340947432
Best:: AccessPath: TableScan
Cost: 113658.319374 Degree: 1 Resp: 113658.319374 Card: 1999998.000000 Bytes: 0.000000

Example of FTS Cost calculation…..

Formula for FTS (system statistics is gathered on load):-

iocost:=(No. of Blocks/MBRC)*mreadtim/sreadtim
cpucost:=#cpu cycles/(cpuspeed*(sreadtim*1000))—->#cpu cycles is Total Scan CPU Cost
No. of blocks:=286592
MBRC:=4
mreadtim:=0.974000
sreadtim:=0.621000
iocost:=(286592 /4)*(0.974000/0.621000)=112377
cpucost:=2340947432/(2942*(0.621000*1000))=1281

You can disable cpu cost using following command:=
alter system set “_optimizer_cost_model” =io;

You can test the explain plan in lower version of oracle by following command:=
SQL> alter system set optimizer_features_enable=”11.2.0″;

Example of FTS (System statistics gathered on
no load)

Formula for FTS (system statistics is gathered on no load):-

Sreadtim:=ioseektim+db_block_size/iotrfrspeed.
Mreadtim:=ioseektim+db_file_multiblock_read_count
*db_block_size/iotrftspeed
No. of blocks=710
db_file_multiblock_read_count=16 (This is set in session level by alter system command)
Sreadtim:=10+8192/4096=12
Mreadtim:=10+16*8192/4096=42
Iocost:=(710/16)*(42/12)=156+1=157

Example of FTS in Parallel…..

Formula for FTS in Parallel:-
10g cost at degree N=ceil(serial cost/0.9*N)
N=degree of parallelism
iocost=ceil(157/0.9*4)=44
If _optimizer_percent_parallel=75 , then 75% of parallel cost+25% of serial cost

Cardinality Calculation

Step 1: We need to create table and index as below:

Create table t1(c1 number(10),c2 char(100),c3 number(10));
Create index ind_c3 on column c3 and ind_c1_c3 on c1 and c3;

Step 2: Please insert some data as below:-

insert into t1 select 1,‘A’,1 from dual connect by rownum<1001;
insert into t1 select 2,‘A’,1 from dual connect by rownum<1001;
insert into t1 select 3,‘A’,1 from dual connect by rownum<1001;
insert into t1 select 4,‘B’,2 from dual connect by rownum<1001;
insert into t1 select 5,‘C’,NULL from dual connect by rownum<1001;
insert into t1 select 5,’D’,14 from dual connect by rownum<1001;

Step 3: Please analyze the table by compute statistics clause.

Case1 : Select * from t1 where c1=1
Cardinality=(num_rows)*(density) where Density=1/No. of distinct value

Case 2: Select * from t1 where c1 in (1,2)
Cardinality=num_rows*No. of occurrence of values in IN operator*individual
cardinality

Case 3:Select * from t1 where c1<=2
Cardinality=num_rows*[(limit-low val)/(high val-low val)+1/No. of distinct value]

Case 4:Select * from t1 where c1>=2
Cardinality=num_rows*[(high val-limit)/(high val-low val)+1/No. of distinct value]

Case 5: Select * from t1 where c2=2
If null value is there in c2,then
Cardinality=(num_rows-null values)*(density) Density=1/No. of distinct value

Case 6: Select * from t1 where c1 =1 and c2=1
Cardinality=(num_rows-null values)*(density of c1)*(density of c2)

Case 7: Select * from t1 where c1 between X and Y
Cardinality=num_rows*[(X-Y)/(high val-low val)+1/No. of distinct value+1/No. of
distinct value]

Cost Calculation for Index based table Scan

Index range scan with one predicate in where clause

Formula for Index based scan :-
Cost:=blevel + ceiling(leaf blocks * effective index selectivity)
Blevel=1
Leaf_blocks=10
Selectivity=1/distinct_value=1/3
Cost:=1+ceiling(10*1/3)=4

SQL> select c3 from t1 where c3=1;
Execution Plan
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 1667 | 3334 | 4 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IND_C3 | 1667 | 3334 | 4 (0)| 00:00:01

Index range scan with more predicate in where clause

Formula for Index based scan :-
Cost:=blevel + ceiling(leaf blocks * effective index selectivity1*effective index
selectivity2)
Blevel=1
Leaf_blocks=14
Selectivity1=1/distinct_value=1/3 Selectivity2=1/distinct_value=1/5
Cost:=1+ceiling(14*1/3*1/5)=2

SQL> select c3 from t1 where c1=1 and c3=1;
——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 333 | 1332 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IND_C1_C3 | 333 | 1332 | 2 (0)| 00:00:01 |

Formula for Index based table scan :-

C##TEST@TESTDB1> select index_name,blevel,distinct_keys,clustering_factor,num_rows,leaf_blocks from user_indexes;

INDEX_NAME
——————————————————————————–
BLEVEL DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS LEAF_BLOCKS
———- ————- —————– ———- ———–
T1_I1
2 500 9743 10000 1111

Cost:=blevel + ceiling(leaf blocks * effective index selectivity)
+ceiling(cluster_factor*effective table selectivity)
Blevel=1
Leaf_blocks=1111
Cluster_factor=9743
Selectivity1=1/distinct_values=1/500=0.002
Cost:=2+ceiling(1111*0.002)+ceiling(9743*0.002)=2+3+20=25

select
small_vc
from
t1
where
n1 = 2
and ind_pad = rpad(‘x’,40)
and n2 = 3
; 2 3 4 5 6 7 8 9

19 rows selected.

—– Plan Table —–

============
Plan Table
============
——————————————————-+———————————–+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
——————————————————-+———————————–+
| 0 | SELECT STATEMENT | | | | 25 | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 20 | 1160 | 25 | 00:00:01 |
| 2 | INDEX RANGE SCAN | T1_I1 | 20 | | 5 | 00:00:01 |
——————————————————-+———————————–+
Predicate Information:
———————-
2 – access(“N1″=2 AND “IND_PAD”=’x ‘ AND “N2″=3)

Content of other_xml column
===========================
db_version : 12.1.0.2
parse_schema : C##TEST
plan_hash_full : 162118435
plan_hash : 3320414027
plan_hash_2 : 162118435
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘12.1.0.2’)
DB_VERSION(‘12.1.0.2’)
ALL_ROWS
OUTLINE_LEAF(@”SEL$1″)
INDEX_RS_ASC(@”SEL$1″ “T1″@”SEL$1” (“T1”.”N1″ “T1″.”IND_PAD” “T1”.”N2″))
BATCH_TABLE_ACCESS_BY_ROWID(@”SEL$1″ “T1″@”SEL$1”)
END_OUTLINE_DATA
*/

10053 trace shows:-

****** Costing Index T1_I1
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
ColGroup Usage:: PredCnt: 3 Matches Full: #1 Partial: Sel: 0.002000
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
ColGroup Usage:: PredCnt: 3 Matches Full: #1 Partial: Sel: 0.002000
Access Path: index (AllEqRange)
Index: T1_I1
resc_io: 25.000000 resc_cpu: 186236
ix_sel: 0.002000 ix_sel_with_filters: 0.002000
Cost: 25.005132 Resp: 25.005132 Degree: 1
Best:: AccessPath: IndexRange
Index: T1_I1
Cost: 25.005132 Degree: 1 Resp: 25.005132 Card: 20.000000 Bytes: 0.000000

***************************************

Calculation For Fast Full Index Scan

Formula for Fast Full Index scan :-
iocost:=(No. of Leaf Blocks/MBRC)*mreadtim/sreadtim
cpucost:=#cpu cycles/cpuspeed*(sreadtim*1000)

Formula for Index skip scan :-

Iocost:=no of distinct value(skipped column) * (blevel+leaf_block*density of
column in where clause)

optimizer_index_cost_adj:-

You can use parameter optimizer_index_cost_adj to adjust index scan cost.Just simply multiply the predicate optimizer_index_cost_adj/100 at the end of index cost
calculation formula.The default value in 10g is 100.So it has no effect in cost calculation.

For example :
Cost:=(blevel + ceiling(leaf blocks * effective index selectivity)
+ceiling(cluster_factor*effective table selectivity))* optimizer_index_cost_adj/100

Calculation For Index Scan with Histogram

Formula for Index scan(Histogram) :-

Cost:=blevel + ceiling(leaf blocks * effective index selectivity)
Blevel=4
Leaf_blocks=3142
Selectivity=999/21992
Cost:=147

SQL> exec dbms_stats.gather_table_stats(user,’IT1′,method_opt=>’for columns size 7 c1′,estimate_percent=>100);

We have generated frequency based histogram on column c1.So the cardinality(999) is exactly same as no of rows for
value ‘F’.With out histogram,full table scan was going on as cardinality was calculated as 21992/7 is 3142.

SQL> select c1 from it1 where c1=’F’;
999 rows selected.
———————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 999 | 976K| 147 (0)| 00:00:02 |
|* 1 | INDEX RANGE SCAN| IT1_C1 | 999 | 976K| 147 (0)| 00:00:02 |

Oracle Optimizer Cost Calculation from explain plan

Posted on November 8, 2017November 18, 2017 by debasis maity

This post video provide you basic method how oracle calculate cost from explain plan it derives

clouddba:-DBA blog on Oracle,Oracle cloud,DevOps,PostgreSQL and Other Databases

General Performance tuning

Poor index identify and resolve manually or using sql tuning advisor

1.Let us create a test case first:-

2.Let us see what application executes SQL query and complaining poor performance

3.Now create an index

In table mytst1,I can see column owner,object_name and object_id are used.So let me create index on those column.
Though We also see object_type used in query,I expect the following index should be good as We included object_id which is there in where clause(filter condition)

I can see for the table mytst2,I can see column object_id and object_name are used in where clause(filter condition) .So let me create index on those column.

Let me check execution plan of the query

The execution plan shows full table scan.It seems index is not being picked up.

6.Now let me re-look the indexes

7.Now creating exclusive index on object_id for mytst2 to avoid skip scan and drop old one.More cost improve far better.

8.Now revisit case 3 and I will show just changing the sequence of column in the index can improve plan drastically.

9.Let us see now if automatic sql tuning can provide us correct recommendation or not.

In my previous example I created some bad index first and Let sql tuning advisior identify it.

I will run tuning advisor now and tuning advisior correctly identified proper index to be created which I had identified from my experience

Let me create index suggested by tuning advisor and it indeed worked.

Please check plan now.

ORACLE OPTIMIZER COST CALCULATIONS BASIC OVERVIEW

Cost Calculation for Full table Scan

How FTS cost depends on system statistics

Example of FTS Cost calculation (System
statistics gathered on load)

10053 trace generation :-

Cost parameters in 10053 trace:-

Example of FTS Cost calculation…..

Example of FTS (System statistics gathered on
no load)

Example of FTS in Parallel…..

Cardinality Calculation

Cost Calculation for Index based table Scan

Index range scan with one predicate in where clause

Index range scan with more predicate in where clause

Formula for Index based table scan :-

10053 trace shows:-

Calculation For Fast Full Index Scan

Formula for Index skip scan :-

optimizer_index_cost_adj:-

Calculation For Index Scan with Histogram

Oracle Optimizer Cost Calculation from explain plan

1.Let us create a test case first:-

2.Let us see what application executes SQL query and complaining poor performance

3.Now create an index

In table mytst1,I can see column owner,object_name and object_id are used.So let me create index on those column. Though We also see object_type used in query,I expect the following index should be good as We included object_id which is there in where clause(filter condition)

I can see for the table mytst2,I can see column object_id and object_name are used in where clause(filter condition) .So let me create index on those column.

Let me check execution plan of the query

The execution plan shows full table scan.It seems index is not being picked up.

6.Now let me re-look the indexes

7.Now creating exclusive index on object_id for mytst2 to avoid skip scan and drop old one.More cost improve far better.

8.Now revisit case 3 and I will show just changing the sequence of column in the index can improve plan drastically.

9.Let us see now if automatic sql tuning can provide us correct recommendation or not.

In my previous example I created some bad index first and Let sql tuning advisior identify it.

I will run tuning advisor now and tuning advisior correctly identified proper index to be created which I had identified from my experience

Let me create index suggested by tuning advisor and it indeed worked.

Please check plan now.

Cost Calculation for Full table Scan

How FTS cost depends on system statistics

Example of FTS Cost calculation (System statistics gathered on load)

10053 trace generation :-

Cost parameters in 10053 trace:-

Example of FTS Cost calculation…..

Example of FTS (System statistics gathered on no load)

Example of FTS in Parallel…..

Cardinality Calculation

Cost Calculation for Index based table Scan

Index range scan with one predicate in where clause

Index range scan with more predicate in where clause

Formula for Index based table scan :-

10053 trace shows:-

Calculation For Fast Full Index Scan

Formula for Index skip scan :-

optimizer_index_cost_adj:-

Calculation For Index Scan with Histogram

In table mytst1,I can see column owner,object_name and object_id are used.So let me create index on those column.
Though We also see object_type used in query,I expect the following index should be good as We included object_id which is there in where clause(filter condition)

Example of FTS Cost calculation (System
statistics gathered on load)

Example of FTS (System statistics gathered on
no load)