Solved

DDL and DML performance tuning

Posted on 2001-08-31
1
780 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Database creation fails 5 49
SQL Query - Oracle 10g - Subract date from next data row 4 48
query in Oracle forms Builder 2 41
SQL query question 8 33
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

914 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

12 Experts available now in Live!

Get 1:1 Help Now