Search Results for: coe_xfr_sql_profile.sql

Using hints with coe_xfr_sql_profile.sql

In an earlier blog post I showed how I used coe_xfr_sql_profile.sql from the sqlt toolkit from Oracle support to force a particular sql statement to run with a more efficient plan that it had used in the past. Today, I’m … Continue reading

Posted in Uncategorized | 8 Comments

Using coe_xfr_sql_profile.sql to force a good plan

Sunday night we worked with Oracle Support on a performance issue with a query that had been running for over 14 hours even though it ran the previous weekend in five minutes.  Oracle Support showed us how to use coe_xfr_sql_profile.sql … Continue reading

Posted in Uncategorized | 11 Comments

Tweaked script to show top SQL by force matching signature

I just finished another production tuning exercise that is like two recent posts: Two New ASH FORCE_MATCHING_SIGNATURE scripts Tuning Example With Outline Hints and SQL Profile The biggest new thing was that I ran the ashtopelapsed.sql script that I mentioned … Continue reading

Posted in Uncategorized | 2 Comments

Tuning Example With Outline Hints and SQL Profile

I tuned a production query this week using outline hints and a SQL profile. This is like other examples I have posted but may have enough differences to be worth documenting here. A query in a batch job was taking … Continue reading

Posted in Uncategorized | Leave a comment

SQL Profile example when best plan not clear

I resolved another production performance issue with a SQL Profile yesterday. I have several posts about SQL Profiles, so I do not want to be redundant, but this case was a little different because it was not clear that I … Continue reading

Posted in Uncategorized | Leave a comment

Example of coe_xfr_sql_profile force_match TRUE

Monday, I used the coe_xfr_sql_profile.sql script from Oracle Support’s SQLT scripts to resolve a performance issue. I had to set the parameter force_match to TRUE so that the SQL Profile I created would apply to all SQL statements with the … Continue reading

Posted in Uncategorized | 6 Comments

SQL Profile to fix slow inserts

I was on call Sunday and got paged about a job that normally runs for 10 to 15 minutes but had run for 5 hours already. I used the coe_xfr_sql_profile.sql script from SQLT to speed up an insert statement that was … Continue reading

Posted in Uncategorized | Leave a comment

Improving performance of top query

I’m on call this week. Here are the steps that I took to speed up a query today. First I got an AWR report and found the top query. Also, someone from support told me to look at November 11 before … Continue reading

Posted in Uncategorized | Leave a comment

Understanding query slowness after platform change

We are moving a production database from 10.2 Oracle on HP-UX 64 bit Itanium to 11.2 Oracle on Linux on 64 bit Intel x86. So, we are upgrading the database software from 10.2 to 11.2. We are also changing endianness … Continue reading

Posted in Uncategorized | Leave a comment

Plan change monitor prevents user impact from bad plan

This morning a colleague of mine noticed an alert from our plan change monitor and prevented any impact on the users by forcing a SQL’s plan to its normal fast plan before the users could be affected.  I want to … Continue reading

Posted in Uncategorized | 11 Comments