Q&A: SQL Server

Checklist for Analyzing Slow-Running Queries?
# Use SQL Server Profiler to help identify the slow query or queries. For more information. Use the sys.dm_exec_query_stats and sys.dm_exec_requests dynamic management views to find similar queries that collectively consume a large number of resources.
# How do I analyze the performance of a slow-running query? After you have identified the slow-running query or queries, you can further analyze query performance by producing a Showplan, which can be a text, XML, or graphical representation of the query execution plan that the query optimizer generates. (You can produce a Showplan using Transact-SQL SET options, SQL Server Management Studio, or SQL Server Profiler.)
# Are suitable indexes available? Would adding one or more indexes improve query performance?
# Are there any data or index hot spots? Consider using disk striping. Disk striping can be implemented by using RAID (redundant array of independent disks) level 0, where data is distributed across multiple disk drives.
# Is the query optimizer provided with the best opportunity to optimize a complex query?
# If you have a large volume of data, do you need to partition it? Data manageability is the main benefit of partitioning, but if your tables and indexes on them are partitioned similarly, partitioning can also improve query performance. For more information, see Understanding Partitioning and Tuning the Physical Database Design.

Q&A: Oracle Performane Tuning

Why using globe temp table (GTT)?
Faster, improve performance.
http://www.dba-oracle.com/t_temporary_tables_sql.htm
http://www.dba-oracle.com/t_sql_rewrite_temporary_tables.htm

What is the difference between 'on commit preserve rows' and 'on commit delete rows' for GTT?
The ON COMMIT PRESERVE ROWS makes this a session based temporary table. rows will stay in this table until a logoff.
The ON COMMIT DELETE ROWS makes this a transaction based temp table. When you commit -- the rows disappear.
http://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:48812348054

What is explain plan and how to read it?
It tells you how oracle processes the query. Read it form inner right to outer left.
http://www.oracle-base.com/articles/8i/ExplainPlanUsage.php
http://www.orafaq.com/node/1420

Why use bulk collect?
Improve performance.
http://www.dba-oracle.com/t_oracle_bulk_collect.htm