Skip to Main Content
  • Questions
  • complicated View does not use /*+ result_cache */

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Alessandra.

Asked: November 11, 2024 - 10:56 am UTC

Last updated: November 13, 2024 - 2:06 am UTC

Version: 19.24

Viewed 100+ times

You Asked

Hi AskTom Team,
I do not know why my view is not using the /*+ result_cache */ hint.
Hoqw can I look for the reason?

select /*+ result_cache */ * from (
WITH pttc as
(
 SELECT    pttc.pttc_code                AS term_code,
           pttc.pttc_cts_value           AS term_cts_value,
           pttc.pttc_create_date         AS term_create_date,
           pttc.pttc_create_user         AS term_create_user,
           pttc.pttc_desc                AS term_description,
           pttc.pttc_id                  AS term_id,
           pttc.pttc_modify_date         AS term_modify_date,
           pttc.pttc_modify_user         AS term_modify_user,
           pttc.pttc_pdtc_id             AS term_pdtc_id,
           pttc.pttc_prftc_id            AS term_prftc_id,
           pttc.pttc_sort                AS term_sort,
           pttc.pttc_term_id             AS term_term_id,
           pttc.pttc_valid_from          AS term_valid_from,
           pttc.pttc_valid_to            AS term_valid_to,
           pttc.pttc_wfttc_id            AS term_wfttc_id,
           proc.proc_create_date         AS create_date,
           proc.proc_create_user         AS create_user,
           proc.proc_desc                AS description,
           proc.proc_id                  AS id,
           lngc.lngc_iso_639_1           AS lngc_code,
           lngc.lngc_id,
           proc.proc_modify_date         AS modify_date,
           proc.proc_modify_user         AS modify_user,
           proc.proc_name                AS name,
           proc.proc_revision_number     AS rev,
           proc.proc_sort                AS sort,
           ccsc.ccsc_code                AS state_code,
           ccsc.ccsc_id                  AS state_id,
           ccsc.ccsc_name                AS state_name,
           proc.proc_valid_from          AS valid_from,
           proc.proc_valid_to            AS valid_to,
           PROC_EXCHANGE_DATE            AS exchange_date,
           PROC_version                  AS version
      FROM cat_proc_type_term  pttc
           INNER JOIN cat_proc_type proc ON proc.proc_pttc_id = pttc.pttc_id
           INNER JOIN ctl_language lngc ON proc.proc_lngc_id = lngc.lngc_id
           INNER JOIN cat_ctl_cat_state ccsc
               ON proc.proc_ccsc_id = ccsc.ccsc_id
           AND     TRUNC (SYSDATE) BETWEEN pttc.pttc_valid_from
                                   AND NVL (
                                           pttc.pttc_valid_to,
                                           TO_DATE ('31-12-9999',
                                                    'dd-mm-yyyy'))
           AND TRUNC (SYSDATE) BETWEEN proc.proc_valid_from
                                   AND NVL (
                                           proc.proc_valid_to,
                                           TO_DATE ('31-12-9999',
                                                    'dd-mm-yyyy'))
           AND ccsc.ccsc_code IN ('CCSC_CAT_CURR', 'CCSC_CAT_PROV')
           
           
               ) ,
 prsc as
 (
 SELECT  prsc.prsc_create_date         AS create_date,
           prsc.prsc_create_user         AS create_user,
           prsc.prsc_desc                AS description,
           prsc.prsc_id                  AS id,
           lngc.lngc_iso_639_1           AS lngc_code,
           lngc.lngc_id                  AS lngc_id,
           prsc.prsc_modify_date         AS modify_date,
           prsc.prsc_modify_user         AS modify_user,
           prsc.prsc_name                AS name,
           prsc.prsc_revision_number     AS rev,
           prsc.prsc_sort                AS sort,
           ccsc.ccsc_code                AS state_code,
           ccsc.ccsc_id                  AS state_id,
           ccsc.ccsc_name                AS state_name,
           pstc.pstc_code                AS term_code,
           pstc.pstc_create_date         AS term_create_date,
           pstc.pstc_create_user         AS term_create_user,
           pstc.pstc_desc                AS term_description,
           pstc.pstc_id                  AS term_id,
           pstc.pstc_modify_date         AS term_modify_date,
           pstc.pstc_modify_user         AS term_modify_user,
           pstc.pstc_sort                AS term_sort,
           pstc.pstc_term_id             AS term_term_id,
           pstc.pstc_valid_from          AS term_valid_from,
           pstc.pstc_valid_to            AS term_valid_to,
           prsc.prsc_valid_from          AS valid_from,
           prsc.prsc_valid_to            AS valid_to,
           PRSC_EXCHANGE_DATE            AS exchange_date,
           PRSC_version                  AS version
      FROM cat_proc_state_term  pstc
           INNER JOIN cat_proc_state prsc ON prsc.prsc_pstc_id = pstc.pstc_id
           INNER JOIN ctl_language lngc ON prsc.prsc_lngc_id = lngc.lngc_id
           INNER JOIN cat_ctl_cat_state ccsc
               ON prsc.prsc_ccsc_id = ccsc.ccsc_id
           AND     TRUNC (SYSDATE) BETWEEN pstc.pstc_valid_from
                                   AND NVL (
                                           pstc.pstc_valid_to,
                                           TO_DATE ('31-12-9999',
                                                    'dd-mm-yyyy'))
           AND TRUNC (SYSDATE) BETWEEN prsc.prsc_valid_from
                                   AND NVL (
                                           prsc.prsc_valid_to,
                                           TO_DATE ('31-12-9999',
                                                    'dd-mm-yyyy'))
           AND ccsc.ccsc_code IN ('CCSC_CAT_CURR', 'CCSC_CAT_PROV')    
),
groc as
(
select /*+ MATERIALIZE */ * from all_groc_valid
),
role as
(
select * from pty_role
),
prty as
(
select /*+ result_cache */ * from pty_party --where prty_ref_nr = :prtyRefNr
),
insf as
(
select * from  ins_procedure_folder
),
ins_role_addr as
(
 SELECT role.role_id,
           role.role_inse_id,
           prty.prty_id,
           prty.prty_name,
           prty.prty_ref_nr,
           addr.addr_street,
           addr.addr_street_number,
           addr.addr_street_number_addition,
           addr.addr_post_code,
           addr.addr_city,
           ctrc.ctrc_name,
           role.role_prof_id,
           lngc.lngc_code,
           roltc.roltc_code
      FROM   role
           INNER JOIN insf ON role_prof_id = insf_prof_id
           INNER JOIN ctl_role_term roltc
               ON     roltc.roltc_id = role.role_roltc_id
                  AND roltc.roltc_code IN
                          ('ROLTC_INS_STANDORT', 'ROLTC_INS_BEW_INH')
           INNER JOIN  prty ON role.role_prty_id = prty.prty_id
           INNER JOIN pty_contact_details codt
               ON codt.codt_prty_id = prty.prty_id
           INNER JOIN pty_address addr ON addr.addr_codt_id = codt.codt_id
           INNER JOIN ctl_country ctrc
               ON addr.addr_crtc_id = ctrc.ctrc_crtc_id
           INNER JOIN ctl_language lngc ON ctrc.ctrc_lngc_id = lngc.lngc_id

)
/*
 SELECT DISTINCT
             prfa_prcd_id,
             prfa_ref_num_proc,
             prcd_email_notifications,
             prty_ref_nr,
             LISTAGG (eligibility,
                      tec_utilities.select_tpar_value ('REP_2025_SEPARATOR'))
             WITHIN GROUP (ORDER BY eligibility)    AS eligibility,
             prcd_submission_date,
             verfahrenstyp                          AS proc_type_name,
             lngc_code,
             NVL (has_open_communications, 0),
             NVL (cnt_running, 0),
             NVL (cnt_done, 0),
             max_date,
             prcd_grouping_pooling,
             prsc_term_code,
             prsc_name,
             eqpf_subject,
             scope_variation,
             prcd_term_code,
             bewilligungsinhaber,
             bewilligungsinhaber_adresse,
             betriebsstandort_party_name,
             betriebsstandort_adresse,
             license_number
        FROM

(
*/
SELECT /*+ result_cache */ DISTINCT
                     role.role_prcd_id
                         prfa_prcd_id,
                     prfa.prfa_ref_num_proc,
                     prcd_email_notifications,
                     prty.prty_ref_nr,
                     (SELECT LISTAGG (
                                 t1.name,
                                 tec_utilities.select_tpar_value (
                                     'REP_2025_SEPARATOR'))
                             WITHIN GROUP (ORDER BY t1.name)
                        FROM (SELECT DISTINCT CASE
                                                  WHEN     rolc_eli.term_id IN
                                                               (SELECT rtgta_roltc_id
                                                                  FROM role_group_asgn
                                                                 WHERE     rtgta_grotc_id =
                                                                           (SELECT term_id
                                                                              FROM all_groc_valid
                                                                             WHERE term_code =
                                                                                   'GROTC_INS_ROLLE_MELDEPF_FACHP')
                                                                       AND rtgta_valid_to
                                                                               IS NULL)
                                                       AND role_eli.role_prty_id
                                                               IS NOT NULL
                                                  THEN
                                                         (SELECT    DECODE (
                                                                        pers_title,
                                                                        NULL, NULL,
                                                                           pers_title
                                                                        || ' ')
                                                                 || pers_given_name
                                                                 || ' '
                                                                 || pers_family_name
                                                            FROM pty_person
                                                           WHERE pers_prty_id =
                                                                 role_eli.role_prty_id)
                                                      || ' - '
                                                      || rolc_eli.name
                                                  WHEN     rolc_eli.term_id IN
                                                               (SELECT rtgta_roltc_id
                                                                  FROM role_group_asgn
                                                                 WHERE     rtgta_grotc_id =
                                                                           (SELECT term_id
                                                                              FROM all_groc_valid
                                                                             WHERE term_code =
                                                                                   'GROTC_INS_ROLLE_MELDEPF_FACHP')
                                                                       AND rtgta_valid_to
                                                                               IS NULL)
                                                       AND role_eli.role_prty_id
                                                               IS NULL
                                                  THEN
                                                      NULL
                                                  ELSE
                                                      rolc_eli.name
                                              END    AS name
                                FROM role role_eli
                                     INNER JOIN
                                     all_rolc_valid rolc_eli
                                         ON     rolc_eli.term_id =
                                                role_eli.role_roltc_id
                                            AND rolc_eli.term_is_eligibility =
                                                1
                                            AND rolc_eli.lngc_code =
                                                pttc.lngc_code
                                     INNER JOIN proc_folder_asgn prfa
                                         ON prfa_prcd_id =
                                            role.role_prcd_id
                                     INNER JOIN
                                     ins_procedure_folder insf
                                         ON insf_prof_id =
                                            prfa_prof_id
                                     INNER JOIN ins_eligibility inse
                                         ON     inse_id =
                                                insf.insf_inse_id
                                            AND role_eli.role_inse_id =
                                                inse.inse_id
                                            AND ROWNUM <=
                                                tec_utilities.select_tpar_value (
                                                    'REP_2025_LIMITER'))
                             t1)
                         AS eligibility,
                     prcd.prcd_submission_date,
                     pttc.name
                         verfahrenstyp,
                     pttc.lngc_code ,
                     get_has_open (prcd.prcd_id, pttc.lngc_code)
                         AS has_open_communications,
                     get_running (prcd.prcd_id, pttc.lngc_code)
                         AS cnt_running,
                     get_done (prcd.prcd_id, pttc.lngc_code)
                         AS cnt_done,
                     get_newest (prcd.prcd_id, pttc.lngc_code)
                         AS max_date,
                     prcd.prcd_grouping_pooling,
                     prsc.term_code
                         AS prsc_term_code,
                     prsc.name
                         AS prsc_name,
                     CAST (NULL AS VARCHAR2 (255))
                         AS eqpf_subject,
                     CAST (NULL AS VARCHAR2 (255))
                         AS scope_variation,
                     pttc.term_code
                         AS prcd_term_code,
                     role_inh.prty_name
                         bewilligungsinhaber,
                        role_inh.addr_street
                     || ' '
                     || role_inh.addr_street_number
                     || CASE
                            WHEN role_inh.addr_street_number_addition
                                     IS NOT NULL
                            THEN
                                ' ' || role_inh.addr_street_number_addition
                        END
                     || DECODE (role_inh.addr_street_number, NULL, '', ', ')
                     || role_inh.addr_post_code
                     || ' '
                     || role_inh.addr_city
                     || DECODE (role_inh.addr_street_number, NULL, '', ', ')
                     || role_inh.ctrc_name
                         bewilligungsinhaber_adresse,
                     role_ort.prty_name
                         betriebsstandort_party_name,
                        role_ort.addr_street
                     || ' '
                     || role_ort.addr_street_number
                     || CASE
                            WHEN role_ort.addr_street_number_addition
                                     IS NOT NULL
                            THEN
                                ' ' || role_ort.addr_street_number_addition
                        END
                     || DECODE (role_ort.addr_street_number, NULL, '', ', ')
                     || role_ort.addr_post_code
                     || ' '
                     || role_ort.addr_city
                     || DECODE (role_ort.addr_street_number, NULL, '', ', ')
                     || role_ort.ctrc_name
                         betriebsstandort_adresse,
                     inse.inse_license_nr
                         AS license_number

                        
 
                       --select count( distinct role_prcd_id) /*+ monitor */  
                       FROM  role 
                      INNER JOIN  prty ON role_prty_id = prty_id 
                      INNER JOIN procedure prcd ON prcd.prcd_id = role_prcd_id and prcd.prcd_is_pub_on_ext_gui = 1
                      INNER JOIN ctl_role_term ON     role_roltc_id = roltc_id AND roltc_code IN ('ROLTC_ANBR', 'ROLTC_BEVOLLM_VERFAHR') 
                      INNER JOIN proc_folder_asgn prfa ON prfa.prfa_prcd_id = role_prcd_id
                      INNER JOIN  insf ON prfa_prof_id = insf_prof_id
                     
                      INNER JOIN ins_eligibility inse ON inse.inse_id = insf.insf_inse_id
                      LEFT JOIN pttc  ON prcd.prcd_pttc_id = pttc.term_id
                      
                      INNER JOIN proc_type_group_asgn ptga ON ptga.ptga_pttc_id = pttc.term_id
                      LEFT JOIN prsc ON prsc.term_id = prcd.prcd_pstc_id AND pttc.lngc_code = prsc.lngc_code
                      INNER JOIN  groc /*THIS SECTION BRINGS 6 seconds more*/
                      ON     groc.term_id = ptga.ptga_grotc_id
                      AND (   
                                (    groc.term_code = 'GROTC_INS_ESERVICE_ERFASST' AND prsc.term_code NOT IN ('PRSC_ABGE') AND prsc.term_code NOT IN ('PSTC_ABGEBRO') )
                                 OR (    groc.term_code ='GROTC_INS_ESERVICE_BEARBEITUNG' AND prsc.term_code IN
                                             ( SELECT prsc2.term_code
                                                FROM  groc
                                                     INNER JOIN
                                                     proc_state_group_term_asgn
                                                     psgta
                                                         ON groc.term_id =
                                                            psgta.psgta_grotc_id
                                                     INNER JOIN
                                                     prsc prsc2
                                                         ON prsc2.term_id =
                                                            psgta.psgta_pstc_id
                                               WHERE     groc.term_code =
                                                         'GROTC_INS_ESERVICE_BEARBEITUNG'
                                                     AND groc.lngc_code =
                                                         prsc.lngc_code  )
                

and Chris said...

That's a complex query with lots of hints in! I don't know why it's not using the result cache in this example.

To understand what's going on, look at the hint report in the plan.

You can get this by doing something like:

set serveroutput off
select /*+ result_cache */ * from <your query>;

select * 
from   dbms_xplan.display_cursor( format => 'BASIC LAST +HINT_REPORT');


At the bottom of the plan you'll see a section like this:

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
 
   1 -  SEL$F5BB74E1
           -  result_cache


This should help you understand why it's not doing what you expect. If you're still struggling, post the output of it here and we'll help out.


And also - your query has SYSDATE in there, which changes every single time you run the query. How would you expect a result cache to manage this?

Rating

  (2 ratings)

Comments

Output of select * from dbms_xplan.display_cursor( format => 'BASIC LAST +HINT_REPORT');

A reader, November 11, 2024 - 4:28 pm UTC

Hi Chris, thanks fpr your support.
In gv$result_cache_objects I see this sql will be bypassed. How can I know why ?

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+ result_cache */ insf_prof_id,insf_inse_id from
ins_procedure_folder

Plan hash value: 3672323742

-----------------------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 1 | RESULT CACHE | 6wm29gyatkq2y9fs20jaf5cjfg |
| 2 | TABLE ACCESS STORAGE FULL| INS_PROCEDURE_FOLDER |
-----------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

1 -


20 Zeilen ausgewõhlt.
Chris Saxon
November 11, 2024 - 5:23 pm UTC

I'm confused as to what the problem is here. The plan shows the query using the result cache (RESULT CACHE | 6wm29gyatkq2y9fs20jaf5cjfg).

Why do you think it's not using the result cache?

A reader, November 11, 2024 - 5:39 pm UTC

Hi Chris,
seeing in gv$result_cache_objects for this query as status "bypassed" I thought something is not godd working.
Chris Saxon
November 12, 2024 - 2:00 pm UTC

Verify that the parameter RESULT_CACHE_MAX_SIZE has not been set to zero and check the status of the result cache by running:

execute dbms_result_cache.memory_report


For more detail review the notes on configuring the result cache in the docs https://docs.oracle.com/en/database/oracle/oracle-database/23/tgdba/tuning-result-cache.html#GUID-B4EB65AF-CBDB-4340-A526-254401AEA8D2

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library