Hello, today we are going to teach you how to find a query in Oracle with system views.
First let’s see what the situation is in which we must analyze these plans.
Search for SQL_ID and SQL_TEXT with HASH_VALUE
We have the following HASH_VALUE: 2433220526
We need to find both your SQL_ID and your SQL_TEXT to see which sentence is being executed. To do this we execute:
select SQL_ID , sql_text from V$sqltext where hash_value=2433220526 order by piece asc;
We order by the pieces ascending and obtain:
select SQL_ID , sql_text from V$sqltext where hash_value=2433220526 order by piece asc; SQL_ID SQL_TEXT ------------ -------------------------------------------------------- fcs9x3q8hgzxf SELECT COUNT (*) FROM TEMP_CAB_MOV TCM , PEDIDOS_VEN_COM_LIN PVC fcs9x3q8hgzxf WHERE PVC.SERIE_NUMERACION = TCM.SERIE_PEDIDO AND PVC.NUMER fcs9x3q8hgzxf O_PEDIDO_COM = TCM.NUMERO_PEDIDO AND PVC.ORGANIZACION_COMPRAS fcs9x3q8hgzxf = TCM.ORGANIZACION_COMP AND PVC.NUMERO_LINEA_COM = TCM.NUMERO fcs9x3q8hgzxf _LINEA AND PVC.EMPRESA = TCM.CODIGO_EMPRESA AND TCM.CODIGO_EM fcs9x3q8hgzxf PRESA = :b1 AND TCM.CODIGO_USUARIO = :b2 AND TCM.FECHA_INICI fcs9x3q8hgzxf O = :b3 AND TCM.CONTADOR = :b4 7 rows selected.
Estimate execution plan
We would already have our select. Now, let’s estimate your execution plan:
This would be the ideal scenario, that the query still remained in the V$SQLAREA and V$SQLTEXT views. The problem appears when we need to find a query that no longer appears in these dynamic views because its execution plan has already changed.
It is a recurring problem when analyzing the statspack reports, it shows us the OLD_HASH_VALUE that we will no longer find in these views.
What do we need to find a query in Oracle?
We have a view that we will attack to find such querys.
The view is stats$sqltext.
We have the following STATSPACK:
Let’s go to the “SQL ordered by CPU” section to find the statement that consumes the most CPU.
In our case, we see that the sentence is not complete and that if we look for it in the V$ it does not appear:
What to do now? All is not lost, we can still consult the stats$sqltext view
select distinct SQL_ID from STATS$SQLTEXT where SQL_ID in (2433220526) ;
We have already found the query!! We can already see the execution plan and see how to optimize it!
Now we execute explain plan plan and then select * from table(dbms_xplan.display)
Just this query, delays its execution time by an Oracle bug referred to in :
We have 2 options, patch or change the parameter:
alter system SET "_push_join_union_view" = FALSE;
If you don’t want to miss any of these entries, sign up for our monthly newsletter. You can see more posts about Oracle here: https://www.gpsos.es/tag/oracle/
See you in future posts.
Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .
Follow GPS on LinkedIn