Oracle SQLチューニング Season2（第17回）　第3章　情報分析による性能改善事例

[実行SQL]実行

SELECT time_id、
       SUM( amount_sold )
FROM SALES_IN
WHERE time_id >= TO_DATE( :from_date , 'yyyy-mm-dd' )
AND time_id <= TO_DATE( :to_date , 'yyyy-mm-dd' )
GROUP BY time_id；

[SQL Bind値］

From_date = '2011-01-20'
To_date = '2011-01-20'

SELECT /*+ gather_plan_statistic */
       time_id,
       SUM( amount_sold )
FROM   SALES_IN
WHERE  time_id >= TO_DATE( :from_date , 'yyyy-mm-dd' )
AND    time_id <= TO_DATE( :to_date , 'yyyy-mm-dd' )
GROUP BY time_id;

SELECT * 
FROM   TABLE( dbms_xplan.display_cursor( NULL , NULL , 'allstats last' ) ); 

------------------------------------------------------------------------------------------
| Id  | Operation           | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |      1 |        |      1 |00:00:00.09 |    4441 |
|   1 |  HASH GROUP BY      |          |      1 |     50 |      1 |00:00:00.09 |    4441 |
|*  2 |   FILTER            |          |      1 |        |    327 |00:00:00.09 |    4441 |
|*  3 |    TABLE ACCESS FULL| SALES_IN |      1 |     50 |    327 |00:00:00.09 |    4441 |
------------------------------------------------------------------------------------------ 
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_DATE(:FROM_DATE,'yyyy-mm-dd')<=TO_DATE(:TO_DATE,'yyyy-mm-dd'))
   3 - filter(("TIME_ID">=TO_DATE(:FROM_DATE,'yyyy-mm-dd') AND "TIME_ID"<=TO_DATE(:TO_DATE,'yyyy-mm-dd')))

SELECT  CASE WHEN column_position = 1 THEN index_name ELSE '' END index_name
     , CASE WHEN column_name like 'SYS%'
            THEN replace(sys.xm_fn_long_to_char(table_owner
                                          , table_name
                                          , index_name
                                          , column_position),'','')
            ELSE column_name END column_name
FROM (
SELECT *
       FROM   dba_ind_columns
       WHERE  table_name  = upper(:v_tname)
       AND    index_owner = upper(:v_own)
       ORDER BY index_name, column_position
     );

INDEX_NAME                       TYPE U COLUMN LIST
-------------------------------- ---- - ------------------------------------------------------
SALES_IN_01                      NORM N CUST_ID
SALES_IN_02                      NORM N PROD_ID

SELECT column_name,
 data_type,
 data_length,
 decode(data_precision  || '/' || data_scale,'/',null,data_precision  || '/' || data_scale) dpds,
 nullable n,
 num_distinct nfv,
 density,
 num_nulls,
 num_buckets,
 sample_size,
 to_char(last_analyzed,'yyyy-mm-dd')  last_anal
FROM   all_tab_columns
WHERE  owner = upper(trim(':schname'))
AND    table_name = upper(trim(':tname'));

COLUMN_NAME      DATA_TYPE    LEN SCAL N        NFV      DENSITY  NUM_NULLS BUCKET SAMPLE_SIZE LAST_ANAL
---------------- ---------- ----- ---- - ---------- ------------ ---------- ------ ----------- ----------
PROD_ID          NUMBER        22      N         72  0.013888889          0      1    918843 2015-06-25
CUST_ID          NUMBER        22      N       7059  0.000141663          0      1    918843 2015-06-25
TIME_ID          DATE           7      N       1460  0.000684932          0      1    918843 2015-06-25
CHANNEL_ID       NUMBER        22      N          4  0.250000000          0      1    918843 2015-06-25
PROMO_ID         NUMBER        22      N          4  0.250000000          0      1    918843 2015-06-25
QUANTITY_SOLD    NUMBER        22 10/2 N          1  1.000000000          0      1    918843 2015-06-25
AMOUNT_SOLD      NUMBER        22 10/2 N       3586  0.000278862          0      1    918843 2015-06-25

SELECT b.datatype_string 、
        b.name 、
        b.value_string 、
        b.last_captured
FROM dba_hist_sqlbind b 、
       dba_hist_snapshot sn
WHERE sn.snap_id = b.snap_id
AND sql_id = :sql_id
ORDER BY b. last_captured DESC

DATATYPE NAME VALUE_STRING LAST_CAPTURED
--------------- ----------- ------------ ------------------------
VARCHAR2(2000) :FROM_DATE 20140120 2015-06-25 1:42:49 午後 1:42:49
VARCHAR2(2000) :TO_DATE	20140120 2015-06-25 1:42:49 午後 1:42:49
...省略
VARCHAR2(2000) :FROM_DATE 20140101 2015-06-25 午後 1:42:49
VARCHAR2(2000) :TO_DATE 20140131 2015-06-25 午後 1:42:49

SELECT TO_CHAR( begin_interval_time , 'yyyy' ) gatharing_date ,
       object_name  ,
       Round(MAX(space_used_total/1024/1024),1) MB
FROM   dba_hist_seg_stat s ,
       dba_hist_seg_stat_obj o ,
       dba_hist_snapshot sn
WHERE  o.owner = :owner
AND    s.obj# = o.obj#
AND    sn.snap_id = s.snap_id
AND    object_name = :obj_name
GROUP BY  TO_CHAR( begin_interval_time , 'yyyy’ ) , object_name
ORDER  BY gatharing_date  asc

GATHARING_DATE	OBJECT_NAME        MB
---------------    ------------ ---------
2011       	   SALES_IN     15.1M  
2012       	   SALES_IN     20.2M  
2013       	   SALES_IN     27.1M  
2014       	   SALES_IN     36.6M  

TABLE_NAME OWNER TAB_S LREAD PREAD LR_PR_RATE PART_TYPE PART_COL PART_CNT
------------ ---------- ----- ---------- ---------- ---------- --------- --------
SALES SH 36.9M 958394839 854954     

CREATE TABLE SALES_IN 
(
PROD_ID                        NUMBER ,                                                                                    
CUST_ID                        NUMBER ,                                                                                    
TIME_ID                        DATE   ,                                                                                    
CHANNEL_ID                     NUMBER ,                                                                                    
PROMO_ID                       NUMBER ,                                                                                    
QUANTITY_SOLD                  NUMBER ,                                                                                    
AMOUNT_SOLD                    NUMBER
)
 PARTITION BY RANGE( time_id )(
 PARTITION sales_2011_1 VALUES less than (to_date('2011-02-01','YYYY-MM-DD')),
 PARTITION sales_2011_2 VALUES less than (to_date('2011-03-01','YYYY-MM-DD')),
 PARTITION sales_2011_3 VALUES less than (to_date('2011-04-01','YYYY-MM-DD')) 
 -- 省略
 PARTITION sales_2015_1 VALUES less than (to_date('2015-02-01','YYYY-MM-DD')),
 PARTITION sales_2015_2 VALUES less than (to_date('2015-03-01','YYYY-MM-DD')),
 PARTITION sales_2015_3 VALUES less than (to_date('2015-04-01','YYYY-MM-DD')), 
 PARTITION sales_2015_4 VALUES less than (to_date('2015-05-01','YYYY-MM-DD')),
 PARTITION sales_2015_5 VALUES less than (to_date('2015-06-01','YYYY-MM-DD')),
 PARTITION sales_2015_6 VALUES less than (to_date('2015-07-01','YYYY-MM-DD'))  
 );

CREATE INDEX sales_time_idx01 ON SALES_IN (time_id) LOCAL;
CREATE INDEX sales_cust_idx01 ON SALES_IN (cust_id, time_id) LOCAL;
CREATE INDEX sales_prod_idx01 ON SALES_IN (prod_id, time_id) LOCAL;