DBA Blogs

Oracle 19c Automatic Indexing: CBO Incorrectly Using Auto Indexes Part I (Neighborhood Threat)

Richard Foote - Fri, 2020-09-18 03:19
Following on from my previous few posts on “data skew”, I’m now going to look at it from a slightly different perspective, where there is an inherent relationship between columns. The CBO has difficulties in recognising (by default) that some combinations of column values are far more common than other combinations, resulting in incorrect cardinality […]
Categories: DBA Blogs

Need to calculate Age as part of select

Tom Kyte - Thu, 2020-09-17 10:06
Hi, We just went live on Oracle a couple of weeks ago. I have a legacy process that includes running a script that was coded for Sybase. I have most of it converted to Oracle, but I'm having trouble with the Age field (it's the last piece I need to get working). I thought about just including the Age piece... then thought to include the entire script for context if nothing else. Thanks in advance for the assist! -Denise Current legacy code <code>SELECT DISTINCT meme.MEME_MEDCD_NO, meme.MEME_BIRTH_DT, AGE = CASE WHEN ( month(convert(datetime, meme.MEME_BIRTH_DT, 103))*100)+ day(convert(datetime, meme.MEME_BIRTH_DT, 103)) - ((month(getdate())*100)+day(getdate())) <= 0 THEN DATEDIFF(YEAR,convert(datetime, meme.MEME_BIRTH_DT, 103),getdate())</b> ELSE DATEDIFF(YEAR,convert(datetime, meme.MEME_BIRTH_DT, 103),getdate())-1 END, sbsb.SBSB_ID, mepe.MEPE_EFF_DT, mepe.MEPE_TERM_DT, mepe.MEPE_ELIG_IND, mepe.CSPI_ID, sbad.SBAD_COUNTY AS 'Member_County', pdpd.LOBD_ID FROM dbo.CMC_MEME_MEMBER meme INNER JOIN dbo.CMC_MEPE_PRCS_ELIG mepe ON mepe.MEME_CK =meme.MEME_CK INNER JOIN dbo.CMC_SBSB_SUBSC sbsb ON sbsb.SBSB_CK = meme.SBSB_CK INNER JOIN CMC_PDPD_PRODUCT pdpd ON mepe.PDPD_ID = pdpd.PDPD_ID INNER JOIN CMC_SBAD_ADDR sbad ON sbsb.SBSB_CK = sbad.SBSB_CK AND sbsb.SBAD_TYPE_MAIL = sbad.SBAD_TYPE WHERE mepe.GRGR_CK IN (1,3,8) AND mepe.MEPE_ELIG_IND = 'Y' AND mepe.MEPE_EFF_DT <= '09/01/2020' AND -- Match file date mepe.MEPE_TERM_DT >= '09/01/2020' AND -- Match file date meme.MEME_MEDCD_NO IN ( )</code>
Categories: DBA Blogs

Database Wallet

Tom Kyte - Thu, 2020-09-17 10:06
Hi Team, We have SSL certificates imported on database server using ORAPKI after creating wallet. We are using utl_http for external system communication from database and using utl_http.set_wallet to access the certificates. Now, we are en-queuing the messages to database queue and writing logic in middle ware to read message from queue and send messages to external system. but the problem is certificates are database server and the communication to external system is from middleware. Can we read the SSL certificate from database server and pass it to middleware? is there a way to pass the certificate from DB to middleware. Can you please advise. Thank You.
Categories: DBA Blogs

Process in order to estimate how many DBAs are needed to support a project

Tom Kyte - Thu, 2020-09-17 10:06
GM, Do you guys know of a whitepaper or training that describes an approach to estimating how many Oracle DBA hours are needed to perform X, Y, Z? We are bidding on an effort, and I would prefer not to have to reinvent the wheel if something already exists. For instance, if a project has these requirements: DBA shall setup a three node 19c RAC cluster with ASM. DBA shall tune the system DBA shall be able to restore the database within a day with minimal data loss DBA shall It will need to be tuned. DBA shall setup a disaster recovery site using data guard DBA shall setup security to meet NIST-3029 I have started to break down all of the 50+ major tasks to satisfy the above requirements, and and threw in there rough daily estimates for each step. Database Security 10 days: o Database instance security hardening setup 3 o Database server security hardening implementation - 2 o Security scanner software setup and troubleshooting - 1 o Troubleshooting false positive security findings and waivers - 2 Oracle install and dB creation with RAC- 5 days o Clusterware setup- 3 days o RAC database creation- 1 o Licensing - .5 o Database Shutdown and Startup setup 1 Backup and Recovery Setup- 2 etc. Thanks, John
Categories: DBA Blogs

Choice State in AWS Step Functions

Pakistan's First Oracle Blog - Thu, 2020-09-17 02:47

Richly asynchronous server-less applications can be built by using AWS step functions. Choice State in AWS Step Functions is the newest feature which was long awaited.

In simply words, we define steps and their transitions and call it State Machine as a whole. In order to define this state machine, we use Amazon States Language (ASL). ASL is a JSON-based structured language that defines state machines and collections of states that can perform work (Task states), determines which state to transition to next (Choice state), and stops execution on error (Fail state). 

So if the requirement is to add a branching logic like if-then-else or case statement in our state transition, then Choice state comes handy. The choice state introduces various new operators into the ASL and the sky is now limit with the possibilities. Operators for choice state include comparison operators like Is Null, IsString etc, Existence operators like Ispresent, glob wildcards where you match some string and also variable string comparison.

Choice State enables developers to simplify existing definitions or add dynamic behavior within state machine definitions. This makes it easier to orchestrate multiple AWS services to accomplish tasks. Modelling complex workflows with extended logic is now possible with this new feature.

Now one hopes that AWS introduces doing it all graphically instead of dabbling into ASL.

Categories: DBA Blogs

Table vs Index Fragmentation

Tom Kyte - Wed, 2020-09-16 15:46
Hello, This is more of a fundamental question, sorry i dont have any test cases. Does table fragmentation also imply index fragmentation for the same table. ?
Categories: DBA Blogs

PARALLEL HINT and DML ERROR logging

Tom Kyte - Wed, 2020-09-16 15:46
HI, <code> CREATE TABLE TEMP_TEST ( ID NUMBER(10) ) ALTER TABLE TEMP_TEST ADD ( CONSTRAINT temp_test_pk UNIQUE (ID) ); </code> Scenario:1: <code> truncate table TEMP_TEST; ALTER SESSION ENABLE PARALLEL DML; INSERT INTO /*+ NOAPPEND PARALLEL(5) */ TEMP_TEST SELECT /*+ PARALLEL */DISTINCT BUCKET FROM source LOG ERRORS INTO ERR$_TEMP_TEST ('insert failed') REJECT LIMIT UNLIMITED; </code> Scenario:2: <code> truncate table TEMP_TEST; ALTER SESSION ENABLE PARALLEL DML; INSERT INTO /*+ NOAPPEND PARALLEL(5) */ TEMP_TEST SELECT DISTINCT BUCKET FROM source LOG ERRORS INTO ERR$_TEMP_TEST ('insert failed') REJECT LIMIT UNLIMITED; </code> Scenario:1 is failing with unique constraint error instead of error records inserting into error table, but scenario:2 error records are inserting into ERR$_TEMP_TEST? The only difference between these two is PARALLEL hint in select statement.
Categories: DBA Blogs

getting ora 01017, invalid username/password when configuring oracle mobile server to my repository db

Tom Kyte - Wed, 2020-09-16 15:46
my local db is a 19c i downloaded the latest version of the mobile server and while going through the installation, i came to this error, i have check my sqlnet.ora file, the tns configuration is good because i am able to connect with toad and sql developer. this is the sqlnet.ora #SQLNET.AUTHENTICATION_SERVICES= (NTS) SQLNET.AUTHENTICATION_SERVICES = (NONE) NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) #SQLNET.ALLOWED_LOGON_VERSION=12 SQLNET.ALLOWED_LOGON_VERSION=9 WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY=C:\Users\TEKYI\Documents\wallet\oracle)))
Categories: DBA Blogs

Anomaly detection

Tom Kyte - Wed, 2020-09-16 15:46
Hello, We have an application that monitors applications, detects anomalies, does correlation between metrics, performs Root Cause Analysis based on a few machine learning algorithms. We are planning onboard oracle monitoring for this application with a few metrics like below. Could you please suggest where we could get some baseline monitoring SQL's to plugin to our application, especially the SQLs that are used to generate ASH/AWR reports. We want to start small and expand over a period of time. Redo (Mb per second) Transactions per second Latency: Log file Sync, Log file parallel write, single block read all in Avg Ms IO MB/per sec Physical Reads MB/sec Physical writes MB/sec DB CPU % usage Network MB/sec Logons per sec Logical Reads Mb/sec File Sync(Avg/ms) RMAN IO mb/ms Waits Locks Top SQL?s Stale statistics on objects Top Objects by Size, growth, Avg growth per day, month Space growth (total vs used), Avg per day, month Thanks, Ravi B
Categories: DBA Blogs

cycle detected in recursive query where it seems to be no cycle

Tom Kyte - Wed, 2020-09-16 15:46
I have recursive query on Oracle 11g table with undirected graph data. Each row represents one edge. The recursive query traverses all edges starting from given input edge. The idea of query is: - input edge is at the 0th level - for n>0, edge is on n-th level if it incides with node of some edge on (n-1)-th level. Query: <code>with edges (id, a, b) as ( select 1, 'X', 'Y' from dual union select 2, 'X', 'Y' from dual ), r (l, id, parent_a, parent_b, child_a, child_b, edge_seen) as ( select 0, id, null, null, a, b, cast(collect(id) over () as sys.ku$_objnumset) from edges where id = 1 union all select r.l + 1, e.id, r.child_a, r.child_b, e.a, e.b , r.edge_seen multiset union distinct (cast(collect(e.id) over () as sys.ku$_objnumset)) from r join edges e on (r.child_a in (e.a, e.b) or r.child_b in (e.a, e.b)) and e.id not member of (select r.edge_seen from dual) ) select * from r; </code> The query worked well with other inputs until two parallel edges between same node pair occured. In this case, there is edge 1 on 0th level of recursion (initial row). I expected edge 2 would be added to result on 1st level of recursion since join condition holds. Instead I get "ORA-32044: cycle detected while executing recursive with query". I know this error is reported when the row newly joined to recursive query result would be same as some existing row. What I don't understand is why Oracle treats row with same node ids but different edge id as duplicate. Adding <code>cycle child_a, child_b set iscycle to 1 default 0</code> clause gives iscycle=1 for new row, adding <code>cycle id, child_a, child_b set iscycle to 1 default 0</code> gives iscycle=0, which is both correct. <b>Is it some known Oracle 11g bug and what's the best way to handle it?</b> I cannot fill LiveSQL link form since LiveSQL supports only Oracle 19 and the problem is reproducible only in Oracle 11g which I can't migrate from. The dbfiddle equivalent is https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=43af3cfae920e31f9a2748c1c31b54ad . Thanks.
Categories: DBA Blogs

ORA-22992, No LOB field selected

Tom Kyte - Wed, 2020-09-16 15:46
I have a SQL statement can run by itself and get the result back over db link. But if I want to put result set into a table either using ?create table?.as..? or ?insert into ?? before the same select statement, I will get ORA-22992 error. What caused this? The SQL statement like: <CODE> Select a.m, a.n, a.o, a.p, b.q, b.r, b.s, c.t, c.u,c.v From a@remote a left join b@remote b on b.m=a.m and b.n=a.n Left join c@remote c on c.m=a.m and c.m=a.n Where a.yr=2019 a.class=1 order by a.m </CODE> table ?a? and ?c? don?t have LOB fields, table ?b? has a field ?Ldesc? which is CLOB, But it is NOT in the select list. Local version : Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production Remote version: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Data Skew Part III (The Good Son)

Richard Foote - Wed, 2020-09-16 04:05
  I’m going to expand just a tad on my previous posts on data skew and run a simple query that returns a few rows based on a column predicate AND another query on the same column that returns many rows. The following table has a CODE column as with previous posts with the data […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Data Skew Part II (Everything’s Alright)

Richard Foote - Mon, 2020-09-14 00:05
In my previous post, I discussed an example with data skew, in which the Automatic Indexing process created a new index, but somehow the CBO when using the index estimated the correct cardinality estimate even though no histograms were explicitly calculated. In this post I’ll answer HOW this achieved by the CBO. Get some idea […]
Categories: DBA Blogs

Drop Foreign Key from Referenced-Partitioned Child table

Tom Kyte - Sat, 2020-09-12 01:46
Hi, Could you please have a look at below table structure <code> drop table child purge / drop table parent purge / create table parent (id number(11,0) ,dt date ,constraint pk_parent primary key (id) ) partition by range (dt) ( partition pytr_hist_p_jan_2018 values less than (to_date(' 2018-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')), partition "pytr_hist_p_feb_2018" values less than (to_date(' 2018-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')), partition "pytr_hist_p_maxvalue" values less than (maxvalue) ) / create table child (id number(11,0) ,name varchar2(20 char) ,constraint pk_child primary key (id) ,constraint fk_parent_id foreign key (id) references parent (id) ) partition by reference (fk_parent_id) / </code> I need to separate out child table from referenced partitioned and make it as independent table. I tried dropping the constraint, but due to reference-partition it not getting completed. <code> alter table child drop constraint fk_parent_id / </code> Getting error as <code> ORA-14650: operation not supported for reference-partitioned tables 14650. 00000 - "operation not supported for reference-partitioned tables" </code> On production environment, child table size if around 40 GB, so could you please suggest any alternative how can I detach child table from parent.
Categories: DBA Blogs

Checking the new PDB as a no-data-clone

Hemant K Chitale - Fri, 2020-09-11 22:56

 In the previous post, I had created a NEWDB as a "NO DATA" clone (even also explicitly excluding all USER_TABLESPACES).

FYI, these are the entries in the Primary database alert log (entries in the Standby are shown in the previous post) :

2020-09-07T23:35:32.840917+08:00
create pluggable database newpdb
from orclpdb1
storage (maxsize 10G)
service_name_convert=('ORCLPDB1','NEWPDB')
user_tablespaces=NONE
standbys=ALL
no data
2020-09-07T23:35:35.215231+08:00
ORCLPDB1(3): MDSYS.SDO_COORD_OP_PARAM_VALS (PARAM_VALUE_FILE) - CLOB populated
2020-09-07T23:36:04.284729+08:00
NEWPDB(4):Endian type of dictionary set to little
****************************************************************
Pluggable Database NEWPDB with pdb id - 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x0000000000000118
****************************************************************
NEWPDB(4):Autotune of undo retention is turned on.
NEWPDB(4):Undo initialization recovery: err:0 start: 786885 end: 786904 diff: 19 ms (0.0 seconds)
NEWPDB(4):[2298] Successfully onlined Undo Tablespace 2.
NEWPDB(4):Undo initialization online undo segments: err:0 start: 786905 end: 786994 diff: 89 ms (0.1 seconds)
NEWPDB(4):Undo initialization finished serial:0 start:786885 end:786996 diff:111 ms (0.1 seconds)
NEWPDB(4):Database Characterset for NEWPDB is AL32UTF8
NEWPDB(4):JIT: pid 2298 requesting stop
2020-09-07T23:36:05.350701+08:00
NEWPDB(4):Buffer Cache flush started: 4
NEWPDB(4):Buffer Cache flush finished: 4
Completed: create pluggable database newpdb
from orclpdb1
storage (maxsize 10G)
service_name_convert=('ORCLPDB1','NEWPDB')
user_tablespaces=NONE
standbys=ALL
no data
2020-09-07T23:40:15.148214+08:00
alter pluggable database orclpdb1 close
2020-09-07T23:40:15.165239+08:00
ORCLPDB1(3):JIT: pid 2298 requesting stop
ORCLPDB1(3):Buffer Cache flush started: 3
ORCLPDB1(3):Buffer Cache flush finished: 3
Pluggable database ORCLPDB1 closed
Completed: alter pluggable database orclpdb1 close
2020-09-07T23:40:22.786209+08:00
alter pluggable database orclpdb1 open read write
ORCLPDB1(3):Autotune of undo retention is turned on.
2020-09-07T23:40:23.211240+08:00
ORCLPDB1(3):Endian type of dictionary set to little
ORCLPDB1(3):Undo initialization recovery: err:0 start: 1045210 end: 1045246 diff: 36 ms (0.0 seconds)
ORCLPDB1(3):[2298] Successfully onlined Undo Tablespace 2.
ORCLPDB1(3):Undo initialization online undo segments: err:0 start: 1045246 end: 1045562 diff: 316 ms (0.3 seconds)
ORCLPDB1(3):Undo initialization finished serial:0 start:1045210 end:1045596 diff:386 ms (0.4 seconds)
2020-09-07T23:40:23.793691+08:00
ORCLPDB1(3):Database Characterset for ORCLPDB1 is AL32UTF8
2020-09-07T23:40:26.180462+08:00
ORCLPDB1(3):Opening pdb with no Resource Manager plan active
ORCLPDB1(3):joxcsys_required_dirobj_exists: directory object exists with required path /opt/oracle/product/19c/dbhome_1/javavm/admin/, pid 2298 cid 3
Pluggable database ORCLPDB1 opened read write
Completed: alter pluggable database orclpdb1 open read write
2020-09-07T23:40:34.702216+08:00
alter pluggable database newpdb close
ORA-65020 signalled during: alter pluggable database newpdb close...
2020-09-07T23:40:44.050260+08:00
alter pluggable database newpdb open read write
NEWPDB(4):Autotune of undo retention is turned on.
2020-09-07T23:40:44.313418+08:00
NEWPDB(4):Endian type of dictionary set to little
NEWPDB(4):Undo initialization recovery: err:0 start: 1066307 end: 1066330 diff: 23 ms (0.0 seconds)
NEWPDB(4):[2298] Successfully onlined Undo Tablespace 2.
NEWPDB(4):Undo initialization online undo segments: err:0 start: 1066330 end: 1066564 diff: 234 ms (0.2 seconds)
NEWPDB(4):Undo initialization finished serial:0 start:1066307 end:1066592 diff:285 ms (0.3 seconds)
NEWPDB(4):Deleting old file#9 from file$
NEWPDB(4):Deleting old file#10 from file$
NEWPDB(4):Deleting old file#11 from file$
NEWPDB(4):Deleting old file#12 from file$
NEWPDB(4):Adding new file#13 to file$(old file#9). fopr-1, newblks-35840, oldblks-19200
NEWPDB(4):Adding new file#14 to file$(old file#10). fopr-1, newblks-47360, oldblks-15360
NEWPDB(4):Adding new file#15 to file$(old file#11). fopr-1, newblks-33280, oldblks-12800
NEWPDB(4):Marking tablespace #5 offline since it has been requested to be skipped as part of the USER_TABLESPACES clause. The tablespace cannot be brought online and needs to be dropped and recreated if it needs to brought online.
2020-09-07T23:40:45.094463+08:00
NEWPDB(4):Successfully created internal service NEWPDB at open
****************************************************************
Post plug operations are now complete.
Pluggable database NEWPDB with pdb id - 4 is now marked as NEW.
****************************************************************
2020-09-07T23:40:45.313606+08:00
NEWPDB(4):Pluggable database NEWPDB dictionary check beginning
NEWPDB(4):Pluggable Database NEWPDB Dictionary check complete
NEWPDB(4):Database Characterset for NEWPDB is AL32UTF8
2020-09-07T23:40:50.931146+08:00
NEWPDB(4):Opening pdb with no Resource Manager plan active
NEWPDB(4):joxcsys_required_dirobj_exists: directory object exists with required path /opt/oracle/product/19c/dbhome_1/javavm/admin/, pid 2298 cid 4
Pluggable database NEWPDB opened read write
Completed: alter pluggable database newpdb open read write
2020-09-07T23:44:00.682159+08:00
Control autobackup written to DISK device

handle '/opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2020_09_07/o1_mf_s_1050536639_hodob0c4_.bkp'



But let's now check if NEWPDB has any left-over objects from the Data Dictionary of ORCLPDB1.

SQL> select con_id, name, open_mode
2 from v$pdbs
3 order by 1
4 /

CON_ID NAME OPEN_MODE
---------- ---------------- ----------
2 PDB$SEED READ ONLY
3 ORCLPDB1 READ WRITE
4 NEWPDB MOUNTED

SQL> alter pluggable database newpdb open read write;

Pluggable database altered.

SQL> select con_id, name, open_mode
2 from v$pdbs
3 order by 1
4 /

CON_ID NAME OPEN_MODE
---------- ---------------- ----------
2 PDB$SEED READ ONLY
3 ORCLPDB1 READ WRITE
4 NEWPDB READ WRITE

SQL>
SQL> select con_id, name, creation_time
2 from v$pdbs
3 order by 1
4 /

CON_ID NAME CREATION_
---------- ---------------- ---------
2 PDB$SEED 04-MAY-19
3 ORCLPDB1 04-MAY-19
4 NEWPDB 07-SEP-20

SQL>


First get the list from ORCLPDB1 :
SQL> alter session set container=ORCLPDB1;

Session altered.

SQL>
SQL> select username, created, oracle_maintained
2 from dba_users
3 where username = 'HEMANT'
4 /

USERNAME CREATED O
---------------- --------- -
HEMANT 07-MAY-19 N

SQL>
SQL> l
1 select object_type, trunc(created), status, count(*)
2 from dba_objects
3 where owner = 'HEMANT'
4 group by object_type, trunc(created), status
5* order by 1,2
SQL> /

OBJECT_TYPE TRUNC(CRE STATUS COUNT(*)
-------------------- --------- ------- ----------
INDEX 18-AUG-19 VALID 3
INDEX 16-SEP-19 VALID 1
INDEX 29-SEP-19 VALID 3
INDEX 12-OCT-19 VALID 2
INDEX 19-OCT-19 VALID 2
INDEX 27-OCT-19 VALID 3
INDEX 28-OCT-19 VALID 2
INDEX 12-NOV-19 VALID 4
INDEX 12-JUL-20 VALID 2
JOB 12-OCT-19 VALID 1
MATERIALIZED VIEW 18-AUG-19 VALID 1
MATERIALIZED VIEW 16-SEP-19 VALID 1
MATERIALIZED VIEW 25-SEP-19 VALID 1
MATERIALIZED VIEW 29-SEP-19 VALID 2
MATERIALIZED VIEW 12-OCT-19 VALID 2
MATERIALIZED VIEW 27-OCT-19 VALID 2
MATERIALIZED VIEW 12-NOV-19 VALID 1
TABLE 07-MAY-19 VALID 1
TABLE 18-AUG-19 VALID 4
TABLE 16-SEP-19 VALID 1
TABLE 25-SEP-19 VALID 1
TABLE 29-SEP-19 VALID 3
TABLE 12-OCT-19 VALID 2
TABLE 19-OCT-19 VALID 3
TABLE 27-OCT-19 VALID 4
TABLE 28-OCT-19 VALID 3
TABLE 12-NOV-19 VALID 4
TABLE 26-MAR-20 VALID 1
TABLE 20-MAY-20 VALID 1
TABLE 12-JUL-20 VALID 1
TABLE 10-AUG-20 VALID 1
TABLE 12-AUG-20 VALID 2
TABLE 14-AUG-20 VALID 4
TABLE 18-AUG-20 VALID 3
TABLE PARTITION 07-MAY-19 VALID 4

35 rows selected.

SQL>


Now get the list from NEWPDB :

SQL> alter session set container=NEWPDB;

Session altered.

SQL> select username, created, oracle_maintained
2 from dba_users
3 where username = 'HEMANT'
4 /

USERNAME CREATED O
---------------- --------- -
HEMANT 07-MAY-19 N

SQL>
SQL> l
1 select object_type, trunc(created), status, count(*)
2 from dba_objects
3 where owner = 'HEMANT'
4 group by object_type, trunc(created), status
5* order by 1,2
SQL> /

OBJECT_TYPE TRUNC(CRE STATUS COUNT(*)
-------------------- --------- ------- ----------
INDEX 18-AUG-19 VALID 3
INDEX 16-SEP-19 VALID 1
INDEX 29-SEP-19 VALID 3
INDEX 12-OCT-19 VALID 2
INDEX 19-OCT-19 VALID 2
INDEX 27-OCT-19 VALID 3
INDEX 28-OCT-19 VALID 2
INDEX 12-NOV-19 VALID 4
INDEX 12-JUL-20 VALID 2
JOB 12-OCT-19 VALID 1
MATERIALIZED VIEW 18-AUG-19 VALID 1
MATERIALIZED VIEW 16-SEP-19 VALID 1
MATERIALIZED VIEW 25-SEP-19 VALID 1
MATERIALIZED VIEW 29-SEP-19 VALID 2
MATERIALIZED VIEW 12-OCT-19 VALID 2
MATERIALIZED VIEW 27-OCT-19 VALID 2
MATERIALIZED VIEW 12-NOV-19 VALID 1
TABLE 07-MAY-19 VALID 1
TABLE 18-AUG-19 VALID 4
TABLE 16-SEP-19 VALID 1
TABLE 25-SEP-19 VALID 1
TABLE 29-SEP-19 VALID 3
TABLE 12-OCT-19 VALID 2
TABLE 19-OCT-19 VALID 3
TABLE 27-OCT-19 VALID 4
TABLE 28-OCT-19 VALID 3
TABLE 12-NOV-19 VALID 4
TABLE 26-MAR-20 VALID 1
TABLE 20-MAY-20 VALID 1
TABLE 12-JUL-20 VALID 1
TABLE 10-AUG-20 VALID 1
TABLE 12-AUG-20 VALID 2
TABLE 14-AUG-20 VALID 4
TABLE 18-AUG-20 VALID 3
TABLE PARTITION 07-MAY-19 VALID 4

35 rows selected.

SQL>


That's interesting. Let's explicitly connect to each PDB and verify. 
 ORCLPDB1 first

SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> select table_name
2 from user_tables
3 order by 1
4 /

TABLE_NAME
------------------------------
EMPLOYEES
HEMANT_SOURCE_TBL
MLOG$_HEMANT_SOURCE_TBL
MLOG$_MY_LARGE_SOURCE
MLOG$_NEW_SOURCE_TABLE
MLOG$_SOURCE_TABLE
MLOG$_SOURCE_TABLE_1
MV_1
MV_2
MV_3
MV_DEPT
MV_EMP
MV_FAST_NOT_POSSIBLE
MV_OF_SOURCE
MY_LARGE_SOURCE
MY_TARGET
MY_TRANSACTIONS
NEW_MV
NEW_MV_2_1
NEW_MV_2_2
NEW_SOURCE_TABLE
NOROWDEP
ROWDEPENDENCIES
RUPD$_HEMANT_SOURCE_TBL
RUPD$_MY_LARGE_SOURCE
RUPD$_SOURCE_TABLE
RUPD$_SOURCE_TABLE_1
SOURCE_TABLE
SOURCE_TABLE_1
SOURCE_TABLE_2
TABLE_A
TABLE_B
T_1
T_2
T_3
T_4
WIDGETS_LIST
X
XYZ

39 rows selected.

SQL>
SQL> select count(*) from employees
2 /

COUNT(*)
----------
10000

SQL> select count(*) from my_target
2 /

COUNT(*)
----------
997

SQL>


Now, NEWPDB :

SQL> connect hemant/hemant@newpdb
Connected.
SQL> select table_name
2 from user_tables
3 order by 1
4 /

TABLE_NAME
------------------------------
EMPLOYEES
HEMANT_SOURCE_TBL
MLOG$_HEMANT_SOURCE_TBL
MLOG$_MY_LARGE_SOURCE
MLOG$_NEW_SOURCE_TABLE
MLOG$_SOURCE_TABLE
MLOG$_SOURCE_TABLE_1
MV_1
MV_2
MV_3
MV_DEPT
MV_EMP
MV_FAST_NOT_POSSIBLE
MV_OF_SOURCE
MY_LARGE_SOURCE
MY_TARGET
MY_TRANSACTIONS
NEW_MV
NEW_MV_2_1
NEW_MV_2_2
NEW_SOURCE_TABLE
NOROWDEP
ROWDEPENDENCIES
RUPD$_HEMANT_SOURCE_TBL
RUPD$_MY_LARGE_SOURCE
RUPD$_SOURCE_TABLE
RUPD$_SOURCE_TABLE_1
SOURCE_TABLE
SOURCE_TABLE_1
SOURCE_TABLE_2
TABLE_A
TABLE_B
T_1
T_2
T_3
T_4
WIDGETS_LIST
X
XYZ

39 rows selected.

SQL>
SQL> select count(*) from employees;

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

SQL> select count(*) from my_target;

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

SQL>


So, NEWPDB has the object definitions but really has no data.


Categories: DBA Blogs

Re-write query without union

Tom Kyte - Fri, 2020-09-11 07:26
Hi Gurus, I have below query with UNION. it scan same table twice. just wondering if it is possible to re-write without union <code>WITH T AS (SELECT 'AAA' AS DESCR, 100 AS AMT FROM DUAL UNION ALL SELECT 'BBB', 200 FROM DUAL UNION ALL SELECT 'BBB', 100 FROM DUAL UNION ALL SELECT 'BBB', 300 FROM DUAL UNION ALL SELECT 'CCC', 444 FROM DUAL UNION ALL SELECT 'CCC', 333 FROM DUAL UNION ALL SELECT 'DDD', 230 FROM DUAL UNION ALL SELECT 'DDD', 240 FROM DUAL UNION ALL SELECT 'EEE', 140 FROM DUAL) SELECT CASE WHEN DESCR IN ('BBB', 'CCC', 'DDD') THEN 'PER_NER' ELSE 'BIZ_NER' END AS DES, SUM (AMT) FROM T WHERE DESCR <> 'AAA' GROUP BY CASE WHEN DESCR IN ('BBB', 'CCC', 'DDD') THEN 'PER_NER' ELSE 'BIZ_NER' END UNION ALL SELECT DESCR, SUM (AMT) FROM T GROUP BY DESCR;</code> <code>WITH T AS (SELECT 'AAA' AS DESCR, 100 AS AMT FROM DUAL UNION ALL SELECT 'BBB', 200 FROM DUAL UNION ALL SELECT 'BBB', 100 FROM DUAL UNION ALL SELECT 'BBB', 300 FROM DUAL UNION ALL SELECT 'CCC', 444 FROM DUAL UNION ALL SELECT 'CCC', 333 FROM DUAL UNION ALL SELECT 'DDD', 230 FROM DUAL UNION ALL SELECT 'DDD', 240 FROM DUAL UNION ALL SELECT 'EEE', 140 FROM DUAL) SELECT descr, SUM (AMT) FROM T GROUP BY GROUPING SETS ( (CASE WHEN DESCR IN ('BBB', 'CCC', 'DDD') THEN 'PER_NER' WHEN DESCR <> 'AAA' THEN'BIZ_NER' END) , DESCR)</code> I tried to use grouping set like below, but I am not able to fix these two issues: as you can see, 1. the underlined record is extra (need to be removed) ; 2, the column name missed for these two record which is case statement in grouping set. Is it possible to re-write the query without union? thanks in advance. the output like below: DESCR SUM(AMT) <u> 100</u> <i> 1847 140</i> CCC 777 DDD 470 EEE 140 BBB 600 AAA 100 expected result is: DES SUM(AMT) <b>PER_NER 1847 BIZ_NER 140</b> CCC 777 DDD 470 EEE 140 BBB 600 AAA 100
Categories: DBA Blogs

Size difference between redo log and archive redo log

Tom Kyte - Fri, 2020-09-11 07:26
Hi, I am confusing with the size of redo log and archive redo log <b>Here I used script for monitoring</b> <code># Daily Count and Size of Redo Log Space (Single Instance) SELECT A.*, Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb FROM ( SELECT To_Char(First_Time,'YYYY-MM-DD') DAY, Count(1) Count#, Min(RECID) Min#, Max(RECID) Max# FROM v$log_history GROUP BY To_Char(First_Time,'YYYY-MM-DD') ORDER BY 1 DESC ) A, ( SELECT Avg(BYTES) AVG#, Count(1) Count#, Max(BYTES) Max_Bytes, Min(BYTES) Min_Bytes FROM v$log ) B ;</code> <b>RESULT</b> <code> DAY COUNT# MIN# MAX# DAILY_AVG_MB ---------- ---------- ---------- ---------- ------------ 2020-09-09 6 1 6 300</code> <b>But the total size of archive redo logs different</b> <code>[oracle@localhost archivelog]$ du -h 58M ./2020_09_09 58M . [oracle@localhost archivelog]$ cd 2020_09_09/ [oracle@localhost 2020_09_09]$ du -h 58M . [oracle@localhost 2020_09_09]$ ls o1_mf_1_3_hokmyqvv_.arc o1_mf_1_4_hokn4s92_.arc o1_mf_1_5_hoknsz36_.arc o1_mf_1_6_hol3smy3_.arc [oracle@localhost 2020_09_09]$ ls -ltr total 58684 -rw-r----- 1 oracle oinstall 15892992 Sep 9 17:51 o1_mf_1_3_hokmyqvv_.arc -rw-r----- 1 oracle oinstall 127488 Sep 9 17:54 o1_mf_1_4_hokn4s92_.arc -rw-r----- 1 oracle oinstall 1089024 Sep 9 18:06 o1_mf_1_5_hoknsz36_.arc -rw-r----- 1 oracle oinstall 42897408 Sep 9 22:21 o1_mf_1_6_hol3smy3_.arc [oracle@localhost 2020_09_09]$</code>
Categories: DBA Blogs

Query performance issue

Tom Kyte - Fri, 2020-09-11 07:26
Hi, Greetings! 1. First time, Executing the query within the same session, it completes quick with good plan. 2. Second time, Executing the same query within the same session, immediately, it hangs and picks up different plan. a) Could you suggest what could be the reason for this sudden change in plan. There is no data changes in the underlying table partition between both the runs. b) Can this query be tuned any further ? PRCING_DATE is the partition key column in both the tables. <code>SQL> WITH LAST_TICKS_BY_DAY AS ( SELECT /*+ gather_plan_statistics */ MAX(LAST_PRICE_TIME) AS LAST_PRICE_TIME, MAX(PRICING_DATE) AS PRICING_DATE FROM TEST.FFINC_PRC A WHERE A.INSTRUMENT_ID = 9730862 AND A.PRICING_DATE BETWEEN TO_DATE('2020-07-28', 'YYYY-MM-DD') AND TO_DATE('2020-07-29', 'YYYY-MM-DD') AND LAST_PRICE_TIME >= TO_TIMESTAMP('28-JUL-20 11.59.59.000000000 PM','DD-MON-YY HH.MI.SS.FF PM') AND LAST_PRICE_TIME <= TO_TIMESTAMP('29-JUL-20 11.59.59.000000000 PM','DD-MON-YY HH.MI.SS.FF PM') AND A.PRICE_TYPE_ID in(5 , 6 ) GROUP BY TRUNC(LAST_PRICE_TIME) ) SELECT /*+ gather_plan_statistics */ p.INSTRUMENT_ID, p.SOURCE, p.BID_PRICE, p.LAST_PRICE_TIME, p.ASK_PRICE, p.MID_PRICE, p.BID_SPREAD, p.ASK_SPREAD, p.MID_SPREAD, p.BID_YIELD, p.ASK_YIELD, p.MID_YIELD, p.ASK_SPREAD_CURVE, p.T_SPREAD, p.MID_T_ 4 SPREAD, p.ASK_T_SPREAD, p.TEST_CONDITION_CODE, p.CURVE_YIELD, p.PRICE_TYPE, p.BID_SPREAD_CURVE, p.MID_SPREAD_CURVE, p.PRICE_TYPE_ID, p.BID_DISCOUNT_RATE, p.ASK_DISCOUNT_RATE, p.EOD_LATE_BID_PRICE_DELTA, p.SPREAD, a.PRICING_SPREAD_TYPE, a.BENCHMARK_NAME, a.TBA_BACK_BENCH, a.DIMINIMIS_ELIGIBLE_FLAG, a.HALF_DEMINIMIS_ELIGIBLE_FLAG, a.BENCHMARK_TBA, a.PAYUP FROM TEST.FFINC_PRC p LEFT JOIN TEST.FPRC_ASSUM a ON p.ASSUMP_ID = a.ASSUMP_ID JOIN LAST_TICKS_BY_DAY B ON P.LAST_PRICE_TIME = B.LAST_PRICE_TIME WHERE p.INSTRUMENT_ID = 9730862 AND p.PRICING_DATE = B.PRICING_DATE AND p.PRICE_TYPE_ID in(5, 6) ORDER BY A.LAST_PRICE_TIME ASC / Elapsed: 00:00:00.09 SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------ SQL_ID 4suz6vatc44ts, child number 0 ------------------------------------- WITH LAST_TICKS_BY_DAY AS ( SELECT /*+ gather_plan_statistics */ MAX(LAST_PRICE_TIME) AS LAST_PRICE_TIME, MAX(PRICING_DATE) AS PRICING_DATE FROM TEST.FFINC_PRC A WHERE A.INSTRUMENT_ID = 9730862 AND A.PRICING_DATE BETWEEN TO_DATE('2020-07-28', 'YYYY-MM-DD') AND TO_DATE('2020-07-29', 'YYYY-MM-DD') AND LAST_PRICE_TIME >= TO_TIMESTAMP('28-JUL-20 11.59.59.000000000 PM','DD-MON-YY HH.MI.SS.FF PM') AND LAST_PRICE_TIME <= TO_TIMESTAMP('29-JUL-20 11.59.59.000000000 PM','DD-MON-YY HH.MI.SS.FF PM') AND A.PRICE_TYPE_ID in(5 , 6 ) GROUP BY TRUNC(LAST_PRICE_TIME) ) SELECT /*+ gather_plan_statistics */ p.INSTRUMENT_ID, p.SOURCE, p.BID_PRICE, p.LAST_PRICE_TIME, p.ASK_PRICE, p.MID_PRICE, p.BID_SPREAD, p.ASK_SPREAD, p.MID_SPREAD, p.BID_YIELD, p.ASK_YIELD, p.MID_YIELD, p.ASK_SPREAD_CURVE, p.T_SPREAD, p.MID_T_SPREAD, p.ASK_T_SPREAD, p.TEST_CONDITION_CODE, p.CURVE_YIELD, p.PRICE_TYPE, p.BID_SPREAD_CURVE, p.MID_SPREAD_CURVE, p.PRICE_TYPE_ID, p.BID Plan hash value: 86494900 ----------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation ...
Categories: DBA Blogs

Partner Webcast – Using Oracle Big Data Service

Earlier this year, Oracle re-launched Big Data Cloud Service on Oracle Cloud Infrastructure Generation 2, as part of the Oracle Cloud Data Science Platform. Oracle Big Data Service is an automated...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Address to Timezone

Tom Kyte - Thu, 2020-09-10 13:06
Hello, Is there any DB utility/custom code/Java stored procedure to find a timezone with an address as an input (City, State, country). Thanks, Ravi B
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs