November | 2008 | EveryDay Practical Solutions for an Oracle|SQL Server DBA

Flashback Query

Oracle Flashback Query allows users to see a consistent view of the database as it was at a point in the past. This functionality allows comparative reporting over time and recovery from logical corruptions.

Prerequisites

Oracle Flashback Query can only be used if the server is configured to use Automatic Undo Management, rather than traditional rollback segments. The maximum time period that can be flashbacked to is defined using the UNDO_RETENTION parameter in the init.ora file. Alternatively, this parameter can be set using:

ALTER SYSTEM SET UNDO_RETENTION = <seconds>;

Using Flashback Query

Flashback Query is enabled and disabled using the DBMS_FLASHBACK package. The point in time of the flashback can be specified using the SCN or the actual time:

EXECUTE Dbms_Flashback.Enable_At_System_Change_Number(123);
EXECUTE Dbms_Flashback.Enable_At_Time(’28-AUG-01 11:00:00′);

Once you’ve finished performing all your read-only operations you can turn off flashback query using:

EXECUTE Dbms_Flashback.Disable;

The flashback query is turned off when the session ends if there is no explicit call to the disable procedure. The current system change number can be returned using the Get_System_Change_Number function.
Example Data Recovery
Assuming all data was accidentally deleted from the EMPLOYEES table at 9:05AM we could recover it using:

DECLARE
CURSOR c_emp IS
SELECT *
FROM employees;
v_row c_emp%ROWTYPE;
BEGIN
Dbms_Flashback.Enable_At_Time('28-AUG-01 09:00:00');
OPEN c_emp;
Dbms_Flashback.Disable;

LOOP
FETCH c_emp INTO v_row;
EXIT WHEN c_emp%NOTFOUND;
INSERT INTO employees VALUES
(v_row.employee_id, v_row.first_name,
v_row.last_name, v_row.email,
v_row.phone_number, v_row.hire_date,
v_row.job_id, v_row.salary,
v_row.commission_pct, v_row.manager_id,
v_row.department_id, v_row.dn);
END LOOP;
CLOSE c_emp;
COMMIT;
END;
/

Notice that the Flashback Query session is disabled after the cursor is created so that the DML operations can be performed to recover the data.

Restrictions

* The server must be configured to use Automatic Undo Management.
* No DDL or DML can be issued when using Flashback Query.
* Flashback Query does not reverse DDL operations such as DROP, only DML alterations to the data.
* Flashback Query does apply to code objects (Packages, Procedures, Function or Triggers). If invoked, the current definition will be executed against the flashback data.

Share on Facebook Tweet Pinterest

Function Based Indexes

Posted by Sagar Patil

Traditionally, performing a function on an indexed column in the where clause of a query guaranteed an index would not be used. Oracle 8i introduced Function Based Indexes to counter this problem. Rather than indexing a column, you index the function on that column, storing the product of the function, not the original column data. When a query is passed to the server that could benefit from that index, the query is rewritten to allow the index to be used. The following code samples give an example of the use of Function Based Indexes:

* Build Test Table
* Build Regular Index
* Build Function Based Index

Build Test Table
First we build a test table and populate it with enough data so that use of an index would be advantageous:

Code:

CREATE TABLE user_data (
id NUMBER(10) NOT NULL,
first_name VARCHAR2(40) NOT NULL,
last_name VARCHAR2(40) NOT NULL);

BEGIN
FOR cur_rec IN 1 .. 2000 LOOP
    IF MOD(cur_rec, 2) = 0 THEN
      INSERT INTO user_data
      VALUES (cur_rec, ‘John’ || cur_rec, ‘Doe’);
    ELSE
      INSERT INTO user_data
      VALUES (cur_rec, ‘Jayne’ || cur_rec, ‘Doe’);
    END IF;
    COMMIT;
END LOOP;
END;
/

ANALYZE TABLE user_data COMPUTE STATISTICS;

At this point the table is not indexed so we would expect a full table scan for any query:

SET AUTOTRACE ON
SELECT *
FROM user_data
WHERE Upper(first_name) = ‘JOHN2’;

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=10)
[b] 1 0 TABLE ACCESS (FULL) OF ‘USER_DATA’ (Cost=1 Card=1 Bytes=10)[/b]

Build Regular Index
If we now create a regular index on the FIRST_NAME column we see that the index is not used:

CREATE INDEX first_name_idx ON user_data (first_name);
ANALYZE TABLE user_data COMPUTE STATISTICS;

SET AUTOTRACE ON
SELECT *
FROM user_data
WHERE Upper(first_name) = ‘JOHN2’;

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=10)
1 0 TABLE ACCESS (FULL) OF ‘USER_DATA’ (Cost=1 Card=1 Bytes=10)

Build Function Based Index
If we now replace the regular index with a function based index on the FIRST_NAME column we see that the index is used:

DROP INDEX first_name_idx;
CREATE INDEX first_name_idx ON user_data (UPPER(first_name));
ANALYZE TABLE user_data COMPUTE STATISTICS;

ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

SET AUTOTRACE ON
SELECT *
FROM   user_data
WHERE Upper(first_name) = ‘JOHN2’;

Execution Plan
———————————————————-
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=14)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF ‘USER_DATA’ (Cost=2 Card=1 Bytes=14)

[b] 2 1 INDEX (RANGE SCAN) OF ‘FIRST_NAME_IDX’ (NON-UNIQUE) (Cost=1 Card=1)[/b]

The QUERY_REWRITE_INTEGRITY and QUERY_REWRITE_ENABLED parameters must be set or the server will not be able to rewrite the queries, and will therefore not be able to use the new index. These parameters may be set in the Init.ora file if they are needed permanently.

Share on Facebook Tweet Pinterest

When to Rebuild a B-Tree Index

Posted by Sagar Patil

You have to periodically check your indexes to see if they become skewed and, therefore, good candidates for rebuild.

A skewed index has many records clumped close together on the index tree due to their similar indexed values. When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur, creating a bottleneck in performance. It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt.

Here is a sample procedure on how to identify the skewed indexes:

1. Gather statistics on your indexes. For large indexes (over one hundred
thousand records in the underlying table), use ESTIMATE instead of COMPUTE STATISTICS.

For example: SQL> analyze index A1_PK compute statistics;
Index analyzed.

2. Run the script given below – index_check.sql – to find out how skewed each index is.

This query checks on all indexes that belong to user SCOTT:

SQL> select index_name, blevel,
decode(blevel,0,'OK BLEVEL',1,'OK BLEVEL',
2,'OK BLEVEL',3,'OK BLEVEL',4,'OK BLEVEL','BLEVEL HIGH') OK
from dba_indexes
where owner='SCOTT';

INDEX_NAME                                BLEVEL  OK
----------------------------------------  ------  ---- 
A1_PK                                     BLEVEL  HIGH
A1_UK                                     BLEVEL  HIGH
BUDVERPORT_BUDVERPORT2_UK                 1       OK BLEVEL
BUDVERPORT_BV_FK_I                        2       OK BLEVEL
BUDVERPORT_CHAR_CL_FK_I                   1       OK BLEVEL
BUDVERPORT_DIRCTE_FK_I                    3       OK BLEVEL
BUDVERPORT_FUND_TYPE_FK_I                 1       OK BLEVEL
BUDVERPORT_OMB_SUBFCT_FK_I                1       OK BLEVEL
BUDVERPORT_ORG_FK_I                       0       OK BLEVEL
BUDVERPORT_PL_TITLE_FK_I                  1       OK BLEVEL
BUDVERPORT_RDC_FK_I                       1       OK BLEVEL
S_INVENTORY_PRODID_WARID_PK               BLEVEL  HIGH
S_ITEM_ORDID_ITEMID_PK                    BLEVEL  HIGH
S_ITEM_ORDID_PRODID_UK                    BLEVEL  HIGH
S_LONGTEXT_ID_PK                          BLEVEL  HIGH
S_ORD_ID_PK                               BLEVEL  HIGH
S_PRODUCT_ID_PK                           BLEVEL  HIGH
S_PRODUCT_NAME_UK                         BLEVEL  HIGH
S_REGION_ID_PK                            BLEVEL  HIGH
S_REGION_NAME_UK                          BLEVEL  HIGH
S_TITLE_TITLE_PK                          BLEVEL  HIGH
S_WAREHOUSE_ID_PK                         BLEVEL  HIGH

3. The BLEVEL (or branch level) is part of the B-tree index format and relates to the number of times Oracle has to narrow its search on the index while searching for a particular record. In some cases, a separate disk hit is requested for each BLEVEL. Prior to 9i, if the BLEVEL is more than 4, it is recommended to rebuild the index. As database are getting bigger and bigger, BLEVEL may not be a good indicator of time to rebuild the index. BLEVEL > 4 may suggest an evaluation of whether the partitioning option could help you.

Note: If you do not analyze the index, the index_check.sql script will
show “BLEVEL HIGH” for such an index.

4. Gather more index statistics using the VALIDATE STRUCTURE option of the ANALYZE command to populate the INDEX_STATS virtual table. This table does not contain an OWNER column and assumes you are looking for statistics for indexes created by your active session only.

SQL> analyze index SCOTT.ORG_PK validate structure;
Index analyzed.

SQL> select DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS) PCT_DELETED,
(LF_ROWS-DISTINCT_KEYS)*100/ decode(LF_ROWS,0,1,LF_ROWS) DISTINCTIVENESS
from index_stats  where NAME='&index_name';

Enter value for index_name: ORG_PK

PCT_DELETED DISTINCTIVENESS
———– —————
0 0

The PCT_DELETED column shows what percent of leaf entries (index entries) have been deleted and remain unfilled. The more deleted entries exist on an index, the more unbalanced the index becomes. If the PCT_DELETED is 20% or higher, the index is candidate for rebuilding. If you can afford to rebuild indexes more frequently, then do so if the value is higher than 10%. Leaving indexes with high PCT_DELETED without rebuild might cause excessive redo allocation on some systems.
The DISTINCTIVENESS column shows how often a value for the column(s) of the index is repeated on average.

For example, if a table has 10000 records and 9000 distinct SSN values, the formula would result in
(10000-9000) x 100 / 10000 = 10. This shows a good distribution of values.

If, however, the table has 10000 records and only 2 distinct SSN values, the formula would result in (10000-2) x 100 /10000 = 99.98. This shows that there are very few distinct values as a percentage of total records in the column. Such columns are not candidates for a rebuild but good candidates for bitmapped indexes.

Share on Facebook Tweet Pinterest

Index Monitoring

Posted by Sagar Patil

Index monitoring could be initiated and stopped using ALTER INDEX syntax shown below.

ALTER INDEX my_index_i MONITORING USAGE;
ALTER INDEX my_index_i NOMONITORING USAGE;

Information about the index usage can be displayed using the V$OBJECT_USAGE view.

SELECT index_name,
table_name,
monitoring,
used,
start_monitoring,
end_monitoring
FROM v$object_usage
WHERE index_name = 'MY_INDEX_I'
ORDER BY index_name;

The V$OBJECT_USAGE view does not contain an OWNER column so you must to log on as the object owner to see the usage data.

Foreign Key Indexes
To quote the Oracle Database Concepts manual:
“You should almost always index foreign keys. The only exception is when the matching unique or primary key is never updated or deleted.”

When a foreign key is unindexed, DML on the parent primary key results in a share row exclusive table lock (or share-subexclusive table lock, SSX) on the child table, preventing DML from other transactions against the child table. If the DML affects several rows in the parent table, the lock on the child table is obtained and released immediately for each row in turn. Despite the speed of the lock-release process, this can cause significant amounts of contention on the child table during periods of heavy update/delete activity on the parent table.

When a foreign key is indexed, DML on the parent primary key results in a row share table lock (or subshare table lock, SS) on the child table. This type of lock prevents other transactions from issuing whole table locks on the child table, but does not block DML on either the parent or the child table. Only the rows relating to the parent primary key are locked in the child table.

Share on Facebook Tweet Pinterest

Building virtual index using the NOSEGMENT clause.

Posted by Sagar Patil

Let’s create a virtual index

07:59:12 orcl> create index hr.emp2_emp_id_virtual on hr.employees2(employee_id) nosegment;
Index created.

I am going to set a hidden startup parameter “_use_nosegment_indexes” to TRUE so that our session will recognize our new virtual index.

08:00:09 orcl> alter session set “_use_nosegment_indexes” = true;
Running our statement again to see if it will use our new virtual index. Check out the access path below. The optimizer has chosen our virtual index.

select employee_id, a.department_id, b.department_name from
hr.departments b, hr.employees2 a where
a.department_id = b.department_id
and employee_id = 203

Execution Plan
———————————————————-
Plan hash value: 2516110069
—————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 25 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES2 | 1 | 9 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP2_EMP_ID_VIRTUAL | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |
—————————————————————————————————-

If I set the “_use_nosegment_indexes” to FALSE, the optimizer did NOT choose virtual index.

08:01:09 orcl> alter session set “_use_nosegment_indexes” = false;
Session altered.

08:01:33 orcl> select employee_id, a.department_id, b.department_name
08:01:47 2 from
08:01:47 3 hr.departments b, hr.employees2 a
08:01:47 4 where
08:01:47 5 a.department_id = b.department_id
08:01:47 6 and employee_id = 203;

Execution Plan
———————————————————-
Plan hash value: 2641883601
——————————————————————————————–

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 25 | 818 (3)| 00:00:10 |
| 1 | NESTED LOOPS | | 1 | 25 | 818 (3)| 00:00:10 |
|* 2 | TABLE ACCESS FULL | EMPLOYEES2 | 1 | 9 | 817 (3)| 00:00:10 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |
——————————————————————————————–

Executing DBMS_STATS to gather statistics on both the virtual and standard index. I have run tests with statistics and without and it does seem to affect virtual index access paths.

08:21:57 orcl> exec dbms_stats.gather_index_stats(‘HR’, ‘EMP2_EMP_ID_NON_VIRTUAL’);
PL/SQL procedure successfully completed.

08:23:10 orcl> exec dbms_stats.gather_index_stats(‘HR’, ‘EMP2_EMP_ID_VIRTUAL’);

PL/SQL procedure successfully completed.

Looking for information on indexes built on the EMPLOYEES2 table.
Oracle returns a row for the standard index but not the virtual index.

08:20:31 orcl> select index_name, last_analyzed from dba_indexes where table_name = ‘EMPLOYEES2’

INDEX_NAME LAST_ANAL
—————————— ———
EMP2_EMP_ID_NON_VIRTUAL 31-MAY-07

Determining f we can find the virtual index in DBA_SEGMENTS. No success.

08:26:09 orcl> select segment_name, segment_type from dba_segments where segment_name like ‘EMP2%’;
SEGMENT_NAME SEGMENT_TYPE
——————– ——————
EMP2_EMP_ID_NON_VIRT INDEX
UAL

Looking for the the virtual index in DBA_OBJECTS. Finally, we find some sort of evidence that the virtual index exists in the database!

08:30:21 orcl> col object_name for a30
08:30:29 orcl> r
1 select object_name, object_type, created, status, temporary
2* from dba_objects where object_name like ‘EMP2%’

OBJECT_NAME OBJECT_TYPE CREATED STATUS T
—————————— ——————- ——— ——- –
EMP2_EMP_ID_NON_VIRTUAL INDEX 31-MAY-07 VALID N
EMP2_EMP_ID_VIRTUAL INDEX 31-MAY-07 VALID N

Share on Facebook Tweet Pinterest

Selecting the Best Join Order : Methods Oracle Uses to Join Tables

Posted by Sagar Patil

Choose the join order that gives you least number of rows to be joined to the other tables. That is, if you’re joining three tables, the one with the more restrictive filter should be joined first to one of the other two tables.

When SQL statement includes a join between two or more tables, the order in which you join tables is extremely important.
– The driving table in a join is the first table that comes after the WHERE clause.
– The driving table in the join should contain the filter that will eliminate the most rows.

Nested loops Join
Sort-merge Join
Hash Join
Cluster Join

Nested Loops Joins
For each relevant row in the first table, find all matching rows in the other table.
Suppose you have a list of all public officials in your city and a telephone book for your city. How would you go about getting the names and telephone numbers of each council member?

You could:
– Go down the list of public officials
– Discard all names except council members
– Look up the remaining names in the telephone book one at a time

Sort-Merge Joins
Sort all of the relevant rows in the first table by the join key, likewise sort the second table, and then merge the two together.

Suppose you and your fianc?e each have a huge compact disc collection. You buy a house together and decide to combine collections. How would you eliminate the duplicate CDs?

You could:
– Alphabetize each collection by artist
– Compare collections side-by-side
– Extract the discs that appear in both

Hash Join
Build a hash table in memory of all relevant rows from the first table. For each relevant row in the second table, probe the hash table to find the matches.

A hash join is like a nested loops join, except that preparatory work is done to one of the two tables to speed up the join process.

Consider the nested loops join example with the council members?
phone numbers, and imagine that the telephone book is not
alphabetized by name.

Cluster Joins
For each relevant row in the first table, find all matching rows in the other table by looking at the cluster index. The matching rows will probably be conveniently located in the same data block.

Consider a stack of purchase orders.
Each PO can have any number of items on it. When you pick up one PO, you have easy access to all of its items.

Share on Facebook Tweet Pinterest

Sid from Unix Process ID

Posted by Sagar Patil

Here is a situation, This afternoon I have seen my CPU hitting through the roof on a Live system. There are about 6 Oracle instances and one is chewing more than 90% of CPU time.

– I want to know which Instance /process is a culprit

  PID USERNAME PRI NICE  SIZE   RES STATE   TIME    CPU COMMAND
638523 oracle    52    0 1360M  503M run   574:22 99.10% oracle
958998 oracle    42    0 1363M  506M run   518:12 46.20% oracle
982071 oracle    42    0 1360M  502M WAIT  618:58 38.40% oracle

select s.username, s.status,  s.sid,     s.serial#,
       p.spid,     s.machine, s.process, s.lockwait
from   v$session s, v$process p
where  p.spid     = &oracle_pid
and    s.paddr    = p.addr;

Replace ‘&unix_pid’ with 958998

USERNAME    STATUS    SID    SERIAL#    SPID    MACHINE    PROCESS    LOCKWAIT
SYS         ACTIVE    241    38945      958998

Share on Facebook Tweet Pinterest

Find out SGA PGA Size

Posted by Sagar Patil

SET LINESIZE 145
SET PAGESIZE 9999

COLUMN sid                     FORMAT 999            HEADING ‘SID’
COLUMN oracle_username         FORMAT a12            HEADING ‘Oracle User’     JUSTIFY right
COLUMN os_username             FORMAT a9             HEADING ‘O/S User’        JUSTIFY right
COLUMN session_program         FORMAT a18            HEADING ‘Session Program’ TRUNC
COLUMN session_machine         FORMAT a8             HEADING ‘Machine’         JUSTIFY right TRUNC
COLUMN session_pga_memory      FORMAT 9,999,999,999 HEADING ‘PGA Memory’
COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING ‘PGA Memory Max’
COLUMN session_uga_memory      FORMAT 9,999,999,999 HEADING ‘UGA Memory’
COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING ‘UGA Memory MAX’

SELECT
s.sid                sid
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9)     os_username
, s.program            session_program
, lpad(s.machine,8)    session_machine
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = ‘session pga memory’)        session_pga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = ‘session pga memory max’)    session_pga_memory_max
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = ‘session uga memory’)        session_uga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = ‘session uga memory max’)    session_uga_memory_max
FROM
v$session s
ORDER BY session_pga_memory DESC
/

SID Oracle User O/S User Session Program     Machine     PGA Memory PGA Memory Max     UGA Memory UGA Memory MAX
—- ———— ——— —————— ——– ————– ————– ————– ————–
14          SYS    oracle sqlplus@bmau32.uk. bmau32.u     14,040,528     14,040,528        209,312        209,312
6                 oracle oracle@bmau32.uk.p bmau32.u     10,670,592     10,670,592         78,496         78,496
5                 oracle oracle@bmau32.uk.p bmau32.u     10,378,816     10,378,816         78,496         78,496
31          SYS                                            5,504,568     26,138,992      4,856,512     24,737,312
13                 oracle oracle@bmau32.uk.p bmau32.u      4,660,064      4,660,064         78,496         78,496
12                 oracle oracle@bmau32.uk.p bmau32.u      4,618,136      4,618,136         78,496         78,496

Share on Facebook Tweet Pinterest

Statspack Scripts

Posted by Sagar Patil

Installation
The following scripts must be run as a user with SYSDBA privilege:
* SPCREATE.SQL: Creates entire Statspack environment (calls SPCUSR.SQL, SPCTAB.SQL, SPCPKG.SQL) * SPDROP.SQL: Drops entire Statspack environment (calls SPDTAB.SQL, SPDUSR.SQL)

The following scripts are run as a user with SYSDBA privilege by the preceding calling scripts:
* SPDTAB.SQL: Drops Statspack tables
* SPDUSR.SQL: Drops the Statspack user (PERFSTAT)

The following scripts are run as PERFSTAT by the preceding calling scripts:
* SPCUSR.SQL: Creates the Statspack user (PERFSTAT)
* SPCTAB.SQL: Creates Statspack tables
* SPCPKG.SQL: Creates the Statspack package

Scripts for Statspack Reporting and Automation

The following scripts must be run as PERFSTAT:
* SPREPORT.SQL: Generates a Statspack report
* SPREPSQL.SQL: Generates a Statspack SQL report for the specific SQL hash value specified
* SPREPINS.SQL: Generates a Statspack report for the database and instance specified
* SPAUTO.SQL: Automates Statspack statistics collection (using DBMS_JOB)

Scripts for Statspack Performance Data Maintenance
The following scripts must be run as PERFSTAT:
* SPPURGE.SQL: Purges a limited range of Snapshot IDs for a given database instance.
* SPTRUNC.SQL: Truncates all performance data in Statspack tables

Share on Facebook Tweet Pinterest

API to Retrieve runtime Replication Parameters

Posted by Sagar Patil

We often get thousands of replication conflits/errors at current system. A resolution is generally looking at EM console and resolving each error manually. Please see examples below.

This approach is working well until we have less than some thousands error x’actions at DEFERROR. Anything above that is causing a performance hit. Also DEFERROR /DEFTRANS get highly fragmented adding to the current problem. A solution would be writing a PLSQL API to retrieve runtime parameters as and when needed than using EM console to retrieve millions of rows.

SELECT argcount, schemaname, packagename, procname
FROM defcall WHERE deferred_tran_id in
(select deferred_tran_id from deftran where rownum <100)

ARGCOUNT	SCHEMANAME	PACKAGENAME	PROCNAME
17	CVD_SYSTEM	VOUCHER	REP_UPDATE
9	CVD_SYSTEM	VOUCHER_EVENT	REP_INSERT
17	CVD_SYSTEM	VOUCHER	REP_UPDATE
9	CVD_SYSTEM	VOUCHER_EVENT	REP_INSERT
17	CVD_SYSTEM	VOUCHER	REP_UPDATE
9	CVD_SYSTEM	VOUCHER_EVENT	REP_INSERT
17	CVD_SYSTEM	VOUCHER	REP_UPDATE
9	CVD_SYSTEM	VOUCHER_EVENT	REP_INSERT
17	CVD_SYSTEM	VOUCHER	REP_UPDATE
9	CVD_SYSTEM	VOUCHER_EVENT	REP_INSERT
17	CVD_SYSTEM	VOUCHER	REP_UPDATE
9	CVD_SYSTEM	VOUCHER_EVENT	REP_INSERT
17	CVD_SYSTEM	VOUCHER	REP_UPDATE
9	CVD_SYSTEM	VOUCHER_EVENT	REP_INSERT
17	CVD_SYSTEM	VOUCHER	REP_UPDATE
9	CVD_SYSTEM	VOUCHER_EVENT	REP_INSERT
17	CVD_SYSTEM	VOUCHER	REP_UPDATE
9	CVD_SYSTEM	VOUCHER_EVENT	REP_INSERT

SELECT e.destination, e.deferred_tran_id, e.origin_tran_db, e.origin_tran_id,
  e.start_time, count(e.callno)
FROM
 sys.deferror e, sys.defcall c WHERE e.deferred_tran_id = c.deferred_tran_id
  GROUP BY e.destination, e.deferred_tran_id, e.origin_tran_db,
  e.origin_tran_id, e.start_time ORDER BY 1

DESTINATION	DEFERRED_TRAN_ID	ORIGIN_TRAN_DB	ORIGIN_TRAN_ID	START_TIME	COUNT(E.CALLNO)CPI_O5CA.O2	105.45.83730	CPI_O5CB.O2	120.23.60368	09/11/2008 20:24:50	3
CPI_O5CA.O2	107.12.84097	CPI_O5CB.O2	121.41.62034	09/11/2008 20:24:51	1
CPI_O5CA.O2	107.13.84100	CPI_O5CB.O2	123.47.50472	09/11/2008 20:24:52	3
CPI_O5CA.O2	108.1.83596	CPI_O5CB.O2	123.42.50472	09/11/2008 20:24:52	1

SELECT error_msg FROM deferror
    WHERE deferred_tran_id = '107.12.84097' AND callno = 2

ERROR_MSG
ORA-01403: no data found

SELECT t.deferred_tran_id, d.dblink, t.start_time, count(c.callno)

FROM sys.deftran t, sys.defcall c, sys.deftrandest d

WHERE d.deferred_tran_id = t.deferred_tran_id

AND c.deferred_tran_id = t.deferred_tran_id

GROUP BY t.deferred_tran_id, t.start_time, d.dblink ORDER BY 3 desc

Find out the Argument Count and Call number :
select deferred_tran_id, callno, argcount, procname, packagename, schemaname
             from defcall;

Substitute Transaction ID, Argument Number and Call Number to locate parameter for that Call 

DECLARE

       atype       INTEGER;

       csform          NUMBER;

       arg_no      NUMBER;

       callno      NUMBER;

       tid         VARCHAR2(22);

       outval      VARCHAR2(2000);

BEGIN

    arg_no:=1;

    callno:='0';

    tid:='105.45.83730';

    atype:=dbms_defer_query.get_arg_type(callno, arg_no, tid);

    csform:=dbms_defer_query.get_arg_form(callno, arg_no, tid);

    IF atype = 2 THEN

      outval:=to_char(dbms_defer_query.get_number_arg(callno, arg_no, tid));

    ELSIF atype = 96 and

          csform = 1 THEN

      outval:=dbms_defer_query.get_char_arg(callno, arg_no, tid);

    ELSIF atype = 96 and

          csform = 2 THEN

      outval:=translate(dbms_defer_query.get_nchar_arg(callno, arg_no, tid) USING CHAR_CS);

    ELSIF atype= 1 and

          csform = 1 THEN

      outval:=dbms_defer_query.get_varchar2_arg(callno, arg_no, tid);

    ELSIF atype= 1 and

              csform = 2 THEN

      outval:=translate(dbms_defer_query.get_nvarchar2_arg(callno, arg_no, tid) USING CHAR_CS);

    ELSIF atype = 12 THEN

         outval:=to_char(dbms_defer_query.get_date_arg(callno, arg_no, tid), 'DD-MON-YYYY HH24:MI:SS');

    ELSIF atype = 23 THEN

         outval:=rawtohex(dbms_defer_query.get_raw_arg(callno, arg_no, tid));

    ELSIF atype = 11 THEN

         outval:=dbms_defer_query.get_rowid_arg(callno, arg_no, tid);

    ELSE

         outval:='-Binary Value-';

      -- RAISE NO_DATA_FOUND;

    END IF;

    dbms_output.put_line(outval);

END;

Share on Facebook Tweet Pinterest

How to enable trace in Oracle

Posted by Sagar Patil

1. Enable trace at instance level

Put the following line in init.ora. It will enable trace for all sessions and the background
processes

to Start : sql_trace = TRUE

to Stop : sql_trace = FALSE

to enable tracing without restarting database run the following command in sqlplus

SQLPLUS> ALTER SYSTEM SET trace_enabled = TRUE;

2. Enable trace at session level

to Start : ALTER SESSION SET sql_trace = TRUE;

to stop : ALTER SESSION SET sql_trace = FALSE;

– or –

to Start : EXECUTE dbms_session.set_sql_trace (TRUE);
to Stop : EXECUTE dbms_session.set_sql_trace (FALSE);

– or –

to Start : EXECUTE dbms_support.start_trace;
to Stop : EXECUTE dbms_support.stop_trace;

3. Enable trace in another session

SELECT * FROM v$session WHERE upper(osuser) like ‘&1’;

to start : EXECUTE dbms_support.start_trace_in_session (SID, SERIAL#);

to stop : EXECUTE dbms_support.stop_trace_in_session (SID, SERIAL#);

– or –

EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, TRUE);
EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, FALSE);

4. Enable trace Using ORADEBUG

SQL> select s.username, p.spid os_process_id, p.pid oracle_process_id
from v$session s, v$process p
where s.paddr = p.addr
   and s.username = upper(‘&user_name’); 2    3    4
Enter value for user_name: repadmin
old   4:    and s.username = upper(‘&user_name’)
new   4:    and s.username = upper(‘repadmin’)

USERNAME                       OS_PROCESS_I ORACLE_PROCESS_ID
—————————— ———— —————–
REPADMIN                       850297                      19
REPADMIN                       955904                      32
REPADMIN                       911971                      20
REPADMIN                       845029                      18
REPADMIN                       533889                      30

SQL> oradebug setospid 533889;
Oracle pid: 30, Unix process pid: 533889, image: oracle@bmau29.uk.pri.o2.com (TNS V1-V3)

SQL> oradebug event 10046 trace name context forever, level 12;
Statement processed.

Share on Facebook Tweet Pinterest

FAN (Fast Application Notification) & ONS (Oracle Notification Services)

Posted by Sagar Patil

Read more…

Share on Facebook Tweet Pinterest

How does the failover mechanism work?

Posted by Sagar Patil

Can't convert a Table Space into Read Only Mode

Posted by Sagar Patil

Reason : Active x’actions running on the table space

Locate SQL_TEXT and Session Address running alter Tablspace Command

SELECT SQL_TEXT, SADDR
FROM V$SQLAREA,V$SESSION
WHERE V$SQLAREA.ADDRESS = V$SESSION.SQL_ADDRESS
AND lower(SQL_TEXT) LIKE ‘alter tablespace%’;

V$transaction will show active X’actions in System

SELECT SES_ADDR, START_SCNB,used_urec,used_ublk
FROM V$TRANSACTION
ORDER BY START_SCNB;

v$transaction View
Important fields of v$transaction are used_ublk and used_urec. They tell of how many blocks and records the undo for a transaction consists. In order to find out the name of the corresponding rollback segment, join the xidusn field with the usn field of v$rollname. This is demonstrated in

List of Sessions blocking tablepsace in write mode

select saddr, SID,Serial#,username,Status, Machine,SQL_ID
from v$session where saddr in
(select SES_ADDR FROM V$TRANSACTION where used_urec is not null
and used_ublk is not null) ;

SQL clarifying Commands fired by Session.

select b.SQL_TEXT,saddr, SID,Serial#,username,Status,Machine,a.SQL_ID
from v$session a ,v$SQL b
where a.saddr in (select SES_ADDR FROM V$TRANSACTION
where used_urec is not null and used_ublk is not null )
and a.sql_id=b.sql_id

Share on Facebook Tweet Pinterest

TAF can be verified by querying the Oracle-provided views

Posted by Sagar Patil

Rectify Differences Between Replicated Tables

Posted by Sagar Patil

Using the DIFFERENCES Procedure

The DIFFERENCES procedure compares two replicas of a table, and determines all rows in the first replica that are not in the second and all rows in the second that are not in the first. The output of this procedure is stored in two user-created tables. The first table stores the values of the missing rows, and the second table is used to indicate which site contains each row.

The RECTIFY procedure uses the information generated by the DIFFERENCES procedure to rectify the two tables. Any rows found in the first table and not in the second are inserted into the second table. Any rows found in the second table and not in the first are deleted from the second table.

To restore equivalency between all copies of a replicated table, complete the following steps:

1. Select one copy of the table to be the “reference” table. This copy will be used to update all other replicas of the table as needed.

2. Determine if it is necessary to check all rows and columns in the table for differences, or only a subset.

For example, it may not be necessary to check rows that have not been updated since the last time that you checked for differences. Although it is not necessary to check all columns, your column list must include all columns that make up the primary key (or that you designated as a substitute identity key) for the table.

3. After determining which columns you will be checking in the table, create two tables to hold the results of the comparison.

You must create one table that can hold the data for the columns being compared. For example, if you decide to compare the employee_id, salary, and department_id columns of the employees table, then your CREATE statement would need to be similar to the following:

CREATE TABLE hr.missing_rows_data (
  employee_id     NUMBER(6),
  salary          NUMBER(8,2),
  department_id   NUMBER(4));

You must also create a table that indicates where the row is found. This table must contain three columns with the datatypes shown in the following example:

CREATE TABLE hr.missing_rows_location (
  present     VARCHAR2(128),
  absent      VARCHAR2(128),
  r_id        ROWID);

4. Suspend replication activity for the replication group containing the tables that you want to compare. Although suspending replication activity for the group is not a requirement, rectifying tables that were not quiesced first can result in inconsistencies in your data.

BEGIN
   DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
      gname => 'hr_repg');
END;
/

5. At the site containing the “reference” table, call the DIFFERENCES procedure in the DBMS_RECTIFIER_DIFF package.

For example, if you wanted to compare the employees tables at the New York and San Francisco sites, then your procedure call would look similar to the following:

BEGIN
   DBMS_RECTIFIER_DIFF.DIFFERENCES (
      sname1              =>   'hr',
      oname1              =>   'employees',
      reference_site      =>   'ny.world',
      sname2              =>   'hr',
      oname2              =>   'employees',
      comparison_site     =>   'sf.world',
      where_clause        =>   '',
      column_list         =>   'employee_id,salary,department_id',
      missing_rows_sname  =>   'hr',
      missing_rows_oname1 =>   'missing_rows_data',
      missing_rows_oname2 =>   'missing_rows_location',
      missing_rows_site   =>   'ny.world',
      max_missing         =>    500,
      commit_rows         =>    50);
END;

6. Rectify the table at the “comparison” site to be equivalent to the table at the “reference” site by calling the RECTIFY procedure in the as shown in the DBMS_RECTIFIER_DIFF package following example:

BEGIN
   DBMS_RECTIFIER_DIFF.RECTIFY (
      sname1              =>   'hr',
      oname1              =>   'employees',
      reference_site      =>   'ny.world',
      sname2              =>   'hr',
      oname2              =>   'employees',
      comparison_site     =>   'sf.world',
      column_list         =>   'employee_id,salary,department_id',
      missing_rows_sname  =>   'hr',
      missing_rows_oname1 =>   'missing_rows_data',
      missing_rows_oname2 =>   'missing_rows_location',
      missing_rows_site   =>   'ny.world',
      commit_rows         =>    50);
END;
/

The RECTIFY procedure temporarily disables replication at the “comparison” site while it performs the necessary insertions and deletions, as you would not want to propagate these changes. RECTIFY first performs all of the necessary DELETE operations and then performs all of the INSERT operations. This ensures that there are no violations of a PRIMARY KEY constraint.

After you have successfully executed the RECTIFY procedure, your missing rows tables should be empty.

Share on Facebook Tweet Pinterest

Flashback Query

Function Based Indexes

When to Rebuild a B-Tree Index

Index Monitoring

Building virtual index using the NOSEGMENT clause.

Selecting the Best Join Order : Methods Oracle Uses to Join Tables

Sid from Unix Process ID

Find out SGA PGA Size

Statspack Scripts

API to Retrieve runtime Replication Parameters

How to enable trace in Oracle

FAN (Fast Application Notification) & ONS (Oracle Notification Services)

How does the failover mechanism work?

Can't convert a Table Space into Read Only Mode

SELECT SQL_TEXT, SADDR
FROM V$SQLAREA,V$SESSION
WHERE V$SQLAREA.ADDRESS = V$SESSION.SQL_ADDRESS
AND lower(SQL_TEXT) LIKE ‘alter tablespace%’;

SELECT SES_ADDR, START_SCNB,used_urec,used_ublk
FROM V$TRANSACTION
ORDER BY START_SCNB;

select saddr, SID,Serial#,username,Status, Machine,SQL_ID
from v$session where saddr in
(select SES_ADDR FROM V$TRANSACTION where used_urec is not null
and used_ublk is not null) ;

select b.SQL_TEXT,saddr, SID,Serial#,username,Status,Machine,a.SQL_ID
from v$session a ,v$SQL b
where a.saddr in (select SES_ADDR FROM V$TRANSACTION
where used_urec is not null and used_ublk is not null )
and a.sql_id=b.sql_id

TAF can be verified by querying the Oracle-provided views

Rectify Differences Between Replicated Tables

Main menu

Search

Categories

Search Errors

Search Docs

Oracle Documentation

Top menu

SELECT SQL_TEXT, SADDR FROM V$SQLAREA,V$SESSION WHERE V$SQLAREA.ADDRESS = V$SESSION.SQL_ADDRESS AND lower(SQL_TEXT) LIKE ‘alter tablespace%’;

SELECT SES_ADDR, START_SCNB,used_urec,used_ublk FROM V$TRANSACTION ORDER BY START_SCNB;

select saddr, SID,Serial#,username,Status, Machine,SQL_ID from v$session where saddr in (select SES_ADDR FROM V$TRANSACTION where used_urec is not null and used_ublk is not null) ;

select b.SQL_TEXT,saddr, SID,Serial#,username,Status,Machine,a.SQL_ID from v$session a ,v$SQL b where a.saddr in (select SES_ADDR FROM V$TRANSACTION where used_urec is not null and used_ublk is not null ) and a.sql_id=b.sql_id

Main menu

Search

Categories

Search Errors

Search Docs

Oracle Documentation

Top menu

SELECT SQL_TEXT, SADDR
FROM V$SQLAREA,V$SESSION
WHERE V$SQLAREA.ADDRESS = V$SESSION.SQL_ADDRESS
AND lower(SQL_TEXT) LIKE ‘alter tablespace%’;

SELECT SES_ADDR, START_SCNB,used_urec,used_ublk
FROM V$TRANSACTION
ORDER BY START_SCNB;

select saddr, SID,Serial#,username,Status, Machine,SQL_ID
from v$session where saddr in
(select SES_ADDR FROM V$TRANSACTION where used_urec is not null
and used_ublk is not null) ;

select b.SQL_TEXT,saddr, SID,Serial#,username,Status,Machine,a.SQL_ID
from v$session a ,v$SQL b
where a.saddr in (select SES_ADDR FROM V$TRANSACTION
where used_urec is not null and used_ublk is not null )
and a.sql_id=b.sql_id