Hi,
I have a performance issue with a query during an application upgrade of a production system
I let it 15 hours now and it's still running ...
If I remove the ORDER BY clause, it take less than one minute but I can't remove it as sorting is important for next steps upgrade
select task_id, r.result_id, r.result_version, planned_result, component, measure, units, r2.text_value
from nai_results r,
(select result_id,
result_version,
text_value
from (select count(1) over(partition by result_id, result_version) record_count,
result_id,
result_version,
attr.text_value
from nai_results res,
table(attributes) attr
where attr.name = 'Calculation')
where record_count=1) r2
where task_id in (select task_id from nai_results, table(attributes) a1 where a1.name = 'Calculation')
and r.result_id = r2.result_id
and r.result_version = r2.result_version
order by task_id, planned_result;
I did a "test" upgrade one year ago, with around 10% less data and it took less than one minute.
I have compared both database running "show parameters" and they are the same, only a little difference on shared_pool_reserved_size (10066329 in old db vs 10905190 in new db)
But I think when I created the new database, I have speciffied different SGA/PGA but I don't know how to know the size and how to change it as the "test" database
Regards,
Patrick