Hi All,
Apologies for the direct approach, but I'm quite stuck at the moment with an SQL query which is timing out ( it's a BI Publisher report with 30 mins timeout).
Attaching(because they are quite big) the query + explain plan (in xml format as it's not allowed to attache sql files).
So basically the query has few WITH clauses(parametrized) which I then use in several UNION ALL queries. The rows expected are not that big - approximately 100k rows, but the complexity of the joins probably caused the timeout error.
Looking at the explain plan, it seems that one of my problems is in XLA temporary table(WITH clause) where XLA_TRANSACTION_ENTITIES(53429 rows) and XLA_AE_HEADERS(100247 rows) tables are 'TABLE ACCESS STORAGE FULL'.
I tried using a leading hint on XLA_AE_HEADERS as this is the table which is getting filtered based on the :p_period_name parameter, but no significant effect.
If you can guide me from where to start improving this or any hints - that would be much appreciated!
Oracle DB version is 12c.
Thanks in advance and sorry for the dummy approach of this post!
Aleks.