Is this a Microsoft or an Oracle Problem?

Question

In my original thread here: How can I fix ORA: 01013 (user requested cancel...) when trying to link Oracle tables in MS Access? I describe an issue attempting to link Oracle tables into a Microsoft Access (office 365) database. The process timed out after entry of a UID and password.

As I researched the problem, I was able to determine that the ODBC drivers and DSN work for ADO, Toad, and Microsoft Power BI (when using a specific query against an Oracle table). I was never able to log entries in the Oracle V$SQL table from either Access or Excel to further troubleshoot the problem.

However, tonight, I was able to get Power BI to recreate the same behavior by attempting to connect through the DSN and browse the tables in Oracle. Oracle captured the SQL call and the result is this gem:

SELECT
    *
FROM
    (
        SELECT
            NULL table_qualifier,
            o1.owner         table_owner,
            o1.object_name   table_name,
            DECODE(o1.owner, 'SYS', DECODE(o1.object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', o1.object_type), 'SYSTEM'
            , DECODE(o1.object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW', 'SYSTEM VIEW', o1.object_type), o1.object_type) table_type,
            NULL remarks
        FROM
            all_objects o1
        WHERE
            o1.object_type IN ('TABLE',
                'VIEW'
            )
        UNION
        SELECT
            NULL table_qualifier,
            s.owner          table_owner,
            s.synonym_name   table_name,
            'SYNONYM' table_type, null remarks
        FROM
            all_objects    o3,
            all_synonyms   s
        WHERE
            o3.object_type IN (
                'TABLE',
                'VIEW'
            )
            AND s.table_owner = o3.owner
            AND s.table_name = o3.object_name
        UNION
        SELECT
            NULL table_qualifier,
            s1.owner          table_owner,
            s1.synonym_name   table_name,
            'SYNONYM' table_type,
            NULL remarks
        FROM
            all_synonyms s1
        WHERE
            s1.db_link IS NOT NULL
    ) tables
WHERE
    1 = 1
    AND ( table_type = 'TABLE'
          OR table_type = 'VIEW' )
ORDER BY
    4,
    2,
    3

I don't know where to start with this query. The second and third subqueries in the union statement are filtered out by the final where clause, so they are useless. The first subquery is attempting to retrieve a list of tables/schemas from all_objects. If I restrict that chunk of SQL to the first 100,000 rows and run it in SQLPLUS, the runtime is over 20 minutes in Oracle 18c (XE). I presume that is because that object is constantly updating, even as the query is running.

The details of the MS ODBC specification to which Oracle certifies are way over my head, so I don't know whether to continue pursuing tickets with Microsoft, or whether to turn my attention to Oracle as the culprit for the problem.

Any and all advice appreciated. I really need to know which party is responsible for the SQL above.

Thanks!

score 1 · Answer 1 · answered May 05 '19 at 03:14

1

This looks like an Oracle problem. Although the error is a direct result of the ODBC timeout settings, that query should not take 20 minutes to finish running.

The first thing to try when running into a data dictionary performance issue is to gather statistics on the data dictionary. This provides Oracle with more information about the sizes of the objects, so it can make better decisions on how to join the tables.

begin
    dbms_stats.gather_fixed_objects_stats;
    dbms_stats.gather_dictionary_stats;
end;
/

If that doesn't work you'll want to shrink the query to the smallest size possible that still has the problem. There are three queries UNION ALL'd together, chances are only one of them is slow. Then we can work on optimizing that one query.

(But tuning a query is a process that requires a lot of back-and-forth, and is difficult to do over the Internet. You might want to try to find a local database administrator who can help. Since you're using Express Edition, you can't contact Oracle Support for help.)

answered May 05 '19 at 03:14

Jon Heller

34,999
6
74
132

Thanks, Jon. I'll try that out. The slow running portion of the query is the subquery against all_objects. The useless two subqueries execute timely. My chief question is why would one use all_objects rather than all_tab_columns for the purpose of generating a list of schema/tables? That seems like an incredibly inefficient approach, – Arlie May 05 '19 at 03:35
@Arlie I believe this query is meant to return all "table-like" things. Anything that can be selected from, such as a table, view, or a synonym that points to another table or view. – Jon Heller May 05 '19 at 05:53
That makes sense, although I note with some humor that any synonyms are ultimately filtered out. I did try the script you recommended and I let the query run indefinitely by disabling the timeout property on the DSN. It did return results after about an hour. If you have any additional database tuning recommendations, perhaps suggested reading, I'm all ears. – Arlie May 05 '19 at 13:53
After digging around on the web, I was able to find other reports of slow performance accessing sys.all_objects and sys.all_synonyms in Oracle 12c and 18c. The behavior in the instance I have here is truly bizarre. The query select count(*) from all_objects takes 25 minutes to run. The query select count(*) from all_objects where object_type='TABLE' runs in 0.10 seconds. The query select count(*) from all_objects where object_in ('TABLE','VIEW') returns me to the 20 minute + runtime realm. So, I am in agreement with you that this seems to be an Oracle issue indeed. – Arlie May 05 '19 at 23:24

score 1 · Answer 2 · answered May 07 '19 at 03:10

If I were able to change the SQL call from Power BI, I find that the following query produces identical output. It runs in less than one second on the laptop where Oracle is installed along with Power BI. That contrasts with almost 30 minutes for the original query. Microsoft, you may want to take a look at how your products connect to the newer Oracle products via DSN:

SELECT
    *
FROM
    (
        SELECT
            NULL table_qualifier,
            o1.owner         table_owner,
            o1.object_name   table_name,
            o1.object_type table_type,
            NULL remarks
        FROM
            all_objects o1
        WHERE
            o1.object_type = 'TABLE' and
            o1.owner not in ('SYS','SYSTEM')
        UNION
        SELECT
            NULL table_qualifier,
            o1.owner         table_owner,
            o1.object_name   table_name,
            o1.object_type table_type,
            NULL remarks
        FROM
            all_objects o1
        WHERE
            o1.object_type = 'VIEW' and
            o1.owner not in ('SYS','SYSTEM')
    ) tables
ORDER BY
    4,
    2,
    3

Is this a Microsoft or an Oracle Problem?

2 Answers2

Linked