Board index » jbuilder » Database Pilot issues analyze statement

Database Pilot issues analyze statement


2005-10-12 11:25:17 PM
jbuilder7
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
 
 

Re:Database Pilot issues analyze statement

Tim Truman wrote:
Quote
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?
 

Re:Database Pilot issues analyze statement

"Paul Nichols [TeamB]" < XXXX@XXXXX.COM >wrote:
Quote
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
 

{smallsort}

Re:Database Pilot issues analyze statement

"Tim Truman" < XXXX@XXXXX.COM >wrote in message
Quote

>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.
Quote

Ah, that explains things a little more. I would suspect that the Connection
object meta data sees the Analyze Any option and uses it by default.
Quote
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.
Quote

Thought I would ask. The Oracle site was vague in the past, about which
drivers would be used. On Oracle 8i, for instance, they recommended
classes12.zip, which did not work well on 1.4.x.
Quote
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.
Quote

I do not know how you are going to adjust this behavior from DBPilot
however.
Hint: Try Squirrel. Does it exhibit the same behavior?
squirrel-sql.sourceforge.net/
 

Re:Database Pilot issues analyze statement

Quote
Hint: Try Squirrel. Does it exhibit the same behavior?
squirrel-sql.sourceforge.net/
Yes. When the metadata indexes tab is selected.
Scary.
But I would guess the code could be modified to eliminate this behavior.
I have read numerous documents and articles up to this point.
It would seem the only way to quickly solve the issue is to provide a single user account for each developer and grant in the objects they need access to. Keeping access to production schemas off limits. I am perplexed however as to why these
tools issue commands that affect the operation behavior of
the database. ANALYZE is a well documented command affecting
the operation of the CBO.
In any event, thank you Paul (TeamB) for taking interest in my issue and providing assitance. It is appreciated.
Tim Truman
Oracle DBA
 

Re:Database Pilot issues analyze statement

Quote
I am willing to wager that most tools are using the
Connection classes getMetaData() from the java.sql
package, which probably uses only default settings and does
not allow overrides to these methods or accessor methods
(sets) for database vendor specifics (such as those in
Oracle). However, the Oracle drivers may allow for this
directly. Since I am not at work, and I do not have Oracle
at home, I cannot test my theory. I will do so this week
(when I get some time) and let you know what I find,
if you do not beat me to it :)
Paul,
You were on the right track. I am no Java expert but I
managed a small program that connects with the database and
gathers metadata. I discovered that databaseMetaData.getIndexInfo
causes the Analyze. Even if Connection.getMetaData()is
typecast with OracleDatabaseMetaData.
// code snip - causes analyze but should not
rset = metadata.getIndexInfo(null, "SCOTT","EMP",false,true);
With that info I googled 'DatabaseMetaData.getIndexInfo analyze'
I found this on OTN:
forums.oracle.com/forums/thread.jspa
I looked up the bug number mentioned on MetaLink. It describes the issue exactly but the bug has no resolution and has been dormant since 07-FEB-2003.
Good call.
 

Re:Database Pilot issues analyze statement

"Tim Truman" < XXXX@XXXXX.COM >wrote in message
Quote

Paul,

You were on the right track. I am no Java expert but I
managed a small program that connects with the database and
gathers metadata. I discovered that databaseMetaData.getIndexInfo
causes the Analyze. Even if Connection.getMetaData()is
typecast with OracleDatabaseMetaData.

// code snip - causes analyze but should not
rset = metadata.getIndexInfo(null, "SCOTT","EMP",false,true);

With that info I googled 'DatabaseMetaData.getIndexInfo analyze'

I found this on OTN:
forums.oracle.com/forums/thread.jspa

I looked up the bug number mentioned on MetaLink. It describes the issue
exactly but the bug has no resolution and has been dormant since
07-FEB-2003.
Quote

Good call.

Well put one up for me on the educated guess column!!!
I guess you will need to write Oracle a note and ask what gives :)
Glad I could be of service. Take care..
Paul
 

Re:Database Pilot issues analyze statement

Tim Truman wrote:
Quote

>Hint: Try Squirrel. Does it exhibit the same behavior?
>squirrel-sql.sourceforge.net/

Yes. When the metadata indexes tab is selected.

Scary.

But I would guess the code could be modified to eliminate this behavior.

>It would seem the only way to quickly solve the issue is to provide a
single user account for each developer and grant in the objects they need
access to. Keeping access to production schemas off limits. I am perplexed
however as to why these tools issue commands that affect the operation
behavior of the database. ANALYZE is a well documented command affecting
the operation of the CBO.

In any event, thank you Paul (TeamB) for taking interest in my issue and
providing assitance. It is appreciated.

Tim,
You may get the developers (or you) to try using the Oracle drivers for your
connections, rather than the java.sql drivers and use the Oracle Drivers.
For instance, try typecasting the Connection.getMetaData() with
OracleDatabaseMetaData dmd =
(OracleDatabaseMetaData)connection.getMetaData();
Check through the dmd instance of OracleDatabaseMetaData. See if it will
return the ANALYSE option. If so, you can probably change this value using
the API.
I am willing to wager that most tools are using the Connection classes
getMetaData() from the java.sql package, which probably uses only default
settings and does not allow overrides to these methods or accessor methods
(sets) for database vendor specifics (such as those in Oracle). However,
the Oracle drivers may allow for this directly. Since I am not at work, and
I do not have Oracle at home, I cannot test my theory. I will do so this
week (when I get some time) and let you know what I find, if you do not
beat me to it :)
Good luck (to both of us).
 

Re:Database Pilot issues analyze statement

Quote
Glad I could be of service. Take care..

Paul
I plan to contact Oracle in the next few business days.
For anybody interested in following the issue on Metalink
the bug # is 2593780. I will reference this # in a new tar
and hopefully get Oracle moving on it. We are a small shop
however.
In the mean time the workaround mentioned earlier in the
thread will keep the bug at bay.
Paul, thank you again for taking an interest in my issue.
Tim