Solved

DDL and DML performance tuning

Posted on 2001-08-31
1
779 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
Comment Utility
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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How can I use listagg with distinct. 4 72
oracle 10G 5 46
Oracle PL/SQL syntax 4 52
null value 14 57
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now