[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
