Oracle深度学习笔记——概要文件

1、25.oracle 深度学习笔记sql profiles要文件1. 引子oracle 系统为了合理分配和使用系统的资源提出了概要文件的概念。 所谓概要文件， 就 是一份描述如何使用系统的资源(主要是cpu资源)的配置文件。将概要文件赋予某个数据库用户，在用户连接并访问数据库服务器时，系统就按照概要文件给他分配资源。包括 :1 、管理数据库系统资源。 利用profile来分配资源限额，必须把初始化参数resource_limit设置为true默认是true的。2、管理数据库口令及验证方式。默认给用户分配的是default既要文件，将该文件赋予了每个创建的用户。但该文件对资源没有任何限制，因此管理

2、员常常需要根据自己数据库系统的环境自行建立概要文件。2. 概要文件限制概要文件主要可以对 数据库系统如下指标进行限制。1)用户的最大并发会话数(session_per_user)2)每个会话的cpu 时钟限制(cpu_per_sessio)n3) 每次调用的 cpu 时钟限制， 调用包含解析、 执行命令和获取数据等等。( cpu_per_cal)l4)最长连接时间。一个会话的连接时间超过指定时间之后，oracle 会自动的断开连接( connect_time)5)最长空闲时间。如果一个会话处于空闲状态超过指定时间，oracle 会自动断开连接( idle_time)6)每个会话可以读取的最大数

3、据块数量(logical_reads_per_sess)ion7)每次调用可以读取的最大数据块数量(logical_reads_per_ca)ll8) sga私有区域的最大容量(private_sga概要文件对 口令的定义和限制 如下：1)登录失败的最大尝试次数(failed_login_attempt)s2)口令的最长有效期(password_life_time)3)口令在可以重用之前必须修改的次数(password_reuse_max)4)口令在可以重用之前必须经过的天数(password_reuse_time)5)超过登录失败的最大允许尝试次数后，账户被锁定的天数6)指定用于判断口令复杂

4、度的函数名在指定概要文件之后， dba 可以手工的将概要文件赋予每个用户。 但是概要文件不是立即生效，而是要将初始化参数文件中的参数resource_limi改置为true之后，概要文件才会生效。3. sql profilesql profil施oracle10g中弓|入，主要目的侧重于 sql优化，弥补了存储概要的缺点. dba可以使用sql调整顾问（stan sql访问顾问（saa诔识别可以得到更好性能的 sql语句， 这些语句可以保存在 sql调整集、一个 awr快照或保存在当前的库缓存中，一旦识别 出调整候选者，这些顾问程序就开始分析捕获到的语句以期获得更好的性能，然后生成专用的语句扩

5、展（就叫做sql配置文件）并重写sql语句以在执行期间获取更佳的性能。与存储概要类似，一个 sql配置文件提供了使用更好的执行计划的能力（如果这个执行计划是可用的），sql配置文件也可以象存储概要一样分阶段执行，或限制到对特定会话才能 执行该sql配置文件，但是大多数重要的改进超过了存储概要sql profile对于一下类型语句有效：selec研句；update语句；insert语句（仅当使用 select?句时有效）；deleted 句；created句（仅当使用 select?句时有效）；merge语句（仅当作 updated insert作时有效）。另外，使用 sql profile还必

6、须有 create any sql profjidrop any sql profile alter any sql profil繇系统权限。4. 测试一创建表tpcctoaddb create table t1 as select object_id,object_name from dba_objects where rownum create table t2 as select * from dba_objects;table created.创建索引：tpcctoaddb create index t2_idx on t2(object_id);index created.收集统计信

7、息：tpcctoaddb exec dbms_stats.gather_table_stats(user,t1,cascade=true,method_opt=forall columns size 1);pl/sql procedure successfully completed.tpcctoaddb exec dbms_stats.gather_table_stats(user,t1,cascade=true,method_opt=forall columns size 1);pl/sql procedure successfully completed.执行无hint的sqltpcct

8、oaddb set autotrace ontpcctoaddb select t1.*,t2.owner from t1,t2 where t1.object_name like %t1% and t1.object_id=t2.object_id;42 rows selected.execution planplan hash value: 1838229974| id | operation| name | rows | bytes | cost (%cpu)| time|0 | select statement | 2500 |97k|498(1)| 00:00:01 |*1 | ha

9、sh join |2500 |97k|498(1)| 00:00:01 |*2 | table access full| t1 |2500 | 72500 |68(0)| 00:00:01 |3 | table access full| t2 | 92021 |988k|430(1)| 00:00:01 |predicate information (identified by operation id):1 - access(t1.object_id=t2.object_id)2 - filter(t1.object_name like %t1% and t1.object_name is

10、not null)statistics0 recursive calls0 db block gets1789 consistent gets0 physical reads0 redo size2350 bytes sent via sql*net to client573 bytes received via sql*net from client0 sql*net roundtrips to/from client0 sorts (memory)0 sorts (disk)0 2 rows processed执行带 hint 的 sqlsqlselect /*+ use_nl(t1 t2

11、) index(t2) */ t1.*,t2.owner from t1,t2 where t1.object_name like %t1% and t1.object_id=t2.object_id;42 rows selected.execution planplan hash value: 1022743391| id | operation| name | rows | bytes | cost (%cpu)| time |0 | select statement |2500 |97k|5069(1)| 00:00:01 |1 | nested loops|2500 |97k|5069

12、(1)| 00:00:012 | nested loops|2500 |97k|5069(1)| 00:00:01*3 | table access full | t1|2500 | 72500 |68 (0)| 00:00:01 |1 (0)| 00:00:01 |11 |2(0)| 00:00:01*4 | index range scan | t2_idx |1 |5 | table access by index rowid| t2 |1 |predicate information (identified by operation id):3 - filter(t1.object_n

13、ame like %t1% and t1.object_name is not null)4 - access(t1.object_id=t2.object_id)statistics1 recursive calls0 db block gets304 consistent gets24 physical reads0 redo size2350 bytes sent via sql*net to client573 bytes received via sql*net from client4 sql*net roundtrips to/from client0 sorts (memory

14、)0 sorts (disk)42 rows processed使用 sql profile查找执行 sql的sql_idtpcctoaddb select sql_id,sql_text from v$sql where sql_text like %t1.object_name%;sql_idsql_text4zbqykx89yc8vselect t1.*,t2.owner from t1,t2 where t1.object_name like %t1% and t1.object_id=t2.object_id18bphz37dajq9select /*+ use_nl(t1 t2)

15、index(t2) */ t1.*,t2.owner from t1,t2 where t1.object_name like %t1% andt1.object_id=t2.object_id运行存储过程如下：var tuning_task varchar2(100);declarel_sql_id v$session.prev_sql_id%type;l_tuning_task varchar2(30);beginl_sql_id:= 4zbqykx89yc8v;l tuning task := dbms sqltune.create tuning task(sql id = l sql

16、id);:tuning_task:=l_tuning_task;dbms sqltune.execute tuning task(l tuning_task);dbms output.put line(l tuning task);end;/task_114pl/sql procedure successfully completed.查看task的名字tpcctoaddb print tuning_task;tuning_tasktask_114查看执行报告set long 99999col comments format a200select dbms_sqltune.report_tun

17、ing_task(:tuning_task)comments from dual; commentsgeneral information sectiontuning task name : task_114tuning task owner : tpccworkload type : single sql statementscope: comprehensivetime limit(seconds): 1800completion status : completedstarted at : 03/06/2016 05:27:21completed at : 03/06/2016 05:2

18、7:24schema name: tpccsql id: 4zbqykx89yc8vsql text : select t1.*,t2.owner from t1,t2 where t1.object_name like %t1% and t1.object_id=t2.object_idfindings section (1 finding)1- sql profile finding (see explain plans section below) a potentially better execution plan was found for this statement.recom

19、mendation (estimated benefit: 83.08%) -consider accepting the recommended sql profile.execute dbms_sqltune.accept_sql_profile(task_name = task_114, task_owner = tpcc, replace = true);validation resultsthe sql profile was tested by executing both its plan and the original plan and measuring their res

20、pective execution statistics. a plan may have been only partially executed if the other could be run to completion in less time.completion status:completecompleteelapsed time (s):.012865.00455664.58 %cpu time (s):.0124.004563.7 %user i/o time (s):00buffer gets:178730283.1 %physical read requests:00p

21、hysical write requests:00physical read bytes:00physical write bytes:00rows processed:4242fetches:4242executions:11notes1. statistics for the original plan were averaged over 10 executions.2. statistics for the sql profile plan were averaged over 10 executions.explain plans section1- original with ad

22、justed costplan hash value: 1838229974| id | operation| name | rows | bytes | cost (%cpu)| time|0 | select statement |hash join|42 |table access full| t1 |42 |1680 |1680 |42 |table access full| t2 | 92021 |4981218 |988k|498(1)| 00:00:01 |(1)| 00:00:01 |68430(0)| 00:00:01 |(1)| 00:00:01 |predicate in

23、formation (identified by operation id):1 - access(t1.object_id=t2.object_id)2 - filter(t1.object_name like %t1% and t1.object_name is not null)2- using sql profileplan hash value: 1022743391| id | operation| name | rows | bytes | cost (%cpu)| time |0 |select statement|42 |1680 |152 (0)| 00:00:01 |1

24、|nested loops|42 |1680 |152 (0)| 00:00:01 |2 |nested loops|42 |1680 |152 (0)| 00:00:01 |*3 |table access full| t1|42 |1218 |68 (0)| 00:00:01 |*4 |index range scan| t2_idx |1 |1 (0)| 00:00:01 |5 |table access by index rowid| t2|1 |11 |2(0)| 00:00:01predicate information (identified by operation id):3

25、 - filter(t1.object_name like %t1% and t1.object_name is not null)4 - access(t1.object_id=t2.object_id)接受分析建议报告中给出了执行方法，如上红色部分接受报告的建议，验证一下如下：tpcctoaddb execute dbms_sqltune.accept_sql_profile(task_name = task_114,task_owner = tpcc, replace = true);pl/sql procedure successfully completed.执行测试再执行原先命令如

26、下：tpcctoaddb select t1.*,t2.owner from t1,t2 where t1.object_name like %t1% and t1.object_id=t2.object_id;42 rows selected.execution planplan hash value: 1022743391| id | operation| name | rows | bytes | cost (%cpu)| time1 |nested loops|42 |1680 |2 |nested loops|42 |1680 |3 |table access full| t1|42 |1218 |4 |index range scan| t2_idx |1 |5 |table access by index rowid| t2 |1|110 | select statement |152 (0)| 00:00:01 |152 (0)| 00:00:01 |68 (0)| 00:00:01 |1 (0)| 00:00:01 |2(0)| 00:00:01|42 |1680 |152 (0)| 00:00:01 |predicate information (identified by operation id):3 - filter