|
做新特性书后习题(SG3)P273,找到Support Workbench包:
/u01/app/oracle/acfsmounts/acfs_db1/station36.example.com_orcl/sysman/emd/state/ORA7445qc_20180729095554_COM_1.zip
ADRCI打包:
- [oracle@station36 state]$ adrci
- ADRCI: Release 11.2.0.1.0 - Production on Sun Jul 29 10:19:53 2018
- Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
- ADR base = "/u01/app/oracle"
- adrci> help
- HELP [topic]
- Available Topics:
- CREATE REPORT
- ECHO
- EXIT
- HELP
- HOST
- IPS
- PURGE
- RUN
- SET BASE
- SET BROWSER
- SET CONTROL
- SET ECHO
- SET EDITOR
- SET HOMES | HOME | HOMEPATH
- SET TERMOUT
- SHOW ALERT
- SHOW BASE
- SHOW CONTROL
- SHOW HM_RUN
- SHOW HOMES | HOME | HOMEPATH
- SHOW INCDIR
- SHOW INCIDENT
- SHOW PROBLEM
- SHOW REPORT
- SHOW TRACEFILE
- SPOOL
- There are other commands intended to be used directly by Oracle, type
- "HELP EXTENDED" to see the list
- adrci> help SET HOMES
- Usage: SET HOMES | HOME| HOMEPATH <homepath_str1 homepath_str2 ...>
- Purpose: Set the ADR homes to query in the current ADRCI session.
- Arguments:
- <homepath_str1 homepath_str2 ...>: The paths of the home,
- relative to the ADR base.
- Note:
- The "diag" directory name can be omitted from the homepath_str.
- If the specified path contains multiple homes, for instance, it is
- set to the database ID which is running RAC. All of the ADR homes
- corresponding to the instances of the database will be added to the
- current ADRCI home settings.
- Example:
- set homepath diag/rdbms/aime3/aime3 diag/rdbms/aime3/aime32
- set homepath rdbms/aime3
- adrci> set homepath diag/rdbms/orcl/orcl
- adrci> SHOW PROBLEM
- ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
- *************************************************************************
- PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME
- -------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
- 1 ORA 7445 [qcstda()+720] 17177 2018-07-29 09:47:11.634000 +08:00
- 1 rows fetched
- adrci> help ips
- HELP IPS [topic]
- Available Topics:
- ADD
- ADD FILE
- ADD NEW INCIDENTS
- CHECK REMOTE KEYS
- COPY IN FILE
- COPY OUT FILE
- CREATE PACKAGE
- DELETE PACKAGE
- FINALIZE PACKAGE
- GENERATE PACKAGE
- GET MANIFEST
- GET METADATA
- GET REMOTE KEYS
- PACK
- REMOVE
- REMOVE FILE
- SET CONFIGURATION
- SHOW CONFIGURATION
- SHOW FILES
- SHOW INCIDENTS
- SHOW PACKAGE
- UNPACK FILE
- UNPACK PACKAGE
- USE REMOTE KEYS
- adrci> help CREATE PACKAGE
- DIA-48433: Unknown help topic
- adrci> help ips CREATE PACKAGE
- Usage: IPS CREATE PACKAGE
- [INCIDENT <incid> | PROBLEM <prob_id> | PROBLEMKEY <prob_key> |
- SECONDS <seconds> | TIME <start_time> TO <end_time>]
- [CORRELATE BASIC | TYPICAL | ALL]
- Purpose: Create a package, and optionally select contents for the package.
- Arguments:
- <incid>: ID of incident to use for selecting package contents.
- <prob_id>: ID of problem to use for selecting package contents.
- <prob_key>: Problem key to use for selecting package contents.
- <seconds>: Number of seconds before now for selecting package contents.
- <start_time>: Start of time range to look for incidents in.
- <end_time>: End of time range to look for incidents in.
- Options:
- CORRELATE BASIC: The package will include the incident dumps, and the
- incident process trace files.
- Additional incidents can be included automatically,
- if they share relevant correlation keys.
- CORRELATE TYPICAL: The package will include the incident dumps, and all
- trace files that were modified in a time window around
- each incident.
- Additional incidents can be included automatically,
- if they share relevant correlation keys, or occurred
- in a time window around the main incidents.
- CORRELATE ALL: The package will include the incident dumps, and all
- trace files that were modified between the first
- selected incident and the last selected incident.
- Additional incidents can be included automatically,
- if they occurred in the same time range.
- Notes:
- If no package contents are specified (incident, problem, etc), an empty
- package will be created. Files and incidents can be added later.
- If no correlation level is specified, the default level is used.
- The default is normally TYPICAL, but it can be changed using the command
- IPS SET CONFIGURATION.
- Example:
- ips create package incident 861;
- ips create package time '2006-12-31 23:59:59.00 -07:00' to
- '2007-01-01 01:01:01.00 -07:00';
- adrci> IPS CREATE PACKAGE PROBLEM 1
- Created package 2 based on problem id 1, correlation level typical
- adrci> help ips GENERATE PACKAGE
- Usage: IPS GENERATE PACKAGE <package_id> [IN <path>]
- [COMPLETE | INCREMENTAL]
- Purpose: Create a physical package (zip file) in target directory.
- Arguments:
- <package_id>: ID of package to create physical package file for.
- <path>: Path where the physical package file should be generated.
- Options:
- COMPLETE: The package will include all package files, even if a
- previous package sequence has been generated.
- This is the default.
- INCREMENTAL: The package will only include files that have been added
- or changed since the last package generation.
- Notes:
- If no target path is specified, the physical package file is generated
- in the current working directory.
- Example:
- ips generate package 12 in /tmp
- adrci> ips generate package 2 in /home/oracle/
- cp: omitting directory `/u01/app/oracle/product/11.2.0/grid/log/station36/racg/racgmain'
- cp: omitting directory `/u01/app/oracle/product/11.2.0/grid/log/station36/racg/racgeut'
- cp: omitting directory `/u01/app/oracle/product/11.2.0/grid/log/station36/racg/racgevtf'
- Generated package 2 in file /home/oracle/ORA7445qc_20180729102305_COM_1.zip, mode complete
- adrci> help ips
- HELP IPS [topic]
- Available Topics:
- ADD
- ADD FILE
- ADD NEW INCIDENTS
- CHECK REMOTE KEYS
- COPY IN FILE
- COPY OUT FILE
- CREATE PACKAGE
- DELETE PACKAGE
- FINALIZE PACKAGE
- GENERATE PACKAGE
- GET MANIFEST
- GET METADATA
- GET REMOTE KEYS
- PACK
- REMOVE
- REMOVE FILE
- SET CONFIGURATION
- SHOW CONFIGURATION
- SHOW FILES
- SHOW INCIDENTS
- SHOW PACKAGE
- UNPACK FILE
- UNPACK PACKAGE
- USE REMOTE KEYS
- adrci> ips SHOW PACKAGE
- PACKAGE_ID 1
- PACKAGE_NAME ORA7445qc_20180729095554
- PACKAGE_DESCRIPTION
- DRIVING_PROBLEM 1
- DRIVING_PROBLEM_KEY ORA 7445 [qcstda()+720]
- DRIVING_INCIDENT 17177
- DRIVING_INCIDENT_TIME 2018-07-29 09:47:11.634000 +08:00
- STATUS Generated (4)
- CORRELATION_LEVEL Typical (2)
- PROBLEMS 1 main problems, 0 correlated problems
- INCIDENTS 1 main incidents, 0 correlated incidents
- INCLUDED_FILES 47
- PACKAGE_ID 2
- PACKAGE_NAME ORA7445qc_20180729102305
- PACKAGE_DESCRIPTION
- DRIVING_PROBLEM 1
- DRIVING_PROBLEM_KEY ORA 7445 [qcstda()+720]
- DRIVING_INCIDENT 17177
- DRIVING_INCIDENT_TIME 2018-07-29 09:47:11.634000 +08:00
- STATUS Generated (4)
- CORRELATION_LEVEL Typical (2)
- PROBLEMS 1 main problems, 0 correlated problems
- INCIDENTS 1 main incidents, 0 correlated incidents
- INCLUDED_FILES 47
- adrci>
复制代码
看体检报告:
能做的体检项目(默认安装装版本,并不是都可以):
- select * from v$hm_check;
复制代码 | ID | NAME | NAME_NLS | CLSID | CLS_NAME | FLAGS | INTERNAL_CHECK | OFFLINE_CAPABLE | DESCRIPTION | 1 | 1 | HM Test Check | HM Test Check | 1 | GENERIC | 35 | Y | Y | Check for health monitor functionality | 2 | 2 | DB Structure Integrity Check | DB Structure Integrity Check | 2 | PERSISTENT_DATA | 4098 | N | Y | Checks integrity of all database files | 3 | 25 | CF Block Integrity Check | CF Block Integrity Check | 2 | PERSISTENT_DATA | 4098 | N | Y | Checks integrity of a control file block | 4 | 3 | Data Block Integrity Check | Data Block Integrity Check | 2 | PERSISTENT_DATA | 4098 | N | Y | Checks integrity of a data file block | 5 | 4 | Redo Integrity Check | Redo Integrity Check | 2 | PERSISTENT_DATA | 4098 | N | Y | Checks integrity of redo log content | 6 | 5 | Logical Block Check | Logical Block Check | 2 | PERSISTENT_DATA | 4097 | Y | N | Checks logical content of a block | 7 | 10 | Transaction Integrity Check | Transaction Integrity Check | 2 | PERSISTENT_DATA | 4096 | N | N | Checks a transaction for corruptions | 8 | 11 | Undo Segment Integrity Check | Undo Segment Integrity Check | 2 | PERSISTENT_DATA | 4096 | N | N | Checks integrity of an undo segment | 9 | 12 | No Mount CF Check | No Mount CF Check | 2 | PERSISTENT_DATA | 19 | Y | Y | Checks control file in NOMOUNT mode | 10 | 31 | Mount CF Check | Mount CF Check | 2 | PERSISTENT_DATA | 19 | Y | Y | Checks control file in mount mode | 11 | 13 | CF Member Check | CF Member Check | 2 | PERSISTENT_DATA | 4115 | Y | Y | Checks a multiplexed copy of the control file | 12 | 14 | All Datafiles Check | All Datafiles Check | 2 | PERSISTENT_DATA | 4115 | Y | Y | Checks all datafiles in the database | 13 | 15 | Single Datafile Check | Single Datafile Check | 2 | PERSISTENT_DATA | 4115 | Y | Y | Checks a data file | 14 | 30 | Tablespace Check Check | Tablespace Check | 2 | PERSISTENT_DATA | 19 | Y | Y | Checks a tablespace | 15 | 16 | Log Group Check | Log Group Check | 2 | PERSISTENT_DATA | 4115 | Y | Y | Checks all members of a log group | 16 | 17 | Log Group Member Check | Log Group Member Check | 2 | PERSISTENT_DATA | 4115 | Y | Y | Checks a particular member of a log group | 17 | 18 | Archived Log Check | Archived Log Check | 2 | PERSISTENT_DATA | 4115 | Y | Y | Checks an archived log | 18 | 19 | Redo Revalidation Check | Redo Revalidation Check | 2 | PERSISTENT_DATA | 4115 | Y | Y | Checks redo log content | 19 | 20 | IO Revalidation Check | IO Revalidation Check | 2 | PERSISTENT_DATA | 4115 | Y | Y | Checks file accessibility | 20 | 21 | Block IO Revalidation Check | Block IO Revalidation Check | 2 | PERSISTENT_DATA | 4115 | Y | Y | Checks file accessibility | 21 | 22 | Txn Revalidation Check | Txn Revalidation Check | 2 | PERSISTENT_DATA | 4113 | Y | N | Revalidate corrupted transaction | 22 | 23 | Failure Simulation Check | Failure Simulation Check | 2 | PERSISTENT_DATA | 4131 | Y | Y | Creates dummy failures | 23 | 24 | Dictionary Integrity Check | Dictionary Integrity Check | 2 | PERSISTENT_DATA | 4096 | N | N | Checks dictionary integrity | 24 | 26 | ASM Mount Check | ASM Mount Check | 3 | ASM | 8195 | Y | Y | Diagnose mount failure | 25 | 27 | ASM Allocation Check | ASM Allocation Check | 3 | ASM | 8194 | N | Y | Diagnose allocation failure | 26 | 28 | ASM Disk Visibility Check | ASM Disk Visibility Check | 3 | ASM | 8195 | Y | Y | Diagnose add disk failure | 27 | 29 | ASM File Busy Check | ASM File Busy Check | 3 | ASM | 8195 | Y | Y | Diagnose file drop failure |
- select * from v$diag_info;
复制代码 | INST_ID | NAME | VALUE | 1 | 1 | Diag Enabled | TRUE | 2 | 1 | ADR Base | /u01/app/oracle | 3 | 1 | ADR Home | /u01/app/oracle/diag/rdbms/orcl/orcl | 4 | 1 | Diag Trace | /u01/app/oracle/diag/rdbms/orcl/orcl/trace | 5 | 1 | Diag Alert | /u01/app/oracle/diag/rdbms/orcl/orcl/alert | 6 | 1 | Diag Incident | /u01/app/oracle/diag/rdbms/orcl/orcl/incident | 7 | 1 | Diag Cdump | /u01/app/oracle/diag/rdbms/orcl/orcl/cdump | 8 | 1 | Health Monitor | /u01/app/oracle/diag/rdbms/orcl/orcl/hm | 9 | 1 | Default Trace File | /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_25214.trc | 10 | 1 | Active Problem Count | 0 | 11 | 1 | Active Incident Count | 0 |
请用以下命令看alter日志:
- adrci> show alert -p "message_text like '%7445%'"
- ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
- *************************************************************************
- Output the results to file: /tmp/alert_25468_3086_orcl_1.ado
复制代码
在Support Workbench中解决600:
->
->
->
->
实施补丁:
实施成功:
---------------------------压缩
索引的压缩
- SQL> select count(*) from tnewf05_a;
- COUNT(*)
- ----------
- 0
- SQL> begin
- 2 for i in 1..22
- 3 loop
- 4 insert into tnewf05_a values('A');
- 5 end loop;
- 6 end;
- 7 /
- PL/SQL procedure successfully completed.
- SQL> commit;
- Commit complete.
- SQL> select count(*) from tnewf05_a;
- COUNT(*)
- ----------
- 22
- SQL> create index inewf05_a on tnewf05_a(a) ;
- Index created.
- SQL> drop index inewf05_a;
- Index dropped.
- SQL> create index inewf05_a on tnewf05_a(a) compress;
- Index created.
- SQL>
复制代码- select * from dba_extents e
- where e.owner='HR' and e.segment_name='TNEWF05_A';
-
- select * from dba_extents e
- where e.owner='HR' and e.segment_name='INEWF05_A';
复制代码 --------建一下带不同默认压缩选项的表空间:
- create tablespace tbs_nocompression datafile size 10M autoextend on;
- create tablespace tbs_basic datafile size 10M autoextend on default compress basic;
- create tablespace tbs_oltp datafile size 10M autoextend on default compress for oltp;
- create tablespace tbs_query datafile size 10M autoextend on default compress for query;
- create tablespace tbs_archive datafile size 10M autoextend on default compress for archive;
复制代码- select t.tablespace_name, t.def_tab_compression, t.compress_for from dba_tablespaces t
- where t.tablespace_name in ('TBS_NOCOMPRESSION',
- 'TBS_BASIC', 'TBS_OLTP',
- 'TBS_QUERY',
- 'TBS_ARCHIVE');
复制代码 | TABLESPACE_NAME | DEF_TAB_COMPRESSION | COMPRESS_FOR | 1 | TBS_NOCOMPRESSION | DISABLED | | 2 | TBS_BASIC | ENABLED | BASIC | 3 | TBS_OLTP | ENABLED | OLTP | 4 | TBS_QUERY | ENABLED | QUERY HIGH | 5 | TBS_ARCHIVE | ENABLED | ARCHIVE LOW |
- --hr--
- create table t_nocompression (a varchar2(200)) tablespace tbs_nocompression;
- create table t_basic (a varchar2(200)) tablespace tbs_basic;
- create table t_oltp (a varchar2(200)) tablespace tbs_oltp;
复制代码- --hr-error--
- create table t_query (a varchar2(200)) tablespace tbs_query;
- create table t_archive (a varchar2(200)) tablespace tbs_archive;
复制代码
- --hr--
- begin
- for i in 1..400
- loop
- insert into t_nocompression values('AAAAAAAAAAAAAAAAAAAA');
- end loop;
- commit;
- end;
- --hr--
- begin
- for i in 1..400
- loop
- insert into t_basic values('AAAAAAAAAAAAAAAAAAAA');
- end loop;
- commit;
- end;
- --hr--
- begin
- for i in 1..400
- loop
- insert into t_oltp values('AAAAAAAAAAAAAAAAAAAA');
- end loop;
- commit;
- end;
复制代码- --hr--
- select t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
- from user_tables t where t.TABLE_NAME in ('T_NOCOMPRESSION','T_BASIC','T_OLTP');
复制代码 | TABLE_NAME | PCT_FREE | COMPRESSION | COMPRESS_FOR | TABLESPACE_NAME | 1 | T_BASIC | 0 | ENABLED | BASIC | TBS_BASIC | 2 | T_NOCOMPRESSION | 10 | DISABLED | | TBS_NOCOMPRESSION | 3 | T_OLTP | 10 | ENABLED | OLTP | TBS_OLTP |
- select count(*) , substr(rowid, 10, 6 ) from hr.t_nocompression group by substr(rowid, 10, 6 );
复制代码 | COUNT(*) | SUBSTR(ROWID,10,6) | 1 | 121 | AAAACF | 2 | 279 | AAAACE |
每个块里头能放多少行跟预估的差不多:(8192*0.9-256)/25
( 块大小*(1-pctfree)-块头)/(20个A+5字节的行头和列长)=284
- select count(*) , substr(rowid, 10, 6 ) from hr.t_basic group by substr(rowid, 10, 6 );
复制代码 之前的这种插入根本就 没有触发basic压缩,还是让你插入,只是同上t_nocompression的这种压缩:- begin
- for i in 1..400
- loop
- insert into t_basic values('AAAAAAAAAAAAAAAAAAAA');
- end loop;
- commit;
- end;
复制代码 | COUNT(*) | SUBSTR(ROWID,10,6) | 1 | 90 | AAAACF | 2 | 310 | AAAACE |
每个块里头能放多少行跟预估的差不多:(8192*-256)/25
( 块大小*1-块头)/(20个A+5字节的行头和列长)=317
- select count(*) , substr(rowid, 10, 6 ) from hr.t_oltp group by substr(rowid, 10, 6 );
复制代码 | COUNT(*) | SUBSTR(ROWID,10,6) | 1 | 400 | AAAACE |
---------------------------------------------------------
- select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
- from hr.T_NOCOMPRESSION group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
复制代码 | COUNT(*) | DBMS_COMPRESSION.GET_COMPRESSI | 1 | 400 | 1 |
- select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
- from hr.T_BASIC group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);
复制代码 | COUNT(*) | DBMS_COMPRESSION.GET_COMPRESSI | 1 | 400 | 1 |
- select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP',row_id => rowid)
- from hr.T_OLTP group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP',row_id => rowid);
复制代码 | COUNT(*) | DBMS_COMPRESSION.GET_COMPRESSI | 1 | 400 | 2 |
HR直接创建这些表:
- create table t_basic2 (a varchar2(200)) tablespace tbs_nocompression compress;
- create table t_oltp2 (a varchar2(200)) tablespace tbs_nocompression compress for oltp;
- --hr-error--
- create table t_query2 (a varchar2(200)) tablespace tbs_nocompression compress for query;
- create table t_archive2 (a varchar2(200)) tablespace tbs_nocompression compress for archive;
复制代码- select t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
- from user_tables t where t.TABLE_NAME in ('T_NOCOMPRESSION','T_BASIC','T_OLTP', 'T_BASIC2','T_OLTP2' );
复制代码 | TABLE_NAME | PCT_FREE | COMPRESSION | COMPRESS_FOR | TABLESPACE_NAME | 1 | T_BASIC | 0 | ENABLED | BASIC | TBS_BASIC | 2 | T_BASIC2 | 0 | ENABLED | BASIC | TBS_NOCOMPRESSION | 3 | T_NOCOMPRESSION | 10 | DISABLED | | TBS_NOCOMPRESSION | 4 | T_OLTP | 10 | ENABLED | OLTP | TBS_OLTP | 5 | T_OLTP2 | 10 | ENABLED | OLTP | TBS_NOCOMPRESSION |
- --hr
- insert /*+ append */ into t_basic2 select * from t_basic;
- commit;
复制代码- select count(*) , substr(rowid, 10, 6 ) from hr.t_basic2 group by substr(rowid, 10, 6 );
复制代码 | COUNT(*) | SUBSTR(ROWID,10,6) | 1 | 400 | AAAACL |
- select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC2',row_id => rowid)
- from hr.T_BASIC2 group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC2',row_id => rowid);
复制代码 | COUNT(*) | DBMS_COMPRESSION.GET_COMPRESSI | 1 | 400 | 2 |
改一个现成表为压缩表:
- --hr--
- alter table t_nocompression compress for oltp;
- select t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
- from user_tables t where t.TABLE_NAME='T_NOCOMPRESSION';
复制代码 | TABLE_NAME | PCT_FREE | COMPRESSION | COMPRESS_FOR | TABLESPACE_NAME | 1 | T_NOCOMPRESSION | 10 | ENABLED | OLTP | TBS_NOCOMPRESSION | 对旧的数据是完全没有动:
- select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
- from hr.T_NOCOMPRESSION group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
复制代码 | COUNT(*) | DBMS_COMPRESSION.GET_COMPRESSI | 1 | 400 | 1 |
对新的数据:
- --hr--
- begin
- for i in 1..400
- loop
- insert into t_nocompression values('AAAAAAAAAAAAAAAAAAAA');
- end loop;
- commit;
- end;
复制代码- select count(*) , substr(rowid, 10, 6 ) from hr.t_nocompression group by substr(rowid, 10, 6 );
复制代码 | COUNT(*) | SUBSTR(ROWID,10,6) | 1 | 279 | AAAACF | 2 | 279 | AAAACE | 3 | 242 | AAAACG |
-SYS:
- select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
- from hr.T_NOCOMPRESSION group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
复制代码 | COUNT(*) | DBMS_COMPRESSION.GET_COMPRESSI | 1 | 800 | 1 | alter 完之后必须挪动表空间:
- --hr--
- alter table t_nocompression move tablespace tbs_nocompression;
- select count(*) , substr(rowid, 10, 6 ) from hr.t_nocompression group by substr(rowid, 10, 6 );
复制代码 | COUNT(*) | SUBSTR(ROWID,10,6) | 1 | 149 | AAAACU | 2 | 651 | AAAACT |
- --sys--
- select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
- from hr.T_NOCOMPRESSION group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
复制代码 | COUNT(*) | DBMS_COMPRESSION.GET_COMPRESSI | 1 | 800 | 2 |
------------------------------BASIC压缩:
- --hr
- insert /*+ append */ into t_basic2 select * from t_basic;
- commit;
复制代码- select count(*) , substr(rowid, 10, 6 ) from hr.t_basic2 group by substr(rowid, 10, 6 );
复制代码 由于直接路径加载,在高水位线之后,所以还是两个块,还要挪动一下表空间:
| COUNT(*) | SUBSTR(ROWID,10,6) | 1 | 400 | AAAACM | 2 | 400 | AAAACL |
- --hr--
- alter table t_basic2 move tablespace tbs_nocompression;
复制代码- select count(*) , substr(rowid, 10, 6 ) from hr.t_basic2 group by substr(rowid, 10, 6 );
复制代码 | COUNT(*) | SUBSTR(ROWID,10,6) | 1 | 725 | AAAACD | 2 | 75 | AAAACE |
- --sys--
- select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
- from hr.T_basic2 group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
复制代码 | COUNT(*) | DBMS_COMPRESSION.GET_COMPRESSI | 1 | 800 | 2 |
------------------------BASIC压缩的另外触发条件:
- create table hr.t_basic_big compress as select * from dba_source;
复制代码- select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC_BIG',row_id => rowid)
- from hr.T_BASIC_BIG group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC_BIG',row_id => rowid);
复制代码
如下图所示,会有一小部分没有压缩:
| COUNT(*) | DBMS_COMPRESSION.GET_COMPRESSI | 1 | 4324 | 1 | 2 | 619084 | 2 |
- alter table t_basic_big nocompress;
复制代码- select t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
- from user_tables t where t.TABLE_NAME ='T_BASIC_BIG';
复制代码 | TABLE_NAME | PCT_FREE | COMPRESSION | COMPRESS_FOR | TABLESPACE_NAME | 1 | T_BASIC_BIG | 0
| DISABLED | | USERS |
- alter table t_basic_big pctfree 10;
- alter table t_basic_big move tablespace users;
复制代码- select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC_BIG',row_id => rowid)
- from hr.T_BASIC_BIG group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC_BIG',row_id => rowid);
复制代码 | COUNT(*) | DBMS_COMPRESSION.GET_COMPRESSI | 1 | 623408 | 1 |
- declare
- v_blkcnt_cmp number;
- v_blkcnt_uncmp number;
- v_row_cmp number;
- v_row_uncmp number;
- v_cmp_ratio number;
- v_comptype_str varchar2(200);
- BEGIN
- DBMS_COMPRESSION.GET_COMPRESSION_RATIO(scratchtbsname => 'USERS',
- ownname =>'HR',
- tabname =>'T_BASIC_BIG',
- partname =>null,
- comptype => 2,
- blkcnt_cmp => v_blkcnt_cmp,
- blkcnt_uncmp => v_blkcnt_uncmp,
- row_cmp =>v_row_cmp,
- row_uncmp => v_row_uncmp,
- cmp_ratio => v_cmp_ratio,
- comptype_str =>v_comptype_str);
- DBMS_OUTPUT.PUT_LINE('Blk count compressed = ' || v_blkcnt_cmp);
- DBMS_OUTPUT.PUT_LINE('Blk count uncompressed = ' || v_blkcnt_uncmp);
- DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || v_row_cmp);
- DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || v_row_uncmp);
- DBMS_OUTPUT.PUT_LINE('ratio: '||v_cmp_ratio);
- DBMS_OUTPUT.PUT_LINE('Compression type = ' || v_comptype_str);
- end;
复制代码
Blk count compressed = 1474
Blk count uncompressed = 2058
Row count per block compressed = 85
Row count per block uncompressed = 60
ratio: 1.39620081411126187245590230664857530529
Compression type = "Compress For OLTP"
BASIC压缩不能删除列:
- create table t_basic_col( a number , b varchar2(20)) compress ;
- insert into t_basic_col values ( 1,'A') ;
- commit;
- create table t_oltp_col( a number , b varchar2(20)) compress for oltp;
- insert into t_oltp_col values ( 1,'A') ;
- commit;
- select * from t_basic_col;
- select * from t_oltp_col;
- alter table t_basic_col drop column b;
复制代码
- alter table t_oltp_col drop column b;
- select * from t_oltp_col;
复制代码
SAA快速调优:
- SQL> conn / as sysdba
- Connected.
- SQL> exec dbms_stats.gather_table_stats('HR','T_BASIC_BIG');
- PL/SQL procedure successfully completed.
- SQL> begin
- 2 dbms_advisor.QUICK_TUNE(ADVISOR_NAME=>'SQL Access Advisor',
- 3 TASK_NAME=>'MYSAA1',
- 4 ATTR1=>'select sum(line) from hr.t_basic_big group by text',
- 5 TEMPLATE=>'SQLACCESS_WAREHOUSE');
- 6 end;
- 7 /
- PL/SQL procedure successfully completed.
复制代码
|
|