1

I'm encountering an "issue" (at least a strange/slow behavior) with a new installation of Oracle 12c on Windows Server 2019, with this query which takes at least 5 minutes to complete:

    SELECT COUNT(*) FROM SYS.ALL_VIEWS;

Why does it take so much time? What am I missing?

Oracle 12c is running on Win2019 (in a VMWare VM) on a SSD. The VM has 5 GB ram. It's probably not enough for a "real" database, but mine contains a default generic database (no user data) because I only need to make a quick test for a customer.

Thank you.

6
  • How is this preventing you from performing the actual test? Oct 20 at 14:16
  • You're right, it does not prevent me from performing it. But since we are using this view, I was wondering why some queries run so slowly (because I guess that since it has happened to me on a new Oracle configuration without any user data, it might happen at some customer's places too)
    – ggo
    Oct 20 at 15:10
  • Have you gathered dictionary stats and fixed object stats (dbms_stats.gather_dictionary_stats and dbms_stats.gather_fixed_object_stats)? It sounds like you're getting bad query plans so making sure the optimizer has the right stats would be step 1. Nov 10 at 12:34
  • @Justin Cave Thanks for the suggestion. Unfortunately it does not improve this query execution time.
    – ggo
    Nov 10 at 14:18
  • Out of curiousity, does anything change if you drop dictionary and fixed object stats (dbms_stats.delete_dictionary_stats and dbms_stats.delete_fixed_object_stats)? Nov 10 at 14:27
3
SELECT COUNT(*) FROM SYS.ALL_VIEWS;

Is not a simple query. ALL_VIEWS is a very complicated view, it has many joins requiring special logic to determine what the calling user is actually allowed to see.

You can use all_views to see the logic behind all_views, this is how it looks on 19c.

select OWNER, VIEW_NAME, TEXT_LENGTH, TEXT, TEXT_VC,TYPE_TEXT_LENGTH, TYPE_TEXT,
       OID_TEXT_LENGTH, OID_TEXT, VIEW_TYPE_OWNER, VIEW_TYPE, SUPERVIEW_NAME,
       EDITIONING_VIEW, READ_ONLY,  CONTAINER_DATA, BEQUEATH,
       ORIGIN_CON_ID, DEFAULT_COLLATION, CONTAINERS_DEFAULT, CONTAINER_MAP,
       EXTENDED_DATA_LINK, EXTENDED_DATA_LINK_MAP, HAS_SENSITIVE_COLUMN
from int$dba_views
where (OWNER = SYS_CONTEXT('USERENV', 'CURRENT_USER')
       or OBJ_ID(OWNER, VIEW_NAME, 4, OBJECT_ID) in
            (select oa.obj#
             from sys.objauth$ oa
             where oa.grantee# in ( select kzsrorol
                                         from x$kzsro
                                  )
            )
        or /* user has system privileges */
           /* 4 is the type# for Views. See kgl.h for more info */
          exists (select null from v$enabledprivs
                  where priv_number in (-45 /* LOCK ANY TABLE */,
                                        -47 /* SELECT ANY TABLE */,
                                        -397/* READ ANY TABLE */,
                                        -48 /* INSERT ANY TABLE */,
                                        -49 /* UPDATE ANY TABLE */,
                                        -50 /* DELETE ANY TABLE */)
                  )
      )

Simple enough? Well, int$dba_views is actually a view itself, so lets go deeper, this view is defined by

select u.name, u.user#, o.name, o.obj#, o.type#, v.textlength, v.text,
       getlong(1, v.rowid),
       t.typetextlength, t.typetext,
       t.oidtextlength, t.oidtext, t.typeowner, t.typename,
       decode(bitand(v.property, 134217728), 134217728,
              (select sv.name from superobj$ h, sys."_CURRENT_EDITION_OBJ" sv
              where h.subobj# = o.obj# and h.superobj# = sv.obj#), null),
       decode(bitand(v.property, 32), 32, 'Y', 'N'),
       decode(bitand(v.property, 16384), 16384, 'Y', 'N'),

       decode(bitand(v.property/4294967296, 134217728), 134217728, 'Y', 'N'),
       decode(bitand(o.flags,8),8,'CURRENT_USER','DEFINER'),
       case when bitand(o.flags, (65536+131072+4294967296))>0 then 1 else 0 end,
       to_number(sys_context('USERENV', 'CON_ID')),
       nls_collation_name(nvl(o.dflcollid, 16382)),
       -- CONTAINERS_DEFAULT
       decode(bitand(v.property, power(2,72)), power(2,72), 'YES', 'NO'),
       -- CONTAINER_MAP
       decode(bitand(v.property, power(2,80)), power(2,80), 'YES', 'NO'),
       -- EXTENDED_DATA_LINK
       decode(bitand(v.property, power(2,52)), power(2,52), 'YES', 'NO'),
       -- EXTENDED_DATA_LINK_MAP
       decode(bitand(v.property, power(2,79)), power(2,79), 'YES', 'NO'),
       -- HAS_SENSITIVE_COLUMN
       decode(bitand(v.property, power(2,89)), power(2,89), 'YES', 'NO')
from sys."_CURRENT_EDITION_OBJ" o, sys.view$ v, sys.user$ u, sys.typed_view$ t
where o.obj# = v.obj#
  and o.obj# = t.obj#(+)
  and o.owner# = u.user#

Guess what, sys."_CURRENT_EDITION_OBJ" is also a view which has complex filters. I won't keep pasting as you can follow the trail on your own 12c installation (and it will likely be slightly different).

But the important thing to note here is that actual usage of ALL_VIEWS is fast when you are looking up a view by name, it is not fast to compute every single view that you might be able to use (and that is reasonable as it's not something you would ever need to do in a hurry).

If you want to use ALL_VIEWS in some sort of benchmark then you could materialize it into a table and count that instead.

create table all_views_mat as select * from all_views;

select count(*) from all_views_mat;
6
  • Thank you @Andrew. Just for the record, the Oracle 12c ODBC driver (at least on Windows, x64) seems to use this view when a program asks the list of schemas (with the SQLTables() ODBC API); and it takes a very long time. Maybe it's better with other versions of the ODBC driver. I will try.
    – ggo
    Oct 22 at 13:31
  • The Oracle ODBC Driver 19c seem to s**k as much as the 12c when it has to retrieve the list of schemas from the database with SQLTables(). I guess that both must use the same query (did not investigate). Fortunately there are other ways to retrieve this list.
    – ggo
    Oct 23 at 14:17
  • Just for fun: retrieving the list of schema with the Oracle ODBC Driver took almost 40 minutes! (loool). I believe that Oracle really should do something about that. Well, maybe I'm the only one retrieving the list of schema in the world from an Oracle database, so no one has ever reported this problem.
    – ggo
    Oct 23 at 15:03
  • Rather than running SQLTables(), use the SQL statement select username from all_users. That said, this sort of query is the sort of thing a developer would be running manually when they are trying to figure out the data model, using ODBC for this is not typical, you would use something like 'SQL Developer' or 'toad' (or a command line alternative). I tried to replicate what you were doing but it seems SQLTables isn't implemented easily by the .net ODBC class (I'm using powershell) so I'd have to write some "proper" code to run it - I would rather just fire up SQL Developer etc. Oct 23 at 19:21
  • 1
    If you want to raise the performance issue of SQLTables() to Oracle then you will need to log it with them directly. Either as a support case (support.oracle.com) which requires a support contract, or maybe unofficially through their community pages (maybe community.oracle.com/tech/apps-infra/categories/… ). Oct 23 at 19:24
2

Running the same query on an Oracle instance at my company which is 1 TB in size, executes in less than a second.

Executing as SYS AS SYSDBA

set pages 50
set lines 230
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT CURRENT_DATE FROM DUAL;
SELECT COUNT(*) FROM SYS.ALL_VIEWS;
SELECT CURRENT_DATE FROM DUAL;

Results in:

Session altered.

CURRENT_DATE         
---------------------
2021-11-05 15:00:26  
1 row selected.

  COUNT(*)
----------
      6785
1 row selected.

CURRENT_DATE         
---------------------
2021-11-05 15:00:26  
1 row selected.

As pointed out in other answers, it can be related to the complexity of the view you are querying, but I wouldn't bet all my money on that.

Other Things to Consider

The query can be related to the permissions that you have. Have you tried running the query as SYS as SYSDBA?

Then having an Oracle database instance with only 5 GB and a database that is 10 GB in size, may impact performance. Think: Data not yet in cache.

Another issue has to do with the memory configuration of your Oracle instance. Are you running Automatic Memory Management (AMM) where you configure the MEMORY_MAX_TARGETand MEMORY_TARGET parameters or Automatic Shared Memory Management (ASMM) where you configure the sga_target and sga_max_size parameters? Have you set the values to something higher than 1 GB so that the Oracle database instance can benefit from having the ability to cache some of the database objects (data) to RAM?

What happens if you run the query a second time? Do you have the same bad performance? Think: Database (data) caching.

If you local system is running other virtual machines, then turn them off and try it again. Does it run faster?

Are you running any other memory or CPU intensive applications on the same desktop/server? Turn them off.

Anti-Virus software? Turn if off or follow the recommendation from Oracle to exclude certain files / directories from being scanned.

If you follow up with a couple of these recommendations, then you should observer a speed up in performance when querying the simple view.

Oh, by the way, my Oracle instance is 1 TB in size and has the SGA_TARGET set to 84 GB. Memory is essential for database performance.

Running Query as SYSTEM

Session altered.

CURRENT_DATE         
---------------------
2021-11-10 12:46:24  
1 row selected.

  COUNT(*)
----------
      6785
1 row selected.

CURRENT_DATE         
---------------------
2021-11-10 12:46:25  
1 row selected.

Running Query as Non-Privileged User

I create an unprivileged user named UNPRIV_USER with absolutely no privileges except for GRANT CONNECT ... and GRANT CREATE SESSION ... and then re-ran the same statement against the huge 1TB Oracle database instance and here are the results:

Session altered.

CURRENT_DATE         
---------------------
2021-11-10 12:56:40  
1 row selected.

  COUNT(*)
----------
      1729
1 row selected.

CURRENT_DATE         
---------------------
2021-11-10 12:56:40  
1 row selected.

So while the unprivileged user doesn't have access to certain VIEWS, this doesn't have an impact on the duration of the query executing.

Conclusion

The permissions / privileges can probably have an affect on the execution time of the simple query, but in my environment it doesn't seem to have that much of an impact. (+/- 1 second)

I'd start looking at your virtualization and or instance-specific configuration settings.

E.g. my instance is running on dedicated hardware (16 cores @ 2.8 GHz, 768 GB RAM, 84 GB SGA for the mentioned instance).

Desktop OS vs. Server OS

Desktop OS and server OS prioritise things differently. A desktop OS will prioritise the foreground application, where a server OS will be normally configured to prioritise the background application (servcies).

On a Windows system follow these quick steps to configure your system to prioritise background applications:

To increase performance for background service like SQL Server / Oracle host instances, follow these steps:

  • Click Start, click Control Panel, and then click System.
    - Alternatively: Hit the Windows Key and type sysdm.cpl and then ENTER. This should bring up the System Control Panel.
  • Click the Advanced tab, and then click Settings under Performance.
  • Click the Advanced tab, click Background services, and then click OK twice.

This should result in better performance for the Oracle service on a Windows OS.

8
  • using SYSTIMESTAMP, my 12c DB has 7122 views. Query took 0.031 seconds. My 19c DB has 12,461 views. Query took 0.156. 19C DB has less RAM by more than half. But I didn't try running it through ODBC, to see how much that added to the time.
    – CaM
    Nov 5 at 19:33
  • @John K. N.: Thank you for the suggestions. and you were right: running this query as SYS/SYSDBA is very fast (less than one second). As you probably guessed I'm not an Oracle expert, so can you explain why it is so slow when I run the same query as SYSTEM please? Is it related to permissions? SYSTEM must have a lot permissions I suppose, so what will happen with "regular" users. Thanks again.
    – ggo
    Nov 6 at 16:56
  • I'll get back to you with results from my environment. I'll run the query with SYSTEM and a generic user to find out how the accounts vary. It does have to do with the complexity of the view as already pointed out, but the query duration might also be impacted by the configuration of your Oracle database instance.
    – John K. N.
    Nov 9 at 6:18
  • @JohnK.N. I'm eager to know your results :)
    – ggo
    Nov 9 at 15:22
  • 1
    In my case the OS is windows server (2019), and it is already configured to "optimize background applications".
    – ggo
    Nov 10 at 13:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.