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

Workaround for ORA-46372: audit file not found

In my environment, I had the following errors popping up in Cloud Control:

ORA-46372: audit file '/u01/oracle/audit/proddb01/ora_audit_10.bin' not found
ORA-17503: ksfdopn:4 Failed to open file
/u01/oracle/audit/proddb01/ora_audit_10.binin
ORA-17500: ODM err:File does not exist

It can be manually reproduced by executing the following query:

select count(1) from unified_audit_trail;

It seems to be related to BUG 26352615 - SPILLOVER AUDIT FILES CANNOT BE FOUND ORA-46372 for platform Linux x86-64. Per May 22ond 2018, it is still in development according to Oracle Support.

In the directory /u01/oracle/audit/proddb01, there is quite correctly no file named ora_audit_10.binin. However, there is one called ora_audit_10.bin

The following workaround worked for me:

Go to the directory /u01/oracle/audit/proddb01
Change the name of the file ora_audit_10.bin to ora_audit_10.binin

Test like this:

select count(1) from unified_audit_trail;

  COUNT(1)
----------
        25

Update 09.03.2020:

Oracle has released a patch for 26352615. It is available for a number of RUs. I just applied the patch for 12c Release 12.2.0.1.200114DBJAN2020RU, and it solved the problem outlined above.

Partitioned index properties

The following query will reveal information about the indexes on a partitioned table.

Very useful when you want to investigate if a partitioned table is correctly and efficiently indexed:

SELECT C.TABLE_NAME,I.INDEX_NAME,I.PARTITIONING_TYPE "part type", I.SUBPARTITIONING_TYPE "sub type", I.LOCALITY,I.ALIGNMENT,C.COLUMN_NAME,C.COLUMN_POSITION "col pos", I.INTERVAL,I.AUTOLIST, II.VISIBILITY
FROM DBA_PART_INDEXES I JOIN DBA_IND_COLUMNS C
ON (I.INDEX_NAME = C.INDEX_NAME)
 JOIN DBA_INDEXES II
   ON (II.INDEX_NAME = I.INDEX_NAME)
WHERE I.TABLE_NAME IN (SELECT TABLE_NAME FROM dba_tables WHERE PARTITIONED='YES' and table_name='MYTABLE')
ORDER BY I.INDEX_NAME,C.COLUMN_POSITION;

In my case, the results were:

TABLE_NAME	INDEX_NAME	part type	sub type	LOCALITY	ALIGNMENT	COLUMN_NAME	col pos	INTERVAL	AUTOLIST	VISIBILITY
MYTABLE	MYTABLE_SI1	RANGE	NONE	LOCAL	PREFIXED	CREATED_DAY	1	NUMTOYMINTERVAL(1,'MONTH')	NO	VISIBLE
MYTABLE	MYTABLE_SI1	RANGE	NONE	LOCAL	PREFIXED	SEQ_NUM	2	NUMTOYMINTERVAL(1,'MONTH')	NO	VISIBLE
MYTABLE	MYTABLE_SI2	RANGE	NONE	LOCAL	NON_PREFIXED	SUBMITTED_DATE	1	NUMTOYMINTERVAL(1,'MONTH')	NO	VISIBLE

How do I find which objects that were analyzed during an autostats job?

Step 1

Find the start and stop times of the maintenance window:

select window_name, cast(last_start_date as date) "Start Time", DURATION, cast(last_start_date+duration as date) "End Time"
FROM DBA_SCHEDULER_WINDOWS
WHERE ENABLED='TRUE'
AND WINDOW_NAME LIKE UPPER('%&&day%');

Which results in:

WINDOW_NAME	Start Time	DURATION	End Time
WEDNESDAY_WINDOW	18.04.2018 23:59:00	+00 04:00:00.000000	19.04.2018 03:59:00

So my Wednesday window starts at 23:59 and ends 4 hours later, at 03:59.

Step 2
Add the query above as a subquery in a query against DBA_OPTSTAT_OPERATIONS, to get all the IDs for the sub-steps that was executed during this window:

select ID
from DBA_OPTSTAT_OPERATIONS 
where operation='gather_database_stats (auto)' 
and CAST(start_time AS DATE) BETWEEN 
                                ( SELECT CAST (LAST_START_DATE AS DATE)
                                  FROM  DBA_SCHEDULER_WINDOWS
                                  WHERE ENABLED='TRUE'
                                  AND   WINDOW_NAME LIKE UPPER('%&&day%')
                                 )
                              AND 
                                 (
                                  SELECT CAST (LAST_START_DATE+DURATION AS DATE)
                                  FROM  DBA_SCHEDULER_WINDOWS
                                  WHERE ENABLED='TRUE'
                                  AND   WINDOW_NAME LIKE UPPER('%&&day%')
                                 )
;

Step 3
Put everything together by querying DBA_OPTSTAT_OPERATION_TASKS, to see which tasks and which objects that were analyzed during the time window:

-- get the details for every task executed by "gather_database_stats (auto)" 
SELECT opid,
       target,
       target_objn,
       target_type,
       target_size,
       cast(start_time as date) "Start Time",
       cast(end_time as date) "End Time", 
       status
FROM DBA_OPTSTAT_OPERATION_TASKS 
WHERE OPID IN ( -- get the unique ID for the "gather_database_stats (auto)" jobs
                SELECT ID FROM DBA_OPTSTAT_OPERATIONS 
                WHERE OPERATION='gather_database_stats (auto)' 
                AND CAST (START_TIME AS DATE) BETWEEN 
                                                -- get the start and stop times for the last window
                                                -- for a particular weekday
                                                (SELECT CAST(LAST_START_DATE AS DATE)
                                                      FROM DBA_SCHEDULER_WINDOWS
                                                      WHERE ENABLED='TRUE'
                                                      AND WINDOW_NAME LIKE UPPER('%&&day%')
                                                     )
                                                AND (
                                                      SELECT CAST(LAST_START_DATE+DURATION AS DATE)
                                                      FROM DBA_SCHEDULER_WINDOWS
                                                      WHERE ENABLED='TRUE'
                                                      AND WINDOW_NAME LIKE UPPER('%&&day%')
                                                     )
)
order by CAST(start_time as DATE) asc;

Sample output (displaying the first and last 5 rows of the record set):

OPID	TARGET	TARGET_OBJN	TARGET_TYPE	TARGET_SIZE	Start Time	End Time	STATUS
48848	SYS.ET$16E30DF80001	6177051	TABLE	0	18.04.2018 23:59:30	18.04.2018 23:59:30	FAILED
48848	SYS.I_SEQ1	79	INDEX	1	18.04.2018 23:59:30	18.04.2018 23:59:31	COMPLETED
48848	SYS.SEQ$	74	TABLE	7	18.04.2018 23:59:30	18.04.2018 23:59:31	COMPLETED
48848	SYS.OPTSTAT_HIST_CONTROL$	506	TABLE	1	18.04.2018 23:59:31	18.04.2018 23:59:31	COMPLETED
48848	SYS.REG$	5775	TABLE	1	18.04.2018 23:59:31	18.04.2018 23:59:31	COMPLETED
...
48885	SYS.WRH$_SERVICE_STAT.WRH$_SERVICE_STAT_2490506650_826	11986128	TABLE PARTITION	5	19.04.2018 02:00:48	19.04.2018 02:00:48	COMPLETED
48885	SYS.WRH$_SEG_STAT_PK.WRH$_SEG_STAT_2490506650_826	11986126	INDEX PARTITION	1	19.04.2018 02:00:48	19.04.2018 02:00:48	COMPLETED
48885	SYS.WRH$_SGASTAT_U.WRH$_SGASTAT_2490506650_826	11986114	INDEX PARTITION	1	19.04.2018 02:00:48	19.04.2018 02:00:48	COMPLETED
48885	SYS.WRH$_SEG_STAT.WRH$_SEG_STAT_2490506650_826	11986124	TABLE PARTITION	5	19.04.2018 02:00:48	19.04.2018 02:00:48	COMPLETED
48885	SYS.WRH$_SEG_STAT.WRH$_SEG_STAT_2490506650_826	11986124	TABLE PARTITION	5	19.04.2018 02:00:48	19.04.2018 02:00:48	COMPLETED

Check the overall status of your run:

SELECT target_type,status,count(*)
FROM DBA_OPTSTAT_OPERATION_TASKS 
WHERE OPID IN ( -- get the unique ID for the "gather_database_stats (auto)" jobs
                SELECT ID FROM DBA_OPTSTAT_OPERATIONS 
                WHERE OPERATION='gather_database_stats (auto)' 
                AND CAST(START_TIME AS DATE) BETWEEN 
                                                -- get the start and stop times for the last window
                                                -- for a particular weekday
                                                (SELECT CAST(LAST_START_DATE AS DATE)
                                                      FROM DBA_SCHEDULER_WINDOWS
                                                      WHERE ENABLED='TRUE'
                                                      AND WINDOW_NAME LIKE UPPER('%&&day%')
                                                     )
                                                AND (
                                                      SELECT CAST(LAST_START_DATE+DURATION AS DATE)
                                                      FROM DBA_SCHEDULER_WINDOWS
                                                      WHERE ENABLED='TRUE'
                                                      AND WINDOW_NAME LIKE UPPER('%&&day%')
                                                     )
)
group by target_type,status
order by status;

Result:

TARGET_TYPE	STATUS	COUNT(*)
INDEX	COMPLETED	501
INDEX PARTITION	COMPLETED	177
TABLE	COMPLETED	394
TABLE PARTITION	COMPLETED	362
TABLE	FAILED	54

To further filter out those objecs that failed, use:

SELECT distinct target,target_objn,target_type
FROM DBA_OPTSTAT_OPERATION_TASKS 
WHERE OPID IN ( -- get the unique ID for the "gather_database_stats (auto)" jobs
                SELECT ID FROM DBA_OPTSTAT_OPERATIONS 
                WHERE OPERATION='gather_database_stats (auto)' 
                AND CAST(START_TIME AS DATE) BETWEEN 
                                                -- get the start and stop times for the last window
                                                -- for a particular weekday
                                                (SELECT CAST(LAST_START_DATE AS DATE)
                                                      FROM DBA_SCHEDULER_WINDOWS
                                                      WHERE ENABLED='TRUE'
                                                      AND WINDOW_NAME LIKE UPPER('%&&day%')
                                                     )
                                                AND (
                                                      SELECT CAST(LAST_START_DATE+DURATION AS DATE)
                                                      FROM DBA_SCHEDULER_WINDOWS
                                                      WHERE ENABLED='TRUE'
                                                      AND WINDOW_NAME LIKE UPPER('%&&day%')
                                                     )
)
AND status = 'FAILED'
order by TARGET;

Which in my case revealed that the following objects need attention:

TARGET	TARGET_OBJN	TARGET_TYPE
SYS.ET$16E30DF80001	6177051	TABLE
SYS.X$LOGMNR_CONTENTS	4294951535	TABLE
SYS.X$LOGMNR_REGION	4294951631	TABLE