How do you "globally" tune a SQL query so it's driven by records in a view if the view name keeps changing but query structure stays the same?

Question

I have a Crystal report running through an application that takes a long time to run due to an inefficient query that takes 15 minutes. We are running Oracle 19.4. CURSOR_SHARING = FORCE for the database and this is required per the vendor. See query below.

The problem is, the view name in the query (such as TW_RPT_11263_7833_199916 in the example below) changes based on the query run inside the application to provide a filtered list of record IDs. Each time the report is run based on a different application query, there is a different SQL ID depending on that particular view's selection criteria.

So, a SQL profile can be generated but it only works for one query/one view. Generating a SQL Profile even with the FORCE option did not make the query faster when it has a different view name TW_RPT_####_#####, and it did not use the sql_profile as seen in v$sql.

Adding a hint to the query works great; the query runs in 1 second (see SQL below). However with a different view name per user, this means that applying a hint would only work for one view and that specific query ID. Also I do not know how it would be possible to inject this hint; it's a Crystal report. Also I do not know if it's possible to use hints with pattern matching such as /*+ USE_HASH(TW_RPT_%) */ or to use some other technique that would change the hint depending on the view name.

The PR table has 2 million rows, whereas the view only has a few rows, and so the view needs to drive the query.

QUERY with hint USE_HASH takes <1 second, whereas without hint, it takes 15 minutes:

SELECT /*+ USE_HASH(TW_RPT_11263_7833_199916)*/ "PR"."ID", "PR"."NAME", "TW_V_IMPACT_LEVEL"."S_VALUE", "PROJECT"."NAME", "PR_1"."ID", "PROJECT_1"."NAME", "PR_1"."NAME", "PR_STATUS_TYPE_1"."NAME", "PR_STATUS_TYPE"."NAME", "PR_1"."PARENT_ID", "PROJECT_2"."NAME", "TW_RPT_11263_7833_199916"."ID", "TW_V_DESCRIPTION"."TEXT", "TW_V_MATERIAL_CONTINUATION_DEC"."TEXT", "TW_V_DESCRIPTION_1"."TEXT", "TW_V_JUSTIFICATION"."TEXT", "TW_V_CLOSURE_SUMMARY"."TEXT", "TW_V_QI_CLOSURE_SUMMARY"."TEXT" FROM   (((((((((((((("TRACKWISE_OWNER"."PR" "PR" LEFT OUTER JOIN "TRACKWISE_OWNER"."PR" "PR_1" ON "PR"."ID"="PR_1"."ROOT_PARENT_ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_DESCRIPTION" "TW_V_DESCRIPTION" ON "PR"."ID"="TW_V_DESCRIPTION"."PR_ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_MATERIAL_CONTINUATION_DEC" "TW_V_MATERIAL_CONTINUATION_DEC" ON "PR"."ID"="TW_V_MATERIAL_CONTINUATION_DEC"."PR_ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_IMPACT_LEVEL" "TW_V_IMPACT_LEVEL" ON "PR"."ID"="TW_V_IMPACT_LEVEL"."PR_ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."PROJECT" "PROJECT" ON "PR"."PROJECT_ID"="PROJECT"."ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."PR_STATUS_TYPE" "PR_STATUS_TYPE" ON "PR"."STATUS_TYPE"="PR_STATUS_TYPE"."ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_CLOSURE_SUMMARY" "TW_V_CLOSURE_SUMMARY" ON "PR"."ID"="TW_V_CLOSURE_SUMMARY"."PR_ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_QI_CLOSURE_SUMMARY" "TW_V_QI_CLOSURE_SUMMARY" ON "PR"."ID"="TW_V_QI_CLOSURE_SUMMARY"."PR_ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."PROJECT" "PROJECT_1" ON "PR_1"."PROJECT_ID"="PROJECT_1"."ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_DESCRIPTION" "TW_V_DESCRIPTION_1" ON "PR_1"."ID"="TW_V_DESCRIPTION_1"."PR_ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."PR_STATUS_TYPE" "PR_STATUS_TYPE_1" ON "PR_1"."STATUS_TYPE"="PR_STATUS_TYPE_1"."ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."PR" "PR_2" ON "PR_1"."PARENT_ID"="PR_2"."ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_JUSTIFICATION" "TW_V_JUSTIFICATION" ON "PR_1"."ID"="TW_V_JUSTIFICATION"."PR_ID") LEFT OUTER JOIN "TRACKWISE_OWNER"."PROJECT" "PROJECT_2" ON "PR_2"."PROJECT_ID"="PROJECT_2"."ID") INNER JOIN "TRACKWISE_OWNER"."TW_RPT_11263_7833_199916" "TW_RPT_11263_7833_199916" ON "PR"."ID"="TW_RPT_11263_7833_199916"."ID" WHERE  ("PROJECT"."NAME"='Quality Investigation - SC' OR "PROJECT"."NAME"='Quality Issue')

I am looking for any ideas to help Oracle figure out the best join order for a query having this structure, regardless of the name of the view (TW_RPT_####-######). An assumption can definitely be made that the view will always have considerably fewer rows than the PR table.

Here is an example view created by the application based on what the end user specifies in the application query before running the report:

**TW_RPT_11263_7833_199916:**
  CREATE OR REPLACE FORCE EDITIONABLE VIEW "TRACKWISE_OWNER"."TW_RPT_11263_7833_199916" ("ID") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  SELECT DISTINCT PR.id 
FROM 
    pr, project , Project_member, Group_member 
WHERE 
    project.id = pr.project_id AND 
    pr.id IN (
        SELECT 
            pr_addtl_data.pr_id 
        FROM 
            pr_addtl_data 
        WHERE 
            pr_addtl_data.pr_id = pr.id AND 
            pr_addtl_data.data_field_id = 573 AND 
            pr_addtl_data.n_value IN (6164231)
    ) AND PR.project_parent_id IN(366,279,395,396) AND Project_member.project_id = PR.project_parent_id AND Group_member.project_member_id = Project_member.id AND Project_member.person_rel_id = 13836 AND ((Project_member.view_all = 1) OR (Project_member.view_self_created = 1 and PR.created_by_rel_id = 13836) OR (Project_member.view_assigned_to = 1 and PR.responsible_rel_id = 13836) OR (Project_member.view_group_created = 1 and PR.user_group_id = Group_member.user_group_id) OR (Project_member.view_by_entity = 1 and PR.entity_id = 1251));

The result from the view is two record IDs as follows, and this returns in milliseconds: 2012202 and 2012397

rather than using hints, you should ask why Oracle is not using a HASH JOIN in the first place. Are statistics being calculated correctly and oftenly ? If you are calculating statistics regularly and the plan is still the issue, probably Oracle is using NESTED LOOPS instead of HASH JOIN for a reason ( indexes perhaps ? ) . On the other hand, although the view name changes, if the query behind is the same, there are other techniques much better than SQL Profiles to force a different plan, without touching the view or the code in Cristal Reports.... — Roberto Hernandez, Sep 11 '21 at 22:29
... One idea would be to use SQL Baselines. I did that myself to change the plan of a query of a close product without touching the query — Roberto Hernandez, Sep 11 '21 at 22:29
Do you have a control over the query generation process? You may use some constant table alias and specify it inside hint. Then you will not need to care about table name — astentx, Sep 12 '21 at 10:19
Have you considered asking the customer support team from Crystal about this? It's an expensive software product and they do have competent suport staff. It's worth a try. — O. Jones, Sep 12 '21 at 18:16
Statistics are calculated recently and not stale. No additional recommended indexes came from the 8 hour long SQL tuning recommendation of the query. The query behind the view keeps changing and so does the view name and hence I do not know whether sql baseline would work here except for one query at a time. I like the idea of asking the Crystal Reports vendor, if we cannot get support through the application vendor. — Doug Weigle, Sep 13 '21 at 01:23
Robert about your suggestion, I like the idea that Oracle should figure it out, ane one article said to ensure to enable histograms on the table during db statistics but without any specifics I really do not know what to do there. Maybe since db statistics were calculated when the system was not in use, any histograms calculated were not accurate? I think it could be worth a try to run db statistics with histograms as users are signing off the system. — Doug Weigle, Sep 13 '21 at 02:04

score 0 · Answer 1 · answered Sep 11 '21 at 19:27

0

One option is to use a Command as the data source for the report. A parameter can control the Table/View used in the Command syntax.

answered Sep 11 '21 at 19:27

MilletSoftware

3,521
2
12
15

This is an interesting idea, thanks. Do you know if it is possible to include Oracle SQL query hints in the SQL when building commands? I do not know if this is possible. – Doug Weigle Sep 13 '21 at 01:57

score 0 · Answer 2 · answered Sep 11 '21 at 22:15

Just use better naming for aliases: use something more common than TW_RPT_11263_7833_199916. For example, instead of INNER JOIN "TRACKWISE_OWNER"."TW_RPT_11263_7833_199916" "TW_RPT_11263_7833_199916" use INNER JOIN "TRACKWISE_OWNER"."TW_RPT_11263_7833_199916" "TW_RPT_JOINED" and use it for your hints

SELECT /*+ USE_HASH(TW_RPT_JOINED)*/ 
    "PR"."ID", 
    "PR"."NAME", 
    "TW_V_IMPACT_LEVEL"."S_VALUE", 
    "PROJECT"."NAME", 
    "PR_1"."ID", 
    "PROJECT_1"."NAME", 
    "PR_1"."NAME", 
    "PR_STATUS_TYPE_1"."NAME", 
    "PR_STATUS_TYPE"."NAME", 
    "PR_1"."PARENT_ID", 
    "PROJECT_2"."NAME", 
    "TW_RPT_JOINED"."ID", 
    "TW_V_DESCRIPTION"."TEXT", 
    "TW_V_MATERIAL_CONTINUATION_DEC"."TEXT", 
    "TW_V_DESCRIPTION_1"."TEXT", 
    "TW_V_JUSTIFICATION"."TEXT", 
    "TW_V_CLOSURE_SUMMARY"."TEXT", 
    "TW_V_QI_CLOSURE_SUMMARY"."TEXT" 
FROM   
    (
        (
            (
                (
                    (
                        (
                            (
                                (
                                    (
                                        (
                                            (
                                                (
                                                    (
                                                        ("TRACKWISE_OWNER"."PR" "PR" 
                                                        LEFT OUTER JOIN "TRACKWISE_OWNER"."PR" "PR_1" 
                                                            ON "PR"."ID"="PR_1"."ROOT_PARENT_ID"
                                                        ) 
                                                    LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_DESCRIPTION" "TW_V_DESCRIPTION" 
                                                        ON "PR"."ID"="TW_V_DESCRIPTION"."PR_ID"
                                                    ) 
                                                    LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_MATERIAL_CONTINUATION_DEC" "TW_V_MATERIAL_CONTINUATION_DEC" 
                                                        ON "PR"."ID"="TW_V_MATERIAL_CONTINUATION_DEC"."PR_ID"
                                                ) 
                                                LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_IMPACT_LEVEL" "TW_V_IMPACT_LEVEL" 
                                                    ON "PR"."ID"="TW_V_IMPACT_LEVEL"."PR_ID"
                                            ) 
                                            LEFT OUTER JOIN "TRACKWISE_OWNER"."PROJECT" "PROJECT" 
                                                ON "PR"."PROJECT_ID"="PROJECT"."ID"
                                        ) 
                                        LEFT OUTER JOIN "TRACKWISE_OWNER"."PR_STATUS_TYPE" "PR_STATUS_TYPE" 
                                            ON "PR"."STATUS_TYPE"="PR_STATUS_TYPE"."ID"
                                    ) 
                                    LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_CLOSURE_SUMMARY" "TW_V_CLOSURE_SUMMARY" 
                                        ON "PR"."ID"="TW_V_CLOSURE_SUMMARY"."PR_ID"
                                ) 
                                LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_QI_CLOSURE_SUMMARY" "TW_V_QI_CLOSURE_SUMMARY" 
                                    ON "PR"."ID"="TW_V_QI_CLOSURE_SUMMARY"."PR_ID"
                            ) 
                            LEFT OUTER JOIN "TRACKWISE_OWNER"."PROJECT" "PROJECT_1" 
                                ON "PR_1"."PROJECT_ID"="PROJECT_1"."ID"
                        ) 
                        LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_DESCRIPTION" "TW_V_DESCRIPTION_1" 
                            ON "PR_1"."ID"="TW_V_DESCRIPTION_1"."PR_ID"
                    ) 
                    LEFT OUTER JOIN "TRACKWISE_OWNER"."PR_STATUS_TYPE" "PR_STATUS_TYPE_1" 
                        ON "PR_1"."STATUS_TYPE"="PR_STATUS_TYPE_1"."ID"
                ) 
                LEFT OUTER JOIN "TRACKWISE_OWNER"."PR" "PR_2" 
                    ON "PR_1"."PARENT_ID"="PR_2"."ID"
            ) 
            LEFT OUTER JOIN "TRACKWISE_OWNER"."TW_V_JUSTIFICATION" "TW_V_JUSTIFICATION" 
                ON "PR_1"."ID"="TW_V_JUSTIFICATION"."PR_ID"
        ) 
        LEFT OUTER JOIN "TRACKWISE_OWNER"."PROJECT" "PROJECT_2" 
            ON "PR_2"."PROJECT_ID"="PROJECT_2"."ID"
    ) 
    INNER JOIN "TRACKWISE_OWNER"."TW_RPT_11263_7833_199916" "TW_RPT_JOINED" 
        ON "PR"."ID"="TW_RPT_JOINED"."ID" 
WHERE  ("PROJECT"."NAME"='Quality Investigation - SC' OR "PROJECT"."NAME"='Quality Issue')

PS. "Wonderful" SQL generator - why so many (((()))))...

that is the Java generator of Cristal Reports. I saw it as well in SAP Business Objects. Really wonderful indeed :) — Roberto Hernandez, Sep 11 '21 at 22:40
I tried the generic alias name instead of the specific view name and it worked! Now I need to figure out how to get Crystal reports to use the generic alias name, and also embed the hint into the query. Any ideas there? — Doug Weigle, Sep 13 '21 at 01:17

How do you "globally" tune a SQL query so it's driven by records in a view if the view name keeps changing but query structure stays the same?

2 Answers2