Query per vedere se è cambiato il piano d’accesso ai dati – PLAN_HASH_VALUE

Published by Patrizia Gardis on

With the Query article to check if the data access plan has changed – PLAN_HASH_VALUE, we have the ability to identify the SQL_ID, the PLAN_HASH_VALUE, and the usage of machine resources with timing and the number of executions of a specific SQL_ID.

Furthermore, if we observe that the PLAN_HASH_VALUE has changed and the resource consumption values have significantly increased, it is possible to revert to the previous data access plan

The selected fields are: SQL_ID – Unique identifier of the SQL statement PLAN_HASH_VALUE – Data access plan assigned by the Oracle optimizer EXECUTIONS – Total number of executions of an SQL_ID BUFFER_GETS – Total amount of memory used by an SQL_ID ELAPSED_TIME – Time used by the statement in microseconds CPU_TIME – Time used by CPU in microseconds DISK_READS – Total number of disk reads performed by an SQL_ID

Additionally, the information is retrieved from the V$SQLAREA table.

Finally, the information is filtered by SQL_ID.

Analisi consumi di un SQL_ID:

set lines 300 
col sql_id for a15
select sql_id, PLAN_HASH_VALUE, EXECUTIONS, BUFFER_GETS, ELAPSED_TIME, CPU_TIME, DISK_READS 
  from v$sqlarea 
 where sql_id='&sql_id';
PLAN_HASH_VALUE
Database Table with modern background

Stay tuned! Non solo PLAN_HASH_VALUE, molto di più….

to viwe my guides on CRS commands click here!
At this LINK, instead, you’ll find the SQL queries.
Resta aggiornato sulle nostre attività entrando a far parte della nostra community:
Facebook
Youtube
Linkedin

For those who haven’t done so yet, we recommend saving this website among your bookmarks, so you always have our solutions to the most common database problems at your fingertips, especially for Oracle databases


0 Comments

Leave a Reply

Avatar placeholder