1

I have a question with an oracle query that takes almost 20 minutes when it is executed from a schema and yet when it runs as sys users it runs in 1 second maximum ... how is this possible? ... I do not know much about tuning plsql and I find it difficult to find the cause of this tremendous delay, I would like to know if there is any tool to autotuning the queries. The sentence in question is:

SELECT FC.ID_SER 
     FROM MIGER.FONT_COUNTER FC
     WHERE TO_CHAR(FC.ID_SER) NOT IN (SELECT 
     ID_SER FROM INAC.SERV)
     -- AND (FC.STATE <>'R' OR FC.STATE <> NULL)
     AND FC.T_ACT='M'

Thank you

1
  • 1
    Could you post the execution plan generated using both users?
    – atokpas
    Feb 12, 2019 at 6:51

2 Answers 2

1

In past I noticed that query plan could be different, because of the Locale (language) being used on the client. Mostly the locale is enherited from client OS.

1
  • It might be because the locale affects string comparison, and string comparison affects indices.
    – peterh
    Jun 5, 2020 at 14:34
0

You can use the TOAD.
Step 1. Shade the query
Step 2. Right click, then, Optimize, then, Auto Optimize SQL from the contextual menu

Remember that when executing a query the first time, it is registered in the database and may take a long time, then the other times it is fast because it uses the query already registered in the database. Maybe the user is the first one to execute the query, that's why it takes so long.

Not the answer you're looking for? Browse other questions tagged or ask your own question.