Why my delete is not running in parallel?

Sometimes is really faster of the delete statement is running in parallel; not only to identify the rows to be deleted, but the delete itself.

 2 observations related to this:

1) Deleting from a table partition, it seems that the alias is not being translated properly, so instead I added a where clause, to access the required partition.

2) Adding 2 where clauses didn't help, but adding a between clause, did the trick.

Example of a query which will NOT do the delete in parallel:


delete /*+ parallel(a,16) full(a)  */
from MYTAB  partition (PR001) a
where expiration_date <  add_months( trunc(sysdate), -85 );

Example of a fixed query, which will run in parallel:

delete /*+ parallel(a,16) full(a)  */

from MYTAB a 

where expiration_date <  add_months( trunc(sysdate), -85 )

and ID between 2500 and 37500;

Notes:

1. ALTER SESSION ENABLE PARALLEL DML; is still required.
2. To check that the delete is running in parallel, the below query should return more than 1 row:


select a.SID,a.USERNAME,b.xidusn,b.used_urec,b.used_ublk
from  v$session a,v$transaction b
where a.SADDR=b.ses_addr
/

ORA-28040: No matching authentication protocol, when connecting to a 12c DB

The issue:

 >sqlplus myuser/mypass@MY12CDB

SQL*Plus: Release 11.2.0.2.0 Production on Fri May 11 10:22:44 2018

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-28040: No matching authentication protocol


 Cause: connecting to a 12c DB, with a client lower than 11.2.0.3

 Solution:

 Add the following to $ORACLE_HOME/network/admin/sqlnet.ora , for both versions:

 SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
 SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

Note: it's important to modify the sqlnet.ora in the above mentioned location.

Which Red Hat version my server is?

cat /etc/redhat-release

A nice detailed link:

https://www.cyberciti.biz/faq/what-version-of-redhat-linux-am-i-running/

How to sort lines in Linux, starting with a specific character position, using "sort"

Let's say we have an file with a list of DB names:

$cat my_file.txt
BMLCRM12
BMLCRM98
BMLCRM99
BMSCRM11
BMSCRM13
BMSCRM15
BMSCRM19
BMSCRM23
BMSCRM24
BMSCRM25

The goal is to sort them out in order, ignoring the first 6 characters.

Solution:

$cat my_file.txt |sort -n -k 1.6,1.8

BMSCRM11

BMLCRM12

BMSCRM13

BMSCRM15

BMSCRM19

BMSCRM23

BMSCRM24

BMSCRM25

BMLCRM98

BMLCRM99

How to color (add) SQLs to the AWR reports?

I just found a very good blog explaining this:

http://oracleprof.blogspot.ca/2011/06/how-to-color-mark-sql-for-awr-snapshots.html


Sometimes, we have a problematic query which runs not that often or not that long to be included in the AWR reports, but we are still interested in see the statistics of this query. So the answer is to "color" this specific query, by providing the SQL_ID.

How to color the query:

exec dbms_workload_repository.add_colored_sql('6zfggtprazcvb');

How to check that it was added to the list:

select * from DBA_HIST_COLORED_SQL;

How to remove the query (uncolor):


exec dbms_workload_repository.remove_colored_sql('6zfggtprazcvb');





How to get a list of the tables whose statistics are locked?

The below query will do it:

SELECT TABLE_NAME FROM DBA_TAB_STATISTICS WHERE STATTYPE_LOCKED = 'ALL';

What exactly is being audited in the DB?

A very nice site, with a clear explanation:

http://www.acehints.com/2012/12/how-to-check-what-is-getting-being.html

Below mentioned 3 data dictionary views can be used to fetch the details of the auditing.

1. dba_obj_audit_opts: Data dictionary view will give the details of auditing options on all objects.user_obj_audit_opts view will provide the details of the auditing enabled on the objects on the particular user session connected

2. dba_priv_audit_opts: Data dictionary view describes the current system privileges being audited across the database and by the user. The column username can be used to find the details user wise. The column value will be NULL for system-wide auditing

Example:

SQL> select * from DBA_PRIV_AUDIT_OPTS

SQL> /

USER_NAME  PROXY_NAME PRIVILEGE                      SUCCESS    FAILURE

---------- ---------- ------------------------------ ---------- ----------

                      CREATE EXTERNAL JOB            BY ACCESS  BY ACCESS

                      CREATE ANY JOB                 BY ACCESS  BY ACCESS

                      GRANT ANY OBJECT PRIVILEGE     BY ACCESS  BY ACCESS

                      EXEMPT ACCESS POLICY           BY ACCESS  BY ACCESS

3. dba_stmt_audit_opts: Data dictionary view describes the current system auditing options across the database and by the user. The column username can be used to find the details user wise. The column value will be NULL for system-wide auditing

Example

SQL> select * from dba_stmt_audit_opts;

USER_NAME  PROXY_NAME AUDIT_OPTION                             SUCCESS    FAILURE

---------- ---------- ---------------------------------------- ---------- ----------

                      PROFILE                                  BY ACCESS  BY ACCESS

                      ROLE                                     BY ACCESS  BY ACCESS

                      DATABASE LINK                            BY ACCESS  BY ACCESS

                      PUBLIC SYNONYM                           BY ACCESS  BY ACCESS