This content has been marked as final. Show 11 replies
No, there are no bitmap indexes at all and USE_HASH hint cannot be removed as this is production environment.
SQL> select * from v$version;
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
No, there are no bitmap indexes at all and USE_HASH hint cannot be removed as this is production environment.That description sounds exactly like a non-production env....
Bitmap indexes are great for production if you have low cardinality and I'm pretty sure all my teachers/professors/mentors told me never to use hints in production env...
I know, but the thing is, there is no low-cardinality in neither columns indexed. Also hint usage is justified. USE_HASH may not work well on large tables, but it works far better than nested loops given the volumes we process.
Forgot to add that I have 900 parallel servers setup to run at maximum. Does this consume such high server and DB resources? Or is it the hint thing ?
I haven't been impressed by MERGE performance, although its possible I haven't found the precise circumstances where it is most effective. In particular I've found that merge can be slow when rows are mostly being inserted (performing lookups for rows that are not there).
How is performance on the lookup query? It it properly lindexed for the volume of data it retrieves?
PQO helps with sorts and full table scans (which are often used by hash joins). It can help or hurt performance. Are you sure it is helping you?
How much data are you processing with the MERGE (bytes and rows)?
--Are statistics refreshed before & after MERGE executes?
Unfortunately, not. But they're not getting too stale as statistics gets refreshed every night anyway.
--In round number, what % of table gets chang
Very little, if we compare 20.000 records to 500.000.000
--The general rule for Oracle is that "temp" tables are not needed.
What do you mean? There are not tem tables. Just the "temp" name
Thanks everybody for your responses so far.
I would provide the execution plan for that statement if the merging table existed. Unfortunately it gets deleted straight after the merges is finished.
I can tell you that it does 2 full joins on the merged and merging tables. I have also run the SQL optimizer in order to prefrom better execution plan. The thing is it cannot be implemented as the second table is always different. Therefore optimizer cannot work out same plan for very similar merge statements.
Anyway, it all worked in acceptable way. Things got worsen few weeks ago. Why? No idea. No parameters change, no infrastructure change. Only the main table grew.
I agree, the merge statement is not perfect, with the use_hash hint, and fact the statistics do not get collected before the executions. But it really worked in accpetable way. Now, is just terrible.
I would suspect the max_servers allowance is the problem. PX send blk event is pretty high for DB not only when merge runs. Most of the performance activity is the user I/O. I also checked the server and quite often there are 400-900 ora_p0* processes running. No wonder it takes that long. What does it mean? Processes cannot get get access to the disk fast enough? Do processes wait for each other to finish? Is infrastructure not powerful enough (8 processors on board) ?