11g OCM备考13——性能优化

最新推荐文章于 2020-09-14 11:48:55 发布

cijiayuan5672

最新推荐文章于 2020-09-14 11:48:55 发布

阅读量442

点赞数

文章标签：数据库 java

题目：

1. AWR

1.1 Manual create a awr snapshot

1.2 Modify the snapshot retention to 15 days and snapshot interval 30 minutes

1.3 Generate a AWR report manually with the last 2 snapshots, and report in HTML file

2. BASELINE

2.1 Create 2 baseline templates using sql

1) baseline template name : baseline_11_11 , duration of 24 hours and in the day of "2016-11-11"

2) baseline template name : baseline_mon, every Monday from 8 am to 6 pm, duration of 10 hours , start from 2016-5-08 to 2016-12-31 and kept for 120 days.

2.2 Create a single baseline using sql and EM

1) baseline name: baseline-test

2) using the last 2 snapshots

3. ADDM

3.1 Retrieving ADDM report by using SQL

3.2 Generate a ASH REPORT manually with the last 5 minutes, and report in HTML file

4. Resource Manage

Set Up and Configure Resource Manager Set up and configure Resource Manager using the following specification:

4.1 Assign the user SH as the resource administrator;

4.2 Create tow resource manager consumer groups, OLTP and DSS (User comment

which will be used for).

4.3 Create a plan named WEEKDAYS with the following derectives only:

4.3.1 For OLTP group, we cannot allow more than 20 active sessions. If more than 20 sessions the request should be aborted if the wait exceeds 60 seconds.

4.3.2 The maximum number of active sessions for the DSS group to 5. If more than 5 sessions then the request should be aborted after 120 seconds.

4.3.3 If more than 10000 times the I/O request and 2500M load on OLTP,

switch to LOW_GROUP, if executed finished, to cut back to the original group (not estimated).

4.3.4 The maximum amount of undo that the OLTP group can use should be 200M(204800K);

4.3.5 Set CPU ratios for OLTP, DSS and OTHER_GROUPS as 50, 30 and 20 respectively.

4.3.6 DSS group has parallel degree limit of 20.

4.3.7 The idle session the longest 120s in the OLTP, if blocked above other process 80s, to termination.

4.4 assign the default consumer group for the OLTP_USER to OLTP group

4.5 assign the default consumer group for the SH to DSS group

4.6 Specify that the WEEKDAYS plan be used by the instance as default.

5. Statistics Gathering

5.1 Gather Optimizer statistics for the SH and HR schema in PROD

5.2 Because of the unevenly distributed data in the DEPARTMENT_ID column of the EMPLOYEES table of the HR schema,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.

5.3 Set sh.sales table not invalidate dependent cursors when do statistics collection.

5.4

5.4.1 The SQL statement suggesting that where conditional statements in columns used together often, requirements for SQL optimization.

5.4.2 The sample SQL is as follows:

SELECT count(*) FROM customers WHERE cust_city= 'BJ' and cust_state_province='CA' and country_id=52790;

5.4.3 Among them:

cust_city,cust_state_province,country_id in customers are often in where clause together,how to improve performance.

6. Index

6.1 our application need to access the CUST_LAST_NAME column in CUSTOMERS in the SH schema of orcl database, the users can supply names without regard to case. The application changes all the upper names, analysis reveals that a normal index we place on the column is not used, create an index that will be used by application

6.2 Rebuild and turn on monitoring for all the indexes on the sales table in SH schema

6.3 analysis has revealed that the COUNTRY_ID column of the CUSTOMERS table of the SH schema always have count compute and never update, create index that can table advantage of above

6.4 create index on the COUNTRY_ID and CUST_CITY column in the CUSTEMERS table of the SH, leading column must be COUNTRY_ID, and creation of index use less space

7. SQL PLAN BASELINE

To provide a SQL script, requirements that Envolve SQL Plan Baseline, SQL as follows:

select last_name from emp where employee_id=100;

8. Shared Pool

8.1 make sure the package STANDARD is always keep in memory

8.2 Tuning SQL Statements in sql "select count(*) from sh.sales" , sh user run it and make sure it can use the same query result from the result cache. Set the result cache size to 10MB

8.3 How to ensure a cursor hold into cache When it open?

8.4 create a view owned by sys that lists the pakages, procedures, triggers and functions occupy more than 50K the view should named LARGE_PROC and visible to all users

8.5 analysis ind that the 3th party application is not used bind variables, and there many share pool latch contention, find the best solution to reduce share pool usage

9. Buffer Cache

9.1 Place the table scott.emp into keep cache

9.2 user sh logon, Create a table tb1(id int) into recycle cache

9.3 your developers notify you that they will need java pool set to a minimum of 20MB

10. AMM

10.1 set your maximum SGA and PGA to 750 MB. Turn on automatic memory management( AMM ). Restart database

10.2 set your maximum SGA to 500 MB. Turn on automatic shared memory management( ASMM ). Restart database

10.3 create a permanent tablespacetest2 to store sample test data using 16k block size.

11. ASSM

A junior dba create a table new_cust on tablespace cust_tbs. But you found it perform poorly and buffer busy wait is much higher than other tables on other tablespaces, and the segment head contention occurs. Try to resolve the problem but large query should be maintained.

(note: cust_tbs is manual segment space management )

12. Synonym

Create a sh.sales@PROD1 on the public synonyms in the PROD2, requires that all users can access.

13. Primary key and Foreign key

create two new tables named STUDENTS and ATTENDEES in the OLTP_USER schema.

This 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 maximun of 20 characters.

ATTENDEES will be an intersection table in a many-to-many relationship between the STUDENTS and CLASSES tables 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 itsellf are the same object.

14. Instance caging

This host is only 2 CPU, for PROD1 and PROD2, so to prevent the occurrence of resource competition.

15. 查看执行计划方法

16. SQL Performance Analyze

16.1 From the 10.2 migration to the 11g environment, provides a dump file (the user password: apps/Apps1234),

Contains the Sql Tuning Set, requires the use of a tool for optimization in STS SQL, and the optimized SQL plan_table write in the execution plan

16.2 Dump file location for: /home/oracle/scripts,the file :appsandstage.dmp.

16.3 Migration from 10.2.0.1 to 11.1.0.7

答案：

1.

1.1 SQL> exec dbms_workload_repository.create_snapshot();

1.2 SQL> exec dbms_workload_repository.MODIFY_SNAPSHOT_SETTINGS(21600,30);

1.3 SQL> @?/rdbms/admin/awrrpt.sql

2.

2.1

SQL> exec dbms_workload_repository.CREATE_BASELINE_TEMPLATE(START_TIME=>to_date('2016-11-11 00:00:00','yyyy-mm-dd hh24:mi:ss'),END_TIME=>to_date('2016-11-12 00:00:00','yyyy-mm-dd hh24:mi:ss'),TEMPLATE_NAME=>'baseline_11_11',BASELINE_NAME=>'baseline_11_11');

SQL> exec dbms_workload_repository.CREATE_BASELINE_TEMPLATE(DAY_OF_WEEK=>'MONDAY',HOUR_IN_DAY=>8,DURATION=>8,START_TIME=>to_date('2016-05-08 00:00:00','yyyy-mm-dd hh24:mi:ss'),END_TIME=>to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss'),BASELINE_NAME_PREFIX=>'BASELINE_MON',TEMPLATE_NAME=>'BASELINE_MON',EXPIRATION=>120);

2.2

SQL> exec dbms_workload_repository.create_baseline(START_SNAP_ID=>113,END_SNAP_ID=>114,BASELINE_NAME=>'BASELINE-TEST');

3.

3.1 SQL> @?/rdbms/admin/addmrpt.sql

3.2 SQL> @?/rdbms/admin/ashrpt.sql

4.用EM操作

5.

5.1

exec dbms_stats.gather_schema_stats(ownname=>'SH');

exec dbms_stats.gather_schema_stats(ownname=>'HR');

5.2

SQL> exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'EMPLOYEES');

5.3

SQL> exec dbms_stats.gather_table_stats(ownname=>'SH',tabname=>'SALES',no_invalidate=>true);

5.4

SQL> exec dbms_stats.gather_table_stats(ownname=>'SH',tabname=>'CUSTOMERS',method_opt=>'FOR COLUMNS (cust_city,cust_state_province,country_id)');

6.

6.1

SQL> create index idx_customers on customers(upper(cust_last_name));

6.2

SQL> select INDEX_NAME from user_indexes where table_name='SALES';

SQL> alter index PROD_ID_IDX monitoring usage;

6.3

SQL> create bitmap index index_customer_cid on customers(country_id);

6.4

SQL> create index idx_cust_id_city on customers(country_id,cust_city) compress;

7.

SQL> alter session set optimizer_capture_sql_plan_baselines=true;

8.

8.1

SQL> execute dbms_shared_pool.keep('SYS.STANDARD');

8.2

SQL> alter system set result_cache_max_size=10m;

SQL> select /*+ RESULT CACHE */ count(*) from sh.sales;

8.3

SQL> alter system set cursor_space_for_time=true scope=spfile;

8.4

create view large_proc as select * from v$db_object_cache where type in ('function','trigger','package','procedure') and sharable_mem>51200;

grant select on large_proc to public;

8.5

SQL> alter system set cursor_sharing=force;

9.

SQL> alter table scott.emp storage(buffer_pool keep);

SQL> create table tb1(id int) storage(buffer_pool recycle);

SQL> alter system set java_pool_size=20m;

10.

(1) alter system set memory_max_target=750m scope=spfile;

alter system set memory_target=750m scope=spfile;

(2)alter system set memory_target=0;

alter system set sga_max_size=500m scope=spfile;

alter system set sga_target=500m scope=spfile;

(3)alter system set db_16k_cache_size=10m;

create tablespace test3 datafile '/u01/app/oracle/oradata/orcl/test03.dbf' size 100m blocksize 16k;

11.

SQL> create tablespace cust_new_tbs datafile '/u01/app/oracle/oradata/woods/cust_new_tbs.dbf' size 100m autoextend on next 10m maxsize 500m extent management local uniform size 10m segment space management auto;

SQL> alter table new_cust move tablespace cust_new_tbs;