基于12c新特性的SQL优化比拼

需要SAMPLE SCHMEA SH;

SALES表记录数不小于29402976条
TIMES表记录数不小于58432条
customers表记录数不小于 1776000条
channels表记录数不小于160条

sqlplus /nolog
conn / as sysdba
create user shoug identified by oracle;
grant dba to shoug;

conn shoug/oracle

create tablespace inmemory 这里表空间属性可以自行决定。

create table sales tablespace inmemory as select * from sh.sales;
insert into sales select * from sales;
insert into sales select * from sales;
insert into sales select * from sales;
insert into sales select * from sales;
insert into sales select * from sales;
commit;

create table times tablespace inmemory as select * from sh.times;
insert into times select * from times;
insert into times select * from times;
insert into times select * from times;
insert into times select * from times;
insert into times select * from times;
commit;

create table customers tablespace inmemory as select * from sh.customers;

insert into customers select * from customers;
insert into customers select * from customers;
insert into customers select * from customers;
insert into customers select * from customers;
insert into customers select * from customers;
commit;

create table channels tablespace inmemory as select * from sh.channels;
insert into channels select * from channels;
insert into channels select * from channels;
insert into channels select * from channels;
insert into channels select * from channels;
insert into channels select * from channels;
commit;

alter session set "_serial_direct_read"=never;

exec dbms_stats.gather_table_stats(user,'TIMES');
exec dbms_stats.gather_table_stats(user,'CUSTOMERS');
exec dbms_stats.gather_table_stats(user,'CHANNELS');
exec dbms_stats.gather_table_stats(user,'SALES');

SELECT /*+ SHOUG ORCL-CON */ c.cust_city,
t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount
FROM SHOUG.sales s, SHOUG.times t, SHOUG.customers c
WHERE s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND c.cust_state_province = 'FL'
AND t.calendar_quarter_desc IN ('2000-01', '2000-02', '1999-12')
AND s.time_id IN
(SELECT time_id
FROM SHOUG.times
WHERE calendar_quarter_desc IN ('2000-01', '2000-02', '1999-12'))
AND s.cust_id IN
(SELECT cust_id FROM SHOUG.customers WHERE cust_state_province = 'FL')
AND s.channel_id IN
(SELECT channel_id
FROM SHOUG.channels
WHERE channel_desc = 'Direct Sales')
GROUP BY c.cust_city, t.calendar_quarter_desc;

** 基于12c IN-MEMORY COLUMN STORE 特性优化以上SQL的运行速度,不考虑 索引、物化视图、Result Cache等特性; 不可以修改SQL ,但可以调整执行计划。
以上环境 需要SAMPLE SCHMEA SALES , 且要求:

SALES表记录数不小于29402976条
TIMES表记录数不小于58432条
customers表记录数不小于 1776000条
channels表记录数不小于160条

从题目公布开始 到17点截止 ,提交答案请到F.SHOUG.INFO 原帖将实验详细过程贴出,优先考量SQL执行速度,精确到 毫秒 ms。若执行耗时相同, 则先发帖者优先。

获胜者将获得IPAD AIR 2。
参与者 前15名获得参与奖。​**
2014-11-08 13:19 添加评论 分享
已邀请:
1

jx13175

赞同来自: robbins

来一个屌丝的测试;

--开启inmemory;

Elapsed: 00:01:28.98

Statistics

1475 recursive calls
22 db block gets
93422 consistent gets
91848 physical reads
1852 redo size
1584 bytes sent via SQLNet to client
562 bytes received via SQL
Net from client
3 SQL*Net roundtrips to/from client
75 sorts (memory)
0 sorts (disk)
24 rows processed

--关闭inmemory;

Elapsed: 00:01:14.59

Statistics

345 recursive calls
21 db block gets
192131 consistent gets
191536 physical reads
1880 redo size
1584 bytes sent via SQLNet to client
562 bytes received via SQL
Net from client
3 SQL*Net roundtrips to/from client
18 sorts (memory)
0 sorts (disk)
24 rows processed

小结; 我给inmemory开了500M,测试证明,这是土豪的游戏。
0

cuizhou - 酷爱钻研oracle hadoop greenplum java 技术并成为专家帮助别人成为专家

赞同来自:

OKAY 我们来优化一下, 既然索引,物化视图等传统技术无法使用,我们只能使用使用一些oracle的大数据处理技术来提高性能
  1. 首先创建表 scripts 可以查看 xxxxxxxx
    这里提一下, 在创建表的时候使用pctfree 0 来适当的降低了逻辑读。

创建完毕

COUNT(*)||'TIME_ROWS'

58432 time_rows
29402976 sales_rows
1776000 customers_rows
160 channles_rows

创建完后 跑了一下

no tuning
172706 consistent gets
Elapsed: 00:00:22.11

oooooopss~ 22秒 看来需要优化
  1. 开始使用 in-memory 组件 来优化

SQL> select * from v$version;
BANNER
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL> show parameter inmemory

NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
inmemory_clause_default string
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 7
inmemory_query string ENABLE
inmemory_size big integer 16G
inmemory_trickle_repopulate_servers_ integer 1
percent
optimizer_inmemory_aware boolean TRUE

如果内存有限 可以适当的只存放 需要的 列来降低使用memory

alter table SHOUG.times inmemory;
alter table SHOUG.sales inmemory;
alter table shoug.sales no inmemory(PROD_ID,PROMO_ID,QUANTITY_SOLD);
alter table shoug.customers inmemory;
alter table SHOUG.channels inmemory;

Statistics

41 recursive calls
17 db block gets
54 consistent gets
2 physical reads
1188 redo size
1584 bytes sent via SQLNet to client
562 bytes received via SQL
Net from client
3 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
24 rows processed

Elapsed: 00:00:19.70

可以看到 物理读几乎已经很弱了, 但是速度还是不快
  1. 优化CPU使用, 可以看到 inmemory 使用后 cpu 使用率达到了100% 但是, 可以看到等待全落在了 单颗 cpu上

所以根据数据量的大小, 来设置并行度
conn shoug/oracle
alter table shoug.sales parallel 8;
alter table shoug.times parallel 1;
alter table shoug.customers parallel 8;
alter table shoug.channel parallel 4;

select table_name,degree from user_tables;

set timing on
SELECT /* use inmemory / /+parallel (shoug.customers 8)*/ c.cust_city,
t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount
FROM SHOUG.sales s, SHOUG.times t, SHOUG.customers c
WHERE s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND c.cust_state_province = 'FL'
AND t.calendar_quarter_desc IN ('2000-01', '2000-02', '1999-12')
AND s.time_id IN
(SELECT time_id
FROM SHOUG.times
WHERE calendar_quarter_desc IN ('2000-01', '2000-02', '1999-12'))
AND s.cust_id IN
(SELECT cust_id FROM SHOUG.customers WHERE cust_state_province = 'FL')
AND s.channel_id IN
(SELECT channel_id
FROM SHOUG.channels
WHERE channel_desc = 'Direct Sales')
GROUP BY c.cust_city, t.calendar_quarter_desc;

24 rows selected.

Elapsed: 00:00:01.37

Statistics

203 recursive calls
0 db block gets
254 consistent gets
0 physical reads
0 redo size
1574 bytes sent via SQLNet to client
562 bytes received via SQL
Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)

[root@db ~]# top
top - 23:51:34 up 6 days, 18:18, 6 users, load average: 0.65, 0.17, 0.15
Tasks: 391 total, 3 running, 387 sleeping, 0 stopped, 1 zombie
Cpu0 : 23.3%us, 0.0%sy, 0.0%ni, 76.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu1 : 22.6%us, 0.3%sy, 0.0%ni, 77.1%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu2 : 23.7%us, 0.3%sy, 0.0%ni, 76.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu3 : 22.3%us, 0.0%sy, 0.0%ni, 77.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu4 : 54.8%us, 0.7%sy, 0.0%ni, 44.5%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu5 : 22.1%us, 0.0%sy, 0.0%ni, 77.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu6 : 24.3%us, 0.0%sy, 0.0%ni, 75.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu7 : 22.6%us, 0.3%sy, 0.0%ni, 77.1%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 32882416k total, 32061328k used, 821088k free, 13416k buffers
Swap: 8388600k total, 52k used, 8388548k free, 30221056k cached

可以看到cpu使用率达到了30% 以上, 并且, 已经没有内存排序

PS: 恭喜 oracle 在12.1.0.2 版本内 以inmemory 列存储的方式 推出了 vector计算方式, 打破了actian vector db 在大数据市场独领风骚的格局。
0

robbins - 80后dba

赞同来自:

export ORACLE_SID=nonCDB
sqlplus / as sysdba
alter system set sga_max_size=3172m scope=spfile;
alter system set sga_target=3172m scope=spfile;
alter system set INMEMORY_SIZE=2048m scope=spfile;

[oracle@db12c ~]$ export ORACLE_SID=nonCDB
[oracle@db12c ~]$
[oracle@db12c ~]$
[oracle@db12c ~]$
[oracle@db12c ~]$
[oracle@db12c ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 9 14:57:47 2014

Copyright (c) 1982, 2014, Oracle. All rights reserved.

showConnected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1241513984 bytes
Fixed Size 2923872 bytes
Variable Size 503317152 bytes
Database Buffers 721420288 bytes
Redo Buffers 13852672 bytes
SQL> alter system set sga_max_size=3172m scope=spfile;
alter system set sga_target=3172m scope=spfile;
alter system set INMEMORY_SIZE=2048m scope=spfile;

System altered.

SQL>
System altered.

SQL>
System altered.

SQL>
SQL>
SQL>
SQL>
SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area 3338665984 bytes
Fixed Size 2929888 bytes
Variable Size 520096544 bytes
Database Buffers 654311424 bytes
Redo Buffers 13844480 bytes
In-Memory Area 2147483648 bytes
Database mounted.
Database opened.
SQL> select name from v$dbfile;

NAME

/u01/app/oracle/oradata/nonCDB/users01.dbf
/u01/app/oracle/oradata/nonCDB/undotbs01.dbf
/u01/app/oracle/oradata/nonCDB/system01.dbf
/u01/app/oracle/oradata/nonCDB/sysaux01.dbf
/u01/app/oracle/oradata/nonCDB/example01.dbf

SQL> create tablespace inmemory datafile '/u01/app/oracle/oradata/nonCDB/inmemory01.dbf' size 4096m;

Tablespace created.

SQL> SQL> create user shoug identified by oracle default tablespace inmemory;
grant dba to shoug;

conn shoug/oracle
User created.

SQL>
Grant succeeded.

SQL> SQL>
SQL> SQL>

Connected.
SQL> SQL> SQL> show user
USER is "SHOUG"
SQL> create table sales tablespace inmemory as select * from sh.sales;
insert into sales select * from sales;
insert into sales select * from sales;
insert into sales select * from sales;
insert into sales select * from sales;
insert into sales select * from sales;
commit;

Table created.

SQL>
918843 rows created.

SQL>

1837686 rows created.

SQL>

3675372 rows created.

SQL>
7350744 rows created.

SQL>

14701488 rows created.

SQL>
Commit complete.

SQL> SQL> SQL> SQL> SQL>
SQL>
SQL> select count(*) from sales;

COUNT(*)

29402976

SQL> create table times tablespace inmemory as select * from sh.times;
insert into times select * from times;
insert into times select * from times;
insert into times select * from times;
insert into times select * from times;
insert into times select * from times;
commit;

Table created.

SQL>
1826 rows created.

SQL>
3652 rows created.

SQL>
7304 rows created.

SQL>
14608 rows created.

SQL>
29216 rows created.

SQL>
Commit complete.

SQL>
SQL> select count(*) from times;

COUNT(*)

58432

SQL>

SQL> create table customers tablespace inmemory as select * from sh.customers;

insert into customers select * from customers;
insert into customers select * from customers;
insert into customers select * from customers;
insert into customers select * from customers;
insert into customers select * from customers;
commit;

Table created.

SQL> SQL>
55500 rows created.

SQL>
111000 rows created.

SQL>
222000 rows created.

SQL>

444000 rows created.

SQL>

888000 rows created.

SQL>
Commit complete.

SQL> SQL> SQL> SQL> SQL>
SQL>
SQL>
SQL> select count(*) from customers;

COUNT(*)

1776000

SQL>

SQL> create table channels tablespace inmemory as select * from sh.channels;
insert into channels select * from channels;
insert into channels select * from channels;
insert into channels select * from channels;
insert into channels select * from channels;
insert into channels select * from channels;
commit;

Table created.

SQL>
5 rows created.

SQL>
10 rows created.

SQL>
20 rows created.

SQL>
40 rows created.

SQL>
80 rows created.

SQL>
Commit complete.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select count(*) from channels;

COUNT(*)

160

SQL>

SQL> alter table sales inmemory;
alter table customers inmemory;
alter table times inmemory;
alter table channels inmemory;

ALTER TABLE SHOUG.sales
INMEMORY MEMCOMPRESS FOR QUERY HIGH (time_id,cust_id,channel_id)
INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (amount_sold);

ALTER TABLE SHOUG.customers
INMEMORY MEMCOMPRESS FOR QUERY HIGH (cust_id,cust_state_province)
INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (cust_city);

ALTER TABLE SHOUG.times
INMEMORY MEMCOMPRESS FOR QUERY HIGH (time_id,calendar_quarter_desc);

ALTER TABLE SHOUG.channels
INMEMORY MEMCOMPRESS FOR QUERY HIGH (channel_id,channel_desc);
Table altered.

SQL>
Table altered.

SQL>
Table altered.

SQL>
Table altered.

SQL> SQL> 2 3

Table altered.

SQL> SQL> 2 3

Table altered.

SQL> SQL> 2

Table altered.

SQL> SQL> SQL> 2

Table altered.

SQL>
SQL>
SQL>

SQL> exec dbms_stats.gather_table_stats(user,'TIMES');
exec dbms_stats.gather_table_stats(user,'CUSTOMERS');
exec dbms_stats.gather_table_stats(user,'CHANNELS');
exec dbms_stats.gather_table_stats(user,'SALES');

PL/SQL procedure successfully completed.

SQL>

PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>

PL/SQL procedure successfully completed.

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
SQL>

SET AUTOTRACE TRACEONLY
--Fisrt time

SQL> SET AUTOTRACE TRACEONLY

SQL> SQL> SELECT /* SHOUG ORCL-CON */ c.cust_city,
t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount
FROM SHOUG.sales s, SHOUG.times t, SHOUG.customers c
WHERE s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND c.cust_state_province = 'FL'
AND t.calendar_quarter_desc IN ('2000-01', '2000-02', '1999-12')
AND s.time_id IN
(SELECT time_id
FROM SHOUG.times
WHERE calendar_quarter_desc IN ('2000-01', '2000-02', '1999-12'))
AND s.cust_id IN
(SELECT cust_id FROM SHOUG.customers WHERE cust_state_province = 'FL')
AND s.channel_id IN
(SELECT channel_id
FROM SHOUG.channels
WHERE channel_desc = 'Direct Sales')
GROUP BY c.cust_city, t.calendar_quarter_desc; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

24 rows selected.

Execution Plan

Plan hash value: 739059432

--------------------------------------------------------------------------------

| Id | Operation | Name | Row
s | Bytes |TempSpc| Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 87
69 | 693K| | 3326 (18)| 00:00:01 |

| 1 | TEMP TABLE TRANSFORMATION | |
| | | | |

| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6646_19DD0B |
| | | | |

| 3 | VECTOR GROUP BY | |
20 | 320 | | 24 (13)| 00:00:01 |

| 4 | KEY VECTOR CREATE BUFFERED | :KV0000 |
| | | | |

|* 5 | TABLE ACCESS INMEMORY FULL | TIMES | 57
33 | 91728 | | 23 (9)| 00:00:01 |

| 6 | LOAD AS SELECT | SYS_TEMP_0FD9D6647_19DD0B |
| | | | |

| 7 | VECTOR GROUP BY | | 6
20 | 16120 | 2592K| 1069 (3)| 00:00:01 |

| 8 | KEY VECTOR CREATE BUFFERED | :KV0001 |
| | | | |

|* 9 | TABLE ACCESS INMEMORY FULL | CUSTOMERS | 729
70 | 1852K| | 527 (5)| 00:00:01 |

| 10 | HASH GROUP BY | | 87
69 | 693K| 808K| 2233 (25)| 00:00:01 |

|* 11 | HASH JOIN | | 87
69 | 693K| | 2064 (27)| 00:00:01 |

| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6647_19DD0B | 6
20 | 16120 | | 3 (0)| 00:00:01 |

|* 13 | HASH JOIN | | 87
69 | 470K| | 2061 (27)| 00:00:01 |

| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6646_19DD0B |
20 | 320 | | 2 (0)| 00:00:01 |

| 15 | VIEW | VW_VT_BCA3D1B3 | 87
69 | 333K| | 2059 (27)| 00:00:01 |

| 16 | VECTOR GROUP BY | | 87
69 | 291K| | 2059 (27)| 00:00:01 |

| 17 | HASH GROUP BY | | 87
69 | 291K| | 2059 (27)| 00:00:01 |

| 18 | KEY VECTOR USE | :KV0000 |
| | | | |

| 19 | KEY VECTOR USE | :KV0001 |
| | | | |

|* 20 | HASH JOIN | |
23M| 762M| | 2032 (26)| 00:00:01 |

| 21 | JOIN FILTER CREATE | :BF0000 |
32 | 416 | | 1 (0)| 00:00:01 |

| 22 | SORT UNIQUE | |
32 | 416 | | 1 (0)| 00:00:01 |

|* 23 | TABLE ACCESS INMEMORY FULL| CHANNELS |
32 | 416 | | 1 (0)| 00:00:01 |

| 24 | JOIN FILTER USE | :BF0000 |
29M| 588M| | 1957 (23)| 00:00:01 |

|* 25 | TABLE ACCESS INMEMORY FULL | SALES |
29M| 588M| | 1957 (23)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

5 - inmemory("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_D
ESC"='2000-01' OR

"T"."CALENDAR_QUARTER_DESC"='2000-02')
filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DES
C"='2000-01' OR

"T"."CALENDAR_QUARTER_DESC"='2000-02')
9 - inmemory("C"."CUST_STATE_PROVINCE"='FL')
filter("C"."CUST_STATE_PROVINCE"='FL')
11 - access("ITEM_10"=INTERNAL_FUNCTION("C0") AND "ITEM_11"="C2")
13 - access("ITEM_8"=INTERNAL_FUNCTION("C0") AND "ITEM_9"="C2")
20 - access("S"."CHANNEL_ID"="CHANNEL_ID")
23 - inmemory("CHANNEL_DESC"='Direct Sales')
filter("CHANNEL_DESC"='Direct Sales')
25 - inmemory(SYS_OP_BLOOM_FILTER(:BF0000,"S"."CHANNEL_ID"))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."CHANNEL_ID"))

Note

- dynamic statistics used: dynamic sampling (level=2)
- 2 Sql Plan Directives used for this statement
- vector transformation used for this statement

Statistics

122 recursive calls
20 db block gets
114770 consistent gets
113385 physical reads
1940 redo size
1584 bytes sent via SQLNet to client
562 bytes received via SQL
Net from client
3 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
24 rows processed

SQL> SQL>

--Second Times

SQL> SELECT /* SHOUG ORCL-CON */ c.cust_city,
t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount
FROM SHOUG.sales s, SHOUG.times t, SHOUG.customers c
WHERE s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND c.cust_state_province = 'FL'
AND t.calendar_quarter_desc IN ('2000-01', '2000-02', '1999-12')
AND s.time_id IN
(SELECT time_id
FROM SHOUG.times
WHERE calendar_quarter_desc IN ('2000-01', '2000-02', '1999-12'))
AND s.cust_id IN
(SELECT cust_id FROM SHOUG.customers WHERE cust_state_province = 'FL')
AND s.channel_id IN
(SELECT channel_id
FROM SHOUG.channels
WHERE channel_desc = 'Direct Sales')
GROUP BY c.cust_city, t.calendar_quarter_desc; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

24 rows selected.

Elapsed: 00:03:44.95

Execution Plan

Plan hash value: 739059432

--------------------------------------------------------------------------------

| Id | Operation | Name | Row
s | Bytes |TempSpc| Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 87
69 | 693K| | 3364 (18)| 00:00:01 |

| 1 | TEMP TABLE TRANSFORMATION | |
| | | | |

| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6666_19DD0B |
| | | | |

| 3 | VECTOR GROUP BY | |
20 | 320 | | 24 (13)| 00:00:01 |

| 4 | KEY VECTOR CREATE BUFFERED | :KV0000 |
| | | | |

|* 5 | TABLE ACCESS INMEMORY FULL | TIMES | 72
64 | 113K| | 23 (9)| 00:00:01 |

| 6 | LOAD AS SELECT | SYS_TEMP_0FD9D6667_19DD0B |
| | | | |

| 7 | VECTOR GROUP BY | | 6
20 | 16120 | 2776K| 1107 (3)| 00:00:01 |

| 8 | KEY VECTOR CREATE BUFFERED | :KV0001 |
| | | | |

|* 9 | TABLE ACCESS INMEMORY FULL | CUSTOMERS | 780
16 | 1980K| | 527 (5)| 00:00:01 |

| 10 | HASH GROUP BY | | 87
69 | 693K| 808K| 2233 (25)| 00:00:01 |

|* 11 | HASH JOIN | | 87
69 | 693K| | 2064 (27)| 00:00:01 |

| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6667_19DD0B | 6
20 | 16120 | | 3 (0)| 00:00:01 |

|* 13 | HASH JOIN | | 87
69 | 470K| | 2061 (27)| 00:00:01 |

| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6666_19DD0B |
20 | 320 | | 2 (0)| 00:00:01 |

| 15 | VIEW | VW_VT_BCA3D1B3 | 87
69 | 333K| | 2059 (27)| 00:00:01 |

| 16 | VECTOR GROUP BY | | 87
69 | 291K| | 2059 (27)| 00:00:01 |

| 17 | HASH GROUP BY | | 87
69 | 291K| | 2059 (27)| 00:00:01 |

| 18 | KEY VECTOR USE | :KV0000 |
| | | | |

| 19 | KEY VECTOR USE | :KV0001 |
| | | | |

|* 20 | HASH JOIN | |
23M| 762M| | 2032 (26)| 00:00:01 |

| 21 | JOIN FILTER CREATE | :BF0000 |
32 | 416 | | 1 (0)| 00:00:01 |

| 22 | SORT UNIQUE | |
32 | 416 | | 1 (0)| 00:00:01 |

|* 23 | TABLE ACCESS INMEMORY FULL| CHANNELS |
32 | 416 | | 1 (0)| 00:00:01 |

| 24 | JOIN FILTER USE | :BF0000 |
29M| 588M| | 1957 (23)| 00:00:01 |

|* 25 | TABLE ACCESS INMEMORY FULL | SALES |
29M| 588M| | 1957 (23)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

5 - inmemory("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_D
ESC"='2000-01' OR

"T"."CALENDAR_QUARTER_DESC"='2000-02')
filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DES
C"='2000-01' OR

"T"."CALENDAR_QUARTER_DESC"='2000-02')
9 - inmemory("C"."CUST_STATE_PROVINCE"='FL')
filter("C"."CUST_STATE_PROVINCE"='FL')
11 - access("ITEM_10"=INTERNAL_FUNCTION("C0") AND "ITEM_11"="C2")
13 - access("ITEM_8"=INTERNAL_FUNCTION("C0") AND "ITEM_9"="C2")
20 - access("S"."CHANNEL_ID"="CHANNEL_ID")
23 - inmemory("CHANNEL_DESC"='Direct Sales')
filter("CHANNEL_DESC"='Direct Sales')
25 - inmemory(SYS_OP_BLOOM_FILTER(:BF0000,"S"."CHANNEL_ID"))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."CHANNEL_ID"))

Note

- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
- vector transformation used for this statement

Statistics

110 recursive calls
16 db block gets
89 consistent gets
3 physical reads
1188 redo size
1573 bytes sent via SQLNet to client
562 bytes received via SQL
Net from client
3 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
24

可以看到 一致性读由 114770 consistent gets变成了89。

CUST_CITY CALENDAR_QUARTER_DESC SALES_AMOUNT
Sanibel 2000-02 586831298.56
Ocala 2000-02 232054128.64
Candler 2000-01 136521973.76
Cypress Gardens 2000-01 116189429.76
Palmdale 2000-02 846372536.32
Molino 2000-01 614899712
Ocala 2000-01 234184048.64
Palmdale 2000-01 1238415441.92
Molino 2000-02 561797529.6
Saint Marks 2000-01 1827843932.16
Evinston 2000-02 2053151457.28
Noma 2000-01 1100105318.4
Winter Springs 2000-02 655360
Candler 2000-02 212793425.92
Saint Marks 2000-02 1601001881.6
Sugarloaf Key 2000-01 394122362.88
Sugarloaf Key 2000-02 316520529.92
Blountstown 2000-02 1246906941.44
Cypress Gardens 2000-02 161511178.24
Evinston 2000-01 1753405521.92
Noma 2000-02 783272509.44
Sanibel 2000-01 520039301.12
Winter Springs 2000-01 1030881.28
Blountstown 2000-01 885545369.6
0

fangtr

赞同来自:

SQL>
SQL> alter system flush buffer_cache;
alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.04
SQL>
System altered.

Elapsed: 00:00:00.02
SQL>
SQL>
SQL> /

24 rows selected.

Elapsed: 00:00:02.93

Execution Plan

Plan hash value: 2613104691

--------------------------------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |

| 0 | SELECT STATEMENT | | 1316 | 114K| 373 (24)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10004 | 1316 | 114K| 373 (24)| 00:00:01 | Q1,04 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 1316 | 114K| 373 (24)| 00:00:01 | Q1,04 | PCWP | |
| 4 | PX RECEIVE | | 1316 | 114K| 373 (24)| 00:00:01 | Q1,04 | PCWP | |
| 5 | PX SEND HASH | :TQ10003 | 1316 | 114K| 373 (24)| 00:00:01 | Q1,03 | P->P | HASH |
| 6 | HASH GROUP BY | | 1316 | 114K| 373 (24)| 00:00:01 | Q1,03 | PCWP | |
|* 7 | HASH JOIN | | 4067K| 345M| 360 (21)| 00:00:01 | Q1,03 | PCWP | |
| 8 | PX RECEIVE | | 12248 | 310K| 73 (5)| 00:00:01 | Q1,03 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10001 | 12248 | 310K| 73 (5)| 00:00:01 | Q1,01 | P->P | BROADCAST |
| 10 | PX BLOCK ITERATOR | | 12248 | 310K| 73 (5)| 00:00:01 | Q1,01 | PCWC | |
|* 11 | TABLE ACCESS INMEMORY FULL | CUSTOMERS | 12248 | 310K| 73 (5)| 00:00:01 | Q1,01 | PCWP | |
|* 12 | HASH JOIN RIGHT SEMI | | 3665K| 220M| 286 (25)| 00:00:01 | Q1,03 | PCWP | |
|* 13 | TABLE ACCESS INMEMORY FULL | CHANNELS | 32 | 416 | 2 (0)| 00:00:01 | Q1,03 | PCWP | |
|* 14 | HASH JOIN | | 3665K| 174M| 283 (25)| 00:00:01 | Q1,03 | PCWP | |
| 15 | PX RECEIVE | | 182 | 5278 | 4 (25)| 00:00:01 | Q1,03 | PCWP | |
| 16 | PX SEND BROADCAST | :TQ10002 | 182 | 5278 | 4 (25)| 00:00:01 | Q1,02 | P->P | BROADCAST |
| 17 | VIEW | VW_GBF_3 | 182 | 5278 | 4 (25)| 00:00:01 | Q1,02 | PCWP | |
| 18 | HASH GROUP BY | | 182 | 2912 | 4 (25)| 00:00:01 | Q1,02 | PCWP | |
| 19 | PX RECEIVE | | 182 | 2912 | 4 (25)| 00:00:01 | Q1,02 | PCWP | |
| 20 | PX SEND HASH | :TQ10000 | 182 | 2912 | 4 (25)| 00:00:01 | Q1,00 | P->P | HASH |
| 21 | HASH GROUP BY | | 182 | 2912 | 4 (25)| 00:00:01 | Q1,00 | PCWP | |
| 22 | PX BLOCK ITERATOR | | 4627 | 74032 | 3 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 23 | TABLE ACCESS INMEMORY FULL| TIMES | 4627 | 74032 | 3 (0)| 00:00:01 | Q1,00 | PCWP | |
| 24 | PX BLOCK ITERATOR | | 29M| 588M| 270 (22)| 00:00:01 | Q1,03 | PCWC | |

| 25 | TABLE ACCESS INMEMORY FULL | SALES | 29M| 588M| 270 (22)| 00:00:01 | Q1,03 | PCWP | |

Predicate Information (identified by operation id):

7 - access("S"."CUST_ID"="C"."CUST_ID")
11 - inmemory("C"."CUST_STATE_PROVINCE"='FL')
filter("C"."CUST_STATE_PROVINCE"='FL')
12 - access("S"."CHANNEL_ID"="CHANNEL_ID")
13 - inmemory("CHANNEL_DESC"='Direct Sales')
filter("CHANNEL_DESC"='Direct Sales')
14 - access("S"."TIME_ID"="ITEM_1")
23 - inmemory("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
"T"."CALENDAR_QUARTER_DESC"='2000-02')
filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
"T"."CALENDAR_QUARTER_DESC"='2000-02')

Note

- dynamic statistics used: dynamic sampling (level=AUTO)
- Degree of Parallelism is 8 because of hint

Statistics

2440 recursive calls
0 db block gets
3517 consistent gets
182 physical reads
0 redo size
1598 bytes sent via SQLNet to client
562 bytes received via SQL
Net from client
3 SQL*Net roundtrips to/from client
132 sorts (memory)
0 sorts (disk)
24 rows processed
0

zhlh_xt - 80后IT男

赞同来自:

--No INMEMORY Features
[oracle@ol64 ~]$ sql

SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 9 16:38:48 2014

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> host cat noinmemory.sql
ALTER TABLE shoug.TIMES NO INMEMORY;
ALTER TABLE shoug.CUSTOMERS NO INMEMORY;
ALTER TABLE shoug.SALES NO INMEMORY;
ALTER TABLE shoug.CHANNELS NO INMEMORY;

SQL> @noinmemory

Table altered.

Table altered.

Table altered.

Table altered.

SQL> @tune
Connected.

CUST_CITY CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Sanibel 2000-02 586831299
Ocala 2000-02 232054129
Candler 2000-01 136521974
Cypress Gardens 2000-01 116189430
Palmdale 2000-02 846372536
Molino 2000-01 614899712
Ocala 2000-01 234184049
Palmdale 2000-01 1238415442
Molino 2000-02 561797530
Saint Marks 2000-01 1827843932
Evinston 2000-02 2053151457

CUST_CITY CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Noma 2000-01 1100105318
Winter Springs 2000-02 655360
Candler 2000-02 212793426
Saint Marks 2000-02 1601001882
Sugarloaf Key 2000-01 394122363
Sugarloaf Key 2000-02 316520530
Blountstown 2000-02 1246906941
Cypress Gardens 2000-02 161511178
Evinston 2000-01 1753405522
Noma 2000-02 783272509
Sanibel 2000-01 520039301

CUST_CITY CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Winter Springs 2000-01 1030881.28
Blountstown 2000-01 885545370

24 rows selected.

Elapsed: 00:00:49.24

Execution Plan

Plan hash value: 505934089

-------------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 8769 | 693K| | 54592 (1)| 00:00:03 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D661D_1E871F | | | | | |
| 3 | VECTOR GROUP BY | | 20 | 320 | | 482 (1)| 00:00:01 |
| 4 | KEY VECTOR CREATE BUFFERED| :KV0000 | | | | | |
|* 5 | TABLE ACCESS FULL | TIMES | 8765 | 136K| | 481 (1)| 00:00:01 |
| 6 | LOAD AS SELECT | SYS_TEMP_0FD9D661E_1E871F | | | | | |
| 7 | VECTOR GROUP BY | | 620 | 16120 | 2600K| 13814 (1)| 00:00:01 |
| 8 | KEY VECTOR CREATE BUFFERED| :KV0001 | | | | | |
|* 9 | TABLE ACCESS FULL | CUSTOMERS | 73153 | 1857K| | 13270 (1)| 00:00:01 |
| 10 | HASH GROUP BY | | 8769 | 693K| 808K| 40295 (1)| 00:00:02 |
|* 11 | HASH JOIN | | 8769 | 693K| | 40126 (1)| 00:00:02 |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D661E_1E871F | 620 | 16120 | | 3 (0)| 00:00:01 |
|* 13 | HASH JOIN | | 8769 | 470K| | 40123 (1)| 00:00:02 |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D661D_1E871F | 20 | 320 | | 2 (0)| 00:00:01 |
| 15 | VIEW | VW_VT_BCA3D1B3 | 8769 | 333K| | 40120 (1)| 00:00:02 |
| 16 | VECTOR GROUP BY | | 8769 | 291K| | 40120 (1)| 00:00:02 |
| 17 | HASH GROUP BY | | 8769 | 291K| | 40120 (1)| 00:00:02 |
| 18 | KEY VECTOR USE | :KV0000 | | | | | |
| 19 | KEY VECTOR USE | :KV0001 | | | | | |
|* 20 | HASH JOIN | | 23M| 762M| | 40094 (1)| 00:00:02 |
| 21 | SORT UNIQUE | | 32 | 416 | | 4 (0)| 00:00:01 |
|* 22 | TABLE ACCESS FULL | CHANNELS | 32 | 416 | | 4 (0)| 00:00:01 |

| 23 | TABLE ACCESS FULL | SALES | 29M| 588M| | 40015 (1)| 00:00:02 |

Predicate Information (identified by operation id):

5 - filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
"T"."CALENDAR_QUARTER_DESC"='2000-02')
9 - filter("C"."CUST_STATE_PROVINCE"='FL')
11 - access("ITEM_10"=INTERNAL_FUNCTION("C0") AND "ITEM_11"="C2")
13 - access("ITEM_8"=INTERNAL_FUNCTION("C0") AND "ITEM_9"="C2")
20 - access("S"."CHANNEL_ID"="CHANNEL_ID")
22 - filter("CHANNEL_DESC"='Direct Sales')

Note

- dynamic statistics used: dynamic sampling (level=2)
- 3 Sql Plan Directives used for this statement
- vector transformation used for this statement

Statistics

301 recursive calls
22 db block gets
197353 consistent gets
141478 physical reads
1896 redo size
1584 bytes sent via SQLNet to client
563 bytes received via SQL
Net from client
3 SQL*Net roundtrips to/from client
14 sorts (memory)
0 sorts (disk)
24 rows processed

SQL>

--Use INMEMORY Features
SQL> show parameter inmemory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default string
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 3
inmemory_query string ENABLE
inmemory_size big integer 2G
inmemory_trickle_repopulate_servers_ integer 50
percent
optimizer_inmemory_aware boolean TRUE
SQL>

SQL> host cat tune.sql
connect shoug/oracle
set timing on
set linesize 160
set autotrace on
SELECT /*+ SHOUG ORCL-CON */ c.cust_city,
t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount
FROM SHOUG.sales s, SHOUG.times t, SHOUG.customers c
WHERE s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND c.cust_state_province = 'FL'
AND t.calendar_quarter_desc IN ('2000-01', '2000-02', '1999-12')
AND s.time_id IN
(SELECT time_id
FROM SHOUG.times
WHERE calendar_quarter_desc IN ('2000-01', '2000-02', '1999-12'))
AND s.cust_id IN
(SELECT cust_id FROM SHOUG.customers WHERE cust_state_province = 'FL')
AND s.channel_id IN
(SELECT channel_id
FROM SHOUG.channels
WHERE channel_desc = 'Direct Sales')
GROUP BY c.cust_city, t.calendar_quarter_desc;

SQL>

SQL> host cat inmemory.sql
ALTER TABLE shoug.TIMES INMEMORY MEMCOMPRESS FOR CAPACITY LOW;
ALTER TABLE shoug.CUSTOMERS INMEMORY MEMCOMPRESS FOR CAPACITY LOW;
ALTER TABLE shoug.SALES INMEMORY MEMCOMPRESS FOR CAPACITY LOW;
ALTER TABLE shoug.CHANNELS INMEMORY MEMCOMPRESS FOR CAPACITY LOW;

SQL>

SQL> @tune

Connected.

Message from syslogd@ol64 at Nov 9 16:35:44 ...
kernel:BUG: soft lockup - CPU#2 stuck for 28s! [oracle_4620_tes:4620]

CUST_CITY CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Sanibel 2000-02 586831299
Ocala 2000-02 232054129
Candler 2000-01 136521974
Cypress Gardens 2000-01 116189430
Palmdale 2000-02 846372536
Molino 2000-01 614899712
Ocala 2000-01 234184049
Palmdale 2000-01 1238415442
Molino 2000-02 561797530
Saint Marks 2000-01 1827843932
Evinston 2000-02 2053151457

CUST_CITY CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Noma 2000-01 1100105318
Winter Springs 2000-02 655360
Candler 2000-02 212793426
Saint Marks 2000-02 1601001882
Sugarloaf Key 2000-01 394122363
Sugarloaf Key 2000-02 316520530
Blountstown 2000-02 1246906941
Cypress Gardens 2000-02 161511178
Evinston 2000-01 1753405522
Noma 2000-02 783272509
Sanibel 2000-01 520039301

CUST_CITY CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Winter Springs 2000-01 1030881.28
Blountstown 2000-01 885545370

24 rows selected.

Elapsed: 00:01:21.76

Execution Plan

Plan hash value: 739059432

----------------------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 8769 | 693K| | 3306 (17)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6619_1E871F | | | | | |
| 3 | VECTOR GROUP BY | | 20 | 320 | | 21 (15)| 00:00:01 |
| 4 | KEY VECTOR CREATE BUFFERED | :KV0000 | | | | | |
|* 5 | TABLE ACCESS INMEMORY FULL | TIMES | 8765 | 136K| | 20 (10)| 00:00:01 |
| 6 | LOAD AS SELECT | SYS_TEMP_0FD9D661A_1E871F | | | | | |
| 7 | VECTOR GROUP BY | | 620 | 16120 | 2600K| 1068 (3)| 00:00:01 |
| 8 | KEY VECTOR CREATE BUFFERED | :KV0001 | | | | | |
|* 9 | TABLE ACCESS INMEMORY FULL | CUSTOMERS | 73153 | 1857K| | 523 (5)| 00:00:01 |
| 10 | HASH GROUP BY | | 8769 | 693K| 808K| 2217 (24)| 00:00:01 |
|* 11 | HASH JOIN | | 8769 | 693K| | 2048 (26)| 00:00:01 |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D661A_1E871F | 620 | 16120 | | 3 (0)| 00:00:01 |
|* 13 | HASH JOIN | | 8769 | 470K| | 2045 (26)| 00:00:01 |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6619_1E871F | 20 | 320 | | 2 (0)| 00:00:01 |
| 15 | VIEW | VW_VT_BCA3D1B3 | 8769 | 333K| | 2043 (26)| 00:00:01 |
| 16 | VECTOR GROUP BY | | 8769 | 291K| | 2043 (26)| 00:00:01 |
| 17 | HASH GROUP BY | | 8769 | 291K| | 2043 (26)| 00:00:01 |
| 18 | KEY VECTOR USE | :KV0000 | | | | | |
| 19 | KEY VECTOR USE | :KV0001 | | | | | |
|* 20 | HASH JOIN | | 23M| 762M| | 2017 (25)| 00:00:01 |
| 21 | JOIN FILTER CREATE | :BF0000 | 32 | 416 | | 1 (0)| 00:00:01 |
| 22 | SORT UNIQUE | | 32 | 416 | | 1 (0)| 00:00:01 |
|* 23 | TABLE ACCESS INMEMORY FULL| CHANNELS | 32 | 416 | | 1 (0)| 00:00:01 |
| 24 | JOIN FILTER USE | :BF0000 | 29M| 588M| | 1941 (22)| 00:00:01 |

|* 25 | TABLE ACCESS INMEMORY FULL | SALES | 29M| 588M| | 1941 (22)| 00:00:01 |

Predicate Information (identified by operation id):

5 - inmemory("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
"T"."CALENDAR_QUARTER_DESC"='2000-02')
filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
"T"."CALENDAR_QUARTER_DESC"='2000-02')
9 - inmemory("C"."CUST_STATE_PROVINCE"='FL')
filter("C"."CUST_STATE_PROVINCE"='FL')
11 - access("ITEM_10"=INTERNAL_FUNCTION("C0") AND "ITEM_11"="C2")
13 - access("ITEM_8"=INTERNAL_FUNCTION("C0") AND "ITEM_9"="C2")
20 - access("S"."CHANNEL_ID"="CHANNEL_ID")
23 - inmemory("CHANNEL_DESC"='Direct Sales')
filter("CHANNEL_DESC"='Direct Sales')
25 - inmemory(SYS_OP_BLOOM_FILTER(:BF0000,"S"."CHANNEL_ID"))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."CHANNEL_ID"))

Note

- dynamic statistics used: dynamic sampling (level=2)
- 3 Sql Plan Directives used for this statement
- vector transformation used for this statement

Statistics

1520 recursive calls
26 db block gets
149595 consistent gets
48327 physical reads
878724 redo size
1584 bytes sent via SQLNet to client
563 bytes received via SQL
Net from client
3 SQL*Net roundtrips to/from client
61 sorts (memory)
0 sorts (disk)
24 rows processed

SQL>

经过简单测试,只是简单开启表的INMEMORY属性,SQL查询效率竟然比没开启的还要慢。
NON-INMEMORY: 00:00:49.24
INMEMORY:00:01:21.76
慢了近50%,设置不合理,暂时也不是很清楚IN-MEMORY特性如何高效提升查询性能。这只能算作初步的了解。
0

fangtr

赞同来自:

SQL> alter system flush buffer_cache;
alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.10
SQL>
System altered.

Elapsed: 00:00:00.02
SQL>
SQL>
SQL> SELECT /* parallel(12) leading(channel_desc,s) / / SHOUG ORCL-CON */ c.cust_city,
2 t.calendar_quarter_desc,
3 SUM(s.amount_sold) sales_amount
4 FROM SHOUG.sales s, SHOUG.times t, SHOUG.customers c
5 WHERE s.time_id = t.time_id
6 AND s.cust_id = c.cust_id
7 AND c.cust_state_province = 'FL'
8 AND t.calendar_quarter_desc IN ('2000-01', '2000-02', '1999-12')
9 AND s.time_id IN
10 (SELECT time_id
11 FROM SHOUG.times
12 WHERE calendar_quarter_desc IN ('2000-01', '2000-02', '1999-12'))
13 AND s.cust_id IN
14 (SELECT cust_id FROM SHOUG.customers WHERE cust_state_province = 'FL')
15 AND s.channel_id IN
16 (SELECT channel_id
17 FROM SHOUG.channels
18 WHERE channel_desc = 'Direct Sales')
19 GROUP BY c.cust_city, t.calendar_quarter_desc;

24 rows selected.

Elapsed: 00:00:02.81

Execution Plan

Plan hash value: 2613104691

--------------------------------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |

| 0 | SELECT STATEMENT | | 1316 | 114K| 250 (24)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10004 | 1316 | 114K| 250 (24)| 00:00:01 | Q1,04 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 1316 | 114K| 250 (24)| 00:00:01 | Q1,04 | PCWP | |
| 4 | PX RECEIVE | | 1316 | 114K| 250 (24)| 00:00:01 | Q1,04 | PCWP | |
| 5 | PX SEND HASH | :TQ10003 | 1316 | 114K| 250 (24)| 00:00:01 | Q1,03 | P->P | HASH |
| 6 | HASH GROUP BY | | 1316 | 114K| 250 (24)| 00:00:01 | Q1,03 | PCWP | |
|* 7 | HASH JOIN | | 4067K| 345M| 241 (21)| 00:00:01 | Q1,03 | PCWP | |
| 8 | PX RECEIVE | | 12248 | 310K| 48 (5)| 00:00:01 | Q1,03 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10001 | 12248 | 310K| 48 (5)| 00:00:01 | Q1,01 | P->P | BROADCAST |
| 10 | PX BLOCK ITERATOR | | 12248 | 310K| 48 (5)| 00:00:01 | Q1,01 | PCWC | |
|* 11 | TABLE ACCESS INMEMORY FULL | CUSTOMERS | 12248 | 310K| 48 (5)| 00:00:01 | Q1,01 | PCWP | |
|* 12 | HASH JOIN RIGHT SEMI | | 3665K| 220M| 192 (25)| 00:00:01 | Q1,03 | PCWP | |
|* 13 | TABLE ACCESS INMEMORY FULL | CHANNELS | 32 | 416 | 2 (0)| 00:00:01 | Q1,03 | PCWP | |
|* 14 | HASH JOIN | | 3665K| 174M| 189 (25)| 00:00:01 | Q1,03 | PCWP | |
| 15 | PX RECEIVE | | 182 | 5278 | 3 (34)| 00:00:01 | Q1,03 | PCWP | |
| 16 | PX SEND BROADCAST | :TQ10002 | 182 | 5278 | 3 (34)| 00:00:01 | Q1,02 | P->P | BROADCAST |
| 17 | VIEW | VW_GBF_3 | 182 | 5278 | 3 (34)| 00:00:01 | Q1,02 | PCWP | |
| 18 | HASH GROUP BY | | 182 | 2912 | 3 (34)| 00:00:01 | Q1,02 | PCWP | |
| 19 | PX RECEIVE | | 182 | 2912 | 3 (34)| 00:00:01 | Q1,02 | PCWP | |
| 20 | PX SEND HASH | :TQ10000 | 182 | 2912 | 3 (34)| 00:00:01 | Q1,00 | P->P | HASH |
| 21 | HASH GROUP BY | | 182 | 2912 | 3 (34)| 00:00:01 | Q1,00 | PCWP | |
| 22 | PX BLOCK ITERATOR | | 4627 | 74032 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 23 | TABLE ACCESS INMEMORY FULL| TIMES | 4627 | 74032 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 24 | PX BLOCK ITERATOR | | 29M| 588M| 180 (23)| 00:00:01 | Q1,03 | PCWC | |

| 25 | TABLE ACCESS INMEMORY FULL | SALES | 29M| 588M| 180 (23)| 00:00:01 | Q1,03 | PCWP | |

Predicate Information (identified by operation id):

7 - access("S"."CUST_ID"="C"."CUST_ID")
11 - inmemory("C"."CUST_STATE_PROVINCE"='FL')
filter("C"."CUST_STATE_PROVINCE"='FL')
12 - access("S"."CHANNEL_ID"="CHANNEL_ID")
13 - inmemory("CHANNEL_DESC"='Direct Sales')
filter("CHANNEL_DESC"='Direct Sales')
14 - access("S"."TIME_ID"="ITEM_1")
23 - inmemory("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
"T"."CALENDAR_QUARTER_DESC"='2000-02')
filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
"T"."CALENDAR_QUARTER_DESC"='2000-02')

Note

- dynamic statistics used: dynamic sampling (level=AUTO)
- Degree of Parallelism is 12 because of hint

Statistics

2039 recursive calls
0 db block gets
3088 consistent gets
138 physical reads
0 redo size
1590 bytes sent via SQLNet to client
563 bytes received via SQL
Net from client
3 SQL*Net roundtrips to/from client
77 sorts (memory)
0 sorts (disk)
24 rows processed

SQL>
0

zhanglei - 苦逼的IT男

赞同来自:

使用in-memory 优化
set linesize 190
col segment_name format a40
SELECT segment_name, BYTES FROM USER_SEGMENTS;
select * from v$inmemory_area;

no tuning
172706 consistent gets
Elapsed: 00:00:39.12

alter table SHOUG.times inmemory;
alter table SHOUG.sales inmemory;
alter table shoug.customers inmemory;
alter table SHOUG.channels inmemory;

91 recursive calls
17 db block gets
56 consistent gets
2 physical reads
1172 redo size
5 sorts (memory)

no tuning
286 consistent gets
Elapsed: 00:00:37.12

结论. in-memory 提高了一些速度, 不过, 不明显
匿名用户
0

匿名用户

赞同来自:

算是了解一下12c inmemory特性,不是很懂。(由3分钟——>2分钟——>55s)
13:23:06 SHOUG # pdbkitten >SELECT /*+ SHOUG ORCL-CON */ c.cust_city,
13:23:22 2 t.calendar_quarter_desc,
13:23:22 3 SUM(s.amount_sold) sales_amount
13:23:22 4 FROM SHOUG.sales s, SHOUG.times t, SHOUG.customers c
13:23:22 5 WHERE s.time_id = t.time_id
13:23:22 6 AND s.cust_id = c.cust_id
13:23:22 7 AND c.cust_state_province = 'FL'
13:23:22 8 AND t.calendar_quarter_desc IN ('2000-01', '2000-02', '1999-12')
13:23:22 9 AND s.time_id IN
13:23:22 10 (SELECT time_id
13:23:22 11 FROM SHOUG.times
13:23:22 12 WHERE calendar_quarter_desc IN ('2000-01', '2000-02', '1999-12'))
13:23:22 13 AND s.cust_id IN
13:23:22 14 (SELECT cust_id FROM SHOUG.customers WHERE cust_state_province = 'FL')
13:23:22 15 AND s.channel_id IN
13:23:22 16 (SELECT channel_id
13:23:22 17 FROM SHOUG.channels
13:23:22 18 WHERE channel_desc = 'Direct Sales')
13:23:22 19 GROUP BY c.cust_city, t.calendar_quarter_desc;

CUST_CITY CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Sanibel 2000-02 586831299
Ocala 2000-02 232054129
Candler 2000-01 136521974
Cypress Gardens 2000-01 116189430
Palmdale 2000-02 846372536
Molino 2000-01 614899712
Ocala 2000-01 234184049
Palmdale 2000-01 1238415442
Molino 2000-02 561797530
Saint Marks 2000-01 1827843932
Evinston 2000-02 2053151457
Noma 2000-01 1100105318
Winter Springs 2000-02 655360
Candler 2000-02 212793426
Saint Marks 2000-02 1601001882
Sugarloaf Key 2000-01 394122363
Sugarloaf Key 2000-02 316520530
Blountstown 2000-02 1246906941
Cypress Gardens 2000-02 161511178
Evinston 2000-01 1753405522
Noma 2000-02 783272509
Sanibel 2000-01 520039301
Winter Springs 2000-01 1030881.28
Blountstown 2000-01 885545370

24 rows selected.

Elapsed: 00:03:10.80

Execution Plan

Plan hash value: 505934089

-------------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 8769 | 693K| | 54600 (1)| 00:00:03 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6630_18B604 | | | | | |
| 3 | VECTOR GROUP BY | | 20 | 320 | | 482 (1)| 00:00:01 |
| 4 | KEY VECTOR CREATE BUFFERED| :KV0000 | | | | | |
|* 5 | TABLE ACCESS FULL | TIMES | 8765 | 136K| | 481 (1)| 00:00:01 |
| 6 | LOAD AS SELECT | SYS_TEMP_0FD9D6631_18B604 | | | | | |
| 7 | VECTOR GROUP BY | | 620 | 16120 | 2648K| 13823 (1)| 00:00:01 |
| 8 | KEY VECTOR CREATE BUFFERED| :KV0001 | | | | | |
|* 9 | TABLE ACCESS FULL | CUSTOMERS | 74498 | 1891K| | 13270 (1)| 00:00:01 |
| 10 | HASH GROUP BY | | 8769 | 693K| 808K| 40295 (1)| 00:00:02 |
|* 11 | HASH JOIN | | 8769 | 693K| | 40126 (1)| 00:00:02 |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6631_18B604 | 620 | 16120 | | 3 (0)| 00:00:01 |
|* 13 | HASH JOIN | | 8769 | 470K| | 40123 (1)| 00:00:02 |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6630_18B604 | 20 | 320 | | 2 (0)| 00:00:01 |
| 15 | VIEW | VW_VT_BCA3D1B3 | 8769 | 333K| | 40121 (1)| 00:00:02 |
| 16 | VECTOR GROUP BY | | 8769 | 291K| | 40121 (1)| 00:00:02 |
| 17 | HASH GROUP BY | | 8769 | 291K| | 40121 (1)| 00:00:02 |
| 18 | KEY VECTOR USE | :KV0000 | | | | | |
| 19 | KEY VECTOR USE | :KV0001 | | | | | |
|* 20 | HASH JOIN | | 23M| 762M| | 40095 (1)| 00:00:02 |
| 21 | SORT UNIQUE | | 32 | 416 | | 4 (0)| 00:00:01 |
|* 22 | TABLE ACCESS FULL | CHANNELS | 32 | 416 | | 4 (0)| 00:00:01 |

| 23 | TABLE ACCESS FULL | SALES | 29M| 588M| | 40015 (1)| 00:00:02 |

Predicate Information (identified by operation id):

5 - filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
"T"."CALENDAR_QUARTER_DESC"='2000-02')
9 - filter("C"."CUST_STATE_PROVINCE"='FL')
11 - access("ITEM_10"=INTERNAL_FUNCTION("C0") AND "ITEM_11"="C2")
13 - access("ITEM_8"=INTERNAL_FUNCTION("C0") AND "ITEM_9"="C2")
20 - access("S"."CHANNEL_ID"="CHANNEL_ID")
22 - filter("CHANNEL_DESC"='Direct Sales')

Note

- dynamic statistics used: dynamic sampling (level=2)
- 2 Sql Plan Directives used for this statement
- vector transformation used for this statement

Statistics

4209 recursive calls
113 db block gets
212028 consistent gets
203764 physical reads
15460 redo size
1584 bytes sent via SQLNet to client
562 bytes received via SQL
Net from client
3 SQL*Net roundtrips to/from client
334 sorts (memory)
0 sorts (disk)
24 rows processed

13:30:59 SHOUG # pdbkitten >alter table CHANNELS INMEMORY;

Table altered.

Elapsed: 00:00:00.31
13:33:55 SHOUG # pdbkitten >alter table sales INMEMORY;

Table altered.

Elapsed: 00:00:00.19
13:34:05 SHOUG # pdbkitten >alter table times INMEMORY;

Table altered.

Elapsed: 00:00:00.03
13:36:30 SHOUG # pdbkitten >alter table customers inmemory;

Table altered.

Elapsed: 00:00:00.55
13:36:57 SHOUG # pdbkitten >
13:36:57 SHOUG # pdbkitten >
13:36:58 SHOUG # pdbkitten >SELECT /*+ SHOUG ORCL-CON */ c.cust_city,
13:37:08 2 t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount
13:37:08 4 FROM SHOUG.sales s, SHOUG.times t, SHOUG.customers c
13:37:08 5 WHERE s.time_id = t.time_id
13:37:08 6 AND s.cust_id = c.cust_id
13:37:08 7 AND c.cust_state_province = 'FL'
13:37:08 8 AND t.calendar_quarter_desc IN ('2000-01', '2000-02', '1999-12')
13:37:08 9 AND s.time_id IN
13:37:08 10 (SELECT time_id
13:37:08 11 FROM SHOUG.times
13:37:08 12 WHERE calendar_quarter_desc IN ('2000-01', '2000-02', '1999-12'))
13:37:08 13 AND s.cust_id IN
13:37:08 14 (SELECT cust_id FROM SHOUG.customers WHERE cust_state_province = 'FL')
13:37:08 15 AND s.channel_id IN
13:37:08 16 (SELECT channel_id
13:37:08 17 FROM SHOUG.channels
13:37:08 18 WHERE channel_desc = 'Direct Sales')
13:37:08 19 GROUP BY c.cust_city, t.calendar_quarter_desc;

CUST_CITY CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Sanibel 2000-02 586831299
Ocala 2000-02 232054129
Candler 2000-01 136521974
Cypress Gardens 2000-01 116189430
Palmdale 2000-02 846372536
Molino 2000-01 614899712
Ocala 2000-01 234184049
Palmdale 2000-01 1238415442
Molino 2000-02 561797530
Saint Marks 2000-01 1827843932
Evinston 2000-02 2053151457
Noma 2000-01 1100105318
Winter Springs 2000-02 655360
Candler 2000-02 212793426
Saint Marks 2000-02 1601001882
Sugarloaf Key 2000-01 394122363
Sugarloaf Key 2000-02 316520530
Blountstown 2000-02 1246906941
Cypress Gardens 2000-02 161511178
Evinston 2000-01 1753405522
Noma 2000-02 783272509
Sanibel 2000-01 520039301
Winter Springs 2000-01 1030881.28
Blountstown 2000-01 885545370

24 rows selected.

Elapsed: 00:02:45.81

Execution Plan

Plan hash value: 739059432

----------------------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 8769 | 693K| | 3318 (17)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6644_18B604 | | | | | |
| 3 | VECTOR GROUP BY | | 20 | 560 | | 24 (13)| 00:00:01 |
| 4 | KEY VECTOR CREATE BUFFERED | :KV0000 | | | | | |
|* 5 | TABLE ACCESS INMEMORY FULL | TIMES | 7264 | 198K| | 23 (9)| 00:00:01 |
| 6 | LOAD AS SELECT | SYS_TEMP_0FD9D6645_18B604 | | | | | |
| 7 | VECTOR GROUP BY | | 620 | 16120 | 2648K| 1076 (3)| 00:00:01 |
| 8 | KEY VECTOR CREATE BUFFERED | :KV0001 | | | | | |
|* 9 | TABLE ACCESS INMEMORY FULL | CUSTOMERS | 74498 | 1891K| | 523 (5)| 00:00:01 |
| 10 | HASH GROUP BY | | 8769 | 693K| 808K| 2218 (24)| 00:00:01 |
|* 11 | HASH JOIN | | 8769 | 693K| | 2049 (26)| 00:00:01 |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6645_18B604 | 620 | 16120 | | 3 (0)| 00:00:01 |
|* 13 | HASH JOIN | | 8769 | 470K| | 2046 (26)| 00:00:01 |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6644_18B604 | 20 | 320 | | 2 (0)| 00:00:01 |
| 15 | VIEW | VW_VT_BCA3D1B3 | 8769 | 333K| | 2044 (26)| 00:00:01 |
| 16 | VECTOR GROUP BY | | 8769 | 291K| | 2044 (26)| 00:00:01 |
| 17 | HASH GROUP BY | | 8769 | 291K| | 2044 (26)| 00:00:01 |
| 18 | KEY VECTOR USE | :KV0000 | | | | | |
| 19 | KEY VECTOR USE | :KV0001 | | | | | |
|* 20 | HASH JOIN | | 23M| 762M| | 2018 (25)| 00:00:01 |
| 21 | JOIN FILTER CREATE | :BF0000 | 32 | 416 | | 1 (0)| 00:00:01 |
| 22 | SORT UNIQUE | | 32 | 416 | | 1 (0)| 00:00:01 |
|* 23 | TABLE ACCESS INMEMORY FULL| CHANNELS | 32 | 416 | | 1 (0)| 00:00:01 |
| 24 | JOIN FILTER USE | :BF0000 | 29M| 588M| | 1942 (22)| 00:00:01 |

|* 25 | TABLE ACCESS INMEMORY FULL | SALES | 29M| 588M| | 1942 (22)| 00:00:01 |

Predicate Information (identified by operation id):

5 - inmemory("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
"T"."CALENDAR_QUARTER_DESC"='2000-02')
filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
"T"."CALENDAR_QUARTER_DESC"='2000-02')
9 - inmemory("C"."CUST_STATE_PROVINCE"='FL')
filter("C"."CUST_STATE_PROVINCE"='FL')
11 - access("ITEM_10"=INTERNAL_FUNCTION("C0") AND "ITEM_11"="C2")
13 - access("ITEM_8"=INTERNAL_FUNCTION("C0") AND "ITEM_9"="C2")
20 - access("S"."CHANNEL_ID"="CHANNEL_ID")
23 - inmemory("CHANNEL_DESC"='Direct Sales')
filter("CHANNEL_DESC"='Direct Sales')
25 - inmemory(SYS_OP_BLOOM_FILTER(:BF0000,"S"."CHANNEL_ID"))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."CHANNEL_ID"))

Note

- dynamic statistics used: dynamic sampling (level=2)
- 3 Sql Plan Directives used for this statement
- vector transformation used for this statement

Statistics

2688 recursive calls
28 db block gets
61614 consistent gets
55956 physical reads
2760 redo size
1584 bytes sent via SQLNet to client
562 bytes received via SQL
Net from client
3 SQL*Net roundtrips to/from client
146 sorts (memory)
0 sorts (disk)
24 rows processed

13:39:57 SHOUG # pdbkitten >
13:48:45 SHOUG # pdbkitten >
13:48:45 SHOUG # pdbkitten >alter table channels inmemory(CHANNEL_DESC);

Table altered.

Elapsed: 00:00:00.22
13:52:33 SHOUG # pdbkitten >
13:52:34 SHOUG # pdbkitten >alter table sales inmemory(CHANNEL_ID);

Table altered.

Elapsed: 00:00:00.05
13:55:53 SHOUG # pdbkitten >alter table sales inmemory(CHANNEL_ID,CUST_ID,TIME_ID);

Table altered.

Elapsed: 00:00:00.07
13:57:54 SHOUG # pdbkitten >alter table customers inmemory(CUST_STATE_PROVINCE,CUST_ID);

Table altered.

Elapsed: 00:00:00.02
13:58:07 SHOUG # pdbkitten >alter table times inmemory(time_id);

Table altered.

Elapsed: 00:00:00.08
14:00:16 SHOUG # pdbkitten >alter table customers inmemory(CUST_STATE_PROVINCE);

Table altered.

Elapsed: 00:00:00.30
14:00:42 SHOUG # pdbkitten >SELECT /*+ SHOUG ORCL-CON */
14:00:50 2 C.CUST_CITY, T.CALENDAR_QUARTER_DESC, SUM(S.AMOUNT_SOLD) SALES_AMOUNT
14:00:50 3 FROM SHOUG.SALES S, SHOUG.TIMES T, SHOUG.CUSTOMERS C
14:00:50 4 WHERE S.TIME_ID = T.TIME_ID
14:00:50 5 AND S.CUST_ID = C.CUST_ID
14:00:50 6 AND C.CUST_STATE_PROVINCE = 'FL'
14:00:50 7 AND T.CALENDAR_QUARTER_DESC IN ('2000-01', '2000-02', '1999-12')
14:00:50 8 AND S.TIME_ID IN
14:00:50 9 (SELECT TIME_ID
14:00:50 10 FROM SHOUG.TIMES
14:00:50 11 WHERE CALENDAR_QUARTER_DESC IN ('2000-01', '2000-02', '1999-12'))
14:00:50 12 AND S.CUST_ID IN
14:00:50 13 (SELECT CUST_ID FROM SHOUG.CUSTOMERS
14:00:50 14 WHERE CUST_STATE_PROVINCE = 'FL')
14:00:50 15 AND S.CHANNEL_ID IN
14:00:50 16 (SELECT CHANNEL_ID
14:00:50 17 FROM SHOUG.CHANNELS
14:00:50 18 WHERE CHANNEL_DESC = 'Direct Sales')
14:00:50 19 GROUP BY C.CUST_CITY, T.CALENDAR_QUARTER_DESC;

CUST_CITY CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Sanibel 2000-02 586831299
Ocala 2000-02 232054129
Candler 2000-01 136521974
Cypress Gardens 2000-01 116189430
Palmdale 2000-02 846372536
Molino 2000-01 614899712
Ocala 2000-01 234184049
Palmdale 2000-01 1238415442
Molino 2000-02 561797530
Saint Marks 2000-01 1827843932
Evinston 2000-02 2053151457
Noma 2000-01 1100105318
Winter Springs 2000-02 655360
Candler 2000-02 212793426
Saint Marks 2000-02 1601001882
Sugarloaf Key 2000-01 394122363
Sugarloaf Key 2000-02 316520530
Blountstown 2000-02 1246906941
Cypress Gardens 2000-02 161511178
Evinston 2000-01 1753405522
Noma 2000-02 783272509
Sanibel 2000-01 520039301
Winter Springs 2000-01 1030881.28
Blountstown 2000-01 885545370

24 rows selected.

Elapsed: 00:00:55.72

Execution Plan

Plan hash value: 739059432

----------------------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 8769 | 693K| | 3318 (17)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6684_18B604 | | | | | |
| 3 | VECTOR GROUP BY | | 20 | 560 | | 24 (13)| 00:00:01 |
| 4 | KEY VECTOR CREATE BUFFERED | :KV0000 | | | | | |
|* 5 | TABLE ACCESS INMEMORY FULL | TIMES | 7264 | 198K| | 23 (9)| 00:00:01 |
| 6 | LOAD AS SELECT | SYS_TEMP_0FD9D6685_18B604 | | | | | |
| 7 | VECTOR GROUP BY | | 620 | 16120 | 2648K| 1076 (3)| 00:00:01 |
| 8 | KEY VECTOR CREATE BUFFERED | :KV0001 | | | | | |
|* 9 | TABLE ACCESS INMEMORY FULL | CUSTOMERS | 74498 | 1891K| | 523 (5)| 00:00:01 |
| 10 | HASH GROUP BY | | 8769 | 693K| 808K| 2218 (24)| 00:00:01 |
|* 11 | HASH JOIN | | 8769 | 693K| | 2049 (26)| 00:00:01 |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6685_18B604 | 620 | 16120 | | 3 (0)| 00:00:01 |
|* 13 | HASH JOIN | | 8769 | 470K| | 2046 (26)| 00:00:01 |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6684_18B604 | 20 | 320 | | 2 (0)| 00:00:01 |
| 15 | VIEW | VW_VT_BCA3D1B3 | 8769 | 333K| | 2044 (26)| 00:00:01 |
| 16 | VECTOR GROUP BY | | 8769 | 291K| | 2044 (26)| 00:00:01 |
| 17 | HASH GROUP BY | | 8769 | 291K| | 2044 (26)| 00:00:01 |
| 18 | KEY VECTOR USE | :KV0000 | | | | | |
| 19 | KEY VECTOR USE | :KV0001 | | | | | |
|* 20 | HASH JOIN | | 23M| 762M| | 2018 (25)| 00:00:01 |
| 21 | JOIN FILTER CREATE | :BF0000 | 32 | 416 | | 1 (0)| 00:00:01 |
| 22 | SORT UNIQUE | | 32 | 416 | | 1 (0)| 00:00:01 |
|* 23 | TABLE ACCESS INMEMORY FULL| CHANNELS | 32 | 416 | | 1 (0)| 00:00:01 |
| 24 | JOIN FILTER USE | :BF0000 | 29M| 588M| | 1942 (22)| 00:00:01 |

|* 25 | TABLE ACCESS INMEMORY FULL | SALES | 29M| 588M| | 1942 (22)| 00:00:01 |

Predicate Information (identified by operation id):

5 - inmemory("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
"T"."CALENDAR_QUARTER_DESC"='2000-02')
filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
"T"."CALENDAR_QUARTER_DESC"='2000-02')
9 - inmemory("C"."CUST_STATE_PROVINCE"='FL')
filter("C"."CUST_STATE_PROVINCE"='FL')
11 - access("ITEM_10"=INTERNAL_FUNCTION("C0") AND "ITEM_11"="C2")
13 - access("ITEM_8"=INTERNAL_FUNCTION("C0") AND "ITEM_9"="C2")
20 - access("S"."CHANNEL_ID"="CHANNEL_ID")
23 - inmemory("CHANNEL_DESC"='Direct Sales')
filter("CHANNEL_DESC"='Direct Sales')
25 - inmemory(SYS_OP_BLOOM_FILTER(:BF0000,"S"."CHANNEL_ID"))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."CHANNEL_ID"))

Note

- dynamic statistics used: dynamic sampling (level=2)
- 3 Sql Plan Directives used for this statement
- vector transformation used for this statement

Statistics

2654 recursive calls
20 db block gets
2815 consistent gets
46 physical reads
2008 redo size
1584 bytes sent via SQLNet to client
562 bytes received via SQL
Net from client
3 SQL*Net roundtrips to/from client
154 sorts (memory)
0 sorts (disk)
24 rows processed

要回复问题请先登录注册

退出全屏模式 全屏模式 回复