If I'm posting a question about Oracle SQL query performance, what should I include in my question?

Question

If I am posting a question about a query against an Oracle database, what should I include in my question so that people have a chance to answer me? How should I get this information?

Simply providing the poorly performing query may not be enough.

Answer 1

The query plan is always useful

Answer 2

• The schema definition of the tables involved.
• The indexes defined on those tables.
• The query you are executing.
• The resulting query execution plan

Answer 3

Ideally, get the full query plan using DBMS_XPLAN.DISPLAY_CURSOR using the sql_id and child_cursor_id from v$sql. Failing that (ie on older versions), try v$sql_plan and include filter and access predicates. EXPLAIN PLAN is fine if it actually shows the plan that was used.

DB version and edition (Express/Standard/Enterprise). Maybe the OS too. SELECT * FROM V$VERSION

If you have any non-standard database parameters, it is useful to know (especially anything optimizer related). select * from v$parameter where rownum < 5 and isdefault != 'TRUE'; *If you do a alter session set events '10053 trace name context forever, level 1' and parse a query, there'll be a log file that will include all the parameters used when optimizing a query *

Real world table sizes and column distributions (eg it is a million row table, with 30% of rows being "Red" etc). And the relevant stats off USER_TABLES, USER_TAB_COLUMNS.

How long it actually look, plus any SQL stats you have available (consistent gets, physical reads) from v$sql.

Also, who do you THINK it should be able to run faster. Do you think there's a better plan, or are you just crossing your fingers.