SQL Tuning need an Oracle Client to function

Good morning TOAD team,

I've tried to tune SQL in TOAD on my laptop, with no Oracle installed. TOAD asks you to defining Oracle_Home. I added a n Oracle Home, without adding Oracle client, but still blocked that I need Oracle Home defined. Did I define it incorrectly? Does TOAD require Oracle to tune?

image

The bin directory is empty, but exists in 19.2.0 and client.

set ORACLE_HOME=C:\oracle\19.2.0\client
set
pause
"C:\Program Files\Quest Software\Toad for Oracle 16.2\Toad.exe"

Larry

Hi Larry,

This error message is not telling you that you need to set the ORACLE_HOME environment variable (You shouldn't have to set that manually). The error is SQL Optimizer (or one of its DLLs) complaining that it can't find an Oracle client.

SQL Optimizer requires an Oracle client. I may be able to make a change so that it can discover the installed client that Toad isn't using. Or I may have to make a change so that it just gives you a nicer error message.

Please let me know exactly what you clicked to tune sql and I'll try to reproduce it and fix.

For now, you can work around it by using your 19c client to make your connection in Toad.

Thanks.

-John

Thank you John for the quick reply.

  1. I have no Oracle client installed. Per your discussion, modern Oracle clients all have memory leaks. I've forgotten which you recommend. Our database is still 12C, latest patches. But upgrade is in the near future. That was my thought before COVID-19 began, too.

  2. I choose the top option, Auto Optomize SQL, for SQL tuning in TOAD itself for this test, although I have used all the options in the past:

image

Larry

Hi Larry,

Thanks for the details. I can reproduce that if no client is installed. I'll make a change to improve the error message.

Your options are

  • Install and use an 18c client (it does not leak memory)
  • Install and use a 19c or 21c client (they do leak, but it's not terrible. Worst case, you just restart Toad every now and then).
  • Install any of the above and leave Toad in "No client" mode. SQL Optimizer will find the client and use it even if Toad is not using it.

-John

1 Like