sql_tune advisor的使用

最新推荐文章于 2021-04-09 09:06:44 发布
最新推荐文章于 2021-04-09 09:06:44 发布
阅读量1k 收藏
点赞数
分类专栏： oracle 调优
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/kiwi_kid/article/details/44657319
版权 
我们现在创建一个测试表，看此时正确的执行计划
13:11:53 scott@orcl> select * from t2 where empno=200;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2008370210

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    39 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    39 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T2 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=200)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1092  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

我们使用hint来强制走一个错误的执行计划
13:11:58 scott@orcl> select /*+ full(t2) */ * from t2 where empno=200;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    39 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   |     1 |    39 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"=200)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         47  consistent gets
          0  physical reads
          0  redo size
       1088  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
现在我们使用sqltune advisor来进行调整
创建TUNING_TASK并执行
declare
  l_task_name varchar2(30);
  l_sql       clob;
begin
  l_sql       := 'select /*+ full(t2) */ * from t2 where empno=200';
  l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text    => l_sql,
                                                 user_name   => 'SCOTT',
                                                 scope       => 'COMPREHENSIVE',
                                                 time_limit  => 60,
                                                 task_name   => 'test01',
                                                 description => null);
end;
/

time_limit：执行的最长时间，默认是60。
scope：
LIMITED，用大概1秒时间去优化SQL语句，但是并不进行SQL Profiling分析。
COMPREHENSIVE，进行全面分析，包含SQL Profiling分析；比LIMITED用时更长。

**也可以用sql_id创建sql tunning任务，比用sql_text方便很多
FUNCTION CREATE_TUNING_TASK RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN
 PLAN_HASH_VALUE                NUMBER                  IN     DEFAULT
 SCOPE                          VARCHAR2                IN     DEFAULT
 TIME_LIMIT                     NUMBER                  IN     DEFAULT
 TASK_NAME                      VARCHAR2                IN     DEFAULT
 DESCRIPTION                    VARCHAR2                IN     DEFAULT

DECLARE
 my_task_name VARCHAR2(30);
BEGIN
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
         SQL_ID      => 'ddw7j6yfnw0vz',
         scope       => 'COMPREHENSIVE',
         time_limit  => 60,
         task_name   => 'tunning_task_ddw7j6yfnw0vz', 
         description => 'Task to tune a query on  ddw7j6yfnw0vz');
END;
/ 

我们查看此时任务的状态
13:27:53 scott@orcl> select task_name,EXECUTION_START,EXECUTION_END,STATUS from DBA_ADVISOR_LOG where task_name like 'test%';

TASK_NAME                      EXECUTION_START     EXECUTION_END       STATUS
------------------------------ ------------------- ------------------- -----------
test01                                                                 INITIAL

执行sql tuning任务
BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'test01' );
END;
/


展示sql tunning结果
SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('test01')
FROM   DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST01')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : test01
Tuning Task Owner  : SCOTT
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 12/21/2014 13:29:11
Completed at       : 12/21/2014 13:29:15

-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID     : 3bgc9fc2fp597
SQL Text   : select /*+ full(t2) */ * from t2 where empno=200

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 93.46%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'test01', task_owner
            => 'SCOTT', 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:            COMPLETE          COMPLETE
  Elapsed Time (s):             .000378           .000098      74.07 %
  CPU Time (s):                 .000299           .000099      66.88 %
  User I/O Time (s):                  0                 0
  Buffer Gets:                       46                 3      93.47 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                     1                 1
  Fetches:                            1                 1
  Executions:                         1                 1

  Notes
  -----
  1. Statistics for the original plan were averaged over 10 executions.
  2. Statistics for the SQL profile plan were averaged over 10 executions.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    39 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   |     1 |    39 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"=200)

2- Using SQL Profile
--------------------
Plan hash value: 2008370210

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    39 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    39 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T2 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=200)

-------------------------------------------------------------------------------

我们可以看到他提供的建议，执行sql_profile，我们根据他的建议执行这个profile
execute dbms_sqltune.accept_sql_profile(task_name => 'test01',task_owner=> 'SCOTT',replace => TRUE);

然后我们再来执行下原来的带hint的语句
select /*+ full(t2) */ * from t2 where empno=200;
13:39:32 scott@orcl> select /*+ full(t2) */ * from t2 where empno=200;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2008370210

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    39 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    39 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T2 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=200)

Note
-----
   - SQL profile "SYS_SQLPROF_014a6b5a4a4a0000" used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1092  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
可以看到使用了sql_profile 从而走了正确的执行计划

现在我们再来看看其他的情况，我们原来的表上没有索引，看看tune advisor能提供什么样的建议
13:42:44 scott@orcl> select * from t4 where empno=200;

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 2560505625

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   100 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T4   |     1 |   100 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"=200)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         14  recursive calls
          0  db block gets
        114  consistent gets
         50  physical reads
          0  redo size
       1088  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
此时表是没有索引的，走的全表扫描
我们使用sql_id的方式来创建task
13:45:41 scott@orcl> select sql_text,sql_id from v$sql where sql_text like 'select * from t4%';

SQL_TEXT                                                     SQL_ID
------------------------------------------------------------ -------------
select * from t4 where empno=200                             5avs113b5fn8v

DECLARE
 my_task_name VARCHAR2(30);
BEGIN
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
         SQL_ID      => '5avs113b5fn8v',
         scope       => 'COMPREHENSIVE',
         time_limit  => 60,
         task_name   => 'tunning_task_5avs113b5fn8v', 
         description => 'Task to tune a query on  5avs113b5fn8v');
END;
/ 

启动这个task
BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tunning_task_5avs113b5fn8v' );
END;
/

查看report
SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tunning_task_5avs113b5fn8v')
FROM   DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNNING_TASK_5AVS113B5FN8V')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : tunning_task_5avs113b5fn8v
Tuning Task Owner  : SCOTT
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 12/21/2014 13:48:02
Completed at       : 12/21/2014 13:48:03

-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID     : 5avs113b5fn8v
SQL Text   : select * from t4 where empno=200

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  Table "SCOTT"."T4" was not analyzed.

  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
            'T4', 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: 86.7%)
  -----------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index SCOTT.IDX$$_00540001 on SCOTT.T4("EMPNO");

  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: 2560505625

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   100 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T4   |     1 |   100 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"=200)

2- Using New Indices
--------------------
Plan hash value: 3508715929

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |   100 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T4             |     1 |   100 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX$$_00540001 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=200)

-------------------------------------------------------------------------------
可以看到 sql_tune advisor提供了建议在empno 列上面创建索引，可见分析的还是很准确的


删除tune_tast
EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('test01');

其他
--sql tunning任务创建后，也可以修改参数
BEGIN
  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
    task_name => 'test_sql_tuning',
    parameter => 'TIME_LIMIT', value => 300);
END;
/

--查看SQL Tuning Advisor的进展（task执行很久）
col opname for a20
col ADVISOR_NAME for a20
SELECT SID,SERIAL#,USERNAME,OPNAME,ADVISOR_NAME,TARGET_DESC,START_TIME SOFAR, TOTALWORK 
FROM   V$ADVISOR_PROGRESS 
WHERE  USERNAME = 'TEST';

bigclouder
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL Tuning Advisor使用总结
03-14
关于oracle的SQL Tuning Advisor全面的使用介绍，包括分析awr、cursor、sql_set、sql文本中获取sql并且建立、执行、获取结果的方法。并且介绍了sql_set，sql_profile的操作。里面涉及到的内容都有可执行的代码。
使用SQL Profile进行SQL优化案例
12-14
2. **生成SQL Profile**：使用`DBMS_SQLTUNE.REPORT_ADVICE`或`DBMS_ADVISOR.GENERATE_ADVICE`过程，让数据库自动生成优化建议。这会根据实际运行情况生成一个针对该SQL的Profile。 3. **应用SQL Profile**：一旦...
SQL Tuning Advisor使用实例
02-01 155
在oracle10g之前，想要优化一个sql语句是比较麻烦，但是在oracle10g这个版本推出的SQL Tuning Advisor这个工具，能大大减少sql调优的工作量，不过要想使用SQL Tuning Advisor，一定...
SQL Tuning Advisor使用
ctoh1352的博客
06-06 108
在oracle10g之前，想要优化一个sql语句是比较麻烦，但是在oracle10g这个版本推出的SQL Tuning Advisor这个工具，能大大减少sql调优的工作量，不过要想使用SQL Tuning Advi...
使用SQL Tuning Advisor(STA)为sql提供优化建议
雅冰石的专栏
05-22 1164
在Oracle10g之前，优化SQL是个比较费力的技术活，不停的分析执行计划，加hint，分析统计信息等等。 在10g中，Oracle推出了自己的SQL优化辅助工具:SQL优化器(SQLTuningAdvisor：STA)，它可以给出优化建议。 使用STA一定要保证优化器是CBO模式下。 一 创建优化任务 可以通过传入文本来获取相应的优化建议，也可以通过sql_id方式获取。 1.1 传入文本方式 DECLARE my_task_nameVARCHAR2(30); ...
Sql Tuning Advisor 使用方法
cpda53349199的博客
06-22 268
Sql Tuning Advisor 1.使用步骤： ?创建优化任务 ?执行优化任务 ?查看建议 ?确认建议（实施建议） 2.使用案例： 2.1 ...
sql turning advise的使用
06-12
2. **用户权限**：执行DBMS_SQLTUNE包中的SQL优化功能需要具有advisor权限。可以通过`grant advisor to 用户名;`命令授予相应用户该权限。 #### 四、STA的具体使用示例 ##### 1. 创建案例用户及授权 首先，需要...
Oracle_Database_10g_SQL_Tuning
10-12
创建SQL Profile的方法包括使用`DBMS_SQLTUNE`包或通过SQL Access Advisor。 #### 5. SQL Access Advisor SQL Access Advisor是Oracle 10g提供的一种工具，用于建议如何改进SQL语句的性能。它可以识别出性能不佳的...
Oracle SQL自动调优生成报告和SQL语句的脚本
04-14
通过运行`DBMS_SQLTUNE`包中的`CREATE_SQL_PROFILE`过程，可以为问题SQL生成Profile。 3. **SQL Tuning Advisor**：Oracle提供了一个强大的工具——SQL Tuning Advisor，它可以分析SQL语句并提出改进建议，如创建...
SQL Tuning Advisor简介
473687880
10-23 348
1. 简介 一直以来，如何有效的诊断和监控高负载的SQL对于DBA来说不是件容易的事情.但是从Oracle 10G 开始,ADDM的引入使得由数据库自身参与监控成为可能.尽管通过ADDM发现出的高负载的SQL可能只会占全部SQL的很小一部分比例,但是如何对这些语句进行调优依然需要经验的积累. 2. 手工调优SQL的挑战 对SQL语句手工调优需要很多的经验和技巧,例如: . 对SQL语句...
使用ORACLE SQL Tuning advisor快速优化低效的SQL语句
12-19
使用ORACLE SQL Tuning advisor快速优化低效的SQL语句, 这里是一个使用范例，有9个步骤， 后面 其他语句，都按照这个步骤来做就可以了。
参考SQL Tunning Adviser进行SQL优化
congjiaqing0635的博客
07-14 165
无论前面介绍的通过“手工”分析手段或通过“工具”分析手段，很多时候我们很有可能面临TOP SQL语句的优化，即SQL优化。很多情况下可以说数据库的优化就是SQL的优化，而SQL的优化呢？那就是的索引的优化！ Or...
ORACLE SQL Tuning Advisor
weixin_42955647的博客
01-20 680
前言：一直以来SQL调优都是DBA比较费力的技术活，而且很多DBA如果没有从事过开发的工作，那么调优更是一项头疼的工作，即使是SQL调优很厉害的高手，在SQL调优的过程中也要不停的分析执行计划、加HINT、分析统计信息等等。从ORACLE 10G开始，数据库采取了很多智能化的管理工作，其中SQL优化器(SQL Tuning Advisor：STA)，大大的提高了DBA进行SQL优化的效率； 准备测试环境（创建两个表并插入数据） create table big_tab as select rownum as
SQL优化篇--SQL TUNNING ADVICER使用
fengsongtao123的博客
09-06 1067
–STA调优（SQL文本） DECLARE my_task_name VARCHAR2(30); my_sqltext CLOB; BEGIN my_sqltext := ‘select * from CUST_OB where object_name=’‘I_CDEF2’’’; my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text...
SQL：addm+sql tuning advisor+sql profile
u010719917的专栏
07-23 377
-----------------------------addmrpt+tunning +sql advisor+sql profile--------------------------------- 一、生成addmrpt找出有问题的sql_id SQLPLUS>@?/rdbms/addmrpt.sql sql_id=6v7msj6q9ujrz 二、SQL Tuning Ad
Oracle10g SQL tune adviser简单介绍【转自ITPUB zhouwf0726】
weixin_34190136的博客
07-17 74
Oracle10g SQL tune adviser Oracle10g SQL tune adviser简单介绍 本文简单介绍下SQL Tuning Adviser的配置使用方法和一些相关知识点，如果了解SQL Tuning Adviser详细信息，参看Oracle联机文档。本文对分析结果没有详细分析。一、自动SQL Tuning简单介绍：1、优化模式： ...
sql tune
bjchangxiao的专栏
04-07 313
 具体包的使用可参照Oracle® Database PLSQL Packages and Types Reference 12c Release 1(12.1) 准备数据： CREATE USER OPT IDENTIFIED BY 1;--新建个用户 GRANT DBA TO OPT;                            --权限无所谓 DROP TABLE
sql tunning advisor的使用
babymouse1212的博客
05-02 414
原文：http://space.itpub.net/184303/viewspace-700658   sql tunning advisor 使用的主要步骤:   1 建立tunning task   2 执行task   3 显示tunning 结果   4 根据建议来运行相应的调优方法   下面来按照这个顺序来实施一遍:    1  建立测试表以及索引 SQL> CREATE TABLE t
oracle sqltune,关于Oracle Baseline和DBMS_SQLTUNE工具
weixin_39772420的博客
04-09 263
前段时间生产系统出现过一个问题。一个报表突然出现运行失败的情况，检查发现报表在运行的时候不停的消耗TEMP表空间，导致TEMP表空间不足然后SQL失败报错。这个SQL平时都是在20分钟之内就跑完的，而且也基本不会占用TEMP表空间，单独拿出来给相应的变量赋值运行也正常。另外，其他的SQL和数据库都正常说明这个SQL的执行计划发生了变化，导致SQL结果跑不出来。原因且不查，先考虑怎么解决。数据库环境...
coe_xfr_sql_profile
最新发布
07-28
回答: 根据引用\[1\]，"coe_xfr_sql_profile"是一个SQL profile的名称，它已经被绑定到了一个执行计划上。根据引用\[2\]，如果想要删除原来的SQL profile，可以使用"dbms_sqltune.drop_sql_profile"过程。至于如何手工创建SQL Profiles，可以使用"DBMS_SQLTUNE.IMPORT_SQL_PROFILE"过程，根据引用\[3\]的描述。 #### 引用[.reference_title] - *1* [【oracle】固定执行计划-使用coe_xfr_sql_profile](https://blog.csdn.net/viviliving/article/details/103086564)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^koosearch_v1,239^v3^insert_chatgpt"}} ] [.reference_item] - *2* *3* [oracle固定执行计划2-sql profile](https://blog.csdn.net/fanzhuozhuo/article/details/114400888)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^koosearch_v1,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值