Home » RDBMS Server » Performance Tuning » Performance tuning made easy (Oracle 10g, Windows 7 64 bit)
Performance tuning made easy [message #586873] Tue, 11 June 2013 07:25 Go to next message
stalin4d
Messages: 226
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
Senior Member
Hi,

What are the easy steps to tune a query or program;
well i would like to know step by step process;

and i am new to this performance tuning concept.
any book or documents which explains tuning in simple.

Stalin
Re: Performance tuning made easy [message #586881 is a reply to message #586873] Tue, 11 June 2013 07:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no simple way to tune otherwise there would be a program that does it automatically.
You can read:
Database Performance Tuning Guide.
And the 2 (or 3) books from Stephane Faroult

Regards
Michel
Re: Performance tuning made easy [message #586961 is a reply to message #586881] Wed, 12 June 2013 01:21 Go to previous messageGo to next message
stalin4d
Messages: 226
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
Senior Member
michel thanks for the reply and books.

well i have a query!

well i have a table which has more than 1 lakh records, each time
i run the table query with some condition it takes 3 to 7 minutes to fetch the data
what might be the problem?

Eg query:

select prod_id, prod_name, unit_price, qty from product_mas where 
prod_id = '23';


when executing the query it takes more than 3 minutes.
how to get it in some seconds.

well we have constraints created in that table too.

stalin


[Updated on: Wed, 12 June 2013 01:22]

Report message to a moderator

Re: Performance tuning made easy [message #586963 is a reply to message #586961] Wed, 12 June 2013 01:30 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Is PROD_ID column indexed?
Re: Performance tuning made easy [message #586964 is a reply to message #586963] Wed, 12 June 2013 01:47 Go to previous messageGo to next message
stalin4d
Messages: 226
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
Senior Member
yes indexed
Re: Performance tuning made easy [message #586965 is a reply to message #586964] Wed, 12 June 2013 01:56 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
After posting over 100 messages you are supposed to know what is EXPLAIN plan and how to post it.
Re: Performance tuning made easy [message #586966 is a reply to message #586961] Wed, 12 June 2013 02:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel
Re: Performance tuning made easy [message #587002 is a reply to message #586965] Wed, 12 June 2013 06:49 Go to previous messageGo to next message
stalin4d
Messages: 226
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
Senior Member
michael_bialik wrote on Wed, 12 June 2013 12:26
After posting over 100 messages you are supposed to know what is EXPLAIN plan and how to post it.


Thank you for your information.
Re: Performance tuning made easy [message #587003 is a reply to message #586966] Wed, 12 June 2013 06:50 Go to previous messageGo to next message
stalin4d
Messages: 226
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
Senior Member
Michel Cadot wrote on Wed, 12 June 2013 12:30
For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel


thank you michael...
Re: Performance tuning made easy [message #587450 is a reply to message #587003] Fri, 14 June 2013 17:33 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
There truly is no EASY way. If it was easy, everybody would be doing it and I could retire.

Here is a 1 minute tip. I assume you know how to get a query plan and know something about performance which means you know the following terms: (HASH JOIN, NESTED LOOPS JOIN, INDEX LOOKUP, FULL TABLE SCAN, INDEX FAST FULL SCAN, INDEX FULL SCAN, and so on).

1) CARDINALITY RULES OPTIMIZATION.  Check your query plan's cardinality estimates with actuals either by using gather_plan_statistics hint, or by running your own reconstruction queries.  When numbers are off my more than 5X there is clearly an issue that needs investigating.  Be especially warry of any cardinality estimate of 1 that does not come from a UNIQUE INDEX SCAN.


2) most problems come from these few mistakes.  Since these are the 99% contributors to bad performance, it stands to reason you should do something about them.

a) bad DATABASE design
     1. not relational in design
     2. constraints not defined (PK/UK/FK/NOT NULL/CHECK)
     3. FK constraints not indexed (consider OLTP vs. WAREHOUSE)
     4. poor datatype selection DATE=DATE not number, not varchar2; NUMBER=?; VARCHAR2=?
     5. dummy values used instead of null (31-DEC-4712,9999999999999) and the like (truthfully I do this too)
     6. poor indexing strategy (missing index, wrong columns in an index)

b) statistics collection errors
     1. missing
     2. stale
     3. bad collection practices
     4. improper histograms (too many or too few)
     5. no extended stats when useful
     6. forgot an index somewhere
     7. didn't do partitions and sub-partitions and global (granularity='ALL')

c) poor code
     1. row by row design
     2. ignorance of advanced features

d) no respect for the Full Table Scan (FTS IS THE FUTURE, LEARN THE 2% RULE).

e) laziness, developer stopped once he got the right answer and did not bother to think about how it could be written to go fast too.


3) My first step in query tuning is to determine the basic query type.  It is either an OLTP kind of query or a WAREHOUSE kind of query.  I'd like to say there is something in between but from an optimizer perspective there isn't really.  Either your query should be driving from a small number of rows and thus should be using NESTED LOOPS AND INDEX LOOKUPS, or it is looking at more than 2% of the data and thus should be using FULL TABLE SCANS AND HASH JOINS.  Figure out which it should be (SEE CARDINALITY ABOVE) then check to make sure your query plan is doing it.


4) DRIVING TABLE is key for an OLTP kind of query.  Are you starting with the right driving table?  Again (see CARDINALITY ABOVE).


Try to make some sense of what was just said. Good luck. Kevin
Previous Topic: How to Refresh a Materialized view Data.
Next Topic: Tuning a query with an EXISTS clause
Goto Forum:
  


Current Time: Thu Mar 28 03:48:18 CDT 2024