在本文中，我们将详细介绍使用hint优化Oracle的运行计划以及SQLTuneAdvisor的使用的各个方面，同时，我们也将为您带来关于Advised，Advisor，Advice，Pointcut

在本文中，我们将详细介绍使用hint优化Oracle的运行计划 以及 SQL Tune Advisor的使用的各个方面，同时，我们也将为您带来关于Advised，Advisor，Advice，Pointcut、ORACLE 11G 禁用 SQL TUNING ADVISOR、Oracle SQL Access Advisor 说明、Oracle SQL Developer unable to find Java Virtual Machine的有用知识。

• 使用hint优化Oracle的运行计划 以及 SQL Tune Advisor的使用
• Advised，Advisor，Advice，Pointcut
• ORACLE 11G 禁用 SQL TUNING ADVISOR
• Oracle SQL Access Advisor 说明
• Oracle SQL Developer unable to find Java Virtual Machine

使用hint优化Oracle的运行计划 以及 SQL Tune Advisor的使用

explain plan for 
select * from (
select ID id,RET_NO retNo, FROM_SYS fromSy, TO_SYS toSys, COMMAND_CODE commandCode, COMMAND, STATUS, 
EXT_CODE, ORIGN_CODE orignCode,error_message errorMessage, RE_F, RET_MSG retMsg 
from interface_table where ((command_code in(''AASSS'') 
			and  status in(''F'',''E'') and (re_f = ''N'') and FROM_SYS = ''MEE'')
			or (COMMAND_CODE in(''XXXX'',''XXXX9'') and FROM_SYS = ''EXT'' and RE_F = ''N'')
			) and MOD(id, 1) = 0  order by id) where rownum <= 100  ;

SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(''PLAN_TABLE''));

Plan hash value: 1871549687
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                    |    99 |   382K|   637   (1)| 00:00:08 |
|*  1 |  COUNT STOPKEY                |                    |       |       |            |          |
|   2 |   VIEW                        |                    |   100 |   386K|   637   (1)| 00:00:08 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| INTERFACE_TABLE    |   355 | 55735 |   637   (1)| 00:00:08 |
|*  4 |     INDEX FULL SCAN           | PK_INTERFACE_TABLE |  1439 |       |   280   (2)| 00:00:04 |
----------------------------------------------------------------------------------------------------

explain plan for 
select * from (
select /*+ index(INT_TABLE IX_INT_TABLE_2)*/ ID id,RET_NO retNo, FROM_SYS fromSy, TO_SYS toSys, COMMAND_CODE commandCode, COMMAND, STATUS, 
EXT_CODE, ORIGN_CODE orignCode,error_message errorMessage, RE_F, RET_MSG retMsg 
from interface_table where ((command_code in(''AASSS'') 
			and  status in(''F'',''E'') and (re_f = ''N'') and FROM_SYS = ''MEE'')
			or (COMMAND_CODE in(''XXXX'',''XXXX9'') and FROM_SYS = ''EXT'' and RE_F = ''N'')
			) and MOD(id, 1) = 0 order by id) where rownum <= 100  ;

SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(''PLAN_TABLE''));

Plan hash value: 3625182869
 
--------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                      |    99 |   382K| 19105   (1)| 00:03:50 |
|*  1 |  COUNT STOPKEY                  |                      |       |       |            |          |
|   2 |   VIEW                          |                      |   356 |  1376K| 19105   (1)| 00:03:50 |
|*  3 |    SORT ORDER BY STOPKEY        |                      |   356 | 55892 | 19105   (1)| 00:03:50 |
|   4 |     CONCATENATION               |                      |       |       |            |          |
|*  5 |      TABLE ACCESS BY INDEX ROWID| INTERFACE_TABLE      |    69 | 10833 |  9552   (1)| 00:01:55 |
|*  6 |       INDEX RANGE SCAN          | IX_INTERFACE_TABLE_2 | 77145 |       |    99   (0)| 00:00:02 |
|*  7 |      TABLE ACCESS BY INDEX ROWID| INTERFACE_TABLE      |   287 | 45059 |  9552   (1)| 00:01:55 |
|*  8 |       INDEX RANGE SCAN          | IX_INTERFACE_TABLE_2 | 77145 |       |    99   (0)| 00:00:02 |
--------------------------------------------------------------------------------------------------------

Advised，Advisor，Advice，Pointcut

Advised->在Spring中创建了AOP代理之后，就能够使用org.springframework.aop.framework.Advised接口对它们进行管理。 任何AOP代理都能够被转型为这个接口，不论它实现了哪些其它接口

Advisor->类似使用Aspect的@Aspect注解的类

Advice->@Before、@After、@AfterReturning、@AfterThrowing、@Around

Pointcut->@Pointcut

package com.enjoy.cap10.aop;

import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.AfterReturning;
import org.aspectj.lang.annotation.AfterThrowing;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;

//日志切面类
@Aspect
public class LogAspects {
   @Pointcut("execution(public int com.enjoy.cap10.aop.Calculator.*(..))")
   public void pointCut(){};
   
   //@before代表在目标方法执行前切入, 并指定在哪个方法前切入
   @Before("pointCut()")
   public void logStart(JoinPoint point){
      System.out.println("除法运行....参数列表是:{}");
   }
   @After("pointCut()")
   public void logEnd(){
      System.out.println("除法结束......");
      
   }
   @AfterReturning("pointCut()")
   public void logReturn(){
      System.out.println("除法正常返回......运行结果是:{}");
   }
   @AfterThrowing("pointCut()")
   public void logException(){
      System.out.println("运行异常......异常信息是:{}");
   }
   
   @Around("pointCut()")
   public Object Around(ProceedingJoinPoint proceedingJoinPoint) throws Throwable{
      System.out.println("@Arount:执行目标方法之前...");
      Object obj = proceedingJoinPoint.proceed();//相当于开始调div地
      System.out.println("@Arount:执行目标方法之后...");
      return obj;
   }
}

ORACLE 11G 禁用 SQL TUNING ADVISOR

生产上有一套11g数据库alert.log报错ORA-16957: sql Analyze time limit interrupt。

查询MOS相关文档Troubleshooting: ORA-16957: "sql Analyze time limit interrupt" Errors (文档 ID 1275248.1)

The ORA-16957 error is an internal error code used to indicate that sql Tuning Task has reached the time limit for tuning a specific sql.

The default time limit is 3600 seconds.

1. Check the current timing:

COLUMNparameter_valueFORMATA30
SELECTparameter_name,parameter_value
FROMdba_advisor_parameters
WHEREtask_name='SYS_AUTO_sql_TUNING_TASK'
ANDparameter_nameIN('TIME_LIMIT','DEFAULT_EXECUTION_TYPE','LOCAL_TIME_LIMIT');

Then,increase the time:

Using:

BEGIN
DBMS_sqlTUNE.SET_TUNING_TASK_ParaMETER(task_name=>'SYS_AUTO_sql_TUNING_TASK',parameter=>'TIME_LIMIT',value=>7200);
END;
/

意思是后台自动分析sql耗时超过了默认的时间限制3600s，需要使用DBMS_sqlTUNE.SET_TUNING_TASK_ParaMETER包增长时间限制。

一般在生产上不默认是不开启sql TUNING ADVISOR。可以使用下面代码关闭自动sql TUNING ADVISOR。

--查询当前任务状态
SYS@db2>selectclient_name,statusfromDBA_AUTOTASK_CLIENT;

CLIENT_NAMESTATUS
------------------------------------------------------------------------
autooptimizerstatscollectionENABLED
autospaceadvisorENABLED
sqltuningadvisorENABLED
--禁用sqltuningadvisor
SYS@db2>BEGIN
2dbms_auto_task_admin.disable(
3client_name=>'sqltuningadvisor',4operation=>NULL,5window_name=>NULL);
6END;
7/

PL/sqlproceduresuccessfullycompleted.
--再次查询状态
SYS@db2>selectclient_name,statusfromDBA_AUTOTASK_CLIENT;

CLIENT_NAMESTATUS
------------------------------------------------------------------------
autooptimizerstatscollectionENABLED
autospaceadvisorENABLED
sqltuningadvisordisABLED
--启用sqltuningadvisor
BEGIN
dbms_auto_task_admin.enable(
client_name=>'sqltuningadvisor',operation=>NULL,window_name=>NULL);
END;

参考文档：http://blog.chinaunix.net/uid-25528717-id-3172008.html

参考文档：http://www.cnblogs.com/suredandan/p/3200157.html

参考文档：http://blog.itpub.net/235507/viewspace-1137629/

官方文档：http://docs.oracle.com/cd/E11882_01/server.112/e25494/tasks.htm#ADMIN12332

Oracle SQL Access Advisor 说明

欢迎进入Oracle社区论坛，与200万技术人员互动交流 >>进入 --Task recommendation 是一个范围，从简单的建议到复杂的解决方案。当advisortask 执行时，SQL Access Advisor 会仔细分析收集数据和用户定义的参数。 1.3.4 Viewand implement the recommendation

欢迎进入oracle社区论坛，与200万技术人员互动交流 >>进入

    --Task recommendation 是一个范围，从简单的建议到复杂的解决方案。当advisortask 执行时，SQL Access Advisor 会仔细分析收集数据和用户定义的参数。

    1.3.4   Viewand implement the recommendations

    You can view therecommendations from SQL Access Advisor in either of the following ways:

    --可以使用如下2种方法来查看recommendation的内容：

    （1）Using thecatalog views

    （2）Generating ascript using the DBMS_ADVISOR.GET_TASK_SCRIPT procedure

    In EnterpriseManager, you may display the recommendations after SQL Access Advisor processhas completed. See "ViewingRecommendations" for a description of using the catalog views toview the recommendations. See "GeneratingSQL Scripts" to see how to create a script.

    --在OEM中，在SQL Access Advisor 进程处理完毕后会自动显示recommendation。

    You need notaccept all recommendations. You can mark the ones to be included in therecommendation script. However, when base table partitioning is recommended,some recommendations depend on others. For example, you cannot implement alocal index if you do not also implement the partitioning recommendation on theindex base table.

    The final stepis then implementing the recommendations and verifying that query performancehas improved.

    1.3.5 SQLAccess Advisor Repository

    All theinformation needed and generated by SQL Access Advisor resides in the Advisorrepository, which is a part of the database dictionary. The benefits of usingthe repository are that it:

    --Advisor 生成的所有信息都存放在Advisor repository中，其是数据字典的一部分，使用repository有如下好处：

    （1）    Collects a complete workloadfor SQL Access Advisor.

    （2）    Supports historical data.

    （3）    Is managed by the server.

    1.3.6 使用SQLAccess Advisor需要的权限

    You must have the ADVISOR privilege tomanage or use SQL Access Advisor. When processing a workload, SQL AccessAdvisor attempts to validate each statement to identify table and columnreferences. The database achieves validation by processing each statement as ifit were being executed by the statement''s original user.

    --必须需要有ADVISOR权限

    If the user doesnot have SELECT privileges to a particular table, then SQL AccessAdvisor bypasses the statement referencing the table. This behavior can causemany statements to be excluded from analysis. If SQL Access Advisor excludesall statements in a workload, then the workload is invalid. SQL Access Advisorreturns the following message:

    QSM-00774, thereare no SQL statements to process for task TASK_NAME

    --必须需要有指定表的select 的权限，否则会报QSM-774错误。

    To avoid missingcritical workload queries, the current database user must have SELECT privilegeson the tables targeted for materialized view analysis. For these tables, these SELECT privilegescannot be obtained through a role.

    Additionally,you must have the ADMINISTER SQL TUNING SET privilege to create andmanage workloads in SQL tuning set objects. To run the Advisor on SQL tuningsets owned by other users, you must have the ADMINISTER ANY SQL TUNING SET privilege.

    --还需要 ADMINISTER SQL TUNING SET的权限来创建和管理workload。

    二。手工生成SQLAccess Advisor 示例

    From:

    http://www.oracle-base.com/articles/10g/SQLAccessAdvisor10g.php

    2.1 DBMS_ADVISOR

    The DBMS_ADVISOR packagecan be used to create and execute any advisor tasks, including SQL AccessAdvisor tasks. The following example shows how it is used to create, executeand display a typical SQL Access Advisor script for the current workload.

    --DBMS_ADVISOR 包可以用来创建和执行advisor 任务。

    DECLARE

    l_taskname     VARCHAR2（30）  := ''test_sql_access_task'';

    l_task_desc    VARCHAR2（128）  := ''Test SQL Access Task'';

    l_wkld_name    VARCHAR2（30）   := ''test_work_load'';

    l_saved_rows   NUMBER         := 0;

    l_failed_rows  NUMBER         := 0;

    l_num_found    NUMBER;

    BEGIN

    -- Create an SQLAccess Advisor task.

    DBMS_ADVISOR.create_task （

    advisor_name => DBMS_ADVISOR.sqlaccess_advisor,

    task_name    => l_taskname,

    task_desc    => l_task_desc）；

    -- Reset the task.

    DBMS_ADVISOR.reset_task（task_name => l_taskname）；

    -- Create a workload.

    SELECT COUNT（*）

    INTO   l_num_found

    FROM   user_advisor_sqlw_sum

    WHERE  workload_name =l_wkld_name;

    IFl_num_found = 0 THEN

    DBMS_ADVISOR.create_sqlwkld（workload_name => l_wkld_name）；

    ENDIF;

    -- Link the workload to the task.

    SELECT count（*）

    INTO   l_num_found

    FROM   user_advisor_sqla_wk_map

    WHERE  task_name     = l_taskname

    AND    workload_name =l_wkld_name;

    IFl_num_found = 0 THEN

    DBMS_ADVISOR.add_sqlwkld_ref（

    task_name     => l_taskname,

    workload_name => l_wkld_name）；

    ENDIF;

    -- Set workload parameters.

    DBMS_ADVISOR.set_sqlwkld_parameter（l_wkld_name, ''ACTION_LIST'',DBMS_ADVISOR.ADVISOR_UNUSED）；

    DBMS_ADVISOR.set_sqlwkld_parameter（l_wkld_name,''MODULE_LIST'', DBMS_ADVISOR.ADVISOR_UNUSED）；

    DBMS_ADVISOR.set_sqlwkld_parameter（l_wkld_name, ''SQL_LIMIT'',DBMS_ADVISOR.ADVISOR_UNLIMITED）；

    DBMS_ADVISOR.set_sqlwkld_parameter（l_wkld_name, ''ORDER_LIST'', ''PRIORITY,OPTIMIZER_COST''）；

    DBMS_ADVISOR.set_sqlwkld_parameter（l_wkld_name, ''USERNAME_LIST'',DBMS_ADVISOR.ADVISOR_UNUSED）；

    DBMS_ADVISOR.set_sqlwkld_parameter（l_wkld_name, ''VALID_TABLE_LIST'',DBMS_ADVISOR.ADVISOR_UNUSED）；

    DBMS_ADVISOR.import_sqlwkld_sqlcache（l_wkld_name, ''REPLACE'', 2,l_saved_rows, l_failed_rows）；

    -- Set task parameters.

    DBMS_ADVISOR.set_task_parameter（l_taskname, ''_MARK_IMPLEMENTATION'',''FALSE''）；

    DBMS_ADVISOR.set_task_parameter（l_taskname, ''EXECUTION_TYPE'',''INDEX_ONLY''）；

    DBMS_ADVISOR.set_task_parameter（l_taskname, ''MODE'', ''COMPREHENSIVE''）；

    DBMS_ADVISOR.set_task_parameter（l_taskname, ''STORAGE_CHANGE'',DBMS_ADVISOR.ADVISOR_UNLIMITED）；

    DBMS_ADVISOR.set_task_parameter（l_taskname, ''DML_VOLATILITY'', ''TRUE''）；

    DBMS_ADVISOR.set_task_parameter（l_taskname, ''ORDER_LIST'',''PRIORITY,OPTIMIZER_COST''）；

    DBMS_ADVISOR.set_task_parameter（l_taskname, ''WORKLOAD_SCOPE'',''PARTIAL''）；

    DBMS_ADVISOR.set_task_parameter（l_taskname, ''DEF_INDEX_TABLESPACE'',DBMS_ADVISOR.ADVISOR_UNUSED）；

    DBMS_ADVISOR.set_task_parameter（l_taskname, ''DEF_INDEX_OWNER'',DBMS_ADVISOR.ADVISOR_UNUSED）；

    DBMS_ADVISOR.set_task_parameter（l_taskname, ''DEF_MVIEW_TABLESPACE'',DBMS_ADVISOR.ADVISOR_UNUSED）；

    DBMS_ADVISOR.set_task_parameter（l_taskname, ''DEF_MVIEW_OWNER'', DBMS_ADVISOR.ADVISOR_UNUSED）；

    -- Execute the task.

    DBMS_ADVISOR.execute_task（task_name => l_taskname）；

    END;

    /

    -- Display the resultingscript.

    SET LONG 100000

    SET PAGESIZE 50000

    SELECT DBMS_ADVISOR.get_task_script（''test_sql_access_task''） AS script FROM  dual;

    SET PAGESIZE 24

    The value for the SET LONG commandshould be adjusted to allow the whole script to be displayed.

    在我测试环境上的输入结果如下：

    PL/SQL procedure successfully completed.

    SCRIPT

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

    Rem  SQL AccessAdvisor: Version 10.2.0.4.0 - Production

    Rem

    Rem  Username:        SYS

    Rem  Task:            test_sql_access_task

    Rem  Executiondate:  31/01/2012 21:50

    Rem

    CREATE BITMAP INDEX "QSOA"."DATA_OA_MESSAGE_IDX$$_167F0001"

    ON"QSOA"."DATA_OA_MESSAGE"

    （"MESS_TYPE"）

    COMPUTESTATISTICS;

    CREATE INDEX"ZHAOKA"."CFG_GAME_AREA_S_IDX$$_167F0004"

    ON "ZHAOKA"."CFG_GAME_AREA_SERVER"

    （"AREA_ID","AREA_NAME","SERVER_ID","SERVER_NAME"）

    COMPUTESTATISTICS;

    …

    2.2 Quick Tune

    If you just wantto tune an individual statement you can use the QUICK_TUNE procedureas follows.

    --如果仅仅是调整一个独立的语句，可以使用QUICK_TUNE过程：

    BEGIN

    DBMS_ADVISOR.quick_tune（

    advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,

    task_name    =>''emp_quick_tune'',

    attr1        => ''SELECT e.*FROM emp e WHERE UPPER（e.ename） = ''''SMITH''''''）；

    END;

    /

    Any recommendations can then be displayed using the previous query with the correcttask name specified.

    查询输出结果和之前的一样，使用：

    Select DBMS_ADVISOR.get_task_script（‘emp_quick_tune’） fromdual;

    2.3 Related Views

    The followingviews can be used to display the SQL Access Advisor output without usingEnterprise Manager or the get_task_script function:

    --可以使用以下视图来查看advisor的输出：

    （1）    DBA_ADVISOR_TASKS：Basic information about existingtasks.

    （2）    DBA_ADVISOR_LOG ：Status information about existingtasks.

    （3）    DBA_ADVISOR_FINDINGS ： Findings identified for anexisting task.

    （4）    DBA_ADVISOR_RECOMMENDATIONS ： Recommendations for the problemsidentified by an existing task.

  [1] [2] 

Oracle SQL Developer unable to find Java Virtual Machine

The Oracle sql developer is NOT supported on 64 bits JDK. To solve it,install a 32 bits / x86 JDK and update your sql developer config file,so that it points to the 32 bits JDK.

Edit the “sqldeveloper.conf“,which can be found under “{ORACLE_HOME}\sqldeveloper\sqldeveloper\bin\sqldeveloper.conf“,make sure “SetJavaHome” is pointing to your 32 bits JDK.

For example,“SetJavaHome C:\Program Files (x86) \Java\jdk1.6.0_13“.

关于使用hint优化Oracle的运行计划 以及 SQL Tune Advisor的使用的介绍已经告一段落，感谢您的耐心阅读，如果想了解更多关于Advised，Advisor，Advice，Pointcut、ORACLE 11G 禁用 SQL TUNING ADVISOR、Oracle SQL Access Advisor 说明、Oracle SQL Developer unable to find Java Virtual Machine的相关信息，请在本站寻找。