-
1. Re: Tune select statement
KayK Nov 15, 2016 11:29 AM (in response to Raj012345)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 Nov 15, 2016 11:35 AM (in response to Raj012345)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 Nov 15, 2016 11:59 AM (in response to Raj012345)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
Peter.Sorger Nov 15, 2016 11:47 AM (in response to Raj012345)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
Raj012345 Nov 15, 2016 12:00 PM (in response to KayK)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 Nov 15, 2016 11:58 AM (in response to Raj012345)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 Nov 15, 2016 1:43 PM (in response to Raj012345)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 Nov 15, 2016 1:11 PM (in response to Raj012345)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 Nov 15, 2016 1:12 PM (in response to Raj012345)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 Nov 15, 2016 3:33 PM (in response to Raj012345)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