Home » RDBMS Server » Performance Tuning » Problems with with-statement (Oracle 10.2.0)
Problems with with-statement [message #364596] Tue, 09 December 2008 04:16 Go to next message
eigeneachse
Messages: 37
Registered: July 2008
Member
Hi@all,
i am having performance issues with some of my with statements. when i fire it for the schema owner then i get the result in a really good time. but if i try to fire it from another db user then i alwas get a really bad peformance. my session monitor shows me "latch: cache buffers chains" wait events.
why are there wait event for users that are not schema owner, and why are there no wait events for the owner?

are there any problems known with the with-statement?

here is an example of my statement.

WITH main_query AS
     (SELECT /*+ materialize */
             pe.beginn pebeginn, vla.vertragsnummer vertragspartner, vla.vertragsbezeichnung,
             CASE
                 WHEN vpe.serien_id IS NULL THEN DECODE (vla.titel,
                                                         NULL, DECODE (vpe.serien_id,
                                                                       NULL, vpe.titel,
                                                                       vpe.serientitel
                                                                      ),
                                                         DECODE (vla.hidden_serien_id,
                                                                 NULL, vla.titel,
                                                                 vla.serientitel
                                                                )
                                                        )
                 ELSE DECODE (vpe.serien_id,
                              NULL, getpresstitle (vpe.fassungmrg_id),
                              vla.serienoriginaltitel
                             )
             END titel,
             DECODE (vla.titel,
                     NULL, DECODE (vpe.serien_id, NULL, '', vpe.titel),
                     DECODE (vla.hidden_serien_id, NULL, '', vla.titel)
                    ) serientitel,
             DECODE (dp.episode, NULL, TO_CHAR (vpe.episodennummer), dp.episode) episodennummer,
             CASE
                 WHEN vpe.serien_id IS NULL THEN DECODE
                                                    (vpe.serien_id,
                                                     NULL, getpresstitle (vpe.fassungmrg_id),
                                                     vla.serienoriginaltitel
                                                    )
                 ELSE DECODE (vla.titel,
                              NULL, DECODE (vpe.serien_id, NULL, vpe.titel, vpe.serientitel),
                              DECODE (vla.hidden_serien_id, NULL, vla.titel, vla.serientitel)
                             )
             END originaltitel,
             pp.programmtyp, SUBSTR (fnc_sys_datetime.nframes2string (pe.beginn), 0,
                                     11) bc_date_old,
             getbroadcastdaystring (:contentid, pe.beginn) bc_date,
             SUBSTR (fnc_sys_datetime.nframes2string (pe.beginn), 12, 5) beginn,
             getlocalamountforlicence
                   (pe.lizenz_id,
                    1,
                    getactivationforlicence (pe.lizenz_id,
                                             fnc_sys_datetime.nframes2datetime (pe.beginn)
                                            )
                   ) loc_preis,
             getusedrunsforlicence (pe.lizenz_id, :END, 0, pe.service_id) exhibitionday,
             getusedrunsforlicence (pe.lizenz_id, :END, 1,
                                    pe.service_id) wiederholung_exhibitionday,
             CASE
                 WHEN getusedrunsforlicence (pe.lizenz_id, :END, 0) IS NOT NULL THEN getlocalamountforlicence
                                                                                        (pe.lizenz_id,
                                                                                         1,
                                                                                         getamortizationforlicence
                                                                                             (pe.lizenz_id,
                                                                                              fnc_sys_datetime.nframes2datetime
                                                                                                  (pe.beginn
                                                                                                  )
                                                                                             )
                                                                                        )
             END loc_amortization,
             pa.methode,
             CASE pa.abschreibemethode_id
                 WHEN 651 THEN CASE getusedrunsforlicence (pe.lizenz_id,
                                                           :END,
                                                           0,
                                                           pe.service_id
                                                          )
                                  WHEN 1 THEN ROUND
                                                 ((  getlocalamountforlicence
                                                         (pe.lizenz_id,
                                                          1,
                                                          getactivationforlicence
                                                              (pe.lizenz_id,
                                                               fnc_sys_datetime.nframes2datetime
                                                                                          (pe.beginn)
                                                              )
                                                         )
                                                   * 0.67
                                                  ),
                                                  2
                                                 )
                                  WHEN 2 THEN ROUND
                                                 ((  getlocalamountforlicence
                                                         (pe.lizenz_id,
                                                          1,
                                                          getactivationforlicence
                                                              (pe.lizenz_id,
                                                               fnc_sys_datetime.nframes2datetime
                                                                                          (pe.beginn)
                                                              )
                                                         )
                                                   * 0.33
                                                  ),
                                                  2
                                                 )
                              END
                 WHEN 652 THEN CASE
                                  WHEN getusedrunsforlicence (pe.lizenz_id, :END, 0, pe.service_id) =
                                                                                                   1
                                   OR getusedrunsforlicence (pe.lizenz_id, :END, 0, pe.service_id) =
                                                                                                   2 THEN ROUND
                                                                                                             ((  getlocalamountforlicence
                                                                                                                     (pe.lizenz_id,
                                                                                                                      1,
                                                                                                                      getactivationforlicence
                                                                                                                          (pe.lizenz_id,
                                                                                                                           fnc_sys_datetime.nframes2datetime
                                                                                                                               (pe.beginn
                                                                                                                               )
                                                                                                                          )
                                                                                                                     )
                                                                                                               * 0.5
                                                                                                              ),
                                                                                                              2
                                                                                                             )
                              END
                 WHEN 650 THEN CASE getusedrunsforlicence (pe.lizenz_id, :END, 0, pe.service_id)
                                  WHEN 1 THEN ROUND
                                                 (getlocalamountforlicence
                                                      (pe.lizenz_id,
                                                       1,
                                                       getactivationforlicence
                                                           (pe.lizenz_id,
                                                            fnc_sys_datetime.nframes2datetime
                                                                                          (pe.beginn)
                                                           )
                                                      ),
                                                  2
                                                 )
                              END
             END cost_saving_exhibitionday,
             0 cost_saving_exhibitionday_wdh, DECODE (pe.exhibitionday, NULL, 'WH', 'RUN') info
        FROM pln_event pe,
             v_pln_beitraege vpe,
             v_lizenz_programm vla,
             liz_konto lk,
             par_abschreibemethode pa,
             dok_programmfassung dpf,
             dok_programm dp,
             par_programmtyp pp,
             liz_vertrag lv,
             par_vertragsart pva
       WHERE pe.lizenz_id = vla.hidden_lizenz_id(+)
         AND pe.merge_id = vpe.fassungmrg_id
         AND vla.hidden_konto_id = lk.konto_id(+)
         AND lk.abschreibemethode_id = pa.abschreibemethode_id(+)
         AND pe.event_id = pe.main_event_id
         AND pe.merge_id = dpf.merge_id
         AND dpf.programm_id = dp.programm_id
         AND dp.programmtyp_id = pp.programmtyp_id
         AND vla.hidden_vertrag_id = lv.vertrag_id
         AND lv.vertragsart_id = pva.vertragsart_id
         AND pva.vertragsart = 'License contract'
         AND pp.kuerzel NOT IN ('LOCAL', 'NEWS')
         AND pe.beginn >= fnc_sys_datetime.datetime2nframes (:BEGIN)
         AND pe.beginn < fnc_sys_datetime.datetime2nframes (:END)
         AND pe.service_id = :contentid)
SELECT   *
    FROM (SELECT 1 group_nr, TO_CHAR (pebeginn) group_data, pebeginn, vertragspartner,
                 vertragsbezeichnung, titel, serientitel, episodennummer, originaltitel,
                 programmtyp, bc_date_old, bc_date, beginn, loc_preis, exhibitionday,
                 wiederholung_exhibitionday, loc_amortization, methode, cost_saving_exhibitionday,
                 cost_saving_exhibitionday_wdh, info
            FROM main_query v
          UNION ALL
          SELECT   1.5 group_nr, getbroadcastdaystring (:contentid, pebeginn) group_data,
                   TO_NUMBER (NULL) pebeginn, TO_CHAR (NULL) vertragspartner,
                   TO_CHAR (NULL) vertragsbezeichnung, TO_CHAR (NULL) titel,
                   TO_CHAR (NULL) serientitel, '' episodennummer, TO_CHAR (NULL) originaltitel,
                   TO_CHAR (NULL) programmtyp, '' bc_date_old, TO_CHAR (NULL) bc_date,
                   TO_CHAR (NULL) beginn, SUM (NVL (loc_preis, 0)) loc_preis,
                   SUM (exhibitionday) exhibitionday,
                   SUM (wiederholung_exhibitionday) wiederholung_exhibitionday,
                   SUM (NVL (loc_amortization, 0)) loc_amortization, TO_CHAR (NULL) methode,
                   SUM (DECODE (info, 'RUN', NVL (cost_saving_exhibitionday, 0))
                       ) cost_saving_exhibitionday,
                   SUM (cost_saving_exhibitionday_wdh) cost_saving_exhibitionday_wdh, '' info
              FROM main_query v
          GROUP BY getbroadcastdaystring (:contentid, pebeginn)
          UNION ALL
          SELECT   2 group_nr, programmtyp group_data, TO_NUMBER (NULL) pebeginn,
                   TO_CHAR (NULL) vertragspartner, TO_CHAR (NULL) vertragsbezeichnung,
                   TO_CHAR (NULL) titel, TO_CHAR (NULL) serientitel, '' episodennummer,
                   TO_CHAR (NULL) originaltitel, TO_CHAR (NULL) programmtyp, '' bc_date_old,
                   TO_CHAR (NULL) bc_date, TO_CHAR (NULL) beginn, SUM (NVL (loc_preis, 0))
                                                                                          loc_preis,
                   SUM (exhibitionday) exhibitionday,
                   SUM (wiederholung_exhibitionday) wiederholung_exhibitionday,
                   SUM (NVL (loc_amortization, 0)) loc_amortization, TO_CHAR (NULL) methode,
                   SUM (DECODE (info, 'RUN', NVL (cost_saving_exhibitionday, 0))
                       ) cost_saving_exhibitionday,
                   SUM (cost_saving_exhibitionday_wdh) cost_saving_exhibitionday_wdh, '' info
              FROM main_query v
          GROUP BY programmtyp
          UNION ALL
          SELECT   3 group_nr, vertragsbezeichnung || ' ' || programmtyp group_data,
                   TO_NUMBER (NULL) pebeginn, TO_CHAR (NULL) vertragspartner,
                   TO_CHAR (NULL) vertragsbezeichnung, TO_CHAR (NULL) titel,
                   TO_CHAR (NULL) serientitel, '' episodennummer, TO_CHAR (NULL) originaltitel,
                   TO_CHAR (NULL) programmtyp, '' bc_date_old, TO_CHAR (NULL) bc_date,
                   TO_CHAR (NULL) beginn, SUM (NVL (loc_preis, 0)) loc_preis,
                   SUM (exhibitionday) exhibitionday,
                   SUM (wiederholung_exhibitionday) wiederholung_exhibitionday,
                   SUM (NVL (loc_amortization, 0)) loc_amortization, TO_CHAR (NULL) methode,
                   SUM (DECODE (info, 'RUN', NVL (cost_saving_exhibitionday, 0))
                       ) cost_saving_exhibitionday,
                   SUM (cost_saving_exhibitionday_wdh) cost_saving_exhibitionday_wdh, '' info
              FROM main_query v
          GROUP BY vertragsbezeichnung || ' ' || programmtyp
          UNION ALL
          SELECT 4 group_nr, '' group_data, TO_NUMBER (NULL) pebeginn,
                 TO_CHAR (NULL) vertragspartner, TO_CHAR (NULL) vertragsbezeichnung,
                 TO_CHAR (NULL) titel, TO_CHAR (NULL) serientitel, '' episodennummer,
                 TO_CHAR (NULL) originaltitel, TO_CHAR (NULL) programmtyp, '' bc_date_old,
                 TO_CHAR (NULL) bc_date, TO_CHAR (NULL) beginn, SUM (NVL (loc_preis, 0)) loc_preis,
                 SUM (exhibitionday) exhibitionday,
                 SUM (wiederholung_exhibitionday) wiederholung_exhibitionday,
                 SUM (NVL (loc_amortization, 0)) loc_amortization, TO_CHAR (NULL) methode,
                 SUM (DECODE (info, 'RUN', NVL (cost_saving_exhibitionday, 0))
                     ) cost_saving_exhibitionday,
                 SUM (cost_saving_exhibitionday_wdh) cost_saving_exhibitionday_wdh, '' info
            FROM main_query v) mainselect
ORDER BY group_nr,
         group_data,
         bc_date,
         beginn,
         programmtyp,
         vertragspartner,
         vertragsbezeichnung,
         titel,
         episodennummer


thanks in advance

regards

eigeneachse
Re: Problems with with-statement ( solved ) [message #364610 is a reply to message #364596] Tue, 09 December 2008 04:36 Go to previous messageGo to next message
eigeneachse
Messages: 37
Registered: July 2008
Member
Hi@all,
i've solved this problem by setting optimizer_secure_view_merging parameter to false. Now everything works great.

Regards

eigeneachse
Re: Problems with with-statement ( solved ) [message #364638 is a reply to message #364610] Tue, 09 December 2008 07:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The only thing I can see is that you're using a (as far as I can tell) non-existant hint. I find no documentation for the MATERIALIZE hint.
Re: Problems with with-statement [message #364692 is a reply to message #364596] Tue, 09 December 2008 11:23 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
its an *undocumented" hint.

Basically meaning ensure oracle physically creates temporary storage to store the WITH results.

Useful to ensure plan stability and prevent view merging.
Previous Topic: Oracle Benchmark Results for Different Servers brand name
Next Topic: Table extents
Goto Forum:
  


Current Time: Sat Dec 03 06:13:31 CST 2016

Total time taken to generate the page: 0.21576 seconds