10 Replies Latest reply on Nov 15, 2016 3:33 PM by ddf_dba

    Tune select statement

    975791

      Hi All,

       

       

      We have the table ACC_REP which has around 7 core record. No Partition on that table.

      Index on RES_Value .The below query takes around 20 hours to execute it.

       

       

      Query

      -----------

      SELECT   V_D,

                 L_ZONE_D,

                 pK_ID,

                 post_iD,

                 CRI_AS_iD,

                 AS_DEP_ID,

                 BILL_TI,

                 RE_TY EV_T,

                 PUR_DATe,         

                 SUBSTR (PUR_TI, 1, 2) AS PUR_TI,

                 MAX (CON_ID) CON_ID

                 MAX (SER_ID) SER_ID,

                 MAX (GEN) GEN,

                 MAX (RATE RATE,

                 MAX (AD_IN) AD_IN,

                 NULL V_AS_START_DT,

                 NULL V_AS_END_DT,

                 'Y' IS_BILL,

                 COUNT (AS_EVENT_ID) PUR_COUNT,

                 SUM (BILL_total) PUR_AMOUNT

          FROM   ACC_REP

         WHERE   RES_Value = 'PUR'

                 AND TRUNC (TO_DATE (PUR_DATE, 'dd/mm/yyyy')) >=

                       TRUNC (SYSDATE - 1)

                 AND TRUNC (TO_DATE (PUR, 'dd/mm/yyyy')) <=

                       TRUNC (SYSDATE - (1-1))

      GROUP BY   V_D,

                 L_ZONE_D,

                pK_ID,

                 ZIP_CD,

                 CRI_AS_iD,

                 AS_DEP_ID,

                 BILL_TI,

                 RE_TY,

                 PUR_DATe,

                 SUBSTR(PUR_TI,1,2)

       

       

      how to tune the above one

       

      Thanks,

        • 1. Re: Tune select statement
          KayK

          Hi,

          sorry but how many rows are "7 core records" ?

          What indexes do you have ?

          What's the datatype of PUR_DATE ?

          What is column PUR or is it a typo ?

          Why do you use the TRUNC around your date columns ? You use trunc around the sysdate, that seems enough.

          Please post the execution plan.

          regards

          Kay

          • 2. Re: Tune select statement
            Andrew Sayer

            So how many rows in that table fit the predicate of RES_Value = 'PUR'?

            Is the index actually being used?

            Should the index also allow you to filter on one of your date column as well or does that not reduce the number of blocks used by much?

            Have you seen the actual execution plan being used? Where is the time going?

            What is the relation between pur and pur_date? Is there one?

             

            Maybe you'd benefit from filtering directly on your date columns rather than a function of them.

            • 3. Re: Tune select statement
              Mustafa KALAYCI

              you should provide much more information at least your execution plan but since your query is too simple:

               

                  FROM   ACC_REP
                 WHERE   RES_Value = 'PUR'
                         AND TRUNC (TO_DATE (PUR_DATE, 'dd/mm/yyyy')) >= TRUNC (SYSDATE - 1)
                         AND TRUNC (TO_DATE (PUR, 'dd/mm/yyyy')) <= TRUNC (SYSDATE - (1-1))
              

               

              this part is important for you. how many rows does this table has? how many of them has "PUR" value for res_value column? Are PUR_DATE and PUR columns varchar2 or date? when you run query above (with "select *", no group by) how many rows are returning? does your table statistics up to date ?

               

              ps: Andrew Sayer I wrote this post without seeing yours, you already ask whatever I asked.

              • 4. Re: Tune select statement
                psorger

                Please find the query:

                select sql_id, sql_text from v$sqlarea where upper(sql_text) like 'SELECT%V_D%SUBSTR%MAX%ACC_REP%' ;

                from the sql_text identify your problematic query and then use:

                select * from table(dbms_xplan.display_cursor('sql_id',null,'advanced')) ;

                 

                Then please post the execution plan here as an attachment, it will be easier to read as a text file then in the bad format that you would post here (maybe)

                • 5. Re: Tune select statement
                  975791

                  Hi All,

                   

                   

                  Thanks for your response......

                   

                  What is column PUR or is it a typo ?

                  it is typo error.it is PUR_DATE,not  PUR.

                  What's the datatype of PUR_DATE ?

                  PUR_DATE is  varchar2(30)

                   

                  What indexes do you have ?

                  Non unique index

                   

                  Why do you use the TRUNC around your date columns ? You use trunc around the sysdate, that seems enough.

                  I couldn't understand it

                   

                  does your table statistics up to date ?

                  Every week we gather  stats.

                   

                  Any Suggestions

                   

                  Thanks,

                  • 6. Re: Tune select statement
                    Mustafa KALAYCI

                    please see Andrew's and mine posts. rowcounts are important.

                    for pure_Date, you can write like this:

                     

                    AND PUR_DATE >= TRUNC (SYSDATE - 1)

                    AND PUR_DATE <= TRUNC (SYSDATE - (1-1))

                     

                    but it may not increase performance, do you have any index that contain, PUR_DATE and RES_VALUE ? please give some info about row counts.

                    • 7. Re: Tune select statement
                      Andrew Sayer

                      Up to date statistics does not mean representative.

                       

                      How many rows/blocks should your predicates return?

                      Don't use to_date on a timestamp.

                      No need to trunc your columns

                       

                      Change predicates to

                      FROM   ACC_REP
                         WHERE   RES_Value = 'PUR'
                                 AND PUR_DATE >=
                                       TRUNC (SYSDATE - 1)
                                 AND pur_date <=
                                       TRUNC (SYSDATE - (1-1))

                       

                      And maybe index res_value, pur_date.

                      Share execution plan and the actual selectivities if that doesn't solve your problem.

                       

                      -edit

                      It seems you editted your post after Id seen it and really the column is a varchar2. Were you not aware of this? Are you aware of how many problems this causes you? Can you fix the table so you are storing data in the correct types and therefore constrain the data properly and give the cost based optimizer the minimum amount of information it needs (stats would also be useful).

                      • 8. Re: Tune select statement
                        AnnEdmund

                        975791 wrote:

                         

                        Hi All,

                         

                         

                        Thanks for your response......

                         

                        What is column PUR or is it a typo ?

                        it is typo error.it is PUR_DATE,not PUR.

                        What's the datatype of PUR_DATE ?

                        PUR_DATE is varchar2(30)

                         

                        What indexes do you have ?

                        Non unique index

                         

                        Why do you use the TRUNC around your date columns ? You use trunc around the sysdate, that seems enough.

                        I couldn't understand it

                         

                        does your table statistics up to date ?

                        Every week we gather stats.

                         

                        Any Suggestions

                         

                        Thanks,

                         

                        First of all, the issue is storing date as VARCHAR2. Fix that and store the date as DATE.

                         

                        Thanks,

                        Ann Edmund

                        • 9. Re: Tune select statement
                          JohnWatson2

                          The fact that PUR_DATE is a varchar2 makes this awkward. Predicates such as

                           

                          AND PUR_DATE >= TRUNC (SYSDATE - 1)

                           

                          may be disastrous, because they will be relying on implicit type casting. Don't even think about it.

                           

                          You do need to say how many rows you expect from the combined predicate and how many for each of the predicates individually. Then you could consider creating a virtual column populated as to_date(pur_date, 'dd/mm/yyyy') and creating a composite index on RES_VALUE plus the virtual column (or perhaps the other way around) and adjusting your query to filter on the virtual column.

                           

                          Note that you may have a problem with statistics. It would seem likely that analyzing once a week means that your stats know nothing of rows entered in the last day.

                          • 10. Re: Tune select statement
                            ddf_dba

                            975791 wrote:

                             

                            Hi All,

                             

                             

                            Thanks for your response......

                             

                            What is column PUR or is it a typo ?

                            it is typo error.it is PUR_DATE,not PUR.

                            What's the datatype of PUR_DATE ?

                            PUR_DATE is varchar2(30)

                             

                            What indexes do you have ?

                            Non unique index

                             

                            Why do you use the TRUNC around your date columns ? You use trunc around the sysdate, that seems enough.

                            I couldn't understand it

                             

                            does your table statistics up to date ?

                            Every week we gather stats.

                             

                            Any Suggestions

                             

                            Thanks,

                            As you've been told the design of this table is seriously flawed, using a VARCHAR2 datatype rather than using DATE.  The query relies on implicit date conversion, which I would't rely on for production data.  I blogged on such mistakes in design here:

                             

                            https://dfitzjarrell.wordpress.com/2016/02/17/that-aint-right/

                             

                            Additionally what you're doing is causing problems with the optimizer since Oracle can't know these values are dates; looking at an example using a VARCHAR2 column storing the date it produces incorrect results:

                             

                            SQL> create table datetst(

                              2  myid       number,

                              3  mydt       varchar2(20));

                             

                            Table created.

                             

                            SQL>

                            SQL> begin

                              2          for i in 1..100 loop

                              3                  insert into datetst

                              4                  values(i, to_char(sysdate+i, 'MM/DD/RRRR'));

                              5          end loop;

                              6

                              7          commit;

                              8  end;

                              9  /

                             

                            PL/SQL procedure successfully completed.

                             

                            SQL>

                            SQL> select *

                              2  from datetst

                              3  where mydt <= sysdate +10;

                            where mydt <= sysdate +10

                                  *

                            ERROR at line 3:

                            ORA-01843: not a valid month

                             

                             

                            SQL>

                            SQL> select *

                              2  from datetst

                              3  where mydt <= to_char(sysdate +10, 'MM/DD/RRRR');

                             

                                  MYID MYDT

                            ---------- --------------------

                                     1 11/16/2016

                                     2 11/17/2016

                                     3 11/18/2016

                                     4 11/19/2016

                                     5 11/20/2016

                                     6 11/21/2016

                                     7 11/22/2016

                                     8 11/23/2016

                                     9 11/24/2016

                                    10 11/25/2016

                                    47 01/01/2017

                             

                                  MYID MYDT

                            ---------- --------------------

                                    48 01/02/2017

                                    49 01/03/2017

                                    50 01/04/2017

                                    51 01/05/2017

                                    52 01/06/2017

                                    53 01/07/2017

                                    54 01/08/2017

                                    55 01/09/2017

                                    56 01/10/2017

                                    57 01/11/2017

                                    58 01/12/2017

                             

                                  MYID MYDT

                            ---------- --------------------

                                    59 01/13/2017

                                    60 01/14/2017

                                    61 01/15/2017

                                    62 01/16/2017

                                    63 01/17/2017

                                    64 01/18/2017

                                    65 01/19/2017

                                    66 01/20/2017

                                    67 01/21/2017

                                    68 01/22/2017

                                    69 01/23/2017

                             

                                  MYID MYDT

                            ---------- --------------------

                                    70 01/24/2017

                                    71 01/25/2017

                                    72 01/26/2017

                                    73 01/27/2017

                                    74 01/28/2017

                                    75 01/29/2017

                                    76 01/30/2017

                                    77 01/31/2017

                                    78 02/01/2017

                                    79 02/02/2017

                                    80 02/03/2017

                             

                                  MYID MYDT

                            ---------- --------------------

                                    81 02/04/2017

                                    82 02/05/2017

                                    83 02/06/2017

                                    84 02/07/2017

                                    85 02/08/2017

                                    86 02/09/2017

                                    87 02/10/2017

                                    88 02/11/2017

                                    89 02/12/2017

                                    90 02/13/2017

                                    91 02/14/2017

                             

                                  MYID MYDT

                            ---------- --------------------

                                    92 02/15/2017

                                    93 02/16/2017

                                    94 02/17/2017

                                    95 02/18/2017

                                    96 02/19/2017

                                    97 02/20/2017

                                    98 02/21/2017

                                    99 02/22/2017

                                   100 02/23/2017

                             

                            64 rows selected.

                             

                            SQL>

                             

                            The first error occurs because of the default NLS date format; an explicit conversion needed to take place to get any results from the query.  Notice also that this query returned 64 rows of data when the expected output should have been no more than 10 rows (given the data in the table).

                             

                            Fixing that (and, yes, I know you can't fix this by changing the table definition) a different result is presented:

                             

                            SQL> drop table datetst purge;

                             

                            Table dropped.

                             

                            SQL>

                            SQL> create table datetst(

                              2  myid       number,

                              3  mydt       date);

                             

                            Table created.

                             

                            SQL>

                            SQL> begin

                              2          for i in 1..100 loop

                              3                  insert into datetst

                              4                  values(i, sysdate+i);

                              5          end loop;

                              6

                              7          commit;

                              8  end;

                              9  /

                             

                            PL/SQL procedure successfully completed.

                             

                            SQL>

                            SQL> select *

                              2  from datetst

                              3  where mydt <= sysdate +10;

                             

                                  MYID MYDT

                            ---------- ---------

                                     1 16-NOV-16

                                     2 17-NOV-16

                                     3 18-NOV-16

                                     4 19-NOV-16

                                     5 20-NOV-16

                                     6 21-NOV-16

                                     7 22-NOV-16

                                     8 23-NOV-16

                                     9 24-NOV-16

                                    10 25-NOV-16

                             

                            10 rows selected.

                             

                            SQL>

                             

                            Now the 10 correct rows are returned and there wasn't any 'fiddling' with the query to get things to process. 

                             

                            Part of your problem, I believe, goes back to the first part of this example where Oracle returned 64 rows when only 10 should have met the criteria.  I expect the poor decision of making the PUR_DATE column a VARCHAR2 datatype is causing far more records to be returned, records that do NOT actually meet the criteria, inflating the result set needlessly and causing more work for Oracle.

                             

                             

                            David Fitzjarrell