{"id":2446,"date":"2020-12-22T07:00:00","date_gmt":"2020-12-22T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=2446"},"modified":"2024-09-11T21:33:49","modified_gmt":"2024-09-11T18:33:49","slug":"pos-kanoyme-force-ena-plano-se-vasi-dedomen","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-kanoyme-force-ena-plano-se-vasi-dedomen\/","title":{"rendered":"How to force a plan in an Oracle database"},"content":{"rendered":"

In previous article<\/a> we have seen why a query can run with a different plan and how to force one in SQL Server. In this article we will see how to force a plan in an Oracle database using SQL profile<\/strong>. <\/p>\n\n\n\n

Because the query can run with a different plan<\/h2>\n\n\n\n

When the Query Optimizer<\/strong> chooses which plan to use depends on information such as the statistics, the amount of data, the values of the variables, the indexes that exist and the SQL Profile.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

If either changes, the Optimizer will create a new plan.<\/p>\n\n\n\n

Parameter sniffing and Adaptive Cursor Sharing<\/h2>\n\n\n\n

As in SQL Server <\/strong>so also in Oracle <\/strong>we have his problem parameter sniffing<\/strong>. When we run a procedure or a sql statement that accepts a parameter depending on the value, the query can return 1 line or 100,000. For the Query Optimizer to use a different plan if it increases performance Oracle has brought it Adaptive Cursor Sharing<\/a>.<\/p>\n\n\n\n

How to force a plan using SQL profile<\/h2>\n\n\n\n

That SQL Profile<\/strong> provides statistics for a SQL Statement and through these statistics o Query Optimizer<\/strong> will choose which plan to use.<\/p>\n\n\n\n

We can to create a SQL Profile<\/strong> but so that we use some past shot<\/strong> which is either located in memory (library_cache)<\/strong> either in the system view dba_hist_sqlplan<\/strong>.<\/p>\n\n\n\n

Carolos Sierra from Oracle Support has made a script named coe_xfr_sql_profile.sql<\/em> which makes it easy to create a SQL Profile that forces a plan into a SQL Statement.<\/p>\n\n\n\n

Example<\/h2>\n\n\n\n

Running a query twice, we see in the system view gv$sqlarea<\/a> that the same SQL_ID has a very different execution time per plan. We keep it from there SQL_ID<\/strong> and PLAN_HASH_VALUE <\/strong>which is faster and we want to force.<\/p>\n\n\n\n

select inst_id,sql_text,sql_fulltext,sql_id,hash_value,plan_hash_value,elapsed_time,last_load_time from gv$sqlarea \nwhere sql_text like '%SELECT h.LOGON_TIME%'\norder by sql_id;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Running a query twice we see the same query that has the same SQL_ID <\/strong>to have a different PLAN_HASH_VALUE <\/strong>that is, a different plan.<\/p>\n\n\n\n
In case we don't know the SQL text but we have information about the session. Then you this article<\/a> we can see how through the systemic view gv$session<\/a><\/strong> we will find it easily SQL_ID <\/strong>with the PLAN_HASH_VALUE <\/strong>who ran her every time.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Before we start we need to download the script first coe_xfr_sql_profile.sql<\/em>.<\/p>\n\n\n\n
coe_xfr_sql_profile<\/a>Download<\/a><\/div>\n\n\n\n
We transfer the script to a folder and execute it by putting it SQL_ID <\/strong>of and the PLAN_HASH_VALUE <\/strong>which we will want him to have forever from now on:<\/p>\n\n\n\n
@coe_xfr_sql_profile.sql 4wtpa4vzh8rpb 1851898411;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Then after we execute it, a new one will be created in the path where the script is located coe_xfr_sql_profile_sql_id.sql<\/em>:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
After we run this with sqlplus, the SQL Profile will be created:<\/p>\n\n\n\n
@coe_xfr_sql_profile_sql_id.sql<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
We can see that SQL Profile has been created by the view dba_sql_profiles<\/em>.<\/p>\n\n\n\n
select * from dba_sql_profiles where 1=1 \nand name like '%4wtpa4vzh8rpb%' --sqlID\n--and sql_text like '%h.LOGON_TIME%'  --SQL TEXT<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Now every time we execute the query it will run with the same plan.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
How can we see plan changes in query over time<\/h2>\n\n\n\n
With the following query we can see how many times the plan of a query was changed to which one and when:<\/p>\n\n\n\n
SELECT trunc(sample_time),h.session_id,h.SESSION_SERIAL#,h.sql_id, h.sql_plan_hash_value, SUM(10) ash_secs, 10*COUNT(h.sql_id) awr_secs,min(sample_time)\nFROM  dba_hist_snapshot X, dba_hist_active_sess_history h\nLEFT OUTER JOIN dba_hist_sqltext t ON t.sql_id = h.sql_id\nWHERE 1=1\nand h.sql_id='3c1kubcdjnppq'\nand x.snap_id=h.snap_id\n--and h.dbid=xxxxx\nand x.snap_id between (select max(snap_id) - 800 from dba_hist_snapshot) and (select max(snap_id) from dba_hist_snapshot)\nGROUP BY trunc(sample_time),h.session_id,h.SESSION_SERIAL#,h.sql_id, h.sql_plan_hash_value\norder by min(sample_time) desc;<\/pre>\n\n\n\n
* However, if we do not have Oracle DB enterprise edition but standard, we will not be able to find the information from historical views, so we can only see from memory library cache<\/strong> with the following query.<\/em><\/p>\n\n\n\n
select plan_hash_value,sql_id,timestamp from gv$sql_plan\nwhere SQL_ID='3c1kubcdjnppq'\norder by timestamp desc<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
We can see the entire SQL Statement by having the SQL_ID with the complete snapshots it ran:<\/p>\n\n\n\n
SELECT * from table(dbms_xplan.display_awr('3c1kubcdjnppq',null,null,'ADVANCED'));<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
To delete a bad shot from the SGA shared pool <\/h2>\n\n\n\n
In order to erase from the memory a bad plan from the SGA we should use the procedure dbms_shared_pool.purge<\/em>.<\/p>\n\n\n\n
First we find the value we want to delete from the view sqlarea<\/em>:<\/p>\n\n\n\n
select address, hash_value from gv$sqlarea where sql_id='4wtpa4vzh8rpb';<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Having the information we need, we can perform the following procedure:  <\/p>\n\n\n\n
begin\nsys.DBMS_SHARED_POOL.purge('000000006AE41DF0,4278476459', 'C');\nend;<\/pre>\n\n\n\n
To delete the SQL Profile<\/h2>\n\n\n\n
If we want to delete one of the SQL Profiles we have created, then we run the following filling in the corresponding name of the SQL Profile:<\/p>\n\n\n\n
begin\ndbms_sqltune.drop_sql_profile('coe_4wtpa4vzh8rpb_185189411');\nend;<\/pre>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n