Donghua's Blog - DBAGlobe

Sunday, June 11, 2023

Use Oracle supported methods to clean up old/inactive patches and free up space in Oracle Home

Check the patch store size after Apr2023 (19.19) RU patch
[oracle@ol ~]$ du -sh $ORACLE_HOME/.*_storage
12K	/u01/db19c/.opatchauto_storage
7.2G	/u01/db19c/.patch_storage
Check the patch version
[oracle@ol ~]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.37

OPatch succeeded.
List Inactive Patches
[oracle@ol ~]$ $ORACLE_HOME/OPatch/opatch ListOrderedInactivePatches
Oracle Interim Patch Installer version 12.2.0.1.37
Copyright (c) 2023, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/db19c
Central Inventory : /u01/app/oraInventory
   from           : /u01/db19c/oraInst.loc
OPatch version    : 12.2.0.1.37
OUI version       : 12.2.0.7.0
Log file location : /u01/db19c/cfgtoollogs/opatch/opatch2023-06-11_21-33-42PM_1.log

Invoking utility "listorderedinactivepatches"
List Inactive patches option provided

The oracle home has the following inactive patch(es) and their respective overlay patches:
-Inactive RU/CPU 34419443, installed on: Sun Nov 06 11:28:33 SGT 2022, with no overlays
-Inactive RU/CPU 34765931, installed on: Sun Jan 22 12:30:46 SGT 2023, with no overlays
-Inactive RU/CPU 34411846, installed on: Sun Nov 06 11:36:34 SGT 2022, with no overlays
-Inactive RU/CPU 34786990, installed on: Sun Jan 22 12:43:31 SGT 2023, with no overlays
Total: 4 inactive RU/CPU patch(es) and 0 inactive overlay patch(es).

OPatch succeeded.
Delete Inactive Patches
[oracle@ol ~]$ $ORACLE_HOME/OPatch/opatch DeleteInactivePatches
Oracle Interim Patch Installer version 12.2.0.1.37
Copyright (c) 2023, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/db19c
Central Inventory : /u01/app/oraInventory
   from           : /u01/db19c/oraInst.loc
OPatch version    : 12.2.0.1.37
OUI version       : 12.2.0.7.0
Log file location : /u01/db19c/cfgtoollogs/opatch/opatch2023-06-11_21-35-24PM_1.log

Invoking utility "deleteinactivepatches"
Inactive Patches Cleanup option provided
Delete Inactive Patches .......

Opatch will delete the following inactive patch(es) and their overlays:
-Inactive RU/CPU 34419443, installed on: Sun Nov 06 11:28:33 SGT 2022, with no overlays
-Inactive RU/CPU 34765931, installed on: Sun Jan 22 12:30:46 SGT 2023, with no overlays
-Inactive RU/CPU 34411846, installed on: Sun Nov 06 11:36:34 SGT 2022, with no overlays
Total: 3 inactive RU/CPU patch(es) and 0 inactive overlay patch(es).

OPatch will keep the following inactive patch(es) and their overlays:
-Inactive RU/CPU 34786990, installed on: Sun Jan 22 12:43:31 SGT 2023, with no overlays
Total: 1 inactive RU/CPU patch(es) and 0 inactive overlay patch(es).

Do you want to proceed? [y|n]
y
User Responded with: Y
y
Deleted patch: 34419443
Deleted patch: 34765931
Deleted patch: 34411846

OPatch succeeded.
Check the patch store size again, size reduced from 7.2GB to 2.8GB.
[oracle@ol 19.19]$ du -sh $ORACLE_HOME/.*_storage
12K	/u01/db19c/.opatchauto_storage
2.8G	/u01/db19c/.patch_storage
List installed patches
[oracle@ol 19.19]$ $ORACLE_HOME/OPatch/opatch lspatches
35050341;OJVM RELEASE UPDATE: 19.19.0.0.230418 (35050341)
35042068;Database Release Update : 19.19.0.0.230418 (35042068)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

OPatch succeeded.
Other advanced util commands comes with OPatch
[oracle@ol 35042068]$ $ORACLE_HOME/OPatch/opatch util -help
Oracle Interim Patch Installer version 12.2.0.1.37
Copyright (c) 2023, Oracle Corporation.  All rights reserved.


DESCRIPTION
    This operation invokes the chosen utilities on an ORACLE_HOME.
    This command doesnt support System Patch.

SYNTAX
opatch util [ -help ] [ COMMAND ]

Use opatch util [ COMMAND ] -help to get help on a particular command.

COMMANDS
        ApplySql
              Apply sql, sql procedure related actions for patches, which
              are already applied using Apply or NApply operation.

        CheckMinimumOpatchVersion
              Check if patch is compatible with the given user input version.

        CheckComponents
	      Check if the given patch is suitable for the Oracle Homes
              registered in the central inventory by components check

	CheckMemory
              Return the required memory for the specified online patch(es)

        Cleanup
              Remove the backup for restore area of the given patch
              or for all the patches.

        CopyListedDirs
             Recursively copy all directories listed in
             ORACLE_HOME/.patch_storage/<ID>/rac/copy_dirs.txt
             to remote nodes.

        CopyListedDirsTest
             Recursively copy a single directory to remote nodes.

        CopyListedFiles
              Copy all files listed in
              ORACLE_HOME/.patch_storage/<ID>/rac/copy_files.txt
              to remote nodes.

        CopyListedFilesTest
             Copy a single file to remote nodes.

        DeleteInactivePatches
	     Delete inactive patches from ORACLE_HOME

        DisableOnlinePatch
             Disable and remove the specified online patch(es)
             on the given database instances.

        EnableOnlinePatch
             Install and enable the specified online patch(es)
             on the given database instances.

        GetPatchLevel
             Return the patching level on Local Grid Home.

        InstallOCM
             Install and configure OCM.

	ListOrderedInactivePatches
	     List inactive patches and its overlays
	     present in ORACLE_HOME

        LoadXML
             Prompt for path/name of the XML file, then check if the
             XML is correct.

        Obfuscate
             Obfuscate the backed up files under ORACLE_HOME/.patch_storage.

        RemoveListedDirs
             Recursively remote directories listed in
             ORACLE_HOME/.patch_storage/<ID>/rac/remove_dirs.txt
             on remote nodes.

        RemoveListedDirsTest
             Recursively remove a directory on remote nodes.

        RemoveListedFiles
             Remove files listed in
             ORACLE_HOME/.patch_storage/<ID>/rac/remove_files.txt
             on remote nodes.

        RemoveListedFilesTest
             Remove a single file on remote nodes.

        RestoreOH
             Restore the OracleHome to the previous state.

        RollbackSql
              Rollback sql, sql procedure related actions for patches, which
              are already rolled back using Rollback or NRollback operation.

        RunAnyCommand
             Run any command on remote nodes.

        RunLocalMake
             Invoke re-link on the local node. Commands are stored in
             ORACLE_HOME/.patch_storage/<ID>/make.txt.

        RunRemoteMake
             Invoke re-link on remote nodes. Commands are stored in
             ORACLE_HOME/.patch_storage/<ID>/rac/make_cmds.txt.

        UpdateRemoteNodes
             Propagate/Remove files/directories to/on remote nodes
             using files under ORACLE_HOME/.patch_storage/<ID>/rac/*.

        UpdateOPatchVersion
             Update the version of OPatch in the inventory of
             Oracle Home.

        Verify
             Using the defined ORACLE_HOME and given patch location via
             -ph, the program will check to make sure the patch was
             applied to the ORACLE_HOME.
             Example: opatch util verify -ph /tmp/patchLoc

OPatch succeeded.

Sunday, April 9, 2023

Grants for new role DB_DEVELOPER_ROLE in Oracle 23c

Granted ROLE Privileges

SQL> set tab off
SQL> column grantee for a17

SQL> select * from dba_role_privs where grantee='DB_DEVELOPER_ROLE';

GRANTEE           GRANTED_ROLE      ADM DEL DEF COM INH
----------------- ----------------- --- --- --- --- ---
DB_DEVELOPER_ROLE SODA_APP          NO  NO  YES YES YES
DB_DEVELOPER_ROLE CTXAPP            NO  NO  YES YES YES

Granted SYSTEM Privileges

SQL> select * from dba_sys_privs where grantee='DB_DEVELOPER_ROLE';

GRANTEE           PRIVILEGE                      ADM COM INH
----------------- ------------------------------ --- --- ---
DB_DEVELOPER_ROLE CREATE DOMAIN                  NO  YES YES
DB_DEVELOPER_ROLE CREATE MLE                     NO  YES YES
DB_DEVELOPER_ROLE CREATE ANALYTIC VIEW           NO  YES YES
DB_DEVELOPER_ROLE CREATE HIERARCHY               NO  YES YES
DB_DEVELOPER_ROLE CREATE ATTRIBUTE DIMENSION     NO  YES YES
DB_DEVELOPER_ROLE EXECUTE DYNAMIC MLE            NO  YES YES
DB_DEVELOPER_ROLE CREATE CUBE BUILD PROCESS      NO  YES YES
DB_DEVELOPER_ROLE CREATE CUBE                    NO  YES YES
DB_DEVELOPER_ROLE CREATE CUBE DIMENSION          NO  YES YES
DB_DEVELOPER_ROLE CREATE MINING MODEL            NO  YES YES
DB_DEVELOPER_ROLE CREATE JOB                     NO  YES YES
DB_DEVELOPER_ROLE DEBUG CONNECT SESSION          NO  YES YES
DB_DEVELOPER_ROLE ON COMMIT REFRESH              NO  YES YES
DB_DEVELOPER_ROLE CREATE DIMENSION               NO  YES YES
DB_DEVELOPER_ROLE CREATE TYPE                    NO  YES YES
DB_DEVELOPER_ROLE CREATE MATERIALIZED VIEW       NO  YES YES
DB_DEVELOPER_ROLE CREATE TRIGGER                 NO  YES YES
DB_DEVELOPER_ROLE CREATE PROCEDURE               NO  YES YES
DB_DEVELOPER_ROLE FORCE TRANSACTION              NO  YES YES
DB_DEVELOPER_ROLE CREATE SEQUENCE                NO  YES YES
DB_DEVELOPER_ROLE CREATE VIEW                    NO  YES YES
DB_DEVELOPER_ROLE CREATE SYNONYM                 NO  YES YES
DB_DEVELOPER_ROLE CREATE TABLE                   NO  YES YES
DB_DEVELOPER_ROLE CREATE SESSION                 NO  YES YES

24 rows selected.

Granted Object Privileges

SQL> select * from dba_tab_privs where grantee='DB_DEVELOPER_ROLE';

GRANTEE           OWNER      TABLE_NAME                GRANTOR    PRIVILEGE  GRA HIE COM TYPE         INH
----------------- ---------- ------------------------- ---------- ---------- --- --- --- ------------ ---
DB_DEVELOPER_ROLE SYS        JAVASCRIPT                SYS        EXECUTE    NO  NO  NO  MLE LANGUAGE NO
DB_DEVELOPER_ROLE SYS        V_$STATNAME               SYS        READ       NO  NO  YES VIEW         YES
DB_DEVELOPER_ROLE SYS        V_$PARAMETER              SYS        READ       NO  NO  YES VIEW         YES
DB_DEVELOPER_ROLE SYS        DBA_PENDING_TRANSACTIONS  SYS        SELECT     NO  NO  YES VIEW         YES

Script to extract ROLE DDL and dbms_metadata.get_granted_ddl bug for Oracle 23c

Script to extract ROLE DDL

set long 8000 
set longchunksize 2000

with role_name as 
  (select 'DBA' as name from dual)
select dbms_metadata.get_ddl('ROLE', r.role) AS ddl
from   dba_roles r, role_name
where  r.role = role_name.name
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from   dba_role_privs rp, role_name
where  rp.grantee = role_name.name
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from   dba_sys_privs sp, role_name
where  sp.grantee = role_name.name
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from   dba_tab_privs tp, role_name
where  tp.grantee = role_name.name
and    rownum = 1
/

Bug in Oracle 23c (23.2)

SQL> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0


SQL> select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','CONNECT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','CONNECT')
--------------------------------------------------------------------------------

  BEGIN NULL; END
  BEGIN NULL; END

There is no such funny bug in Oracle 21c (21.9)

SQL> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.9.0.0.0


SQL> select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','CONNECT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','CONNECT')
--------------------------------------------------------------------------------

  GRANT CREATE SESSION TO "CONNECT"
  GRANT SET CONTAINER TO "CONNECT"

DROP and CREATE in Oracle 23c with IF EXISTS and IF NOT EXISTS

DROP and CREATE in Oracle 21c

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.9.0.0.0

SQL> drop table t;
drop table t
           *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> create table t (id int);

Table created.

SQL> create table t (id int);
create table t (id int)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object

DROP and CREATE in Oracle 23c with `IF EXISTS` and `IF NOT EXISTS`

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0


SQL> drop table if exists t;

Table dropped.

SQL> create table t (id int);

Table created.

SQL> create table if not exists t (id int);

Table created.

`CREATE OR REPLACE` can't coexist with `NOT EXISTS`

SQL> create synonym if not exists s for t;

Synonym created.

SQL> create or replace synonym s for t;

Synonym created.

SQL> create or replace if not exists s for t;
create or replace if not exists s for t
                  *
ERROR at line 1:
ORA-00922: missing or invalid option
SQL>

Saturday, April 8, 2023

Set Search Path at database level for PostgreSQL

There are quite a lot examples on how to set search_path at user level, here is one at the database level.

Check the setting before change

dev=> select * from pg_db_role_setting where setdatabase=(select oid from pg_database where datname='dev');
 setdatabase | setrole | setconfig
-------------+---------+-----------
(0 rows)

dev=> show search_path;
   search_path
-----------------
 "$user", public
(1 row)

Change the default search path at database level

The setting can be overwritten by user level setting and session level setting.

dev=> alter database dev set search_path="$user",appuser,public;
ALTER DATABASE

dev=> select * from pg_db_role_setting where setdatabase=(select oid from pg_database where datname='dev');
 setdatabase | setrole |                 setconfig
-------------+---------+--------------------------------------------
       16400 |       0 | {"search_path=\"$user\", appuser, public"}
(1 row)

-- the setting doesn't take effect without reconnection
dev=> show search_path;
   search_path
-----------------
 "$user", public
(1 row)

Reconnect

dev=> \c dev

psql (14.3, server 14.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
You are now connected to database "dev" as user "postgres".
dev=> show search_path;
       search_path
--------------------------
 "$user", appuser, public
(1 row)

Tuesday, April 4, 2023

Install Oracle 23c Free Developer Edition on Oracle Linux 8

Download Oracle Database 23c Free Developer Release here. OTN login is no longer required.

wget https://yum.oracle.com/repo/OracleLinux/OL8/developer/x86_64/getPackage/oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm
wget https://download.oracle.com/otn-pub/otn_software/db-free/oracle-database-free-23c-1.0-1.el8.x86_64.rpm

Perform the installation on Oracle Linux 8 (steps on RHEL8 and CentOS 8 shall be similar)

sudo yum install ./oracle-database-free-23c-1.0-1.el8.x86_64.rpm ./oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm

Configure Oracle database.

[oracle@ol stage]$ sudo /etc/init.d/oracle-free-23c configure
Specify a password to be used for database accounts. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. Note that the same password will be used for SYS, SYSTEM and PDBADMIN accounts:
Confirm the password:
Configuring Oracle Listener.
Listener configuration succeeded.
Configuring Oracle Database FREE.
Enter SYS user password:
************
Enter SYSTEM user password:
*******
Enter PDBADMIN User Password:
*******
Prepare for db operation
7% complete
Copying database files
29% complete
Creating and starting Oracle instance
30% complete
33% complete
36% complete
39% complete
43% complete
Completing Database Creation
47% complete
49% complete
50% complete
Creating Pluggable Databases
54% complete
71% complete
Executing Post Configuration Actions
93% complete
Running Custom Scripts
100% complete
Database creation complete. For details check the logfiles at:
 /opt/oracle/cfgtoollogs/dbca/FREE.
Database Information:
Global Database Name:FREE
System Identifier(SID):FREE
Look at the log file "/opt/oracle/cfgtoollogs/dbca/FREE/FREE.log" for further details.

Connect to Oracle Database using one of the connect strings:
     Pluggable database: ol.lab/FREEPDB1
     Multitenant container database: ol.lab

By default, Oracle installation to /opt/oracle/product/23c/dbhomeFree and data file created in /opt/oracle/oradata/. To change the data file location, modify this configure file /etc/sysconfig/oracle-free-23c.conf prior to run /etc/init.d/oracle-free-23c configure.

#This is a configuration file to setup the Oracle Database.
#It is used when running '/etc/init.d/oracle-free-23c configure'.

# LISTENER PORT used Database listener, Leave empty for automatic port assignment
LISTENER_PORT=

# Character set of the database
CHARSET=AL32UTF8

# Database file directory
# If not specified, database files are stored under Oracle base/oradata
DBFILE_DEST=

# DB Domain name
DB_DOMAIN=

# SKIP Validations, memory, space
SKIP_VALIDATIONS=false

Connect to Oracle database

[oracle@ol stage]$ . oraenv
ORACLE_SID = [orcl21c] ? FREE
The Oracle base has been changed from /u01/app/oracle to /opt/oracle

[oracle@ol stage]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue Apr 4 16:41:46 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0


SQL> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

Sunday, March 19, 2023

Compare [NOT] IN/EXISTS query optimisation between Oracle and PostgreSQL

Overview

We will compare "IN", "EXISTS", "JOIN", "NOT IN", "NOT EXISTS" and "OUTER JOIN" between Oracle 21c and PostgresQL 15.

The execution summary is as below:

QueryID	Query	Oracle	PostgreSQL
Query 1: "IN"	select sum(v) from c where pid in (select pid from p where v>0.95);	00:00:00.01	76.142 ms
Query 2: "EXISTS"	select sum(v) from c where exists (select pid from p where c.pid = p.pid and v>0.95);	Same plan as query 1	Same plan as query 1
Query 3: "JOIN"	select sum(c.v) from c join p on c.pid=p.pid and p.v>0.95;	Same plan as query 1	Same plan as query 1
Query 4: "NOT IN"	select sum(v) from c where pid not in (select pid from p where v<=0.95);	00:00:00.01	112.662 ms
Query 5: "NOT EXISTS"	select sum(v) from c where not exists (select pid from p where c.pid = p.pid and v<=0.95);	Same plan as query 4	103.857 ms
Query 6: "OUTER JOIN"	select sum(c.v) from c left outer join (select * from p where p.v<=0.95 ) p2 on c.pid=p2.pid where p2.pid is null;	00:00:00.02	Same plan as query 5

Setup before testing

Oracle

-- setup demo table

drop table c;
drop table p;

create table p(pid int primary key, v number(3,2), extra char(100));
create table c(cid int primary key, pid int, v number(3,2), extra char(100), 
                constraint c_p_fk foreign key (pid) references p(pid));

insert into p select level, round(dbms_random.value,2), 'x' 
                from dual connect by level <= 1000;
insert into c select level, floor(dbms_random.value(1,1000)),round(dbms_random.value,2), 'x' 
                from dual connect by level <= 100000;
commit;

create index idx_p_v on p(v);
create index idx_c_pid on c(pid);

exec dbms_stats.gather_table_stats('','P');
exec dbms_stats.gather_table_stats('','C');

-- formating related
set timing on;
set autotrace on explain
set linesize 120
set tab off

PostgreSQL

-- setup demo table

drop table c;
drop table p;

create table p(pid int primary key, v numeric(3,2), extra char(100));
create table c(cid int primary key, pid int, v numeric(3,2), extra char(100), 
                constraint c_p_fk foreign key (pid) references p(pid));

insert into p select level, round(random()::numeric,2), 'x' 
                from generate_series(1,1000) level;
insert into c select level, floor(random()::numeric*1000)+1,round(random()::numeric,2), 'x' 
                from generate_series(1,1000000) level;

create index idx_p_v on p(v);
create index idx_c_pid on c(pid);

vacuum analyze p;
vacuum analyze c;

-- formating related
\timing

Database Version

SQL> select banner_full from v$version;

BANNER_FULL
----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.9.0.0.0

mytest=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-16), 64-bit
(1 row)

Query 1: "IN"

select sum(v) from c
  where pid in (select pid from p where v>0.95);

Oracle

SQL> select sum(v) from c
  2     where pid in (select pid from p where v>0.95);

    SUM(V)
----------
    2484.1

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 423065292

---------------------------------------------------------------------------------------------
| Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                  |     1 |    14 |   484   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE          |                  |     1 |    14 |            |          |
|*  2 |   HASH JOIN              |                  |  5005 | 70070 |   484   (1)| 00:00:01 |
|*  3 |    VIEW                  | index$_join$_002 |    50 |   350 |     5   (0)| 00:00:01 |
|*  4 |     HASH JOIN            |                  |       |       |            |          |
|*  5 |      INDEX RANGE SCAN    | IDX_P_V          |    50 |   350 |     2   (0)| 00:00:01 |
|   6 |      INDEX FAST FULL SCAN| SYS_C008978      |    50 |   350 |     4   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL     | C                |   100K|   683K|   479   (1)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("PID"="PID")
   3 - filter("V">0.95)
   4 - access(ROWID=ROWID)
   5 - access("V">0.95)

Note
-----
   - this is an adaptive plan

PostgreSQL

mytest=# select sum(v) from c
mytest-#   where pid in (select pid from p where v>0.95);
   sum
----------
 24233.38
(1 row)

Time: 76.142 ms


mytest=# explain
mytest-# select sum(v) from c
mytest-#    where pid in (select pid from p where v>0.95);
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=24522.20..24522.21 rows=1 width=32)
   ->  Gather  (cost=24521.98..24522.19 rows=2 width=32)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=23521.98..23521.99 rows=1 width=32)
               ->  Hash Join  (cost=23.88..23470.94 rows=20417 width=4)
                     Hash Cond: (c.pid = p.pid)
                     ->  Parallel Seq Scan on c  (cost=0.00..22348.67 rows=416667 width=8)
                     ->  Hash  (cost=23.27..23.27 rows=49 width=4)
                           ->  Bitmap Heap Scan on p  (cost=4.65..23.27 rows=49 width=4)
                                 Recheck Cond: (v > 0.95)
                                 ->  Bitmap Index Scan on idx_p_v  (cost=0.00..4.64 rows=49 width=0)
                                       Index Cond: (v > 0.95)
(12 rows)

Time: 0.715 ms

Query 2: "EXISTS"

select sum(v) from c 
  where exists (select pid from p where c.pid = p.pid and v>0.95);

Oracle

SQL> select sum(v) from c
  2  where exists (select pid from p where c.pid = p.pid and v>0.95);

    SUM(V)
----------
    2484.1

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 423065292

---------------------------------------------------------------------------------------------
| Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                  |     1 |    14 |   484   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE          |                  |     1 |    14 |            |          |
|*  2 |   HASH JOIN              |                  |  5005 | 70070 |   484   (1)| 00:00:01 |
|*  3 |    VIEW                  | index$_join$_002 |    50 |   350 |     5   (0)| 00:00:01 |
|*  4 |     HASH JOIN            |                  |       |       |            |          |
|*  5 |      INDEX RANGE SCAN    | IDX_P_V          |    50 |   350 |     2   (0)| 00:00:01 |
|   6 |      INDEX FAST FULL SCAN| SYS_C008978      |    50 |   350 |     4   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL     | C                |   100K|   683K|   479   (1)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C"."PID"="P"."PID")
   3 - filter("V">0.95)
   4 - access(ROWID=ROWID)
   5 - access("V">0.95)

Note
-----
   - this is an adaptive plan

PostgreSQL

mytest=# select sum(v) from c
mytest-#   where exists (select pid from p where c.pid = p.pid and v>0.95);
   sum
----------
 24233.38
(1 row)

Time: 76.109 ms

mytest=# explain
mytest-# select sum(v) from c
mytest-#   where exists (select pid from p where c.pid = p.pid and v>0.95);
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=24522.20..24522.21 rows=1 width=32)
   ->  Gather  (cost=24521.98..24522.19 rows=2 width=32)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=23521.98..23521.99 rows=1 width=32)
               ->  Hash Join  (cost=23.88..23470.94 rows=20417 width=4)
                     Hash Cond: (c.pid = p.pid)
                     ->  Parallel Seq Scan on c  (cost=0.00..22348.67 rows=416667 width=8)
                     ->  Hash  (cost=23.27..23.27 rows=49 width=4)
                           ->  Bitmap Heap Scan on p  (cost=4.65..23.27 rows=49 width=4)
                                 Recheck Cond: (v > 0.95)
                                 ->  Bitmap Index Scan on idx_p_v  (cost=0.00..4.64 rows=49 width=0)
                                       Index Cond: (v > 0.95)
(12 rows)

Time: 0.723 ms

Query 3: "JOIN"

select sum(c.v) from c join p on c.pid=p.pid and p.v>0.95;

Oracle

SQL> select sum(c.v) from c join p on c.pid=p.pid and p.v>0.95;

  SUM(C.V)
----------
    2484.1

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 423065292

---------------------------------------------------------------------------------------------
| Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                  |     1 |    14 |   484   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE          |                  |     1 |    14 |            |          |
|*  2 |   HASH JOIN              |                  |  5005 | 70070 |   484   (1)| 00:00:01 |
|*  3 |    VIEW                  | index$_join$_002 |    50 |   350 |     5   (0)| 00:00:01 |
|*  4 |     HASH JOIN            |                  |       |       |            |          |
|*  5 |      INDEX RANGE SCAN    | IDX_P_V          |    50 |   350 |     2   (0)| 00:00:01 |
|   6 |      INDEX FAST FULL SCAN| SYS_C008978      |    50 |   350 |     4   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL     | C                |   100K|   683K|   479   (1)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C"."PID"="P"."PID")
   3 - filter("P"."V">0.95)
   4 - access(ROWID=ROWID)
   5 - access("P"."V">0.95)

Note
-----
   - this is an adaptive plan

PostgreSQL

mytest=# select sum(c.v) from c join p on c.pid=p.pid and p.v>0.95;
   sum
----------
 24233.38
(1 row)

Time: 75.596 ms

mytest=# explain
mytest-# select sum(c.v) from c join p on c.pid=p.pid and p.v>0.95;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=24522.20..24522.21 rows=1 width=32)
   ->  Gather  (cost=24521.98..24522.19 rows=2 width=32)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=23521.98..23521.99 rows=1 width=32)
               ->  Hash Join  (cost=23.88..23470.94 rows=20417 width=4)
                     Hash Cond: (c.pid = p.pid)
                     ->  Parallel Seq Scan on c  (cost=0.00..22348.67 rows=416667 width=8)
                     ->  Hash  (cost=23.27..23.27 rows=49 width=4)
                           ->  Bitmap Heap Scan on p  (cost=4.65..23.27 rows=49 width=4)
                                 Recheck Cond: (v > 0.95)
                                 ->  Bitmap Index Scan on idx_p_v  (cost=0.00..4.64 rows=49 width=0)
                                       Index Cond: (v > 0.95)
(12 rows)

Time: 0.707 ms

Query 4: "NOT IN"

select sum(v) from c
  where pid not in (select pid from p where v<=0.95);

Oracle

SQL> select sum(v) from c
  2    where pid not in (select pid from p where v<=0.95);

    SUM(V)
----------
    2484.1

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 257737780

----------------------------------------------------------------------------------------------
| Id  | Operation                 | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                  |     1 |    14 |   485   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE           |                  |     1 |    14 |            |          |
|*  2 |   HASH JOIN RIGHT ANTI SNA|                  |  4905 | 68670 |   485   (1)| 00:00:01 |
|*  3 |    VIEW                   | index$_join$_002 |   950 |  6650 |     6   (0)| 00:00:01 |
|*  4 |     HASH JOIN             |                  |       |       |            |          |
|*  5 |      INDEX RANGE SCAN     | IDX_P_V          |   950 |  6650 |     3   (0)| 00:00:01 |
|   6 |      INDEX FAST FULL SCAN | SYS_C008978      |   950 |  6650 |     4   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL      | C                |   100K|   683K|   479   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("PID"="PID")
   3 - filter("V"<=0.95)
   4 - access(ROWID=ROWID)
   5 - access("V"<=0.95)

PostgreSQL

mytest=# select sum(v) from c
mytest-#   where pid not in (select pid from p where v<=0.95);
   sum
----------
 24233.38
(1 row)

mytest=# explain
mytest-# select sum(v) from c
mytest-#   where pid not in (select pid from p where v<=0.95);
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=24944.27..24944.28 rows=1 width=32)
   ->  Gather  (cost=24944.05..24944.26 rows=2 width=32)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=23944.05..23944.06 rows=1 width=32)
               ->  Parallel Seq Scan on c  (cost=32.88..23423.21 rows=208333 width=4)
                     Filter: (NOT (hashed SubPlan 1))
                     SubPlan 1
                       ->  Seq Scan on p  (cost=0.00..30.50 rows=951 width=4)
                             Filter: (v <= 0.95)
(9 rows)

Time: 0.493 ms

Query 5: "NOT EXISTS"

select sum(v) from c 
  where not exists (select pid from p where c.pid = p.pid and v<=0.95);

Oracle

SQL> select sum(v) from c
  2    where not exists (select pid from p where c.pid = p.pid and v<=0.95);

    SUM(V)
----------
    2484.1

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 3094349839

---------------------------------------------------------------------------------------------
| Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                  |     1 |    14 |   485   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE          |                  |     1 |    14 |            |          |
|*  2 |   HASH JOIN RIGHT ANTI   |                  |  4905 | 68670 |   485   (1)| 00:00:01 |
|*  3 |    VIEW                  | index$_join$_002 |   950 |  6650 |     6   (0)| 00:00:01 |
|*  4 |     HASH JOIN            |                  |       |       |            |          |
|*  5 |      INDEX RANGE SCAN    | IDX_P_V          |   950 |  6650 |     3   (0)| 00:00:01 |
|   6 |      INDEX FAST FULL SCAN| SYS_C008978      |   950 |  6650 |     4   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL     | C                |   100K|   683K|   479   (1)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C"."PID"="P"."PID")
   3 - filter("V"<=0.95)
   4 - access(ROWID=ROWID)
   5 - access("V"<=0.95)

PostgreSQL

mytest=# select sum(v) from c
mytest-#   where not exists (select pid from p where c.pid = p.pid and v<=0.95);
   sum
----------
 24233.38
(1 row)

Time: 103.857 ms

mytest=# explain
mytest-# select sum(v) from c
mytest-#   where not exists (select pid from p where c.pid = p.pid and v<=0.95);
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=25186.02..25186.03 rows=1 width=32)
   ->  Gather  (cost=25185.80..25186.01 rows=2 width=32)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=24185.80..24185.81 rows=1 width=32)
               ->  Hash Anti Join  (cost=42.39..24134.76 rows=20417 width=4)
                     Hash Cond: (c.pid = p.pid)
                     ->  Parallel Seq Scan on c  (cost=0.00..22348.67 rows=416667 width=8)
                     ->  Hash  (cost=30.50..30.50 rows=951 width=4)
                           ->  Seq Scan on p  (cost=0.00..30.50 rows=951 width=4)
                                 Filter: (v <= 0.95)
(10 rows)

Time: 0.751 ms

Query 6: "OUTER JOIN""

select sum(c.v) from c left outer join (select * from p where p.v<=0.95 ) p2 
    on c.pid=p2.pid where p2.pid is null;

Oracle

SQL> select sum(c.v) from c left outer join (select * from p where p.v<=0.95 ) p2
  2      on c.pid=p2.pid where p2.pid is null;

  SUM(C.V)
----------
    2484.1

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 3112675040

----------------------------------------------------------------------------------------------
| Id  | Operation                 | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                  |     1 |    14 |   485   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE           |                  |     1 |    14 |            |          |
|*  2 |   FILTER                  |                  |       |       |            |          |
|*  3 |    HASH JOIN RIGHT OUTER  |                  |  4905 | 68670 |   485   (1)| 00:00:01 |
|*  4 |     VIEW                  | index$_join$_003 |   950 |  6650 |     6   (0)| 00:00:01 |
|*  5 |      HASH JOIN            |                  |       |       |            |          |
|*  6 |       INDEX RANGE SCAN    | IDX_P_V          |   950 |  6650 |     3   (0)| 00:00:01 |
|   7 |       INDEX FAST FULL SCAN| SYS_C008978      |   950 |  6650 |     4   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL     | C                |   100K|   683K|   479   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("P"."PID" IS NULL)
   3 - access("C"."PID"="P"."PID"(+))
   4 - filter("P"."V"(+)<=0.95)
   5 - access(ROWID=ROWID)
   6 - access("P"."V"<=0.95)

PostgreSQL

mytest=# select sum(c.v) from c left outer join (select * from p where p.v<=0.95 ) p2
mytest-#     on c.pid=p2.pid where p2.pid is null;
   sum
----------
 24233.38
(1 row)

Time: 104.988 ms

mytest=# explain
mytest-# select sum(c.v) from c left outer join (select * from p where p.v<=0.95 ) p2
mytest-#     on c.pid=p2.pid where p2.pid is null;
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=25186.02..25186.03 rows=1 width=32)
   ->  Gather  (cost=25185.80..25186.01 rows=2 width=32)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=24185.80..24185.81 rows=1 width=32)
               ->  Hash Anti Join  (cost=42.39..24134.76 rows=20417 width=4)
                     Hash Cond: (c.pid = p.pid)
                     ->  Parallel Seq Scan on c  (cost=0.00..22348.67 rows=416667 width=8)
                     ->  Hash  (cost=30.50..30.50 rows=951 width=4)
                           ->  Seq Scan on p  (cost=0.00..30.50 rows=951 width=4)
                                 Filter: (v <= 0.95)
(10 rows)

Time: 0.844 ms

Sunday, June 11, 2023

Check the patch store size after Apr2023 (19.19) RU patch

Check the patch version

List Inactive Patches

Delete Inactive Patches

Check the patch store size again, size reduced from 7.2GB to 2.8GB.

List installed patches

Other advanced util commands comes with OPatch

Sunday, April 9, 2023

Granted ROLE Privileges

Granted SYSTEM Privileges

Granted Object Privileges

Script to extract ROLE DDL

Bug in Oracle 23c (23.2)

There is no such funny bug in Oracle 21c (21.9)

DROP and CREATE in Oracle 21c

DROP and CREATE in Oracle 23c with IF EXISTS and IF NOT EXISTS

CREATE OR REPLACE can't coexist with NOT EXISTS

Saturday, April 8, 2023

Check the setting before change

Change the default search path at database level

Reconnect

Tuesday, April 4, 2023

Sunday, March 19, 2023

Overview

Setup before testing

Database Version

Query 1: "IN"

Query 2: "EXISTS"

Query 3: "JOIN"

Query 4: "NOT IN"

Query 5: "NOT EXISTS"

Query 6: "OUTER JOIN""

Disclaimer

Labels

Blog Archive

Search This Website

Tweets by Me

DROP and CREATE in Oracle 23c with `IF EXISTS` and `IF NOT EXISTS`

`CREATE OR REPLACE` can't coexist with `NOT EXISTS`