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 part of the job. We restarted the job and it completed in a few minutes.

I have written a number of posts about the use of coe_xfr_sql_profile.sql. Sunday’s issue was most like the post on using hints with coe_xfr_sql_profile.sql.

The first thing I did was look at the execution history of the problem insert statement which had sql_id = ‘ba9w9cjy87hd8’. I used my sqlstat.sql script.

SQL_ID        PLAN_HASH_VALUE END_INTERVAL_TIME         EXECUTIONS_DELTA Elapsed Average ms CPU Average ms IO Average ms Cluster Average ms Application Average ms Concurrency Average ms Average buffer gets Average disk reads Average rows processed
------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ----------------------
ba9w9cjy87hd8      3341942963 05-NOV-17 07.00.03.091 AM                2         1302824.53        1162165      572.8985                  0                  2.271                61.1125             6077761               1021                    8.5
ba9w9cjy87hd8      3341942963 05-NOV-17 10.00.23.889 AM                3          846842.04     844443.333       108.995                  0                      0                      0          4475478.33         196.333333             5.66666667

Even though this job runs every weekend and we keep 6 weeks of history in the AWR ba9w9cjy87hd8 only showed up yesterday. I checked the SQL and it did not have any constants. It uses bind variables so the same sql_id should apply every week. My guess is that it normally runs so fast that it did not get recorded in the AWR on previous weeks but this week it chose a terrible plan. I see this a lot with SQL statements that have bind variables. I think that the bad execution plan gets chosen based on some abnormal bind variable value and then it sticks in memory.

The other thing I noticed while looking at the session for this job is that it was not using any parallel processes. I looked at the plan and there were no parallel steps. This system uses a lot of parallel query so I suspected that a parallel plan was the normal plan and the bad plan somehow came out with serial execution.

I checked our test databases to see if they had any execution history for this SQL and they did not. So, I was stuck trying to get a better plan and then using a SQL profile to force that plan on the insert statement. I ended up running the explain plan statement without giving the bind variable a value and it came out with a parallel plan. I used the outline hint from that plan to force the insert statement to run the parallel plan against a test database. I ran the insert with a rollback statement so it wouldn’t commit the new rows. Also I picked a bind variable value from the production database’s AWR to run with my test query.

Next I decided to run my test insert statement against production with the rollback statement. The first time I tried the statement it hung on a TM enqueue. I think this was because the insert was using an append hint and the running job was holding a TM lock. After we killed the production job I could run my test insert with the rollback statement.

I had to get the parallel plan into memory or into the AWR so that coe_xfr_sql_profile.sql would pick it up based on the plan hash value. It felt weird running an insert statement for a production table but I tested it on a test database first and made sure it would be rolled back.

I ran this select after the insert and rollback to get the plan for the insert in production:

select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

Part of the output of the select included the plan hash value of the plan that my test insert used:

Plan hash value: 3809184161

Finally, I forced the good plan which had hash value 3809184161 onto the sql_id ba9w9cjy87hd8  using coe_xfr_sql_profile.sql. Then the job ran in a few minutes.

Bobby