Днес се сблъсках с този проблем и искам да споделя решението, което открих в блога на Харалд.
Една заявка изведнъж започна да се държи странно. Вместо да минава за нула време, отнема повече от час. Интересното е, че никой не я е променял от месеци.
Очевидното в плана беше, че се прави FTS (Full Table Scan, да не се бърка с Formula Telecom Solutins 😉 ) на една от най-тлъстите ни таблици. И това се виждаше само в v$sql_plan
– ако дам explain plan
обещава, да мине през индекс.
Използвах SELECT * FROM TABLE(dbms_xplan.display_awr(‘&sql_id’)); и видях, че плана е сгъбен от днес. Старите планове си минаваха през индекс. Защо се е променил (и то към лошо) – така и не се разбра. Не задълбах много в тази насока. Въпроса беше как мога да я изгоня от library cache, за да се генерира нов план.
Най-драстичното решение е alter system flush shared_pool
. Но това е като да си запалиш къщата, за да изгониш една мишка от нея. Да, този план вероятно ще се оправи, друг може пък да се счупи, а това ще е придружено от маса CPU докато се напарснат всички заявки. Не, благодаря.
Друго нещо, което ми хрумна, е да инвалидирам заявката като пусна някакъв празен DDL на някоя от таблиците. Варианти много – примерно да сложа коментар (и това е DDL). Алтернативно мога да set-на статистики на някоя колона (без да ги променям). Тук лошото е отново големия размах, с който се действа – инвалидира се всичко, свързано с тази таблица.
Решението, което предлага Харалд, е много елегантно. Пакета dbms_shared_pool
има процедура purge
, която прави точно това – изритва нещо (било то курсор, пакет, или друго) от library cache. По принцип тя е нова в 11.1, но е толкова ценна, че са я добавили в 10.2.0.4. Има, обаче, две подробности.
Първо, в нашата версия на dbms_shared_pool
нямаше тази процедура. Това се оправя лесно:
@?/rdbms/admin/dbmspool
След това тествах, тествах – то не бачка. Процедурата си минава, но не прави нищо полезно. Тогава се зачетох из коментарите на блога на Харалд и намерих това:
alter session set events '5614566 trace name context forever';
След това нещата потръгнаха:
select address, hash_value from v$sqlarea where sql_id = '1mqxxf66dtgdp';
exec sys.dbms_shared_pool.PURGE('0000000A5C166350, 2363276725', 'C');
Тук параметъра C идва да покаже, че гоним курсор. По същия начин може да се изритат и други неща:
– Value Kind of Object to keep
– –– –––––––-
– P package/procedure/function
– Q sequence
– R trigger
– T type
– JS java source
– JC java class
– JR java resource
– JD java shared data
– C cursor
Ta… това е. Мисля, че е полезно. И работи, тествано е.