10 points may not be used by Oracle DBA

1) ORADEBUG SUSPEND | RESUME | WAKEUP

SUSPEND command:

This command suspends the current process. First select a process using SETORAPID or SETOSPID

Do not use SETMYPID as the current ORADEBUG process will hang and cannot be resumed even from another ORADEBUG process.

e.g.,

ORADEBUG SUSPEND

RESUME command:

resumes the current process.

While the process is suspended ORADEBUG can be used to take dumps of the current process state e.g. global area, heap, subheaps etc.

e.g.,

ORADEBUG RESUME

WAKEUP command

To wake up a process use:

ORADEBUG WAKEUP pid

For example to wake up SMON, first obtain the PID using:

SELECT pid FROM v$process

WHERE addr =

(

    SELECT paddr FROM v$bgprocess

    WHERE name = 'SMON'

);

If the PID is 6 then send a wakeup call using:

ORADEBUG WAKEUP 6

e.g.,
Suspend a running process
SQL> oradebug setorapid 12
Unix process pid: 10131, image: oracle@localhost.localdomain (TNS V1-V3)
SQL> oradebug suspend
Statement processed.

Resume a process
SQL> oradebug resume
Statement processed.

If you have trace turned on a suspended and resume session you will see the following messages in the trace file.
....
FETCH #5:c=54992,e=55962,p=0,cr=2334,cu=0,mis=0,r=1,dep=1,og=1,tim=1249098751175228
EXEC #5:c=0,e=72,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1249098751175408
Received ORADEBUG command ‘suspend’ from process Unix process pid: 22990, image:
*** 2017-12-14 21:12:34.268
Received ORADEBUG command ‘resume’ from process Unix process pid: 22990, image:
FETCH #5:c=58991,e=32316703,p=0,cr=2334,cu=0,mis=0,r=1,dep=1,og=1,tim=1249098783492125
EXEC #5:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1249098783492321
....

3) SQL Full Text: Which view I will use? v$sql | v$sqlarea | v$sqlstats

The column definition for columns in V$SQLSTATS are identical to those in the V$SQL and V$SQLAREA views. However, the V$SQLSTATS view differ from V$SQL and V$SQLAREA in that it is faster, more scalable, and has a greater data retention (the statistics may still appear in the view, even after the cursor has been aged out of the shared pool).

3) DESC command can be used inside SQL query to view dynamic result instead of cancel the whole query and re-write:

e.g.,

SQL> select TABLE_NAME,INDEX_NAME,LAST_ANALYZED

  2  from dba_indexes

  3  where

  4  #desc dba_indexes

 Name                                                              Null?    Type

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

 OWNER                                                             NOT NULL VARCHAR2(128)

 INDEX_NAME                                                        NOT NULL VARCHAR2(128)

 INDEX_TYPE                                                                 VARCHAR2(27)

 TABLE_OWNER                                                       NOT NULL VARCHAR2(128)

 TABLE_NAME                                                        NOT NULL VARCHAR2(128)

 TABLE_TYPE                                                                 VARCHAR2(11)

 LAST_ANALYZED                                                              DATE

 DEGREE                                                                     VARCHAR2(40)

…

  4  OWNER='SCOTT';

TABLE_NAME                    INDEX_NAME                                    LAST_ANALYZED

EMP                                       empid_pk                                           07-MAR-17

DEPT                                      deptno_pk                                         07-MAR-17

…..                                         

SQL>

4) Transaction commit when exiting SQL*Plus

if the case of exit without commit from sqlplus, the running transaction commit or rollback??

Answer will be Yes|no. It depends upon what the exitcommit parameter is set to!

To see current setting:

e.g.,

SQL> sho exitcommit

exitcommit ON                  (default)

To OFF:

SET EXITCOMMIT OFF;  ( this makes you “rollback” on exit)

5) Estimating Cardinalities using GATHER_PLAN_STATISTICS hint ( Oracle 10g & above)

If we add the GATHER_PLAN_STATISTICS hint to our simple SQL statement we should be able to see the actual cardinality of each operation at execution time alongside the Optimizer estimates for each cardinality in the plan.

e.g.,

SELECT     /*+ GATHER_PLAN_STATISTICS */ p.prod_name, SUM(s.quantity_sold)

FROM       sales s, products p

WHERE      s.prod_id =p.prod_id

AND        p.prod_desc = 'Consumables'

GROUP By p.prod_name ;

…..

SQL> SELECT *

FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));

The FORMAT parameter value 'ALLSTATS LAST' requests that the execution statistics of the last execution of the cursor be shown.

In order to see the A-Rows values for all of the operations in the plan you would have to use the FORMAT value  'ALLSTATS ALL', which will show you the execution statistics for ALL executions of the cursor. Be careful here as the values in the A-Rows column is now cumulative (incremented on every execution). The more the statement is executed the higher the values in the A-Rows column will become. This FORMAT will also show you a lot more columns in the execution plan.

6. Create index with COMPRESS:

People have often commented to me that Table Compression (and Advanced Row Compression) are  great at helping to reduce the storage footprint of an Oracle database by a factor of 2x-5x, but Oracle shouldn’t forget about the indexes.

In my experience, indexes often take up to 50% of the total database space and it is not uncommon to have 10-20 indexes on a single table (in extreme cases, It may 100 indexes per table).

Syntax:

CREATE INDEX idx_name ON table_name(col1, col2, col3) COMPRESS [<prefix_col_length>] ;

e.g.,

create index idx_test product(product_type,product_color,manufacture_date) compress 2;

The <prefix_col_length> after the COMPRESS keyword denotes how many columns to compress. The default (and the maximum) prefix length for a non-unique index is the number of key columns, and the maximum prefix length for a unique index is the number of key columns minus one.

Prior to Oracle 11g, it is risk if prefix columns are defined properly. You may have following issues:

a)       there is a small CPU overhead to reconstruct the key column values during index lookup or scans

b)      running ANALYZE INDEX takes an exclusive lock on the table, effectively making the table “offline” for this period

c)       Not useful if prefix columns are unique in a block

d)      You cannot compress your Functional Indexes with Advanced Index Compression.

e)      Bitmap indexes can't be compressed.

On partition Table:

CREATE INDEX my_test_idx ON test(a, b) COMPRESS ADVANCED LOW local

    (PARTITION p1 COMPRESS ADVANCED LOW,

     PARTITION p2 COMPRESS,

     PARTITION p3,

 PARTITION p4 NOCOMPRESS);

7. _use_nosegment_indexes parameter and nosegment keyword:

In contrast to conventional indexes, a virtual index has no associated segment, so the creation time and associated disk space are irrelevant. In addition, it is not seen by other sessions, so it doesn't affect the normal running of your system. This article presents a simple example of how virtual indexes are used.

A virtual index is a "fake" index whose definition exists in the data dictionary, but has no index tress association.  It is used by Oracle developers to test whether a specific index is going to use useful without having to use the disk space associated with the "real" index.  The hidden parameter _use_nosegment_indexes is used by Quest tools and is also use in the Oracle Tuning pack.

Because the virtual index is a fake index, Oracle will never be able to use it, but you can use the hidden _use_nosegment_indexes parameter to evaluate execution plans for virtual indexes:

SQL> set autotrace on explain;

SQL> alter session set "_use_nosegment_indexes" = true;

Session altered.

SQL> select ename from emp where ename = 'MILLER';

ENAME

-----

MILLER

1 rows selected.

OBJECT     OPERATION

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

                 SELECT STATEMENT()

                  NESTED LOOPS()

EMP                TABLE ACCESS(FULL)

EMP                 TABLE ACCESS(BY INDEX ROWID)

VIRTUAL_INDEX         INDEX(UNIQUE SCAN)

Creating virtual indexes:

The CREATE INDEX statement has a special hidden "nosegment" clause that allows one to create an index definition without actually creating the index.

To build a virtual index, runs the following statement:

CREATE unique INDEX virt_ind_name on table_name(col_name) NOSEGMENT;

Removing virtual indexes:

It is important to drop the index after you're done with it.

e.g.,

DROP INDEX virt_ind_name;

8. Create Tigger with DISABLE : (Oracle 11g onwards)

Prior to Oracle 11g, disabling triggers used to be a two step process. Triggers were automatically created in an ‘enabled’ state. To disable it we had to disable it using the DISABLE clause of the ‘ALTER TRIGGER’ or ‘ALTER TABLE ‘ commands.

In Oracle 11g, we can specify ‘DISABLE’ clause in trigger definition it self.  In the absence of the ‘DISABLE’ clause, the trigger is created in an ‘ENABLED’ state. Following is the script to create table and then create trigger in disabled state.

CREATE TABLE TEST

(

COL1 VARCHAR(5) NOT NULL,

COL2 NUMBER(5),

COL3 DATE

);

Let us create a trigger now.

CREATE OR REPLACE TRIGGER TRI_TEST

BEFORE INSERT ON TEST

FOR EACH ROW

DISABLE

BEGIN

:NEW.COL3 := SYSDATE;

END;

Trigger will be created successfully.  We can verify the status of the trigger using data dictionary view user_triggers. Even though trigger is created in a disabled status,  Oracle will make sure that it does not have any compilation errors. Trigger will be compiled with errors if we try to reference non-existent column in the trigger.

This feature can be very useful when we want to enable trigger at later stage for a specific events and as mentioned earlier, we can avoid extra step of altering the trigger after creation.

9. Export direct =y ( EXPDP direct=y …)

The speedup of the direct path export can be large however. The 10% of the processing that it cuts out, accounts for a much larger percentage of the run-time. For example, I have just exported about 100meg of data, and 1.2 million records. The direct path export took about one minute. The conventional path export on the other hand took three minutes.

Setting direct=true bypasses the SGA, so it will be faster. Help full in case mission critical/ OLTPs environments.

10. SQL*Plus Error Logging

Suppose you have a SQL script called myscript.sql:

set puase on

set lines 132 pages 0 trimsppol on

select * from nonexistent_table

/

Note there are several errors in the script: the first line has "pause" misspelled, the second line has "trimspool" misspelled, and finally the third line has a select statement from a table that does not even exist. When you run the script via SQL*Plus prompt, unless you spooled the output, you will not be able to check the error afterward. Even if you spooled, you would have access to the physical server to examine the spool file, which may not be possible.

Oracle Database 11g has a perfect solution: Now you can log the errors coming from SQL*Plus on a special table. You should issue, as a first command:

SQL> set errorlogging on

Now you run the script:

SQL> @myscript

The run will produce the following error messages:

SP2-0158: unknown SET option "puase"

SP2-0158: unknown SET option "trimsppol"

select * from nonexistent_table

              *

ERROR at line 1:

ORA-00942: table or view does not exist

which you may or may not have seen, depending on how you ran the script—in the foreground from an SQL*Plus prompt or in the background as a script invocation. After the script completes, you can log into the database and check the errors in a table named SPERRORLOG.

sql> col timestamp format a15

sql> col username format a15

sql> col script format a10

sql> col identifier format a15

sql> col statement format a20

sql> col message format a20

sql> select timestamp, username, script, statement, message

  2> from sperrorlog;

Note that you checked the error from a different session, not the session where the script was run. In fact the script has finished and the session has been terminated anyway. This gives you a powerful ability to check errors after they occurred in SQL*Plus sessions that were impossible, or at least difficult, to track otherwise.

The table SPERRORLOG is a special table that is created for this purpose only. You can also create your own table and populate that with errors from SQL*Plus. This table should be created as:

SQL> create table my_sperror_log

  2  (

  3     username varchar2(256),

  4     timestamp       timestamp,

  5     script          varchar2(1024),

  6     identifier      varchar(256),

  7     message         clob,

  8     statement       clob

  9  )

 10  /

Table created.

Now you can use this table in error logging instead of the default table.

SQL> set errorlogging on table sh.my_sperror_log;

SQL> @myscript

Now MY_ERROR_LOG (and not SPERRORLOG) will hold the error log. You can truncate all the rows in the table by issuing

SQL> set errorlogging on truncate

There is an optional IDENTIFIER clause that allows you to tag the errors from specific sessions. Suppose you issue the command:

SQL> set errorlogging on identifier MYSESSION1

Now if you run the script, the records will be created with the column called IDENTIFIER populated with the value MYSESSION1. You can extract those records only by issuing the query:

select timestamp, username, script, statement, message

from sperrorlog

where identifier = 'MYSESSION1';

You will see the records from that session only. This is very useful if you are trying to isolate errors in multiple scripts and sessions.