Thanks for the question, jj.
Asked: January 24, 2023 - 9:33 pm UTC
Last updated: January 31, 2023 - 6:47 am UTC
Version: 13
Viewed 1000+ times
You Asked
Hello,
Thanks to this forum a lot of my answers were answered.
I noticed that most of the queries in an application that am working on is using the MATERIALIZE hint in the subqueries.
Our queries heavily use CTEs and relies on multiple joins.
Our query performance is not great.
In OEM, i noticed all these queries have high user I/O and am guessing, its because all the queries that query tables of 100s million rows are materialized.
Is that true? Is it better to keep it there rather than remove it?
When should i REALLY be using materialized hint?
The queries also use
"where column1 = CASE WHEN this THEN 1 WHEN THAT THEN 2 WHEN OTHER_THAT THEN 3"
Also uses
Select NVL(this, NVL(that,99)), column, column from table;
Does the above impact performance? If so how can i improve it?
I also noticed use of "use_concat materialize" hint. For some of the queries, when i execute it without "use_concat" the speed is almost 300% faster.
Under what condition should this hint be used?
What would help with performance
and Connor said...
1) My stance is MATERIALIZE should only be used as a tuning activity, never as a default.
If we want to be *really* strict about it, MATERIALIZE is not even documented so officially you should never use it, but obviously its usage out in the real world is fairly commonplace and I don't envision it going away any time soon.
With *every* query in an application I try work along the following guide:
- no hints at all
- if I hit a problem, make sure its not lack/wrong stats
- if normal stats not good enough, see if dynamic sampling is an option (for analytical not oltp style queries)
- if after all of that, then look at hints to get a good plan
- if that helps, then lock plan in with SPM and remove the hints
2) Select NVL(this, NVL(that,99)), column, column from table;
is not an issue because its applied only AFTER the records you want are found
3) "where column1 = CASE WHEN this THEN 1 WHEN THAT THEN 2 WHEN OTHER_THAT THEN 3"
*might* be an issue if "this", "that" are columns not static values, because it might disable the use of indexes on 'column1'
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment