数据库管理-m6米乐安卓版下载

第四堂继续！

考点3：sql statement tuning sql语句调优

1. 收集schema统计信息

exec dbms_stats.gather_schems_stats('hr');

2. 开启制定表索引监控

create index sh_cust_ind_1 on sh.customers(cust_last_name));
select 'alter index sh.'||index_name||' monitoring usage;' from dba_indexes where owner='sh' and table_name='customers';
-- 执行输出结果

3. 创建索引组织表
   按照题目要求创建对应的表：

create table oltp_user.students(stud_id number primary key,fname varchar2(20),lname varchar2(20));
create table oltp_user.classes(class_id number primary key,class_name varchar2(20));
create user oltp_attendees (
	stud_id number,
	class_id number,
	constraint pk_s_c_pk ( stud_id, class_id ),
	constraint fk_stud_id foreign key ( stud_id ) references oltp_user.students ( stud_id ),
	constraint fk_class_id foreign key ( class_id ) references oltp_user.classes ( class_id ) 
) organization index;

4. 收集直方图统计信息

exec dbms_stats.gather_table_stats('hr','employees', method_opt=>'for columns size skewolny');

5. 创建位图索引（低选择性）

create bitmap index sh.cust_c_bit on sh.customers(country_id);

6. 压缩索引

create index sh.cust_c_ind sh.customers(country_id,cust_city) compress 1;

7. 共享池保存

exec dbms_shared_pool.keep('standard','p');

8. 绑定变量配置为相似匹配

alter system set cursor_sharing='similar';

9. 段空间管理改为自动
   需要将cust_tbs表空间转换为级自动管理：

select 'alter table '||owner||'.'||table_name||' move tablepsace users;' from dba_tables where tablespace_name='cust_tbs';
select 'alter index '||owner||'.'||index_name||' rebuild tablepsace users;' from dba_indexes where tablespace_name='cust_tbs';
-- 执行输出
drop tablespace cust_tbs including contents and datafiles;
create tablespace cust_tbs datafile '/u01/app/oracle/oradata/prod4/cust_tbs' size 10m autoextend on;
-- 替换前面输出结果将表和索引表空间变更回cust_id

10. 收集多列统计信息

exec dbms_stats.create_extended_stats('hr','employees','(department_id,salary'));
exec dbms_stats.gather_table_stat('hr','empolyees',method_opt=>'for columns (departmemt_id,salary)');

11. spa
    建议使用emcc进行操作:
    
    
    后续操作根据提示来做。如时间不足可放弃。
12. spm基线稳定

oracle database 19c:
$\qquad$ ->pl/sql packages and types reference
$\qquad\qquad$ ->171 dbms_sqltune
$\qquad\qquad\qquad$ ->171.5 summary of dbms_sqltune subprograms
$\qquad\qquad\qquad\qquad$ ->171.5.8 create_sqlset procedure and function
$\qquad\qquad\qquad\qquad$ ->171.5.35 select_cursor_cache function
$\qquad\qquad\qquad\qquad$ ->171.5.19 load_sqlset procedure

oracle database 19c:
$\qquad$ ->pl/sql packages and types reference
$\qquad\qquad$ ->163 dbms_spm
$\qquad\qquad\qquad$ ->163.6 summary of dbms_spm subprograms
$\qquad\qquad\qquad\qquad$ ->163.6.15 load_plans_from_sqlset function
$\qquad\qquad\qquad\qquad$ ->163.6.9 evolve_sql_plan_baseline function

select * from scott.e1 where empno=7788;
exec dbms_sqltune.create_sqlset(sqlset_name=>'sqlset1',sqlset_owner=>'sys');
declare
	cur dbms_sqltune.sqlset_cursor;
	a1 varchar2(100);
begin
	a1 := 'upper(sql_text) like "select % from scott.e1 where empno=7788%';
	open cur for
	select value(p) from table(dbms_sqltune.select_cursor_cache(attribute_list=>'typical',base_filter=>a1)) p;
	dbms_sqltune.load_sqlset(sqlset_name=>'sqlset1',populate_cursor=>cur,load_option=>'merge',update_option=>'accumulate',sqlset_owner=>'sys');
end;
/
alter session set optimizer_capture_sql_plan_baseline=true;
declare
	mp pls_integer;
begin
	mp :=dbms_spm.load_plans_from_sqlset(sqlset_name=>'sqlset1');
end;
/
select * from scott.e1 where empno=7788;
select sql_handel,plan_name,accepted,enabled,fixed from dba_sql_plan_baselines; --结果a
create index scott.e1_ind on scott.e1(empno);
select * from scott.e1 where empno=7788;
select sql_handel,plan_name,accepted,enabled,fixed from dba_sql_plan_baselines; --结果b
declare
	r clob;
begin
	r:=dbms_spm.evolve_sql_plan_baseline(sql_handle=>'上面查出来的sql_handle');
	-- dbms_output.put_line(r);
end;
/
select sql_handel,plan_name,accepted,enabled,fixed from dba_sql_plan_baselines; --结果变为相同

考点4：create appropriate schema objects 创建schema对象

lob列建表配置：

create table ...,lob_col(clob),... tablespace user
	lob(lob_col) store as (
		tablespace lob_data
		disable storage in row
		chunk 16k
		nocache
		storage (
			initial 2m
			next 2m
		)
	)
;

考点5：configuring parallel execution 配置并行执行

这里就要详见数据库管理-第九十一期 19c ocm之路-第三堂（20230711）考点9，上一期也讲了变更原因。

考点6：instance tuning 实例调优

为某pdb或某实例设置最大使用2颗cpu资源：

sqlplus sys/oracle@pdbprod2 as sysdba
	alter system set cpu_count=2;
sqlplus sys/oracle@prod4 as sysdba
	alter system set cpu_count=2 scope=spfile;
	shut immediate
	startup

总结

第四堂考试内容基本结束。
老规矩，知道写了些啥。