Painful 41 minute load time for Oracle ESS connection. NEED HELP!

[HowardCrut]

Summary: Painful 41 minute load time for Oracle ESS connection. NEED HELP!

Product: FileMaker Server

Version: Filemaker Server Advanced 11 (Version: 11.0.2.217)

Operating system version: PC server: 2.9 GHz Intel Pentium Pro/II/III running OS Version: Windows NT 5.2 Service Pack 2

Description of the issue:

PROBLEM: My server hosted database with an Oracle ESS connection causes my FMP Client to freeze for 41 minutes. After the 41 minutes of “load time” things run smoothly. The 41 minutes is a deal breaker for me. Can someone PLEASE help me understand? Help me know what I need to do to avoid the load time.

Steps to reproduce the problem:

DETAILS:

I’ve got a PC server running FMSA11 with a hosted database that connects via ODBC to mysql and it works wonderfully. My problem is when I add an ODBC connection to Oracle.

Once I add any ESS Oracle table to the relationship graph and hit “OK” on the Manage Database pop up screen, the Filemaker Client freeze up for 41 minutes, only the spinning beach ball. It does not matter if the table I add to the relationship graph has only two records consisting of two text fields.

After it finally unfreezes, then everything works beautifully. All the Oracle data is displayed. Even when I exit the database, everything works fine the next time I launch it, until, of course I go to a layout with ANY field that requires the connection to the Oracle tables. At that point, I have to pull out my stopwatch/timer to come back in 41 minutes when I can use Filemaker again. Nothing is accessible w/ Filemaker at this point. Another words, it is not just freezing that single window, or that single database, but the entire application is temporarily frozen until it loads everything it is trying to load.

The Oracle box has several PeopleSoft and Oracle databases which I assume consists of hundreds of Oracle tables. I am not the Oracle DBA. I don’t even know what any of the other Oracle databases/tables are being used for. My username/password credentials only permit me to access my single Oracle database which contains about 25 Oracles tables.

If I include even one Oracle table (does not matter which table) in the relationship graph pulling from an Oracle ESS using ODBC, then it causes the same delay of about 41 minutes.

I can’t confirm it, but my guess is that since the data I need to access is on an Oracle server that contains so much other data, then it will severely cripple me. If I had the luxury of having the Oracle data I need to sit on a separate server with nothing else, my guess is that it would be quite fast, but in my case, this is not a possible solution.

After searching for way to long to try to find a solution, I came across:

http://forums.filemaker.com/posts/436b8f977f

where posted 11/25/09 by FluffyBear, ”Filemaker's odbc access is inefficient, that's why it is slow... It's not the odbc layer, it's just FM make really bad calls. Until they program more efficient ways of handling the data, you're not going to see any improvement. If you want to know what the thing does, run a query log on your oracle server and see all the extra stuffs that FM do to fetch a single result and you'll know why it's slow.”

If I connect to the Oracle server from my Filemaker CLIENT on my Mac using the latest Actual ODBC Oracle driver using the same credentials accessing the same tables, then I get the same delay.

PLEASE HELP!!!

-Howard

Expected result: Expect to get 0-1 minute load time.

Actual result: Load time takes 41+ minutes!

Exact text of any error message(s) that appear: There are no error messages.

Configuration information:

OTHER BACKGROUND INFO:

I’m using Filemaker Server Advanced 11 (Version: 11.0.2.217)

PC server: 2.9 GHz Intel Pentium Pro/II/III running OS Version: Windows NT 5.2 Service Pack 2

On the client side, I’m using Filemaker Pro Advanced 11 on a MacBookPro MacOS X 10.6.6

I’m pretty sure the Oracle version is 10g.

On the PC server to install the Oracle ODBC drivers, we installed a client under c:\oracle\client. It is a full client install, so it is bigger. The ODBC connection uses the tnsnames.ora file.

Workaround: The only solution I have found is to pull out a stop watch, come back in 41 minutes, then use the database. If I have to open Manage Database window, then I'll have to wait another 41 minutes.

[Steven H. Blackwell]

PC server: 2.9 GHz Intel Pentium Pro/II/III running OS Version: Windows NT 5.2 Service Pack 2

What version is this? XP 64 bit or Server 2003??

Please confirm, and then we can get started trying to figure out what is happening here. I suspect you need to set up some views in Oracle to avoid massive data transfers.

Steven

[HowardCrut]

Steven,

Thanks for taking a look into this!

I'm running Windows Server 2003.

Howard

[Wim Decorte]

Have you tried building just a view in Oracle and setting your DSN to select just that view?

As it is now, I'm guessing you see a great many oracle tables to choose from?

Where is the oracle database located in relation to you? Same building?

[HowardCrut]

Have you tried building just a view in Oracle and setting your DSN to select just that view?

As it is now, I'm guessing you see a great many oracle tables to choose from?

Where is the oracle database located in relation to you? Same building?

1. I have not tried building just a view in Oracle. Wouldn't that prevent me from being able to edit the content?

2. How can I set my DSN to select just from a select group of the entire Oracle list of tables? You are correct, there are 182 Oracle tables to choose from, most of which I DO NOT WANT included now or in the future (eg. when I'm in Manage Database / Relationships, add a new table, select the Data Source of the Oracle server that was set in the system DSN). All of the tables do have a decent naming convention. And all of the tables I need start with the same MYTABLES "prefix" - such as "MYTABLES.CONTACT" or "MYTABLES.ORDERS" etc. Can I just manually edit the tnsnames.ora file that is stored on my FMServer. The tnsnames.ora file used in the ODBC connection contains network configuration data for all the Oracle databases and tables, not just the ones I need.

3. The Oracle database is in the same building. It is on one of the internal servers maintained by our technology specialists.

Sample from the tnsnames.ora file:

PS.MYOGANIZATION.ORG =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = myserver1.myorganization.org)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = myserver2.myorganization.org)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = myserver3.myorganization.org)(PORT = 1521))

(LOAD_BALANCE = yes)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ps.myorganization.org)

(FAILOVER_MODE =

(TYPE = SELECT)

(METHOD = BASIC)

(RETRIES = 180)

(DELAY = 5)

)

)

)

[HowardCrut]

Can anyone offer some additional advice?

Thank you in advance,

Howard

[Wim Decorte]

Sorry, nothing obvious. This is probably one of these things that can be solved with an on-site visit more efficiently.

If your data doesn't need to be up-to-the-minute fresh you can set up a schedule to dump data out of Oracle and import it into FM nightly?

[mr_vodka]

PROBLEM: My server hosted database with an Oracle ESS connection causes my FMP Client to freeze for 41 minutes. After the 41 minutes of “load time” things run smoothly. The 41 minutes is a deal breaker for me. Can someone PLEASE help me understand? Help me know what I need to do to avoid the load time

Unfortunately, you will get this with large Oracle dbs. We dealt with the issue when ESS first came out and eventually we just gave up and decided to go with a straight SQL import into FileMaker rather than trying to use ESS directly.

FileMaker loads the entire data dictionary and as you make changes to the FileMaker TO graph, it has to read it to load the info as well as when checking if any changes were made to the graph.

An idea that you could try is to have a dedicated filemaker file just for your Oracle TO. Then reference that file in your main file. This should help with the main file's graph.

[LaRetta]

As John points out, the dictionary loads for all objects you have access too… even if that means read-only… such as the ability to read the oracle Meta data “dictionaries.” To avoid the loading of the Meta data create a new account in Oracle which does not have access to anything except the tables / views to be used via ESS.

To test the theory, set up a test server in Oracle which is a decent replica of the current setup… start changing permissions and messing with the user accounts and user account access settings and see what changes improve the performance. Then drop unused objects (databases, tables, & views) and see if having a smaller number of objects has any impact on performance.

Next, if none of those ideas seem to help and the performance is still terrible, try using Smartpill and script a PHP connector to the Oracle server using the direct connect driver available for PHP - it isn't ideal but it may allow you to edit Oracle content. Or use a JDBC connection leveraging 360 works java bridge to FileMaker. These options would be viable or not based on the magnitude and manner in which the Oracle ESS is integrated into the FileMaker solution.

[acacord]

2. How can I set my DSN to select just from a select group of the entire Oracle list of tables? You are correct, there are 182 Oracle tables to choose from, most of which I DO NOT WANT included now or in the future (eg. when I'm in Manage Database / Relationships, add a new table, select the Data Source of the Oracle server that was set in the system DSN). All of the tables do have a decent naming convention. And all of the tables I need start with the same MYTABLES "prefix" - such as "MYTABLES.CONTACT" or "MYTABLES.ORDERS" etc. Can I just manually edit the tnsnames.ora file that is stored on my FMServer. The tnsnames.ora file used in the ODBC connection contains network configuration data for all the Oracle databases and tables, not just the ones I need.

Does anyone have an answer to this? I'm running into the same issue and would like to dramatically reduce the amount of data coming back. I only need a single view right now....

[Ted S]

I've integrated FM and Oracle but not through ESS and it's a read-only setup. I have done what has already been suggested; a daily scheduled import of data from Oracle into FM. My Oracle DB has 1,000 tables and tens of millions of rows in certain tables. I don't import those.

I went with the import method rather than the ESS method because it makes searching (find mode) consistent throughout. With ESS the users had to do Oracle type searching when searching Oracle data and Filemaker type searching when searching FM data. And since your users are working in FM how will they know which fields are Oracle and which are FM? For instance when searching for the word plant in FM you just go into find mode and type plant and it will return all these records:

Soy bean plant

Soy Bean Plant

SOY BEAN PLANT

SoY BeAn PlAnT

In Oracle if you entered plant it would return no records because there are no records have the word plant and only the word plant in lowercase letters. To find the same result set you need to do four searches:

%plant%

%Plant%

%PLANT%

%PlAnT%

My users weren't going to do that. So until they make searching ESS fields exactly like searching native FM fields I'm afraid I can't use ESS.