Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 540
  • Last Modified:

Improve performance

How to improve performance in the following sentence?

SELECT FUNHVA.FAE_ID ID_DIS,
                F.NIS NIS,
                ('(' || DECODE(FXA.FIC_ID, NULL, 'NA', FXA.FIC_ID) || ')' ||
                EVAMB.EVA_DESCRIPCION) EVENTO,
                TO_CHAR(HVA.HVA_HORA_INICIO, 'dd/MM/yyyy hh24:mi') FEC_INI,
                TO_CHAR(HVA.HVA_HORA_FIN, 'dd/MM/yyyy hh24:mi') FEC_FIN,
                HVA.HVA_HORA_INICIO FECHA,
                A.AMB_NOMBRE AMBIENTE,
                EVAMB.EVA_ID EVENTO_ID
           FROM GESAMBIENTE.FUNCIONARIOXHVA         FUNHVA,
                GESAMBIENTE.HORARIO_EVENTO_AMBIENTE HVA,
                GESAMBIENTE.EVENTO_AMBIENTE         EVAMB,
                GESAMBIENTE.PROGRAMACION_AMBIENTE   PGA,
                GESAMBIENTE.FICHAXAMBIENTE          FXA,
                GESAMBIENTE.PPFXINSTRUCTOR          PPFINST,
                COMUN.FUNCIONARIO                   F,
                GESAMBIENTE.AMBIENTE                A
          WHERE HVA.HVA_ID = FUNHVA.HVA_ID
            AND EVAMB.EVA_ID = HVA.EVA_ID
            AND F.NIS = NVL(PPFINST.NIS_INSTRUCTOR, FUNHVA.NIS_FUN_APOYA)
            AND FUNHVA.PPI_ID = PPFINST.PPI_ID(+)
            AND FUNHVA.FAE_ESTADO <> 2
            AND PGA.PGA_ID(+) = EVAMB.PGA_ID
            AND HVA.HVA_ESTADO = '1'
            AND FXA.FIA_ID(+) = PGA.FIA_ID
            AND A.AMB_ID = EVAMB.AMB_ID
            AND ((PPFINST.NIS_INSTRUCTOR = 2334338 AND FUNHVA.NIS_FUN_APOYA IS NULL) OR
                 (FUNHVA.NIS_FUN_APOYA = 2336400   AND FUNHVA.PPI_ID IS NULL))
            AND TRUNC(HVA.HVA_HORA_FIN) <= SYSDATE
            AND TRUNC(HVA.HVA_HORA_INICIO) >= SYSDATE - 20

Gracias

Jair Duran
explainPlan.jpg
GESAMBIENTE.PPFXINSTRUCTOR.txt
0
jairdur
Asked:
jairdur
  • 10
  • 6
  • 2
2 Solutions
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
How much time it is taking and how much you want?
0
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
Also provide full execution plan. The attached plan is not completely view able.
0
 
jairdurAuthor Commented:
Full Explain Plan explainPlan.txt
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
slightwv (䄆 Netminder) Commented:
That is hard to read.  
Please post the results from sqlplus:

SQL> explain plan for
select ... your select
/

SQL> select * from table(dbms_xplan.display);
0
 
jairdurAuthor Commented:
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

Error: cannot fetch last explain plan from PLAN_TABLE


Plan de Ejecuci¾n
----------------------------------------------------------
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01157: cannot identify/lock data file 501 - see DBWR trace file
ORA-01110: data file 501: '+DGARC/sofia/tempfile/temp.dbf'


SP2-0612: Error al generar el informe AUTOTRACE EXPLAIN

EstadÝsticas
----------------------------------------------------------
         24  recursive calls
          0  db block gets
        120  consistent gets
          0  physical reads
          0  redo size
        395  bytes sent via SQL*Net to client
        350  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
0
 
slightwv (䄆 Netminder) Commented:
>>ORA-01110: data file 501: '+DGARC/sofia/tempfile/temp.dbf'

You might want/need to get that looked at.

>>Error: cannot fetch last explain plan from PLAN_TABLE

Not quite what I was looking for.

Can you try again and see if the TEMP error is still there?
0
 
jairdurAuthor Commented:
No, no jet.

Sorry
0
 
jairdurAuthor Commented:
0
 
slightwv (䄆 Netminder) Commented:
While I try do decipher the plan from an image can you respond to the other Experts question about expectations?  How long does it take now and what are you trying to get?

'make it faster' doesn't help a lot when tuning SQL.


It looks like the outer join above the highlighted line is causing most of the problems.

0
 
jairdurAuthor Commented:
9.75 seg
0
 
slightwv (䄆 Netminder) Commented:
Is that the current time or expected time?

I apologize for asking this because of the language differences but is that 9.75 seconds?
0
 
jairdurAuthor Commented:
Excuse me

Is that the current time 9.57 sec
0
 
slightwv (䄆 Netminder) Commented:
What are you looking to get this down to?
0
 
jairdurAuthor Commented:
reduce execution time and avoid the full access table
0
 
slightwv (䄆 Netminder) Commented:
A full table scan is not always bad.

Like I mentioned before it looks like the outer join is the bad guy here.
0
 
jairdurAuthor Commented:
I just solved with UNION ALL. thanks
0
 
jairdurAuthor Commented:

SELECT FUNHVA.FAE_ID ID_DIS, 
       F.NIS NIS, 
       ('(' || DECODE(FXA.FIC_ID, NULL, 'NA', FXA.FIC_ID) || ')' || EVAMB.EVA_DESCRIPCION) EVENTO, 
       TO_CHAR(HVA.HVA_HORA_INICIO, 'dd/MM/yyyy hh24:mi') FEC_INI, 
       TO_CHAR(HVA.HVA_HORA_FIN, 'dd/MM/yyyy hh24:mi') FEC_FIN, 
       HVA.HVA_HORA_INICIO FECHA, 
       A.AMB_NOMBRE AMBIENTE, 
       EVAMB.EVA_ID EVENTO_ID 
  FROM GESAMBIENTE.FUNCIONARIOXHVA FUNHVA, 
       GESAMBIENTE.HORARIO_EVENTO_AMBIENTE HVA, 
       GESAMBIENTE.EVENTO_AMBIENTE EVAMB, 
       GESAMBIENTE.PROGRAMACION_AMBIENTE PGA, 
       GESAMBIENTE.FICHAXAMBIENTE FXA, 
       GESAMBIENTE.PPFXINSTRUCTOR PPFINST, 
       COMUN.FUNCIONARIO F, 
       GESAMBIENTE.AMBIENTE A 
 WHERE HVA.HVA_ID    = FUNHVA.HVA_ID 
   AND EVAMB.EVA_ID  = HVA.EVA_ID 
   AND F.NIS         = NVL(PPFINST.NIS_INSTRUCTOR, FUNHVA.NIS_FUN_APOYA) 
   AND FUNHVA.PPI_ID = PPFINST.PPI_ID (+) 
   AND FUNHVA.FAE_ESTADO <> 2 
   AND PGA.PGA_ID (+) = EVAMB.PGA_ID 
   AND HVA.HVA_ESTADO = '1' 
   AND FXA.FIA_ID (+) = PGA.FIA_ID 
   AND A.AMB_ID       = EVAMB.AMB_ID 
   AND (PPFINST.NIS_INSTRUCTOR = 10279121 AND FUNHVA.NIS_FUN_APOYA IS NULL)
   AND TRUNC(HVA.HVA_HORA_FIN) <= to_Date('01092011','ddmmyyyy') 
   AND TRUNC(HVA.HVA_HORA_INICIO) >= to_Date('01012011','ddmmyyyy')
union all 
SELECT FUNHVA.FAE_ID ID_DIS, 
       F.NIS NIS, 
       ('(' || DECODE(FXA.FIC_ID, NULL, 'NA', FXA.FIC_ID) || ')' || EVAMB.EVA_DESCRIPCION) EVENTO, 
       TO_CHAR(HVA.HVA_HORA_INICIO, 'dd/MM/yyyy hh24:mi') FEC_INI, 
       TO_CHAR(HVA.HVA_HORA_FIN, 'dd/MM/yyyy hh24:mi') FEC_FIN, 
       HVA.HVA_HORA_INICIO FECHA, 
       A.AMB_NOMBRE AMBIENTE, 
       EVAMB.EVA_ID EVENTO_ID 
  FROM GESAMBIENTE.FUNCIONARIOXHVA FUNHVA, 
       GESAMBIENTE.HORARIO_EVENTO_AMBIENTE HVA, 
       GESAMBIENTE.EVENTO_AMBIENTE EVAMB, 
       GESAMBIENTE.PROGRAMACION_AMBIENTE PGA, 
       GESAMBIENTE.FICHAXAMBIENTE FXA, 
       GESAMBIENTE.PPFXINSTRUCTOR PPFINST, 
       COMUN.FUNCIONARIO F, 
       GESAMBIENTE.AMBIENTE A 
 WHERE HVA.HVA_ID = FUNHVA.HVA_ID 
   AND EVAMB.EVA_ID = HVA.EVA_ID 
   AND F.NIS = NVL(PPFINST.NIS_INSTRUCTOR, FUNHVA.NIS_FUN_APOYA) 
   AND FUNHVA.PPI_ID = PPFINST.PPI_ID (+) 
   AND FUNHVA.FAE_ESTADO <> 2 
   AND PGA.PGA_ID (+) = EVAMB.PGA_ID 
   AND HVA.HVA_ESTADO = '1' 
   AND FXA.FIA_ID (+) = PGA.FIA_ID 
   AND A.AMB_ID = EVAMB.AMB_ID 
   AND (FUNHVA.NIS_FUN_APOYA = 7958573 AND FUNHVA.PPI_ID IS NULL)
   AND TRUNC(HVA.HVA_HORA_FIN) <= to_Date('01092011','ddmmyyyy') 
   AND TRUNC(HVA.HVA_HORA_INICIO) >= to_Date('01012011','ddmmyyyy')

Open in new window

0
 
jairdurAuthor Commented:
Apply UNION ALL
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 10
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now