Steps to generate good plan from nonPROD to PROD
PT Issue: step by step Doc to generate good plan from nonPROD to PROD.
Issue in detail:
- Query Performance of Hyperion application monthly batch job in PROD is very slow and not moving further after 10 hours. As per Application team Normal execution time is below 15 minutes and No code changes happened. Application team uses Oracle Data Integrator to schedule the batch jobs.
- Application team informed that the same data volume is present in PROD and non-PROD and queries are running very fast in non-PROD.
- Five queries had been found in PROD with slow performance.
- In the non-prod the same queries executed in 5 to 10 minutes, but in the prod, query had been running for 10 hours.
- Verified that the stats have been gathered on all schemas and found that the plan hash value is different in PROD Vs non-PROD environments even though the data volume is same in both PROD and non-PROD.
- It had been found problematic SQL_IDs and then created profiles in the non-PROD and then applied in the PROD one after the other query.
- Doc ID 1487302.1 followed to resolve the issue.
–> Using Sqltxplain to create a ‘SQL Profile’ to consistently reproduce a good plan (Doc ID 1487302.1)
Steps followed to resolve issue:
1) On both PROD and nonPROD
Download sqltxplain utility from
SQLT (SQLTXPLAIN) – Tool that helps to diagnose a SQL statement performing poorly (Doc ID 215187.1)
The script named coe_xfr_sql_profile.sql is provided under the sqlt/utl directory.
Unzip sqlt.zip on both databases.
2) On Non PROD
Find the sql_id and the plan_hash_value (if there is more than one plan) for the statement that you want to create the profile against.
To get SQL_ID è select sql_id from v$session where sid=<SID>;
Having the sql_id, we can obtain the “plan_hash_value” running the statement below. Note that if there is more than one plan for the same sql, it will show multiples. And based on latest SNAP_ID(AWR) take the current PLAN_HASH_VALUE
To get the plan_hash_valueè
| SQL> set lines 100 pages 100;
SQL> col plan_hash_value format 999999999999; SQL> col elapse_us format 999999999999999999; SQL> col buffer_gets_total format 999999999999999999; SQL> select snap_id, plan_hash_value, executions_total, elapsed_time_total elapse_us, buffer_gets_total 2 from dba_hist_sqlstat 3 where sql_id=’4tvpf2r4p5747′ 4 and snap_id in(select snap_id from dba_hist_snapshot where begin_interval_time>=sysdate -6); |
Output of above Query:
SNAP_ID PLAN_HASH_VALUE EXECUTIONS_TOTAL ELAPSE_US BUFFER_GETS_TOTAL
———- ————— —————- ——————- ——————-
19356 3296833727 1 113652024 1466917
OR
| WITH p AS ( SELECT plan_hash_value FROM gv$sql_plan WHERE sql_id = TRIM(‘&&sql_id.’) AND other_xml IS NOT NULL UNION SELECT plan_hash_value FROM dba_hist_sql_plan WHERE sql_id = TRIM(‘&&sql_id.’) AND other_xml IS NOT NULL ), m AS ( SELECT plan_hash_value, SUM(elapsed_time)/SUM(executions) avg_et_secs FROM gv$sql WHERE sql_id = TRIM(‘&&sql_id.’) AND executions > 0 GROUP BY plan_hash_value ), a AS ( SELECT plan_hash_value, SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs FROM dba_hist_sqlstat WHERE sql_id = TRIM(‘&&sql_id.’) AND executions_total > 0 GROUP BY plan_hash_value ) SELECT p.plan_hash_value, ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs FROM p, m, a WHERE p.plan_hash_value = m.plan_hash_value(+) AND p.plan_hash_value = a.plan_hash_value(+) ORDER BY avg_et_secs NULLS LAST; |
Output for the above query:
Enter value for sql_id: 4tvpf2r4p5747
PLAN_HASH_VALUE AVG_ET_SECS
————— ———–
3296833727 .089
3) ON Non PROD
Having the sql_id and the plan_hash_id, run the following:
| sqlplus / as sysdba
SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE]; start coe_xfr_sql_profile.sql 4tvpf2r4p5747 3296833727 |
This will generate a script named like:
coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql
For example:
coe_xfr_sql_profile_4tvpf2r4p5747_3296833727.sql
This file contains the stored profile from the database that you obtained it from. You can now use this script to apply the same plan to another database:
SQL> START coe_xfr_sql_profile.sql 4tvpf2r4p5747 3296833727
Parameter 1:
SQL_ID (required)
PLAN_HASH_VALUE AVG_ET_SECS
————— ———–
3296833727 112.573
Parameter 2:
PLAN_HASH_VALUE (required)
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : “4tvpf2r4p5747”
PLAN_HASH_VALUE: “3296833727”
SQL>BEGIN
2 IF :sql_text IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20100, ‘SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).’);
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
Execute coe_xfr_sql_profile_4tvpf2r4p5747_3296833727.sql
on TARGET system in order to create a custom SQL Profile
with plan 3296833727 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
Profiles that were generated for PROD env from non- PROD shown below in the screen shot.
In non production:
| coe_xfr_sql_profile_4tvpf2r4p5747_3296833727.sql coe_xfr_sql_profile_gbgahmaan5w7u_3956090555.sql coe_xfr_sql_profile_3pwx7b1v2s8wg_3763355411.sql coe_xfr_sql_profile_6ux6hwwqjw83y_4208858076.sql coe_xfr_sql_profile_5kprm78jk2da0_2871340315.sql |
4) ON PROD
Just execute this script in the DB where you want to create this profile
Copy from NONPROD to PROD
sqlplus / as sysdba
SQL> START coe_xfr_sql_profile_4tvpf2r4p5747_3296833727.sql
PROD Database:
| coe_xfr_sql_profile_4tvpf2r4p5747_3296833727.sql coe_xfr_sql_profile_4tvpf2r4p5747_3296833727.log coe_xfr_sql_profile_gbgahmaan5w7u_3956090555.sql coe_xfr_sql_profile_gbgahmaan5w7u_3956090555.log coe_xfr_sql_profile_3pwx7b1v2s8wg_3763355411.sql coe_xfr_sql_profile_3pwx7b1v2s8wg_3763355411.log coe_xfr_sql_profile_6ux6hwwqjw83y_4208858076.sql coe_xfr_sql_profile_6ux6hwwqjw83y_4208858076.log coe_xfr_sql_profile_5kprm78jk2da0_2871340315.sql coe_xfr_sql_profile_5kprm78jk2da0_2871340315.log |
Note:
After creation of SQL profiles in PROD with good plan from non prod ,Each and every Querys’ execution completed in less than 10 mins separately.
See Also: