Feed aggregator

Sequence functionality without a real sequence

Tom Kyte - Mon, 2021-12-06 13:26
Dear Tom, I am looking for a way to create a function or procedure that works like a sequence. To the background, we use a sequence to create a unique key in our application until the sequence comes to the limit. Then, we found, that most of the created keys from the sequence are not used (they where only temporarily used and nothing stored with this key). So we build a table with all the "unused" keys and create a function to get one of these keys each time we need a key. But now, we are facing massive row lock contentions because of very high frequency to that function and table. I am really interested to know, if there is a way to create a function or procedure that can cache 1000 entries for all processes. Do you have any idea to create such functionality or any other idea to fix our locking issue during the access to the table with the keys? best regards Joachim
Categories: DBA Blogs

Building Bigger Applications with FastAPI

Andrejus Baranovski - Sun, 2021-12-05 10:35
I share a few tips and tricks on how to build a clean and readable REST API for larger apps with FastAPI framework. You will learn how to split endpoints into separate Python scripts and assign tags with prefixes for all endpoints from the given router at once.

 

Refreshable Clone PDB -- 3 The ArchiveLog and ArchParLog files

Hemant K Chitale - Sun, 2021-12-05 05:15

 In my previous blog post about Datafile Name mapping for a Refreshable Clone PDB, I had presented only a portion of the trace file for the ALTER PLUGGABLE DATABASE RO_PDB REFRESH command.

Here is the full listing of that trace file (I have already explained the AFN mapping of FILE#s)


Trace file /opt/oracle/diag/rdbms/cdb2/CDB2/trace/CDB2_ora_6159.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
Build label: RDBMS_19.12.0.0.0DBRU_LINUX.X64_210715
ORACLE_HOME: /opt/oracle/product/19c/dbhome_1
System name: Linux
Node name: ora19cs2
Release: 4.14.35-1902.10.4.1.el7uek.x86_64
Version: #2 SMP Mon Jan 27 14:13:38 PST 2020
Machine: x86_64
Instance name: CDB2
Redo thread mounted by this instance: 1
Oracle process number: 59
Unix process pid: 6159, image: oracle@ora19cs2


*** 2021-12-04T17:39:25.431585+08:00 (RO_PDB(5))
*** SESSION ID:(377.22911) 2021-12-04T17:39:25.431608+08:00
*** CLIENT ID:() 2021-12-04T17:39:25.431613+08:00
*** SERVICE NAME:(CDB2) 2021-12-04T17:39:25.431617+08:00
*** MODULE NAME:(sqlplus@ora19cs2 (TNS V1-V3)) 2021-12-04T17:39:25.431622+08:00
*** ACTION NAME:() 2021-12-04T17:39:25.431626+08:00
*** CLIENT DRIVER:(SQL*PLUS) 2021-12-04T17:39:25.431630+08:00
*** CONTAINER ID:(5) 2021-12-04T17:39:25.431634+08:00

JIT: pid 6159 requesting stop

*** 2021-12-04T17:39:48.052938+08:00 (RO_PDB(5))
Started Serial Media Recovery
This is Foreign Recovery:- HotClone:1 Pre-Plugin:0 HotClone_for_Refresh:1. The Redo going to be applied in Current PDB(Target) will be from Foreign PDB(Source) and there will be file# conversion. Source AFN is from Foreign DB and Target AFN is on Current(local) DB. Dumping Source PDB:3 to Target PDB:5 AFN mapping.
Source AFN:26 = Target AFN:47
Source AFN:32 = Target AFN:49
Source AFN:12 = Target AFN:46
Source AFN:31 = Target AFN:48
Source AFN:9 = Target AFN:43
Source AFN:11 = Target AFN:45
Source AFN:10 = Target AFN:44
Dumping database incarnation table:
Resetlogs 0 scn and time: 0x0000000000f22944 11/30/2021 22:58:02
Dumping PDB pathvec - index 0
0000 : pdb 5, dbinc 3, pdbinc 0
db rls 0x0000000000f22944 rlc 1090018682
incscn 0x0000000000000000 ts 0
br scn 0x0000000000000000 ts 0
er scn 0x0000000000000000 ts 0
0001 : pdb 5, dbinc 2, pdbinc 0
db rls 0x0000000000e88404 rlc 1084312848
incscn 0x0000000000000000 ts 0
br scn 0x0000000000000000 ts 0
er scn 0x0000000000000000 ts 0
Recovery target incarnation = 3, activation ID = 0
Influx buffer limit = 39160 min(50% x 78320, 100000)
Start recovery at thread 1 ckpt scn 16121330 logseq 37 block 41766

*** 2021-12-04T17:39:48.430170+08:00 (RO_PDB(5))
Media Recovery add redo thread 1

*** 2021-12-04T17:39:48.494955+08:00 (RO_PDB(5))
Media Recovery Log /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2021_12_04/o1_mf_1_37_jtpcbxyv_.arc

*** 2021-12-04T17:39:48.868915+08:00 (RO_PDB(5))
Log read is SYNCHRONOUS though disk_asynch_io is enabled!

*** 2021-12-04T17:39:51.447683+08:00 (RO_PDB(5))
Media Recovery Log /opt/oracle/FRA/ORCLCDB/ORCLCDB/foreign_archivelog/ORCLPDB1/2021_12_04/o1_mf_1_38_jtpfttmz_.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!

*** 2021-12-04T17:40:04.041793+08:00 (RO_PDB(5))
==== Redo read statistics for thread 1 ====
Total physical reads (from disk and memory): 89402Kb
-- Redo read_disk statistics --
Read rate (SYNC): 89402Kb in 15.61s => 5.59 Mb/sec
Total redo bytes: 89402Kb Longest record: 24Kb, moves: 78/213168 moved: 0Mb (0%)
Longest LWN: 2052Kb, reads: 3089
Last redo scn: 0x0000000000f7e789 (16246665)
Change vector header moves = 31243/386616 (8%)
----------------------------------------------

*** 2021-12-04T17:40:04.041915+08:00 (RO_PDB(5))
Media Recovery drop redo thread 1

*** 2021-12-04T17:40:07.389420+08:00 (RO_PDB(5))
KCBR: Number of read descriptors = 1024
KCBR: Media recovery blocks read (ASYNC) = 650
KCBR: Influx buffers flushed = 40 times
KCBR: Reads = 30 reaps (5 null, 8 wait), 2 all
KCBR: Redo cache copies/changes = 15025/15025

*** 2021-12-04T17:40:07.389466+08:00 (RO_PDB(5))
Completed Media Recovery
----- Abridged Call Stack Trace -----
(deleted by me)


The interesting parts are :
"Start recovery at thread 1 ckpt scn 16121330 logseq 37 block 41766"
and
"Media Recovery Log /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2021_12_04/o1_mf_1_37_jtpcbxyv_.arc"
and
"Media Recovery Log /opt/oracle/FRA/ORCLCDB/ORCLCDB/foreign_archivelog/ORCLPDB1/2021_12_04/o1_mf_1_38_jtpfttmz_.arc"

It identifies the SCN from which it needs to recover transactions from the source CDB (ORCLCDB) and PDB (ORCLPDB1).  In a MultiTenant environment, all the PDBs share the same Redo Thread -- so any ArchiveLog may have transactions of multple PDBs.
For the purpose of refresh RO_PDB in CDB2, it identifies the entries from the source CDB ArchiveLogs.  For Log Sequence#28, it explicitly identifies the ORCLPDB1 (not the whole CDB) entries as "foreign archivelog".  However, this "foreign archivelog" is later automaticallly purged by ORCLCDB as I can see in the ORCLCDB alert log entry

2021-12-04T17:40:07.555215+08:00
Deleted Oracle managed file /opt/oracle/FRA/ORCLCDB/ORCLCDB/foreign_archivelog/ORCLPDB1/2021_12_04/o1_mf_1_38_jtpfttmz_.arc


However, sometimes the Refresh requires to read Redo Entries where an ArchiveLog has not been completely generated.  For example, a later Refresh at 22:41 required a partial ArchiveLog for Sequence#41

Start recovery at thread 1 ckpt scn 16246668 logseq 0 block 0

*** 2021-12-04T22:41:32.365818+08:00 (RO_PDB(5))
Media Recovery add redo thread 1

*** 2021-12-04T22:41:32.370629+08:00 (RO_PDB(5))
Media Recovery Log /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2021_12_04/o1_mf_1_38_jtpzhl2s_.arc

*** 2021-12-04T22:41:32.470154+08:00 (RO_PDB(5))
Log read is SYNCHRONOUS though disk_asynch_io is enabled!

*** 2021-12-04T22:41:39.259689+08:00 (RO_PDB(5))
Media Recovery Log /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2021_12_04/o1_mf_1_39_jtpzhxck_.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!

*** 2021-12-04T22:41:42.187854+08:00 (RO_PDB(5))
Media Recovery Log /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2021_12_04/o1_mf_1_40_jtpzhy18_.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!

*** 2021-12-04T22:41:42.773949+08:00 (RO_PDB(5))
Media Recovery Log /opt/oracle/product/19c/dbhome_1/dbs/archparlog_1_41_f1dfc534_1084312848.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!


Sequence#41 was still an Online Redo Log at ORCLCDB and had not yet been archived (In fact, the source ORCLPDB FRA was full and ORCLPDB was unable to Archive Log files). So, it seems that for Sequence#41, a Partial Extract was obtained so as to Refresh the Clone PDB.
This doesn't seem to be documented.
Categories: DBA Blogs

How To Resolve Tablespace Created With Windows Path In Linux

Michael Dinh - Sat, 2021-12-04 09:20

Now I understand the rationale for not providing DBA privileges to inexperience developers.

Currently, I am uncertain if the current environment is production or not.

Here is demo for 11.2.0.4 to demonstrate the issue and resolution.

Reference:
https://docs.oracle.com/cd/E18283_01/server.112/e17120/dfiles005.htm#i1006478

[oracle@ol7-112-dg1 trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 4 14:46:37 2021

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /u01/oradata

----------------------------------------
--- Windows path used for datafile:
----------------------------------------

SQL> create tablespace TEST datafile 'D:\Oracle\oradata\E1Local\TEST.dbf' SIZE 16M;

Tablespace created.

SQL> set lines 200
SQL> col name for a80
SQL> select file#,name from v$datafile order by 1;

     FILE# NAME
---------- --------------------------------------------------------------------------------
         1 /u01/oradata/hawk/system01.dbf
         2 /u01/oradata/hawk/sysaux01.dbf
         3 /u01/oradata/hawk/undotbs01.dbf
         4 /u01/oradata/hawk/users01.dbf
         5 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf

SQL> alter tablespace TEST offline normal;

Tablespace altered.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEST                           OFFLINE

6 rows selected.

SQL> host

----------------------------------------
--- Rename datafile from OS
----------------------------------------

[oracle@ol7-112-dg1 trace]$ mv -fv /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf /u01/oradata/test.dbf
‘/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf’ -> ‘/u01/oradata/test.dbf’

[oracle@ol7-112-dg1 trace]$ exit
exit

SQL> alter tablespace TEST rename datafile '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf' TO '/u01/oradata/test.dbf';

Tablespace altered.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEST                           OFFLINE

6 rows selected.

SQL> alter tablespace TEST online;

Tablespace altered.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEST                           ONLINE

6 rows selected.

SQL> drop tablespace TEST including contents and datafiles;

Tablespace dropped.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ol7-112-dg1 trace]$

----------------------------------------
--- Alert Log: NO ERRORS!
----------------------------------------

[oracle@ol7-112-dg1 trace]$ tail -25 alert_hawk.log
LNS: Standby redo logfile selected for thread 1 sequence 126 for destination LOG_ARCHIVE_DEST_2
Sat Dec 04 14:47:17 2021
create tablespace TEST datafile 'D:\Oracle\oradata\E1Local\TEST.dbf' SIZE 16M
Completed: create tablespace TEST datafile 'D:\Oracle\oradata\E1Local\TEST.dbf' SIZE 16M
Sat Dec 04 14:48:08 2021
alter tablespace TEST offline normal
Completed: alter tablespace TEST offline normal
Sat Dec 04 14:48:08 2021
Starting background process SMCO
Sat Dec 04 14:48:08 2021
SMCO started with pid=36, OS id=5159
Sat Dec 04 14:48:19 2021
Checker run found 2 new persistent data failures
Sat Dec 04 14:49:33 2021
alter tablespace TEST rename datafile '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf' TO '/u01/oradata/test.dbf'
Completed: alter tablespace TEST rename datafile '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf' TO '/u01/oradata/test.dbf'
Sat Dec 04 14:49:42 2021
Checker run found 1 new persistent data failures
Sat Dec 04 14:50:00 2021
alter tablespace TEST online
Completed: alter tablespace TEST online
Sat Dec 04 14:50:33 2021
drop tablespace TEST including contents and datafiles
Deleted file /u01/oradata/test.dbf
Completed: drop tablespace TEST including contents and datafiles
[oracle@ol7-112-dg1 trace]$

----------------------------------------
--- Resolution for 19c with ASM
----------------------------------------

SQL> alter session set container=PDB;
SQL> ALTER DATABASE MOVE DATAFILE '/u02/app/oracle/product/19.0.0.0/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf' TO '+DATAC1';
__ATA.cmd.push(function() { __ATA.initDynamicSlot({ id: 'atatags-26942-61ab87396a577', location: 120, formFactor: '001', label: { text: 'Advertisements', }, creative: { reportAd: { text: 'Report this ad', }, privacySettings: { text: 'Privacy', } } }); });

Refreshable Clone PDB -- 2 Datafile Names

Hemant K Chitale - Sat, 2021-12-04 03:57

 In my previous post, I demonstrated creating a Refreshable Clone PDB.  How do you handle datafile names ? You can use the FILE_NAME_CONVERT to change file names based on substring matching of the file names.  Or you could simply rely on the good-old (old nowadays !) feature / parameter DB_CREATE_FILE_DEST

In the ORCLPDB1 Pluggable Database at ORCLDB (Source),  these are my datafiles :

SQL> connect sys/manager@orclcdb as sysdba
Connected.
SQL> show parameter db_create_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /opt/oracle/oradata
SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> col file_name format a98
SQL> select file_id, file_name
2 from dba_data_files
3 order by file_id
4 /

FILE_ID FILE_NAME
---------- --------------------------------------------------------------------------------------------------
9 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
10 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
11 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
12 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
26 /opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_data_min_j2p8z0qn_.dbf
31 /opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoo5nr_.dbf
32 /opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoojqn_.dbf

7 rows selected.

SQL>



Note that DB_CREATE_FILE_DEST can be modified at any time. Here you can see that it was  modified before the the last 3 tablespaces/datafiles were added to the database.  (The datafiles are automatically sized at an initial 100MB, autoextensible to 32GB).

So, what are the file names in the RO_PDB at CDB2 ?


SQL> connect sys/manager@cdb2 as sysdba
Connected.
SQL> alter pluggable database ro_pdb open read only;

Pluggable database altered.

SQL> show parameter db_create_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /opt/oracle/oradata
SQL> alter session set container=RO_PDB;

Session altered.

SQL> col file_name format a98
SQL> select file_id, file_name
2 from dba_data_files
3 order by file_id
4 /

no rows selected

SQL>


HEY ! Why I can't I see the data files in RO_PDB ?  This is likely Oracle Bug#30536162.  See MOS Document "Some Data File Missing From DBA_DATA_FILES or CDB_DATA_FILES in Refreshable PDB (Doc ID 2610743.1)".   The workaround is to use V$DATAFILE at the Instance Level.
Let me check that in both instances.

At the source :

SQL> connect sys/manager@ORCLCDB as sysdba
Connected.
SQL> select file#, name
2 from v$datafile
3 where con_id =
4 (select con_id
5 from v$pdbs
6 where name = 'ORCLPDB1')
7 order by 1
8 /

FILE# NAME
------ --------------------------------------------------------------------------------------------------
9 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
10 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
11 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
12 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
26 /opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_data_min_j2p8z0qn_.dbf
31 /opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoo5nr_.dbf
32 /opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoojqn_.dbf

7 rows selected.

SQL>


And, at the target :

SQL> connect sys/manager@cdb2 as sysdba
Connected.
SQL> show parameter db_create_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /opt/oracle/oradata
SQL>
SQL> select file#, name
2 from v$datafile
3 where con_id =
4 (select con_id
5 from v$pdbs
6 where name = 'RO_PDB')
7 order by 1
8 /

FILE# NAME
---------- --------------------------------------------------------------------------------------------------
43 /opt/oracle/oradata/CDB2/D240086D48C012D0E0550A00275FC834/datafile/o1_mf_system_jtncqq6j_.dbf
44 /opt/oracle/oradata/CDB2/D240086D48C012D0E0550A00275FC834/datafile/o1_mf_sysaux_jtncqq6n_.dbf
45 /opt/oracle/oradata/CDB2/D240086D48C012D0E0550A00275FC834/datafile/o1_mf_undotbs1_jtncqq6o_.dbf
46 /opt/oracle/oradata/CDB2/D240086D48C012D0E0550A00275FC834/datafile/o1_mf_users_jtncqq6r_.dbf
47 /opt/oracle/oradata/CDB2/D240086D48C012D0E0550A00275FC834/datafile/o1_mf_data_min_jtncqq6s_.dbf
48 /opt/oracle/oradata/CDB2/D240086D48C012D0E0550A00275FC834/datafile/o1_mf_hemant_d_jtncqq6t_.dbf
49 /opt/oracle/oradata/CDB2/D240086D48C012D0E0550A00275FC834/datafile/o1_mf_hemant_d_jtncqq6v_.dbf

7 rows selected.

SQL>


Unlike the source ORCLPDB1 in ORCLCDB, the datafiles in RO_PDB at CDB2 are all OMF files because I had configured DB_CREATE_FILE_DEST before creating RO_PDB.

Why are the FILE_IDs (FILE# in V$DATAFILE) different between the Source PDB and the Target PDB ?  This is because the Source Container Database and Target Container Database may have different numbers of Pluggable Databases, each with different numbers of datafiles.  So, when the Target Pluggable Database is created from the Source PDB, it is allocated FILE# values as they are available in the target Container Database.

For example, these are the numbers at the Source (4 CON_IDs, 18 Datafiles) :

SQL> select con_id, file#
2 from v$datafile
3 order by 1,2
4 /

CON_ID FILE#
---------- ------
1 1
1 3
1 4
1 7
2 5
2 6
2 8
3 9
3 10
3 11
3 12
3 26
3 31
3 32
4 27
4 28
4 29
4 30

18 rows selected.

SQL>
SQL> select con_id
2 from v$pdbs
3 where name = 'ORCLPDB1'
4 /

CON_ID
----------
3

SQL>


And these are at the Target (5 CON_ID, 23 datafiles)

SQL> select con_id, file#
2 from v$datafile
3 order by 1,2
4 /

CON_ID FILE#
---------- ----------
1 1
1 3
1 4
1 7
2 5
2 6
2 8
3 9
3 10
3 11
3 12
3 26
4 27
4 28
4 29
4 30
5 43
5 44
5 45
5 46
5 47
5 48
5 49

23 rows selected.

SQL> select con_id
2 from v$pdbs
3 where name = 'RO_PDB'
4 /

CON_ID
----------
5

SQL>


The Source ORCLPDB1 is CON_ID=3 at ORCLCDB and the Target RO_PDB is CON_ID=5 at CDB2.

If I refresh RO_PDB from the source

SQL> alter pluggable database ro_pdb close;

Pluggable database altered.

SQL> alter pluggable database ro_pdb refresh;

Pluggable database altered.

SQL> alter pluggable database ro_pdb open read only;

Pluggable database altered.

SQL>


I find this in the session's trace file at CDB2 (the Target doing the Refresh) :

Trace file /opt/oracle/diag/rdbms/cdb2/CDB2/trace/CDB2_ora_6159.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
Build label: RDBMS_19.12.0.0.0DBRU_LINUX.X64_210715
ORACLE_HOME: /opt/oracle/product/19c/dbhome_1
System name: Linux
Node name: ora19cs2
Release: 4.14.35-1902.10.4.1.el7uek.x86_64
Version: #2 SMP Mon Jan 27 14:13:38 PST 2020
Machine: x86_64
Instance name: CDB2
Redo thread mounted by this instance: 1
Oracle process number: 59
Unix process pid: 6159, image: oracle@ora19cs2


*** 2021-12-04T17:39:25.431585+08:00 (RO_PDB(5))
*** SESSION ID:(377.22911) 2021-12-04T17:39:25.431608+08:00
*** CLIENT ID:() 2021-12-04T17:39:25.431613+08:00
*** SERVICE NAME:(CDB2) 2021-12-04T17:39:25.431617+08:00
*** MODULE NAME:(sqlplus@ora19cs2 (TNS V1-V3)) 2021-12-04T17:39:25.431622+08:00
*** ACTION NAME:() 2021-12-04T17:39:25.431626+08:00
*** CLIENT DRIVER:(SQL*PLUS) 2021-12-04T17:39:25.431630+08:00
*** CONTAINER ID:(5) 2021-12-04T17:39:25.431634+08:00

JIT: pid 6159 requesting stop

*** 2021-12-04T17:39:48.052938+08:00 (RO_PDB(5))
Started Serial Media Recovery
This is Foreign Recovery:- HotClone:1 Pre-Plugin:0 HotClone_for_Refresh:1. The Redo going to be applied in Current PDB(Target) will be from Foreign PDB(Source) and there will be file# conversion. Source AFN is from Foreign DB and Target AFN is on Current(local) DB. Dumping Source PDB:3 to Target PDB:5 AFN mapping.
Source AFN:26 = Target AFN:47
Source AFN:32 = Target AFN:49
Source AFN:12 = Target AFN:46
Source AFN:31 = Target AFN:48
Source AFN:9 = Target AFN:43
Source AFN:11 = Target AFN:45
Source AFN:10 = Target AFN:44
.... and there is more information in the trace file which I will cover in my next Blog Post


The Source FILE# values (from ORCLPDB1 at ORCLCDB) are : 26, 32, 12, 31, 9, 11, 10
The corresponding Target FILE# values (in RO_PDB at CDB2) are : 47, 49, 46, 48, 43, 45, 44

To match them, check that the single SYSTEM tablespace datafile  is FILE#9 in ORCLPDB1 and FILE#43 in RO_PDB.
The single USERS tablespace datafile is FILLE#12 in ORCLPDB1 and FILE#46 in RO_PDB.
The two HEMANT_DATA tablespace datafile are FILE#s 31, 32 in ORCLPDB1 and FILE#s 48, 49 in RO_PDB.
Quite unfortunately, it seems that the mapping information in the Trace File is not ordered by FILE#

The trace file for the ALTER PLUGGABLE DATABASE RO_PDB has more information which I will cover in the next Blog Post.

Categories: DBA Blogs

influxdb: copying data with SELECT INTO - pay attention to the TAGS (or they are transformed to fields)

Dietrich Schroff - Sat, 2021-12-04 03:17

 If you are using influxdb, one usecase could be, copy the data from a measurement ("table") to another.

This can be done with this statement:

select * into testtable2 from testtable1

By the way: the CLI is opened with

/usr/bin/influx -unsafeSsl -ssl -database telegraf(if your database is named telegraf)

In my case (zigbee / mqtt / telegraf) the layout of mqtt_consumer measurement was like this:

> show tag keys from mqtt_consumer
name: mqtt_consumer
tagKey
------
host
topic
> show field keys from mqtt_consumer
name: mqtt_consumer
fieldKey    fieldType
--------    ---------
battery     float
contact     boolean
current     float
...
But after copying this to a testtable, the tags where gone and everything was a field. 

This is not a big problem - you can work with that data without a problem. BUT if you want to copy it back or merge it to the original table, you will get a table with the additional columns host_1 and topic_1.

This is because for influx you already had a column host. So it added a column field host_1. 

If a query in this new table (with host + host_1) spans over a time where both of this columns are in, you only select the data, with the entry host. If the time spans only entries with host_1, it is shown as host and you get your data. Really a unpredictable way to get data.

What is the solution? Easy:

select * into table1 from mqtt_consumer group by host,topic The "group by" does not group anything. It just tells influx: host & topic are tags and not fields. Please do not transform them...


Transition from signle tenant Multitenant database

Tom Kyte - Fri, 2021-12-03 12:06
Hi TOM, i have been working with single tenant database from the start 8i upto Database version 11.2 , now i need to work with multitenant database 12c and above. i have installed 19c database, but i am having difficulty moving around (login etc) Can you point some resources which will make it clear to transition from single tenant to multitenant database. Thanks Fahd
Categories: DBA Blogs

Character set of processed file

Tom Kyte - Fri, 2021-12-03 12:06
Dear Asktom team, We are processing external tables, where it is agreed that the incoming files are in character set EE8SSWIN1250. Nevertheless, it may happen that the incoming file has a different character set. The file is processed, but the characters are scrambled. That leads to unwanted results. Is it possible to check from PL/SQL the character set of the file before processing? Thanks, Dusan
Categories: DBA Blogs

Can you Receive Compensation for Anxiety after a Car Accident in Las Vegas?

OraQA - Fri, 2021-12-03 05:39

Is anxiety a thing that you can claim after an accident? Then the answer is whenever someone gets injured in an accident in Las Vegas; he seeks a compensation claim. They are likely to get a lesser settlement as your primary attention is only on the physical injuries after an accident. But the truth is that accidents always cause some amount of mental trauma. It may manifest differently and leave some impact on your ability to live everyday life. The Naqvi Accident Injury Law firm can help you get appropriate compensation. This blog will discuss entitlement to receive money for anxiety or any psychological effects. Before that, let’s read a bit about the types of damages. 

Types of damages after a car accident:

There are two types of damages after a car accident-economic damage and non-economic damage. 

  • Economic damage- It includes the monetary damages cover the property damages, medical bills, and lost wages
  • Non-economic damage – It is measured by your pain and emotional distress, including anxiety. Anxiety can seriously affect life to a specific grade.

How to prove anxiety issues to get the claim?

It will help if you do not neglect the emotional trauma. Just because it’s not visible like a physical injury doesn’t mean it won’t affect you. It doesn’t necessarily mean that you’re entitled to a claim for your anxiety. But you have to prove and show that the stress happened post-accident. Psychiatrists and psychologists can help you get the claim with proper medical documentation. It is the only way to get a lawsuit.

How much compensation can you receive?

A personal injury attorney can help you prove your condition after the accident and help you get the compensation amount. You will get paid the compensation amount in an agreement or a jury decision. It also depends on whether the other party’s insurance company agrees to settle for a justifiable amount or your lawyer suggests going to court to get a higher amount.

Conclusion

Take professionals to help you with your case as they understand that recovering, dealing with trauma and anxiety can affect your life in different ways. An attorney can help you negotiate till the insurance company offers you a justifiable compensation amount. Else he helps you to take your case to court. Then the jury decides the compensation amount

The post Can you Receive Compensation for Anxiety after a Car Accident in Las Vegas? appeared first on ORA QA.

Column Groups

Jonathan Lewis - Fri, 2021-12-03 04:40

Here’s a little test that should have been the second test I ran when column groups were first made available in Oracle. It’s a check on the second of the two basic algorithms that Oracle uses for combining predicates:

  1. sel(predA and predB) = sel(predA) * sel(predB)
  2. sel(predA or predB) = sel(predA) + sel(predB) – sel(predA and predB)

As I pointed out many years ago in Cost Based Oracle – Fundamentals these are the formulae for combining probabilities of independent events. But if the predicates are not independent the formulae make the optimizer produce bad cardinality estimates and that’s why the Oracle developers introduced column groups – virtual columns that gather statistics about combinations of columns.

So let’s set up an extreme example and test the results (I’ve run it one 21c and 19c, but the issue is the same for all versions with column groups):

rem
rem     Script:         column_group_and_or.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Dec 2021
rem
rem     Last tested 
rem             21.3.0.0
rem             19.11.0.0
rem

create table t1
as
with generator as (
        select 
                rownum id
        from dual 
        connect by 
                level <= 1e4    -- > comment to avoid WordPress format issue
)
select
        mod(rownum,100)                 n1,
        mod(rownum,100)                 n2,
        lpad(rownum,10,'0')             v1,
        lpad('x',100,'x')               padding
from
        generator 
;

prompt  =========================================
prompt  Run the script twice, 
prompt  first without gathering the column group
prompt  then after gathering the column group
prompt  =========================================

begin
        dbms_stats.gather_table_stats(
                ownname     => null,
                tabname     => 'T1',
                method_opt  => 'for columns (n1, n2) size 1'
        );
end;
/

column column_name format a32

select
        column_name, num_distinct, data_default
from
        user_tab_cols
where
        table_name = 'T1'
/

This script gives you a table with 10,000 rows, where the n1 and n2 columns always have exactly the same values as each other, and each column has 100 rows each of 100 distinct values. If you allow the pl/sql block to run the column stats query will report the following:

COLUMN_NAME                      NUM_DISTINCT DATA_DEFAULT
-------------------------------- ------------ --------------------------------------------------------------------------------
N1                                        100
N2                                        100
V1                                      10000
PADDING                                     1
SYS_STUBZH0IHA7K$KEBJVXO5LOHAS            100 SYS_OP_COMBINED_HASH("N1","N2")

5 rows selected.

After creating the data (and statistics) we can execute the following simple statements:

set autotrace traceonly explain

select  * 
from    t1
where
        n1 = 50
/

select  * 
from    t1
where
        n1 = 50
and     n2 = 50
/

select  *
from    t1
where
        n1 = 50
or      n2 = 50
/

set autotrace off

We know that the first query will return 100 rows, and that the optimizer has enough information to get the correct estimate: the selectivity of “n1 = 50” is 1/100, the number of rows in the table is 10,000, so the estimate should be 1/100 * 10,000 = 100.

For the second query: if we don’t create the column group we expect the optimizer to multiply the selectivities of the two predicates together before multiplying by the number of rows in the table to get an estimate of (1/100 * 1/100) * 10,000 = 1

If we’ve created the column group we expect the optimizer to use the column group selectivity to calculate its estimate (1/100 * 10,000) = 100.

Then we get to the third query: if we don’t create the column group we expect the optimizer to slot 1/100 into the 4 places it has to appear in the formula to get an estimate (1/100 + 1/100 – (1/100 * 1/100)) * 10,000 = 199.

If we have created the column group the third expression in the formula can use the column group 1/num_distinct for the third expression to get: (1/100 + 1/100 – (1/100)) * 10,000 = 100

Here are the three execution plans (with a little cosmetic work) when the column group exists:

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 | 11800 |    26   (4)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   100 | 11800 |    26   (4)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N1"=50)


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 | 11800 |    26   (4)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   100 | 11800 |    26   (4)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N1"=50 AND "N2"=50)


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   199 | 23482 |    27   (8)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   199 | 23482 |    27   (8)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N1"=50 OR "N2"=50)

As you can see from the results in the Rows column, the optimizer has used the column group statistics for the AND estimate, but not for the OR estimate, resulting in an estimate that is out by a factor of nearly 2.

It should be noted that failure to use the column group for AND’ed predicates can introduce a massive error in cardinality estimates but for OR’ed predicates the worst it can be is a factor of 2.

Summary

There is a possible enhancement to the the optimizer’s use of column group statistics that could make the arithmetic consistent for AND and OR predicates and could allow the optimizer to produce better estimates for OR conditions.

Although any error in cardinality estimates can lead to undesirable execution plans the scale of the error due to the current inconsistency will be at worst a factor or 2, which is unlikely to introduce many surprise plans. The nature of the error means the estimate will be too big, which might push the optimizer into using tablescans and hash joins when indexed accesses and nested loops might be the better choice.

Problem with the pivot and unpivot functions - is it possible to merge numbers with text?

Tom Kyte - Thu, 2021-12-02 17:46
Hello everyone, I ask for help to solve a problem with the pivot and unpivot functions. is it possible to merge numbers with text? in the result I would like to add the row of the column ects_cod. Thank you <code>CDS_COD;VALORE;31;30;29;28;27;26;25;24;23;22;21;20;19;18 AG0060;vote;31;30;29;28;27;26;25;24;23;22;21;20;19;18 AG0060;tot;297;655;509;731;632;583;496;427;316;282;226;214;142;169 AG0060;%;5;12;9;11;11;10;9;8;6;5;4;4;3;3 AG0061;ects_cod;A;A-B;B;B-C;C;C;C-D;D;D;D;D;E;E;E</code>
Categories: DBA Blogs

Little Things Doth Crabby Make – Part XXIII. IEC 60027 Still Makes Me Crabby!

Kevin Closson - Wed, 2021-12-01 17:57

I know the base 10, base 2 IEC 60027-2 thing only makes me crabby. But since it does, I had to make a Little Things post. This sort of stuff, well, makes me crabby:

OK, now that’s off my chest. Have a good day and thanks for stopping by.

Best Practice

Jonathan Lewis - Wed, 2021-12-01 13:08

This showed up recently on the Oracle Developer Forum.

For a while I’m questioning myself about a best practice query for performance.

I’ve 2 tables: EMPLOYEES and DEPARTMENTS. The EMPLOYEES table can contains millions of records. Which query is better for performance ?

Select t1.emp_id, t1.emp_name, t2.dept_name
from EMPLOYEES t1, DEPARTMENTS t2
where t1.dept_id = t2.dept_id
order by t1.emp_name
Select emp_id, emp_name, (select mypackage.get_dept_name(dept_id) from dual) as dept_name
from EMPLOYEES
order by emp_name

The nice thing thing about this question is the correct use of English grammar; it uses “better” rather than “best” (the former is correct when comparing two things, the latter requires at least three things to be compared). On the down-side, any suggestion that a single pattern might be “best practice” for performance should be viewed with great caution; it’s far too easy for the less experience to translate “this is a good idea for this query” as “you should always do this”. At best it’s possible to identify patterns at the edges of style where you could say either: “this usually works well enough” or “you shouldn’t do this unless it’s really necessary”.

So I’m going to ignore the generic question and pretend that the question was supposed to be about the performance of the specific case; and then I’m going to complain that the question is badly posed because it omits several pieces of information that are likely to be relevant.

Some points to consider as background before looking too closely into performance:

  • We might expect that there is a referential integrity (RI) constraint between departments and employees. We might also expect to see emp.dept_id declared as not null – if it’s not then do we really want employees who are not yet assigned to a department to disappear from the report. Maybe the join should be an outer join.
  • How many departments are there. If there are millions of employees might there be thousands, or even tens of thousands, of departments? That’s going to make a difference to performance and (depending on strategy) to the consistency of the performance.
  • How important is it to squeeze the last bit of performance from this query. An Oracle database can perform perfectly well with millions of rows but how rapidly can the network transfer the rows from the database to the client, and how well is the client process going to deal with such a large result set. (Why does the client want to run a query that returns millions of rows – is the client then going to do something, after a long and tedious data transfer, that should have been done in 1/100th of the time in the database to supply a much smaller result set).
  • How competent are the people who may be required to support the query once it has gone into production. Should you follow the principle of preferring simplicity to optimum performance. What’s the difference in (database) performance between the better performance and the one that’s easier to understand?
  • Is there any reason why the query must not use parallel execution. Depending on the coding strategy adopted it’s possible that the slower serial query becomes the faster parallel query, and parallelism hasn’t even been mentioned.
  • Do you think that the version of Oracle may change the relative performance of different strategies for the query? (The correct answer is “yes”, and the query that uses a function call and scalar subquery is the one that’s more likely to be affected by future tweaks to the Oracle internal processing.)

So let’s look at specifics.

I’m going to ignore the option for using of a PL/SQL function to generate a department name from an id. Possibly the hope that using it would reduce the number of times a piece of SQL was executed against the dept table; but you shouldn’t use the public result cache for such a lightweight call, particularly when the session’s scalar subquery cache would probably get all the benefit that was available anyway.

Assume, then, that the referential integrity and not null declarations are in place, and that the number of departments is vaguely commensurate with the millions of employees. I’ve set up a little test with 50,000 rows in an “emp” table and 200 rows in a “dept” table just to have some concrete results to prompt a few thoughts.

Given the nature of the query (no predicates to limit data selected) the simple join with “order by” probably has only one sensible path:

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |  50000 |00:00:00.03 |     953 |       |       |          |
|   1 |  SORT ORDER BY      |      |      1 |  50000 |  50000 |00:00:00.03 |     953 |  3738K|   834K| 3322K (0)|
|*  2 |   HASH JOIN         |      |      1 |  50000 |  50000 |00:00:00.01 |     953 |  1335K|  1335K| 1566K (0)|
|   3 |    TABLE ACCESS FULL| DEPT |      1 |    200 |    200 |00:00:00.01 |       5 |       |       |          |
|   4 |    TABLE ACCESS FULL| EMP  |      1 |  50000 |  50000 |00:00:00.01 |     948 |       |       |          |
-----------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPT"."ID"="EMP"."DEPT_ID")

I’ve run this with with rowsource execution stats enabled so that I can highlight details that might suggest ways to save resources like CPU or memory consumption. This gives rise to two thoughts:

First, would the subquery method do anything to reduce the volume of data sorted – i.e. would the subquery run late; secondly, even if it didn’t run late might it reduce the work needed to translate department ids into department names.

Here’s a version of the query using a scalar subquery with the resulting execution plan:

select 
        emp.id, emp.ename,
        (select dept.dept_name from dept where dept.id = emp.dept_id) dept_name
from 
        emp
order by
         emp.ename
/

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |  50000 |00:00:00.05 |    5884 |       |       |          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |   4931 |      1 |   4931 |00:00:00.01 |    4935 |       |       |          |
|*  2 |   INDEX UNIQUE SCAN         | DEPT_PK |   4931 |      1 |   4931 |00:00:00.01 |       4 |       |       |          |
|   3 |  SORT ORDER BY              |         |      1 |  50000 |  50000 |00:00:00.05 |    5884 |  3738K|   834K| 3322K (0)|
|   4 |   TABLE ACCESS FULL         | EMP     |      1 |  50000 |  50000 |00:00:00.01 |     949 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPT"."ID"=:B1)

The plan for any query with scalar subqueries in the select list reports the subqueries at the same level as the main query and before the query – so we can see here that the scalar subquery has been executed only 4,931 times, rather than the full 50,000, and that’s the benefit of scalar subquery caching. The execution count is in the thousands rather than being 200 (number of departments) because the cache is rather small and when hash values for department ids collide hashing only the first into the cache is retained (the cache doesn’t use linked lists to handle collisions).

There’s some ambiguity here, though. We can see that the Buffers statistic at operation 3 (the Sort) matches the total for the query, and consists of the value for the table scan plus the value for the subquery execution (5884 = 4935 + 949, but don’t ask me why the emp tablescan reports 949 buffers instead of the 948 previously reported) so we can’t easily tell whether Oracle executed the subquery before or after it had sorted the data from the tablescan. This could make a difference to performance since including the department name in the data to be sorted could mean using a lot more memory (and more I/O if the larger volume resulted in the sort spilling to disc). We can do a quick check with a simplified query – selecting only the department id from emp, rather than translating id to name.

select 
        emp.id, emp.ename,
        emp.dept_id
--      (select dept.dept_name from dept where dept.id = emp.dept_id) dept_name
from 
        emp
order by
         emp.ename
/

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |  50000 |00:00:00.02 |     948 |       |       |          |
|   1 |  SORT ORDER BY     |      |      1 |  50000 |  50000 |00:00:00.02 |     948 |  2746K|   746K| 2440K (0)|
|   2 |   TABLE ACCESS FULL| EMP  |      1 |  50000 |  50000 |00:00:00.01 |     948 |       |       |          |
----------------------------------------------------------------------------------------------------------------


With this simpler query the Used-Mem for the sort operation drops from 3322K to 2440K so Oracle must have been using the scalar subquery to fetch the department name before sorting. (I don’t know (but why Buffers for the tablescan has dropped to 948 again.)

So maybe it would be worth rewriting the query to sort the “narrower” data set before calling the scalar subquery:

select
        /*+ 
                qb_name(main) 
                no_merge(@inline)
                no_eliminate_oby(@inline)
        */
        v1.id, v1.ename, 
        (select /*+ qb_name(ssq) */ dept.dept_name from dept where dept.id = v1.dept_id) dept_name
from    (
        select 
                /*+ qb_name(inline) */
                emp.id, emp.ename, emp.dept_id
        from 
                emp
        order by
                emp.ename
        )       v1
/

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |  50000 |00:00:00.03 |     949 |       |       |          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |   4909 |      1 |   4909 |00:00:00.01 |    5112 |       |       |          |
|*  2 |   INDEX UNIQUE SCAN         | DEPT_PK |   4909 |      1 |   4909 |00:00:00.01 |     203 |       |       |          |
|   3 |  VIEW                       |         |      1 |  50000 |  50000 |00:00:00.03 |     949 |       |       |          |
|   4 |   SORT ORDER BY             |         |      1 |  50000 |  50000 |00:00:00.02 |     949 |  2746K|   746K| 2440K (0)|
|   5 |    TABLE ACCESS FULL        | EMP     |      1 |  50000 |  50000 |00:00:00.01 |     949 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPT"."ID"=:B1)

As you can see, my execution plan now has a View operation telling us that the Sort operation completed inside a non-mergable view; and we can see that the Buffers and the Used-Mem statistics for the Sort operation has stayed at the 948(+1 again) buffers and 2440K of the simpler query. Of course, the need for the data (i.e. sorted rowsource) to pass up the plan through the view operation means a little extra CPU at that point before we start executing the subquery. By a lucky fluke the hash collisions on the scalar subquery cache have dropped slightly because the data department ids are arriving in a different order – this can be a good thing but, as you will have seen in the linked article above on filter subqueries, the change of order could have been a disaster. (More articles on scalar subquery caching – and deterministic functions – linked to from this URL)

You might also note the odd little glitch in the reporting – the final figure for Buffers has “lost” the result from the scalar subquery. (And this was on 19.11.0.0 – so it’s a not an old error.)

So it seems we can do better than either of the options supplied in the original post. But the important question then is whether we should adopt that strategy or not. And the answer in many cases will be “probably not, unless it’s in very special circumstances and carefully documented”.

I’ve used a no_merge() hint and the undocumented no_eliminate_oby() hint; and that happens to have done what I wanted in the release of Oracle I was using; but in principle the optimizer could unnest the “@qb_name(ssq)” scalar subquery and produce a join between the “@qb_name(inline)” inline view and the unnested table – and that might cause the ordered data to become disordered (even though the sort order by operation would still be taking place). That’s a little unlikely, of course, but if it did happen someone would have to notice and deal with an urgent rewrite. (Strangely the reason why the optimizer doesn’t do this unnest in 19c and 21c is because – according to the 10053 trace file – the subquery “might return more than one row”, even though it’s selecting by equality on primary key!)

Summary Observations

Resist the temptation to ask for “best practices” for writing performant SQL. There are no immediately practical answers to the question (though there may be a couple of suggestions about how not to write your SQL).

You don’t necessarily want to put the more performant query into production.

Footnote

If you want to play around with this model, here’s the script I used to generate the data and run the test:

Click here to expand/contract the text
rem
rem     Script:         emp_dept_optimise.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Sept 2021
rem     Purpose:        
rem
rem     Last tested 
rem             19.11.0.0
rem

define m_dept=200

drop table emp;
drop table dept;

execute dbms_randoms.seed(0)

create table dept as
select 
        rownum id, 
        lpad(dbms_random.value,20) dept_name, 
        rpad('x',100) padding
from
        all_objects
where 
        rownum &amp;lt;= &amp;amp;m_dept
/

alter table dept add constraint dept_pk primary key(id);

create table emp as
select 
        rownum id, 
        1 + mod(rownum-1,&amp;amp;m_dept) dept_id,
        lpad(dbms_random.value,20) ename, 
        rpad('x',100) padding
from
        all_objects
where
        rownum &amp;lt;= 50000
/

alter table emp add constraint emp_pk primary key (id)
/

alter table emp add constraint emp_fk_dept foreign key(dept_id) references dept
/

set linesize 180
set arraysize 250

set termout off
set serveroutput off

alter session set statistics_level = all;

select 
        emp.id, emp.ename,
        emp.dept_id
--      (select dept.dept_name from dept where dept.id = emp.dept_id) dept_name
from 
        emp
order by
         emp.ename
/

spool emp_dept_optimise.lst

prompt  ============================================
prompt  For a baseline on memory, query with dept_id
prompt  ============================================

select * from table(dbms_xplan.display_cursor(format=&amp;gt;'allstats last'));
spool off

select 
        emp.id, emp.ename,
        dept.dept_name
from 
        emp,
        dept
where
        dept.id = emp.dept_id
order by
         emp.ename
/

spool emp_dept_optimise.lst append

prompt  ======================================
prompt  For a simple join between emp and dept
prompt  ======================================

select * from table(dbms_xplan.display_cursor(format=&amp;gt;'allstats last projection'));
spool off

select 
        emp.id, emp.ename,
--      emp.dept_id
        (select dept.dept_name from dept where dept.id = emp.dept_id) dept_name
from 
        emp
order by
         emp.ename
/

spool emp_dept_optimise.lst append

prompt  ==============================================
prompt  Now switching to the dept_name scalar subquery
prompt  ==============================================

select * from table(dbms_xplan.display_cursor(format=&amp;gt;'allstats last'));
spool off



select
        /*+ 
                qb_name(main) 
                no_merge(@inline)
                no_eliminate_oby(@inline)
        */
        v1.id, v1.ename, 
        (select /*+ qb_name(ssq) */ dept.dept_name from dept where dept.id = v1.dept_id) dept_name
from    (
        select 
                /*+ qb_name(inline) */
                emp.id, emp.ename, emp.dept_id
        from 
                emp
        order by
                emp.ename
        )       v1
/

spool emp_dept_optimise.lst append

prompt  ==================================================
prompt  Now with the dept_name scalar subquery 'postponed'
prompt  ==================================================

select * from table(dbms_xplan.display_cursor(format=&amp;gt;'allstats last outline'));
spool off

set serveroutput on
alter session set statistics_level = typical;

sql tuning advisor

Tom Kyte - Wed, 2021-12-01 05:06
Hi Tom, I am not able to understand one thing, I have two test databases, and separate hardware. It is a Server class installation and Enterprise addition, with all default optimizer settings. In one database say db1, when I am running dbms sqltune advisory it says a better execution plan exists and suggests me to accept the new plan, and when I am excepting the new plan, the response time gets reduced to half. In the second database the advisory says no better plan is there for the same query. and the costs are different in both the databases. Could you please help. Regards.
Categories: DBA Blogs

dbi services at AWS re:Invent 2021 : day 2

Yann Neuhaus - Tue, 2021-11-30 19:35

For my day 1 recap, please jump here.

For the second day of that conference, I decided first to attend the keynote of Adam Selipsky, CEO of Amazon Web Services. Lots of annoucements as always covering various area and services. For instance : AWS Lake Formation, new Row and cell-level security, transactions for governed tables, a AWS Private 5G service allowing users to create their own private mobile network, a new AWS Mainframe Modernization service, new types of EC2 instances, aso.
The video from AWS is available here https://www.youtube.com/watch?v=WGA2P_oH5Xc

Right after this keynote, I rushed and headed to a very interesting workshop.

Testing resiliency of AWS workloads using chaos engineering

Organized by Seth Eliot and Mahanth Jayadeva from AWS, this was a 135 min workshop. As its title says, it is focused on testing resiliency. Workloads deployed on AWS cloud should apply best practices established by AWS. They called this set of best practices the AWS Well-Architected Framework. Five pillars in that framework : Operational excellence, Security, Reliability, Performance efficiency and Cost optimization.

Here, we were there to test resiliency and improve reliability.

Design principles for reliability:

  • Automatically recover from failure
  • Test recovery procedures

The workshop started, a kind-of basic workload was already deployed to a given AWS account using AWS Cloud formation. It consisted in a set of EC2 instances, using RDS MySQL databases, with an ALB on top of it.
Various activities allowed us to inject EC2 failures (for instance an instance goes down, how the system could react ?) or RDS instance failures (Is the RDS is deployed in a multi-AZ schema ?)
Failures were manually triggered either by executing bash scripts or by using the AWS FIS service, FIS standing for Failure Injection Simulator.

One of the sessions I then decided to attend was related to Kubernetes on AWS cloud : EKS. I jumped into a shuttle and was right on time for that presentation.

Getting to large Amazon EKS clusters

Presented by Alex Kestner and Shyam Jeedigunta, the presentation started right by showing the result of a CNCF study, claiming that 65% of organizations choose AWS to run their containers (including so all AWS related solutions).
A quick recap then showed the benefit of running on AWS EKS : easy to scale, more security, better integration with other AWS services.
AWS EKS runs vanilla Kubernetes, is upstream and supports 4 versions of Kubernetes, giving time for the users to test and roll out upgrades. A slide surprised me, showing how portable EKS is : you can run it on AWS cloud of course, but also on AWS Outpost, on AWS Local Zones, using AWS EKS Anywhere on on-premises infrastructures (using VMware vSphere). There is even a dedicated Linux distro, called Amazon EKS Distro.

In term of high-availability, Amazon EKS survives to single-AZ events, provides highly available cluster endpoints, with a 99.95% SLA.  To support large clusters deployments, AWS made some changes. They improved the etcd volume throughput up to 3000 IOPS. The api-server was also improved : its throughput is increased dynamically, as well as kube-controllers for an increase of pods creation/deletion rate and the kubelet.

In term of networking, IPv6 has been introduced, one of the advantage here is to fight against IP shortage and get a unique IPv6 address assigned per pod.

Two components brought my attention.

Karpenter is an open-source Kubernetes auto-scaler. By using it, you should be able to spin-off automatically new compute resources when pods are in pending state because of lack of resources.

Bottlerocket OS is an open-source OS built by AWS and designed to run containers only. Promising faster boot-time and better resource utilization as only the need components are there, it is fully compatible with EKS

Both available on GitHub and not tighted to AWS only, those tools are definitely on my to-do list on my journey with AWS EKS.

And even more fun…

This event is synonym of learn and sharing sessions, but also with fun ! In a total respect of health measures in place, lots of side events are organized. For example, ping pong tournaments were scheduled. We also met a real human-sized pac-man or safely played with light-saber.

This day was very dense, here is just an extract of the sessions we attended. The steps count on the smartwatch reached its peak ! However, looking forward for the third day.

 

 

Cet article dbi services at AWS re:Invent 2021 : day 2 est apparu en premier sur Blog dbi services.

More tricks with OPT_PARAM

XTended Oracle SQL - Tue, 2021-11-30 18:24

Did you know you can set most parameters for the execution of a single statement without using an Alter Session by using an OPT_PARAM hint? For example, regular parameters (here forcing the storage clause in the query plan):

SQL> select /*+ OPT_PARAM('cell_offload_plan_display' 'always') */ col1 From table1;

and underscore parameters:

SQL>  select /*+ OPT_PARAM('_arch_comp_dbg_scan',2048) */ n_name from nation;

However if you try conflicting settings that set a different value in an inner query block, the value you set in the outermost query block will prevail. In this trvial example _arch_comp_dbg_scan=2048 will prevail:

SQL> select /*+ OPT_PARAM('_arch_comp_dbg_scan',2048) */ n_name from nation
where n_nationkey = (
    select /*+ OPT_PARAM('_arch_comp_dbg_scan',0) */ min(r_regionkey)
    from region where n_name < r_name); 

Another point to remember is that not all parameters get their value from the cursor environment. For example the buffer cache gets the raw value of _serial_direct_read so it cannot be overruled in this way: 

SQL> select /*+ OPT_PARAM('_serial_direct_read', always) */ n_name from nation; 

 will not force a DR scan of the table but an alter session will.

Just one more tool to keep in your toolkit for when you need it.


Roger

Categories: Development

Improvements to HCC with wide tables in 12.2

XTended Oracle SQL - Tue, 2021-11-30 18:20
HCC Compression Unit Sizing 

Since the beginning Oracle has provided four compression levels to offer a trade-off between the compression ratio and various other factors including table scans and the performance of single-row retrieval. I can not emphasize enough that the various trade offs mean that YMMV with the different levels and you should always test what works best with your application and hardware when choosing the level. Historically people have rarely used Query Low since the fast compression with reduced compression ratio means that the extra disk I/O is slower than the cost of decompression with Query High. The one time that Query Low makes sense on spinning disks is if you still have a significant number of row retrieval operations (including from index access joins).

NMVe Flash

X5 introduced NVMe technology which means that the extra I/O from Query Low is faster than ZLIB decompression which makes Query Low beneficial. So we needed to reassess the sizing of Compression Units. from 11.2.0.1 to 12.1.2.4 the sizing guidelines are as follows:

Name Target
 Rows
 Target 
 Minimum Size
  Target
 Maximum Size
 CompressionQuery Low 1000 to 8000 32 kb 32 kb LZOQuery High 1000 to 8000 32 kb 64 kb ZLIBArchive Low 8000 64 kb 256 kb ZLIBArchive High 8000 256 kb 256 kb BZ2

So, for example, Compress for Query High aims to pivot around at least 1000 rows and create a minimum compression unit size of 32 kb and a maximum of 64 kb. Using 12.1.2.3 I ran these against a TPC-H Lineitem table than contained between 1 and 6 copies of each column.

For Query Low fixed 32 kb CUs this gave us the following:

 Additional copies of lineitem Rows per 32 kb CU 0 2797 1 580 2 318 3 216 4 162 5 129

and for Query High 32 to 64 kb CUs this gave us:

 Additional copies of lineitem Rows per 32 kb CU CU ave size 0 5031 32 1 1010 32 2 936 51 3 794 63 4 595 67 5 476 63

so we see that the CU size remains as 32 kb as long as we are getting a 1000 rows or more then increases in size to 64 kb to try and fit in at least 1000 rows.

It became clear that this size range was inadequate for wide tables so to get more efficient compression and longer column runs for faster predicate performance (and also better CELLMEMORY rewrites) we removed the fixed size for Query Low and increased the max:

Query Low: 32 kb to 64 kb

Query High:  32 kb to 80 kb

This will not affect narrow tables at all but wider tables should see better table compression and faster scans at the cost of slightly slower single row retrieval for data loaded by 12.2 RDBMS. If you have HCC wide tables and typically cache them on flash cache you should consider re-evaluating Query Low for data loaded in 12.2 (or use Alter Table Move Compress to recompress existing data).

Roger 

Categories: Development

How to tell if the Exadata column cache is fully loaded

XTended Oracle SQL - Tue, 2021-11-30 18:11

When a background activity is happening on the cell you typically can’t use RDBMS v$ views to monitor it in the same way. One such question is how to tell if a segment is fully loaded in the Exadata column cache since this does not appear in the equivalent In-Memory v$ views.

When a segment is scanned by Smart Scan sufficiently often to be eligible the AUTOKEEP pool (typically that means at least twice an hour), the eligible 1MB chunks are written to flash in 12.1.0.2 style format, and put on a background queue. Lower priority tasks pick up the queued 1MB 12.1.0.2 format chunks from the flash cache, run them though the In-Memory loader, and rewrite the pure columnar representation in place of the old 12.1.0.2 style column cache chunks.

The easiest way that I know of to tell when this completes is to monitor that background activity is to use the following query until it shows zero:

select name, sum(value) value from (
      select extractvalue(value(t),'/stat/@name') name,
            extractvalue(value(t),'/stat') value
      from v$cell_state cs,
           table(xmlsequence(extract(xmltype(cs.statistics_value),
                                     '//stats[@type="columnarcache"]/stat'))) t
     where statistics_type='CELL')
     where name in ('outstanding_imcpop_requests')
     group by name;

Categories: Development

External Tables Part 1 – Project Columns All vs Referenced

XTended Oracle SQL - Tue, 2021-11-30 18:09

I normally blog about table scans on Oracle native data but Oracle also supports a wide variety of features for scanning external tables and I need to cover these too. One of the ways I learn new things is being sent a bug report and saying to myself “Oh! I didn’t know you could do that”. So today I’m going to start with the grammar:

Alter Table <xt> Project Columns [ All | Referenced ]  

This DDL changes the value in the Property column displayed in user_external_tables:

SQL> select property
2    from user_external_tables
3    where table_name='C_ET';

PROPERTY
----------
ALL       

Here we have an external table defined using the legacy driver ORACLE_LOADER. This driver defaults to projecting all the columns in the base table rather than just those needed to satisfy the query (i.e. the referenced columns) and discarding rows that have data conversion issues up to the reject limit. 

So for example we have a DATE column in our external table that contains dirty data that won’t convert cleanly to Oracle internal dates using the supplied mask we can either import it as a VARCHAR2 to not lose values or import it as a date but lose rows even on queries that don’t need that date column. We can change the behaviour to only project the referenced columns by 

SQL> alter table c_et project column referenced;
 
Table altered.
 
SQL> 
SQL> select property 
2    from user_external_tables
3    where table_name='C_ET';
 
PROPERTY   
----------
REFERENCED 

The driver will now ignore unreferenced columns and if the date column is not used we will get all the rows in the external data and the query will run faster since datatype conversion is expensive but we may get an inconsistent number of rows depending on which columns are used in any given query. This is OK if the DBA knows a priori that there are no conversion errors or if they are willing to live with inconsistency in the number of rows returned.

The big data drivers such as ORACLE_HIVE have a different default behaviour which is to only project referenced columns and to replace data with conversion errors with NULL values and i.e. they default to returning a consistent number of rows with best performance.

Take away: In order to get consistency and fast table scan performance with ORACLE_LOADER,  the trick is to define the external table with the ‘convert_error store_null’ option and switch to ‘Project Column Referenced’. For example:

CREATE TABLE "T_XT"
(
  c0 varchar(10),
  c1 varchar(10)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY DMPDIR
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE
    CHARACTERSET  al32utf8
    ESCAPE
    FIELDS CSV WITH EMBEDDED terminated by ',' enclosed by '|'
    REJECT ROWS WITH ALL NULL FIELDS
    nullif equal blanks
    truncate_columns
    convert_error store_null
    (
      "C0" CHAR(10),
      "C1" CHAR(10)
    )
  )
  location
  (
    'simple.dat'
  )
) REJECT LIMIT UNLIMITED
parallel 4;

If you want more information on handling conversion errors when scanning an external table please check the Utilities Guide section on each driver type

Categories: Development

Examining the new Columnar Cache with v$cell_state

XTended Oracle SQL - Tue, 2021-11-30 18:03

12.1.0.2 introduced the new Columnar Flash Cache where 1MB of  blocks that are all in HCC format are rewritten so as to make each column from each CU contiguous. This works by first writing all the block headers to an array, then writing all the CU headers to an array, finally writing all the Column 1 column-level-CUs, then writing all the Column2 column-level-CUs etc.

The flash cache hash table maintains a simple mapping of column numbers to 64KB flash pages so, for any given query, we can simply do asynchronous disk I/O of the minimum set of 64KB pages required to cover the referenced columns.

Within the “flashcache” cell stats there is a new nested cell stat called “columnarcache” that enables you to track how it is working.

> set long 50000000
> set pagesize 10000
> select xmltype(STATISTICS_VALUE).getclobval(2,2) from v$cell_state;

XMLTYPE(STATISTICS_VALUE).GETCLOBVAL(2,2)
--------------------------------------------------------------------------------
....

  <stats type="flashcache">
    <stats type="columnarcache">
      <stat name="columnar_cache_size">0</stat>
      <stat name="columnar_cache_keep_size">0</stat>
      <stat name="columnar_cache_keep_read">0</stat>
      <stat name="columnar_cache_keep_read_bytes">0</stat>
      <stat name="columnar_attempted_read">0</stat>
      <stat name="columnar_attempted_read_bytes">0</stat>
      <stat name="columnar_cache_hits">0</stat>
      <stat name="columnar_cache_hits_read_bytes">0</stat>
      <stat name="columnar_cache_hits_saved_bytes">0</stat>
      <stat name="columnar_cache_pop">0</stat>
      <stat name="columnar_cache_pop_bytes">0</stat>
      <stat name="columnar_cache_pop_throttled">0</stat>
      <stat name="columnar_cache_pop_invalid_buf">0</stat>
      <stat name="columnar_cache_pop_size_align">0</stat>
      <stat name="columnar_cache_pop_findchdrfailure_mainchdr">0</stat>
      <stat name="columnar_cache_pop_findchdrfailure_childchdr">0</stat>
    </stats>
  </stats>

I typically spool the output of this to wade through it an editor but if we want to monitor how it is working with some workload, we need to extract individual stats, for example I bounced the cells and verified the cache was empty:

> select xmlcast(xmlquery('/cell_stats/stats/stats/stat[@name="columnar_cache_size"]' passing xmltype(STATISTICS_VALUE) returning content) as varchar2(200) ) "COLUMNAR CACHE SIZE"
from v$cell_state
where xmlexists('/cell_stats/stats/stats[@type="columnarcache"]' passing xmltype(STATISTICS_VALUE));

COLUMNAR CACHE SIZE
--------------------------------------------------------------------------------
0

I am using the 1GB TPC-H schema which takes a little more 400MB on disk when compressed with Query Low:

SQL> select sum(bytes) from user_segments where SEGMENT_NAME in ('SUPPLIER','PARTSUPP','LINEITEM','ORDERS','PART','CUSTOMER');

SUM(BYTES)
----------
 420675584

and checking the columnar cache again shows about half of the data has been rewritten into columnar cache format instead of caching raw blocks:

SQL> select xmlcast(xmlquery('/cell_stats/stats/stats/stat[@name="columnar_cache_size"]' passing xmltype(STATISTICS_VALUE) returning content) as varchar2(200) ) "COLUMNAR CACHE SIZE"
from v$cell_state
where xmlexists('/cell_stats/stats/stats[@type="columnarcache"]' passing xmltype(STATISTICS_VALUE));

COLUMNAR CACHE SIZE
--------------------------------------------------------------------------------
179306496

So let’s look at how the cache helped:

select
   xmlcast(
      xmlquery(
         '/cell_stats/stats/stats/stat[@name="columnar_cache_hits_read_bytes"]' 
         passing xmltype(STATISTICS_VALUE) 
         returning content
      ) as varchar2(200) 
   ) "HIT READ BYTES"
from v$cell_state
where xmlexists('/cell_stats/stats/stats[@type="columnarcache"]' passing xmltype(STATISTICS_VALUE));

 
HIT READ BYTES
--------------------------------------------------------------------------------
1909456896

select
   xmlcast(
      xmlquery(
         '/cell_stats/stats/stats/stat[@name="columnar_cache_hits_saved_bytes"]'
         passing xmltype(STATISTICS_VALUE)
         returning content
      ) as varchar2(200) 
   ) "HIT SAVED BYTES"
from v$cell_state
where xmlexists('/cell_stats/stats/stats[@type="columnarcache"]' passing xmltype(STATISTICS_VALUE));

 
HIT SAVED BYTES
--------------------------------------------------------------------------------
1128267776

which shows we were able to eliminate about 35% of the disk I/O for this query run!

We could, of course, have gotten that information more simply with the regular stat “cell physical IO bytes saved by columnar cache” but I wanted to show how to pull values from v$cell_state for use in scripts.


Many people only use Query High compression as they find the increased disk I/O from Query Low more than offsets the savings from cheaper decompression costs. However, with the columnar cache in place, those trade-offs have changed. It may be worth re-evaluating the decision as to when user Query Low vs. Query High particularly on CPU-bound cells.

Addendum: performance bug 20525311 affecting the columnar cache with selective predicates is fixed in the next rpm.

Roger MacNicol
Oracle Data Storage Technology 
Categories: Development

Pages

Subscribe to Oracle FAQ aggregator