课程第43/44/45次（2018-10-14星期日上下午，2018-10-15星期一）
查看OMS的安装信息：/u01/app/oracle/product/middleware/oms/install/portlist.iniSYSTEM作为resource manager管理员的授权：
BEGIN
dbms_resource_manager_privs.grant_system_privilege(privilege_name=>'ADMINISTER_RESOURCE_MANAGER', grantee_name=>'SYSTEM', admin_option=>FALSE);
END;CDB PLAN:
DECLARE
spfileValue VARCHAR2(1000);
execText VARCHAR2(1000);
scopeValue VARCHAR2(30) := 'MEMORY';
planName VARCHAR2(100) :='DAYTIMEP';
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_cdb_plan( plan => 'DAYTIMEP', comment => 'DAYTIMEP');
dbms_resource_manager.create_cdb_plan_directive(
plan => 'DAYTIMEP',
pluggable_database => 'PDBPROD1',
comment => '',
shares => 6,
utilization_limit => 75,
parallel_server_limit => 50 );
dbms_resource_manager.create_cdb_plan_directive(
plan => 'DAYTIMEP',
pluggable_database => 'PDBPROD2',
comment => '',
shares => 3,
utilization_limit => 75,
parallel_server_limit => 50 );
dbms_resource_manager.create_cdb_plan_directive(
plan => 'DAYTIMEP',
pluggable_database => 'PDBPROD3',
comment => '',
shares => 1,
utilization_limit => 75,
parallel_server_limit => 50 );
dbms_resource_manager.submit_pending_area();
select value into spfileValue from v$parameter where name = 'spfile';
IF spfileValue IS NOT NULL then
EXECUTE IMMEDIATE 'alter system set resource_manager_plan = '||planName||' scope=BOTH';
END IF;
dbms_resource_manager.switch_plan( plan_name => 'DAYTIMEP' , sid => 'PRODCDB' );
END;
RESULT CACHE:
在PDBPROD1 数据库中，设置result cache size 15m，使用hr 登录，执行脚本8_1_1.sql，让脚本中的语句都可以使用result cache
$ export ORACLE_SID=PRODCDB
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 14 16:52:44 2016
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, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options
SYS@PRODCDB> show pdbs
CON_ID CON_NAME OPEN MODERESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLYNO
3 PDBPROD1 READ WRITE NO
4 PDBPROD2 MOUNTED
5 PDBPROD3 READ WRITE NO
6 PDBPROD4 MOUNTED
7 PDBPROD5 MOUNTED
SYS@PRODCDB>
SYS@PRODCDB> show parameter result_cache_max_size
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
result_cache_max_size big integer 2M
SYS@PRODCDB>
SYS@PRODCDB> alter system set result_cache_max_size=15m;
System altered.
SYS@PRODCDB> show parameter result_cache_max_size
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
result_cache_max_size big integer 15M
SYS@PRODCDB>
SYS@PRODCDB> conn hr/hr@pdbprod1
Connected.
HR@pdbprod1> alter session set result_cache_mode=force;
Session altered.
HR@pdbprod1> @/home/oracle/scripts/8_1_1.sql
DEPARTMENT_ID SUM(SALARY)
------------- -----------
100 51608
30 24900
7000
20 19000
70 10000
90 58000
110 20308
50 156400
40 6500
80 304500
10 4400
60 28800
12 rows selected.
2 rows updated.
Commit complete.
DEPARTMENT_ID SUM(SALARY)
------------- -----------
100 51608
30 24900
7000
20 19020
登录PDBPROD1数据库的SH用户，运行脚本8_2_1.sql ，脚本中的查询，表customers的CUST_CITY,CUST_STATE_PROVINCE,COUNTRY_ID列经常一起使用。提高优化器对这些语句计算的可选择率。:
select dbms_stats.create_extended_stats('SH','CUSTOMERS','(CUST_CITY,CUST_STATE_PROVINCE,COUNTRY_ID)')
from dual;
select dbms_stats.create_extended_stats('SH','CUSTOMERS','(CUST_CITY,CUST_STATE_PROVINCE,COUNTRY_ID)')
from dual;
select* fromcdb_tab_col_statistics where owner='SH' and table_name='CUSTOMERS';
begin
dbms_stats.gather_table_stats('SH','CUSTOMERS',
estimate_percent=>100,
method_opt=>'for all columns size auto for columns size skewonly (CUST_CITY,CUST_STATE_PROVINCE,COUNTRY_ID)');
end;
select* fromcdb_tab_col_statistics where owner='SH' and table_name='CUSTOMERS';不执行SQL语句做基线：
如果一条SQL性能下降了，可以采用SPM，否则，可以使用SQL Tuning Advisor来完成性能的性能提升:
单独从SQL Tuningset中取一条语句做基线：
SYS@pdbprod5>conn sys/oracle_4U@pdbprod1 as sysdba
Connected.
declare
v_1 number;
begin
v_1 := dbms_spm.load_plans_from_sqlset(sqlset_name=>'STS1',basic_filter=>'sql_id=''9uwfmhuqp69up''');
5end;
6/
PL/SQL procedure successfully completed.
SYS@pdbprod1>
执行SQL语句做基线：
索引可见鱼不可见：
select table_name,column_name,index_name from user_ind_columns where table_name='PROD_INFO';
select table_name,index_name,visibility from user_indexes where table_name='PROD_INFO';
alter index IDX_PROD invisible;
create index PROD_IDX on SH.PROD_INFO(PRODUCT_ID) parallel 4 tablespace example global partition by hash(PRODUCT_ID) partitions 4;
select* from user_ind_columns c
where c.table_name='PRODUCTS';
select * from user_indexesi
where i.index_name='I1';
alter index PRODUCTS_PROD_CAT_IXinvisible;
create index i1 on products(PROD_CATEGORY)
parallel 4globalpartition by hash (PROD_CATEGORY)(partition p1tablespace users,
partition p2 tablespace example,
partition p3 tablespace users,
partition p4tablespace example);
要创建某个公共都能看的对象：
SYS@pdbprod1> grant select on sh.sales to public;
Grant succeeded.
SYS@pdbprod1> create public synonym sales1 for sh.sales;
创建物理备库：
1) host01 主机中的PROD5 作为主库，SID 为PROD5，DB_UNIQUE_NAME 设置为PROD5H1
2) host02 主机中的PROD5 作为备库，SID 为PROD5，DB_UNIQUE_NAME 设置为PROD5H2
3) 目录对象应该在主备库都存在
4) 可以使用如下方法连到备库，connect sys/oracle@PROD5H2 as sysdba，要dedicated 模式
5) 对主备库的全局临时表进行DML时，最小化Redo 的产生
$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Sun Oct 14 16:30:45 2018
Copyright (c) 1982, 2014, Oracle.All rights reserved.
@>conn / as sysdba
Connected to an idle instance.
SYS@PROD4>startup
ORACLE instance started.
Total System Global Area 1895825408 bytes
Fixed Size 2925744 bytes
Variable Size 1241516880 bytes
Database Buffers 637534208 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SYS@PROD4>archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 17
Current log sequence 19
SYS@PROD4>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD4>startup mount
ORACLE instance started.
Total System Global Area 1895825408 bytes
Fixed Size 2925744 bytes
Variable Size 1258294096 bytes
Database Buffers 620756992 bytes
Redo Buffers 13848576 bytes
Database mounted.
SYS@PROD4>alter database archivelog ;
Database altered.
SYS@PROD4>show parameter temp
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_max_failed_login_attempts integer 3
temp_undo_enabled boolean FALSE
SYS@PROD4>alter system set temp_undo_enabled=true;
System altered.
SYS@PROD4>show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string PROD4
SYS@PROD4>alter system set db_name='PROD4H1';
alter system set db_name='PROD4H1'
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SYS@PROD4>alter system set db_name='PROD4H1' scope=spfile ;
alter system set db_name='PROD4H1' scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-32016: parameter "db_name" cannot be updated in SPFILE
SYS@PROD4>alter system set db_unique_name='PROD4H1' scope=spfile ;
System altered.
SYS@PROD4>alter system set db_unique_name='PROD4H1';
alter system set db_unique_name='PROD4H1'
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SYS@PROD4>shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SYS@PROD4>startup
ORACLE instance started.
Total System Global Area 1895825408 bytes
Fixed Size 2925744 bytes
Variable Size 1258294096 bytes
Database Buffers 620756992 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SYS@PROD4>alter user dbsnmp identified by oracle_4U;
User altered.
SYS@PROD4>alter user dbsnmp identified by oracle_4U account unlock ;
User altered.
SYS@PROD4>alter database force loggging;
alter database force loggging
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
SYS@PROD4>alter database force logging;
Database altered.
SYS@PROD4>
1) 物理备用数据库确认收到重做，只有当它可以保证能够应用重做时:指的是最大可用模式。
2) 当物理备用数据库接收重做超过30秒时，它应该发出告警，不论哪个数据库是备库。
3) 当redo尚未被物理备用数据库在45秒内应用，应该发出告警，无论哪个数据库是备库:
DGMGRL> edit database 'PROD5H1' set property TransportLagThreshold=30;
Property "transportlagthreshold" updated
DGMGRL> edit database 'PROD5H2' set property TransportLagThreshold=30;
Property "transportlagthreshold" updated
DGMGRL>
DGMGRL> edit database 'PROD5H2' set property ApplyLagThreshold=45;
Property "applylagthreshold" updated
DGMGRL> edit database 'PROD5H1' set property ApplyLagThreshold=45;
创建一个序列SEQ2，用于全局临时表。它的值必须在会话级唯一，但不同会话中可以允许重复。
create sequence seq2 start with 1 increment by 1 nomaxvalue session;
页:
[1]