Oracle SQL Performance tuning is one of the frequently reported issues in a Oracle Database administrator’s daily job.
Very often we notice that sql queries or concurrent Programs which were running within few minutes earlier are now taking hours to be completed.
Underlying reason is due to the plan_hash_value used by the query has been changed.
coe_xfr_sql_profile.sql is the script developed by Oracle Support which helps us to identify the plan_hash_value which will resolve the performance issue.
Steps to use the script coe_xfr_sql_profile.sql
1. Download the script to a temporary directory on the database server.
Script is also available in Oracle Metalink.
2. Before Running the coe_xfr_sql_profile.sql, we need to know the sql_id of the sql query which is taking longer time to complete than usual.
To find the sql_id use below query
2.1 If you know the database SID of the long running query.
select inst_id,sid,serial#,status,last_Call_et,sql_id from gv$session where sid= ‘&sessionid’;
2.2 If you know the query test, Use gv$sql and gv$sqlarea views to get the sql_id.
3. Login to sqlplus as sysdba and run coe_xfr_sql_profile.sql at the SQL prompt.
$ sqlplus “/as sysdba”
SQL> @coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)
Enter value for 1: 0a3f7vuks8d7y (–this is the sql_id of long running sql)
PLAN_HASH_VALUE AVG_ET_SECS
————— ———–
1484137450 15.6
3622468234 4560.76
[Output shows the list of available plan hash values, which can be forced on to the sql query. We need to select the plan_hash_value with low ETA to resolve the performance issue]
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 1484137450
++Based on the inputs provided, it generates a sql script with naming convention coe_xfr_sql_profile_0a3f7vuks8d7y_1484137450.sql as output
coe_xfr_sql_profile_<sql_id>_<plan_hash_value>.sql
4. Run the script as sysdba
SQL>coe_xfr_sql_profile_0a3f7vuks8d7y_1484137450.sql
5. Verify that Performance issue is resolved by Re-running the sql.
Very often we notice that sql queries or concurrent Programs which were running within few minutes earlier are now taking hours to be completed.
Underlying reason is due to the plan_hash_value used by the query has been changed.
coe_xfr_sql_profile.sql is the script developed by Oracle Support which helps us to identify the plan_hash_value which will resolve the performance issue.
Steps to use the script coe_xfr_sql_profile.sql
1. Download the script to a temporary directory on the database server.
Script is also available in Oracle Metalink.
2. Before Running the coe_xfr_sql_profile.sql, we need to know the sql_id of the sql query which is taking longer time to complete than usual.
To find the sql_id use below query
2.1 If you know the database SID of the long running query.
select inst_id,sid,serial#,status,last_Call_et,sql_id from gv$session where sid= ‘&sessionid’;
2.2 If you know the query test, Use gv$sql and gv$sqlarea views to get the sql_id.
3. Login to sqlplus as sysdba and run coe_xfr_sql_profile.sql at the SQL prompt.
$ sqlplus “/as sysdba”
SQL> @coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)
Enter value for 1: 0a3f7vuks8d7y (–this is the sql_id of long running sql)
PLAN_HASH_VALUE AVG_ET_SECS
————— ———–
1484137450 15.6
3622468234 4560.76
[Output shows the list of available plan hash values, which can be forced on to the sql query. We need to select the plan_hash_value with low ETA to resolve the performance issue]
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 1484137450
++Based on the inputs provided, it generates a sql script with naming convention coe_xfr_sql_profile_0a3f7vuks8d7y_1484137450.sql as output
coe_xfr_sql_profile_<sql_id>_<plan_hash_value>.sql
4. Run the script as sysdba
SQL>coe_xfr_sql_profile_0a3f7vuks8d7y_1484137450.sql
5. Verify that Performance issue is resolved by Re-running the sql.
I loved as much as you will receive carried out right here. The sketch is tasteful, your authored material stylish. nonetheless, you command get got an shakiness over that you wish be delivering the following. unwell unquestionably come further formerly again as exactly the same nearly a lot often inside case you shield this increase.
I’d like to find out more? I’d love to find out more details. http://www.kayswell.com
I have observed that car insurance companies know the motors which are vulnerable to accidents and other risks. In addition, they know what form of cars are inclined to higher risk and the higher risk they have the higher the premium charge. Understanding the uncomplicated basics involving car insurance will allow you to choose the right type of insurance policy which will take care of your requirements in case you get involved in an accident. Thank you for sharing the particular ideas for your blog.
hi!,I like your writing so much! share we communicate more about your post on AOL? I require an expert on this area to solve my problem. Maybe that’s you! Looking forward to see you.
Attractive section of content. I just stumbled upon your blog and in accession capital to assert that I get actually enjoyed account your blog posts. Any way I will be subscribing to your feeds and even I achievement you access consistently rapidly.
Thanks for posting. I really enjoyed reading it, especially because it addressed my problem. http://www.ifashionstyles.com It helped me a lot and I hope it will help others too.
I enjoyed reading your piece and it provided me with a lot of value. http://www.kayswell.com
I enjoyed reading your piece and it provided me with a lot of value. http://www.kayswell.com
I enjoyed reading your piece and it provided me with a lot of value. http://www.goodartdesign.com
I truly appreciate your technique of writing a blog. I added it to my bookmark site list and will
The articles you write help me a lot and I like the topic http://www.hairstylesvip.com
Nice post. I learn something totally new and challenging on websites
May I request that you elaborate on that? http://www.kayswell.com Your posts have been extremely helpful to me. Thank you!
This was beautiful Admin. Thank you for your reflections.
Very well presented. Every quote was awesome and thanks for sharing the content. Keep sharing and keep motivating others.
Thanks for simplifying a difficult subject.
Great job, keep it up!
I love the structure of your explanation.
I like the efforts you have put in this, regards for all the great content.
Hi, the whole thing is going perfectly here and ofcourse
every one is sharing data, that’s really fine, keep up writing.
I all the time used to read piece of writing in news
papers but now as I am a user of net so from now I am using net for articles, thanks to web.
Amazing! This blog looks exactly like my old one!
It’s on a entirely different subject but it has pretty much the same layout and design. Superb choice of colors!
This paragraph presents clear idea for the new people
of blogging, that in fact how to do blogging and site-building.
Howdy! I’m at work browsing your blog from my new iphone 4!
Just wanted to say I love reading your blog
and look forward to all your posts! Carry on the outstanding work!
Thanks for finally talking about > How to use coe_xfr_sql_profile.sql to Improve performance of a sql query?
– Apps DBA Diaries < Loved it!
Hi! Do you know if they make any plugins to help with SEO?
I’m trying to get my blog to rank for some targeted keywords but I’m not seeing
very good success. If you know of any please share.
Thank you!
Neat blog! Is your theme custom made or did you download it from somewhere?
A theme like yours with a few simple tweeks would really make my blog stand out.
Please let me know where you got your design. Kudos
Simply want to say your article is as surprising. The clarity to your submit is just nice
and that i could suppose you are knowledgeable on this subject.
Fine with your permission let me to seize your RSS feed to keep updated with coming near near post.
Thank you 1,000,000 and please carry on the enjoyable work.
Hey there! Quick question that’s totally off topic. Do you
know how to make your site mobile friendly?
My blog looks weird when viewing from my iphone 4.
I’m trying to find a template or plugin that might be able to correct this issue.
If you have any suggestions, please share. Appreciate it!
Hello! I just wanted to ask if you ever have any trouble with hackers?
My last blog (wordpress) was hacked and I ended up losing months of hard work due to no
backup. Do you have any solutions to protect against hackers?
Touche. Great arguments. Keep up the amazing spirit.
Its like you read my mind! You seem to know so much
about this, like you wrote the book in it or something.
I think that you could do with a few pics to drive the message
home a bit, but other than that, this is magnificent blog.
A great read. I’ll certainly be back.
With havin so much content and articles do you ever run into any problems of plagorism or
copyright infringement? My blog has a lot of exclusive content I’ve either authored myself or outsourced but it appears
a lot of it is popping it up all over the internet without my agreement.
Do you know any techniques to help stop content from being stolen? I’d certainly
appreciate it.
I don’t even know how I ended up here, but I thought this post was good.
I don’t know who you are but definitely you’re going to
a famous blogger if you are not already 😉 Cheers!
What’s up everyone, it’s my first pay a visit at this web site,
and article is actually fruitful for me, keep up posting these types of
posts.
Greetings I am so delighted I found your web site, I really found
you by mistake, while I was researching on Aol for something
else, Regardless I am here now and would just like to
say many thanks for a tremendous post and
a all round interesting blog (I also love the theme/design), I don’t have time to browse it all at the minute but I have
saved it and also included your RSS feeds, so when I have time I will be back to
read more, Please do keep up the awesome job.
Hello! Quick question that’s totally off topic. Do you know how to make your site mobile
friendly? My weblog looks weird when viewing from my iphone4.
I’m trying to find a template or plugin that might be able to fix this issue.
If you have any suggestions, please share. Appreciate it!
If you would like to obtain much from this paragraph then you have to apply such
methods to your won webpage.
May I simply just say what a comfort to uncover
a person that really understands what they are discussing online.
You certainly understand how to bring a problem to light and make
it important. More and more people must look at this and understand this side of your story.
I can’t believe you’re not more popular given that you certainly have the gift.
Hey! Would you mind if I share your blog with my twitter group?
There’s a lot of folks that I think would really
appreciate your content. Please let me know. Cheers
Fine way of describing, and good article to obtain information regarding my presentation topic, which i am going
to present in academy.
This is a topic that’s close to my heart… Cheers!
Where are your contact details though?
I every time emailed this website post page to all my friends, for the
reason that if like to read it after that my friends will too.
Right now it seems like Expression Engine is the top blogging platform out there right
now. (from what I’ve read) Is that what you’re using on your
blog?
Hi, i read your blog from time to time and i own a similar one and i was just curious if you
get a lot of spam comments? If so how do
you prevent it, any plugin or anything you can recommend?
I get so much lately it’s driving me crazy so any support is very much appreciated.
Awesome! Its genuinely awesome post, I have got much clear
idea about from this post.
I’ve been exploring for a little for any high-quality articles or blog
posts on this kind of area . Exploring in Yahoo I finally stumbled upon this
website. Reading this info So i’m happy to exhibit that I have
a very good uncanny feeling I discovered exactly what I needed.
I so much indisputably will make certain to don?t fail to
remember this website and provides it a look
on a relentless basis.
It’s actually very complex in this full of activity
life to listen news on Television, thus I only use web for that purpose, and obtain the hottest information.
With havin so much written content do you ever run into any issues of plagorism or copyright violation? My site has
a lot of exclusive content I’ve either authored myself
or outsourced but it looks like a lot of it is popping it up all over the web without my agreement.
Do you know any ways to help protect against content from being ripped off?
I’d genuinely appreciate it.
Quality articles or reviews is the important to be a
focus for the users to go to see the web site, that’s what this web page is
providing.
It’s very simple to find out any matter on net as compared to textbooks, as I
found this piece of writing at this web site.
You mentioned this adequately!
Wow! Finally I got a web site from where I be able to truly
get valuable information concerning my study and knowledge.
Very good facts Regards!
Great web site you’ve got here.. It’s difficult to find quality
writing like yours nowadays. I truly appreciate individuals like you!
Take care!!
Howdy excellent blog! Does running a blog similar to this
take a large amount of work? I have no understanding
of coding however I was hoping to start my own blog soon. Anyhow,
if you have any ideas or tips for new blog owners please share.
I understand this is off subject but I just wanted to
ask. Thanks a lot!
Hi I am so happy I found your weblog, I really found you by error,
while I was searching on Aol for something else, Anyways
I am here now and would just like to say thanks for a fantastic post and a all round exciting
blog (I also love the theme/design), I don’t have time to look over it
all at the moment but I have saved it and also added your RSS feeds, so when I have
time I will be back to read more, Please do keep up the excellent work.
Beneficial stuff Thanks.
Do you have a spam issue on this site; I also am a blogger, and
I was wondering your situation; we have developed some nice practices
and we are looking to swap methods with others, be sure to
shoot me an e-mail if interested.
OMT’s mix of online and on-site options սѕes adaptability, maҝing mathematics accessible
and charming, whiⅼe inspiring Singapore trainees fοr test success.
Discover tһe benefit of 24/7 online math tuition ɑt OMT, wheгe interestіng resources mɑke discovering enjoyable аnd effective for ɑll levels.
Offered tһat mathematics plays аn essential function in Singapore’s financial development and development, purchasing specialized math tuition equips trainees ԝith the problem-solving
abilities required tо flourish іn ɑ competitive landscape.
primary school math tuition builds examination stamina tһrough timed drills, mimicking tһe PSLE’s tᴡⲟ-paper format and helping trainees manage tіme efficiently.
Іn Singapore’s competitive education ɑnd learning landscape, secondary math tuition supplies tһe extra side required tօ stand ɑpart in O Level positions.
Tuuition ɡives methods for time management during the
lengthy Ꭺ Level mathematics tests, permitting trainees tο allocate efforts efficiently
аcross sections.
OMT’ѕ exclusive mathematics program matches MOE standards Ьy emphasizing conceptual proficiency оver rote understanding,
causing much deeper ⅼong-term retention.
OMT’ѕ systеm encourages goal-setting ѕia, tracking milestones in tһe direction of attaining higher qualities.
Singapore’ѕ incorporated math educational program tаke advantage оf tuition tһat
connects topics across degrees fоr cohesive examination preparedness.
Ηere is my web site … cbse maths home tuition in chennai
My relatives all the time say that I am wasting my time
here at web, but I know I am getting knowledge daily by
reading such good articles or reviews.
Người chơi muốn tham gia 99OK chỉ cần nhấp vào mục này để bắt đầu quá
trình tạo tài khoản nhà cái 99OK.
Hello would you mind letting me know which hosting company
you’re utilizing? I’ve loaded your blog in 3 different web
browsers and I must say this blog loads a lot quicker then most.
Can you recommend a good web hosting provider at a reasonable price?
Thank you, I appreciate it!
What’s up to all, it’s genuinely a nice for me to pay a quick visit this site, it includes helpful Information.