Как да намерите цената на съхранена процедура в Oracle и да я оптимизирате

Може ли някой да ме уведоми дали има някакъв начин да разбера цената на съхранена процедура в Oracle? Ако няма директен начин, бих искал да знам каквито и да е заместители.
Начинът, по който намерих цената, е да направя автоматично проследяване на всички заявки, използвани в съхранената процедура, и след това да изчисля цената на процедурата според честотата на изпълнение на заявки.
В допълнение към това бих искал предложения за оптимизиране на моята съхранена процедура, особено на заявката, дадена по-долу.
Логика на процедурата:
По-долу е динамичната sql заявка, използвана като курсор в моята съхранена процедура . Този курсор се отваря и извлича вътре в цикъл. Извличам информацията и ги поставям в масив, преброявам данните и след това ги вмъквам в таблица.
Целта ми е да разбера цената на proc, както и да оптимизирам sp.

SELECT DISTINCT acct_no    
FROM raw
WHERE 1=1
AND code = ''' || code ||
''' AND qty       < 0  
AND acct_no 
IN (SELECT acct_no FROM ' || table_name || ' WHERE counter = 
(SELECT MAX(counter) FROM ' || table_name || '))

performance oracle stored-procedures query-optimization plsql
person ramesh    schedule 08.02.2013    source източник

Отговори (3)


arrow_upward
5
arrow_downward

Един от най-добрите инструменти за анализиране на производителността на SQL и PLSQL е естественото SQL трасиране.

  1. активирайте проследяване във вашата сесия:

    SQL> alter session set SQL_TRACE=TRUE;

Session altered

  2. Стартирайте процедурата си

  3. Излезте от сесията си

  4. Отидете до директорията udump на вашия сървър и намерете вашия файл за проследяване (обикновено последен)

  5. Изпълнете tkprof

Това ще създаде файл, съдържащ списък на всички изрази с много информация, включително броя пъти, в които всеки е бил изпълнен, неговия план на заявка и статистика. Това е по-подробно и прецизно от ръчното изпълнение на плана за всеки избран.

Ако искате да оптимизирате производителността на процедура, обикновено сортирате файла за проследяване по времето, необходимо за изпълнение (с sort=EXEELA) или извлечете SQL и се опитайте да оптимизирате заявките, които работят най-много.

Можете също така да направите събития за изчакване на файла за проследяване, като използвате следната команда на стъпка 1:

ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
person Vincent Malgrat    schedule 08.02.2013
comment
Нямаме достъп до db сървъра. Следователно директорията udump е недостъпна. Можете ли да предложите някаква алтернатива от страна на клиента. - person ramesh; 19.02.2013
comment
Попитайте DBA? Сериозно информацията за проследяване на сесията е фундаментална за отстраняване на грешки/оптимизиране, достъпът до директорията за изхвърляне на user е доста необходим. В противен случай трябва да проверите решението на @APC, което няма да се нуждае от достъп до сървъра. Мисля, че SQL Developer има някои вградени отчети, които взаимодействат с DBMS_PROFILER. - person Vincent Malgrat; 19.02.2013

arrow_upward
4
arrow_downward

Начинът да разберете цената (в изпълнение на времето) за съхранена процедура е да наемете профилиращ. 11g въведе йерархичния профильор, който е много чист. Научете повече.

Преди 11g имаше само DBMS_PROFILER, което е достатъчно добро, особено ако вашата съхранена процедура не използва обекти в други схеми. Научете повече.

Проследяването е добро за идентифициране на лошо работещ SQL. Профайлърите са добри за идентифициране на цената на PL/SQL елементите на съхранена процедура. Ако вашата процедура има някои скъпи изчислителни елементи, които не четат или записват в таблици, това няма да се покаже в SQL трасирането.

По същия начин, ако имате добре настроен SQL оператор, но го използвате лошо, стартирането на профайлър вероятно ще ви помогне повече от проследяването. Пример за това, което имам предвид, е многократното изпълнение на една и съща команда SELECT в цикъл на курсора: знам, че това не е точно това, което правите, но е достатъчно близо.

Очевидно йерархичният профильор DBMS_HPROF е инсталиран по подразбиране в 11g, но DBA трябва да предостави някои привилегии на разработчиците, които искат да го използват. Научете повече.

За да инсталирате DBMS_PROFILER в 10g (или по-рано), DBA трябва да изпълни този скрипт:

$ORACLE_HOME/rdbms/admin/proftab.sql

Не забравяйте да получите и инфраструктурата за отчитане:

$ORACLE_HOME/plsql/demo/profsum.sql

(Името или местоположението на този скрипт може да варира в по-ранните версии).

person APC    schedule 08.02.2013
comment
Не съм сигурен как да използвам DBMS_PROFILER. Таблицата PLSQL_PROFILER_RUNS или PLSQL_PROFILER_UNITS не съществува. Нямам достъп до сървъра. Има ли помощ при използването на SQL разработчик. Знам само за проследяване на SQL. - person ramesh; 19.02.2013
comment
SQL Developer е просто интерфейс към вградените пакети на Oracle. Така че нямате късмет, ако вашите DBA не инсталират пакетите вместо вас. Но не виждам защо трябва да отказват. Грях е в интерес на всички вие да пишете код, който се представя добре. - person APC; 19.02.2013

arrow_upward
1
arrow_downward

Лесният начин е да изпълните процедурата и след това да заявите v$sql. ако искате малък съвет, който да улесни живота ви (не само за пакети), добавете празен коментар към заявката в процедурата, нещо като

select /* BIG DADDY */ * from dual;

и след това заявете v$sql, както следва

select * from v$sql where sql_text like '%BIG DADDY%';

най-добрият начин определено е начинът, предложен от @Винсънт Малграт.

късмет.

person haki    schedule 09.02.2013
comment
Тук имаме 3 колони. hash_value, old_hash_value и new_hash_value. Кой от тях трябва да се вземе предвид? Освен това не прави дисекция на заявката, както прави trace. - person ramesh; 19.02.2013
comment
не разбирам въпроса. относно нивото на дисекция - без съмнение tkprof дай повече информация. v$sql предоставя основна информация като изпълнения, изминало време, процесорно време, използване на паметта, време за анализ, твърд/мек анализ и т.н. - person haki; 23.02.2013