Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Query performance based on calling app...
I have a pretty simple query inside a stored procedure that joins three
tables.
When the procedure is executed by the third party software (Interworld), I get about 18,000 buffer gets and 18,000 disk reads for the execution of the query (based on Toad's SGA Trace) and it takes about 4 seconds.
When the passing in the same data (the developer swears the data he is giving me is the same) into the procedure from SQL*Plus, it does no disk reads and about 700 buffer gets. It also runs this way if I execute the query directly.
I am flushing the shared pool before each test to better read the data and this database has a large Buffer Cache (500M). Any hints on where to look to figure out why it runs so differently? On a smaller development database, this query performs normally. I am at a loss...
Steve