Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

When your query takes too long ...

Rob van WijkApr 27 2007 — edited Feb 23 2009
This thread is not a question.

I will use this thread to refer to in other threads, to keep me from saying the same thing over and over again.

What to do when your query is too slow?

First of all, you have to know why it is slow. What is the real cause of your problem. If the reason why is not known, suggesting to rewrite the query, or hinting the query, suggesting parallellization et cetera is not very productive. Once in a while you may get lucky. But even then you have to realize that if your problem seems "solved", but you don't know why, nothing guarantees you that the problem won't come back tomorrow. So the first step should always be to investigate the root cause.

The tools at your disposal are, among more:
- dbms_profiler
- explain plan
- SQL*Trace / tkprof
- statspack

Use dbms_profiler if you want to know where time is being spent in PL/SQL code. Statspack is a must if you are a dba and want to know what is going on in your entire database. For a single query or a small process, explain plan and SQL*Trace and tkprof are your tools.

explain plan

in SQL*Plus you have to type:
explain plan for <your query>;
select * from table(dbms_xplan.display);
When you get error messages or a message complaining about an old version of plan_table, make sure you run the script utlxplan.sql.

The output you get here basically shows you what the cost based optimizer expects. It gives you an idea on why the cost based optimizer chooses an access path.

SQL*Trace/tkprof

For this you have to type in SQL*Plus:
- alter session set sql_trace true;
- <run your query>
- disconnect (this step is important, because it ensures all cursors get closed, and "row source operation" is generated)
- identify your trace file in the server directory as specified in the parameter user_dump_dest
- on your operating system: tkprof <trace file> a.txt sys=no sort=prsela exeela fchela

The file a.txt will now give you valuable information on what has actually happened. No predictions but the truth.

By comparing the output from explain plan with the output from tkprof, you are able to identify the possible problem areas.

So before rushing into possible solutions, always post the output of explain plan and tkprof with your question and don't forget to post them between the tags {noformat}
{noformat} and {noformat}
{noformat} for readability.

PS: I am fully aware that this text is only a tiny fraction of what can be done, and that other people may choose different tools and actions, but the above gives you a very reasonable start at solving your performance problem.

Edited by: Rob van Wijk on 17-sep-2008 16:32

Adjusted the layout for the new forum software

Comments

thatJeffSmith-Oracle

Step 1, go get version 19.4, b/c the formatter has several improvements in the areas of proper alignment already, so that's a better baseline

1. Comments (and hints) are filtered away by parser, and are consequently ignored by formatter. To be able to process them with Custom Format, the input text must be made accessible as java object in Nashorn. I made this amendment to 20.1 as per your earlier request to be able to update the formatting timestamp. Then, a hint can be recognized in the input and, consequently, new line can be appended. However you would likely want to indent the first column with the others -- this doesn't look easy at the moment.

2. You have disabled double breaks after statements in the Advanced Options, but you need to enable them back to make the rule brkX2 working. If you don't need double line breaks under the certain conditions, then you just remove those disjuncts. Assuming you don't want any of them, here is the amended rule:

brkX2:

--brkX2_1 | brkX2_2

[node) basic_decl_item_list & [node+1) full_cursor_body

->;

The formatted output:

DECLARE

c_defaultbsstrno CONSTANT store.str_no%TYPE := 0;

c_defaultbsstrno CONSTANT store.str_no%TYPE := 0;

CURSOR l_rtltndrdtlcur IS

SELECT *

 FROM dual;

CURSOR l_rtltndrdtlcur IS

SELECT *

 FROM dual;

BEGIN

NULL;

END;

simpleIndentConditions:

...

| [node) privilege_audit_clause

--| [node) parm_list_opt & [node^) fml_part

--| [node) prm_spec & [node^) fml_part

--| [node) query_partition_clause --& [node^) analytic_clause

...

_paddedIdsInScope1: (

-- types

:alignTypeDecl & [id) identifier & [id+1) datatype & [scope) relational_properties

--| :alignTypeDecl & ( :breaksAfterComma | :breaksBeforeComma ) & [id) decl_id & ([id+1) prm_spec_unconstrained_type | [id+1) mode /*30067569:*/| [id+1) 'OUT' | [id+1) 'IN' ) & [scope) fml_part

| :alignTypeDecl & ( :breaksAfterComma | :breaksBeforeComma ) & [id) decl_id & ([id+1) constrained_type | [id+1) object_d_rhs) & [scope) adt_definition

...

_extraBrkBefore:

...

| :breaksBeforeComma & [node+1) field & [node) ','

--| :breaksBeforeComma & [node+1) prm_spec & [node) ','

| :breaksBeforeComma & [node+1) XML_attributes_clause & [node) ','

...

Output:

CREATE OR REPLACE PACKAGE BODY emp_mgmt AS

tot_emps NUMBER;

tot_depts NUMBER;

FUNCTION hire (last_name VARCHAR2,job_id VARCHAR2,manager_id NUMBER,salary NUMBER,department_id NUMBER) RETURN NUMBER IS

  new\_empno NUMBER;

BEGIN

...

format advanced.png

simpleIndentConditions:

...

| [node) alter_method_spec

--| [node) and_expr & ![node) rel

| .breaksProcArgs & [node) arg & [node^) paren_expr_list & [node+1) arg_list

...

_extraBrkBefore:

[node) 'AND' & [node+1) arith_expr |

[node) physical_properties

...

Output:

BEGIN

  IF (l\_prevstrno = l\_tndrcrctnchgrec.str\_no)

  AND (l\_prevtrmlno = l\_tndrcrctnchgrec.trml\_no)

  AND (l\_prevtxno = l\_tndrcrctnchgrec.tx\_no)

  AND (l\_prevtxdte = l\_tndrcrctnchgrec.tx\_dte)

  AND (l\_prevtrmltypcd = l\_tndrcrctnchgrec.trml\_typ\_cd)

  AND (l\_prevrundte = l\_tndrcrctnchgrec.run\_dte)

  THEN ilevel := 'Low Income';

P.S. Tested everything in 19.4, not 19.2.

Dilly1990

Thanks Vadim for your code snippets.

After migrating to 19.4, I was able to break lines and align code as needed.

However on few instance I'm unable to align the code.

Can you please help me on the following requirement,

SQL and SQL within the PL/SQL:

  • On the CASE_EXPRESSION both CASE and END need to be aligned on the same column.

     SEARCHED\_CASE\_EXPRESSION AND ELSE\_CLAUSE  need to be indent within the case statement.
    
  • Indent ON_USING_CONDITION next to JOIN_CLAUSE.

SELECT rtd.col1

  ,rtd.col2

  ,**CASE**

WHEN rtd.col1 = 'Y' THEN (rtd.col1 / 100) * - 1

ELSE (rtd.col1 / 100) * 1

END AS prod_prc

  ,t99.col1

  ,TRUNC (SYSDATE) AS run\_dte

FROM table1 rtd

JOIN table2 t99

ON rtd.str_no = t99.str_no

AND CASE

WHEN rtd.col1 = 'N' THEN rtd.col1 * - 1

ELSE rtd.col1 / 100 * 1

END = rtd.extd_prc

AND CASE

WHEN rtd.can_key_fg = 'N' THEN 'A'

ELSE 'V'

END = t99.void_fg

JOIN table2 t98

ON rtd.str_no = t98.str_no

WHERE t99.run_dte BETWEEN p_runDte AND p_runDte + 1;

Expected:

pastedImage_0.png

Vadim Tropashko-Oracle

1. Advanced Format -> Line Breaks -> IF/CASE/WHILE  = Terse (line breaks only after actions)

2. Custom Format:

simpleIndentConditions:

...

   | [node) case_expression & ![node) select_term & ![node^) "aliased_expr" & ![node^) comparison_condition

...

  | [node) on_using_condition

...

pairwiseAlignments1: predecessor = node-1 & (

    [predecessor) group_by_list[5,12)            & [node) ','

  | [predecessor) order_by_clause[31,38)          & [node) ','

  | [predecessor) select_list                    & [node) ','

  | [predecessor) cartesian_product              & [node) ','

  | [predecessor) merge_insert_clause[26,33)      & [node) ','

  | [predecessor) column                          & [node) merge_insert_clause[26,33)

  | [predecessor) "expr_list_def"[16,34)          & [node) ','

  | [predecessor) "expr_list"                    & [node) ','          -- leading commas

  | [predecessor) column                          & [node) "expr_list_def"[16,34)

  | [predecessor) merge_update_clause[36,56)      & [node) ','

  | !:alignRight & [predecessor) condition        & [node) AND_OR

  | [predecessor) expr                  & [node) compound_expression[25,44)

  | [predecessor) '>'                    & [node) expr  & [node^) arg

  | [predecessor) arith_expr            & [node)  binary_add_op

| [predecessor) 'CASE'      & [node)  searched_case_expression

);

pairwiseAlignments2: predecessor=node-1-1  & (

    [predecessor) select_list    & [node) select_term & [node-1) ','

  | [predecessor) cartesian_product      & [node) table_reference  & [node-1) ','

  | [predecessor) condition      & [node) condition  & [node-1) AND_OR

  | [predecessor) adt_field_list & [node) adt_field  & [node-1) ','

  | [predecessor) adt_field      & [node) adt_field  & [node-1) ','

  | [predecessor) "expr_list"    & [node) expr        & [node-1) ','

  | [predecessor) expr          & [node) expr      

  | !:breakOnSubqueries  & [predecessor) '('    & [node) ')'  & [node-1) query_block & ![node^) par_subquery

  | [predecessor) 'CASE'      & [node)  'END'

  | [predecessor-1) 'CASE'      & [node)  'END'

  | [predecessor) 'CASE'      & [node)  else_clause

);

additional2Spaces:

   [node) searched_case_expression

| [node) else_clause

-> { padNode(2) }

Output:

SELECT rtd.col1

      ,rtd.col2

      ,CASE

          WHEN rtd.col1 = 'Y' THEN (rtd.col1 / 100) * - 1

          ELSE (rtd.col1 / 100) * 1

        END AS prod_prc

      ,t99.col1

      ,trunc (sysdate) AS run_dte

  FROM table1 rtd

  JOIN table2 t99

   ON rtd.str_no = t99.str_no

      AND CASE

            WHEN rtd.col1 = 'N' THEN rtd.col1 * - 1

            ELSE rtd.col1 / 100 * 1

          END = rtd.extd_prc

      AND CASE

            WHEN rtd.can_key_fg = 'N' THEN 'A'

            ELSE 'V'

          END = t99.void_fg

  JOIN table2 t98

   ON rtd.str_no = t98.str_no

WHERE t99.run_dte BETWEEN p_rundte AND p_rundte + 1;

Dilly1990

Case and End are aligned, however when there are multiple WHEN's, first WHEN alignment goes slightly off.

Can you please help me on this.

SELECT rtd.col1

      ,rtd.col2

      ,CASE

              WHEN rtd.col1 = 'N'      THEN (rtd.col1 / 100) * - 1

        WHEN rtd.col1 = 'Y'      THEN (rtd.col1 / 100) * - 1

        WHEN rtd.col1 != 'Y'     THEN (rtd.col1 / 100) * - 1

          ELSE (rtd.col1 / 100) * 1

        END AS prod_prc

      ,t99.col1

      ,TRUNC (SYSDATE) AS run_dte

FROM table1 rtd;

Expected:

SELECT rtd.col1

      ,rtd.col2

      ,CASE

          WHEN rtd.col1 = 'N'      THEN (rtd.col1 / 100) * - 1

          WHEN rtd.col1 = 'Y'      THEN (rtd.col1 / 100) * - 1

          WHEN rtd.col1 != 'Y'     THEN (rtd.col1 / 100) * - 1

          ELSE (rtd.col1 / 100) * 1

        END AS prod_prc

      ,t99.col1

      ,TRUNC (SYSDATE) AS run_dte

FROM table1 rtd;

Dilly1990

-- https://community.oracle.com/thread/4202758

Another question about the pairwiseAlignments7_3, it aligns the merge_update_clause only when Line breaks on comma ->  After.

Can you please let me know what changes are needed on the 7_3 to align merge_update_clause when Line breaks on comma ->  Before.

MERGE INTO employees e

USING hr_records h ON (e.id = h.emp_id)

WHEN MATCHED THEN

UPDATE

SET e.address = h.address e.col1 = b.col1

,e.col2 = b.col2

,e.col3 = b.col3

Vadim Tropashko-Oracle

Thank you for the new test cases. Correction:

additional2Spaces:

  [node) searched_case_expression#

| [node) else_clause

-> { padNode(2) }

Output:

SELECT rtd.col1

      ,rtd.col2

      ,CASE

          WHEN rtd.col1 = 'N'      THEN (rtd.col1 / 100) * - 1

          WHEN rtd.col1 = 'Y'      THEN (rtd.col1 / 100) * - 1

          WHEN rtd.col1 != 'Y'     THEN (rtd.col1 / 100) * - 1

          ELSE (rtd.col1 / 100) * 1

        END AS prod_prc

      ,t99.col1

      ,trunc (sysdate) AS run_dte

  FROM table1 rtd;

Fixing merge statement format:

pairwiseAlignments9_1: -- https://community.oracle.com/thread/4310878

  [predecessor) column

& [node) ',' & [node+1) column

& predecessor=ancestor-3

& ancestor < node

;

pairwiseAlignments:

...

  | pairwiseAlignments9_1

...

->

;

less2Spaces:   :breaksBeforeComma & (

    [node+1) arg & [node) ','

  | [node+1) prm_spec & [node) ','

  | [node+1) select_term & [node) ','

  | [node+1) group_by_col & [node) ','              

  | [node+1) "ord_by_1desc" & [node) ','            

  | [node+1) table_reference & [node) ','

  | [node+1) par_expr_list[19,31) & [node) ','  -- in insert clause

  | [node-1) "expr_list" & [node) ','           -- in insert clause

  | [node^) query_partition_clause[14,21) & [node) ','

  | [node+1) alias_in_out_constraints & [node) ',' 

  | [node+1) column & [node+2) '=' & [node) ',' 

) -> {

    var node = tuple.get("node");      

    var nodeIndent = struct.getNewline(node.from);

    var offset = 2;

    if( !struct.getBoolBind("spaceAfterCommas") )

        offset = 1;

    if( nodeIndent != null )

        struct.putNewline(node.from, nodeIndent.substr(0,nodeIndent.length-offset));

}

Output:

MERGE INTO employees e

USING hr_records h ON (e.id = h.emp_id)

WHEN MATCHED THEN UPDATE

   SET e.address = h.address

      ,e.col1 = b.col1

      ,e.col2 = b.col2

      ,e.col3 = b.col3

To remove indentation of the SET clause:

rightAlignments: :alignRight & (

...

  | [node) 'SET'   &  ![node^) merge_update_clause

...

) -> 

;

Dilly1990

HI Vadim,

I'm trying to format Analytic functions in both SQL and PL/SQL, looks like each function (SUM, RANK, COUNT) has different grammar rules.

is there a way to format the analytic function in similar way.

Input Sql:

SELECT ORDER_CD

  ,SUM (ORDERS)

OVER (

  PARTITION BY COL1,COL2,COL3

  ORDER BY DATE1

)

COL1

  ,RANK (UNITS\_ORDERED)

OVER (

PARTITION BY COL1,COL2,COL3

ORDER BY DATE1

) COL2

  ,COUNT (UNITS\_ORDERED)

    OVER (

       PARTITION BY COL1,COL2,COL3

       ORDER BY DATE1

    )

COL3

  ,UNITS\_ORDERED

FROM DUAL

Expected:

pastedImage_0.png

Thanks,

Dilly

Vadim Tropashko-Oracle

_extraBrkBefore:

...

| [node) order_by_clause

| [node) 'OVER'

...

pairwiseAlignments00: predecessor = node^^ & (

    [predecessor) function            & [node)  'OVER'

);

pairwiseAlignments0: predecessor = node^ & (

    [predecessor) case_expression      & [node)  'END'

  | [predecessor) else_clause          & [node)  expr

  | [predecessor) over_clause          & [node)  ')'

  | [predecessor) function            & [node)  over_clause

  | [predecessor) function            & [node)  'OVER'

);

pairwiseAlignments1: predecessor = node-1 & (

...

  | [predecessor) query_partition_clause        & [node)  order_by_clause

);

pairwiseAlignments30: predecessor=node-1-1-1  & (

    [predecessor) 'OVER'          & [node) ')'

);

pairwiseAlignments:

    pairwiseAlignments00

  | pairwiseAlignments0

  | pairwiseAlignments1

  | pairwiseAlignments2

  | pairwiseAlignments30

  | pairwiseAlignments3

...

Tested with

breaksAfterSelect=false

breaksComma=Before

commasPerLine=1

SELECT order_cd

    , SUM(orders)

        OVER(PARTITION BY col1

                        , col2

                        , col3

              ORDER BY date1

        ) col1

    , RANK(units_ordered)

        OVER(PARTITION BY col1

                        , col2

                        , col3

              ORDER BY date1

        ) col2

    , COUNT(units_ordered)

        OVER(PARTITION BY col1

                        , col2

                        , col3

              ORDER BY date1

        ) col3

    , units_ordered

FROM dual

jgebal

Vadim Tropashko-Oracle wrote:

1. Comments (and hints) are filtered away by parser, and are consequently ignored by formatter. To be able to process them with Custom Format, the input text must be made accessible as java object in Nashorn. I made this amendment to 20.1 as per your earlier request to be able to update the formatting timestamp. Then, a hint can be recognized in the input and, consequently, new line can be appended. However you would likely want to indent the first column with the others -- this doesn't look easy at the moment.

Will the parser enhancements allow to distinguish between:

- single-line comments -- comment

- multi-line comments /* comment newline comment */

- single-line hint --+ hint

- multi-line hint /*+  hint newline hint */

The reason why I'm asking is that there is a significant behavioral impact if we don't do (remove) a newline after a single-line comment/hint

SELECT --+ INDEX(A)

     *

  FROM SOME_TABLE A

WHERE ID = :X;

SELECT --+ INDEX(A) *

  FROM SOME_TABLE A

WHERE ID = :X;

The example 2 in above is not a valid SQL while below, both are valid.

SELECT /*+ INDEX(A) */

  *

FROM SOME_TABLE A

WHERE ID = :X;

SELECT /*+ INDEX(A) */ *

FROM SOME_TABLE A

WHERE ID = :X;

Taking this into consideration, some formatting could actually make code invalid if using single-line comments/hints

1 - 10
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 23 2009
Added on Apr 27 2007
3 comments
191,692 views