[20150403]修正sql語句.txt

[20150403]修正sql語句.txt

--主要在一些除錯與最佳化時加入hint容易.

--參考了連結,我自己做了小量的修改.
http://blog.itpub.net/22034023/viewspace-1063610/

/* Formatted on 2015/4/3 9:01:53 (QP5 v5.252.13127.32867) */
--SET LINESIZE 153
SET VERIFY OFF
--SET PAGESIZE 10000
--ACCEPT sql_id -
--PROMPT 'Enter value for sql_id: ' -
--DEFAULT '&&1'

SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('&&1', NULL, 'outline'));

ACCEPT hint_txt -
PROMPT 'Enter value for hint_text: ' -
DEFAULT 'comment'
SET FEEDBACK OFF
SET SQLBLANKLINES ON
SET SERVEROUTPUT ON

DECLARE
   l_profile_name   VARCHAR2 (30);
   cl_sql_text      CLOB;
BEGIN
   SELECT sql_fulltext
     INTO cl_sql_text
     FROM v$sqlarea
    WHERE sql_id = '&&1';

   SELECT 'profile ' || '&&1'  INTO l_profile_name FROM DUAL;

   DBMS_SQLTUNE.import_sql_profile
   (
      sql_text      => cl_sql_text
     ,profile       => sqlprof_attr (q'[&&hint_txt]')
     ,category      => ''
     ,name          => l_profile_name
     ,force_match   => FALSE
   );

   DBMS_OUTPUT.put_line (' ');
   DBMS_OUTPUT.put_line ('Profile ' || l_profile_name || ' created.');
   DBMS_OUTPUT.put_line (' ');
END;
/

COL FIRST_LOAD_TIME FOR a20

SELECT OBJECT_STATUS
      ,FIRST_LOAD_TIME
      ,plan_hash_value
      ,executions
      ,buffer_gets
      ,LAST_ACTIVE_TIME
      ,SQL_ID
  FROM v$sql
WHERE sql_id = '&&1';

prompt =================================================================================================================================================
prompt if drop or alter sql profile ,run :
prompt execute dbms_sqltune.drop_sql_profile(name => 'profile &&1')
prompt execute dbms_sqltune.alter_sql_profile(name => 'profile &&1',attribute_name=>'STATUS',value=>'DISABLED')
prompt =================================================================================================================================================
prompt
prompt
PRO
PRO
UNDEF sql_id
UNDEF hint_txt

SET SQLBLANKLINES OFF
SET SERVEROUTPUT OFF
SET FEEDBACK ON