Report database tables for GoldenGate
There are several techniques
for determining the tables referenced by a packaged report
applications. The set of database tables includes the
base and
look up tables.
For packaged applications such as R12 E-Business suite, the
tables are determined using techniques such as:
§
Oracle TKPROF utility
§
Oracle audit
§
Oracle Enterprise Manager
(OEM)
Implementing the above three
techniques described next. However; this chapter uses TKPROF
to identify the query and referenced tables. The objective
of determining the query is for enabling query re-write by
creating materialized view and materialized view log. Unlike
TKPROF and Oracle audit that are part of the Oracle software
license, Oracle Enterprise Manager (OEM) SQL Monitor is a
licensable software.
TKPROF
utility and GoldenGate
The TKPROF utility needs the
report's session SQL trace file, which is achieved by
activating SQL trace using a designated database service
name alone, or a combination of the session ID, serial# and
the database service name.
Because using the database
service name is more efficient to determine the session's
SQL trace, dynamic database service names are created and
used by the report's session. The original report uses the
database service name 'ggs1',
the report running on the reporting instance uses the
database service name 'ggs2'.
Create
dedicated database service name for GoldenGate
To ensure monitoring the
specific report, we create a dedicated database
service name to
be used by the report's database session. The Oracle
supplied PL/SQL package
dbms_service
creates and manages non-default database service names.
Create service name
We use the
dbms_service.create_service procedure to create the
service name. We provide the service name (ggs1) and the
network service name (ggs1.precisetrace.com).
SQL> conn / as sysdba
Connected.
SQL> EXEC DBMS_SERVICE.CREATE_SERVICE('ggs1',
'ggs1.precisetrace.com');
PL/SQL procedure successfully
completed.
Prior to using the service
ggs1, it must be started using
dbms_service.start_service. We verify that the service
is registered with the database listener using
lsnrctl
command-line, lsnrctl
services
SQL> EXEC
DBMS_SERVICE.START_SERVICE('ggs1');
PL/SQL procedure successfully
completed.
We verify the service name is
listed for the database services. The parameter
service_name
supports multiple service names separated by comma.
SQL> SHOW PARAMETERS service
NAME
TYPE
VALUE
------------------------------------
----------- ---------------------------
service_names
string
ggs1.precisetrace.com
Next, we add the service name
ggs1 to TNSNAMES.ORA file.
ggs1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ggs-source)(PORT
= 1521))
(CONNECT_DATA =
(SERVICE_NAME = ggs1.precisetrace.com)
)
)
Now we can perform a quick
connection test, using the service name.
SQL> conn osm$repapi/oracle@ggs1
Connected.
We use
dbms_monitor
PL/SQL package to enable SQL trace for the sessions
established using the service name,
ggs1.
SQL> EXEC
DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name=>'ggs1');
PL/SQL procedure successfully
completed.
We run the application using
the database connect string
ggs1, which uses
the service name,
ggs1. Because the application is running with SQL trace
enabled, it starts generating trace files.
An alternative of
dbms_monitor is
to use dbms_system
to enable SQL trace for the sessions connected via the
service name ggs1.
The following anonymous PL/SQL block enables SQL trace for
sessions connected using
ggs1.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2
CURSOR c1 IS SELECT sid, serial#
3
FROM
v$session
4
WHERE
service_name='ggs1';
5
vsid
NUMBER;
6
vserial# NUMBER;
7
BEGIN
8
OPEN c1;
9
LOOP
10
FETCH c1 into vsid, vserial#;
11
EXIT WHEN c1%NOTFOUND;
12
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(vsid,
vserial#, true);
13
DBMS_OUTPUT.PUT_LINE('SID='||vsid||'
'||'SERIAL#='||vserial#);
14
END LOOP;
15
CLOSE C1;
16
END;
17
/
SID=17 SERIAL#=285
SID=40 SERIAL#=863
PL/SQL procedure successfully
completed.
TKPROF output
The TKPROF utility formats
the generated trace files for identifying the referenced
tables from SQL statements listed on the output file. The
directory location of the trace file is at
$ORACLE_BASE/diag/rdbms/s1e2/S1E2/trace. We sort the
files using the UNIX/Linux command
ls –ltr to
display the files sorted by modification time on reverse
order. The option
sys=no ignores complex recursive SQL statements
generated by SYS user during report runtime, this makes the
output file easier to identify the report runtime SQL
select
statements.
$ tkprof S1E2_ora_4222.trc
S1E2_ora_4222.txt SYS=NO
TKPROF: Release 11.2.0.3.0 -
Development on Mon Dec 16 15:14:35 2015
Copyright (c) 1982, 2015, Oracle
and/or its affiliates.
All rights reserved.
$ vi S1E2_ora_4222.txt
The output file is reviewed
for identifying the report queries. The report lists the
main query, but it is intuitive to return additional
sub-queries for supporting bind variables used by the main
query. Regardless of the report development technique,
TKPROF identifies the base tables for views.
The first SQL statement
references the
polcies table. This returned value may not be relevant
to the main query.
SQL ID: gkrxkpw4gkx57 Plan Hash:
3563362071
SELECT COUNT(*)
FROM
POLICIES
The second SQL statement
references policies
and customers
tables. This is the report main report query. The main query
is used to create the materialized view, which is necessary
to speed up report runtime.
SQL ID: bw1jwbvmp3ua7 Plan Hash:
583893952
SELECT P.CUST_NO, C.CUST_NAME,
P.POL_NO, P.POL_FROM, P.POL_TO, P.POL_VALUE,
P.POL_SUB_TOTAL, P.PT_CODE, P.DT_CODE,
P.POL_TOTAL
FROM
POLICIES P, CUSTOMERS C
WHERE
P.CUST_NO=C.CUST_NO
ORDER BY P.POL_NO
The third SQL statement
references
policy_types table. This is a look up table used by the
main query for referencing the column
pt_code.
SQL ID: d6tvhwxmabarf Plan Hash:
2250350636
SELECT PT_DESC
FROM
POLICY_TYPES
WHERE
PT_CODE = :B1
The fourth SQL statement
references
discount_types table. This is a
look up table
used by the main query for referencing the column
dt_code.
SQL ID: gfttngmmwcxzx Plan Hash:
1956721938
SELECT DT_DESC
FROM
DISCOUNT_TYPES
WHERE
DT_CODE = :B1
Referenced tables in
GoldenGate
The list of
sql id values
shows the report is referencing the database objects listed
below. Because Oracle GoldenGate performs change data
capture from tables, identify the base tables for views and
materialized views. The
sql id is used
for extracting performance related details from
v$session, v$sql
and v$sqltext.
Table 4-2 lists the tables
extracted from TKPROF formatted output.
|
Table
Name
|
Query
Type
|
|
POLICIES
|
Main query
|
|
CUSTOMERS
|
Main query
|
|
POLICY_TYPES
|
Look up query
|
|
DISCOUNT_TYPES
|
Look up query
|
Table 4-2: Referenced tables by the
report application
We verify the objects type
using the data dictionary view
dba_objects or
user_objects.
SQL> COLUMN object_name FORMAT A30
SQL> COLUMN object_type FORMAT A12
SQL> SELECT object_name, object_type
2
FROM
dba_objects
3
WHERE
object_name in ('POLICIES', 'CUSTOMERS', 'POLICY_TYPES',
'DISCOUNT_TYPES') AND
4
OWNER = 'OSM$REPAPI';
OBJECT_NAME
OBJECT_TYPE
------------------------------
------------
CUSTOMERS
TABLE
DISCOUNT_TYPES
TABLE
POLICIES
TABLE
POLICY_TYPES
TABLE
For objects of type
view query the
data dictionary
dba_views to identify the view base tables. Furthermore,
we identify the type of objects returned as a view so that
we may query from a view.
For object of type
materilized view
we query the data dictionary view
dba_mviews to
identify the materialized view base tables.
|
|
 |
|
Oracle GoldenGate 12c
The above is an excerpt from the upcoming
12c book
Oracle GoldenGate 12c: A Hands-on Guide to Data
Replication & Integration using Oracle & SQL Server.
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
