DBA Blogs

ORA-06553: PLS-306: wrong number or types of arguments when selecting object type instance

Tom Kyte - Tue, 2020-02-11 18:04
We're experiencing this when testing upgrade from Ora 12.1 to 19. What's wrong with the code?
Categories: DBA Blogs

ORA-30926 not raised in merge statement for non-deterministic set of rows

Tom Kyte - Tue, 2020-02-11 18:04
Hi Connor, Chris et al, Could you please kindly help me better understand when ORA-30926 is to be raised for non-deterministic set of rows on input? Up to 12.1 it worked as a charm , regardless of sort order and number duplicate rows on input O...
Categories: DBA Blogs

Oracle Sequence expiry

Tom Kyte - Tue, 2020-02-11 18:04
I have few sequences which are about to expires and I need notification over emails when last_number increases. Can you suggest how to do it.
Categories: DBA Blogs

Labs for Multitenant

Tom Kyte - Tue, 2020-02-11 18:04
Hi All: I have a question on the Multitenant Fundamentals hands on session scheduled for Thu, Feb 13, 2020 16:00-17:00 UTC: Will these labs be making use of VMs based on https://cloudmarketplace.oracle.com/marketplace/listing/69658839? * I'm a...
Categories: DBA Blogs

Scheduling Queries

Tom Kyte - Tue, 2020-02-11 18:04
Hi Connor, Chris, Could you please have a look at below scenario related to dbms_scheduler program and job setu: <code> -------------- Start Use Case Setup -------------- -----------------------------------------------------------------------...
Categories: DBA Blogs

Truncate Partition is very slow

Tom Kyte - Tue, 2020-02-11 18:04
Hi Tom We got a table which is partitioned on Range (date), Each partition holds approximately 5 Million records, there are 60 partitions. There are 4 Indexes on this table and they are Global Indexes. To remove old data we truncate partition. ...
Categories: DBA Blogs

Oracle Entity Framework Core 3.x

Tom Kyte - Tue, 2020-02-11 18:04
When will the https://www.nuget.org/packages/Oracle.EntityFrameworkCore/ support dotnet core sdk 3.1 ?
Categories: DBA Blogs

Python 3.8.1 Linux Install Without Root

Bobby Durrett's DBA Blog - Tue, 2020-02-11 11:40

I wanted to install the latest Python on Linux in a low powered user’s home directory and not have the install interfere with the Python that comes with Linux. I wanted my own local copy of Python that I could install Python packages to without interfering with anything else. I was willing to use root to install the needed Linux packages, but everything else was done with my low powered user.

I did a yum update on my Oracle Enterprise Linux 7.7 install before I started to get everything on the latest version. As root I used yum to install the Linux packages I needed to install Python 3.8.1 from source:

[root@pythonvm ~]# yum install gcc openssl-devel bzip2-devel libffi-devel tcl* tk* -y
Loaded plugins: langpacks, ulninfo
mysql-connectors-community                                                   | 2.5 kB  00:00:00
mysql-tools-community                                                        | 2.5 kB  00:00:00
mysql57-community                                                            | 2.5 kB  00:00:00
ol7_UEKR4                                                                    | 2.5 kB  00:00:00
ol7_latest                                                                   | 2.7 kB  00:00:00
Package gcc-4.8.5-39.0.3.el7.x86_64 already installed and latest version
Package 1:openssl-devel-1.0.2k-19.0.1.el7.x86_64 already installed and latest version
Package bzip2-devel-1.0.6-13.el7.x86_64 already installed and latest version
Package libffi-devel-3.0.13-18.el7.x86_64 already installed and latest version
Package 1:tcl-devel-8.5.13-8.el7.x86_64 already installed and latest version
Package 1:tcl-8.5.13-8.el7.x86_64 already installed and latest version
Package tcl-pgtcl-2.0.0-5.el7.x86_64 already installed and latest version
Package 1:tk-devel-8.5.13-6.el7.x86_64 already installed and latest version
Package 1:tk-8.5.13-6.el7.x86_64 already installed and latest version
Nothing to do

I must have already installed these on this VM. I did the rest of these steps as my low powered user “bobby”. The next thing I did was download the latest Python source, the 3.8.1 version. I had to use the -k option to get around certificate issues:

[bobby@pythonvm ~]$ curl -k -O https://www.python.org/ftp/python/3.8.1/Python-3.8.1.tgz
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 22.8M  100 22.8M    0     0  7830k      0  0:00:02  0:00:02 --:--:-- 7828k

For some reason I had to do this twice. The first download failed. Next I gunzipped and untarred this archive under my home directory:

tar zxfv Python-3.8.1.tgz

Python-3.8.1/Objects/sliceobject.c
Python-3.8.1/Objects/listobject.c
Python-3.8.1/Objects/typeslots.inc
Python-3.8.1/Objects/weakrefobject.c
Python-3.8.1/Objects/unicodeobject.c
Python-3.8.1/Objects/complexobject.c
Python-3.8.1/Objects/picklebufobject.c
Python-3.8.1/Objects/odictobject.c
Python-3.8.1/Objects/genobject.c
[bobby@pythonvm ~]$

I created a directory called “python” under my home directory to use as the top-level directory for my Python install. This directory tree will hold the binaries and any Python packages that I install.

[bobby@pythonvm ~]$ mkdir python

After changing directory to where I untarred the source files I configured the Python make and install to use the directory I just created as a “prefix”:


[bobby@pythonvm ~]$ cd Python-3.8.1

[bobby@pythonvm Python-3.8.1]$ pwd
/home/bobby/Python-3.8.1

./configure --prefix=/home/bobby/python

config.status: creating Misc/python-embed.pc
config.status: creating Misc/python-config.sh
config.status: creating Modules/ld_so_aix
config.status: creating pyconfig.h
creating Modules/Setup.local
creating Makefile


If you want a release build with all stable optimizations active (PGO, etc),
please run ./configure --enable-optimizations

Then I did the make and make altinstall from the same directory:

make
...
renaming build/scripts-3.8/idle3 to build/scripts-3.8/idle3.8
renaming build/scripts-3.8/2to3 to build/scripts-3.8/2to3-3.8
/usr/bin/install -c -m 644 ./Tools/gdb/libpython.py python-gdb.py
gcc -pthread -c -Wno-unused-result -Wsign-compare -DNDEBUG -g -fwrapv -O3 -Wall    -std=c99 -Wextra -Wno-unused-result -Wno-unused-parameter -Wno-missing-field-initializers -Werror=implicit-function-declaration  -I./Include/internal  -I. -I./Include    -DPy_BUILD_CORE -o Programs/_testembed.o ./Programs/_testembed.c
gcc -pthread     -Xlinker -export-dynamic -o Programs/_testembed Programs/_testembed.o libpython3.8.a -lcrypt -lpthread -ldl  -lutil -lm   -lm
sed -e "s,@EXENAME@,/home/bobby/python/bin/python3.8," < ./Misc/python-config.in >python-config.py
LC_ALL=C sed -e 's,\$(\([A-Za-z0-9_]*\)),\$\{\1\},g' < Misc/python-config.sh >python-config
[bobby@pythonvm Python-3.8.1]$

make altinstall
...
Looking in links: /tmp/tmpdrnzr3vb
Collecting setuptools
Collecting pip
Installing collected packages: setuptools, pip
Successfully installed pip-19.2.3 setuptools-41.2.0
[bobby@pythonvm Python-3.8.1]$

So, now Python 3.8.1 is installed in /home/bobby/python but I want to put the bin directory in the path so I can run python or pip and by default have them be this version. The install created python and pip as python3.8 and pip3.8 but I created links to them so that I could access them without 3.8 at the end of their names:

[bobby@pythonvm Python-3.8.1]$ cd /home/bobby/python/bin
[bobby@pythonvm bin]$ ls -al
total 16704
drwxr-xr-x. 2 bobby bobby     4096 Feb 10 15:36 .
drwxrwxr-x. 6 bobby bobby       52 Feb 10 15:36 ..
-rwxrwxr-x. 1 bobby bobby      109 Feb 10 15:36 2to3-3.8
-rwxrwxr-x. 1 bobby bobby      249 Feb 10 15:36 easy_install-3.8
-rwxrwxr-x. 1 bobby bobby      107 Feb 10 15:36 idle3.8
-rwxrwxr-x. 1 bobby bobby      231 Feb 10 15:36 pip3.8
-rwxrwxr-x. 1 bobby bobby       92 Feb 10 15:36 pydoc3.8
-rwxr-xr-x. 1 bobby bobby 17075312 Feb 10 15:35 python3.8
-rwxr-xr-x. 1 bobby bobby     3095 Feb 10 15:36 python3.8-config

[bobby@pythonvm bin]$ ln -s python3.8 python
[bobby@pythonvm bin]$ ln -s pip3.8 pip

Then I added/home/bobby/python/bin to the front of the path to keep these new python and pip links ahead of the python links or binaries in the rest of the path.

[bobby@pythonvm bin]$ cd
[bobby@pythonvm ~]$ vi .bashrc

export ORACLE_HOME=/home/oracle/app/oracle/product/12.1.0/client_1
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=/home/oracle/app/oracle/product/12.1.0/client_1/lib
export PATH=/home/bobby/python/bin:$PATH

~
~
~
~
~
~
".bashrc" 17L, 451C written     

I logged out and in as bobby and tried python and pip to see that they were the correct version:

[bobby@pythonvm ~]$ python
Python 3.8.1 (default, Feb 10 2020, 15:33:01)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-39.0.3)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>>
[bobby@pythonvm ~]$ pip list
Package    Version
---------- -------
pip        19.2.3
setuptools 41.2.0
WARNING: You are using pip version 19.2.3, however version 20.0.2 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.
[bobby@pythonvm ~]$   

Finally, I updated pip just to show that we can update something in this new install. Then I installed numpy to see if I could install a new package.

[bobby@pythonvm ~]$ pip install --upgrade pip
Collecting pip
  Downloading https://files.pythonhosted.org/packages/54/0c/d01aa759fdc501a58f431eb594a17495f15b88da142ce14b5845662c13f3/pip-20.0.2-py2.py3-none-any.whl (1.4MB)
     |¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦| 1.4MB 863kB/s
Installing collected packages: pip
  Found existing installation: pip 19.2.3
    Uninstalling pip-19.2.3:
      Successfully uninstalled pip-19.2.3
Successfully installed pip-20.0.2

[bobby@pythonvm ~]$ pip list
Package    Version
---------- -------
pip        20.0.2
setuptools 41.2.0

[bobby@pythonvm ~]$ pip install numpy
Collecting numpy
  Downloading numpy-1.18.1-cp38-cp38-manylinux1_x86_64.whl (20.6 MB)
     |¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦| 20.6 MB 62 kB/s
Installing collected packages: numpy
Successfully installed numpy-1.18.1
[bobby@pythonvm ~]$ pip list
Package    Version
---------- -------
numpy      1.18.1
pip        20.0.2
setuptools 41.2.0

This is a quick and dirty post, but I want to remember these steps for myself and it might be helpful for someone who uses Redhat/Centos/OEL.

Bobby

Categories: DBA Blogs

Basic Replication -- 13 : Some Interesting SYS tables

Hemant K Chitale - Sun, 2020-02-09 08:45
I found an interesting SQL in the AWR report from my previous blog post.

What do you think this SQL statement does ?

DELETE FROM SYS.MVREF$_STMT_STATS WHERE REFRESH_ID = :B2 AND MV_OBJ# = :B1

Here are some interesting objects (I don't know which Oracle release they started appearing in) :

SQL> l
1 select object_name, object_type
2 from dba_objects
3 where owner = 'SYS'
4 and object_name like 'MVREF$%'
5* order by 2,1
SQL> /

OBJECT_NAME OBJECT_TYPE
------------------------------ -----------------------
MVREF$_STATS_SEQ SEQUENCE
MVREF$_CHANGE_STATS TABLE
MVREF$_RUN_STATS TABLE
MVREF$_STATS TABLE
MVREF$_STATS_PARAMS TABLE
MVREF$_STATS_SYS_DEFAULTS TABLE
MVREF$_STMT_STATS TABLE

7 rows selected.

SQL>


Right now, the SYS.MVREF$_STMT_STATS table appears to be empty.
SQL> desc SYS.MVREF$_STMT_STATS
Name Null? Type
----------------------------------------- -------- ----------------------------
MV_OBJ# NOT NULL NUMBER
REFRESH_ID NOT NULL NUMBER
STEP NOT NULL NUMBER
SQLID NOT NULL VARCHAR2(14)
STMT NOT NULL CLOB
EXECUTION_TIME NOT NULL NUMBER
EXECUTION_PLAN SYS.XMLTYPE STORAGE BINARY

SQL>


It would be interesting to know how Oracle is using this and the other MVREF$% tables.
SYS.MVREF$_CHANGE_STATS obviously captures DML operations

This SYS.MVREF$_RUN_STATS captures the last refresh operation (*does it only capture the last operation ?*) And what does SYS.MVREF$_STATS capture :

SQL> l
1 select *
2 from SYS.MVREF$_RUN_STATS
3* where MVIEWS='"HEMANT"."MV_1"'
SQL> /

RUN_OWNER_USER# REFRESH_ID NUM_MVS_TOTAL NUM_MVS_CURRENT MVIEWS BASE_TABLES METHOD ROLLBACK P R PURGE_OPTION
--------------- ---------- ------------- --------------- ------------------ ------------ ------ -------- - - ------------
PARALLELISM HEAP_SIZE A N O NUMBER_OF_FAILURES START_TIME END_TIME ELAPSED_TIME LOG_SETUP_TIME
----------- ---------- - - - ------------------ -------------------------- -------------------------- ------------ --------------
LOG_PURGE_TIME C TXNFLAG ON_COMMIT_FLAG
-------------- - ---------- --------------
106 245 1 1 "HEMANT"."MV_1" Y N 1
0 0 Y N N 0 09-FEB-20 09.55.33.000000 09-FEB-20 09.55.49.000000 16 1
PM PM
9 Y 0 0


SQL>
SQL> l
1 select mviews, count(*) from sys.mvref$_run_Stats group by mviews
2* order by 1
SQL> /

MVIEWS COUNT(*)
------------------------------------------ ----------
"HEMANT"."MV_1" 1
"HEMANT"."MV_2" 8
"HEMANT"."MV_DEPT", "HEMANT"."MV_EMP" 1
"HEMANT"."MV_FAST_NOT_POSSIBLE" 1
"HEMANT"."MV_OF_SOURCE" 1
"HEMANT"."NEW_MV" 2
"HEMANT"."NEW_MV_2_1" 1
"HEMANT"."NEW_MV_2_2" 2
"HR"."HR_MV_ON_COMMIT" 1
"HR"."MY_LARGE_REPLICA" 1

10 rows selected.

SQL>
SQL> desc sys.mvref$_run_stats
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
RUN_OWNER_USER# NOT NULL NUMBER
REFRESH_ID NOT NULL NUMBER
NUM_MVS_TOTAL NOT NULL NUMBER
NUM_MVS_CURRENT NOT NULL NUMBER
MVIEWS VARCHAR2(4000)
BASE_TABLES VARCHAR2(4000)
METHOD VARCHAR2(4000)
ROLLBACK_SEG VARCHAR2(4000)
PUSH_DEFERRED_RPC CHAR(1)
REFRESH_AFTER_ERRORS CHAR(1)
PURGE_OPTION NUMBER
PARALLELISM NUMBER
HEAP_SIZE NUMBER
ATOMIC_REFRESH CHAR(1)
NESTED CHAR(1)
OUT_OF_PLACE CHAR(1)
NUMBER_OF_FAILURES NUMBER
START_TIME TIMESTAMP(6)
END_TIME TIMESTAMP(6)
ELAPSED_TIME NUMBER
LOG_SETUP_TIME NUMBER
LOG_PURGE_TIME NUMBER
COMPLETE_STATS_AVAILABLE CHAR(1)
TXNFLAG NUMBER
ON_COMMIT_FLAG NUMBER

SQL> desc sys.mvref$_stats
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
MV_OBJ# NOT NULL NUMBER
REFRESH_ID NOT NULL NUMBER
ATOMIC_REFRESH NOT NULL CHAR(1)
REFRESH_METHOD VARCHAR2(30)
REFRESH_OPTIMIZATIONS VARCHAR2(4000)
ADDITIONAL_EXECUTIONS VARCHAR2(4000)
START_TIME TIMESTAMP(6)
END_TIME TIMESTAMP(6)
ELAPSED_TIME NUMBER
LOG_SETUP_TIME NUMBER
LOG_PURGE_TIME NUMBER
INITIAL_NUM_ROWS NUMBER
FINAL_NUM_ROWS NUMBER
NUM_STEPS NUMBER
REFMET NUMBER
REFFLG NUMBER

SQL>
SQL> select mv_obj#, count(*)
2 from sys.mvref$_stats
3 group by mv_obj#
4 /

MV_OBJ# COUNT(*)
---------- ----------
73223 1
73170 1
73065 1
73244 1
73079 8
73094 1
73197 2
73113 2
73188 1
73167 1
73110 1

11 rows selected.

SQL>
SQL> desc sys.mvref$_stats_params
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
MV_OWNER NOT NULL VARCHAR2(128)
MV_NAME NOT NULL VARCHAR2(128)
COLLECTION_LEVEL NOT NULL NUMBER
RETENTION_PERIOD NOT NULL NUMBER

SQL> select count(*)
2 from sys.mvref$_stats_params;

COUNT(*)
----------
0

SQL> desc sys.mvref$_stats_sys_defaults
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
COLLECTION_LEVEL NOT NULL NUMBER
RETENTION_PERIOD NOT NULL NUMBER

SQL> select * from sys.mvref$_stats_sys_defaults
2 /

COLLECTION_LEVEL RETENTION_PERIOD
---------------- ----------------
1 31

SQL>



Oracle has been introducing some more "internal" tables to trace MView Refresh operations.


Categories: DBA Blogs

Basic Replication -- 12 : MV Refresh Captured in AWR

Hemant K Chitale - Sun, 2020-02-09 08:40
Building on the example of an Index having been created on a Materialized View  in my previous blog post in this series, I've captured some information from the AWR report in 19c when this code is executed :

SQL> delete source_table_1;

72454 rows deleted.

SQL> insert into source_table_1 select object_id, owner, object_name from source_table_2;

72366 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_mview.refresh('MV_OF_SOURCE');

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_mview.refresh('MV_1');

PL/SQL procedure successfully completed.


(Note that "MV_OF_SOURCE" is not dependent on SOURCE_TABLE_1 and as really had no rows to refresh, did not cause any load).

Some information in the AWR Report (note that this is 19.3) :

SQL ordered by Elapsed Time             DB/Inst: ORCLCDB/ORCLCDB  Snaps: 54-55
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
-> %Total - Elapsed Time as a percentage of Total DB time
-> %CPU - CPU Time as a percentage of Elapsed Time
-> %IO - User I/O Time as a percentage of Elapsed Time
-> Captured SQL account for 108.1% of Total DB Time (s): 30
-> Captured PL/SQL account for 85.2% of Total DB Time (s): 30

Elapsed Elapsed Time
Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
16.1 1 16.09 53.5 12.8 21.6 2uusn1kyhm9h8
Module: SQL*Plus
PDB: ORCLPDB1
BEGIN dbms_mview.refresh('MV_1'); END;

8.7 1 8.66 28.8 5.3 13.6 8chh7ksnytb52
PDB: ORCLPDB1
delete from "HEMANT"."MLOG$_SOURCE_TABLE_1" where snaptime$$ <= :1

4.5 1 4.55 15.1 17.3 75.6 57ctmbtabx1rw
Module: SQL*Plus
PDB: ORCLPDB1
BEGIN dbms_mview.refresh('MV_OF_SOURCE'); END;

4.0 1 3.96 13.2 37.2 26.1 dsyxhpb9annru
Module: SQL*Plus
PDB: ORCLPDB1
delete source_table_1

3.7 144,820 0.00 12.3 36.7 8.3 9ucb4uxnvzxc8
Module: SQL*Plus
PDB: ORCLPDB1
INSERT /*+ NO_DST_UPGRADE_INSERT_CONV IDX(0) */ INTO "HEMANT"."MLOG$_SOURCE_TABL
E_1" (dmltype$$,old_new$$,snaptime$$,change_vector$$,xid$$,"OBJECT_ID") VALUES (
:d,:o,to_date('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'),:c,:x,:1)

3.5 1 3.52 11.7 19.7 45.9 dxnyhyq7sqf8j
PDB: ORCLPDB1
DELETE FROM "HEMANT"."MV_1" SNAP$ WHERE "OBJ_ID" IN (SELECT * FROM (SELECT MLOG$
."OBJECT_ID" "OBJ_ID" FROM "HEMANT"."MLOG$_SOURCE_TABLE_1" MLOG$ WHERE "SNAPTIME
$$" > :1 AND ("DMLTYPE$$" != 'I')) AS OF SNAPSHOT(:B_SCN) )

3.3 1 3.25 10.8 45.2 .6 9n1gw9vpj9248
Module: SQL*Plus
PDB: ORCLPDB1
insert into source_table_1 select object_id, owner, object_name from source_tabl
e_2

2.3 2 1.14 7.6 18.4 77.4 94z4z19ygx34a
Module: SQL*Plus
PDB: ORCLPDB1
begin sys.dbms_irefstats.run_sa(:1, :2, :3, :4, :5, :6); end;

2.1 1 2.11 7.0 19.1 21.6 a2sctn32qtwnf
PDB: ORCLPDB1
/* MV_REFRESH (MRG) */ MERGE INTO "HEMANT"."MV_1" "SNA$" USING (SELECT * FROM (S
ELECT CURRENT$."OBJ_ID",CURRENT$."OBJ_OWNER",CURRENT$."OBJ_NAME" FROM (SELECT "S
OURCE_TABLE_1"."OBJECT_ID" "OBJ_ID","SOURCE_TABLE_1"."OWNER" "OBJ_OWNER","SOURCE
_TABLE_1"."OBJECT_NAME" "OBJ_NAME" FROM "SOURCE_TABLE_1" "SOURCE_TABLE_1") CURRE

1.7 1 1.67 5.6 50.3 43.5 btqubgr940awu
Module: sqlplus@oracle-19c-vagrant (TNS V1-V3)
PDB: CDB$ROOT
BEGIN dbms_workload_repository.create_snapshot(); END;

1.3 1 1.33 4.4 27.3 .0 ggaxdw7tpmqjb
PDB: ORCLPDB1
update "HEMANT"."MLOG$_SOURCE_TABLE_1" set snaptime$$ = :1 where snaptime$$ > t
o_date('2100-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS')

0.9 89 0.01 3.1 1.7 98.6 3un99a0zwp4vd
PDB: ORCLPDB1
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(proper
ty,0),subname,type#,flags,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and
p_obj#=obj#(+) order by order#

0.5 183 0.00 1.6 6.0 98.3 2sxqgx5hx76qr
PDB: ORCLPDB1
select /*+ rule */ bucket, endpoint, col#, epvalue, epvalue_raw, ep_repeat_count
, endpoint_enc from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by b
ucket

0.5 2 0.23 1.5 15.0 70.0 6tbg6ydrx9jmm
Module: SQL*Plus
PDB: ORCLPDB1
begin dbms_irefstats.purge_stats_mv_rp(in_time => :1, in_objnum => :2, in_r
etention_period => :3); end;

0.4 9 0.04 1.3 15.4 69.2 g1s379sraujaq
Module: SQL*Plus
PDB: ORCLPDB1
DELETE FROM SYS.MVREF$_STMT_STATS WHERE REFRESH_ID = :B2 AND MV_OBJ# = :B1

0.4 2 0.20 1.3 16.4 76.8 8szmwam7fysa3
Module: SQL*Plus
PDB: ORCLPDB1
insert into wri$_adv_objspace_trend_data select timepoint, space_usage, space_a
lloc, quality from table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, N
ULL, NULL, 'FALSE', :5, 'FALSE'))

0.4 59 0.01 1.3 9.5 97.3 03guhbfpak0w7
PDB: CDB$ROOT
select /*+ index(idl_ub1$ i_idl_ub11) */ piece#,length,piece from idl_ub1$ where
obj#=:1 and part=:2 and version=:3 order by piece#

0.3 2 0.15 1.0 11.0 .0 a8xypykqc348c
PDB: ORCLPDB1
BEGIN dbms_stats_internal.advisor_setup_obj_filter(:tid, :rid, 'EXECUTE', FAL
SE); END;

0.3 2 0.15 1.0 8.7 .0 avf5k3k0x0cxn
PDB: ORCLPDB1
insert into stats_advisor_filter_obj$ (rule_id, obj#, flag
s, type) select :rule_id, obj#, :flag_include, :type_expanded
from stats_advisor_filter_obj$ where type = :type_priv
and (bitand(flags, :flag_orcl_owned) = 0 or :get_orcl_objects = 'T')


Quite interesting that there are large number of operations that occur.

Unlike a Trace File, the AWR does not report SQL operations as a chronologically-ordered sequence.  In this case, they are ordered by Elapsed Time per operation.

Also, remember that PL/SQL calls will include the time for "child" SQL calls, so you will encounter double-counting if you add up the figures (e.g. the "dbms_mview.refresh('MV_1');" call included a number of SQL calls --- technically you can identify them only if you *trace* the session making this PL/SQL call.  However, since there was no other activity in this database, almost everything that happened appears in this AWR extract.

The actual calls "delete source_table_1;" and "insert into source_table_1 select object_id, owner, object_name from source_table_2;" were issued *before* the "exec dbms_mview.refresh('MV_1');" and are are not "child" calls.  The child calls that do appear in the AWR are not necessarily in the same chronological order of their execution.

The interesting "child" calls from the "dbms_mview.refresh" call that I would like to point out are :

delete from "HEMANT"."MLOG$_SOURCE_TABLE_1" where snaptime$$ <= :1

INSERT /*+ NO_DST_UPGRADE_INSERT_CONV IDX(0) */ INTO "HEMANT"."MLOG$_SOURCE_TABL
E_1" (dmltype$$,old_new$$,snaptime$$,change_vector$$,xid$$,"OBJECT_ID") VALUES (
:d,:o,to_date('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'),:c,:x,:1)

DELETE FROM "HEMANT"."MV_1" SNAP$ WHERE "OBJ_ID" IN (SELECT * FROM (SELECT MLOG$
."OBJECT_ID" "OBJ_ID" FROM "HEMANT"."MLOG$_SOURCE_TABLE_1" MLOG$ WHERE "SNAPTIME
$$" > :1 AND ("DMLTYPE$$" != 'I')) AS OF SNAPSHOT(:B_SCN) )

/* MV_REFRESH (MRG) */ MERGE INTO "HEMANT"."MV_1" "SNA$" USING (SELECT * FROM (S
ELECT CURRENT$."OBJ_ID",CURRENT$."OBJ_OWNER",CURRENT$."OBJ_NAME" FROM (SELECT "S
OURCE_TABLE_1"."OBJECT_ID" "OBJ_ID","SOURCE_TABLE_1"."OWNER" "OBJ_OWNER","SOURCE
_TABLE_1"."OBJECT_NAME" "OBJ_NAME" FROM "SOURCE_TABLE_1" "SOURCE_TABLE_1") CURRE


In my next post, I'll share some other findings after I found something interesting in the AWR report.


Categories: DBA Blogs

ORA-29279: SMTP permanent error: 550 XXXsmtpXX: Host xxx.xxx.xxx.xxx: No unauthenticated relaying permitted

Tom Kyte - Fri, 2020-02-07 18:03
Hi Tom, I want to send email through PL/SQL by using utl_mail package, I have completed below steps 1. Installed scripts <b>@$ORACLE_HOME/rdbms/admin/utlmail.sql @$ORACLE_HOME/rdbms/admin/prvtmail.plb</b> 2. granted execute to us...
Categories: DBA Blogs

how to load XML file into oracle table and using XML query to get result

Tom Kyte - Fri, 2020-02-07 18:03
Hi Tom, I have XML file from Web Report. there are some error records in the file, it is difficult to find the error in XML file, I want to load this file into oracle table. then use XML query. below is sample file and expected result. This is fir...
Categories: DBA Blogs

How to retrieve the 200millions of record into txt file using select query?

Tom Kyte - Tue, 2020-02-04 09:02
Hi Tom, We have 200+millions of records in our table, we have to retrieve all the records into text file without impacting the performance. I tried using DBMS_parallel_execute but this doesn?t have return type. Is there a way to get all Millions of...
Categories: DBA Blogs

Getting error -12842 : ORA-12842: CURSOR INVALIDATED DURING PARALLEL EXECUTION

Tom Kyte - Tue, 2020-02-04 09:02
Below is my scenario. We are getting error randomly in production. when we restart the process error disappears. we are not able to reproduce even in lower environment. Can you please help in letting us know the potential issue with insert statement ...
Categories: DBA Blogs

Truncate Multiple tables in a single line statement.

Tom Kyte - Tue, 2020-02-04 09:02
My question: Is it possible to truncate multiple tables in a single line statement? Firstly, thanks for giving this opportunity to ask a question. I have developed a code to truncate multiple tables in a single line statement. Please refer the ...
Categories: DBA Blogs

How to tune a create table that runs in loop

Tom Kyte - Tue, 2020-02-04 09:02
Hi All, can someone help me to tune this peice of code. It is taking 45 minutes to complete the execution as of now. We have tried taking off the loop and creating 5 different create statements but did gain just 2 minutes Each of the table have and...
Categories: DBA Blogs

How many Not guaranteed Restore Points can I use at given point

Tom Kyte - Tue, 2020-02-04 09:02
Hello! We have a development database where we won't do any backup of the database. Our method to recreate it will be rman duplicate from the production one. But we want to give the option to our development team to do a flashback of that database...
Categories: DBA Blogs

Archiving Using DBMS_DATAPUMP

Tom Kyte - Tue, 2020-02-04 09:02
I have a requirement to archive historical data, that is delete data from a number of tables that is more than 36 months old, but only a subset of data, for arguments sake lets call them quotes given to customers that have not been taken up. We curre...
Categories: DBA Blogs

Creating index for interval values

Tom Kyte - Tue, 2020-02-04 09:02
Hi! Is it possible to create an index with some kind of "between" option? I have a dimension-table with 30 columns where the unique key is a combination of 2 columns (from_minute, to_minute). Primary key is a unique sequence. Total number of ...
Categories: DBA Blogs

Select returns rows that it should not

Tom Kyte - Tue, 2020-02-04 09:02
Hi. I have a reproduction of strange behavior of select statement. Reproduction: <code> --drop table test_a1 --drop table test_a2 create table test_a1 ( id1 number(19), value1 number(1) ) / create table test_a2 ( id2 number(19), v...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs