S5

Skillset 5：(90 min)

**描述**  
**Description**

数据文件可以自动扩展，但不能超过2G；  
Datafiles can autoextend but can not more than 2G;

该部分不能重启数据库 PROD1。  
This section can not restart database PROD1.

**数据库名称 用户/密码**  
**Database Name User/Password**

PROD1 sh/sh、hr/hr、oe/oe、oltp_user/oltp_user1、sys/oracle、system/oracle

PROD2/PROD3 sys/oracle、system/oracle

PROD4 sys/oracle、system/oracle、rc_admin/RC_ADMIN

EMREP sys/Oracle123、system/Oracle123、sysman/Oracle123 
```
安装em

emca -config dbconsole db -repos recreate 
端口数据库端口

```
**任务**  
**Tasks**

**第一部分：创建同义词**  
**Section 1: Create a Synonym**

PROD2 再次被销毁（隐含）  
PROD2 was destroyed again (implied)

1. 系统表空间的数据库文件被删除  
 The data file that belongs to the system tablespace is deleted
2. 控制文件被删除  
 The controlfile is deleted
```
export ORACLE_SID=PROD2
rman target / catalog rc_admin/RC_ADMIN@prod4
list failure;
advise failure;
repair failure;

list failure;
advise failure;
repair failure;

```
1.1. 在 PROD2 数据库中为 sh.sales@PROD1 创建一个公共同义词。  
 Create a public synonym for sh.sales@PROD1 in the PROD2 Database.
```
create public database link prod1 connect to sh identified by sh using 'prod1';
 
create public synonym sales for sh.sales@prod1;
 
select count(*) from sales;
```
---

**第二部分：配置资源管理器**  
**Section 2: Configure the Resource Manager**

在 PROD1 数据库上设置和配置资源管理器。以下是规格：  
Set up and configure the Resource Manager on the PROD1 database. Following are the specifications:

2.1. 将 SH 用户指定为资源管理员。  
Assign the SH user as the resource administrator.
```
em
server 
User  
object Name - SH
Edit
System Privileges
Edit List
adminster resourse manager - move 
OK
Apply
```

2.2. 创建两个资源管理消费者组：OLTP 和 DSS。（使用注释标记每个组的用途。）  
 Create two resource manager consumer groups: OLTP and DSS. (Use comments with each to denote what the groups will be used for.)
```
em
server 
Consumer Groups
create
分别创建（一次两个格子一样的，第二个格子为描述） OLTP和DSS

```
2.3. 创建一个名为 DAYTIME 的计划，具有以下指令：  
 Create a plan named DAYTIME with the following directives only.
```
em
server
Plans
create
Plan DAYTIME
```

2.3.1. 对于 OLTP 组，不允许超过 20 个活动会话。如果第 21 个用户尝试登录，且等待超过 60 秒，则请求应被中止。  
 For the OLTP group, we cannot allow more than 20 active sessions. If the 21st user attempts to log in, the request should be aborted if the wait exceeds 60 seconds.

2.3.2. DSS 组的最大活动会话数为 5。如果请求超过 5 个会话，则请求在 120 秒后应被中止。  
The maximum number of active sessions for the DSS group is 5. If more than 5 sessions are requested, the request should abort after 120 seconds.
```
General
Modify
添加 OLTP /  DSS / LOW_GROUP 
Session 
Max Number of Active Sessions 最大会话数
Activation Queue Timeout (sec) 超时时间

```
2.3.3. OLTP 组可以使用的最大撤销量应设置为 200 MB。  
The maximum amount of undo that the OLTP group can use should be set to 200 MB.
```
undo pool
204800
```
2.3.4. 为 OLTP、DSS 和 OTHER_GROUPS 设置 CPU 比率，分别为 50、30 和 20。  
Set CPU ratios for OLTP, DSS, and OTHER_GROUPS as 50, 30, and 20 respectively.
```
General
填 对应Percentage
```
2.3.5. DSS 组的并行度限制为 20。  
DSS group has a parallel degree limit of 20.
```
Parallelism
Max Degree of Parallelism
20
```
2.3.6. OLTP 中最长空闲会话为 120 秒，如果被阻塞超过 80 秒，则终止该会话。  
The idle session the longest 120s in the OLTP, if blocked above other process 80s, to termination.
```
idle time
120  80
```
2.3.7. 如果 DSS 组的会话超过 10,000 次 I/O 请求或超过 2,500 MB 的数据传输，则暂时将该会话切换到 LOW_GROUP 消费者组。会话在完成有问题的顶级调用后应返回其原始组。  
Temporarily switch any session in DSS group to the LOW_GROUP consumer group if the session exceeds 10,000 I/O requests or exceeds 2,500 megabytes of data transferred. The session should return to its original group after the offending top call is complete.
```
Thresholds
2500 10000 switch to group  Low_group revert    after call
```
2.4. DAYTIME 资源计划应为活动计划。  
2.4. The DAYTIME resource plan should be an active plan.
```
General
Activate this plan
			
	Automatic Plan Switching Enabled
两个选项打勾
```
---

**第三部分：优化架构对象**  
**Section 3: Optimize Schema Objects**

3.1. 一个应用程序需要访问 PROD1 数据库中 SH 架构的 CUSTOMERS 表中的 CUST_LAST_NAME 列。问题是用户可以提供不考虑大小写的名称。应用程序将所有用户提供的名称更改为大写。分析发现该列上的普通索引未被使用。为 CUST_LAST_NAME 列创建一个索引，供应用程序使用。  
An application needs to access the CUST_LAST_NAME column in the CUSTOMERS table in the SH schema of the PROD1 database. The problem is that users can supply names without regard to case. The application changes all the user-supplied names to uppercase. Analysis reveals that a normal index on that column is not used. Create an index on the CUST_LAST_NAME column that will be used by the application.
```
conn sh/sh

create index cust_indx on customers(upper(cust_last_name))

set autotrace trace
select * from customers where upper(cust_last_name)='TEST'; 
set autotrace off

select index_name,index_type,status from user_indexes where index_name='CUST_INDX';
```
3.2. 在 PROD1 数据库中，为 SH 架构的 CUSTOMERS 表上的所有索引启用监控。架构密码为 sh。  
Turn on monitoring for all the indexes on the CUSTOMERS table in the SH schema of the PROD1 database. The schema password is sh.
```
select 'alter index '|| index_name || ' monitoring usage;' from user_indexes where table_name='CUSTOMERS';
执行以上 alter index 语句

set linesize 120
select * from v$object_usage;

```
3.3. 在 PROD1 数据库的 OLTP_USER 架构中创建两个新表，名为 STUDENTS 和 ATTENDEES。STUDENTS 表将包含三列：STUD_ID 应为数字且为主键，FNAME 和 LNAME 为其他两列，长度最大为 20 个字符。ATTENDEES 将是与 OLTP_USER 架构中的 CLASSES 表之间的多对多关系的交集表，ATTENDEES 表将包含其他表的主键作为主键。创建 ATTENDEES 表，使主键索引和表本身为同一对象。  
Create two new tables named STUDENTS and ATTENDEES in the OLTP_USER schema of the PROD1 database. The STUDENTS table will contain three columns: STUD_ID will be a number and the primary key. FNAME and LNAME will be the other two columns and may vary in length with a maximum of 20 characters. ATTENDEES will be an intersection table in a many-to-many relationship between the STUDENTS and CLASSES tables, which is also in the OLTP_USER schema. The ATTENDEES table will contain the primary keys of each of the other tables as its primary key. Create the ATTENDEES table so that the primary key index and the table itself are the same object.
```
conn oltp_user/oltp_user1
create table students (stud_id number primary key,fname varchar(20),lname varchar(20));
desc classes
select constraint_name,column_name from user_cons_columns where table_name='CLASSES';
-- 创建一个名为 attendees 的表
create table attendees (
    -- 学生 ID，引用 students 表中的某一列
    stud_id references students, 
    
    -- 课程 ID，引用 classes 表中的某一列
    class_id references classes, 
    
    -- 定义主键约束，确保 stud_id 和 class_id 的组合是唯一的
    constraint att_pk primary key (stud_id, class_id)
) 
-- 指定表的存储结构为索引组织表（IOT）
organization index;
```
3.4. 由于 HR 架构的 EMPLOYEES 表中的 DEPARTMENT_ID 列数据分布不均，需要提供更多信息给优化器，以便更有效地使用索引。重新生成 EMPLOYEES 表的统计信息以解决此问题。  
Because of the unevenly distributed data in the DEPARTMENT_ID column of the EMPLOYEES table of the HR schema of the PROD1 database, you need to supply more information to the optimizer to allow for more efficient use of indexes. Regenerate statistics on the EMPLOYEES table to solve this problem.
```
export ORACLE_SID=PROD1 
exec dbms_stats.gather_table_stats('HR','EMPLOYEES',method_opt=>'for all indexed columns size auto',cascade=>true);
```
3.5. 分析表明，SH 架构的 CUSTOMERS 表中的 COUNTRY_ID 列具有非常低的基数。该列从未更新。创建一个索引，可以利用该列的这些属性。  
 Analysis has revealed that the COUNTRY_ID column of the CUSTOMERS table of the SH schema of the PROD1 database has very low cardinality. This column is never updated. Create an index that can take advantage of these attributes of this column.
```
create bitmap index sh.cust_indx2 on sh.customers(country_id);
conn sh/sh
select index_name, index_type,status from user_indexes where table_name='CUSTOMERS';

```
3.6. 在 PROD1 数据库的 SH 架构的 CUSTOMERS 表中为 COUNTRY_ID 和 CUST_CITY 列创建一个索引。应用程序要求该索引的前导列必须是 COUNTRY_ID 列。利用 Oracle 索引的特性，在创建索引时，当前导列经常重复时，可以减少使用的空间。  
Create an index on the COUNTRY_ID and CUST_CITY columns in the CUSTOMERS table of the SH schema of the PROD1 database. The application requires that the leading column of this index must be the COUNTRY_ID column. Take advantage of the Oracle feature of indexes that allows the creation of the index to use less space when the leading column is often duplicated.
```
create index cust_indx3 on customers(country_id,cust_city) compress 1;
```
3.7. 确保在所有情况下，PROD1 数据库中的 STANDARD 包在内存中可用。  
Make sure that the package named STANDARD in the PROD1 database is available in memory in all situations.
```
exec dbms_shared_pool.keep('STANDARD','P');

select name,type,kept from v$db_object_cache where name='STANDARD';

dbms_shared_pool.unkeep 将包移出内存
exec dbms_shared_pool.unkeep('STANDARD','P');
```
3.8. 对 PROD1 数据库的分析表明，第三方应用程序未使用绑定变量并且数据偏斜。此外，我们发现共享池锁争用。寻找减少共享池使用的最佳解决方案。  
 Analysis of the PROD1 database reveals that a third-party application is not using bind variables and has skewed data. In addition, we find shared pool latch contention. Find the best solution to reduce shared pool usage.
```
alter system set cursor_sharing=force; 
```
3.9. 你的初级 DBA 创建了一个新表空间 CUST_TBS，并将一个表 NEW_CUST 放在该表空间中。观察表明，该实例在头块上记录了大量的缓冲区忙等待。V\$WAITSTAT 视图在段头类别中累计了大量等待。NEW_CUST 表在工作时间有大量的并发插入。采取适当措施减少争用。应保持长期查询性能。  
Your junior DBA has created a new tablespace, CUST_TBS, and placed a table, NEW_CUST, in that tablespace. Observation shows that the instance records significant numbers of buffer busy waits on the header block. The V$WAITSTAT view has accumulated large numbers of waits in the segment header category. The NEW_CUST table has large numbers of concurrent inserts during business hours. Take appropriate actions to reduce the contention. Long-term query performance should be maintained.
```
select tablespace_name,table_name from dba_tables where table_name = 'NEW_CUST';
select tablespace_name,segment_space_management from dba_tablespaces where tablespace_name = 'CUST_TBS';
conn hr/hr
select index_name,table_name from user_indexes where table_name = 'NEW_CUST';
select constraint_name,table_name from user_constraints where table_name = 'NEW_CUST';
```

代码方式
```
BEGIN
    DBMS_REDEFINITION.CAN_REDEF_TABLE('hr', 'NEW_CUST', DBMS_REDEFINITION.CONS_USE_PK);
END;
/

CREATE TABLE hr.int_NEW_CUST (
    x NUMBER,
    CONSTRAINT int_pk_NEW_CUST PRIMARY KEY (x)
) TABLESPACE users;

BEGIN
    DBMS_REDEFINITION.START_REDEF_TABLE('hr', 'NEW_CUST', 'int_NEW_CUST', 'x x', DBMS_REDEFINITION.CONS_USE_PK);
END;
/

BEGIN
    DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(
        uname => 'HR',
        orig_table => 'NEW_CUST',
        int_table => 'INT_NEW_CUST',
        dep_type => DBMS_REDEFINITION.CONS_INDEX,
        dep_owner => 'HR',
        dep_orig_name => 'pk_NEW_CUST',
        dep_int_name => 'Int_pk_NEW_CUST'
    );
END;
/

BEGIN
    DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(
        uname => 'HR',
        orig_table => 'NEW_CUST',
        int_table => 'INT_NEW_CUST',
        dep_type => DBMS_REDEFINITION.CONS_CONSTRAINT,
        dep_owner => 'HR',
        dep_orig_name => 'pk_NEW_CUST',
        dep_int_name => 'Int_pk_NEW_CUST'
    );
END;
/

DECLARE
    num_errors PLS_INTEGER;
BEGIN
    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('hr', 'NEW_CUST', 'int_NEW_CUST', DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/

BEGIN
    DBMS_REDEFINITION.SYNC_INTERIM_TABLE('HR', 'NEW_CUST', 'INT_NEW_CUST');
END;
/

BEGIN
    DBMS_REDEFINITION.FINISH_REDEF_TABLE('HR', 'NEW_CUST', 'INT_NEW_CUST');
END;
/
```
em表的在线重定义
```
schema
Reorganize Objects
Schema Objects
next
add
tablespace - CUST_TBS
表选上
ok

set attributes by type:指定对象及索引最终存放的表空间 
表空间执行USERS 确保modified都是对号
next
Availlability online USEROWID选中
Rebuild indexs withoutlogging 选中
next
next
账号密码
oracle、oracle123
next
sublit job
状态successded
```

em在线重定义表空间属性
```
service
tablespace
schema
Reorganize Objects
Set Attibutes
Automatic
Availlability online USEROWID选中
Use tablespace rename feature  选中
Rebuild indexs withoutlogging 选中
next
next
```

3.10. 以 SH 用户身份登录并收集 SALES 表的统计信息。确保与该对象相关的游标在缓存中永远不会失效。  
Log in as the SH user and gather statistics for the SALES table. Ensure that the cursors pertaining to the object in the cache are never invalidated.
```
conn sh/sh
exec dbms_stats.set_table_prefs('SH','SALES','NO_INVALIDATE','TRUE');
exec dbms_stats.gather_table_stats('SH','SALES');
```
3.11. 在 PROD1 数据库中，SH 架构的 PRODUCT_INFORMATION_PART 表在插入时存在性能问题。该表中使用序列生成 PRODUCT_ID 列的值。还经常执行范围扫描。为 PRODUCT_ID 列创建一个索引 PROD_IDX，以克服插入表时的性能问题。  
Your junior DBA has created a new tablespace, CUST_TBS, and placed a table, NEW_CUST, in that tablespace. Observation shows that the instance records significant numbers of buffer busy waits on the header block. The V$WAITSTAT view has accumulated large numbers of waits in the segment header category. The NEW_CUST table has large numbers of concurrent inserts during business hours. Take appropriate actions to reduce the contention. Long-term query performance should be maintained.
```
conn sh/sh
create index prod_idx on product_information_part(product_id) global partition by hash(product_id) partitions 4;
create index prod_idx on product_information_part(product_id) global partition by hash(product_id) partitions 4 parallel 4;

```

**第四部分：使用结果缓存：**  
**Section 4: Use Result Cache:**

4.1. 在 PROD1 数据库中，以 HR 用户身份登录并运行位于 /home/oracle/scripts 目录中的 s5_22.sql 脚本。  
In the PROD1 database, log in as HR user and run the s5_22.sql script located in the /home/oracle/scripts directory.
调整查询以使用结果缓存中的相同查询。将结果缓存大小设置为 15 MB。  
Tune the queries to use the same query result from the result cache. Set the result cache size to 15 MB.
```
alter system set result_cache_max_size=15m; 
select dbms_result_cache.status() from dual;

修改脚本
select 后加/*+ result_cache */

或
alter table employees result_cache (mode force);
再执行脚本  不用修改脚本
```
**第五部分：使用多列统计信息**  
**Section 5: Use multi column statistics**

5.1. 在查询中，CUST_CITY、CUST_STATE_PROVINCE 和 COUNTRY_ID 列一起用作谓词。  
In the query, the CUST_CITY, CUST_STATE_PROVINCE, and COUNTRY_ID columns are used together as the predicates.

示例 SQL 如下：SELECT count(*) FROM customers WHERE cust_city='BJ' and cust_state_province='CA' and country_id=52790;  
The sample SQL is as follows: SELECT count(*) FROM customers WHERE cust_city= 'BJ' and cust_state_province='CA' and country_id=52790;

其中：customers 中的 cust_city、cust_state_province、country_id 经常一起出现在 WHERE 子句中，如何提高性能。  
Among them: cust_city, cust_state_province, country_id in customers are often in where clause together, how to improve performance.
```
conn sh/sh

create index cust_com_index on customers(cust_city,cust_state_province,country_id);

exec dbms_stats.gather_table_stats('sh','customers', method_opt =>'for all columns size skewonly for columns (cust_city,cust_state_province,country_id) size skewonly');
或
exec dbms_stats.gather_table_stats('sh','customers', method_opt =>'for columns (cust_city,cust_state_province,country_id) size auto');

验证
SELECT SYS.DBMS_STATS.SHOW_EXTENDED_STATS_NAME('sh','customers', '(cust_city,cust_state_province,country_id)') col_group_name FROM DUAL;
```

**第六部分：使用 SQL 性能分析器**  
**Section 6: Use SQL Performance Analyzer**

公司正在从 Oracle Database 10g (10.2.0.1) 迁移到 Oracle Database 11g (11.2.0.3)。  
Your company is migrating from Oracle Database 10g (10.2.0.1) to Oracle Database 11g (11.2.0.3).

6.1. 测试新环境中查询的性能。appsandstage.dmp 文件包含 SQL 语句集。APPS 架构的密码是 Apps1234。  
Test the performance of the queries in the new environment. The appsandstage.dmp file contains the set of SQL statements. The password for the APPS schema is Apps1234.
```
cd /home/oracle/scripts/
export ORACLE_SID=PROD1
create directory tune_results as '/home/oracle/scripts'; 
grant all on directory tuen_results to public;
impdp system/oracle directory=tune_results dumpfile=appsandstage.dmp logfile=appsandstage.log
```
6.2. 导入在 STS_JFV_PS 表中分阶段的 STS_PS SQL 调优集。使用适当的工具改善 STS_PS SQL 调优集中的语句的性能。将改善后的计划存储在 APPS 架构中的持久计划表 PLAN_TABLE 中。  
Import the STS_PS SQL tuning set staged in the STS_JFV_PS table. Use the appropriate tool to improve the performance of the statements in the STS_PS SQL tuning set. Store the improved plan in the persistent plan table named PLAN_TABLE in the APPS schema.

转储文件位置：/home/oracle/scripts，文件名：appsandstage.dmp。  
Dump file location for: /home/oracle/scripts, the file: appsandstage.dmp.
```
exec dbms_sqltune.unpack_stgtab_sqlset('%','%',true,'STS_JFV_PS','APPS');
```

**第七部分：使用 SQL 计划管理功能**  
**Section 7: Use SQL Plan Management feature**

7.1. 发展 SQL 计划基线，规格如下：  
Evolve the SQL plan baselines as the following specifications:
以 SH 用户身份登录到 PROD1 数据库并运行如下脚本：  
Log in as the SH user to the PROD1 database and run the script as follows:
select last_name from emp where employee_id=100;  
select last_name from emp where employee_id=100;

发展 SQL 语句的 SQL 计划基线。  
Evolve the SQL plan baselines for the SQL statement.
```
conn hr/hr
select last_name from emp where employee_id=100;

truncate table emp;

begin
for i in 1..6
loop
insert into hr.emp select * from hr.employees; commit;
insert into hr.emp select * from hr.emp;
commit;
end loop;
update hr.emp set employee_id=rownum;
commit;
end;
/

select count(*) from emp;

```
---

**第八部分：实施实例笼**  
**Section 8: Implement instance caging**

8.1. 此主机只有 2 个 CPU，防止 PROD1 与数据库服务器上的 PROD2 实例干扰。  
This host is only 2 CPU, Prevent the PROD1 from instance interfering with the PROD2 instance on the Database Server machine.
```
alter system set cpu_count=1

show parameter cpu_count
 
show parameter resource_manager_plan

```

DBLOG

导航1234

最近发表

友情链接