Oracle woes

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Oracle woes

Rob Manning
All,

I've solved the following issue (locally) but I am not quite ready to
check it in:

http://sourceforge.net/tracker/index.php?func=detail&aid=1276957&group_id=28383&atid=393414
(Oracle Protocol Violation)

My solution was to synchronize access to the DMD inside of
SQLDatabaseMetaData.  Since it is
pretty common that we do
session.getConnection().getDatabaseMetaData.getXXX, there were
many places in the code that I had to touch to do this.  Now what I've
discovered is, I've made the
"freezing" that SQuirreL does when you open a session much worse.  I
believe the cause is the event
thread doing database work. So, in my new rabbit trail, I've discovered
an optimization for Oracle
that I'm not quite sure how to implement.  The reason I'm picking on
Oracle, is because that is where
the "freezing" is the worst.  And here is why.  Unlike other databases
it seems when we ask for
schemas for Oracle, we get every schema in the database,  not just the
ones that have tables that we
can access.  And so, as we load the schemas into the object tree, we
access the database many times
for objects that we probably don't have access to.  So, I've discovered
an Oracle-specific way to
determine what schemas we should display: select distinct owner from
user_tab_privs  + dmd.getUserName.
In my Oracle 9i database this eliminates about 30 Oracle internal
schemas.  Now here's my question:
In DatabaseExpander.createSchemaNodes we do md.getSchemas() and get this
big list.  Should I
provide an alternative INodeExpander implementation for
DatabaseObjectType SESSION that
overrides this default behavior in DatabaseExpander?  Or should it be a
new API method that plugins
would override that would return a list of schemas for a particular
database type and session?

In case someone has the idea that schema prefix session property or even
the object filter would suit this
purpose, I say those "solutions" are inadequate, because :

1) I believe session properties are global - it's not currently possible
to have alias-specific session properties
2) Even if we did have alias-specific session properties, I would have
to manually change these for *every*
    session alias I've created.  I currently have about 20 aliases
defined for Oracle. Having SQuirreL do this
    for me every time I connect would be preferable over manually
setting this and changing it as things change
    in the database.

Rob


CollabraSpace - Revolutionary Collaboration
Visit us at http://www.collabraspace.com
This message has been scanned for viruses by
ClamAV v0.83




-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems?  Stop!  Download the new AJAX search engine that makes
searching your log files as easy as surfing the  web.  DOWNLOAD SPLUNK!
http://ads.osdn.com/?ad_id=7637&alloc_id=16865&op=click
_______________________________________________
Squirrel-sql-develop mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop
Reply | Threaded
Open this post in threaded view
|

Re: Oracle woes

Gerd Wagner
Rob,

just to make sure I understand things right.

1. You found out that bug "[1276957] Oracle Protocol Violation" is a
concurrency problem?

2. "[1276957] Oracle Protocol Violation" is the same problem you
discussed with Andrea?

3. "[1276957] Oracle Protocol Violation" is also the problem that keeps
some of your colleagues from using SQuirreL

3. You solved the problem doing some synchronization and for this
"touched many places in the code"?

4. Your version that solved the problem is here:
http://squirrel-sql.sourceforge.net/downloads/squirrel-sql-2.1_robs_build-install.jar?

5. The work you did to solve the problem makes worse the freezing of
SQuirreL during the opening of a Session mostly on Oracle.

6. You found a way to reduce the freezing on Oracle by providing an
alternative INodeExpander implementation for DatabaseObjectType SESSION
that reduces Schema loading and this INodeExpander implementation of
yours is placed in the Oracle Plugin?

If you say yes to all of these questions I'd recommend you to commit
your work.

I tested your version against several RDBMses including our not so small
database via two VPN connections: From my home to work and from there to
on of our customers. I felt the freezing was acceptable.

Perhaps we should hint Oracle users that they better use the Oracle
Plugin. Perhaps we should do this during installation as well as on our
Web page.


Gerd



Rob Manning wrote:

> All,
>
> I've solved the following issue (locally) but I am not quite ready to
> check it in:
>
> http://sourceforge.net/tracker/index.php?func=detail&aid=1276957&group_id=28383&atid=393414 
>
> (Oracle Protocol Violation)
>
> My solution was to synchronize access to the DMD inside of
> SQLDatabaseMetaData.  Since it is
> pretty common that we do
> session.getConnection().getDatabaseMetaData.getXXX, there were
> many places in the code that I had to touch to do this.  Now what I've
> discovered is, I've made the
> "freezing" that SQuirreL does when you open a session much worse.  I
> believe the cause is the event
> thread doing database work. So, in my new rabbit trail, I've discovered
> an optimization for Oracle
> that I'm not quite sure how to implement.  The reason I'm picking on
> Oracle, is because that is where
> the "freezing" is the worst.  And here is why.  Unlike other databases
> it seems when we ask for
> schemas for Oracle, we get every schema in the database,  not just the
> ones that have tables that we
> can access.  And so, as we load the schemas into the object tree, we
> access the database many times
> for objects that we probably don't have access to.  So, I've discovered
> an Oracle-specific way to
> determine what schemas we should display: select distinct owner from
> user_tab_privs  + dmd.getUserName.
> In my Oracle 9i database this eliminates about 30 Oracle internal
> schemas.  Now here's my question:
> In DatabaseExpander.createSchemaNodes we do md.getSchemas() and get this
> big list.  Should I
> provide an alternative INodeExpander implementation for
> DatabaseObjectType SESSION that
> overrides this default behavior in DatabaseExpander?  Or should it be a
> new API method that plugins
> would override that would return a list of schemas for a particular
> database type and session?
>
> In case someone has the idea that schema prefix session property or even
> the object filter would suit this
> purpose, I say those "solutions" are inadequate, because :
>
> 1) I believe session properties are global - it's not currently possible
> to have alias-specific session properties
> 2) Even if we did have alias-specific session properties, I would have
> to manually change these for *every*
>    session alias I've created.  I currently have about 20 aliases
> defined for Oracle. Having SQuirreL do this
>    for me every time I connect would be preferable over manually setting
> this and changing it as things change
>    in the database.
>
> Rob
>
>
> CollabraSpace - Revolutionary Collaboration
> Visit us at http://www.collabraspace.com
> This message has been scanned for viruses by
> ClamAV v0.83
>
>
>
>
> -------------------------------------------------------
> This SF.net email is sponsored by: Splunk Inc. Do you grep through log
> files
> for problems?  Stop!  Download the new AJAX search engine that makes
> searching your log files as easy as surfing the  web.  DOWNLOAD SPLUNK!
> http://ads.osdn.com/?ad_id=7637&alloc_id=16865&op=click
> _______________________________________________
> Squirrel-sql-develop mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop
>



-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems?  Stop!  Download the new AJAX search engine that makes
searching your log files as easy as surfing the  web.  DOWNLOAD SPLUNK!
http://ads.osdn.com/?ad_id=7637&alloc_id=16865&op=click
_______________________________________________
Squirrel-sql-develop mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop
Reply | Threaded
Open this post in threaded view
|

Re: Oracle woes

Rob Manning
Gerd Wagner wrote:

> Rob,
>
> just to make sure I understand things right.
>
> 1. You found out that bug "[1276957] Oracle Protocol Violation" is a
> concurrency problem?
>
> 2. "[1276957] Oracle Protocol Violation" is the same problem you
> discussed with Andrea?
>
> 3. "[1276957] Oracle Protocol Violation" is also the problem that
> keeps some of your colleagues from using SQuirreL
>
> 3. You solved the problem doing some synchronization and for this
> "touched many places in the code"?
>
> 4. Your version that solved the problem is here:
> http://squirrel-sql.sourceforge.net/downloads/squirrel-sql-2.1_robs_build-install.jar?
>
>
> 5. The work you did to solve the problem makes worse the freezing of
> SQuirreL during the opening of a Session mostly on Oracle.
>
> 6. You found a way to reduce the freezing on Oracle by providing an
> alternative INodeExpander implementation for DatabaseObjectType
> SESSION that reduces Schema loading and this INodeExpander
> implementation of yours is placed in the Oracle Plugin?

Yes to all of the above.  I have done this over the past two weeks, so I
had forgotten how much work I've done.

>
> If you say yes to all of these questions I'd recommend you to commit
> your work.
>
Ok.  It occurred to me that I need to cover the case where we login as a
"system" user on Oracle.
So, once that is solved I'll checkin the plugin changes I've made.  I'll
also get the core changes in as soon as
I can.

> I tested your version against several RDBMses including our not so
> small database via two VPN connections: From my home to work and from
> there to on of our customers. I felt the freezing was acceptable.

Thanks for testing it out.

>
> Perhaps we should hint Oracle users that they better use the Oracle
> Plugin. Perhaps we should do this during installation as well as on
> our Web page.

Yes indeed.  Thanks again for the followup - It's good to have you back.

Rob


CollabraSpace - Revolutionary Collaboration
Visit us at http://www.collabraspace.com
This message has been scanned for viruses by
ClamAV v0.83




-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems?  Stop!  Download the new AJAX search engine that makes
searching your log files as easy as surfing the  web.  DOWNLOAD SPLUNK!
http://ads.osdn.com/?ad_id=7637&alloc_id=16865&op=click
_______________________________________________
Squirrel-sql-develop mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop