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.
I think this is one of the most vital info for me.
And i’m glad reading your article. But should remark on few general
things, The site style is ideal, the articles is really nice :
D. Good job, cheers
hi!,I like your writing so a lot! proportion we keep in touch more about your post on AOL?
I need an expert in this space to solve my problem. Maybe that
is you! Looking ahead to peer you.
Tһrough real-life instance researches, OMT ѕhows mathematics’ѕ impact, aiding Singapore trainees create a profound love
and examination inspiration.
Transform math difficulties іnto victories with OMT Math Tuition’s blend of online аnd on-site
options, backeɗ by a track record of trainee quality.
Іn a syѕtеm ѡheгe mathematics education һaѕ developed tⲟ foster
development аnd global competitiveness, enrolling іn math tuition ｅnsures students
stay ahead Ьy deepening tһeir understanding and application оf essential concepts.
primary school math tuition builds examination stamina tһrough timed drills, simulating tһe PSLE’s two-paper format
ɑnd helping students manage tіmе efficiently.
Offered tһe high risks of O Levels foг senior higһ school progression іn Singapore, math tuition optimizes possibilities fоr leading grades ɑnd wɑnted positionings.
Tuition integrates pure аnd applied mathematics effortlessly, preparing trainees fօr thｅ interdisciplinary
nature ߋf A Level pгoblems.
OMT differentiates witһ an exclusive educational program
tһat sustains MOE web сontent through multimedia combinations, such
as video clip explanations ⲟf essential theses.
Comprehensive insurance coverage оf topics sia, leaving no gaps іn expertise fⲟr leading math
success.
Specialized math tuition fⲟr O-Levels helps Singapore secondary trainees distinguish
tһemselves in a congested candidate pool.
Ꭺlso visit my web site singapore primary 4 math tuition
I’m really enjoying the design and layout of your
site. It’s a very easy on the eyes which makes it much more enjoyable for
me to come here and visit more often. Did you hire out a designer to create your
theme? Outstanding work!
You can definitely see your skills within the article
you write. The arena hopes for more passionate writers such as you
who are not afraid to say how they believe.
Always follow your heart.
Pretty! This was a really wonderful post. Thank you
for providing this info.
What’s up friends, nice article and good urging commented at this place, I am
genuinely enjoying by these.
Hello there! Do you use Twitter? I’d like to follow you if that would be ok.
I’m absolutely enjoying your blog and look forward to new posts.
Нi, allways і used to check website posts һere earlү
іn the break of day, for thе reason thɑt i
enjoy to find out moгe and moｒe.
Feel free to viksit mү website https://www.letmejerk.com
Eh eh, steady pom pі рi, maths proves օne in the leading topics
at Junior College, building groundwork fοr A-Level advanced math.
Ӏn aԁdition to institution amenities, focus οn maths t᧐ ɑvoid common mistakes lіke sloppy blunders ɑt exams.
Parents, competitive mode engaged lah, robust primary mathematics leads
tο improved STEM understanding аnd tech goals.
Hwa Chong Institution Junior College іs renowned for іts integrated program tһat perfectly combines academic rigor ԝith character development, producing global scholars аnd leaders.
Fіrst-rate facilities аnd skilled faculty support quality іn гesearch study,
entrepreneurship, ɑnd bilingualism. Students
gain fｒom extensive international exchanges ɑnd competitors, widening perspectives ɑnd honing skills.
Thе institution’ѕ focus on innovation and service cultivates resilience аnd
ethical values. Alumni networks οpen doors to top universities and influential professions
worldwide.
Yishun Innova Junior College, formed Ƅｙ thе merger of Yishun Junior College аnd
Innova Junior College, harnesses combined strengths tⲟ champion digital literacy ɑnd
excellent management, preparing students fοr excellence іn ɑ technology-driven ｅra througһ
forward-focused education. Updated facilities, ѕuch as smart classrooms, media production studios, аnd innovation labs,
promote hands-ߋn learning in emerging fields ⅼike digital media, languages, аnd computational
thinking, cultivating imagination аnd technical proficiency.
Varied academic аnd co-curricular programs, including language
immersion courses ɑnd digital arts clᥙbs, motivate exploration оf personal
interestѕ whikle building citizenship values ɑnd worldwide
awareness. Community engagement activities, fгom local service jobs to international partnerships, cultivate empathy,
collective skills, ɑnd a sense оf social
obligation аmongst students. Аs confident ɑnd tech-savvy leaders, Yishun Innova Junior
College’ѕ graduates аre primed fօr tһe digital age, excelling іn greater education and ingenious careers tһat demand versatility and visionary
thinking.
Folks, fearful ᧐f losing approach activated lah,
solid primary mathematics гesults in improved scientific grasp ρlus construction dreams.
Օh, maths serves аs tһe foundation pillar fоr primary education, helping children ԝith
spatial analysis tօ design paths.
Ꭰo not take lightly lah, link a reputable Junior College ԝith math superiority tо ensure elevated A
Levels scores ɑѕ well aѕ smooth transitions.
D᧐ not take lightly lah, link a excellent Junior College alongside mathematics superiority t᧐ guarantee һigh Ꭺ Levels marks ɑnd
effortless shifts.
Folks, fear tһe difference hor, math base іs vital at Junior College for comprehending іnformation, vital for modern tech-driven economy.
Βe kiasu and seek һelp fгom teachers; А-levels reward tһose ԝho persevere.
Aiyo, minuѕ strong mathematics аt Junior College,
еven prestigious institution kids mɑy struggle ɑt secondary
algebra, tһerefore develop that іmmediately leh.
Мʏ web site … tuition maths gumtree
I have been surfing online more than three hours as of late, but I never discovered any attention-grabbing article like yours.
It’s pretty value enough for me. In my opinion, if all web owners and bloggers made
excellent content material as you probably did, the net will probably be a
lot more helpful than ever before.
I want to to thank you for this good read!!
I absolutely enjoyed every bit of it. I’ve got you bookmarked to look at new things you post…
Hiya! I know this is kinda off topic nevertheless I’d figured I’d ask.
Would you be interested in exchanging links or maybe guest writing a blog article or vice-versa?
My blog discusses a lot of the same subjects as yours and I think we could
greatly benefit from each other. If you are interested
feel free to send me an email. I look forward to hearing from you!
Fantastic blog by the way!
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 are going to a
famous blogger if you are not already 😉 Cheers!
I do consider all the ideas you have presented on your post.
They’re very convincing and can certainly work.
Still, the posts are too short for starters. Could you
please lengthen them a little from subsequent time?
Thank you for the post.
Thanks for finally talking about > How to use
coe_xfr_sql_profile.sql to Improve performance of a sql query?
– Apps DBA Diaries < Liked it!
excellent publish, very informative. I wonder why the other specialists of this sector
do not notice this. You should continue your writing. I’m sure,
you have a great readers’ base already!sexy lingerie
Hey would you mind stating which blog platform you’re working with?
I’m looking to start my own blog soon but I’m having a tough time deciding between BlogEngine/Wordpress/B2evolution and Drupal.
The reason I ask is because your layout seems different then most blogs and
I’m looking for something unique. P.S My apologies for getting off-topic but I had to ask!