Method

1. System Statistics

System statistics are used to provide System performance metric information to the Optimizer and reflect hardware performance metrics such as I/O and CPU. These statistics are measured when Tibero first starts, and the Optimizer refers to the measured values using these statistics.

The following is an example of using System statistics. The CPU speed is measured by default, and it can be seen that Disk I/O uses internally stored default values.

SQL> col sname format a20; 
SQL> col pname format a10;
SQL> col pval2 format a6;
SQL> select * from sys._dd_aux_stats;

 SNAME               PNAME      PVAL1      PVAL2 
-------------------- ---------- ---------- ------
SYSSTATS_INFO        STATUS                READY               
SYSSTATS_INFO        STARTDT
SYSSTATS_INFO        STOPDT
SYSSTATS_MAIN        CPUSPEED   2200.02549
SYSSTATS_MAIN        SEEKTM              4
SYSSTATS_MAIN        TRFSPEED         4096
SYSSTATS_MAIN        SBLKRDTM
SYSSTATS_MAIN        MBLKRDTM
SYSSTATS_MAIN        MBLKRDCNT

9 rows selected.

The characteristics of each item in SYSSTATS_MAIN are as follows.

The SEEKTM and TRFSPEED items affect other I/O-related metrics, so adjusting these values can change the calculated cost figures.

At this time, the Cost of Table Full Scan and Index Fast Full Scan is especially influenced by I/O metrics, so these values can be adjusted to enable or suppress the selection of the scan algorithm by the Optimizer.

The following is an example of changes in cost calculation values according to changes in metrics.

• Initialize System statistics values and gather statistics information for the table

SQL> CREATE TABLE T (A NUMBER, B CHAR(100));
SQL> INSERT INTO T SELECT LEVEL, LEVEL FROM DUAL CONNECT BY LEVEL<=100000; COMMIT;
SQL> CREATE INDEX I ON T(A);
SQL> CALL DBMS_STATS.DELETE_SYSTEM_STATS;
SQL> CALL DBMS_STATS.GATHER_TABLE_STATS (USER,'T',ESTIMATE_PERCENT=>100,DEGREE=>4);

• Check which the Optimizer chooses between Index Range Scan or Full Table Scan

SQL> set linesize 150
SQL> CALL DBMS_STATS.DELETE_SYSTEM_STATS;
SQL> SELECT SNAME,PNAME,PVAL1 FROM SYS._DD_AUX_STATS;

 SNAME               PNAME      PVAL1      
-------------------- ---------- ---------- 
SYSSTATS_INFO        STATUS                               
SYSSTATS_INFO        STARTDT
SYSSTATS_INFO        STOPDT
SYSSTATS_MAIN        CPUSPEED   2200.02549
SYSSTATS_MAIN        SEEKTM              4
SYSSTATS_MAIN        TRFSPEED         4096
SYSSTATS_MAIN        SBLKRDTM
SYSSTATS_MAIN        MBLKRDTM
SYSSTATS_MAIN        MBLKRDCNT

9 rows selected.

SQL> set autotrace traceonly exp
SQL> SELECT /*+FULL(T)*/ * FROM T WHERE A<=20000;

Execution Plan
--------------------------------------------------------------------------------
1  TABLE ACCESS (FULL): T (Cost:708, %%CPU:0, Rows:20000)

Predicate Information
--------------------------------------------------------------------------------
 1 - filter: ("T"."A" <= 20000) (0.200)

SQL> SELECT /*+INDEX(T)*/ * FROM T WHERE A<=20000;
Execution Plan
--------------------------------------------------------------------------------
1  TABLE ACCESS (ROWID): T (Cost:360, %%CPU:0, Rows:20000) 
2    
INDEX (RANGE SCAN): I (Cost:45, %%CPU:0, Rows:20000)

SQL> SELECT * FROM T WHERE A<=20000;
Execution Plan
--------------------------------------------------------------------------------
1  TABLE ACCESS (ROWID): T (Cost:360, %%CPU:0, Rows:20000) 
2    
INDEX (RANGE SCAN): I (Cost:45, %%CPU:0, Rows:20000)

SQ> set autotrace off

The cost of Table Full Scan was calculated as 663, and the cost of Index Range Scan + Rowid Scan was 355, so Index Range Scan was chosen.

• After changing the Disk I/O metric TRFSPEED, recheck

SQL> set linesize 150
SQL> CALL DBMS_STATS.DELETE_SYSTEM_STATS('TRFSPEED', 60000);
SQL> SELECT SNAME,PNAME,PVAL1 FROM SYS._DD_AUX_STATS;

 SNAME               PNAME      PVAL1       
-------------------- ---------- ---------- 
SYSSTATS_INFO        STATUS                               
SYSSTATS_INFO        STARTDT
SYSSTATS_INFO        STOPDT
SYSSTATS_MAIN        CPUSPEED    2200.0268
SYSSTATS_MAIN        SEEKTM              4
SYSSTATS_MAIN        TRFSPEED        60000
SYSSTATS_MAIN        SBLKRDTM
SYSSTATS_MAIN        MBLKRDTM
SYSSTATS_MAIN        MBLKRDCNT

9 rows selected.

SQL> set autotrace traceonly exp
SQL> SELECT /*+ FULL(T) */ FROM T WHERE A <= 20000;

Execution Plan
------------------------------------------------------------
 1  TABLE ACCESS (FULL): T (Cost:225, %%CPU:1, Rows:20000)

  
SQL> SELECT /*+INDEX(T)*/ * FROM T WHERE A<=20000;

Execution Plan
--------------------------------------------------------------------------------
1  TABLE ACCESS (ROWID): T (Cost:360, %%CPU:0, Rows:20000) 
2    INDEX (RANGE SCAN): I (Cost:45, %%CPU:0, Rows:20000


SQL> SELECT * FROM T WHERE A<=20000;

Execution Plan
--------------------------------------------------------------------------------
1  TABLE ACCESS (FULL): T (Cost:225, %%CPU:1, Rows:20000)

SQL> set autotrace off

The cost of Table Full Scan decreased to 190. This is because increasing the Disk Transfer Speed metric shortened the predicted I/O time.

Depending on the server's disk environment, the performance of Full Scan and Range Scan may differ even for the same query. In this case, adjusting the metrics to match the environment can produce a plan with better performance.