Spring AOP with Hibernate
http://www.springframework.org/docs/wiki/Spring_AOP_with_Hibernate.html
External articles & tutorials
http://www.hibernate.org/78.html
Hibernate / Spring / Middlegen / XDoclet Tutorial v1.5
http://www.warfrog.com/hibernatetutorial/
Object-relation mapping without the container
http://www.ibm.com/developerworks/library/j-hibern/?ca=dnt-515
JNIUS for Hibernate
http://www.hibernate.org/226.html
Appfuse
http://appfuse.org/display/APF/Home
2007/07/30
2007/07/26
Session-level statspack
Session-level statspack
Filed under: Performance — tanelp @ 3:23 pm
Statspack is a useful tool for easily gathering and reporting some Oracle’s historical workload statistics. However it has its limitations and problems:
One of them that in past it used to record only Oracle statistics, measured from inside Oracle. This made Oracle and people using Oracle ignore other crucial statistics like OS workload below Oracle. For example statspack may show you heavy latch contention as the performance problem, while looking at vmstat output one would see that the server just has been heavily overloaded with numerous other jobs (like multiple backup, export and compress jobs overrunning their run-window) and the latch contention is just a symptom of CPU starvation.
This has been somewhat addressed by adding V$OSSTAT to Oracle 10g and statspack now gathers this info as well. Now we need people to start looking into those stats.
Another problem with statspack is which is described in Dan Fink’s blog, about cursor-level CPU accounting limitations before 10gR2.
However neither of those problems are the main limitations of statspack. The main problem is that statspack samples it’s statistic snapshots from system level views like V$SYSSTAT and V$SYSTEM_EVENT. If every single session in your database is performing exactly the same (kind) of operation, then system level aggregates might be quite OK to get an overview what’s going on in the database. But from the moment you start having different kind of sessions (e.g. OLTP vs batch vs data feed vs report) in your database, then system level aggregates used by statspack do heavily distort what the reality looks like for specific sessions.
There’s another gotcha - by default statspack excludes the “SQL*Net message from client” wait event from main report section, stating that it is not important to look into idle events. However by that we will throw away an important piece of information when diagnosing end-to-end performance, the end user experience. The bad thing is that even if we did include the SQL*Net wait event in our reports (by deleting the corresponding row from STATS$IDLE_EVENT), then we would still have no idea how many of those gazillions of system-wide SQL*Net client wait seconds do belong to our specific session or user.
Statspack doesn’t measure session-experience, it just gives you a system-wide aggregate, which cannot be translated back to individual session statistics (just as you can not convert a hash value back to original value - most of the information is just lost!).
How to get session-level detailed overview of database performance, with historical reporting capability then?
Oracle 10g addresses this partially with ASH and AWR and DBMS_MONITOR’s selective statistic sampling ( V$SERV_MOD_ACT_STATS and V$CLIENT_STATS )
While ASH and AWR are both awesome products, they are not usable in Standard Edition nor before 10g ( you may want to check out Kyle Hailey’s ASH simulator instead ) and for using them you need to pay a separate license fee.
So let me introduce my attempt to solve those problems ( NB! Free stuff!!! ;):
The poor-man’s version of Automatic Workload Repository: It’s called… um… Semi-Automatic Workload Repository :)
The clever-man’s addition to statspack, which takes session-level snapshots: In other words, Sesspack.
The idea is very simple, just take snapshots of V$SESSION_EVENT and some V$SESSTAT (and V$SESS_TIME_MODEL in 10g) statistics and store those in a repository just like statspack does. Snapshots are taken using sesspack.snap_xyz procedures. As a parameter to these procedures I can pass a specific SID, a group of SIDs, a Oracle db username a OS-username or whatever filtering fields can be found from V$SESSION.
Once you have taken your snapshots when running the workload, you can run a report which calculates statistic and wait event deltas between snapshots and that it.
A demo?
The simplest one is to just snap my own session details:
The list.sql has shown that I have two performance snapshots, with ID 1 and 2, so I’ll run a delta report between them with script srs.sql (SAWR Report by SessionID):
An overview of what all background processes were doing at that time :)
If you ever had to diagnose performance issues in a large-scale variable-workload environment with lots of completely different activity going on, you already see the value of session-level statistic snapshots.
When you read the readme, you see that there are various options for taking selective snapshots, like running:
Also it is possible to snap based on completely custom conditions, using whatever SQL you write to return list of SID’s:
sesspack.snap_sidlist_internal(’select sid from v$session where state=”ACTIVE” and last_call_et > 300′);
sesspack.snap_sidlist_internal(’select sid from v$session where logon_time > sysdate - 1/48′);
Note that there are few crucial issues which need resolving in the snapshot engine (for example, waits are not recorded in V$SESSION_EVENT before event ends or times out, this is a problem with long waits exceeding snapshot interval).
The major work yet to be done is building easy reporting and visualizing capability as this is critical for any useful performance product.
I have experimented with Oracle Application Express for providing a convenient GUI and charting mechanism. Even though APEX charting is still not flexible enough for complex performance data visualization, nevertheless it looks promising! I will release it in version 0.06 perhaps, once rest of the crucial base features have been implemented. I’ll keep you posted.
It all is plain PL/SQL, you can download the package from here: http://www.tanelpoder.com/files/sesspack_0.04.zip
All feedback is appreciated, enjoy!
Filed under: Performance — tanelp @ 3:23 pm
Statspack is a useful tool for easily gathering and reporting some Oracle’s historical workload statistics. However it has its limitations and problems:
One of them that in past it used to record only Oracle statistics, measured from inside Oracle. This made Oracle and people using Oracle ignore other crucial statistics like OS workload below Oracle. For example statspack may show you heavy latch contention as the performance problem, while looking at vmstat output one would see that the server just has been heavily overloaded with numerous other jobs (like multiple backup, export and compress jobs overrunning their run-window) and the latch contention is just a symptom of CPU starvation.
This has been somewhat addressed by adding V$OSSTAT to Oracle 10g and statspack now gathers this info as well. Now we need people to start looking into those stats.
Another problem with statspack is which is described in Dan Fink’s blog, about cursor-level CPU accounting limitations before 10gR2.
However neither of those problems are the main limitations of statspack. The main problem is that statspack samples it’s statistic snapshots from system level views like V$SYSSTAT and V$SYSTEM_EVENT. If every single session in your database is performing exactly the same (kind) of operation, then system level aggregates might be quite OK to get an overview what’s going on in the database. But from the moment you start having different kind of sessions (e.g. OLTP vs batch vs data feed vs report) in your database, then system level aggregates used by statspack do heavily distort what the reality looks like for specific sessions.
There’s another gotcha - by default statspack excludes the “SQL*Net message from client” wait event from main report section, stating that it is not important to look into idle events. However by that we will throw away an important piece of information when diagnosing end-to-end performance, the end user experience. The bad thing is that even if we did include the SQL*Net wait event in our reports (by deleting the corresponding row from STATS$IDLE_EVENT), then we would still have no idea how many of those gazillions of system-wide SQL*Net client wait seconds do belong to our specific session or user.
Statspack doesn’t measure session-experience, it just gives you a system-wide aggregate, which cannot be translated back to individual session statistics (just as you can not convert a hash value back to original value - most of the information is just lost!).
How to get session-level detailed overview of database performance, with historical reporting capability then?
Oracle 10g addresses this partially with ASH and AWR and DBMS_MONITOR’s selective statistic sampling ( V$SERV_MOD_ACT_STATS and V$CLIENT_STATS )
While ASH and AWR are both awesome products, they are not usable in Standard Edition nor before 10g ( you may want to check out Kyle Hailey’s ASH simulator instead ) and for using them you need to pay a separate license fee.
So let me introduce my attempt to solve those problems ( NB! Free stuff!!! ;):
The poor-man’s version of Automatic Workload Repository: It’s called… um… Semi-Automatic Workload Repository :)
The clever-man’s addition to statspack, which takes session-level snapshots: In other words, Sesspack.
The idea is very simple, just take snapshots of V$SESSION_EVENT and some V$SESSTAT (and V$SESS_TIME_MODEL in 10g) statistics and store those in a repository just like statspack does. Snapshots are taken using sesspack.snap_xyz procedures. As a parameter to these procedures I can pass a specific SID, a group of SIDs, a Oracle db username a OS-username or whatever filtering fields can be found from V$SESSION.
Once you have taken your snapshots when running the workload, you can run a report which calculates statistic and wait event deltas between snapshots and that it.
A demo?
The simplest one is to just snap my own session details:
SQL> exec sesspack.snap_me;
PL/SQL procedure successfully completed.
SQL> select avg(length(text)) from dba_source;
AVG(LENGTH(TEXT))
-----------------
114.01304
SQL> exec sesspack.snap_me;
PL/SQL procedure successfully completed.
SQL> @list
Snapshot Snapped
Snap ID Snapshot Time Taken By Mode Sessions
-------- -------------------- -------------------- ---------------------------------------- --------
1 2007-06-24 14:36:47 PERFSTAT SNAP_ME: PERFSTAT 1
2 2007-06-24 14:36:52 PERFSTAT SNAP_ME: PERFSTAT 1
The list.sql has shown that I have two performance snapshots, with ID 1 and 2, so I’ll run a delta report between them with script srs.sql (SAWR Report by SessionID):
SQL> set tab off
SQL> @srs 1 2
SNAPSHOT_BEGIN SNAPSHOT_END DUR_SEC DUR_MIN
----------------- ----------------- ---------- ----------
20070624 14:36:47 20070624 14:36:52 5 .083333333
ms/ ms in Waits in Avg Wait
SID AUDSID Event Name % Total sec snapshot snapshot ms
---------- ---------- ---- --------------------------------------------- ------------ ------- ----------- --------- --------
146 130096 db file scattered read |####### | 676 3378 338 10
CPU Usage |### | 260 1300 0 1300
db file sequential read |# | 44 220 6 37
SQL*Net message from client | | 1 3 3 1
log file sync | | 0 1 1 1
SQL*Net message to client | | 0 0 3 0
6 rows selected.
Another example which should bring the usefulness of sesspack out better:
I dirtied some buffer cache buffers using the delete command, then ran a alter system checkpoint between two snapshots of all background process activity (sesspack.snap_bg).
Tanel@Prod01> delete t;
50705 rows deleted.
Tanel@Prod01> exec sesspack.snap_bg;
PL/SQL procedure successfully completed.
Tanel@Prod01> alter system checkpoint;
System altered.
Tanel@Prod01> exec sesspack.snap_bg;
PL/SQL procedure successfully completed.
Tanel@Prod01> @list
Snapshot Snapped
Snap ID Snapshot Time Taken By Mode Sessions
-------- -------------------- -------------------- ---------------------------------------- --------
1 2007-06-24 14:36:47 PERFSTAT SNAP_ME: PERFSTAT 1
2 2007-06-24 14:36:52 PERFSTAT SNAP_ME: PERFSTAT 1
5 2007-06-24 14:57:49 TANEL SNAP_BG: 14
6 2007-06-24 14:57:55 TANEL SNAP_BG: 14
Tanel@Prod01> @ srs 5 6
SNAPSHOT_BEGIN SNAPSHOT_END DUR_SEC DUR_MIN
----------------- ----------------- ---------- ----------
20070624 14:57:49 20070624 14:57:55 6 .1
ms/ ms in Waits in Avg Wait
SID AUDSID Event Name % Total sec snapshot snapshot ms
------- ---------- ---- --------------------------------------------- ------------ ------- ----------- --------- --------
160 0 rdbms ipc message |##########| 1000 5999 6 1000
161 0 rdbms ipc message |##### | 500 3000 1 3000
162 0 rdbms ipc message |######### | 833 5000 1 5000
165 0 rdbms ipc message |##### | 496 2974 4 744
control file parallel write |## | 159 953 11 87
control file sequential read |# | 57 341 26 13
direct path write | | 0 2 12 0
direct path read | | 0 0 12 0
166 0 rdbms ipc message |##########| 998 5988 3 1996
log file parallel write | | 1 4 2 2
167 0 rdbms ipc message |######### | 818 4911 4 1228
db file parallel write |## | 123 737 93 8
168 0 rdbms ipc message |##########| 1002 6010 2 3005
169 0 rdbms ipc message |##########| 1000 6000 2 3000
170 0 pmon timer |##########| 1000 6000 2 3000
15 rows selected.
An overview of what all background processes were doing at that time :)
If you ever had to diagnose performance issues in a large-scale variable-workload environment with lots of completely different activity going on, you already see the value of session-level statistic snapshots.
When you read the readme, you see that there are various options for taking selective snapshots, like running:
sesspack.snap_sid(57);
sesspack.snap_sid(’150,165,177,202′);
sesspack.snap_orauser(’REPORT_USER’);
sesspack.snap_program(’sqlplus.exe’);
sesspack.snap_machine(’nycprd0005118′);
Also it is possible to snap based on completely custom conditions, using whatever SQL you write to return list of SID’s:
sesspack.snap_sidlist_internal(’select sid from v$session where state=”ACTIVE” and last_call_et > 300′);
sesspack.snap_sidlist_internal(’select sid from v$session where logon_time > sysdate - 1/48′);
Note that there are few crucial issues which need resolving in the snapshot engine (for example, waits are not recorded in V$SESSION_EVENT before event ends or times out, this is a problem with long waits exceeding snapshot interval).
The major work yet to be done is building easy reporting and visualizing capability as this is critical for any useful performance product.
I have experimented with Oracle Application Express for providing a convenient GUI and charting mechanism. Even though APEX charting is still not flexible enough for complex performance data visualization, nevertheless it looks promising! I will release it in version 0.06 perhaps, once rest of the crucial base features have been implemented. I’ll keep you posted.
It all is plain PL/SQL, you can download the package from here: http://www.tanelpoder.com/files/sesspack_0.04.zip
All feedback is appreciated, enjoy!
Fw how to use DBMS_PROFILER
DBMS_PROFILER
The DBMS_PROFILER package was introduced in Oracle8i to allows developers to profile the run-time behaviour of PL/SQL code, making it easier to identify performance bottlenecks which can then be investigated more closely.
The first step is to install the DBMS_PROFILER package:
CONNECT sys/password@service AS SYSDBA
@$ORACLE_HOME/rdbms/admin/profload.sql
CREATE USER profiler IDENTIFIED BY profiler DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANT connect TO profiler;
CREATE PUBLIC SYNONYM plsql_profiler_runs FOR profiler.plsql_profiler_runs;
CREATE PUBLIC SYNONYM plsql_profiler_units FOR profiler.plsql_profiler_units;
CREATE PUBLIC SYNONYM plsql_profiler_data FOR profiler.plsql_profiler_data;
CREATE PUBLIC SYNONYM plsql_profiler_runnumber FOR profiler.plsql_profiler_runnumber;
CONNECT profiler/profiler@service
@$ORACLE_HOME/rdbms/admin/proftab.sql
GRANT SELECT ON plsql_profiler_runnumber TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_data TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_units TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_runs TO PUBLIC;
Next we create a dummy procedure to profile:
CREATE OR REPLACE PROCEDURE do_something (p_times IN NUMBER) AS
l_dummy NUMBER;
BEGIN
FOR i IN 1 .. p_times LOOP
SELECT l_dummy + 1
INTO l_dummy
FROM dual;
END LOOP;
END;
/
Next we start the profiler, run our procedure and stop the profiler:
DECLARE
l_result BINARY_INTEGER;
BEGIN
l_result := DBMS_PROFILER.start_profiler(run_comment => 'do_something: ' || SYSDATE);
do_something(p_times => 100);
l_result := DBMS_PROFILER.stop_profiler;
END;
/
With the profile complete we can analyze the data to see which bits of the process took the most time, with all times presented in nanoseconds. First we check out which runs we have:
SET LINESIZE 200
SET TRIMOUT ON
COLUMN runid FORMAT 99999
COLUMN run_comment FORMAT A50
SELECT runid,
run_date,
run_comment,
run_total_time
FROM plsql_profiler_runs
ORDER BY runid;
RUNID RUN_DATE RUN_COMMENT RUN_TOTAL_TIME
----- --------- ---------------------------------- --------------
1 21-AUG-03 do_something: 21-AUG-2003 14:51:54 131072000
We can then use the appropriate RUNID value in the following query:
COLUMN runid FORMAT 99999
COLUMN unit_number FORMAT 99999
COLUMN unit_type FORMAT A20
COLUMN unit_owner FORMAT A20
SELECT u.runid,
u.unit_number,
u.unit_type,
u.unit_owner,
u.unit_name,
d.line#,
d.total_occur,
d.total_time,
d.min_time,
d.max_time
FROM plsql_profiler_units u
JOIN plsql_profiler_data d ON u.runid = d.runid AND u.unit_number = d.unit_number
WHERE u.runid = 1
ORDER BY u.unit_number, d.line#;
RUNID UNIT_NU UNIT_TYPE UNIT_OWNER UNIT_NAME LINE# TOTAL_OCCUR TOTAL_TIME MIN_TIME MAX_TIME
----- ------- --------------- ----------- ------------ ----- ----------- ---------- -------- --------
1 1 ANONYMOUS BLOCK 4 1 0 0 0
1 1 ANONYMOUS BLOCK 5 1 0 0 0
1 1 ANONYMOUS BLOCK 6 1 0 0 0
1 2 PROCEDURE MY_SCHEMA DO_SOMETHING 4 101 0 0 0
1 2 PROCEDURE MY_SCHEMA DO_SOMETHING 5 100 17408000 0 2048000
5 rows selected.
The results of this query show that line 4 of the DO_SOMETHING procedure ran 101 times but took very little time, while line 5 ran 100 times and took proportionately more time. We can check the line numbers of the source using the following query:
SELECT line || ' : ' || text
FROM all_source
WHERE owner = 'MY_SCHEMA'
AND type = 'PROCEDURE'
AND name = 'DO_SOMETHING';
LINE||':'||TEXT
---------------------------------------------------
1 : PROCEDURE do_something (p_times IN NUMBER) AS
2 : l_dummy NUMBER;
3 : BEGIN
4 : FOR i IN 1 .. p_times LOOP
5 : SELECT l_dummy + 1
6 : INTO l_dummy
7 : FROM dual;
8 : END LOOP;
9 : END;
As expected, the query took proportionately more time than the procedural loop. Assuming this were a real procedure we could use the DBMS_TRACE or the SQL trace facilities to investigate the problem area further.
For further information see:
DBMS_PROFILER
DBMS_TRACE
Hope this helps. Regards Tim...
The DBMS_PROFILER package was introduced in Oracle8i to allows developers to profile the run-time behaviour of PL/SQL code, making it easier to identify performance bottlenecks which can then be investigated more closely.
The first step is to install the DBMS_PROFILER package:
CONNECT sys/password@service AS SYSDBA
@$ORACLE_HOME/rdbms/admin/profload.sql
CREATE USER profiler IDENTIFIED BY profiler DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANT connect TO profiler;
CREATE PUBLIC SYNONYM plsql_profiler_runs FOR profiler.plsql_profiler_runs;
CREATE PUBLIC SYNONYM plsql_profiler_units FOR profiler.plsql_profiler_units;
CREATE PUBLIC SYNONYM plsql_profiler_data FOR profiler.plsql_profiler_data;
CREATE PUBLIC SYNONYM plsql_profiler_runnumber FOR profiler.plsql_profiler_runnumber;
CONNECT profiler/profiler@service
@$ORACLE_HOME/rdbms/admin/proftab.sql
GRANT SELECT ON plsql_profiler_runnumber TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_data TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_units TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_runs TO PUBLIC;
Next we create a dummy procedure to profile:
CREATE OR REPLACE PROCEDURE do_something (p_times IN NUMBER) AS
l_dummy NUMBER;
BEGIN
FOR i IN 1 .. p_times LOOP
SELECT l_dummy + 1
INTO l_dummy
FROM dual;
END LOOP;
END;
/
Next we start the profiler, run our procedure and stop the profiler:
DECLARE
l_result BINARY_INTEGER;
BEGIN
l_result := DBMS_PROFILER.start_profiler(run_comment => 'do_something: ' || SYSDATE);
do_something(p_times => 100);
l_result := DBMS_PROFILER.stop_profiler;
END;
/
With the profile complete we can analyze the data to see which bits of the process took the most time, with all times presented in nanoseconds. First we check out which runs we have:
SET LINESIZE 200
SET TRIMOUT ON
COLUMN runid FORMAT 99999
COLUMN run_comment FORMAT A50
SELECT runid,
run_date,
run_comment,
run_total_time
FROM plsql_profiler_runs
ORDER BY runid;
RUNID RUN_DATE RUN_COMMENT RUN_TOTAL_TIME
----- --------- ---------------------------------- --------------
1 21-AUG-03 do_something: 21-AUG-2003 14:51:54 131072000
We can then use the appropriate RUNID value in the following query:
COLUMN runid FORMAT 99999
COLUMN unit_number FORMAT 99999
COLUMN unit_type FORMAT A20
COLUMN unit_owner FORMAT A20
SELECT u.runid,
u.unit_number,
u.unit_type,
u.unit_owner,
u.unit_name,
d.line#,
d.total_occur,
d.total_time,
d.min_time,
d.max_time
FROM plsql_profiler_units u
JOIN plsql_profiler_data d ON u.runid = d.runid AND u.unit_number = d.unit_number
WHERE u.runid = 1
ORDER BY u.unit_number, d.line#;
RUNID UNIT_NU UNIT_TYPE UNIT_OWNER UNIT_NAME LINE# TOTAL_OCCUR TOTAL_TIME MIN_TIME MAX_TIME
----- ------- --------------- ----------- ------------ ----- ----------- ---------- -------- --------
1 1 ANONYMOUS BLOCK
1 1 ANONYMOUS BLOCK
1 1 ANONYMOUS BLOCK
1 2 PROCEDURE MY_SCHEMA DO_SOMETHING 4 101 0 0 0
1 2 PROCEDURE MY_SCHEMA DO_SOMETHING 5 100 17408000 0 2048000
5 rows selected.
The results of this query show that line 4 of the DO_SOMETHING procedure ran 101 times but took very little time, while line 5 ran 100 times and took proportionately more time. We can check the line numbers of the source using the following query:
SELECT line || ' : ' || text
FROM all_source
WHERE owner = 'MY_SCHEMA'
AND type = 'PROCEDURE'
AND name = 'DO_SOMETHING';
LINE||':'||TEXT
---------------------------------------------------
1 : PROCEDURE do_something (p_times IN NUMBER) AS
2 : l_dummy NUMBER;
3 : BEGIN
4 : FOR i IN 1 .. p_times LOOP
5 : SELECT l_dummy + 1
6 : INTO l_dummy
7 : FROM dual;
8 : END LOOP;
9 : END;
As expected, the query took proportionately more time than the procedural loop. Assuming this were a real procedure we could use the DBMS_TRACE or the SQL trace facilities to investigate the problem area further.
For further information see:
DBMS_PROFILER
DBMS_TRACE
Hope this helps. Regards Tim...
Fw how to use DBMS_TRACE
DBMS_TRACE
The DBMS_TRACE package provides an API to allow the actions of PL/SQL programs to be traced. The scope and volume of the tracing is user configurable. This package can be used in conjunction with the DBMS_PROFILER package to identify performance bottlenecks.
The first step is to install the tables which will hold the trace data:
CONNECT sys/password@service AS SYSDBA
@$ORACLE_HOME/rdbms/admin/tracetab.sql
CREATE PUBLIC SYNONYM plsql_trace_runs FOR plsql_trace_runs;
CREATE PUBLIC SYNONYM plsql_trace_events FOR plsql_trace_events;
CREATE PUBLIC SYNONYM plsql_trace_runnumber FOR plsql_trace_runnumber;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_trace_runs TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_trace_events TO PUBLIC;
GRANT SELECT ON plsql_trace_runnumber TO PUBLIC;
Next we create a dummy procedure to trace:
CREATE OR REPLACE PROCEDURE do_something (p_times IN NUMBER) AS
l_dummy NUMBER;
BEGIN
FOR i IN 1 .. p_times LOOP
SELECT l_dummy + 1
INTO l_dummy
FROM dual;
END LOOP;
END;
/
Next we run our procedure three times with different tracing levels:
DECLARE
l_result BINARY_INTEGER;
BEGIN
DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_calls);
do_something(p_times => 100);
DBMS_TRACE.clear_plsql_trace;
DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_sql);
do_something(p_times => 100);
DBMS_TRACE.clear_plsql_trace;
DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_lines);
do_something(p_times => 100);
DBMS_TRACE.clear_plsql_trace;
END;
/
With the tracing complete we can identify the available RUNIDs using the following query:
SELECT r.runid,
TO_CHAR(r.run_date, 'DD-MON-YYYY HH24:MI:SS') AS run_date,
r.run_owner
FROM plsql_trace_runs r
ORDER BY r.runid;
RUNID RUN_DATE RUN_OWNER
---------- -------------------- -------------------------------
1 22-AUG-2003 08:27:18 TIM_HALL
2 22-AUG-2003 08:27:18 TIM_HALL
3 22-AUG-2003 08:27:18 TIM_HALL
We can then use the appropriate RUNID in the following query to look at the trace:
SET LINESIZE 200
SET TRIMOUT ON
COLUMN runid FORMAT 99999
COLUMN event_seq FORMAT 99999
COLUMN event_unit_owner FORMAT A20
COLUMN event_unit FORMAT A20
COLUMN event_unit_kind FORMAT A20
COLUMN event_comment FORMAT A30
SELECT e.runid,
e.event_seq,
TO_CHAR(e.event_time, 'DD-MON-YYYY HH24:MI:SS') AS event_time,
e.event_unit_owner,
e.event_unit,
e.event_unit_kind,
e.proc_line,
e.event_comment
FROM plsql_trace_events e
WHERE e.runid = 1
ORDER BY e.runid, e.event_seq;
The content of the trace record depends on the trace level being used. The available options are:
trace_all_calls constant INTEGER := 1;
trace_enabled_calls constant INTEGER := 2;
trace_all_exceptions constant INTEGER := 4;
trace_enabled_exceptions constant INTEGER := 8;
trace_all_sql constant INTEGER := 32;
trace_enabled_sql constant INTEGER := 64;
trace_all_lines constant INTEGER := 128;
trace_enabled_lines constant INTEGER := 256;
trace_stop constant INTEGER := 16384;
trace_pause constant INTEGER := 4096;
trace_resume constant INTEGER := 8192;
trace_limit constant INTEGER := 16;
Trace can be limited to specified programs by starting the trace with the DBMS_TRACE.%_enabled_% options. A program can have trace enabled using one of the following methods:
ALTER SESSION SET PLSQL_DEBUG=TRUE;
CREATE OR REPLACE [PROCEDURE | FUNCTION | PACKAGE BODY] ...
or:
ALTER [PROCEDURE | FUNCTION | PACKAGE] COMPILE DEBUG [BODY];
For further information see:
DBMS_TRACE
DBMS_PROFILER
Hope this helps. Regards Tim...
The DBMS_TRACE package provides an API to allow the actions of PL/SQL programs to be traced. The scope and volume of the tracing is user configurable. This package can be used in conjunction with the DBMS_PROFILER package to identify performance bottlenecks.
The first step is to install the tables which will hold the trace data:
CONNECT sys/password@service AS SYSDBA
@$ORACLE_HOME/rdbms/admin/tracetab.sql
CREATE PUBLIC SYNONYM plsql_trace_runs FOR plsql_trace_runs;
CREATE PUBLIC SYNONYM plsql_trace_events FOR plsql_trace_events;
CREATE PUBLIC SYNONYM plsql_trace_runnumber FOR plsql_trace_runnumber;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_trace_runs TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_trace_events TO PUBLIC;
GRANT SELECT ON plsql_trace_runnumber TO PUBLIC;
Next we create a dummy procedure to trace:
CREATE OR REPLACE PROCEDURE do_something (p_times IN NUMBER) AS
l_dummy NUMBER;
BEGIN
FOR i IN 1 .. p_times LOOP
SELECT l_dummy + 1
INTO l_dummy
FROM dual;
END LOOP;
END;
/
Next we run our procedure three times with different tracing levels:
DECLARE
l_result BINARY_INTEGER;
BEGIN
DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_calls);
do_something(p_times => 100);
DBMS_TRACE.clear_plsql_trace;
DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_sql);
do_something(p_times => 100);
DBMS_TRACE.clear_plsql_trace;
DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_lines);
do_something(p_times => 100);
DBMS_TRACE.clear_plsql_trace;
END;
/
With the tracing complete we can identify the available RUNIDs using the following query:
SELECT r.runid,
TO_CHAR(r.run_date, 'DD-MON-YYYY HH24:MI:SS') AS run_date,
r.run_owner
FROM plsql_trace_runs r
ORDER BY r.runid;
RUNID RUN_DATE RUN_OWNER
---------- -------------------- -------------------------------
1 22-AUG-2003 08:27:18 TIM_HALL
2 22-AUG-2003 08:27:18 TIM_HALL
3 22-AUG-2003 08:27:18 TIM_HALL
We can then use the appropriate RUNID in the following query to look at the trace:
SET LINESIZE 200
SET TRIMOUT ON
COLUMN runid FORMAT 99999
COLUMN event_seq FORMAT 99999
COLUMN event_unit_owner FORMAT A20
COLUMN event_unit FORMAT A20
COLUMN event_unit_kind FORMAT A20
COLUMN event_comment FORMAT A30
SELECT e.runid,
e.event_seq,
TO_CHAR(e.event_time, 'DD-MON-YYYY HH24:MI:SS') AS event_time,
e.event_unit_owner,
e.event_unit,
e.event_unit_kind,
e.proc_line,
e.event_comment
FROM plsql_trace_events e
WHERE e.runid = 1
ORDER BY e.runid, e.event_seq;
The content of the trace record depends on the trace level being used. The available options are:
trace_all_calls constant INTEGER := 1;
trace_enabled_calls constant INTEGER := 2;
trace_all_exceptions constant INTEGER := 4;
trace_enabled_exceptions constant INTEGER := 8;
trace_all_sql constant INTEGER := 32;
trace_enabled_sql constant INTEGER := 64;
trace_all_lines constant INTEGER := 128;
trace_enabled_lines constant INTEGER := 256;
trace_stop constant INTEGER := 16384;
trace_pause constant INTEGER := 4096;
trace_resume constant INTEGER := 8192;
trace_limit constant INTEGER := 16;
Trace can be limited to specified programs by starting the trace with the DBMS_TRACE.%_enabled_% options. A program can have trace enabled using one of the following methods:
ALTER SESSION SET PLSQL_DEBUG=TRUE;
CREATE OR REPLACE [PROCEDURE | FUNCTION | PACKAGE BODY] ...
or:
ALTER [PROCEDURE | FUNCTION | PACKAGE]
For further information see:
DBMS_TRACE
DBMS_PROFILER
Hope this helps. Regards Tim...
2007/07/25
nic alias
configure a network interface and alias. For example:
To setup eth0:0 alias type the following command as the root user:
Verify alias is up and running using following command:
However if you reboot system you will lost your alias. To make it permanent you need to add it network configuration file:
If you are using Debian Linux
Open the file /etc/network/interfaces:
Append text as follows:
Save the file and restart the network:
If you are using Red Hat / Fedora Linux
Copy etc/sysconfig/network-scripts/ifcfg-eth0 file etc/sysconfig/network-scripts/ifcfg-eth0:0 -
Open file /etc/sysconfig/network-scripts/ifcfg-eth0:0 using vi text editor:
Find entry that read as follows:
Replace with:
Find entry that read as follows:
Replace it with your actual IP address:
At the end your file should like as follows:
Open file /etc/sysconfig/network-scripts/ifcfg-eth0 and make sure file does not have a GATEWAY= entry:
Find the entry that read as follows:
Remove or comment it out by prefixing # (hash) :
Save the file. Add the GATEWAY= to your /etc/sysconfig/network:
Append or modify GATEWAY entry:
Save the file. Reboot the system or run the following command:
OR
eth0 NIC IP - 192.168.1.5
eth0:0 first NIC alias: 192.168.1.6
To setup eth0:0 alias type the following command as the root user:
# ifconfig eth0:0 192.168.1.6 up
Verify alias is up and running using following command:
# ifconfig -a
# ping 192.168.1.6
However if you reboot system you will lost your alias. To make it permanent you need to add it network configuration file:
If you are using Debian Linux
Open the file /etc/network/interfaces:
# vi /etc/network/interfaces
Append text as follows:
auto eth0:1
iface eth0:1 inet static
name Ethernet alias LAN card
address 192.168.1.7
netmask 255.255.255.0
broadcast 192.168.1.255
network 192.168.1.0
Save the file and restart the network:
# /etc/init.d/networking restart
If you are using Red Hat / Fedora Linux
Copy etc/sysconfig/network-scripts/ifcfg-eth0 file etc/sysconfig/network-scripts/ifcfg-eth0:0 -
# cp /etc/sysconfig/network-scripts/ifcfg-eth0 /etc/sysconfig/network-scripts/ifcfg-eth0:0
Open file /etc/sysconfig/network-scripts/ifcfg-eth0:0 using vi text editor:
# vi /etc/sysconfig/network-scripts/ifcfg-eth0:0
Find entry that read as follows:
DEVICE=eth0
Replace with:
DEVICE=eth0:0
Find entry that read as follows:
IPADDR=xxx.xxx.xxx.xxx
Replace it with your actual IP address:
IPADDR=192.168.1.7
At the end your file should like as follows:
DEVICE=eth0:0
IPADDR=192.168.1.7
NETMASK=255.255.255.0
NETWORK=192.168.1.0
ONBOOT=yes
NAME=eth0:0
Open file /etc/sysconfig/network-scripts/ifcfg-eth0 and make sure file does not have a GATEWAY= entry:
# vi /etc/sysconfig/network-scripts/ifcfg-eth0
Find the entry that read as follows:
GATEWAY=192.168.1.254
Remove or comment it out by prefixing # (hash) :
# GATEWAY=192.168.1.254
Save the file. Add the GATEWAY= to your /etc/sysconfig/network:
# vi /etc/sysconfig/network
Append or modify GATEWAY entry:
GATEWAY=192.168.1.254
Save the file. Reboot the system or run the following command:
# ifup eth0:0
OR
# service network restart
2007/07/24
ORA-08104
発生したORA-08104エラーについて
エラー原因と対応方法を報告します。
背景：
テーブルのANALYZE失敗：
SQL> analyze table tmuser.m01_cust compute statistics;
analyze table tmuser.m01_cust compute statistics
*
ERROR at line 1:
ORA-08104: this index object 6571 is being online built or rebuilt
そして上記エラー対象の索引削除も失敗：
SQL> drop index IDX_M01_ADDR_CODE;
drop index IDX_M01_ADDR_CODE
*
ERROR at line 1:
ORA-08104: this index object 6571 is being online built or rebuilt
対応方法：
① 「REBUILD FLAG」が残っている索引を確認する。
SQL> select obj# from ind$ where bitand(flags, 512)=512;
OBJ#
----------
6571
② 対象索引の「REBUILD FLAG」を更新する。
SQL> update ind$ set flags=flags-512 where obj#=6571;
1 row updated.
SQL> commit;
Commit complete.
③ 更新後の「REBUILD FLAG」状態を確認する、「０」であれば、正常に戻ることを判断できる。
SQL> select bitand(flags, 512) from ind$ where obj#=6571;
BITAND(FLAGS,512)
-----------------
0
④ 索引削除できました。
SQL> conn tmuser/tmadmin
Connected.
SQL> drop index IDX_M01_ADDR_CODE;
Index dropped.
⑤ 索引再作成してANALYZEもできました。
SQL> CREATE INDEX TMUSER.IDX_M01_ADDR_CODE
ON TMUSER.M01_CUST
(ADDR_CODE_PREF, ADDR_CODE_CITY, ADDR_CODE_ZONE)
TABLESPACE USERS_INDEX_M01
STORAGE(INITIAL 128M MINEXTENTS 1) NOLOGGING;
Index created.
SQL> analyze table tmuser.m01_cust compute statistics;
Table analyzed.
エラー原因と対応方法を報告します。
背景：
テーブルのANALYZE失敗：
SQL> analyze table tmuser.m01_cust compute statistics;
analyze table tmuser.m01_cust compute statistics
*
ERROR at line 1:
ORA-08104: this index object 6571 is being online built or rebuilt
そして上記エラー対象の索引削除も失敗：
SQL> drop index IDX_M01_ADDR_CODE;
drop index IDX_M01_ADDR_CODE
*
ERROR at line 1:
ORA-08104: this index object 6571 is being online built or rebuilt
対応方法：
① 「REBUILD FLAG」が残っている索引を確認する。
SQL> select obj# from ind$ where bitand(flags, 512)=512;
OBJ#
----------
6571
② 対象索引の「REBUILD FLAG」を更新する。
SQL> update ind$ set flags=flags-512 where obj#=6571;
1 row updated.
SQL> commit;
Commit complete.
③ 更新後の「REBUILD FLAG」状態を確認する、「０」であれば、正常に戻ることを判断できる。
SQL> select bitand(flags, 512) from ind$ where obj#=6571;
BITAND(FLAGS,512)
-----------------
0
④ 索引削除できました。
SQL> conn tmuser/tmadmin
Connected.
SQL> drop index IDX_M01_ADDR_CODE;
Index dropped.
⑤ 索引再作成してANALYZEもできました。
SQL> CREATE INDEX TMUSER.IDX_M01_ADDR_CODE
ON TMUSER.M01_CUST
(ADDR_CODE_PREF, ADDR_CODE_CITY, ADDR_CODE_ZONE)
TABLESPACE USERS_INDEX_M01
STORAGE(INITIAL 128M MINEXTENTS 1) NOLOGGING;
Index created.
SQL> analyze table tmuser.m01_cust compute statistics;
Table analyzed.
2007/07/19
f.y.i F5 overview guide
Users fed up with poor performing apps? Defuse the situation with expert application acceleration advice from F5
Subscribe to:
Posts (Atom)