This forum is currently locked. You can't register or post questions at this time. (read more)

performance tuning concepts.

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

coolboy98699
Member
Posts: 6
Joined: Mon Jun 25, 2012 11:14 am

performance tuning concepts.

Postby coolboy98699 » Mon Jun 25, 2012 11:34 am

Hi Sir,

I am newbie to performance tuning concepts.

Sir if the query is running slow then i will check the explain plan..

In that what parameters should i check..

Thank you

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: performance tuning concepts.

Postby Tim... » Mon Jun 25, 2012 12:24 pm

Hi.

Whole books have been written on this subject, so I'm not sure what I can impart that is of use in a forum post.

1) Check the cardinality of the operations. That is, how many rows Oracle is processing for each step.

What is important here is the estimated number of rows match the actual number of rows. When Oracle generates an execution plan it uses database statistics to decide how expensive operations will be. If the stats are not representative of the data, then it may pick the wrong execution plan. A single way to check this is to compare the estimated cardinality with the actual cardinality. There is an example of this here:

http://www.oracle-base.com/articles/9i/ ... stics_hint

If the actual and expected cardinalities do not match (or are close), then Oracle is using unrepresentative stats to make its decisions, which is not good.

http://www.oracle-base.com/articles/mis ... istics.php

2) The next step is to sense check the operations. Do the execution plan seem sensible. For example, if you are expecting to return a single row, is a full table scan of 1 billion rows sensible? Likewise, if you are expecting to visit 1 billion rows, using an index to access them is probably not a great idea. This sense check may identify obvious issues, like you are trying to use a full table scan, where an index scan would be more appropriate, or vice versa.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

coolboy98699
Member
Posts: 6
Joined: Mon Jun 25, 2012 11:14 am

Re: performance tuning concepts.

Postby coolboy98699 » Mon Jun 25, 2012 4:29 pm

Sir

Thank you very much for your reply........

As you said in these two cases

"
1.if you are expecting to return a single row, is a full table scan of 1 billion rows sensible? Likewise,
2. if you are expecting to visit 1 billion rows, using an index to access them is probably not a great idea. "

what should we do to overcome please explain...

Thank you very much

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: performance tuning concepts.

Postby Tim... » Mon Jun 25, 2012 6:52 pm

Hi.

1) It might mean you need to add an index to the table. It might be an index is present, but not being used because of the way the query is written.

2) This could be because of bad stats, so Oracle things the index is more efficient.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

coolboy98699
Member
Posts: 6
Joined: Mon Jun 25, 2012 11:14 am

Re: performance tuning concepts.

Postby coolboy98699 » Tue Jun 26, 2012 5:46 am

Thank you sir.....

I am clear about those things now...

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: performance tuning concepts.

Postby Tim... » Tue Jun 26, 2012 6:54 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 3 guests

cron