🚀 go-pugleaf

"Paul Nichols [TeamB]" <paul@comp.net> wrote:
>Tim Truman wrote:
>> Hello Everyone, 
>> I recently had a developer connect to a production Oracle database (8.1.7) with Database Pilot using Oracle jbdc. A few hours later the database was reported as sluggish when issued certain SQL. Investigation revealed that Database Pilot had issued the ANALYZE statement against several tables. A SQL trace revealed that it does this each time a table is selected in the tree view or against a table specified in an executed SQL statement. 
>> Sample : 
>> This entry was created when I highlighted table TEST in the tree view�
>> PARSING IN CURSOR #5 len=45 dep=0 uid=59 oct=62 lid=59 tim=0 hv=608376794 ad='26902a0c'
>> analyze table TIM.TEST estimate statistics
>> END OF STMT
>> PARSE #5:c=0,e=0,p=0,cr=4,cu=0,mis=1,r=0,dep=0,og=4,tim=0
>> 
>> I need to stop Database Pilot from issuing the analyze statements. Has anybody else encountered this issue? 
>> 
>> Thanks , 
>> 
>> Tim Truman
>> Oracle DBA 
>> 
>That's interesting. I have not noticed these problems.
>
>What version of the Oracle jdbc drivers are you using, drivers are you 
>using, and which jre?
>
>Also, are you resoving these through TNSNames, or direct?
>

You may not notice it if users access table objects outside there schema through grants and do not have the ANALYZE ANY system privilege. Tables in the user's schema can be analyzed however. It may also be that some DBA's keep ANALYZE statistics current so random updates do not severely effect the optimizer. 

We are using the Oracle jdbc driver Versions 9.2.0.1 and 10.1.0.4.0.  Thin direct connections. The JRE version I am using to duplicate the issue is 1.4.2_06.

As a side note there are several business applications connected to the production server using both driver versions and JRE 1.4.2_06 that do ~not~ exhibit this behavior. I just duplicated the behavior on Oracle 10g Release 2 10.2.0.1. So my guess is I can get this to happen on 9i as well. 

To reproduce the behavior : 

-- sql worksheet
connect scott/tiger@orcl; 

-- undo the analyze on scott.tbl_inventory  
analyze table scott.emp delete statistics;

-- verify there are no analyzed based statistics 
select 
  table_name, 
  num_rows, 
  avg_row_len, 
  blocks, last_analyzed 
from 
 user_tables 
where 
 table_name = 'EMP';

-- TABLE_NAME NUM_ROWS AVG_ROW_LEN BLOCKS LAST_ANAL
-- ------------------------------------------------
-- EMP

-- Start Database Pilot then click [+] next to table scott.emp.
-- Then run the above sql again. 

-- TABLE_NAME NUM_ROWS AVG_ROW_LEN BLOCKS LAST_ANAL
-- ------------------------------------------------
-- EMP        14       40          5      13-OCT-05