12 Mart 2018 Pazartesi

Exadata Lab 3: HCC Performance

TABLE Compress Performance

[oracle@qr01dbadm01 labs]$ sqlplus sh/sh
SQL> set timing on
SQL> alter session force parallel dml;
Session altered.

SQL> alter session force parallel ddl;
Session altered.

SQL> create table mycust_query_low
  2  column store compress for query low
  3  nologging parallel 4
  4  as select * from mycustomers;
Table created.
Elapsed: 00:02:17.89

SQL> create table mycust_query_high
  2  column store compress for query high
  3  nologging parallel 4
  4  as select * from mycustomers;
Table created.
Elapsed: 00:00:28.45

SQL> create table mycust_archive_low
  2  column store compress for archive low
  3  nologging parallel 4
  4  as select * from mycustomers;
Table created.
Elapsed: 00:00:43.35


SQL> create table mycust_archive_high
  2  column store compress for archive high
  3  nologging parallel 4
  4  as select * from mycustomers;
Table created.
Elapsed: 00:01:20.94

SQL> col segment_name format a30
SQL> select segment_name,MB,
  2  round((max(MB) over (partition by X))/MB,1) COMP_RATIO
  3  from (select segment_name,sum(bytes)/1024/1024 MB,1 X
  4  from user_segments
  5  where segment_name like 'MYCUST%'
  6  group by segment_name)
  7  order by 2 desc;

SEGMENT_NAME        MB COMP_RATIO
------------------------------ ---------- ----------
MYCUSTOMERS                   654       1
MYCUST_QUERY_LOW       260       2.5
MYCUST_QUERY_HIGH      161       4.1
MYCUST_ARCHIVE_LOW   149      4.4
MYCUST_ARCHIVE_HIGH  111       5.9


SQL>insert /*+APPEND*/ into mycustomers select * from seed_data;
SQL>commit;

SQL>insert /*+APPEND*/ into mycustomers select * from seed_data;
SQL>insert /*+APPEND*/  into MYCUST_QUERY_HIGH select * from seed_data;
SQL>commit;

SQL>insert /*+APPEND*/  into MYCUST_QUERY_HIGH select * from seed_data;
SQL>insert /*+APPEND*/  into MYCUST_ARCHIVE_HIGH select * from seed_data;
SQL>commit;

SQL>insert /*+APPEND*/  into MYCUST_ARCHIVE_HIGH select * from seed_data;
SQL> insert /*+APPEND*/ into mycustomers select * from seed_data;
500000 rows created.
Elapsed: 00:00:21.34

SQL> commit;
Commit complete.

SQL> insert /*+APPEND*/ into mycustomers select * from seed_data;
500000 rows created.
Elapsed: 00:00:09.05

SQL> insert /*+APPEND*/  into MYCUST_QUERY_HIGH select * from seed_data;
500000 rows created.
Elapsed: 00:00:04.42


SQL> commit;
Commit complete.


SQL> insert /*+APPEND*/  into MYCUST_QUERY_HIGH select * from seed_data;
500000 rows created.
Elapsed: 00:00:03.90

SQL> insert /*+APPEND*/  into MYCUST_ARCHIVE_HIGH select * from seed_data;
500000 rows created.
Elapsed: 00:00:07.08

SQL>commit;
Commit complete.

SQL>insert /*+APPEND*/  into MYCUST_ARCHIVE_HIGH select * from seed_data;
500000 rows created.
Elapsed: 00:00:06.57

Hiç yorum yok:

Yorum Gönder