189 8069 5689
今天就跟大家聊聊有关怎么进行ORACLE SQL PROFILE使用，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。
创新互联建站是一家集网站建设,镇坪企业网站建设,镇坪品牌网站建设,网站定制,镇坪网站建设报价,网络营销,网络优化,镇坪网站推广为一体的创新建站企业，帮助传统企业提升企业形象加强企业竞争力。可充分满足这一群体相比中小企业更为丰富、高端、多元的互联网需求。同时我们时刻保持专业、时尚、前沿，时刻以成就客户成长自我，坚持不断学习、思考、沉淀、净化自己，让我们为更多的企业打造出实用型网站。
sql profile 是从ORACLE 10G 才有的新特性，可以通过OEM和DBMS_SQLTUNE去管理。
automatic sql tuning:
从字面意思我们就能看出来是自动的来优化SQL 语句。 优化器可能因为缺少一些信息而生成效率不是很高的执行计划，这个时候可能需要我们手工干预如加HINTS让优化器作出正确的决定。但是对于打包的APPLICATION是不允许你修改CODE的，这个时候AUTOMATIC SQL TUNING 通过SQL PROFILE 就可以解决这个问题。首先需要对这个SQL STATEMENT 创建一个PROFILE。然后SQL PROFILE解决上面生成POOR EXECPLAN 通过收集额外的信息例如抽样，局部的执行技术。最后SQL PROFILE 会出一个REPORT.清楚的显示出来给出的建议。例如哪些字段需要建立INDEX,哪些TALBE 需要ANALYZE等等。
SQL PROFILE：收集的信息存放在数据字典里面。让优化器创建一个高效的执行计划。需要注意的是随着数据量的增加和INDEX的创建。可能我们固定的OUTLINE已经不适用啦。所以要过一个时间重新REGENERATE SQL PROFILE。
SQL PROFILE 有效范围：
select statements
update statements
insert statements(only with a select clause)
delete statements
create table statements(only with the as select clause)
merge statements(the update or insert operations)
declare
my_task_name varchar2(30);
MySQLtext clob;
begin
mysqltext:='select * from t where object_id=100';
my_task_name:=dbms_sqltune.create_tuning_task
(sql_text=>mysqltext,
user_name=>'SYSTEM',
scope=>'COMPREHENSIVE',
task_name=>'sql_tuning_test'
);
end;
/
整个过程：
conn system/admin
Connected.
SQL> create table t as select object_id,object_name from dba_objects;
Table created.
SQL> set autotrace on;
SQL> select * from t where object_id=100;
OBJECT_ID
----------
OBJECT_NAME
----------------------------------------------------------------------------------------------------
100
ORA$BASE
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 316 | 96 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 4 | 316 | 96 (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=100)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
68 recursive calls
0 db block gets
429 consistent gets
345 physical reads
0 redo size
497 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> declare
my_task_name varchar2(30);
mysqltext clob;
begin
mysqltext:='select * from t where object_id=100';
my_task_name:=dbms_sqltune.create_tuning_task
(sql_text=>mysqltext,
user_name=>'SYSTEM',
scope=>'COMPREHENSIVE',
task_name=>'sql_tuning_test'
);
end;
/ 2 3 4 5 6 7 8 9 10 11 12 13
PL/SQL procedure successfully completed.
SQL> exec dbms_sqltune.execute_tuning_task('sql_tuning_test');
PL/SQL procedure successfully completed.
SQL> SET LONG 999999
SQL> SET LINESIZE 100
SQL> set serveroutput on size 999999
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_test') FROM DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : sql_tuning_test
Tuning Task Owner : SYSTEM
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 04/10/2012 19:27:31
Completed at : 04/10/2012 19:27:32
-------------------------------------------------------------------------------
Schema Name: SYSTEM
SQL ID : 5314t67qk27hg
SQL Text : select * from t where object_id=100
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "SYSTEM"."T" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SYSTEM', tabname =>
'T', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt
=> 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 96.86%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index SYSTEM.IDX$$_003C0001 on SYSTEM.T("OBJECT_ID","OBJECT_NAME");
Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 316 | 96 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 4 | 316 | 96 (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=100)
2- Using New Indices
--------------------
Plan hash value: 2426277634
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 3 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX$$_003C0001 | 1 | 79 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"=100)
-------------------------------------------------------------------------------
Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
Statistics
----------------------------------------------------------
1596 recursive calls
970 db block gets
940 consistent gets
1 physical reads
572 redo size
22597 bytes sent via SQL*Net to client
14289 bytes received via SQL*Net from client
100 SQL*Net roundtrips to/from client
28 sorts (memory)
0 sorts (disk)
1 rows processed
执行分析：SQL> analyze table t compute statistics;
Table analyzed.
SQL> create index SYSTEM.IDX$$_003C0001 on SYSTEM.T("OBJECT_ID","OBJECT_NAME");
Index created.
删除任务：
exec dbms_sqltune.drop_tuning_task('sql_tuning_test');
重建任务，并执行，才能再次查看到新的报告。 这为记录是放在数据字典里的，所以只有这样。
看完上述内容，你们对怎么进行ORACLE SQL PROFILE使用有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注创新互联行业资讯频道，感谢大家的支持。