What is the meaning of INCREMENTAL_STALENESS in dbms_stats?

INCREMENTAL_STALENESS means "how we decide if a partition's statistics are stale".

Its values can be either USE_STALE_PERCENT or USE_LOCKED_STATS:

USE_STALE_PERCENT - a partition/subpartition is not considered as stale if DML changes are less than the
STALE_PERCENT preference value.

USE_LOCKED_STATS - locked partitions/subpartitions statistics are not considered as stale, regardless of DML changes

NULL - this is the default value, meaning a partition or subpartition is considered as stale as long as it has any DML changes

STALE_PERCENT is the value that determine the percentage of rows in a table that have to change stats before the statistics are deemed stale and should be regathered. Default = 10%


I have used the following script to implement this in one of my 12.2 database.
The last preference (DEGREE) set has nothing to do with incremental statistics, but I have found that using the default degree of parallelism is a good setting for most databases

define owner = 'SCOTT';
define table_name = 'EMP';
BEGIN
   dbms_stats.set_table_prefs('&&owner','&&table_name','INCREMENTAL','TRUE');
   dbms_stats.set_table_prefs('&&owner','&&table_name','INCREMENTAL_STALENESS','USE_STALE_PERCENT, USE_LOCKED_STATS');
   dbms_stats.set_table_prefs('&&owner','&&table_name','DEGREE','DBMS_STATS.DEFAULT_DEGREE');
END;
/

Verfiy your settings with the following script::

 
define owner = 'SCOTT';
define table_name = 'EMP';
set lines 200
set verify off
col "incremental" format a20
col "incr staleness" format a40
col "incremental level" format a10
col "stale percent" format a20
col "synopsis type" format a40
col "degree" format a30
col "granularity" format a20

select dbms_stats.get_prefs('INCREMENTAL','&&owner','&&table_name') "incremental",
        dbms_stats.get_prefs('INCREMENTAL_STALENESS','&&owner','&&table_name') "incr staleness",
        dbms_stats.get_prefs('INCREMENTAL_LEVEL','&&owner','&&table_name') "incremental level",
        dbms_stats.get_prefs('STALE_PERCENT','&&owner','&&table_name') "stale percent",
        dbms_stats.get_prefs('APPROXIMATE_NDV_ALGORITHM','&&owner','&&table_name') "synopsis type",
        dbms_stats.get_prefs('DEGREE','&&owner','&&table_name') "degree" ,
        dbms_stats.get_prefs('GRANULARITY','&&owner','&&table_name') "granularity"
FROM DUAL
/

Example output:

incremental	incr staleness	incremental level	stale percent	synopsis type	degree	granularity
TRUE	USE_STALE_PERCENT, USE_LOCKED_STATS	PARTITION	10	REPEAT OR HYPERLOGLOG	32	AUTO

Source: "Understanding Optimizer Statistics With Oracle Database 12c Release 2"

How to solve ORA-55622 during DROP USER ... CASCADE;

During a DROP USER ... CASCADE operation, you may encounter the following error:

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "SCOTT"."SYS_FBA_TCRV_83287"

Solution: disable flashback archive:

Check the currently enabled flashback archive tables:

set lines 155
col TABLE_NAME for a30
col OWNER_NAME for a30
col FLASHBACK_ARCHIVE_NAME for a30
col ARCHIVE_TABLE_NAME for a30
col STATUS for a30
SELECT * 
FROM DBA_FLASHBACK_ARCHIVE_TABLES 
WHERE OWNER_NAME=upper('SCOTT');

Put the following in a script called gen_disable_fda.sql:

set trimspool on
set heading off
set feedback off
set echo off
set verify off
spool disable_flb_archive.sql
SELECT 'spool disable_flb_archive.log' FROM dual;
SELECT 'alter table ' || owner_name || '.' || table_name || ' no flashback archive;' 
FROM DBA_FLASHBACK_ARCHIVE_TABLES 
WHERE OWNER_NAME=upper('SCOTT');
SELECT 'exit' FROM dual;
exit

The script above will generate alter table statements for all SCOTT-owned tables with flashback archive enabled:

sqlplus / as sysdba @gen_disable_fda.sql

Finally, run the script disable_flb_archive.sql, which now contain statements as shown below:

alter table SCOTT.EMP no flashback archive;

After this has been done, the schema may be dropped.

Display the current locks in a tree-like fashion

Oracle provides some built-in scripts and views that can be used to monitor locks in the database


http://docs.oracle.com/database/121/ADMIN/monitoring.htm#ADMIN11255

To illustrate this, I will open a session that updates a table, then create another session that updates the same table:

Session# 1:

select SYS_CONTEXT('userenv','con_name') "container name",
       SYS_CONTEXT('userenv','con_id') "container id",
       SYS_CONTEXT('userenv','CURRENT_SCHEMA') "Current schema",
       SYS_CONTEXT('userenv','SID') "SID"
FROM DUAL

container name       container id         Current schema                 SID
-------------------- -------------------- ------------------------------ -----
VEGDB01              3                    SCOTT                          362

--Update a the table rows:
 update emp set sal=1000;

14 rows updated.

I will keep the session alive, without doing commit/rollback.

From session# 2:

select SYS_CONTEXT('userenv','con_name') "container name",
        SYS_CONTEXT('userenv','con_id') "container id",
        SYS_CONTEXT('userenv','CURRENT_SCHEMA') "Current schema",
        SYS_CONTEXT('userenv','SID') "SID"
FROM DUAL;

container name       container id         Current schema                 SID
-------------------- -------------------- ------------------------------ -----
VEGDB01              3                    SCOTT                          130

--Update the same rows as in session# 1:
update emp set sal=2000;

This session will not give you the prompt back, as it is trying to aquire a lock on the table rows.

I then ran the utllockt.sql script:

sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/utllockt.sql

Output (abbreviated):

WAITING_SESSION   LOCK_TYPE         MODE_REQUESTED  MODE_HELD     LOCK_ID1          LOCK_ID2
----------------- ----------------- --------------- ------------ ----------------- -----------------
362               None                
   130            Transaction       Exclusive       Exclusive     524308             17836

The leftmost session (362) is blocking the sessions listed underneath it.

Let's check what the session with SID = 130 is doing:

SELECT sess.sid,sess.serial#,sess.sql_id,s.plan_hash_value, s.child_number,sess.client_info,substr(sql_text,1,30) "sql text",sess.program,sess.pq_status,
        decode(sess.blocking_session_status,'VALID','blocked', 'NO HOLDER','Not blocked') "blocked?",
        sess.blocking_session "blocking session SID",sess.event
FROM V$SESSION sess inner join v$sql s 
on (sess.sql_id = s.sql_id)
WHERE sess.SID IN (130)
and sess.sql_child_number = s.child_number;

This query confirms the output from the Oracle supplied utllockt.sql, and reveals what the blocking session is trying to do, in this case, to execute DML on table rows as that are already being locked for update:

SID	SERIAL#	SQL_ID	PLAN_HASH_VALUE	sql text	PROGRAM	PQ_STATUS	blocked?	blocking session SID	EVENT
130	9755	1gpj28ptjj43p	1494045816	update emp set sal=2000	sqlplus@	ENABLED	blocked	362	enq: TX - row lock contention

How to find the number of times a query has been executed

The following query was given to me by an experienced colleague. It shows the number of times different variations of a particular query has been executed. The information is collected from V$SQL which samples the contents in the Library Cache, a part of the Shared Pool.

SELECT SQL_ID, COUNT(DISTINCT SQL_ID) NUM_SQL_IDS, COUNT(CHILD_NUMBER) ANT_CHILD, SUM(EXECUTIONS) 
FROM V$SQL
WHERE SQL_TEXT LIKE ' select count(*) TOTALS from   scott.emp%'
GROUP BY ROLLUP(SQL_ID)
;

Here is parts of the output:

SQL_ID	NUM_SQL_IDS	ANT_CHILD	SUM(EXECUTIONS)
01b71y5wmr2bx	1	1	0
081ha7tszas19	1	3	2
0bfn4yj267xy0	1	2	34
0dk42bqfz6fbr	1	3	6
g1vq03475hqgc	1	3	10
g3hky4g8vu108	1	3	9
--------- Abbreviated ---------
	274	549	1940

From the output we can see that there are a number of variations of the query being executed. The numbers are being rolled up nicely at the end, letting us know that there are 274 unique SQL_IDs, 549 different child cursors with a total of 1940 executions.

This information can certainly be useful during an effort to tune the library cache.

one-liner for debugging script parameter output

To view the output of your script parameters, put this line in the beginning of your script:

printf "Parameters passed are : $*\n"

What are 'TCP Socket (Kgas)' Waits?

Doc ID 416451.1 "What are 'TCP Socket (Kgas)' Waits?" from Oracle support explains:

A session is waiting for an external host to provide requested data over a network socket. The time that this wait event tracks does not indicate a problem, and even a long wait time is not a reason to contact Oracle Support.

and

An application that communicates with a remote host must wait until the data it will read has arrived.
The db session cannot proceed to do anything else until the external host provides the requested data over the network socket.

The proposed "solution":

From the database point of view, these waits can safely be ignored; the wait event does not represent a database issue. It merely reports the total elapsed time for a network connection to be established or for data to arrive from over the network.


Check also Doc ID 558510.1 "WAITEVENT: "TCP Socket (KGAS)" Reference Note"

Shell script snipplet for resetting a password

This little code snipplet to use a Linux/Unix escape character to send a line shift into a string of mulitple commands:

 #!/bin/bash
. /home/oracle/.bash_profile
userid=$1
password=$2
userexist="WHENEVER SQLERROR EXIT;"
echo -e "alter session set container=PDB01;\n$userexist\nalter user $userid identified by $password account unlock;" | sqlplus -L -s / as sysdba