Think simple and spare yourself a facepalm

On one of my training sessions, I was presented with a simple query which looked like this:

SELECT company,
            COUNT(*)
FROM invoices
WHERE can_access( company ) = 1
GROUP BY company;

Execution plan with statistics was a follows:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation               | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT        |                   |      2 |        |       |   264 (100)|          |       |       |   2587 |00:00:02.84 |    2402 |       |       |          | |   1 |  VIEW                   |                   |      2 |   1733 | 36393 |   264  (24)| 00:00:04 |       |       |   2587 |00:00:02.84 |    2402 |       |       |          | |   2 |   HASH GROUP BY         |                   |      2 |   1733 | 15597 |   264  (24)| 00:00:04 |       |       |   2587 |00:00:02.84 |    2402 |    17M|  3187K|     1/0/0| |   3 |    PARTITION RANGE ALL  |                   |      2 |   2900 | 26100 |   263  (24)| 00:00:04 |     1 |    41 |    586K|00:00:03.00 |    2402 |       |       |          | |*  4 |     INDEX FAST FULL SCAN| INVOICES          |     82 |   2900 | 26100 |   263  (24)| 00:00:04 |     1 |    41 |    586K|00:00:02.74 |    2402 |       |       |          | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$2 / from$_subquery$_001@SEL$1    2 - SEL$2    4 - SEL$2 / INVOICES@SEL$2 Predicate Information (identified by operation id): ---------------------------------------------------    4 - filter("CAN_ACESS"(TO_NUMBER("COMPANY"))=1)

Function can_access was containing a single SQL and some simple PL/SQL code. Purpose of this function was to filter records based on privileges of current user.

First, I told them, that I don’t like the usage of PL/SQL function at all in this kind of situation. It’s a CPU burner on SQL and PL/SQL context switches in first place, and second, it’s hiding some important information from Oracle optimizer (selectivity for example).

Response was that its legacy stuff and that they have to deal with it somehow … ouch. The problem was, that they knew that function could be ran on grouped result set (limiting calls by great deal), but they was unable to force Oracle to do so.

So I tried usual shenanigans with parentheses, no_merge, no_query_transformation and great deal of begging. But it was to no use. Execution plan looked always the same. In the end I used the following trick to do the job:

SELECT * FROM
            (SELECT /*+ no_merge */
                        company,
                        COUNT(*)
            FROM invoices
            GROUP BY company)
WHERE (SELECT can_access( company ) FROM DUAL) = 1;

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT         |                   |      1 |        |       |  2758 (100)|          |       |       |   2537 |00:00:00.17 |    1201 |       |       |          | |*  1 |  FILTER                  |                   |      1 |        |       |            |          |       |       |   2537 |00:00:00.17 |    1201 |       |       |          | |   2 |   VIEW                   |                   |      1 |   2536 | 55792 |   221   (9)| 00:00:04 |       |       |   2537 |00:00:00.15 |    1201 |       |       |          | |   3 |    HASH GROUP BY         |                   |      1 |   2536 | 22824 |   221   (9)| 00:00:04 |       |       |   2537 |00:00:00.15 |    1201 |    17M|  3187K|     1/0/0| |   4 |     PARTITION RANGE ALL  |                   |      1 |    290K|  2549K|   205   (2)| 00:00:03 |     1 |    41 |    293K|00:00:00.21 |    1201 |       |       |          | |   5 |      INDEX FAST FULL SCAN| INVOICES          |     41 |    290K|  2549K|   205   (2)| 00:00:03 |     1 |    41 |    293K|00:00:00.10 |    1201 |       |       |          | |   6 |   FAST DUAL              |                   |   2537 |      1 |       |     2   (0)| 00:00:01 |       |       |   2537 |00:00:00.01 |       0 |       |       |          | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$2 / from$_subquery$_001@SEL$1    3 - SEL$2    5 - SEL$2 / INVOICES@SEL$2    6 - SEL$3 / DUAL@SEL$3 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(=1)

Half processed buffers, nice time, great! After that I left home. Next day I’m telling my success story to a colleague of mine and he is like

“... Umm ... why don’t you use HAVING?”

 So final solution should have looked like this:

SELECT company,
            COUNT(*)
FROM invoices
GROUP BY company
HAVING can_access(company) = 1;

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation               | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT        |                   |      1 |        |       |   221 (100)|          |       |       |   2537 |00:00:00.15 |    1201 |       |       |          | |*  1 |  FILTER                 |                   |      1 |        |       |            |          |       |       |   2537 |00:00:00.15 |    1201 |       |       |          | |   2 |   HASH GROUP BY         |                   |      1 |     26 |   234 |   221   (9)| 00:00:04 |       |       |   2537 |00:00:00.14 |    1201 |    17M|  3187K|     1/0/0| |   3 |    PARTITION RANGE ALL  |                   |      1 |    290K|  2549K|   205   (2)| 00:00:03 |     1 |    41 |    293K|00:00:00.21 |    1201 |       |       |          | |   4 |     INDEX FAST FULL SCAN| INVOICES          |     41 |    290K|  2549K|   205   (2)| 00:00:03 |     1 |    41 |    293K|00:00:00.10 |    1201 |       |       |          | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    4 - SEL$1 / INVOICES@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("CAN_ACCESS"(TO_NUMBER("COMPANY"))=1)

So remember folks … don’t try to solve stuff, when you are tired after a long day and think simple. You will spare yourself some facepalm.

ORA-23313: object group is not mastered at

DISCLAIMER:

Solution presented below is a last resort solution. Official Oracle stand is that you should NEVER ever make any change in data dictionary. Always double check and make notes and backups. Use on your own risk.

One of our customers made a copy of production database (11.2.0.4) to test environment. Administrator also made all necessary changes to environment and database. When I wanted to drop our master replication group, I’ve got following error:

begin DBMS_REPCAT.DROP_MASTER_REPGROUP('MYREPGROUP'); commit; end; Error report: ORA-23313: object group "PUBLIC"."MYREPGROUP" is not mastered at MYDB.MYNEWDOMAIN.COM ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105 ORA-06512: at "SYS.DBMS_REPCAT_UTL4", line 2870 ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2662 ORA-06512: at "SYS.DBMS_REPCAT", line 635 ORA-06512: at line 2 23313. 00000 -  "object group \"%s\".\"%s\" is not mastered at %s"

I queried dba_db_links and everything looked fine. So I checked database domain and global name:

SELECT * FROM GLOBAL_NAME; GLOBAL_NAME           ---------------------- MYDB.MYNEWDOMAIN.COM show parameter domain; NAME        TYPE        VALUE           ----------- ----------- ----------------- db_domain   string      mynewdomain.com

Changes he made also (unfortunately) included change of database domain and global name.

Usual solution would be to revert changes by:

alter system set db_domain='mydomain.com' scope=spfile;
alter database rename global_name to MYDB.mydomain.com;

Then bounce database, drop master replication group, change database domain and global name back and bounce database.

Unfortunately I was not able to implement this solution since database bounce was not possible because of running tests.

In the end I had to do following change in data dictionary to be able to drop master replication group:

alter table system.REPCAT$_REPSCHEMA disable constraint REPCAT$_REPSCHEMA_DEST;
update system.REPCAT$_REPSCHEMA set dblink='MYDB.MYNEWDOMAIN.COM' where sname='MYREPGROUP';
update SYSTEM.DEF$_DESTINATION set dblink='MYDB.MYNEWDOMAIN.COM' where dblink='MYDB.MYDOMAIN.COM';
alter table system.REPCAT$_REPSCHEMA enable constraint REPCAT$_REPSCHEMA_DEST;

I encourage you to study impacted tables very carefully and make backup copy before making any change. Triple check any change you are about to make. Don’t forget about remote sites which you should try to handle before dropping master site. Principle is the same, but usually change in database link is sufficient.

Presented change was done with knowledge that we WILL drop whole replication group and recreate it from scratch. We were not concerned with any delayed transactions.

Locks and Locking

If you missed Oracle Locks and Locking Virtual Session, you can still check out the presentation:



I'll probably stick another free virtual session to start of June. I would like to hear from you what you would like to see. Looking forward to your suggestions in comments bellow ...