Search Results for: SQL Profile

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

Another On Call Week, Another SQL Profile (or two)

I was on call again last week and Sunday night I used a SQL Profile to fix a performance problem. We rolled out some updates Saturday night and the modified query ran fine in some cases, but others ran for … Continue reading

Posted in Uncategorized | 2 Comments

SQL Profile not used on slightly different query

Last week I was asked to help with a performance problem that looked a lot like a problem I fixed in July with a SQL Profile. The query whose plan I fixed back in July was modified by a minor … Continue reading

Posted in Uncategorized | 2 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

Another SQL Profile to the rescue!

We have had problems with set of databases over the past few weeks. Our team does not support these databases, but my director asked me to help. These are 11.2.0.1 Windows 64 bit Oracle databases running on Windows 2008. The incident … Continue reading

Posted in Uncategorized | Leave a comment

Query to show sql_ids related to SQL Profiles

I have a number 0f SQL Profiles on a database I’m working on and I wanted to know the sql_id of the SQL statements that they relate to.  Here is what I came up with: select distinct p.name sql_profile_name, s.sql_id … Continue reading

Posted in Uncategorized | 19 Comments

SQL Profile appears to not work but really does

Over the weekend a coworker applied a SQL profile to a long running SQL statement but it looked like the profile was not applied.  For one thing, the SQL statement ran just as long after applying the profile.  Also, the … Continue reading

Posted in Uncategorized | 3 Comments

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

force_match => TRUE option of DBMS_SQLTUNE.IMPORT_SQL_PROFILE

Yesterday and today I’ve read or heard two people mention the force_match => TRUE parameter value for DBMS_SQLTUNE.IMPORT_SQL_PROFILE and how it forces a profile to work on all SQL statements that are the same except for their literal values.  So, … Continue reading

Posted in Uncategorized | 3 Comments