Feed aggregator

Listagg Vs Stragg

Tom Kyte - 4 hours 41 sec ago
Tom: I was benchmarking stragg with listagg and results below, is that stragg is better than listagg? Please let me know your thoughts on this. <code>drop table t purge; create table t as select * from ( select * from all_objects ...
Categories: DBA Blogs

Difference between select * and select a.* with alias on a table

Tom Kyte - 4 hours 41 sec ago
Hi Tom, What is the difference between using "select * from table1" and "selelct a.* from table1 a". Observed that while doing the select statement "select a.* from table1 a" locks the data for update (like if we use the statement from PLSQL dev...
Categories: DBA Blogs

BLOB Columns In tables

Tom Kyte - 4 hours 41 sec ago
Hi Tom, This is a general question about the design of the tables in a DAILY use database. The users will uploading and downloading pdf files from a web app daily. What i want to ask is it a good idea to create my tables with BLOB columns and savi...
Categories: DBA Blogs

CLOB to XMLTYPE out of memory issue

Tom Kyte - 4 hours 41 sec ago
I am copying XML data from Operating System file into CLOB and then casting as XMLTYPE for extracting information into relational database. This solution is working for years but I received a business requirement to process big XML files ranging fro...
Categories: DBA Blogs

Materialized View On Commit Refresh failure

Tom Kyte - 4 hours 41 sec ago
Hi Team, Thanks a lot for all the support you have provided so far :) I have created an On Commit Materialized view MVA recently on tableA. Primary Key M View log is used here. Occasionally this refresh fails with ORA-12034 error. Since thi...
Categories: DBA Blogs

explain plan for index scan

Tom Kyte - 4 hours 41 sec ago
Hi Tom, i have updated the plan.i was unaware how to submit the further information on the questions asked, thats why put the questions again. this is with reference to question asked at below link https://asktom.oracle.com/pls/apex/asktom.sear...
Categories: DBA Blogs

Full Table Scan on join query

Tom Kyte - 4 hours 41 sec ago
Hello, Ask TOM Team. I have two tables: TABLE1 (parent) and TABLE2 (child). TABLE1_ID (FK) on TABLE2 table has an index. When I run a join query, I got a table access full. There are ~4M rows in the result set. <b>select * from user.table1 t ...
Categories: DBA Blogs

SQL Versus ASMCMD

Michael Dinh - Tue, 2020-05-26 23:10

This is a set of scripts to help on Oracle ASM Monitoring

Reviewing the SQL looks rather complicated especially when trying to find information for specific Disk Group.

Use one command line to retrieve data or create complex SQL to do the same?


Size_MB  is Bytes/1e6
Alloc_MB is Space/1e6

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> @asm.sql

                                                                                                                                                              System
ASM File Name / Volume Name / Device Name                                                  Size_MB           Alloc_MB File Type          Creation Date        Created?
------------------------------------------------------------------------------- ------------------ ------------------ ------------------ -------------------- --------
+ORCL_SID_DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.311.990946061                                     0                  1      PARAMETERFILE 31-OCT-2018 06:47:40    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/CONTROLFILE/Backup.313.990988403                                43                 50        CONTROLFILE 31-OCT-2018 18:33:22    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/CONTROLFILE/Current.257.990939051                                0                  8        CONTROLFILE 31-OCT-2018 04:50:50    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/CONTROLFILE/Current.258.990939051                                0                  8        CONTROLFILE 31-OCT-2018 04:50:50    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/CONTROLFILE/current.256.990939051                               43                 50        CONTROLFILE 31-OCT-2018 04:50:51    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/AUDIT_TBS.264.996655365                            32,212             32,247           DATAFILE 04-JAN-2019 08:42:44    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/AUDIT_TBS.319.996656095                             1,074              1,076           DATAFILE 04-JAN-2019 08:54:55    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/AVAIL.277.990939537                                   701                703           DATAFILE 31-OCT-2018 04:58:56    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/EUR_ORDER.271.990939353                            32,212             32,247           DATAFILE 31-OCT-2018 04:55:53    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/EUR_ORDER.316.992686897                               315                317           DATAFILE 20-NOV-2018 10:21:37    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/EUR_PDA.321.1007139241                             11,035             11,037           DATAFILE 01-MAY-2019 16:54:01    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/EZFETCH_IE.270.990939353                            3,821              3,823           DATAFILE 31-OCT-2018 04:55:53    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/EZFETCH_UK.269.990939199                           33,521             33,555           DATAFILE 31-OCT-2018 04:53:18    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/EZFETCH_UK.279.990939547                            6,946              6,948           DATAFILE 31-OCT-2018 04:59:06    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_ESS.284.990939589                          105                107           DATAFILE 31-OCT-2018 04:59:49    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_IAS_OPSS.285.990939591                     126                128           DATAFILE 31-OCT-2018 04:59:50    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_IAS_UMS.287.990939593                      703                705           DATAFILE 31-OCT-2018 04:59:53    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_IAU.289.990939595                           63                 65           DATAFILE 31-OCT-2018 04:59:55    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_MDS.282.990939585                          734                736           DATAFILE 31-OCT-2018 04:59:45    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_SOAINFRA.290.1010082787                 32,212             32,247           DATAFILE 04-JUN-2019 18:33:07    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_SOAINFRA.292.1010082785                 32,212             32,247           DATAFILE 04-JUN-2019 18:33:04    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_SOAINFRA.314.1010082781                 32,212             32,247           DATAFILE 04-JUN-2019 18:33:00    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_SOAINFRA.318.1010082769                 32,212             32,247           DATAFILE 04-JUN-2019 18:32:48    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_SOAINFRA.326.1013130689                 13,342             13,344           DATAFILE 09-JUL-2019 01:11:28    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_STB.288.990939595                           10                 12           DATAFILE 31-OCT-2018 04:59:54    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_WLS.286.990939591                           63                 65           DATAFILE 31-OCT-2018 04:59:51    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/IFW_DT_TBS.280.990939551                           12,885             12,887           DATAFILE 31-OCT-2018 04:59:10    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/IFW_IDX_TBS.281.990939585                           1,074              1,076           DATAFILE 31-OCT-2018 04:59:45    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/KOMPASS.274.990939395                              34,360             34,361           DATAFILE 31-OCT-2018 04:56:35    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/KOMPASS.324.1028374119                             13,627             13,629           DATAFILE 30-DEC-2019 11:28:39    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/MBSYNC_UK_MCD.291.990939597                        25,166             25,200           DATAFILE 31-OCT-2018 04:59:57    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/PDA_STG.267.990939163                              34,360             34,361           DATAFILE 31-OCT-2018 04:52:42    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/PDA_STG.278.990939539                              33,580             33,589           DATAFILE 31-OCT-2018 04:58:59    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/PDA_STG.325.1014480263                              6,107              6,109           DATAFILE 24-JUL-2019 16:04:23    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/PERFSTAT.320.1001228979                             2,961              2,963           DATAFILE 26-FEB-2019 07:09:39    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/SYSAUX.260.990939059                               34,341             34,361           DATAFILE 31-OCT-2018 04:50:59    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/SYSAUX.268.990939163                                9,026              9,028           DATAFILE 31-OCT-2018 04:52:43    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/SYSAUX.275.990939395                               32,212             32,247           DATAFILE 31-OCT-2018 04:56:35    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/SYSTEM.259.990939059                               11,996             11,998           DATAFILE 31-OCT-2018 04:50:58    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/UNDOTBS1.261.990939059                             34,360             34,361           DATAFILE 31-OCT-2018 04:50:59    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/UNDOTBS1.283.1041158311                             4,295              4,297           DATAFILE 23-MAY-2020 10:38:30    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/UNDOTBS2.263.990939063                             34,360             34,361           DATAFILE 31-OCT-2018 04:51:03    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/UNDOTBS2.323.1041061103                            34,359             34,361           DATAFILE 22-MAY-2020 07:38:23    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/USERS.262.990939059                                     7                  7           DATAFILE 31-OCT-2018 04:50:59    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/WEBMDATA.272.990939357                             27,259             27,281           DATAFILE 31-OCT-2018 04:55:57    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/WEBMINDX.273.990939393                              1,074              1,076           DATAFILE 31-OCT-2018 04:56:32    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAGUARDCONFIG/ORCL_SID_PHX1RAC.312.990949397                   0                  1    DATAGUARDCONFIG 31-OCT-2018 07:43:17    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_1.293.990939821                              1,074              1,076          ONLINELOG 31-OCT-2018 05:03:41    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_10.300.990939985                             1,074              1,076          ONLINELOG 31-OCT-2018 05:06:25    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_11.301.990940017                             1,074              1,076          ONLINELOG 31-OCT-2018 05:06:56    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_12.302.990940049                             1,074              1,076          ONLINELOG 31-OCT-2018 05:07:28    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_13.303.990940081                             1,074              1,076          ONLINELOG 31-OCT-2018 05:08:01    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_14.304.990940083                             1,074              1,076          ONLINELOG 31-OCT-2018 05:08:02    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_2.294.990939853                              1,074              1,076          ONLINELOG 31-OCT-2018 05:04:12    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_21.305.990940085                             1,074              1,076          ONLINELOG 31-OCT-2018 05:08:04    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_22.306.990940115                             1,074              1,076          ONLINELOG 31-OCT-2018 05:08:35    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_23.307.990940179                             1,074              1,076          ONLINELOG 31-OCT-2018 05:09:38    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_24.308.990940241                             1,074              1,076          ONLINELOG 31-OCT-2018 05:10:40    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_25.309.990940273                             1,074              1,076          ONLINELOG 31-OCT-2018 05:11:12    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_26.310.990940273                             1,074              1,076          ONLINELOG 31-OCT-2018 05:11:13    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_3.295.990939915                              1,074              1,076          ONLINELOG 31-OCT-2018 05:05:15    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_4.296.990939917                              1,074              1,076          ONLINELOG 31-OCT-2018 05:05:17    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_5.297.990939919                              1,074              1,076          ONLINELOG 31-OCT-2018 05:05:19    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_6.298.990939951                              1,074              1,076          ONLINELOG 31-OCT-2018 05:05:51    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_9.299.990939953                              1,074              1,076          ONLINELOG 31-OCT-2018 05:05:53    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/TEMPFILE/FMWEURPD12_IAS_TEMP.317.991419415                  18,822             18,824           TEMPFILE 05-NOV-2018 18:16:55    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/TEMPFILE/NA_ARCHIVE_TEMP.322.1007140587                     10,737             10,740           TEMPFILE 01-MAY-2019 17:16:26    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/TEMPFILE/TEMP.315.991419415                                 34,359             34,361           TEMPFILE 05-NOV-2018 18:16:54    Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/dr1ORCL_SID.dat                                                  0                  1    DATAGUARDCONFIG 31-OCT-2018 07:43:17    N
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/snapcf_ORCL_SID.f                                               43                 50        CONTROLFILE 31-OCT-2018 18:33:22    N
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/spfileORCL_SID.ora                                               0                  1      PARAMETERFILE 31-OCT-2018 06:47:40    N
                                                                                ------------------ ------------------
                                                                                           736,613            737,115

                                                                                ------------------ ------------------
Grand Total:                                                                               736,613            737,115

71 rows selected.

SQL>

================================================================================

$ asmcmd lsdg -g ORCL_SID_DATA
Inst_ID  State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
      1  MOUNTED  EXTERN  N         512             512   4096  1048576    786492    83442                0           83442              0             N  ORCL_SID_DATA/
      2  MOUNTED  EXTERN  N         512             512   4096  1048576    786492    83442                0           83442              0             N  ORCL_SID_DATA/

================================================================================

$ asmcmd ls -lsh ORCL_SID_DATA/*/*
Type             Redund  Striped  Time             Sys  Block_Size   Blocks        Bytes        Space  Name

+ORCL_SID_DATA/ORCL_SID_PHX1RAC/CONTROLFILE/:
CONTROLFILE      UNPROT  FINE     MAY 26 22:00:00  Y           16K     2635        41.2M          48M  Backup.313.990988403
CONTROLFILE      UNPROT  FINE     OCT 31 2018      Y            2K        1           2K           8M  Current.257.990939051
CONTROLFILE      UNPROT  FINE     OCT 31 2018      Y            2K        1           2K           8M  Current.258.990939051
CONTROLFILE      UNPROT  FINE     MAR 11 16:00:00  Y           16K     2635        41.2M          48M  current.256.990939051

+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/:
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  AUDIT_TBS.264.996655365
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   131073           1G           1G  AUDIT_TBS.319.996656095
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    85625       668.9M         670M  AVAIL.277.990939537
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  EUR_ORDER.271.990939353
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    38401         300M         302M  EUR_ORDER.316.992686897
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1347073        10.3G        10.3G  EUR_PDA.321.1007139241
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   466433         3.6G         3.6G  EZFETCH_IE.270.990939353
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4091905        31.2G        31.3G  EZFETCH_UK.269.990939199
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   847873         6.5G         6.5G  EZFETCH_UK.279.990939547
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    12801         100M         102M  FMWEURPD12_ESS.284.990939589
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    15361         120M         122M  FMWEURPD12_IAS_OPSS.285.990939591
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    85761         670M         672M  FMWEURPD12_IAS_UMS.287.990939593
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K     7681          60M          62M  FMWEURPD12_IAU.289.990939595
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    89601         700M         702M  FMWEURPD12_MDS.282.990939585
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  FMWEURPD12_SOAINFRA.290.1010082787
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  FMWEURPD12_SOAINFRA.292.1010082785
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  FMWEURPD12_SOAINFRA.314.1010082781
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  FMWEURPD12_SOAINFRA.318.1010082769
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1628673        12.4G        12.4G  FMWEURPD12_SOAINFRA.326.1013130689
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K     1281          10M          11M  FMWEURPD12_STB.288.990939595
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K     7681          60M          62M  FMWEURPD12_WLS.286.990939591
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1572865          12G          12G  IFW_DT_TBS.280.990939551
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   131073           1G           1G  IFW_IDX_TBS.281.990939585
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194303          32G          32G  KOMPASS.274.990939395
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1663489        12.7G        12.7G  KOMPASS.324.1028374119
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3072001        23.4G        23.5G  MBSYNC_UK_MCD.291.990939597
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194303          32G          32G  PDA_STG.267.990939163
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4099073        31.3G        31.3G  PDA_STG.278.990939539
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   745473         5.7G         5.7G  PDA_STG.325.1014480263
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   361473         2.8G         2.8G  PERFSTAT.320.1001228979
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4192001          32G          32G  SYSAUX.260.990939059
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1101825         8.4G         8.4G  SYSAUX.268.990939163
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  SYSAUX.275.990939395
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1464321        11.2G        11.2G  SYSTEM.259.990939059
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194303          32G          32G  UNDOTBS1.261.990939059
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   524289           4G           4G  UNDOTBS1.283.1041158311
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194303          32G          32G  UNDOTBS2.263.990939063
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194177          32G          32G  UNDOTBS2.323.1041061103
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K      801         6.3M           7M  USERS.262.990939059
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3327489        25.4G        25.4G  WEBMDATA.272.990939357
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   131073           1G           1G  WEBMINDX.273.990939393

+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAGUARDCONFIG/:
DATAGUARDCONFIG  UNPROT  COARSE   MAY 19 03:00:00  Y            4K        3          12K           1M  ORCL_SID_PHX1RAC.312.990949397

+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/:
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_1.293.990939821
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_10.300.990939985
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_11.301.990940017
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_12.302.990940049
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_13.303.990940081
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_14.304.990940083
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_2.294.990939853
ONLINELOG        UNPROT  COARSE   MAY 26 21:00:00  Y           512  2097153           1G           1G  group_21.305.990940085
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_22.306.990940115
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_23.307.990940179
ONLINELOG        UNPROT  COARSE   MAY 26 20:00:00  Y           512  2097153           1G           1G  group_24.308.990940241
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_25.309.990940273
ONLINELOG        UNPROT  COARSE   MAY 26 20:00:00  Y           512  2097153           1G           1G  group_26.310.990940273
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_3.295.990939915
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_4.296.990939917
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_5.297.990939919
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_6.298.990939951
ONLINELOG        UNPROT  COARSE   MAY 26 21:00:00  Y           512  2097153           1G           1G  group_9.299.990939953

+ORCL_SID_DATA/DB_UNKNOWN/PARAMETERFILE/:
PARAMETERFILE    UNPROT  COARSE   MAY 26 22:00:00  Y           512       17         8.5K           1M  SPFILE.311.990946061

+ORCL_SID_DATA/ORCL_SID_PHX1RAC/TEMPFILE/:
TEMPFILE         UNPROT  COARSE   MAR 11 16:00:00  Y            8K  2297601        17.5G        17.5G  FMWEURPD12_IAS_TEMP.317.991419415
TEMPFILE         UNPROT  COARSE   MAR 11 16:00:00  Y            8K  1310721          10G          10G  NA_ARCHIVE_TEMP.322.1007140587
TEMPFILE         UNPROT  COARSE   MAR 11 16:00:00  Y            8K  4194177          32G          32G  TEMP.315.991419415
DATAGUARDCONFIG  UNPROT  COARSE   MAY 19 03:00:00  N            4K        3          12K           1M  dr1ORCL_SID.dat => +ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAGUARDCONFIG/ORCL_SID_PHX1RAC.312.990949397
CONTROLFILE      UNPROT  FINE     MAY 26 22:00:00  N           16K     2635        41.2M          48M  snapcf_ORCL_SID.f => +ORCL_SID_DATA/ORCL_SID_PHX1RAC/CONTROLFILE/Backup.313.990988403
PARAMETERFILE    UNPROT  COARSE   MAY 26 22:00:00  N           512       17         8.5K           1M  spfileORCL_SID.ora => +ORCL_SID_DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.311.990946061

================================================================================

$ asmcmd ls -lsh ORCL_SID_DATA/*/*/*
Type             Redund  Striped  Time             Sys  Block_Size   Blocks        Bytes        Space  Name
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  AUDIT_TBS.264.996655365
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   131073           1G           1G  AUDIT_TBS.319.996656095
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    85625       668.9M         670M  AVAIL.277.990939537
CONTROLFILE      UNPROT  FINE     MAY 26 22:00:00  Y           16K     2635        41.2M          48M  Backup.313.990988403
CONTROLFILE      UNPROT  FINE     OCT 31 2018      Y            2K        1           2K           8M  Current.257.990939051
CONTROLFILE      UNPROT  FINE     OCT 31 2018      Y            2K        1           2K           8M  Current.258.990939051
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  EUR_ORDER.271.990939353
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    38401         300M         302M  EUR_ORDER.316.992686897
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1347073        10.3G        10.3G  EUR_PDA.321.1007139241
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   466433         3.6G         3.6G  EZFETCH_IE.270.990939353
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4091905        31.2G        31.3G  EZFETCH_UK.269.990939199
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   847873         6.5G         6.5G  EZFETCH_UK.279.990939547
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    12801         100M         102M  FMWEURPD12_ESS.284.990939589
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    15361         120M         122M  FMWEURPD12_IAS_OPSS.285.990939591
TEMPFILE         UNPROT  COARSE   MAR 11 16:00:00  Y            8K  2297601        17.5G        17.5G  FMWEURPD12_IAS_TEMP.317.991419415
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    85761         670M         672M  FMWEURPD12_IAS_UMS.287.990939593
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K     7681          60M          62M  FMWEURPD12_IAU.289.990939595
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K    89601         700M         702M  FMWEURPD12_MDS.282.990939585
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  FMWEURPD12_SOAINFRA.290.1010082787
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  FMWEURPD12_SOAINFRA.292.1010082785
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  FMWEURPD12_SOAINFRA.314.1010082781
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  FMWEURPD12_SOAINFRA.318.1010082769
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1628673        12.4G        12.4G  FMWEURPD12_SOAINFRA.326.1013130689
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K     1281          10M          11M  FMWEURPD12_STB.288.990939595
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K     7681          60M          62M  FMWEURPD12_WLS.286.990939591
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1572865          12G          12G  IFW_DT_TBS.280.990939551
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   131073           1G           1G  IFW_IDX_TBS.281.990939585
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194303          32G          32G  KOMPASS.274.990939395
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1663489        12.7G        12.7G  KOMPASS.324.1028374119
DATAGUARDCONFIG  UNPROT  COARSE   MAY 19 03:00:00  Y            4K        3          12K           1M  MBEURINP_SPMBRAC.312.990949397
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3072001        23.4G        23.5G  MBSYNC_UK_MCD.291.990939597
TEMPFILE         UNPROT  COARSE   MAR 11 16:00:00  Y            8K  1310721          10G          10G  NA_ARCHIVE_TEMP.322.1007140587
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194303          32G          32G  PDA_STG.267.990939163
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4099073        31.3G        31.3G  PDA_STG.278.990939539
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   745473         5.7G         5.7G  PDA_STG.325.1014480263
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   361473         2.8G         2.8G  PERFSTAT.320.1001228979
PARAMETERFILE    UNPROT  COARSE   MAY 26 22:00:00  Y           512       17         8.5K           1M  SPFILE.311.990946061
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4192001          32G          32G  SYSAUX.260.990939059
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1101825         8.4G         8.4G  SYSAUX.268.990939163
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3932161          30G          30G  SYSAUX.275.990939395
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  1464321        11.2G        11.2G  SYSTEM.259.990939059
TEMPFILE         UNPROT  COARSE   MAR 11 16:00:00  Y            8K  4194177          32G          32G  TEMP.315.991419415
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194303          32G          32G  UNDOTBS1.261.990939059
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   524289           4G           4G  UNDOTBS1.283.1041158311
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194303          32G          32G  UNDOTBS2.263.990939063
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  4194177          32G          32G  UNDOTBS2.323.1041061103
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K      801         6.3M           7M  USERS.262.990939059
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K  3327489        25.4G        25.4G  WEBMDATA.272.990939357
DATAFILE         UNPROT  COARSE   MAY 26 01:00:00  Y            8K   131073           1G           1G  WEBMINDX.273.990939393
CONTROLFILE      UNPROT  FINE     MAR 11 16:00:00  Y           16K     2635        41.2M          48M  current.256.990939051
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_1.293.990939821
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_10.300.990939985
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_11.301.990940017
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_12.302.990940049
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_13.303.990940081
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_14.304.990940083
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_2.294.990939853
ONLINELOG        UNPROT  COARSE   MAY 26 21:00:00  Y           512  2097153           1G           1G  group_21.305.990940085
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_22.306.990940115
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_23.307.990940179
ONLINELOG        UNPROT  COARSE   MAY 26 20:00:00  Y           512  2097153           1G           1G  group_24.308.990940241
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_25.309.990940273
ONLINELOG        UNPROT  COARSE   MAY 26 20:00:00  Y           512  2097153           1G           1G  group_26.310.990940273
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_3.295.990939915
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_4.296.990939917
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_5.297.990939919
ONLINELOG        UNPROT  COARSE   MAY 26 22:00:00  Y           512  2097153           1G           1G  group_6.298.990939951
ONLINELOG        UNPROT  COARSE   MAY 26 21:00:00  Y           512  2097153           1G           1G  group_9.299.990939953
__ATA.cmd.push(function() { __ATA.initDynamicSlot({ id: 'atatags-26942-5ecded202cec1', location: 120, formFactor: '001', label: { text: 'Advertisements', }, creative: { reportAd: { text: 'Report this ad', }, privacySettings: { text: 'Privacy settings', } } }); });

Machine Learning for Oracle Professionals

Gerger Consulting - Tue, 2020-05-26 17:00

In this webinar, Oracle ACE Director Craig Shallahamer will introduce you to the world of applied Machine Learning from an Oracle Professional (DBA/Developer/Manager) perspective. This includes understanding what ML is, why use it and why now.

He will demonstrate how to create an automated anomalous performance detection system. He'll use industry standard Python with its ML libraries and Jupyter Notebooks. You will be able to download and do everything he does in this webinar!


If you have ever wondered how ML can be applied in an IT environment, you don't want to miss this webinar.



Categories: Development

automatic partition naming

Tom Kyte - Tue, 2020-05-26 16:26
hi , is there any way to give a patern name to automatic partitions when using this DBMS_REDEFINITION.start_redef_table package. thank you by advance.
Categories: DBA Blogs

Assigning values into table type variable within loop results in error - PLS-00382: expression is of wrong type

Tom Kyte - Tue, 2020-05-26 16:26
Please refer the script I attached with this question - There are records which are supposed to be stored in table type variable as a result of cursor loop on table - test_mgr_hierar5. The table type is expected to hold values - 177603,157564,15...
Categories: DBA Blogs

ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind

Tom Kyte - Tue, 2020-05-26 16:26
This is just weird. It works fine on 12.1. On version 19 it requires the "workaround" used in foo3.
Categories: DBA Blogs

ORA-02298: cannot validate (APEX_200100.WWV_FLOW_DATA_SESSION_FK) - parent keys not found

Tom Kyte - Tue, 2020-05-26 16:26
Hi, Today I upgraded one of our Oracle APEX environment from 19.2 to 20.1. The upgrade was successful except that I found the following error in the installation log: <code># Error: ORA-02298: cannot validate (APEX_200100.WWV_FLOW_DATA_SESSION_F...
Categories: DBA Blogs

Formating SQL commands output from within RMAN

Tom Kyte - Tue, 2020-05-26 16:26
Is it possible to format SQL interface commands output in RMAN .. at least some basic like >linesize< ? Regards Dejan
Categories: DBA Blogs

Create MVW takes 15 mins. Refresh of same MVW takes 12 hours (or mostly failes)

Tom Kyte - Tue, 2020-05-26 16:26
We have a materialized view, which has undergone some tuning. When creating the new MVW, it took 15 mins. The MVW is then refreshed using a shell in which many MVWs are refreshed in order. As part of the refresh, to make it faster, the indexes on ...
Categories: DBA Blogs

Explain conversion rules used in NVL2

Tom Kyte - Tue, 2020-05-26 16:26
Please explain the conversion rules used for this SQL statement (we found some rules https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/NVL2.html#GUID-414D6E81-9627-4163-8AC2-BD24E57742AE but could not apply in this case): <code...
Categories: DBA Blogs

Oracle Cloud & Third party tools

Syed Jaffar - Tue, 2020-05-26 11:55
There have been several buzz words and offerings since the invention of Cloud concepts. We have been hearing of Private Cloud, Public Cloud, Hybrid cloud and more recently multi-clouds concepts and offering from many leading cloud market players.  All most all leading market players are offering several tools and solutions to support seamless administration, maintenance and operations tasks.

Despite many OCI offerings, still we sometimes might need third-party tools or solutions to achieve some of the goals to move or manage the cloud platforms. So, I gonna walk through some of the third-party tools/solutions that are certified and supported OCI operations.

  • According to Commvault, over 80% of companies are using the multi-cloud today. Commvault Cloud Backup solution provides support backup and recovery for  over 40% clouds. This can ease backup and recovery operations between clouds.
  • Vertitas's Netbackup 7.7.x supports backup and recovery operations on Oracle Cloud as well as supports multi-cloud data protection solutions. With NetBackup multi-cloud data protection, you can achieve seamless deduplication between data centers and multi-clouds.  
  • Using Cyberduck version 6.4.0 or higher, you can connect to OCI object storage
  • With dbm cloud systems, you can easy move data and VMs from Classic to OCI, migrate & replicate data from on-premise to OCI and move data stored from any cloud vendor into OCI.
  • VictorOps with OCI integration can greatly assist in cloud infrastructure and application monitoring, as well as real-time incident response will improve significantly. 
  • ZeroDown software provides HA option for Oracle cloud infrastructure. 
  • Coriolis is the simplest way to migrate your windows or Linux VMs alongside their underlying storage and networking configuration across cloud platforms.

There are many other third-party useful solutions available in the market. This is a just tiny list of useful third-party solutions on OCI.

[AZ-900] Microsoft Azure Fundamentals Certification Exam: Everything You Must Know

Online Apps DBA - Tue, 2020-05-26 08:43

Exam AZ-900: Microsoft Azure Fundamentals tests the candidate one on foundational level knowledge of Cloud concepts & Azure services. The exam is very beneficial for anyone who’d like to start his/her Azure Cloud journey. Want to know about [AZ-900] Microsoft Azure Fundamentals Certification Exam, its eligibility, benefits & the topics you should learn? If YES, […]

The post [AZ-900] Microsoft Azure Fundamentals Certification Exam: Everything You Must Know appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

How to use DBMS_SCHEDULER to improve performance ?

Yann Neuhaus - Sun, 2020-05-24 18:28

From an application point of view, the oracle scheduler DBMS_SCHEDULER allows to reach best performance by parallelizing your process.

Let’s start with the following PL/SQL code inserting in serial several rows from a metadata table to a target table. In my example, the metadata table does not contain “directly” the data but a set a of sql statement to be executed and for which the rows returned must be inserted into the target table My_Target_Table_Serial :

Let’s verify the contents of the source table called My_Metadata_Table:

SQL> SELECT priority,dwh_id, amq_name, sql_statement,scope from dwh_amq_v2;
ROWNUM  DWH_ID  AMQ_NAME SQL_STATEMENT          SCOPE
1	7	AAA1	 SELECT SUM(P.age pt.p	TYPE1
2	28	BBB2  	 SELECT CASE WHEN pt.p	TYPE1
3	37	CCC3	 "select cm.case_id fr"	TYPE2
4	48	DDD4	 "select cm.case_id fr"	TYPE2
5	73	EEE5	 SELECT DISTINCT pt.p	TYPE1
6	90	FFF6 	 SELECT LAG(ORW pt.p	TYPE1
7	114	GGG7	 SELECT distinct pt.	TYPE1
8	125	HHH8	 SELECT DISTINCT pt.p	TYPE1
...
148    115     ZZZ48    SELECT ROUND(TO_NUMBER TYPE2

Now let’s check the PL/SQL program :

DECLARE
  l_errm VARCHAR2(200);
  l_sql  VARCHAR2(32767) := NULL;
  sql_statement_1  VARCHAR2(32767) := NULL;
  sql_statement_2  VARCHAR2(32767) := NULL;
  l_amq_name VARCHAR2(200);
  l_date NUMBER;
BEGIN
  SELECT TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')) INTO l_date FROM dual;
  FOR rec IN (SELECT dwh_id, amq_name, sql_statement,scope 
                FROM My_Metadata_Table,
                     (SELECT dwh_pit_date FROM dwh_code_mv) pt
               WHERE dwh_status = 1
                 AND (pt.dwh_pit_date >= dwh_valid_from AND pt.dwh_pit_date < dwh_valid_to) 
               ORDER BY priority, dwh_id) LOOP
    ...
    sql_statement_1 := substr(rec.sql_statement, 1, 32000);
    sql_statement_2 := substr(rec.sql_statement, 32001);
    IF rec.SCOPE = 'TYPE1' THEN 
      -- TYPE1 LEVEL SELECT
      l_sql := 'INSERT /*+ APPEND */ INTO My_Target_Table_Serial (dwh_pit_date, AMQ_ID, AMQ_TEXT, CASE_ID, ENTERPRISE_ID)'||CHR(13)|| 'SELECT DISTINCT TO_DATE(code.dwh_pit_date, ''YYYYMMDDHH24MISS''),'||rec.dwh_id|| ',''' ||rec.amq_name ||''', case_id, 1'||CHR(13)
      || ' FROM (SELECT dwh_pit_date FROM dwh_code) code, ('||sql_statement_1;
      EXECUTE IMMEDIATE l_sql || sql_statement_2 || ')';
      COMMIT;    
    ELSE 
      -- TYPE2 LEVEL SELECT
      l_sql :=  'INSERT /*+ APPEND */ INTO My_Target_Table_Serial (dwh_pit_date, AMQ_ID, AMQ_TEXT, CASE_ID, ENTERPRISE_ID)
      SELECT DISTINCT TO_DATE(code.dwh_pit_date, ''YYYYMMDDHH24MISS''), '||rec.dwh_id|| ',''' ||rec.amq_name || ''', cm.case_id, cm.enterprise_id'||CHR(13)
      || '  FROM (SELECT dwh_pit_date FROM dwh_code) code, v_sc_case_master cm, v_sc_case_event ce, ('||sql_statement_1;
              
      EXECUTE IMMEDIATE l_sql || sql_statement_2 || ') pt'||CHR(13)
      || ' WHERE cm.case_id = ce.case_id'||CHR(13) 
      || '   AND cm.deleted IS NULL AND cm.state_id <> 1'||CHR(13)
      || '   AND ce.deleted IS NULL AND ce.pref_term = pt.pt_name';
      COMMIT;         
    END IF;
    ...
   END LOOP:
END;
Number of Rows Read : 148 (Means 148 Sql Statement to execute)
START : 16:17:46
END : 16:57:42
Total :  40 mins

 

As we can see, each Sql Statement is executed in serial, let’s check the audit table recording the loading time (Insert Time) and the “scheduling”   :

CREATE_DATE		NAME	START_DATE		END_DATE            LOADING_TIME
22.05.2020 16:46:34	AAA1	22.05.2020 16:46:34	22.05.2020 16:57:42    11.08mins
22.05.2020 16:42:05	BBB2	22.05.2020 16:42:05	22.05.2020 16:46:34    04.29mins
22.05.2020 16:41:15	CCC3	22.05.2020 16:41:15	22.05.2020 16:42:05    50sec
22.05.2020 16:40:42	DDD4	22.05.2020 16:40:42	22.05.2020 16:41:15    32sec
22.05.2020 16:40:20	EEE5	22.05.2020 16:40:20	22.05.2020 16:40:42    22sec
22.05.2020 16:37:23	FFF6	22.05.2020 16:37:23	22.05.2020 16:40:20    02.57mins
22.05.2020 16:37:12	GGG7	22.05.2020 16:37:12	22.05.2020 16:37:23    11sec
...
22.05.2020 16:36:03	ZZZ148	22.05.2020 16:17:35	22.05.2020 16:17:46    11sec

To resume :

  • The 148 rows (148 Sql Statement) coming from the source table are loaded in serial in 40mins.
  • The majority of rows have taken less than 01 min to load (Ex. : Name = CCC3,DDD4,EEE5,GGG7 and ZZZ148)
  • Few rows have taken more than a couple of minutes to load.
  • The maximum loading time is 11.08mins for the Name “AA1”.
  • Each row must wait the previous row complete his loading before to start his loading (compare END_DATE previous vs START_DATE current).

To optimize the process, let’s trying to load all the rows coming from the source table in parallel by using the oracle scheduler DBMS_SCHEDULER.

Instead to execute directly the Insert command in the loop, let’s create a job through DBMS_SCHEDULER:

FOR rec IN (SELECT priority,dwh_id, amq_name, sql_statement,scope 
                FROM My_Metadata_Table,
                     (SELECT dwh_pit_date FROM dwh_code_mv) pt
               WHERE dwh_status = 1
                 AND (pt.dwh_pit_date >= dwh_valid_from AND pt.dwh_pit_date < dwh_valid_to) 
               ORDER BY priority, dwh_id) LOOP

     l_amq_name := rec.amq_name;
       IF rec.SCOPE = 'TYPE1' THEN 
        -- TYPE1 LEVEL SELECT
         ...
  
            --Execute Job to insert the AMQ : Background process
            DBMS_SCHEDULER.CREATE_JOB (
            job_name             => 'AMQ_P'||rec.priority||'j'||i||'_'||l_date,
            job_type             => 'PLSQL_BLOCK',
            job_action           => 'BEGIN
                                      LOAD_DATA(''CASE'','||''''||l_amq_name||''''||','||rec.priority||','||l_date||','||v_SESSION_ID||','||i||');
                                     END;',
            start_date    =>  sysdate,  
            enabled       =>  TRUE,  
            auto_drop     =>  TRUE,  
            comments      =>  'job for amq '||l_amq_name);
          END IF;
        ELSE 
            ...
            END IF;
        END IF; 
      i := i +1;
  END LOOP;
Number of Rows Read : 148 (Means 148 Sql Statement to execute)
START : 08:14:03
END : 08:42:32
Total :  27.57 mins

To resume :

  • The 148 rows (148 Sql Statement) coming from the source table are loaded now in parallel in 27.57mins instead of 40mins in serial.
  • The options of DBMS_SCHEDULER are  :
    • As we are limited in number of character for the parameter “job_action”, we have to insert the data through a PL/SQL procedure LOAD_DATA.
    • The job is executed immediately (start_date=sysdate) and purged immediately after his execution (auto_drop=TRUE).

Let’s check now how the jobs are scheduled. Since we do a loop of 148 times, I expect to have 148 jobs:

First, let’s check now if the rows (Remember, One Row = One Insert Into Target Table From Source Table) are loaded in parallel :

CREATE_DATE 	    NAME START_DATE 	        END_DATE 				       
22.05.2020 16:46:34 AAA1 23.05.2020 08:14:04	23.05.2020 08:21:19
22.05.2020 16:42:05 BBB2 23.05.2020 08:14:04	23.05.2020 08:20:43
22.05.2020 16:41:15 CCC3 23.05.2020 08:14:04	23.05.2020 08:21:59
22.05.2020 16:40:42 DDD4 23.05.2020 08:14:03	23.05.2020 08:15:29
22.05.2020 16:40:20 EEE5 23.05.2020 08:14:03	23.05.2020 08:15:05
22.05.2020 16:37:23 FFF6 23.05.2020 08:14:03	23.05.2020 08:14:47
22.05.2020 16:37:12 GGG7 23.05.2020 08:14:03	23.05.2020 08:15:59
...                     
22.05.2020 16:36:03 ZZZ148 22.05.2020 16:17:35 22.05.2020 16:17:46

This is the case, all rows have the same start_date, meaning all rows start in parallel. Let’s verify into “all_scheduler_job_run_details” to check we have our 148 jobs in parallel :

SQL> select count(*) from all_scheduler_job_run_details where job_name like '%20200523081403';

  COUNT(*)
----------
       148
SQL> select log_date,job_name,status,req_start_date from all_scheduler_job_run_details where job_name like '%20200523081403';
LOG_DATE		JOB_NAME		        STATUS		REQ_START_DATE
23-MAY-20 08.42.41	AMQ_P3J147_20200523081403	SUCCEEDED	23-MAY-20 02.42.32
23-MAY-20 08.42.32	AMQ_P2J146_20200523081403	SUCCEEDED	23-MAY-20 02.23.13
23-MAY-20 08.37.56	AMQ_P2J145_20200523081403	SUCCEEDED	23-MAY-20 02.23.13
23-MAY-20 08.37.33	AMQ_P2J144_20200523081403	SUCCEEDED	23-MAY-20 02.23.13
23-MAY-20 08.37.22	AMQ_P2J143_20200523081403	SUCCEEDED	23-MAY-20 02.23.13
23-MAY-20 08.37.03	AMQ_P2J141_20200523081403	SUCCEEDED	23-MAY-20 02.23.13
23-MAY-20 08.36.50	AMQ_P2J142_20200523081403	SUCCEEDED	23-MAY-20 02.23.13
23-MAY-20 08.33.57	AMQ_P2J140_20200523081403	SUCCEEDED	23-MAY-20 02.23.13
--Only the first 8 rows are displayed

To resume :

  • We have 148 jobs all started, most of the time in parallel (job with same REQ_START_DATE, oracle parallelizes jobs per block randomly).
  • My PL/SQL process now took 27.57 mins instead of 40mins.

But if we have a look in details, we have a lot of small jobs. Those are jobs where run_duration is less than 01 mins:

SQL> select run_duration from all_scheduler_job_run_details where job_name like '%20200523081403' order by run_duration;

RUN_DURATION
+00 00:00:04.000000
+00 00:00:07.000000
+00 00:00:09.000000
+00 00:00:10.000000
+00 00:00:13.000000
+00 00:00:15.000000
+00 00:00:20.000000
+00 00:00:27.000000
+00 00:00:33.000000
+00 00:00:35.000000
+00 00:00:36.000000
+00 00:00:38.000000
+00 00:00:43.000000
+00 00:00:46.000000
+00 00:00:51.000000
+00 00:00:52.000000

As we have a lot of small jobs (short-lived jobs), it will be more interesting to use lightweight jobs instead of regular jobs.

In contrary of regular jobs, lightweight jobs :

  • Require less meta data, so they have quicker create and drop times.
  • Suited for short-lived jobs (small jobs, jobs where run_duration is low).

Let’s rewrite our PL/SQL process using lightweight jobs :

To use lightweight jobs, first create a program suitable for a lightweight job :

begin
dbms_scheduler.create_program
(
    program_name=>'LIGHTWEIGHT_PROGRAM',
    program_action=>'LOAD_AMQ',
    program_type=>'STORED_PROCEDURE',
    number_of_arguments=>6, 
    enabled=>FALSE);
END;

Add the arguments (parameters) and enable the program :

BEGIN
dbms_scheduler.DEFINE_PROGRAM_ARGUMENT(
program_name=>'lightweight_program',
argument_position=>1,
argument_type=>'VARCHAR2',
DEFAULT_VALUE=>NULL);

dbms_scheduler.DEFINE_PROGRAM_ARGUMENT(
program_name=>'lightweight_program',
argument_position=>2,
argument_type=>'VARCHAR2');

dbms_scheduler.DEFINE_PROGRAM_ARGUMENT(
program_name=>'lightweight_program',
argument_position=>3,
argument_type=>'NUMBER');

dbms_scheduler.DEFINE_PROGRAM_ARGUMENT(
program_name=>'lightweight_program',
argument_position=>4,
argument_type=>'NUMBER');

dbms_scheduler.DEFINE_PROGRAM_ARGUMENT(
program_name=>'lightweight_program',
argument_position=>5,
argument_type=>'VARCHAR');

dbms_scheduler.DEFINE_PROGRAM_ARGUMENT(
program_name=>'lightweight_program',
argument_position=>6,
argument_type=>'NUMBER');

dbms_scheduler.enable('lightweight_program');  
end;

Into the PL/SQL code, let’s create the lightweight job without forget to set the argument value before running the job:

DECLARE
...
BEGIN
....
LOOP
DBMS_SCHEDULER.create_job (
job_name        => 'AMQ_P'||rec.priority||'j'||i||'_'||l_date,
program_name    => 'LIGHTWEIGHT_PROGRAM',
job_style       => 'LIGHTWEIGHT',
enabled         => FALSE);
                  
 DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
   job_name                => 'AMQ_P'||rec.priority||'j'||i||'_'||l_date,
   argument_position       => 1,
   argument_value          => rec.scope);
   
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
   job_name                => 'AMQ_P'||rec.priority||'j'||i||'_'||l_date,
   argument_position       => 2,
   argument_value          => l_amq_name);
   
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
   job_name                => 'AMQ_P'||rec.priority||'j'||i||'_'||l_date,
   argument_position       => 3,
   argument_value          => rec.priority);

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
   job_name                => 'AMQ_P'||rec.priority||'j'||i||'_'||l_date,
   argument_position       => 4,
   argument_value          => l_date);   

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
   job_name                => 'AMQ_P'||rec.priority||'j'||i||'_'||l_date,
   argument_position       => 5,
   argument_value          => v_SESSION_ID);  

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
   job_name                => 'AMQ_P'||rec.priority||'j'||i||'_'||l_date,
   argument_position       => 6,
   argument_value          => i); 

dbms_scheduler.run_job('AMQ_P'||rec.priority||'j'||i||'_'||l_date,TRUE);
...
END LOOP;
Number of Rows Read : 148 (Means 148 Sql Statement to execute) 
START : 18:08:56
END : 18:27:40
Total : 18.84 mins

 

Let’s check we have always 148 jobs in parallel :

SQL> select count(*) from all_scheduler_job_run_details where job_name like '%20200524175036';

  COUNT(*)
----------
       148
SQL> select log_date,job_name,status,req_start_date from all_scheduler_job_run_details where job_name like '%20200524175036';

LOG_DATE           JOB_NAME     STATUS	        REQ_START_DATE
24-MAY-20 05.50.51 AB1C		SUCCEEDED	24-MAY-20 05.50.36
24-MAY-20 05.50.56 AB1D		SUCCEEDED	24-MAY-20 05.50.51
24-MAY-20 05.51.14 AB1E		SUCCEEDED	24-MAY-20 05.50.56
24-MAY-20 05.51.49 AB1I		SUCCEEDED	24-MAY-20 05.51.14
24-MAY-20 05.52.14 AB1P		SUCCEEDED	24-MAY-20 05.51.49
24-MAY-20 05.52.34 AB1L		SUCCEEDED	24-MAY-20 05.52.14
24-MAY-20 05.52.55 AB1N		SUCCEEDED	24-MAY-20 05.52.34
24-MAY-20 05.53.17 AB1M		SUCCEEDED	24-MAY-20 05.52.55
24-MAY-20 05.53.29 AB1K		SUCCEEDED	24-MAY-20 05.53.17
24-MAY-20 05.53.39 AB1O		SUCCEEDED	24-MAY-20 05.53.29
24-MAY-20 05.53.57 AB1U		SUCCEEDED	24-MAY-20 05.53.39
24-MAY-20 05.54.07 AB1V		SUCCEEDED	24-MAY-20 05.53.57

To resume :

  • We have 148 jobs all started, most of the time in parallel.
  • My PL/SQL process now took 18.54 mins (Lightweight Jobs) instead of 27.57mins (Regular Jobs).
  • If we compare Regular Jobs VS Lightweight Jobs, the former seems to schedule the jobs randomly (start jobs with block of 4,5,6…8) while the last one schedule jobs by block of 3 or 4 (as we can see above).

Conclusion :

  • DBMS_SCHEDULER (Regular Jobs or Lightweight Jobs) can improve significantly your PL/SQL performance transforming transforming your serial process in parallel process.
  • If you have small jobs (short lived-jobs), use lightweight jobs instead regular jobs.
  • Don’t underestimate the development time (development, test, bug solving) to transform your serial process to parallel process. Create 1 job is different to create more than 100 or 1000 jobs through a PL/SQL loop (concurrency problem, CPU used by create/drop the jobs).
  • As developer, you are responsible to manage your jobs (create,drop,purge) in order to not fill the oracle parameter job_queue_processes (used by a lot of critical oracle processes).

Cet article How to use DBMS_SCHEDULER to improve performance ? est apparu en premier sur Blog dbi services.

Certified Kubernetes Administrator (CKA) Certification Exam

Online Apps DBA - Sat, 2020-05-23 07:25

Certified Kubernetes Administrator (CKA) is designed for who is new to Kubernetes and want to learn administration on Kubernetes. Comment: 1. If you are a Certified Kubernetes Administrator (CKA) Certified. Comment: 2. If you are not a Certified Kubernetes Administrator (CKA) Certified but want to become a (CKA). To know more, check out K21 Academy […]

The post Certified Kubernetes Administrator (CKA) Certification Exam appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Pages

Subscribe to Oracle FAQ aggregator