Blogger Buzz: Blogger integrates with Amazon Associates

Blogger Buzz: Blogger integrates with Amazon Associates

Come join me on Oracle Community

Join me on Oracle Community The social network for Oracle people Muhammad Abdul Halim has: 1 friend 5 photos thanks Halim www.halimdba.blogspot.com Click to Join Members on Oracle Community: anuj Venkat Ark David Haimes Bilal Hatipoglu aykut omer ozturk About Oracle Community For anyone interested in Oracle databases, applications and related technologies. 7219 members 470 photos 60 songs 46 videos 777 discussions 155 Events 391 blog posts   To control which emails you receive on Oracle Community, click here

How to connect from oracle to mysql

Two products - DG4MSQL and DG4ODBC.

a) DG4ODBC is for free and requires a 3rd party ODBC driver
and it can connect to any 3rd party database as long as you use a suitable ODBC driver

b) DG4MSQL is more powerfull as it is designed for MS SQL Server databases and it supports many functions it can directly map to SQL Server equivalents - it can also call remote procedures or participtae in distributed transactions. Please be aware DG4MSQL requires a license - it is not for free.

==============================================================
Here I user use ODBC for connect from oracle database to Mysql
connectivity to MYSQL via a database link and heterogeneous services.
==============================================================

Oracle version is 10.2.0.1.0 on Windows Server 2003

1.

In windows control panal > administration tools > data source odbc > SYSTEM DSN

I create a SYSTEM DSN to a SQL Server 2000.The DSN tests "successfully".
Data source name : mysql
Descreption : (by default)
server : localhost
user : root
passward : password of root

Then click test button.

2.

I created a file called initmysql.ora under oracle_home/hs/admin
with this in it:

HS_FDS_CONNECT_INFO = mySql
HS_FDS_TRACE_LEVEL = 0


3. I added this to the tnsnames.ora file

mysql=
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.11.1.248)(PORT=1521)
) (CONNECT_DATA= (SID=mysql)
) (HS=OK)
)


4. I added this to the listerner.ora file:
(under the "SID_LIST_LISTENER =" portion)

(SID_DESC=
(SID_NAME=mysql)
(ORACLE_HOME=G:\oracle_new\DB10G2\APP1\BEFTN)
(PROGRAM=hsodbc)
(ENVS=LD_LIBRARY_PATH = G:\oracle_new\DB10G2\APP1\BEFTN\lib)
)
--------------------------------------listerner look like------------
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.1.248)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = G:\oracle_new\DB10G2\APP1\BEFTN)
(PROGRAM = extproc)
)
(SID_DESC=
(SID_NAME=mysql)
(ORACLE_HOME=G:\oracle_new\DB10G2\APP1\BEFTN)
(PROGRAM=hsodbc)
(ENVS=LD_LIBRARY_PATH = G:\oracle_new\DB10G2\APP1\BEFTN\lib)
)
)
----------------------------------------------------------------------------

5 After this you need to do
a) STOP and START the listener . show LSNRCTL> SERVICES
B) CMD> tnsping mysql (tns name ) result is ok


6. Then I created the database link

CREATE DATABASE LINK "MYSQL.REGRESS.RDBMS.DEV.US.ORACLE.COM"
CONNECT TO "root" (MUST NEED TO GIVE "" , NOT IN PASSWORD)
IDENTIFIED BY
USING 'mysql'; -----TNS NAME

Then test it with toad . successful

complete your connectivity--------
==========================================================================

Some testing.............and solution

7. open sqlplus run following block . if it success then connection establish.

for checking
=============

DECLARE
ret integer;
c integer;
BEGIN
c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@mysql;
DBMS_HS_PASSTHROUGH.PARSE@mysql(c, 'SET SESSION SQL_MODE=''ANSI_QUOTES'';');
ret := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@mysql(c);
dbms_output.put_line(ret ||' passthrough output');
DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@mysql(c);
END;
/

8. If You face

problem:
==============
Error: ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][unixODBC][TCX][MyODBC]Access denied for user 'oracleuser'@'localhost'
(using password: YES) (SQL State: S1000; SQL Code: 1045)
ORA-02063: preceding 2 lines from MYSQL

Solution
============
mysql> SET PASSWORD FOR
-> [email='some_user'@'some_host'] = OLD_PASSWORD(’newpwd’);
Alternatively, use UPDATE and FLUSH PRIVILEGES:

mysql> UPDATE mysql.user SET Password = OLD_PASSWORD(’newpwd’)
-> WHERE Host = ’some_host’ AND User = ’some_user’;
mysql> FLUSH PRIVILEGES;

Audit Trail setup in a oracle application

Audit Trail setup in a oracle application
========================================

1) Keeping Track Data Change of important Table.
2) Keeping Track Source Code change (procedure,function,package,type etc).

====================================================
====================================================
1) Keeping Track Data Change of important Table setup:
====================================================
====================================================

=========================
1. create a audit table
==========================
CREATE TABLE audit_table_basel2
(
tabnam VARCHAR2(50 BYTE) NULL,
colnam VARCHAR2(50 BYTE) NULL,
oldval VARCHAR2(1000 BYTE) NULL,
newval VARCHAR2(1000 BYTE) NULL,
dbuser VARCHAR2(20 BYTE) DEFAULT USER NULL,
modusr VARCHAR2(6 BYTE) NULL,
machine VARCHAR2(100 BYTE) NULL,
host_path VARCHAR2(100 BYTE) NULL,
os_user VARCHAR2(100 BYTE) NULL,
ipaddr VARCHAR2(30 BYTE) NULL,
program VARCHAR2(1024 BYTE) NULL,
user_id VARCHAR2(30 BYTE) NULL,
TIMESTAMP DATE NULL
)
/

===========================================================
11. Create a package for inserting data into audit table
==========================================================


CREATE OR REPLACE PACKAGE audit_pkg
AS
PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN VARCHAR2,
l_old IN VARCHAR2,
p_user_id IN VARCHAR2
);

PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN DATE,
l_old IN DATE,
p_user_id IN VARCHAR2
);

PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN NUMBER,
l_old IN NUMBER,
p_user_id IN VARCHAR2
);
END;
/


CREATE OR REPLACE PACKAGE BODY audit_pkg
AS
PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN VARCHAR2,
l_old IN VARCHAR2,
p_user_id IN VARCHAR2
)
IS
v_user VARCHAR2 (100);
v_terminal VARCHAR2 (100);
v_sessionid VARCHAR2 (100);
v_program VARCHAR2 (200);
v_machine VARCHAR2 (200);
v_host VARCHAR2 (100);
v_os_user VARCHAR2 (100);
v_ipadd VARCHAR2 (200);
BEGIN
IF ( l_new <> l_old
OR (l_new IS NULL AND l_old IS NOT NULL)
OR (l_new IS NOT NULL AND l_old IS NULL)
)
THEN
BEGIN
SELECT USER, SYS_CONTEXT ('USERENV', 'TERMINAL') terminal,
SYS_CONTEXT ('USERENV', 'SESSIONID') sessionid,
(SELECT NVL (module, 'NULL')
FROM v$session
----grant select any dictionary to basel2 need it
WHERE audsid = SYS_CONTEXT ('USERENV', 'SESSIONID'))
program,
(SELECT machine
FROM v$session
WHERE audsid = SYS_CONTEXT ('USERENV', 'SESSIONID'))
machine,
SYS_CONTEXT ('USERENV', 'HOST') HOST,
SYS_CONTEXT ('USERENV', 'OS_USER') os_user,
SYS_CONTEXT ('USERENV', 'IP_ADDRESS') ip_address
INTO v_user, v_terminal,
v_sessionid,
v_program,
v_machine,
v_host,
v_os_user,
v_ipadd
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

DBMS_OUTPUT.put_line ('In package New ' || l_new || ' Old ' || l_old);

INSERT INTO audit_table_basel2
(tabnam, colnam, oldval, newval, dbuser,
modusr, machine, host_path, os_user, ipaddr,
program, user_id, TIMESTAMP
)
VALUES (UPPER (l_tname), UPPER (l_cname), l_old, l_new, USER,
p_user_id, v_machine, v_host, v_os_user, v_ipadd,
v_program, p_user_id, SYSDATE
);
END IF;
END;

PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN DATE,
l_old IN DATE,
p_user_id IN VARCHAR2
)
IS
v_user VARCHAR2 (100);
v_terminal VARCHAR2 (100);
v_sessionid VARCHAR2 (100);
v_program VARCHAR2 (200);
v_machine VARCHAR2 (200);
v_host VARCHAR2 (100);
v_os_user VARCHAR2 (100);
v_ipadd VARCHAR2 (200);
BEGIN
IF ( l_new <> l_old
OR (l_new IS NULL AND l_old IS NOT NULL)
OR (l_new IS NOT NULL AND l_old IS NULL)
)
THEN
BEGIN
SELECT USER, SYS_CONTEXT ('USERENV', 'TERMINAL') terminal,
SYS_CONTEXT ('USERENV', 'SESSIONID') sessionid,
(SELECT NVL (module, 'NULL')
FROM v$session
WHERE audsid = SYS_CONTEXT ('USERENV', 'SESSIONID'))
program,
(SELECT machine
FROM v$session
WHERE audsid = SYS_CONTEXT ('USERENV', 'SESSIONID'))
machine,
SYS_CONTEXT ('USERENV', 'HOST') HOST,
SYS_CONTEXT ('USERENV', 'OS_USER') os_user,
SYS_CONTEXT ('USERENV', 'IP_ADDRESS') ip_address
INTO v_user, v_terminal,
v_sessionid,
v_program,
v_machine,
v_host,
v_os_user,
v_ipadd
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

INSERT INTO audit_table_basel2
(tabnam, colnam, oldval, newval, dbuser,
modusr, machine, host_path, os_user, ipaddr,
program, user_id, TIMESTAMP
)
VALUES (UPPER (l_tname), UPPER (l_cname), l_old, l_new, USER,
p_user_id, v_machine, v_host, v_os_user, v_ipadd,
v_program, p_user_id, SYSDATE
);
END IF;
END;

PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN NUMBER,
l_old IN NUMBER,
p_user_id IN VARCHAR2
)
IS
v_user VARCHAR2 (100);
v_terminal VARCHAR2 (100);
v_sessionid VARCHAR2 (100);
v_program VARCHAR2 (200);
v_machine VARCHAR2 (200);
v_host VARCHAR2 (100);
v_os_user VARCHAR2 (100);
v_ipadd VARCHAR2 (200);
BEGIN
IF ( l_new <> l_old
OR (l_new IS NULL AND l_old IS NOT NULL)
OR (l_new IS NOT NULL AND l_old IS NULL)
)
THEN
BEGIN
SELECT USER, SYS_CONTEXT ('USERENV', 'TERMINAL') terminal,
SYS_CONTEXT ('USERENV', 'SESSIONID') sessionid,
(SELECT NVL (module, 'NULL')
FROM v$session
WHERE audsid = SYS_CONTEXT ('USERENV', 'SESSIONID'))
program,
(SELECT machine
FROM v$session
WHERE audsid = SYS_CONTEXT ('USERENV', 'SESSIONID'))
machine,
SYS_CONTEXT ('USERENV', 'HOST') HOST,
SYS_CONTEXT ('USERENV', 'OS_USER') os_user,
SYS_CONTEXT ('USERENV', 'IP_ADDRESS') ip_address
INTO v_user, v_terminal,
v_sessionid,
v_program,
v_machine,
v_host,
v_os_user,
v_ipadd
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

INSERT INTO audit_table_basel2
(tabnam, colnam, oldval, newval, dbuser,
modusr, machine, host_path, os_user, ipaddr,
program, user_id, TIMESTAMP
)
VALUES (UPPER (l_tname), UPPER (l_cname), l_old, l_new, USER,
p_user_id, v_machine, v_host, v_os_user, v_ipadd,
v_program, p_user_id, SYSDATE
);
END IF;
END;
END audit_pkg;
/


=====================================================
3. Create Trigger in that tables which need to track.
=====================================================

create Trigger script for individual table. save it in a txt file then execute it
in SQLPLUS> @ script_path ;

----------------------------------------------------
set serveroutput on
set feedback off
set verify off
set embedded on
set heading off
set echo off
set linesize 2000
spool tmp.sql

prompt create or replace trigger aud#&&1
prompt after update or insert or delete on &&1
prompt for each row
prompt begin

select ' audit_pkg.check_val( ''&&1'', ''' || column_name ||
''', ' || ':new.' || column_name || ', :old.' ||
column_name ||','||'''OPRSTAMP''' ||');'
from user_tab_columns where table_name = upper('&&1')


/
prompt end;;
prompt /

spool off
set feedback on
set embedded off
set heading on
set verify on

@tmp

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

=======================================================================
=======================================================================
2) Keeping Track Source Code change (procedure,function,package,type etc).
=======================================================================
=======================================================================
For this

1. create audit source table

CREATE TABLE audit_source_hist
(
change_date DATE NULL,
NAME VARCHAR2(30 BYTE) NULL,
TYPE VARCHAR2(12 BYTE) NULL,
line NUMBER NULL,
text VARCHAR2(4000 BYTE) NULL,
dbuser VARCHAR2(20 BYTE) DEFAULT USER NULL,
modusr VARCHAR2(6 BYTE) NULL,
machine VARCHAR2(100 BYTE) NULL,
host_path VARCHAR2(100 BYTE) NULL,
os_user VARCHAR2(100 BYTE) NULL,
ipaddr VARCHAR2(30 BYTE) NULL,
program VARCHAR2(1024 BYTE) NULL,
user_id VARCHAR2(30 BYTE) NULL,
TIMESTAMP DATE NULL
);



2. create a trigger in that schema(user).
===============================================

CREATE OR REPLACE TRIGGER change_hist
AFTER CREATE ON basel2.SCHEMA
DECLARE
V_USER VARCHAR2(100);
V_TERMINAL VARCHAR2(100);
V_SESSIONID VARCHAR2(100);
V_PROGRAM VARCHAR2(200);
V_MACHINE VARCHAR2(200);
V_HOST VARCHAR2(100);
V_OS_USER VARCHAR2(100);
V_IPADD VARCHAR2(200);
BEGIN

BEGIN

select user,SYS_CONTEXT('USERENV','TERMINAL') terminal,
SYS_CONTEXT('USERENV','SESSIONID') sessionid,
(SELECT NVL(module,'NULL')
FROM V$SESSION ----grant select any dictionary to basel2 need it
WHERE AUDSID = SYS_CONTEXT('USERENV','SESSIONID')) PROGRAM,
(SELECT MACHINE
FROM V$SESSION
WHERE AUDSID = SYS_CONTEXT('USERENV','SESSIONID')) machine,
SYS_CONTEXT('USERENV','HOST') host,
SYS_CONTEXT('USERENV','OS_USER') os_user,
SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address
INTO V_USER,V_TERMINAL,V_SESSIONID,V_PROGRAM,V_MACHINE,V_HOST,
V_OS_USER,V_IPADD
From Dual;

EXCEPTION
WHEN OTHERS THEN
NULL;
END;


IF ora_dict_obj_type IN
('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TYPE')
THEN
INSERT INTO AUDIT_SOURCE_HIST
SELECT SYSDATE, NAME, TYPE, LINE, TEXT,USER,V_USER,V_MACHINE,
V_HOST,V_OS_USER,V_IPADD,V_PROGRAM,V_USER,SYSDATE
FROM user_source
WHERE TYPE = ora_dict_obj_type AND NAME = ora_dict_obj_name
and line=1;
END IF;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20000, SQLERRM);
END;
/

How to track column level value change in oracle by a generic trigger

How to track column level value change in oracle by a generic trigger
======================================================
======================================================


1. First create a audit table
=============================

CREATE TABLE audit_table
( TIMESTAMP DATE,
user_name VARCHAR2(30),
table_name VARCHAR2(30),
column_name VARCHAR2(30),
OLD_value VARCHAR2(2000),
NEW_value VARCHAR2(2000),
os_user VARCHAR2(2000),
machine VARCHAR2(2000),
program_name VARCHAR2(2000),
application_user VARCHAR2(2000),
action varchar2(200) default 'DElect'
)
/



2. Create package specification
===============================


CREATE OR REPLACE PACKAGE audit_pkg
AS
PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN VARCHAR2,
l_old IN VARCHAR2
);

PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN DATE,
l_old IN DATE
);

PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN NUMBER,
l_old IN NUMBER
);
END;
/

3. Create package Body
========================


CREATE OR REPLACE PACKAGE BODY audit_pkg
AS
PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN VARCHAR2,
l_old IN VARCHAR2
)
IS
t_sessionid VARCHAR2(200);
t_osuser VARCHAR2(200);
t_machine VARCHAR2(200);
t_program VARCHAR2(200);
BEGIN
---need to permession 'grant select any dictionary to user_name '
SELECT USERENV ('SESSIONID')
INTO t_sessionid
FROM DUAL;

SELECT osuser, machine, NVL (program, 'NULL')
INTO t_osuser, t_machine, t_program
FROM v$session
WHERE audsid = t_sessionid;


IF ( l_new <> l_old
OR (l_new IS NULL AND l_old IS NOT NULL)
OR (l_new IS NOT NULL AND l_old IS NULL)
)
THEN
INSERT INTO audit_table(TIMESTAMP, USER_NAME, TABLE_NAME, COLUMN_NAME, OLD_VALUE, NEW_VALUE,
OS_USER, MACHINE, PROGRAM_NAME, APPLICATION_USER)
VALUES (SYSDATE, USER, UPPER (l_tname), UPPER (l_cname), l_old,l_new
,t_osuser, t_machine, t_program,t_sessionid);
END IF;
END;

PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN DATE,
l_old IN DATE
)
IS
t_sessionid VARCHAR2(200);
t_osuser VARCHAR2(200);
t_machine VARCHAR2(200);
t_program VARCHAR2(200);
BEGIN
---need to permession 'grant select any dictionary to user_name '
SELECT USERENV ('SESSIONID')
INTO t_sessionid
FROM DUAL;

SELECT osuser, machine, NVL (program, 'NULL')
INTO t_osuser, t_machine, t_program
FROM v$session
WHERE audsid = t_sessionid;

IF ( l_new <> l_old
OR (l_new IS NULL AND l_old IS NOT NULL)
OR (l_new IS NOT NULL AND l_old IS NULL)
)
THEN
INSERT INTO audit_table(TIMESTAMP, USER_NAME, TABLE_NAME, COLUMN_NAME, OLD_VALUE, NEW_VALUE,
OS_USER, MACHINE, PROGRAM_NAME, APPLICATION_USER)
VALUES (SYSDATE, USER, UPPER (l_tname), UPPER (l_cname), l_old,l_new
,t_osuser, t_machine, t_program,t_sessionid);
END IF;
END;


PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN NUMBER,
l_old IN NUMBER
)
IS

t_sessionid VARCHAR2(200);
t_osuser VARCHAR2(200);
t_machine VARCHAR2(200);
t_program VARCHAR2(200);
BEGIN
---need to permession 'grant select any dictionary to user_name '
SELECT USERENV ('SESSIONID')
INTO t_sessionid
FROM DUAL;

SELECT osuser, machine, NVL (program, 'NULL')
INTO t_osuser, t_machine, t_program
FROM v$session
WHERE audsid = t_sessionid;

IF ( l_new <> l_old
OR (l_new IS NULL AND l_old IS NOT NULL)
OR (l_new IS NOT NULL AND l_old IS NULL)
)
THEN
INSERT INTO audit_table(TIMESTAMP, USER_NAME, TABLE_NAME, COLUMN_NAME, OLD_VALUE, NEW_VALUE,
OS_USER, MACHINE, PROGRAM_NAME, APPLICATION_USER)
VALUES (SYSDATE, USER, UPPER (l_tname), UPPER (l_cname), l_old,l_new
,t_osuser, t_machine, t_program,t_sessionid);
END IF;
END;
END audit_pkg;


4. create trigger on specific table by runing this script
==========================================================


SQL> @create_trigger.sql
---------------------------------
set serveroutput on
set feedback off
set verify off
set embedded on
set heading off
set echo off
spool tmp.sql

prompt create or replace trigger aud#&&1
prompt after update or delete or insert on &&1
prompt for each row
prompt begin

select ' audit_pkg.check_val( ''&&1'', ''' || column_name ||
''', ' || ':new.' || column_name || ', :old.' ||
column_name || ');'
from user_tab_columns where table_name = upper('&&1')


/
prompt end;;
prompt /

spool off
set feedback on
set embedded off
set heading on
set verify on

@tmp
-----------------------------------

Oracle Database 10g CBO (Cost based optimizer) and cost model

Oracle Database 10g CBO (Cost based optimizer) and cost model

The CBO chooses the best path for your queries, based on what it knows about your data and by leveraging Oracle database features such as bitmap indexes, function-based indexes, hash joins, index-organized tables, and partitioning, whereas the RBO just follows established rules (heuristics). With the release of Oracle Database 10g, the RBO's obsolescence is official and the CBO has been significantly improved yet again.

The CBO now has a special tuning mode that is used by many other Oracle Database 10g features, including the SQL Tuning Advisor and SQL Access Advisor.

When a SQL Query submitted

SQL queries submitted to the Oracle database engine first run through the parser, which checks syntax and analyzes semantics. The result of this run-through is a set of query blocks that is sent to the optimizer, which comprises three main functional subsystems—specifically, the query transformation engine, the estimator, and the plan generator.

1. Query transformation engine

The query transformation engine applies heuristics- or rules-based algorithms to the query blocks. Some common transformations include select-join, group-by, and distinct-view merging and materialized view rewrite.

Common Transformations

Oracle performs numerous query transformations, changing and rewriting queries into equivalent but more optimal forms. Some of these transformations are heuristics- or rules-based transformations, meaning they are based on the structural properties of the query—number of tables, types of join and filters, presence of grouping clauses, for example—and not on selectivity, cardinality, distribution, join order, or related costs of various database operations.

Other transformations, such as materialized view rewrite, star transformation, and OR-expansion, are cost-based transformations in which a query is copied, transformed, and costed repeatedly (and recursively, each time using a new set of transformations), until at the end of the process, one or more transformations is selected to be applied to the original query, if it results in an optimal cost. Here's a summary of the most common transformations:

Heuristics-based transformations.

• Select-join
• Select-join-project view merging
• Group-by and distinct-view merging
• Subquery unnesting
• Filter predicate push-down in a view
• Predicate move-around
• Common subexpression elimination
• Join predicate push down
• Outer to inner join conversion
• Subsumed subquery removal through window function,

Cost-based transformations.

• OR expansion—converts a query with ORs in the WHERE-clause into a UNION ALL of several queries without ORs, which is highly beneficial when the ORs refer to restrictions of different tables
• Materialized view rewrite
• Star transformation
• Conversion of set operator intersect into join and minus into anti-join

A large number of query blocks and interdependence of the transformations themselves add to the complexity of any cost-based transformation strategy. With Oracle Database 10g, the transformation engine has been redesigned as an extensible framework that can support new algorithms as needed, to ensure that Oracle can always quickly select the optimal transformation.

In addition, the Oracle Database 10g transformation framework supports several new state space search algorithms that ensure that the optimizer finds the best plan quickly, even when transformations are complex.

2. The estimator

The estimator measures cardinality (the number of rows); selectivity (the fraction of rows from a row set filtered by predicate); and cost, defined as resources (disk I/O, CPU usage, and memory) of the various operations (scanning a table or joining tables together, for instance) that will yield the data. The accuracy of these estimates depends on statistics—the object statistics and now, in Oracle Database 10g, system statistics. SQL profiles specific to any query are also used at this point to effectively estimate costs.

Oracle Database 10g CBO Uses a New Cost Model

More important,However, the cost model itself has changed. In Oracle9i Database, the CBO used an I/O cost model that evaluated everything primarily in terms of single block reads, largely ignoring CPU costs (or using constants to estimate CPU costs). In Oracle Database 10g, the cost model now includes system statistics that realistically capture CPU and I/O on your system with respect to the Oracle workload.

In Oracle Database 10g, the default cost model is CPU plus I/O, in which the cost unit is time: The CBO estimates execution time for a query by estimating the number of I/O operations, the type of I/O operations, and the number of CPU cycles the database will perform while executing the query. These estimates depend on the existence of system statistics, which the CBO uses to convert the number of CPU cycles and the number of I/Os into execution time. (Note that some operations, such as bitmap merge, sort, or hash joins, may not require any I/O but that all operations include a CPU component.)

Oracle Database 10g gathers two types of system statistics—statistics captured without a workload (noworkload) and statistics captured with a workload. Noworkload statistics capture I/O system performance—average I/O seek time and transfer speed—and CPU speed. When gathering noworkload statistics, the CBO issues sample reads of different sizes from the database's datafiles; it times every read and then uses statistical methods to compute average seek time and transfer speed. This takes from a few seconds to a few minutes. The CBO computes CPU speed in millions of cycles per second.

Workload statistics make the CBO aware of the workload. The system statistics captured during workload conditions identify whether the system is I/O- or CPU-bound; the CBO uses the data to adjust the cost of the plans accordingly. To gather workload statistics, execute these commands at the start and the conclusion of your workload:

dbms_stats.gather_system_stats(gathering_mode=>'start')

dbms_stats.gather_system_stats(gathering_mode=>'stop')

You can see the value by following query.

SQL> select sname, pname, pval1

from sys.aux_stats$;

CPUSPEEDNW, IOSEEKTIM, and IOTFRSPEED are noworkload statistics; SREADTIM, MREADTIM, CPUSPEED, MBRC, MAXTHR, and SLAVETHR represent workload statistics.

If both workload and noworkload statistics are available, the optimizer uses workload statistics.

• SREADTIM—single block read time (milliseconds)—is the average time Oracle takes to read a single block.
• MREADTIM—multiblock read time (milliseconds)—is the average time taken to read sequentially.
• MBRC—multiblock read count—is the blocks, on average, read during multiblock sequential reads. The CBO uses MBRC instead of thedb_ multiblock_read_count parameter during query optimization to compute costs for table and fast full index scans.
• MAXTHR—maximum I/O system throughput—is captured only if the database runs parallel queries.
• SLAVETHR—maximum slave I/O throughput—is captured only if the database runs parallel queries.

Another benefit of the Oracle Database 10g cost model is that it enables the CBO to use other techniques to create the fastest plan possible—for example, by reordering predicates in a query. Because the CBO knows the costs associated with a given query in terms of CPU and because the main ingredient in the cost of a predicate is CPU, the CBO can calculate costs with alternative predicate orderings and reorganize the predicates in the most efficient order.

For example, assume that for the following select statement, b <> 0 returns all rows in table t:

select * from t 

where a > 0 and b <>

If predicate order is a > 0 and b <> 0, then the predicate a > 0 will be evaluated just once.

The algorithms for ordering predicates consider the costs of various combinations of the predicate order and selectivity when determining the best order. This feature provides considerable improvement, especially for nonselective and expensive predicates.

3. The plan generator

The plan generator recombines the query in various ways, constructing alternative plans by using different join methods and join orders. The more table joins in the original query, the more alternative plans are generated and the longer the time it may take the CBO to find the "best" plan. Conversely, if there are few joins in a particular query or the joins are in the best order to begin with, the CBO will find the optimal plan quickly.

Ultimately, the CBO picks the best plan—the one with the lowest estimated cost relative to other plans. But precisely how the CBO measures the cost of processing any SQL statement—the cost model—is influenced by many factors, including several initialization parameters , starting with the optimizer_mode. The optimizer_mode sets the overall performance goal for the CBO in terms of throughput (the minimal time to return all rows) versus response time (the minimal time to return the first rows).

Conclusion

Oracle's cost-based optimizer is a strategic subsystem of Oracle Database. Given that the CBO improves with each new release of Oracle Database, you'll want to consider the ramifications that changes to the CBO may have on your Oracle database applications. If you're migrating an existing system to Oracle Database 10g, be sure to back up all the statistics your system uses for daily operations and test these statistics out with your applications in a test environment built around the new version of the database before moving into production.

Annexure - A

Initialization Parameters that Affect the CBO

select * from v$sys_optimizer_env ;

Optimizer_mode sets an overall performance goal for the CBO in terms of throughput versus response time. In Oracle Database 10g, the default setting (all_rows), primes the CBO to select for overall best throughput—the optimizer will choose the plan that delivers all the rows in a query in the shortest possible overall time. On the other hand, the first_rows setting will cause the optimizer to provide fastest response time by choosing the plan that delivers an initial subset of rows more quickly, and the remainder of the query in a longer period of time than the all_rows setting.

The "first_rows_n" setting (new as of Oracle9i Database) lets you balance the possible variances in this continuum (throughput versus response) more directly, by letting you set the number of rows to 1, 10, 100, or 1000. In Oracle Database 10g, if the query contains a rownum predicate, the CBO automatically switches to first_rows_n mode, deriving the value of n from the rownum predicate.

Another important optimizer initialization parameter is optimizer_dynamic_sampling. Statistics on database objects (size of tables, indexes, number of rows in tables, number of distinct values in columns, percentage of table below the high-water mark, and so on) play a key role in estimating costs (see Table 2), and if the CBO can't find up-to-date statistics to work with during the cost estimation phase of statement processing, it can dynamically sample statistical data at that time—if this parameter is set. The default level in Oracle Database 10g is 2, which means the CBO will use dynamic sampling on nonanalyzed tables to collect necessary statistics from them while optimizing the query.

Annexure - B

SQL profile

================

One of the artifacts produced by the SQL tuning mode is a SQL profile—auxiliary statistics specific to a given statement—that can be persisted to the data dictionary and used at runtime (during normal CBO operations) to improve SQL performance. SQL profiles are particularly useful for packaged applications (or any application for which you cannot access the source code).

SQL profiles use sampling to collect additional information; partial execution techniques to verify and adjust cost estimates; and execution history information for the SQL statement to modify parameter settings, if needed.