Opa, Repare ali nas linhas 10, 13 e 15. Esta sendo gerado um produto cartesiano. Verifique se os relacionamentos estão corretos ou se não esta faltando alguma coluna para relacionar. Pense pelo lado positivo!!! A sua consulta leva APENAS 1 minuto!!! A da Aline ta rodando há +- 60 horas e ainda não terminou!! (não resisti a piadinha :D) []'s -- Alex Fernando Kirsten MSN: [EMAIL PROTECTED] Oracle DBA (in curse)
On 7/11/05, RUI MADALENO - 00805 <[EMAIL PROTECTED]> wrote: > Bom dia, > > preciso da vossa ajuda para perceber qual é o problema nessa query. > Demora 1 minuto a ser executada. > > Oracle 10g > Tenho estatisticas calculadas com DBMS_STATS, com a option 'FOR ALL INDEXED > COLUMNS' > > > SELECT > A.PRO_NUMERO AS PRO_NUMERO, > A.AUT_TIPO AS AUT_TIPO, > A.UTI_AUTORIZACAO AS DESTINATARIO, > R.RAM_DESCRICAO AS RAM_DESCRICAO, > DA.DET_AUT_NUMERO AS DET_AUT_NUMERO, > TO_CHAR(P.PRO_DAT_INICIO, 'YYYY-MM-DD') AS PRO_DAT_INICIO, > TO_CHAR(DA.DET_AUT_DATA, 'YYYY-MM-DD') AS DET_AUT_DATA, > E.ESC_NOME AS ESC_NOME, > U.UTI_NOME AS UTI_NOME, > M.MED_NOME AS MED_NOME > FROM > OWF_MGR.WF_WORKLIST_V WL , > OWF_MGR.WF_ITEM_ACTIVITY_STATUSES S, > wf_prod.AUTORIZACOES A, > wf_prod.DET_AUTORIZACOES DA, > wf_prod.PROCESSOS P, > wf_prod.RAMOS R, > wf_prod.UTILIZADORES U, > wf_prod.MEDIADORES M, > wf_prod.ESCRITORIOS E > WHERE S.NOTIFICATION_ID = WL.NID > AND S.ITEM_KEY = TO_CHAR(DA.DET_AUT_NUMERO) > AND DA.AUT_NUMERO = A.AUT_NUMERO > AND E.ESC_DIRECCAO = p.ESC_DIRECCAO > AND E.ESC_ZONA = p.ESC_ZONA > AND E.ESC_ESCRITORIO = p.ESC_ESCRITORIO > AND A.PRO_NUMERO = P.PRO_NUMERO > AND P.RAM_CODIGO = R.RAM_CODIGO > AND P.UTI_TECNICO = U.UTI_CODIGO > AND P.MED_CODIGO = M.MED_CODIGO > AND WL.ITEM_TYPE='AUTOR' > AND UPPER(WL.RECIPIENT_ROLE) = 'JSOUSA01' > AND WL.STATUS = 'OPEN' ORDER BY PRO_DAT_INICIO DESC, DET_AUT_DATA DESC > > ---------------------- > EXPLAIN PLAN > ------------------------- > > 111 linhas seleccionadas. > > Decorreram: 00:00:57.51 > > Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=155 Card=1 Bytes=400 > ) > > 1 0 SORT (ORDER BY) (Cost=155 Card=1 Bytes=400) > 2 1 HASH JOIN (Cost=154 Card=1 Bytes=400) > 3 2 HASH JOIN (Cost=132 Card=383 Bytes=146689) > 4 3 HASH JOIN (Cost=111 Card=383 Bytes=108389) > 5 4 HASH JOIN (Cost=97 Card=246 Bytes=62730) > 6 5 TABLE ACCESS (FULL) OF 'ESCRITORIOS' (TABLE) (Co > st=3 Card=99 Bytes=3465) > > 7 5 HASH JOIN (Cost=94 Card=246 Bytes=54120) > 8 7 INDEX (FULL SCAN) OF 'UTILIZADORES_IDX05' (IND > EX) (Cost=1 Card=84 Bytes=2268) > > 9 7 HASH JOIN (Cost=92 Card=246 Bytes=47478) > 10 9 MERGE JOIN (CARTESIAN) (Cost=34 Card=1 Bytes > =140) > > 11 10 TABLE ACCESS (BY INDEX ROWID) OF 'WF_ITEM_ > ACTIVITY_STATUSES' (TABLE) (Cost=2 Card=1 Bytes=9) > > 12 11 NESTED LOOPS (Cost=31 Card=1 Bytes=78) > 13 12 MERGE JOIN (CARTESIAN) (Cost=25 Card=1 > Bytes=69) > > 14 13 MERGE JOIN (CARTESIAN) (Cost=3 Card= > 1 Bytes=34) > > 15 14 INDEX (RANGE SCAN) OF 'WF_LOOKUPS_ > TL_PK' (INDEX (UNIQUE)) (Cost=2 Card=1 Bytes=27) > > 16 14 BUFFER (SORT) (Cost=1 Card=1 Bytes > =7) > > 17 16 INDEX (RANGE SCAN) OF 'WF_ITEM_T > YPES_TL_PK' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=7) > > 18 13 BUFFER (SORT) (Cost=24 Card=7 Bytes= > 245) > > 19 18 TABLE ACCESS (BY INDEX ROWID) OF ' > WF_NOTIFICATIONS' (TABLE) (Cost=25 Card=7 Bytes=245) > > 20 19 BITMAP CONVERSION (TO ROWIDS) > 21 20 BITMAP AND > 22 21 BITMAP CONVERSION (FROM ROWI > DS) > > 23 22 INDEX (RANGE SCAN) OF 'WF_ > NOTIFICATIONS_N5' (INDEX) (Cost=0 Card=2303) > > 24 21 BITMAP CONVERSION (FROM ROWI > DS) > > 25 24 SORT (ORDER BY) > 26 25 INDEX (RANGE SCAN) OF 'W > FNOTIFIC_IDX01' (INDEX) (Cost=3 Card=2303) > > 27 12 INDEX (RANGE SCAN) OF 'WF_ITEM_ACTIVIT > Y_STATUSES_N2' (INDEX) (Cost=1 Card=1) > > 28 10 BUFFER (SORT) (Cost=32 Card=1 Bytes=62) > 29 28 TABLE ACCESS (FULL) OF 'RAMOS' (TABLE) ( > Cost=3 Card=1 Bytes=62) > > 30 9 TABLE ACCESS (FULL) OF 'PROCESSOS' (TABLE) ( > Cost=57 Card=6228 Bytes=330084) > > 31 4 INDEX (FAST FULL SCAN) OF 'AUT_IDX_01' (INDEX) (Co > st=13 Card=9718 Bytes=272104) > > 32 3 INDEX (FAST FULL SCAN) OF 'MEDIADOR_IDX04' (INDEX) ( > Cost=21 Card=15233 Bytes=1523300) > > 33 2 INDEX (FAST FULL SCAN) OF 'DET_AUT_IDX03' (INDEX) (Cos > t=21 Card=21063 Bytes=358071) > > > > > > Statistics > ---------------------------------------------------------- > 309 recursive calls > 0 db block gets > 836 consistent gets > 37660 physical reads > 0 redo size > 9110 bytes sent via SQL*Net to client > 585 bytes received via SQL*Net from client > 9 SQL*Net roundtrips to/from client > 5 sorts (memory) > 0 sorts (disk) > 111 rows processed > > SQL> _____ __o > ------- -\<, > ----- ( )/( ) > ~~~~~~~~~~~~~~~~~~~~~~~~~~~ > Rui Madaleno > > > ______________________________________________________________________ > > Cancelar assinatura...: [EMAIL PROTECTED] > Moderadores da lista....:Dorian Anderson Soutto [EMAIL PROTECTED] > Fernanda Damous [EMAIL PROTECTED] > Alisson Aguiar [EMAIL PROTECTED] > ______________________________________________________________________ > http://br.groups.yahoo.com/group/oracle_br/ > ______________________________________________________________________ > > Sair da Lista...: [EMAIL PROTECTED] > Links do Yahoo! Grupos > > > > > > > > ______________________________________________________________________ Cancelar assinatura...: [EMAIL PROTECTED] Moderadores da lista....:Dorian Anderson Soutto [EMAIL PROTECTED] Fernanda Damous [EMAIL PROTECTED] Alisson Aguiar [EMAIL PROTECTED] ______________________________________________________________________ http://br.groups.yahoo.com/group/oracle_br/ ______________________________________________________________________ Sair da Lista...: [EMAIL PROTECTED] Links do Yahoo! Grupos <*> Para visitar o site do seu grupo na web, acesse: http://br.groups.yahoo.com/group/oracle_br/ <*> Para sair deste grupo, envie um e-mail para: [EMAIL PROTECTED] <*> O uso que você faz do Yahoo! Grupos está sujeito aos: http://br.yahoo.com/info/utos.html
