收集scott.test的统计信息才对.
2.当一条sql既有sql profile又有stored outline时,优化器优先选择stored outline.
3.force_match参数,TRUE:FORCE (match even when different literals in SQL),FALSE:EXACT (similar to CURSOR_SHARING).
4.通过sql profile手动加hint的方法很简单,而为sql添加最合理的hint才是关键.
5.测试完后,可以通过 exec dbms_sqltune.drop_sql_profile(name =>'coe_6w02d3ggsj4xb_2317948335' );删除这个sql profile.
? ? -- trim parameters
? ? COL sql_id NEW_V sql_id FOR A30;
? ? COL plan_hash_value NEW_V plan_hash_value FOR A30;
? ? SELECT TRIM('&&sql_id.') sql_id, TRIM('&&plan_hash_value.') plan_hash_value FROM DUAL;
? ? VAR sql_text CLOB;
? ? VAR other_xml CLOB;
? ? EXEC :sql_text := NULL;
? ? EXEC :other_xml := NULL;
? ? -- get sql_text from memory
? ? DECLARE
? ? ? l_sql_text VARCHAR2(32767);
? ? BEGIN -- 10g see bug 5017909
? ? ? FOR i IN (SELECT DISTINCT piece, sql_text
? ? ? ? ? ? ? ? ? FROM gv$sqltext_with_newlines
? ? ? ? ? ? ? ? WHERE sql_id = TRIM('&&sql_id.')
? ? ? ? ? ? ? ? ORDER BY 1, 2)
? ? ? LOOP
? ? ? ? IF :sql_text IS NULL THEN
? ? ? ? ? DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE);
? ? ? ? ? DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE);
? ? ? ? END IF;
? ? ? ? l_sql_text := REPLACE(i.sql_text, CHR(00), ' ');
? ? ? ? DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text), l_sql_text);
? ? ? END LOOP;
? ? ? IF :sql_text IS NOT NULL THEN
? ? ? ? DBMS_LOB.CLOSE(:sql_text);
? ? ? END IF;
? ? EXCEPTION
? ? ? WHEN OTHERS THEN
? ? ? ? DBMS_OUTPUT.PUT_LINE('getting sql_text from memory: '||SQLERRM);
? ? ? ? :sql_text := NULL;
? ? END;
? ? /
? ? -- get sql_text from awr
? ? BEGIN
? ? ? IF :sql_text IS NULL OR NVL(DBMS_LOB.GETLENGTH(:sql_text), 0) = 0 THEN
? ? ? ? SELECT REPLACE(sql_text, CHR(00), ' ')
? ? ? ? ? INTO :sql_text
? ? ? ? ? FROM dba_hist_sqltext
? ? ? ? WHERE sql_id = TRIM('&&sql_id.')
? ? ? ? ? AND sql_text IS NOT NULL
? ? ? ? ? AND ROWNUM = 1;
? ? ? END IF;
? ? EXCEPTION
? ? ? WHEN OTHERS THEN
? ? ? ? DBMS_OUTPUT.PUT_LINE('getting sql_text from awr: '||SQLERRM);
? ? ? ? :sql_text := NULL;
? ? END;
? ? /
? ? SELECT :sql_text FROM DUAL;
? ? -- validate sql_text
? ? SET TERM ON;
? ? BEGIN
? ? ? IF :sql_text IS NULL THEN
? ? ? ? RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
? ? ? END IF;
? ? END;
? ? /
? ? SET TERM OFF;
? ? -- to avoid errors when sql_text lacks LFs and is more than 2000 bytes
? ? BEGIN
? ? ? :sql_text := REPLACE(:sql_text, ')', ')'||CHR(10));
? ? ? :sql_text := REPLACE(:sql_text, ',', ','||CHR(10));
? ? ? -- remove consecutive LFs
? ? ? :sql_text := REPLACE(:sql_text, CHR(10)||CHR(10)||CHR(10)||CHR(10)||CHR(10), CHR(10));
? ? ? :sql_text := REPLACE(:sql_text, CHR(10)||CHR(10)||CHR(10), CHR(10));
? ? ? :sql_text := REPLACE(:sql_text, CHR(10)||CHR(10), CHR(10));
? ? END;
? ? /
? ? SELECT :sql_text FROM DUAL;
? ? -- get other_xml from memory
? ? BEGIN
? ? ? FOR i IN (SELECT other_xml
? ? ? ? ? ? ? ? ? FROM gv$sql_plan
? ? ? ? ? ? ? ? WHERE sql_id = TRIM('&&sql_id.')
? ? ? ? ? ? ? ? ? AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
? ? ? ? ? ? ? ? ? AND other_xml IS NOT NULL
? ? ? ? ? ? ? ? ORDER BY
? ? ? ? ? ? ? ? ? ? ? child_number, id)
? ? ? LOOP
? ? ? ? :other_xml := i.other_xml;
? ? ? ? EXIT; -- 1st
? ? ? END LOOP;
? ? EXCEPTION
? ? ? WHEN OTHERS THEN
? ? ? ? DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM);
? ? ? ? :other_xml := NULL;
? ? END;
? ? /
? ? -- get other_xml from awr
? ? BEGIN
? ? ? IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN
? ? ? ? FOR i IN (SELECT other_xml
? ? ? ? ? ? ? ? ? ? FROM dba_hist_sql_plan
? ? ? ? ? ? ? ? ? WHERE sql_id = TRIM('&&sql_id.')
? ? ? ? ? ? ? ? ? ? AND plan_hash_value = TO_NUMBER(TRIM