Menu de navegação

[sergiomsoParticipante]

Bom dia Pessoal

Queria uma ajuda de vocês.

Criei um cursor que me mostra-se explain dos sql_id. Ess script gera os select dos sql_ids como abaixo:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘gskt29d4c6ud5′,0,’ALLSTATS LAST -NOTE -ROWS -PREDICATE’));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘cnrbjhthxmmbd’,0,’ALLSTATS LAST -NOTE -ROWS -PREDICATE’));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘4qy72r1khncxx’,0,’ALLSTATS LAST -NOTE -ROWS -PREDICATE’));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘8ydudj68byu8p’,0,’ALLSTATS LAST -NOTE -ROWS -PREDICATE’));

ao executar a variavel cmd da erro.

ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 31

Segue o script

——
set serveroutput on size 1000000
declare
cmd varchar2(32767);
cursor c1 is

SELECT S.SID,
S.SERIAL#,
S.USERNAME,
S.STATUS,
S.MODULE,
S.LAST_CALL_ET TEMPO_s,
TO_CHAR(TO_NUMBER(L.OPTIMIZER_COST), ‘9,999,999,999’) COST,
S.EVENT,
L.SQL_ID,
L.SQL_FULLTEXT
FROM V$SESSION S, V$SQL L, V$SQLAREA A
WHERE S.SQL_ADDRESS = L.ADDRESS
AND S.SQL_HASH_VALUE = L.HASH_VALUE
AND S.SQL_CHILD_NUMBER = L.CHILD_NUMBER
AND S.SQL_HASH_VALUE = A.HASH_VALUE
AND A.SQL_ID = L.SQL_ID
AND S.STATUS = ‘ACTIVE’
AND S.EVENT IN (‘db file scattered read’, ‘db file sequential read’)
–AND L.OPTIMIZER_COST > 500
–AND S.LAST_CALL_ET > 300
ORDER BY L.OPTIMIZER_COST DESC;

begin
for c in c1 loop

cmd := ‘SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘ || CHR(39) || c.SQL_ID || CHR(39) || ‘,0,’ || CHR(39) || ‘TYPICAL -NOTE -ROWS -PREDICATE’ || CHR(39) || ‘));’;
execute immediate cmd;
–dbms_output.put_line(cmd);
end loop;

end;
/

—–

[José Laurindo ChiappaModerador]

Bom, o teu erro parece ser ** ABSOLUTAMENTE ÓBVIO ** : o FATO é que dentro do PL/SQL (seja via SQL fixo, seja via SQL dinâmico tanto com EXECUTE IMMEDIATE quanto com DBMS_SQL ou com REF CURSOR) vc é TOTALMENTE PROIBIDO de fazer um SELECT “solto”, pois PL/SQL **** não tem capacidade de Imprimir resultestsm okdoc ??? Então, por princpipio dentro do PL/SQL um SELECT *** TEM QUE ** ser OU acompanhado do INTO (se for retornar só uma linha), guardando as colunas retornadas em variáveis, OU tem que ter o resultset guardado numa outra tabela OU tem que ser processando num LOOP OU tem que retornar o resultset para o consumidor da procedure via REFCURSOR….
O teu comando que vc tenta executar é :

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘gskt29d4c6ud5′,0,’ALLSTATS LAST -NOTE -ROWS -PREDICATE’));

**** CADÊ **** o INTO, OU um LOOP, OU o armazenamento em tabela OU o retorno vai REFCURSOR ou PIPE ?? ÓBVIO que não vai funcionar…

Como exemplo apenas, vou processar cada linha num LOOP , colocando no buffer do DBMS_OUTPUT (já que estou usando um programa cliente que o permite) :

set serveroutput on size 1000000
DECLARE
cmd varchar2(32767);
v_plan_output varchar2(300);
BEGIN
for c in (SELECT S.SID,
S.SERIAL#,
S.EVENT,
L.SQL_ID,
L.SQL_FULLTEXT
FROM V$SESSION S, V$SQL L, V$SQLAREA A
WHERE S.SQL_ADDRESS = L.ADDRESS
AND S.SQL_HASH_VALUE = L.HASH_VALUE
AND S.SQL_CHILD_NUMBER = L.CHILD_NUMBER
AND S.SQL_HASH_VALUE = A.HASH_VALUE
AND A.SQL_ID = L.SQL_ID
— AND S.STATUS = ‘ACTIVE’
— AND S.EVENT IN (‘db file scattered read’, ‘db file sequential read’)
–AND L.OPTIMIZER_COST > 500
–AND S.LAST_CALL_ET > 300
ORDER BY L.OPTIMIZER_COST DESC)
loop
dbms_output.put_line(‘Plano de Execução para SQLID=’ || c.sql_id);
for r in (SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(to_char(c.sql_id),0,’TYPICAL -NOTE -ROWS -PREDICATE’)))
loop
dbms_output.put_line(r.plan_table_output);
end loop;
end loop;
end;
/

==> O resultado foi :

Procedimento PL/SQL concluído com sucesso.

Plano de Execução para SQLID=6c6xzh14tcnmk
SQL_ID 6c6xzh14tcnmk, child number 0
————————————-
SELECT S.SID, S.SERIAL#, S.EVENT, L.SQL_ID, L.SQL_FULLTEXT FROM
V$SESSION S, V$SQL L, V$SQLAREA A WHERE S.SQL_ADDRESS = L.ADDRESS AND
S.SQL_HASH_VALUE = L.HASH_VALUE AND S.SQL_CHILD_NUMBER = L.CHILD_NUMBER
AND S.SQL_HASH_VALUE = A.HASH_VALUE AND A.SQL_ID = L.SQL_ID ORDER BY
L.OPTIMIZER_COST DESC

Plan hash value: 675061204

——————————————————————————————————-
| Id | Operation | Name | Bytes | Cost (%CPU)| Time |
——————————————————————————————————-
| 0 | SELECT STATEMENT | | | 1 (100)| |
| 1 | SORT ORDER BY | | 2285 | 1 (100)| 00:00:01 |
| 2 | NESTED LOOPS | | 2285 | 0 (0)| |
| 3 | NESTED LOOPS | | 2238 | 0 (0)| |
| 4 | NESTED LOOPS | | 170 | 0 (0)| |
| 5 | NESTED LOOPS | | 123 | 0 (0)| |
| 6 | FIXED TABLE FULL | X$KSUSE | 97 | 0 (0)| |
| 7 | FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) | 26 | 0 (0)| |
| 8 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 47 | 0 (0)| |
| 9 | FIXED TABLE FIXED INDEX | X$KGLCURSOR_CHILD (ind:1) | 2068 | 0 (0)| |
| 10 | FIXED TABLE FIXED INDEX | X$KGLCURSOR_CHILD_SQLID (ind:2 | 47 | 0 (0)| |
——————————————————————————————————-

É por sua conta adaptar para a sua necessidade : como é uma tool de Auditoria, creio que vc vai querer gravar o plano numa tabela…

[]s

Chiappa

[Autor]

Posts