Find a query in Oracle with stats$sqltext

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:

Find query in Oracle

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:

stats sql text

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:

find a query in Oracle

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 and then select * from table(dbms_xplan.display)

Just this query, delays its execution time by an Oracle bug referred to in :

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=369334702236336&parent=EXTERNAL_SEARCH&sourceId=PROBLEM&id=2382419.1&_afrWindowMode=0&_adf.ctrl-state=ioe1lcbsm_4

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.

Comments are closed.