The Oracle Documentation lists reasons for poor performance, but they are not as comprehensive or useful as our BC top-10 list. Oracle Corporation has never been as good at tuning their own software as third-party experts, and their performance cause list is missing many important factors.
Obviously, a mis-configured I/O sub-system is a major cause of poor Oracle performance, but some of the others will surprise you.
Click below to see the BC top-10 list of the most common Oracle performance problems:
http://oracle-tips.c.topica.com/maal8FqabJAW6bLGJrib/
The top-10 reasons for poor Oracle performance
Important 10g upgrade gotchas revealed
Oracle has improved the cost-based Oracle optimizer in 9.0.5 and again in 10g, so one of the most common causes of bad performance right after moving to 10g are related to environmental parameter settings (init.ora parms) and your optimizer statistics. Properly configured, Oracle 10g should always faster than earlier releases, both for PL/SQL and SQL, so it is likely that any slow performance after an Oracle 10g upgrade is due to initialization parameter settings or incomplete CBO statistics.
Remember, Oracle 10g is the world's most flexible and complex database, and upgrading to Oracle 10g is very tricky. Prior to putting your Oracle 10g upgrade into production, it's a best practice to thoroughly test the migration with a real-world workload. As an alternative, many shops obtain an independent Oracle health check to identify their sub-optimal configuration settings. Experts who frequently perform 10g upgrades know exactly where to look, and they can save you weeks of frustration.
To see the secrets, click below to see the most common causes of poor performance after a 10g upgrade:
http://oracle-tips.c.topica.com/maal8FjabJATTbLGJrib/
Also, see these notes on justifying an Oracle10g migration:
http://oracle-tips.c.topica.com/maal8FjabJATUbLGJrib/
Important research on pre-joining Oracle tables
One serious performance issue with highly-normalized, non-redundant Oracle table designs (e.g. third normal form) is that Oracle experiences a high degree of overhead (especially CPU consumption) when joining dozens of tables together, over-and-over again.
Using materialized views we can pre-join the tables together, resulting in a single, fat, wide and highly-redundant table. This can reduce logical I/O from tens of thousands to a single row fetch, resulting in blisteringly fast response time, but careful attention must be paid to choosing the proper materialized view partition keys and the best refresh interval.
The problem with materialized view for pre-joined tables is keeping them refreshed. Because the materialized view is built from many tables, and changes to the base tables require an update to the materialized view.
Read more on this issue here:
http://oracle-tips.c.topica.com/maal8GhabJA8ebLGJrib/
For more details, see my book "Oracle Tuning: The Definitive Reference". It's 30% off, directly from the publisher:
http://oracle-tips.c.topica.com/maal8GhabJA8dbLGJrib/
Oracle LIKE clause searches with text indexes
One serious SQL performance problem occurs when you use the SQL “LIKE clause” operator to find a string within a large Oracle table column (e.g. VARCHAR(2000), CLOB, BLOB):
Select stuff from bigtab where text_column like ‘%ipod%’;
Select stuff from bigtab where full_name like ‘%JONES’;
Because standard Oracle cannot index into a large column, there “like” queries cause full-table scans, and Oracle must examine every row in the table, even when the result set is very small. These unnecessary full-table scans are a problem:
1) Large-table full-table scans increase the load on the disk I/O sub-system
2) Small table full table scans (in the data buffer) cause high consistent gets and drive-up CPU consumption
One obscure trick for indexing queries with a leading wildcard character (like '%SON') is to create a REVERSE index and them programmatically reverse the SQL like clause to read "like 'NOS%'", effectively indexing on the other side of the text, clumsy, yet effective.
Read more about indexing on Oracle text based searches here:
http://oracle-tips.c.topica.com/maalZ3vabItU3bLGJrib/
Subscribe to:
Posts (Atom)