Determine a leap year with SQL

According to the calendar, a year that is divisible by 4 and not divisible by 100 or divisible by 400 is a leap year

For example, those years are divisible by 100 but not 400 and therefore they are not leap years:

1700, 1800, 1900, 2100, 2200

The simple way to determine a leap year written in SQL

SQL> select year, case when (mod(year,4)=0 and mod(year,100) != 0 or mod(year,400)=0) then 'Y' else 'N' end is_leap_year from (select 2016 year from dual);

YEAR IS_LEAP_YEAR
---  ------------
2016 Y

Another deadlock explanation

As it is known, deadlock is the way Oracle Database controls mutually exclusive situations between sessions’ work. Since transactions dealt with insert, update or delete rows database to continue work it requires resolve deadlocks.

Quote by Tom Kite:

I lock “X”
You lock “Y”
I try to lock “Y” (but cannot)
and you then try to lock “X” (deadlock)

Let’s illustrate with an example with two sessions updating rows.

Session 1 updates 1st row

SQL> UPDATE HR.EMPLOYEES3 SET LAST_NAME = LAST_NAME || 1 WHERE employee_id=100;
1 row updated.

Session 2 updates 2nd row

SQL> UPDATE HR.EMPLOYEES3 SET LAST_NAME = LAST_NAME || 2 WHERE employee_id=206;
 1 row updated.

Validating Partition Content

You can identify whether rows in a partition are conformant to the partition definition or whether the partition key of the row is violating the partition definition with the ORA_PARTITION_VALIDATION SQL function. The SQL function takes a rowid as input and returns 1 if the row is in the correct partition and 0 otherwise. The function is applicable for internal, external, and hybrid partitioned tables for both internal and external partitions and subpartitions

Example:

SELECT test1.*, ORA_PARTITION_VALIDATION(rowid) FROM test1;

COL1       ORA_PARTITION_VALIDATION(ROWID)
---------- -------------------------------
         1                               1        
        99                               0

Details can be found in VLDB and Partitioning Guide

Database Upgrade Reference

Release Schedule of Current Database Releases (Doc ID 742060.1)
Exadata Database Machine and Exadata Storage Server Supported Versions (Doc ID 888828.1)
Exadata Critical Issues (Doc ID 1270094.1)

Database Capture and Replay testing issues

Database Capture and Replay: Common Errors and Reasons (Doc ID 463263.1)

Functions and procedures in the WITH Clause

When appropriate, it is more convenient to immediately use the function inside the WITH clause instead of creating it in the schema

create table t_src (id number);
insert into t_src values (100); commit;
create table t_test (date_op date, x number);

WITH
 FUNCTION with_function(p_val IN NUMBER) RETURN NUMBER IS
  BEGIN
  execute immediate 'insert into t_test (date_op,x) values (sysdate,:numval)'     using in p_val;
  commit;
   RETURN p_val;
  END;
SELECT with_function(100) FROM t_src WHERE rownum = 1;
/

ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at “WITH_FUNCTION”, line 3

DMLs update/insert/delete won’t work inside a function called from SELECT except for autonomous transactions:

Oracle Database Patch Update Basics

Building commercial off the shelf (COTS) software includes numerous technological operations. And it requires comprehensive release management and thoroughful versioning.

Beginning with the 2018 Database release, new feature releases of the database product are being provided by Oracle annually.

Oracle maintains both quarterly Release Updates (Updates) are being provided each January, April, July and October, and Release Update Revisions (Revisions) are being issued on the same quarterly basis to augment the Updates with fixes for known regressions and to include the latest security fixes as well.

Oracle recommends that customers stay current with quarterly Updates. Some customers may prioritize security updates over functional fixes and choose Revisions.

In Oracle document Release Update Introduction and FAQ (Doc ID 2285040.1) explained how customers could easily switch back and forth between Updates and Revisions.

Monitoring Parallel Execution Performance

VLDB and Partitioning Guide – Oracle 19c

V$PX_SESSION

Monitor parallel execution performance with the V$PX_SESSION dynamic performance view.

https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/monitor-parallel-performance.html

AWR service queries

Check AWR snapshot status in Oracle RAC for selected interval

select snap_id, instance_number, begin_interval_time, error_count, count(*) over (partition by snap_id order by snap_id asc) instances_this_snap from dba_hist_snapshot
where snap_id between <begin_snap_id> and <end_snap_id>
order by snap_id, instance_number asc;

Export in parallel average execution time from AWR snapshots for SQL statement

select /+ parallel(8)/ sq.snap_id, sn.begin_interval_time, sq.sql_id, sq.executions_delta execs,
round(sq.elapsed_time_delta/power(10,6),3 ) elapsed_time_sec,
round(sq.elapsed_time_delta/power(10,6)/sq.executions_delta,3) elapsed_time_per_exec from dba_hist_sqlstat sq, dba_hist_snapshot sn
where
sq.instance_number = sn.instance_number
and sq.snap_id = sn.snap_id
and sq.dbid=<db_id> and sq.instance_number=4 and sq.sql_id='<sql_id>'
order by sq.snap_id;

row cache mutex in Oracle 18c

now revealed concurrency class wait event named “row cache mutex” over 16,87% DB Time, from ASH report it shown

Top User Events DB/Inst:  (Jul 23 01:07 to 01:09)

Event Event Class % Event Sessions

CPU + Wait for CPU CPU 54.78 128.77
row cache mutex Concurrency 16.87 39.66
direct path read User I/O 4.71 11.08
cell single block physical read User I/O 3.41 8.01
direct path read temp User I/O 1.54 3.61

and furthermore

Event % Event P1 Value, P2 Value, P3 Value % Activity

Parameter 1 Parameter 2 Parameter 3

row cache mutex 16.87 "10","19","0" 16.56
cache id where requested NOT DEFINED

finally, from

select cache#, type, parameter, count from v$rowcache where cache#=10;

CACHE#,TYPE,PARAMETER,COUNT
10,PARENT,dc_users,913

Consequently, in this particular example dc_users row cache objects is the root cause.