Oracle 分区,索引,测试 (2)

s5ps398v6x

这次做 select 操作每张表都是1亿
三个表的索引都disable了
先测试压缩

--sales_data 有位图不适合
alter table sales_data1 compress;
---查看
SELECT table_name, partition_name, compression
FROM user_tab_partitions;
SELECT table_name, partition_name, compression
FROM user_tables;
---压缩
ALTER TABLE SALES_DATA1 MOVE PARTITION SALES_200901 COMPRESS PARALLEL;
ALTER TABLE SALES_DATA1 MOVE PARTITION SALES_200902 COMPRESS PARALLEL;
ALTER TABLE SALES_DATA1 MOVE PARTITION SALES_200903 COMPRESS PARALLEL;
ALTER TABLE SALES_DATA1 MOVE PARTITION SALES_200904 COMPRESS PARALLEL;
ALTER TABLE SALES_DATA1 MOVE PARTITION SALES_200905 COMPRESS PARALLEL;
 
 一些语句

--查看表空间的文件存放等
SELECT FILE_NAME,TABLESPACE_NAME,BYTES,AUTOEXTENSIBLE,
MAXBYTES,INCREMENT_BY
FROM DBA_DATA_FILES;
---表空间使用率
select   a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024   "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name)   a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name)   b
where   a.tablespace_name=b.tablespace_name
order   by   ((a.bytes-b.bytes)/a.bytes)   desc
---表空间是否自增
select file_id,file_name,tablespace_name,autoextensible,increment_by from dba_data_files order by file_id desc;
---表的大小
Select Segment_Name, Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name

  
压缩完后的比较
　　SEGMENT_NAME                                                                      SUM(BYTES)/1024/1024   
　　--------------------------------------------------------------------------------- ---------------------- 
　　SALES_DATA                                                                        3676.625               
　　SALES_DATA1                                                                       1643.625               
　　SALES_DATA2                                                                       3717      
 
小了好多 ;
 
 搜集信息

execute dbms_stats.gather_table_stats(ownname => 'TOBY', tabname => 'SALES_DATA',degree =>4);
execute dbms_stats.gather_table_stats(ownname => 'TOBY', tabname => 'SALES_DATA1',degree =>4);
execute dbms_stats.gather_table_stats(ownname => 'TOBY', tabname => 'SALES_DATA2',degree =>4);
 
做join 看看压缩不压缩的区别

---为压缩 35,167ms elapsed
select city,sum(sales_amount) from sales_data
inner join city using (city_id)
where sales_date between to_date ('2009-01-1' ,'yyyy-mm-dd') and to_date ('2009-02-28' ,'yyyy-mm-dd')
group by city;

---压缩 21,549ms
select city,sum(sales_amount) from sales_data1
inner join city using (city_id)
where sales_date between to_date ('2009-01-1' ,'yyyy-mm-dd') and to_date ('2009-02-28' ,'yyyy-mm-dd')
group by city;

 
35秒对21秒 
再看执行计划
　　Statistics
　　-----------------------------------------------------------
　　267  recursive calls
　　234762  consistent gets direct
　　234762  physical reads direct
　　0  recovery blocks read
　　0  redo buffer allocation retries
 
　　Statistics
　　-----------------------------------------------------------
　　357  recursive calls
　　104407  consistent gets direct
　　104407  physical reads direct
　　0  recovery blocks read
　　0  redo buffer allocation retries
　　
　　差不多相差2倍多的读取.
　　
 ----------测试用不用并行的时间相差

alter table sales_data NOPARALLEL;
alter table city NOPARALLEL;
select city,sum(sales_amount) from sales_data
inner join city using (city_id)
where sales_date between to_date ('2009-01-1' ,'yyyy-mm-dd') and to_date ('2009-02-28' ,'yyyy-mm-dd')
group by city;

 
　　
　　42,734ms elapsed
　　Plan hash value: 3773866511
　　
　　---------------------------------------------------------------------------------------------------------
　　| Id  | Operation                  | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
　　---------------------------------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT           |            |    23 |   805 | 58734  (13)| 00:11:45 |       |       |
　　|   1 |  SORT GROUP BY             |            |    23 |   805 | 58734  (13)| 00:11:45 |       |       |
　　|*  2 |   HASH JOIN                |            |    50M|  1679M| 53390   (4)| 00:10:41 |       |       |
　　|   3 |    TABLE ACCESS FULL       | CITY       |    23 |   437 |     3   (0)| 00:00:01 |       |       |
　　|   4 |    PARTITION RANGE ITERATOR|            |    50M|   767M| 52923   (3)| 00:10:36 |     1 |     2 |
　　|*  5 |     TABLE ACCESS FULL      | SALES_DATA |    50M|   767M| 52923   (3)| 00:10:36 |     1 |     2 |
　　---------------------------------------------------------------------------------------------------------
　　
对比 一个34秒对 42秒
对比 压缩的 就是21 对 42秒
 
 SALES_DATA 建索引

--分区索引放在对应表空间
create index index_sales_data_partition on sales_data (sales_date) local
(
partition sales_200901 tablespace ts_sales_200901,
partition sales_200902 tablespace ts_sales_200902,
partition sales_200903 tablespace ts_sales_200903,
partition sales_200904 tablespace ts_sales_200904,
partition sales_200905 tablespace ts_sales_200905,
partition sales_200906 tablespace ts_sales_200906,
partition sales_200907 tablespace ts_sales_200907,
partition sales_200908 tablespace ts_sales_200908,
partition sales_200909 tablespace ts_sales_200909,
partition sales_200910 tablespace ts_sales_200910,
partition sales_200911 tablespace ts_sales_200911,
partition sales_200912 tablespace ts_sales_200912,
partition sales_201001 tablespace ts_sales_201001,
partition sales_201002 tablespace ts_sales_201002
);
---位图
create bitmap index index_sales_data_sales_type on sales_data (sales_type) local ;
--位图连接
create bitmap index index_sales_data_city on sales_data (city.city_id)
from sales_data,city
where sales_data.city_id=city.city_id
local ;

 
跑SQL

select  city,sum(sales_amount) from sales_data
inner join city using (city_id)
where sales_date = to_date ('2009-01-1' ,'yyyy-mm-dd')  or sales_date = to_date ('2009-03-5' ,'yyyy-mm-dd')
group by city;

 
　　
　　22,493ms elapsed
　　Plan hash value: 303492610
　　
　　---------------------------------------------------------------------------------------------------------------------------------------
　　| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
　　---------------------------------------------------------------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT            |            |    23 |   805 | 14650   (3)| 00:02:56 |       |       |        |      |            |
　　|   1 |  PX COORDINATOR             |            |       |       |            |          |       |       |        |      |            |
　　|   2 |   PX SEND QC (RANDOM)       | :TQ10002   |    23 |   805 | 14650   (3)| 00:02:56 |       |       |  Q1,02 | P->S | QC (RAND)  |
　　|   3 |    SORT GROUP BY            |            |    23 |   805 | 14650   (3)| 00:02:56 |       |       |  Q1,02 | PCWP |            |
　　|   4 |     PX RECEIVE              |            |    23 |   805 | 14650   (3)| 00:02:56 |       |       |  Q1,02 | PCWP |            |
　　|   5 |      PX SEND HASH           | :TQ10001   |    23 |   805 | 14650   (3)| 00:02:56 |       |       |  Q1,01 | P->P | HASH       |
　　|   6 |       SORT GROUP BY         |            |    23 |   805 | 14650   (3)| 00:02:56 |       |       |  Q1,01 | PCWP |            |
　　|*  7 |        HASH JOIN            |            |  1820K|    60M| 14609   (3)| 00:02:56 |       |       |  Q1,01 | PCWP |            |
　　|   8 |         PX RECEIVE          |            |    23 |   437 |     1   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
　　|   9 |          PX SEND BROADCAST  | :TQ10000   |    23 |   437 |     1   (0)| 00:00:01 |       |       |  Q1,00 | P->P | BROADCAST  |
　　|  10 |           PX BLOCK ITERATOR |            |    23 |   437 |     1   (0)| 00:00:01 |       |       |  Q1,00 | PCWC |            |
　　|  11 |            TABLE ACCESS FULL| CITY       |    23 |   437 |     1   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
　　|  12 |         PX BLOCK ITERATOR   |            |  1820K|    27M| 14604   (3)| 00:02:56 |KEY(I) |KEY(I) |  Q1,01 | PCWC |            |
　　|* 13 |          TABLE ACCESS FULL  | SALES_DATA |  1820K|    27M| 14604   (3)| 00:02:56 |KEY(I) |KEY(I) |  Q1,01 | PCWP |            |
　　---------------------------------------------------------------------------------------------------------------------------------------
　　
　　Predicate Information (identified by operation id):
　　---------------------------------------------------
　　
　　7 - access("SALES_DATA"."CITY_ID"="CITY"."CITY_ID")
　　13 - filter("SALES_DATA"."SALES_DATE"=TO_DATE('2009-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') OR 
　　"SALES_DATA"."SALES_DATE"=TO_DATE('2009-03-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
　　
　　Statistics
　　-----------------------------------------------------------
　　40  recursive calls
　　234602  consistent gets direct
　　234602  physical reads direct
　　0  recovery blocks read
　　0  redo buffer allocation retries
 
 用了22秒 没有走分区索引
强制使用索引

select /*+ index(sales_data INDEX_SALES_DATA_PARTITION) */ city,sum(sales_amount) from sales_data
inner join city using (city_id)
where sales_date = to_date ('2009-01-1' ,'yyyy-mm-dd')  or sales_date = to_date ('2009-03-5' ,'yyyy-mm-dd')
group by city;

 
 
　　
　　50,296ms elapsed
　　Plan hash value: 1538767871
　　
　　-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
　　| Id  | Operation                                     | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
　　-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT                              |                            |    23 |   805 |   240K  (1)| 00:48:12 |       |       |        |      |            |
　　|   1 |  PX COORDINATOR                               |                            |       |       |            |          |       |       |        |      |            |
　　|   2 |   PX SEND QC (RANDOM)                         | :TQ10003                   |    23 |   805 |   240K  (1)| 00:48:12 |       |       |  Q1,03 | P->S | QC (RAND)  |
　　|   3 |    SORT GROUP BY                              |                            |    23 |   805 |   240K  (1)| 00:48:12 |       |       |  Q1,03 | PCWP |            |
　　|   4 |     PX RECEIVE                                |                            |    23 |   805 |   240K  (1)| 00:48:12 |       |       |  Q1,03 | PCWP |            |
　　|   5 |      PX SEND HASH                             | :TQ10002                   |    23 |   805 |   240K  (1)| 00:48:12 |       |       |  Q1,02 | P->P | HASH       |
　　|   6 |       SORT GROUP BY                           |                            |    23 |   805 |   240K  (1)| 00:48:12 |       |       |  Q1,02 | PCWP |            |
　　|*  7 |        HASH JOIN                              |                            |  1820K|    60M|   240K  (1)| 00:48:11 |       |       |  Q1,02 | PCWP |            |
　　|   8 |         PX RECEIVE                            |                            |    23 |   437 |     1   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
　　|   9 |          PX SEND HASH                         | :TQ10001                   |    23 |   437 |     1   (0)| 00:00:01 |       |       |  Q1,01 | P->P | HASH       |
　　|  10 |           PX BLOCK ITERATOR                   |                            |    23 |   437 |     1   (0)| 00:00:01 |       |       |  Q1,01 | PCWC |            |
　　|  11 |            TABLE ACCESS FULL                  | CITY                       |    23 |   437 |     1   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
　　|  12 |         BUFFER SORT                           |                            |       |       |            |          |       |       |  Q1,02 | PCWC |            |
　　|  13 |          PX RECEIVE                           |                            |  1820K|    27M|   240K  (1)| 00:48:11 |       |       |  Q1,02 | PCWP |            |
　　|  14 |           PX SEND HASH                        | :TQ10000                   |  1820K|    27M|   240K  (1)| 00:48:11 |       |       |        | S->P | HASH       |
　　|  15 |            INLIST ITERATOR                    |                            |       |       |            |          |       |       |        |      |            |
　　|  16 |             PARTITION RANGE ITERATOR          |                            |  1820K|    27M|   240K  (1)| 00:48:11 |KEY(I) |KEY(I) |        |      |            |
　　|  17 |              TABLE ACCESS BY LOCAL INDEX ROWID| SALES_DATA                 |  1820K|    27M|   240K  (1)| 00:48:11 |KEY(I) |KEY(I) |        |      |            |
　　|* 18 |               INDEX RANGE SCAN                | INDEX_SALES_DATA_PARTITION |  1831K|       |  4884   (1)| 00:00:59 |KEY(I) |KEY(I) |        |      |            |
　　-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
　　Predicate Information (identified by operation id):
　　---------------------------------------------------
　　
　　7 - access("SALES_DATA"."CITY_ID"="CITY"."CITY_ID")
　　18 - access("SALES_DATA"."SALES_DATE"=TO_DATE('2009-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') OR "SALES_DATA"."SALES_DATE"=TO_DATE('2009-03-05 00:00:00', 
　　'yyyy-mm-dd hh24:mi:ss'))
　　
　　Statistics
　　-----------------------------------------------------------
　　76  recursive calls
　　0  consistent gets direct
　　3644  physical reads direct
　　0  recovery blocks read
　　0  redo buffer allocation retries
 
用了50秒..  看来还是要用oralce 自己的优化器...  用了比没用 多了一半的时间