Query per vedere se è cambiato il piano d’accesso ai dati – PLAN_HASH_VALUE
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';
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