Query Fast In SQLPlus and Toad but Slow Otherwise
Environment is Oracle 10g on AIX with 10g drivers on Windows XP as the client. Several queries run in less than 9 seconds in Toad and SQLPlus but take hours or never return in Business Objects Deski, MS Access, and SQL Server SSIS. The exact same SQL produces completely different explain plans based on if it is run from Toad/SQLPlus vs the other products I mentioned. All the software is on the same XP machine and does not use middleware. 99 percent of our queries will run fast or slow in all products. What is it that Toad and/or SQLPlus does to run the queries fast? Why does Oracle change explain plans based on what application connects to it? The goal is to run Business Objects reports from Oracle. Any help or direction for further troubleshooting would be appreciated. Thank-you.