When profiling a SQL statement, SQL Tuning Advisor uses a specific set of bind values as input, and then compares the optimizer estimate with values obtained by executing fragments of the statement on a data sample. When significant variances are found, SQL Tuning Advisor bundles corrective actions together in a SQL profile, and then recommends its acceptance.
The corrected statistics in a SQL profile can improve optimizer cardinality estimates, which in turn leads the optimizer to select better plans. SQL profiles provide the following benefits over other techniques for improving plans:
当在分析一个sql 语句的时候，sql优化建议器使用一个传入的明确值，然后比较 优化器通过在数据样本里面计算的出来的值
Unlike hints and stored outlines, SQL profiles do not tie the optimizer to a specific plan or subplan. SQL profiles fix incorrect estimates while giving the optimizer the flexibility to pick the best plan in different situations.
与使用hints 和stored outlines 不同的是 sql profile 不绑定一个明确的plan 或者subplan给优化器，sql profile 修正一个不正确的估值给优化器在不用的情况下选择最好的执行计划。
Unlike hints, no changes to application source code are necessary when using SQL profiles. The use of SQL profiles by the database is transparent to the user.
与hints 不同 ，在使用sql profile 的时候不需要修改程序源代码。sql profile 的使用对用户来说是透明的。
A SQL profile is a collection of auxiliary statistics on a query, including all tables and columns referenced in the query. The profile stores this information in the data dictionary. The optimizer uses this information at optimization time to determine the correct plan.
sql profile 是在执行sql 查询的时候提供的辅助统计信息，包括在sql 查询中使用到的表的所有的列的信息。 sql profile 将这些信息保存在数据字典中。
Note:
The SQL profile contains supplemental statistics for the entire statement , not individual plans . The profile does not itself determine a specific plan.A SQL profile contains, among other statistics, a set of cardinality adjustments. The cardinality measure is based on sampling the WHERE clause rather than on statistical projection. A profile uses parts of the query to determine whether the estimated cardinalities are close to the actual cardinalities and, if a mismatch exists, uses the corrected cardinalities. For example, if a SQL profile exists for SELECT * FROM t WHERE x=5 AND y=10, then the profile stores the actual number of rows returned.
When choosing plans, the optimizer has the following sources of information:
The environment, which contains the database configuration, bind variable values, optimizer statistics, data set, and so on
sql profile 包含其他统计，基数调整，这个基数，确保了他是基于where 条件而不是统计投影。一个profile 使用一部分查询决定计算的基数和真实的基数相近， 如果存在误差，使用正确的基数。例如如果一个sql SELECT * FROM t WHERE x=5 AND y=10
sql profile 会保存返回行的数量 在选择执行计划，优化器的时候会基于上诉信息：
包括数据库配置，绑定变量，优化统计器，数据集 等等。
The supplemental statistics in the SQL profile
Figure 22-1 shows the relationship between a SQL statement and the SQL profile for this statement. The optimizer uses the SQL profile and the environment to generate an execution plan. In this example, the plan is in the SQL plan baseline for the statement.
图22-1 列出了profile 和sql 语句之间的关系，优化器使用sql profile 和环境产生一个执行计划，在下例中执行计划存放在执行计划基线里面。
either the optimizer environment or SQL profile change, then the optimizer can create a new plan. As tables grow, or as indexes are created or dropped, the plan for a SQL profile can change. The profile continues to be relevant even if the data distribution or access path of the corresponding statement changes. In general, you do not need to refresh SQL profiles.
Over time, profile content can become outdated. In this case, performance of the SQL statement may degrade. The statement may appear as high-load or top SQL. In this case, the Automatic SQL Tuning task again captures the statement as high-load SQL. You can implement a new SQL profile for the statement.
优化器环境或者sqlprofile 发生了变化 优化器能够重新创建一个执行计划。 随着表的增长，或者所有的创建或者删除， sqlprofile 里的执行计划会发生改变， 当数据分布发生变化或者对于的语句访问路径发生变化，sqlprofile 仍然继续提供相关服务， 一般来说你并不需要重新创建sql profile。
随着时间推移，profile 会变的过时 这种情况下 使用了sqlprofile 的sql 语句性能会下降。sql 语句很可能成为一个高负载或者top sql。
在这种情况下，sql 自动调优任务会再次捕获这个高负载sql 你需要重新创建一个sql profile
Internally, a SQL profile is implemented using hints that address different types of problems. These hints do not specify any particular plan. Rather, the hints correct errors in the optimizer estimation algorithm that lead to suboptimal plans. For example, a profile may use theTABLE_STATS hint to set object statistics for tables when the statistics are missing or stale.
本质上，sqlprofile 是使用hint 解决的不同情况下的问题。
As explained in "SQL Profiling", SQL Tuning Advisor invokes Automatic Tuning Optimizer to generate SQL profile recommendations. Recommendations to implement SQL profiles occur in a finding, which appears in a separate section of the SQL Tuning Advisor report.
就像在sqlprofile 里面解释的 sql 优化建议器会自动产生sqlprofile 建议。 在发现一个新的sqlprofile 变更的时候会会提示你创建sqlprofile。 这些都会在sql 调优建议报告里面体现出来。
When you implement (or accept) a SQL profile, the database creates the profile and stores it persistently in the data dictionary. However, the SQL profile information is not exposed through regular dictionary views.
当你在实施一个sqlproffile 操作的时候，数据创建profile 并保存固定在数据字典里面。但是sqlprofile 信息不会在数据字典中显示出来。
Example 22-1 SQL Profile Recommendation （SQL PROFILE 建议）
In this example, the database found a better plan for a SELECT statement that uses several expensive joins. The database recommends running DBMS_SQLTUNE.ACCEPT_SQL_PROFILE to implement the profile, which enables the statement to run 98.53% faster.
在下面的举例中，数据库发现对于使用消耗比较大的joins 的语句有更好的执行计划。 数据库建议运行DBMS_SQLTUNE.ACCEPT_SQL_PROFILE 来实施profile 这将会提高98% 的效率
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement. Choose
one of the following SQL profiles to implement.
Recommendation (estimated benefit: 99.45%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'my_task',
object_id => 3, task_owner => 'SH', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: PARTIAL COMPLETE
Elapsed Time(us): 15467783 226902 98.53 %
CPU Time(us): 15336668 226965 98.52 %
User I/O Time(us): 0 0
Buffer Gets: 3375243 18227 99.45 %
Disk Reads: 0 0
Direct Writes: 0 0
Rows Processed: 0 109
Fetches: 0 109
Executions: 0 1
Notes
-----
1. The SQL profile plan was first executed to warm the buffer cache.
2. Statistics for the SQL profile plan were averaged over next 3 executions.
Sometimes SQL Tuning Advisor may recommend implementing a profile that uses the Automatic Degree of Parallelism (Auto DOP) feature. A parallel query profile is only recommended when the original plan is serial and when parallel execution can significantly reduce the elapsed time for a long-running query.
有时sql优化建议器可能会建议你在profile使用Automatic Degree of Parallelism 功能。
When it recommends a profile that uses Auto DOP, SQL Tuning Advisor gives details about the performance overhead of using parallel execution for the SQL statement in the report. For parallel execution recommendations, SQL Tuning Advisor may provide two SQL profile recommendations, one using serial execution and one using parallel.
当在profile 使用了Auto DOP，sql 优化建议器会针对使用了Auto DOP功能的sql 提供详细的性能负载。
The following example shows a parallel query recommendation. In this example, a degree of parallelism of 7 improves response time significantly at the cost of increasing resource consumption by almost 25%. You must decide whether the reduction in database throughput is worth the increase in response time.
在一个并行度为7的语句中资源消耗提高了将近25%，你需要考虑清楚在消耗资源和响应时间上做出选择。
下面的例子 展示了一个并行查询建议，在例中
Recommendation (estimated benefit: 99.99%)
------------------------------------------
- Consider accepting the recommended SQL profile to use parallel execution
for this statement.
execute dbms_sqltune.accept_sql_profile(task_name => 'gfk_task',
object_id => 3, task_owner => 'SH', replace => TRUE,
profile_type => DBMS_SQLTUNE.PX_PROFILE);
Executing this query parallel with DOP 7 will improve its response time
82.22% over the SQL profile plan. However, there is some cost in enabling
parallel execution. It will increase the statement's resource consumption by
an estimated 24.43% which may result in a reduction of system throughput.
Also, because these resources are consumed over a much smaller duration, the
response time of concurrent statements might be negatively impacted if
sufficient hardware capacity is not available.
The following data shows some sampled statistics for this SQL from the past
week and projected weekly values when parallel execution is enabled.
Past week sampled statistics for this SQL
-----------------------------------------
Number of executions 0
Percent of total activity .29
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec) 76.51
Projected statistics with Parallel Execution
--------------------------------------------
Weekly DB time (in sec) 95.21
You can use SQL profiles with or without SQL plan management. No strict relationship exists between the SQL profile and the plan baseline. If a statement has multiple plans in a SQL plan baseline, then a SQL profile is useful because it enables the optimizer to choose the lowest-cost plan in the baseline.
在使用sqlprofile 的 时候 你可以使用也可以不使用那个sql plan 来管理。
sql profile 和plan baseline 之间关系没有严格上的要求。，如果一个语句在 sql plan 的基线里面有多个执行计划， 那么sql profile 就比较有效 ，因为他会选者消耗最小的执行计划基线。
Oracle Enterprise Manager Cloud Control (Cloud Control) usually handles SQL profiles as part of automatic SQL tuning.
On the command line, you can manage SQL profiles with the DBMS_SQLTUNE package. To use the APIs, you must have the ADMINISTER SQL MANAGEMENT OBJECT privilege.
1、OEM 能够将sql profile 当作自动sql 调优里面的一项任务。
2、在命令行你可以通过 DBMS_SQLTUNE 包来管理sql profile 要使用 APIS 你必须拥有ADMINISTER SQL MANAGEMENT OBJECT 的权限
This section explains the basic tasks involved in managing SQL profiles. Figure 22-2 shows the basic workflow for implementing, altering, and dropping SQL profiles.
这节介绍了在管理sql profile 中的基本任务。 图22-2 展示了 实施，修改，删除sql profile 的基本流程。
Figure 22-2 Managing SQL Profiles
Typically, you manage SQL profiles in the following sequence:
通常你管理sql profi 需要以下四步：
Implement a recommended SQL profile.
"Implementing a SQL Profile" describes this task.
实施sql profile
Obtain information about SQL profiles stored in the database.
"Listing SQL Profiles" describes this task.
获取数据库中sql profile 存放的信息
Optionally, modify the implemented SQL profile.
"Altering a SQL Profile" describes this task.
对存在的sqlprofile 进行修改
Drop the implemented SQL profile when it is no longer needed.
"Dropping a SQL Profile" describes this task.
删除一个不再需要的sql profile
Implementing (also known as accepting) a SQL profile means storing it persistently in the database. A profile must be implemented before the optimizer can use it as input when generating plans.
实施一个sqlprofile 意味着将它固定在数据中 profile 必须在计划产生之前实施。
As a rule of thumb, implement a SQL profile recommended by SQL Tuning Advisor. If the database recommends both an index and a SQL profile, then either use both or use the SQL profile only. If you create an index, then the optimizer may need the profile to pick the new index.
根据经验，通过 sql 优化建议实施的sqlprofile 如果数据库对索引和sqlprofile 都使用，那么两个都将生效，或者值使用sql profile。
如果你创建了一个索引，优化器可能需要profile 选择新的索引。
In some situations, SQL Tuning Advisor may find an improved serial plan in addition to an even better parallel plan. In this case, the advisor recommends both a standard and a parallel SQL profile, enabling you to choose between the best serial and best parallel plan for the statement. Implement a parallel plan only if the increase in response time is worth the decrease in throughput.
在某些情况下，除了更好的并行计划，sql 优化顾问会找出改进的串行计划。对于此，优化顾问针对表准的和并行的都可接受，使得你能够为sql 语句选择最好的串行或者最好的并行计划
To implement a SQL profile, execute the DBMS_SQLTUNE.ACCEPT_SQL_PROFILE procedure. Some important parameters are as follows:
通过执行 DBMS_SQLTUNE.ACCEPT_SQL_PROFILE 过程来实施sql profile 其中有一些重要的参数需要设置：
profile_type
Set this parameter to REGULAR_PROFILE for a SQL profile without a change to parallel execution, or PX_PROFLE for a SQL profile with a change to parallel execution.
force_match
This parameter controls statement matching. Typically, an accepted SQL profile is associated with the SQL statement through a SQL signature that is generated using a hash function. This hash function changes the SQL statement to upper case and removes all extra whites spaces before generating the signature. Thus, the same SQL profile works for all SQL statements in which the only difference is case and white spaces.
force_match 参数控制语句匹配。典型的，一个sql 通过hash 算法产生一个hash 值和sql profile 关联。这个hash函数将sql语句转化为大写，并且在给语句标记钱将先前所有的whites spaces 删除。 这样相同的sql profile 为所有的 大小写不同和white spaces. 的语句提供 服务。
By setting force_match to true, the SQL profile additionally targets all SQL statements that have the same text after the literal values in the WHERE clause have been replaced by bind variables. This setting may be useful for applications that use only literal values because it enables SQL with text differing only in its literal values to share a SQL profile. If both literal values and bind variables are in the SQL text, or if force_match is set to false (default), then the literal values in the WHERE clause are not replaced by bind variables.
将 force_match 设置为true sql profile 将所有where 条件相同使用了绑定变量的sql 语句都起作用。设置为 false （默认） where 条件后面的将视为不同。
This section shows how to use the ACCEPT_SQL_PROFILE procedure to implement a SQL profile.
Assumptions
This tutorial assumes the following:
The SQL Tuning Advisor task STA_SPECIFIC_EMP_TASK includes a recommendation to create a SQL profile.
The name of the SQL profile is my_sql_profile.
The PL/SQL block accepts a profile that uses parallel execution (profile_type).
The profile uses force matching.
To implement a SQL profile:
Connect SQL*Plus to the database with the appropriate privileges, and then execute the ACCEPT_SQL_PROFILE function.
For example, execute the following PL/SQL:
DECLARE
my_sqlprofile_name VARCHAR2(30);
BEGIN
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => 'STA_SPECIFIC_EMP_TASK'
, name => 'my_sql_profile'
, profile_type => DBMS_SQLTUNE.PX_PROFILE
, force_match => true
);
END;
/
The data dictionary view DBA_SQL_PROFILES stores SQL profiles persistently in the database. The statistics are in an Oracle internal format, so you cannot query profiles directly. However, you can list profiles.
To list SQL profiles:
Connect SQL*Plus to the database with the appropriate privileges, and then query the DBA_SQL_PROFILES view.
For example, execute the following query:
COLUMN category FORMAT a10 COLUMN sql_text FORMAT a20 SELECT NAME, SQL_TEXT, CATEGORY, STATUS FROM DBA_SQL_PROFILES;
Sample output appears below:
NAME SQL_TEXT CATEGORY STATUS
------------------------------ -------------------- ---------- --------
SYS_SQLPROF_01285f6d18eb0000 select promo_name, c DEFAULT ENABLED
ount(*) c from promo
tions p, sales s whe
re s.promo_id = p.pr
omo_id and p.promo_c
ategory = 'internet'
group by p.promo_na
me order by c desc
You can alter attributes of an existing SQL profile using the attribute_name parameter of the ALTER_SQL_PROFILE procedure.
The CATEGORY attribute determines which sessions can apply a profile. View the CATEGORY attribute by queryingDBA_SQL_PROFILES.CATEGORY. By default, all profiles are in the DEFAULT category, which means that all sessions in which theSQLTUNE_CATEGORY initialization parameter is set to DEFAULT can use the profile.
By altering the category of a SQL profile, you determine which sessions are affected by profile creation. For example, by setting the category toDEV, only sessions in which the SQLTUNE_CATEGORY initialization parameter is set to DEV can use the profile. Other sessions do not have access to the SQL profile and execution plans for SQL statements are not impacted by the SQL profile. This technique enables you to test a profile in a restricted environment before making it available to other sessions.
The example in this section assumes that you want to change the category of the SQL profile so it is used only by sessions with the SQL profile category set to TEST, run the SQL statement, and then change the profile category back to DEFAULT.
To alter a SQL profile:
Connect SQL*Plus to the database with the appropriate privileges, and then use the ALTER_SQL_PROFILE procedure to set theattribute_name.
For example, execute the following code to set the attribute CATEGORY to TEST:
VARIABLE pname my_sql_profile BEGIN DBMS_SQLTUNE.ALTER_SQL_PROFILE ( name => :pname , attribute_name => 'CATEGORY' , value => 'TEST' ); END;
Change the initialization parameter setting in the current database session.
For example, execute the following SQL:
ALTER SESSION SET SQLTUNE_CATEGORY = 'TEST';
Test the profiled SQL statement.
Use the ALTER_SQL_PROFILE procedure to set the attribute_name.
For example, execute the following code to set the attribute CATEGORY to DEFAULT:
VARIABLE pname my_sql_profile
BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE (
name => :pname
, attribute_name => 'CATEGORY'
, value => 'DEFAULT'
);
END;
You can drop a SQL profile with the DROP_SQL_PROFILE procedure.
Assumptions
This section assumes the following:
You want to drop my_sql_profile.
You want to ignore errors raised if the name does not exist.
To drop a SQL profile:
Connect SQL*Plus to the database with the appropriate privileges, call the DBMS_SQLTUNE.DROP_SQL_PROFILE procedure.
The following example drops the profile named my_sql_profile:
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE (
name => 'my_sql_profile'
);
END;
/
You can transport SQL profiles. This operation involves exporting the SQL profile from the SYS schema in one database to a staging table, and then importing the SQL profile from the staging table into another database. You can transport a SQL profile to any Oracle database created in the same release or later.
Table 22-1 shows the main procedures and functions for managing SQL profiles.
Table 22-1 APIs for Transporting SQL Profiles
| Procedure or Function | Description |
|---|---|
| CREATE_STGTAB_SQLPROF | Creates the staging table used for copying SQL profiles from one system to another. |
| PACK_STGTAB_SQLPROF | Moves profile data out of the SYS schema into the staging table. |
| UNPACK_STGTAB_SQLPROF | Uses the profile data stored in the staging table to create profiles on this system. |
The following graphic shows the basic workflow of transporting SQL profiles:
Description of the illustration tgsql_vm_066.png
Assumptions
This tutorial assumes the following:
You want to transport my_profile from a production database to a test database.
You want to create the staging table in the dba1 schema.
To transport a SQL profile:
Connect SQL*Plus to the database with the appropriate privileges, and then use the CREATE_STGTAB_SQLPROF procedure to create a staging table to hold the SQL profiles.
The following example creates my_staging_table in the dba1 schema:
BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (
table_name => 'my_staging_table'
, schema_name => 'dba1'
);
END;
/
Use the PACK_STGTAB_SQLPROF procedure to export SQL profiles into the staging table.
The following example populates dba1.my_staging_table with the SQL profile my_profile:
BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (
profile_name => 'my_profile'
, staging_table_name => 'my_staging_table'
, staging_schema_owner => 'dba1'
);
END;
/
Move the staging table to the database where you plan to unpack the SQL profiles.
Move the table using your utility of choice. For example, use Oracle Data Pump or a database link.
On the database where you plan to import the SQL profiles, use UNPACK_STGTAB_SQLPROF to unpack SQL profiles from the staging table.
The following example shows how to unpack SQL profiles in the staging table:
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(
replace => true
, staging_table_name => 'my_staging_table'
);
END;
/
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26764973/viewspace-1450747/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/26764973/viewspace-1450747/
691
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
