How to execute and capture an SQL statement in the SMB at the same time

An experienced co-worker showed me this neat trick to execute an SQL statement and capture its execution plan in the SQL Plan Management Base at the same time.

/* First, select the bind variables used in the SQL statement you're interested in, into the variable value_string.
Record them in a text file. You will be using them later
*/
SELECT DECODE (value_string, 'NULL,', ''''',', value_string)
  FROM (  SELECT DECODE (datatype_string,
                         'VARCHAR2(32)', '''' || value_string || ''',',
                         value_string || ',')
                    value_string
            FROM v$sql_bind_capture
           WHERE sql_id = '4d6sdzm63st1u' AND child_number = 1
        ORDER BY position);

/*
Get the SQL text including the bind usage (but not the bind variable values)
*/
SET SERVEROUTPUT ON

DECLARE
   l_sql      VARCHAR2 (32000);
   l_sql_id   VARCHAR2 (30) := '4d6sdzm63st1u';
BEGIN
   BEGIN
      SELECT sql_fulltext
        INTO l_sql
        FROM v$sql                                          
       WHERE sql_id = l_sql_id AND ROWNUM < 2;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         SELECT sql_text
           INTO l_sql
           FROM dba_hist_sqltext
          WHERE sql_id = l_sql_id AND ROWNUM < 2;
   END;

   DBMS_OUTPUT.put_line (l_sql);

/* Capture the baseline for this statement */ 
   EXECUTE IMMEDIATE
      'alter session set optimizer_capture_sql_plan_baselines=true';

/* Send the SQL including the bind variable values to the database */
   EXECUTE IMMEDIATE l_sql
      USING 5022000,
            9834,
            9822,
            9,
            4,
            6;
/* Turn the capture off again */
   EXECUTE IMMEDIATE
      'alter session set optimizer_capture_sql_plan_baselines=false';
END;
/

How to match an exact string using grep

grep -w string

See it in use here

How to remove unwanted parameters from init.ora using a shell script

1. Create a "removal list" containing your unwanted parameters. For example, put the following into a text file called removal_list.txt, and save it in $ORACLE_HOME/dbs:

_b_tree_bitmap_plans
_fast_full_scan_enabled
_grant_secure_role
_like_with_bind_as_equality
_projection_pushdown

2. create a Shell script to loop through the lines in remval_list.txt, called remove_params.sh:

for a in $(cat removal_list.txt ); do
 echo now testing $a
 sleep 1
 curr_param=`grep -w $a init${ORACLE_SID}.ora | awk -F '[=]' '{ print $1 }'`
 if [ $curr_param ]; then
  echo removing $curr_param
  sed -i "/${curr_param}/d" init${ORACLE_SID}.ora
  sleep 1
 else
  echo $a not found in parameter file. Skipping...
  sleep 1
 fi;
done
exit 0

It is important to use the -w flag with grep, as many Oracle initialization parameters can be named similarly and the script will then save multiple values in the curr_param variable

3. Make Your script executable:

chmod 755 remove_params.sh

4. execute the script:

./remove_params.sh

Expected output:

now testing _b_tree_bitmap_plans
removing _b_tree_bitmap_plans
now testing _fast_full_scan_enabled
removing _fast_full_scan_enabled
now testing _grant_secure_role
_grant_secure_role not found in parameter file. Skipping...
now testing _like_with_bind_as_equality
removing _like_with_bind_as_equality
now testing _projection_pushdown
_projection_pushdown not found in parameter file. Skipping...

Basic compression, advanced compression, HCC - an overview

Basic Compression:

Basic compression was released in Oracle 9iR2.It is part of the Enterprise Edition and comes at no extra licencing cost.

If you specify only the keyword COMPRESS, it is equivalent to specifying ROW STORE COMPRESS BASIC and enables basic table compression.

Basic Compression
CREATE TABLE SCOTT.test ( id NUMBER, fname VARCHAR2(32), ename VARCHAR2(32), added_date DATE ) TABLESPACE USERS COMPRESS;

The keyword COMPRESS is kept for backward compability, but the current preferred syntax is

ROW STORE COMPRESS

or

ROW STORE COMPRESS BASIC

Basic compression does not apply compression to DML operations after the initial load.
With basic compression, Oracle will attempt to compress data during direct-path INSERT operations, but not during conventional-path INSERT operations. Make sure you fullfil the requirements for direct path inserts.

Advanced Compression:

Avanced compression was released in Oracle 11gR1 in 2007, and was called "OLTP Table Compression" at the time. You need to purchace the Advanced Compression Option from Oracle to use it.

When you enable table compression by specifying ROW STORE COMPRESS ADVANCED, you enable Advanced Row Compression. Oracle Database compresses data during all DML operations on the table. This form of compression is recommended for OLTP environments.

Advanced Compression
CREATE TABLE SCOTT.test ( id NUMBER, fname VARCHAR2(32), ename VARCHAR2(32), added_date DATE ) TABLESPACE USERS ROW STORE COMPRESS ADVANCED;

In earlier releases, Advanced Row Compression was called OLTP table compression and was enabled using COMPRESS FOR OLTP. This syntax is still supported for backward compatibility.

Hybrid Columnar Compression

For certain storage systems, Oracle has introduced Hybrid Columnar Compression, or HCC.


COLUMN STORE COMPRESS FOR QUERY is useful in data warehousing environments. Valid values are LOW and HIGH, with HIGH providing a higher compression ratio. The default is HIGH:

HCC Compression - non-dormant data
CREATE TABLE SCOTT.test ( id NUMBER, fname VARCHAR2(32), ename VARCHAR2(32), added_date DATE ) TABLESPACE USERS COLUMN STORE COMPRESS FOR QUERY HIGH | LOW;

COLUMN STORE COMPRESS FOR ARCHIVE uses higher compression ratios than COLUMN STORE COMPRESS FOR QUERY, and is useful for compressing data that will be stored for long periods of time. Valid values are LOW and HIGH, with HIGH providing the highest possible compression ratio. The default is LOW:

HCC Compression - dormant data
CREATE TABLE SCOTT.test ( id NUMBER, fname VARCHAR2(32), ename VARCHAR2(32), added_date DATE ) TABLESPACE USERS COLUMN STORE COMPRESS FOR ARCHIVE HIGH | LOW;

If you attempt to create a table With HCC on a non-supported Storage system, you will receive the following error Message:

ORA-64307:  Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type

Sources: Create Table

Oracle Advanced Compression With Oracle 12cR2 - excellent document

How to find the number of full table scans

From the Oracle Documentation:

The view DBA_HIST_SEG_STAT view displays historical information about segment-level statistics. This view captures the top segments based on a set of criteria and captures information from V$SEGSTAT. The total value is the value of the statistics since instance startup. The delta value is the value of the statistics from the BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME in the DBA_HIST_SNAPSHOT view.

 SELECT O.OBJECT_NAME,O.OWNER, SUM(S.TABLE_SCANS_TOTAL) "# of FTS",(SELECT NUM_ROWS FROM DBA_TABLES T WHERE T.TABLE_NAME = O.OBJECT_NAME) "num rows total"
 FROM DBA_HIST_SEG_STAT S JOIN DBA_OBJECTS O ON (S.OBJ# = O.DATA_OBJECT_ID)
 WHERE O.OWNER NOT IN ( 'SYS','DBSNMP','XDB')
 AND O.OBJECT_TYPE = 'TABLE'
 GROUP BY S.OBJ#,O.OBJECT_NAME,O.OWNER
 ORDER BY 4 DESC;

OBJECT_NAME	OWNER	# of FTS	num rows total
TABLE1	USER1	5702	1583895743
TABLE2	USER1	11	588921781
TABLE3	USER1	6195	241550246

As can be seen from above, the extreme number of rows in TABLE1, coupled with 5702 full table scans, should bring it to your attention as a candidate for partitioning!

How to purge objects from the recycle bin

sqlplus / as sysdba
purge dba_recyclebin; --> purges all recyclebin objects, globally
purge tablespace user_data user scott; --> purge only scott's Objects from the user_data tablespace
purge tablespace user_data; --> purge all recyclebin Objects from the user_data tablespace

conn scott/tiger
purge recyclebin; --> purges scott's recycle bin only

The next statement purges the specified object from the recycle bin. The first example refers directly to the system-generated name of the object, the second is refering to the original name of the object.

purge table "BIN$W/n28BqBgRbgU64JAQoz3A==$0";
purge table emp; 

What is the Adaptive SQL Plan Management introduced in Oracle 12c?

By "Adaptive SQL Plan Management", Oracle is referring to a new autotask that is executed every night during the maintenance window, as a part of the "sql tuning advisor".

It will automatically evolve all non-accepted plans in the SQL Plan Management Base.
If the New plans improves the performance of the Query, they will automatically be promoted to an accepted plan.

In previous versions of Oracle, plans needed to be evolved manually by the DBA by running dbms_spm.evolve_sql_plan_baselines.

Is the sql tuning task enabled?

SELECT CLIENT_NAME, STATUS
FROM   DBA_AUTOTASK_CLIENT
WHERE  CLIENT_NAME = 'sql tuning advisor';

View details about a specific run:

SELECT OWNER,TASK_ID, TASK_NAME,DESCRIPTION,ADVISOR_NAME,EXECUTION_TYPE,STATUS,RECOMMENDATION_COUNT "num rec",EXECUTION_START,EXECUTION_END,LAST_EXECUTION,HOW_CREATED
FROM  DBA_ADVISOR_TASKS 
WHERE TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK';

OWNER	TASK_ID	TASK_NAME	DESCRIPTION	ADVISOR_NAME	EXECUTION_TYPE	STATUS	num rec	EXECUTION_START	EXECUTION_END	LAST_EXECUTION	HOW_CREATED
SYS	2	SYS_AUTO_SPM_EVOLVE_TASK	Automatic SPM Evolve Task	SPM Evolve Advisor	SPM EVOLVE	COMPLETED	0	13.11.2017 23:00:06	13.11.2017 23:00:07	EXEC_20606	AUTO

If the parameter accepted_plans is set to TRUE, it means plans will be automatically evolved:

SELECT PARAMETER_NAME,PARAMETER_VALUE
FROM DBA_ADVISOR_PARAMETERS 
WHERE TASK_NAME LIKE 'SYS_AUTO_SPM_EVOLVE_TASK'
AND PARAMETER_NAME='ACCEPT_PLANS';

PARAMETER_NAME	PARAMETER_VALUE
ACCEPT_PLANS	TRUE

If you want to view the results of such an automatic job, use the following code:

SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 100 LINESIZE 100
SELECT DBMS_SPM.report_evolve_task(task_name => 'SYS_AUTO_SPM_EVOLVE_TASK', execution_name => 'EXEC_20606') AS output
FROM dual;