-
Incident report
-
Resolution: Fixed
-
Major
-
3.4.7
-
Server = Centos 7 x86_64, database = Oracle 12.1 on AIX + 3par flash storage. Housekeeping is off (using partitioned history tables)
-
Sprint 30
-
0.5
After upgrade from 3.4.4 to 3.4.7 we recieved a slow query performance of the query:
select * from (select clock,ns,value from history_text where itemid=:"SYS_B_0" and clock>:"SYS_B_1" and clock<=:"SYS_B_2" order by clock desc) where rownum<=:"SYS_B_3"
where :"SYS_B_..." - are known as bind variables in Oracle.
The problem is that SQL monitoring shows that vaiable "SYS_B_1" in this query is 0 (zero value). clock<=:"SYS_B_2" is good, it contains different unix timestamp values. Final query in Oracle looks like this:
select * from (select clock,ns,value from history_text where itemid=111950 and clock>0 and clock<=1520240673 order by clock desc) where rownum<=1
All of our history tables are weekly partitioned by CLOCK value and our installation is rather big (current NVPS = 4303.62). Weekly partitions are about 47-48 billions records each. We are keeping ~15 last partitions of history tables. So the SQL plan itself is good (using local indexes in partitioned table, optimizer cost is very low), but the amount of actual fetch from history_text is large, event though there are partitions and index (itemid + clock) inside every partiton.
I wonder is there any known bug in in Oracle backend code according to clock>:"SYS_B_1"?