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.
