Oracle分区,索引,测试(2)
Oracle分区,索引,测试(2)
这次做 select 操作每张表都是1亿
三个表的索引都disable了
先测试压缩
Sql代码
--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;
一些语句
Sql代码
--查看表空间的文件存放等
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
小了好多 ;
搜集信息
Sql代码
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 看看压缩不压缩的区别
Sql代码
---为压缩 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倍多的读取.
----------测试用不用并行的时间相差
Sql代码
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 建索引
Sql代码
--分区索引放在对应表空间
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
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秒 没有走分区索引
强制使用索引
Sql代码
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 自己的优化器... 用了比没用 多了一半的时间
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。