Solved

DDL and DML performance tuning

Posted on 2001-08-31
1
786 Views
Last Modified: 2012-08-13
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
Comment
Question by:k_murli_krishna
1 Comment
 

Accepted Solution

by:
tiborhorvath earned 50 total points
ID: 6446784
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Consolidating oracle query results to a single line 8 65
Oracle Pivot Question 8 71
Oracle - SQL Parse String 5 45
Oracle Nested table uses ? 2 34
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question