We're not sure if this is a joke or just (more likely) the work of an idiot. Here are 15 Tips for better performance and tuning in Oracle SQL and PL/SQL:
- FTS (Full Table Scans) are always bad and Index usage is always good.
- Usage of dynamic SQL within the stored procedure code is always bad even for search procedures.
- Empty Space in an index that gets created due to the DML operations do not get used.
- Indexes should be rebuilt at regular intervals.
- Indexes and statistics are the same thing. Also, histograms are needed only on indexed columns.
- Usage of cursors is always bad so avoid them like the plague.
- Truncate command cannot be rolled back because it is a non-logged operation.
- Table variables in SQL Server are always only memory resident.
- Column order in a covered index does not matter.
- In the case of SQL Server, one can separate the clustered index from the table.
- Only committed data gets written to the disk.
- Logical I/Os (LIO) are not a cause of concern, only Physical IO (PIO) are.
- Count(1) is better performing than count(*).
- Issue frequent commits in the application to make the transaction faster and also improve concurrency.
- Views are evil evil DB Objects that always slow down performance.
We like the fact that 8, 9 and 10 appear to be about SQL Server, despite the heading. Probably Nawal could only think of 12 fake Oracle tips but thought nobody would notice. Can you help him out with some more misleading tips for Oracle? (Or SQL Server. Nobody will notice.) For example,
- The buffer cache hit ratio is a reliable indicator of system performance.
- Bitmap indexes are perfect for columns with a small number of distinct values, like 'Y' and 'N'.
- The Inuit have fifty words for snow.
Spotted by Michel Cadot on Oracle-L.
Update: Yong Huang pointed out that the source of the article appears to be a list of common myths posted on a SQL Server blog, which makes it slightly less funny than it first seemed. Oh well.
4 comments:
1. Enhance your rman backup performance by backing up to disk on /dev/null
Joins are expensive and should be avoided at all costs. Instead you either do them procedurally in pl/sql or denormalize everything so there is no need for master-detail relationships.
try rebuilding the index on the words_for_snow table.
If there are only one or two words for snow that would be a bitmap index of course. More than 15% and you'll want a b-tree.
Bugger about with OPTIMIZER_INDEX_CACHING, OPTIMIZER_INDEX_COST_ADJ and DB_FILE_MULTIBLOCK_READ_COUNT until your query uses that damn index.
Use INSERT /*+ APPEND NOLOGGING */ to improve INSERT performance.
Can I just apologise for the fact that this page of fake performance tips turned out not be a real one, and instead just a page of random crap posted to generate clickthrough ad revenue or whatever it's called, which we've now boosted. However perhaps this WTF post can still serve a useful purpose if we can use it to capture the definitive list of performance tips used by idiots.
In PL/SQL, use the most longwinded explicit cursor syntax available to avoid an unproven "extra fetch".
Post a Comment