• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 808
  • Last Modified:

DDL and DML performance tuning

I know for select statements, we can use indexes for the most queried columns and I know how to implement of all types. What all other measures can be taken and other objects created and used for better performance w.r.t DDL and DML statements. Please try and give entire list with brief description. Also, please point out a smaller sized textbook or manual using which i can make oracle work faster within the least time. Internal performance tuning is the other question you will find.
0
k_murli_krishna
Asked:
k_murli_krishna
1 Solution
 
tiborhorvathCommented:
There is little to be done about DDL.
For DML, INSERT, UPDATE and DELETE dont need much help.
For SELECT tuning
  Learn how to  EXPLAIN PLAN and learn how to assess the efficiency of an execution plan.
     Oracle documentation readily available.
  Learn tkprof.  This tool is also documented.  It allows you to check out the actual execution of your queries, and sometimes reveals a different answer from EXPLAIN PLAN.
  For any Oracle release beyond 7.2, make sure your database is set to CHOOSE optimizer method.
     This will cause the COST based optimizer to plan you queries.  However, you must remember to run
SQL>  exec dbms_utility.analyze_schema('ACTUALUSERID','COMPUTE');
    periodically for the COST based optimizer to work well.
  Learn about Oracle HINTS, again well documented.
  Get a tool like TOAD, you can download a trial copy of this from www.quest.com. It allows you to browse all kinds of details about your database, including execution plans for all cached SQL statements and queries you compose on the fly.

http://technet.oracle.com has full online documentation for most of the Oracle products.  When poking around finally gets you to a significant master index, bookmark the URL so you can use it directly.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now