A user mentioned one call is faster in test than in prod. And the table has the same size. How could we tune the production to make it quick?
user007@PROD> select count(*) from t; COUNT(*) ---------- 19832501 Elapsed: 00:03:05.00
Let’s try in test :
user007@TEST> select count(*) from t select count(*) from t * ERROR at line 1: ORA-00942: table or view does not exist Elapsed: 00:00:00.16
Wow, 00:00:00.16! This is a much quicker response time. Probably the ORA-942 was catched by the application and the user did not realise it…
Next time someone ask you to tune a count(*), just revoke access to the table to optimize the response time