Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Li.

Asked: December 06, 2016 - 6:44 am UTC

Last updated: December 06, 2016 - 11:35 am UTC

Version: all

Viewed 1000+ times

You Asked

Hi,guy!
I have some question about the SQL PROFILE, when use SQL PROFILE to bind the SQL,it performanced good,but as time goes by,the data in the table will grow rapidly and the SQL which use SQL PROFILE performanced bad. So what's the meaning of the SQL PROFILE? And when to use the SQL PROFILE to tune the SQL?
Hope for your answer!
Thanks!

and Connor said...

A SQL profile is like some "assisting" information for the optimizer to improve the optimizer plan. For example, an optimization step on a join might assume 10 rows will come out of the join, but in reality, 100 rows come out of it. A sql profile will convey that needed 'adjustment'.

Now of course, if the data changes dramatically over time, then the profile information may no longer be valid.

Some good information here

http://www.oracle.com/technetwork/database/manageability/sql-profiles-technical-overview-128535.pdf

But you might want to consider sql plan baselines instead

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-management-11gr2-133099.pdf

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-12c-1963237.pdf

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

For thanks!

Li, December 06, 2016 - 9:27 am UTC

Thanks for your answer, I would follow your guidance to understand the SQL PROFILE.
Thanks a lot!
Connor McDonald
December 06, 2016 - 11:35 am UTC

glad we could help

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.