Again related to performance

Raj

unread,

Aug 7, 2007, 6:10:29 AM8/7/07

to ORACLE_DBA_EXPERTS

Hello Davind and all,

I am running out of ideas for the below mentioned query which is
taking two seconds for one execution.

Will appreciate pointers to improve the performance. This query is a
part of trigger which is getting fired when any update or insert is
taking place on table.

SELECT a.fpl_id, a.pzug_id, a.pzv_key, a.ein, a.e_id, a.bst_rei,
count(*), b.zn FROM PV_BTE a, PPL_Z b
WHERE ( a.pzug_id=b.pzug_id ) GROUP BY a.fpl_id, a.pzug_id, a.pzv_key,
a.ein, a.e_id, a.bst_rei, b.zn HAVING count(*)>1

call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0
0 0
Execute 1 0.00 0.00 0 0
0 0
Fetch 1 0.52 2.03 607 14436
55 0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 3 0.52 2.03 607 14436
55 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 102

Rows Row Source Operation
------- ---------------------------------------------------
0 FILTER
82650 SORT GROUP BY
82649 NESTED LOOPS
6347 TABLE ACCESS FULL PPL_Z
82649 TABLE ACCESS BY INDEX ROWID PV_BTE
88995 INDEX RANGE SCAN (object id 50700)

Index on PPL_Z:

INDEX_NAME UNIQUENES COLUMN_NAME
------------------------------ ---------
------------------------------
FK1_PZUG_I NONUNIQUE FPL_ID

PZUG_PK UNIQUE PZUG_ID

PZUG_UK UNIQUE FPL_ID
UNIQUE ZN

Index on PV_BTE :

INDEX_NAME UNIQUENES COLUMN_NAME
------------------------------ ---------
------------------------------
PZVK_BSF_FK_I NONUNIQUE E_ID
NONUNIQUE BST_REI

PZVK_BST_FK_I NONUNIQUE BST_ID

PZVK_PK UNIQUE PZVK_ID

PZVK_PZV_FK_I NONUNIQUE PZUG_ID
NONUNIQUE PZV_KEY

Can somebody suggests ways to improve the performance of this
query....I am running out of ideas.

Thanks, raj

fitzj...@cox.net

unread,

Aug 7, 2007, 8:32:41 AM8/7/07

to ORACLE_DBA_EXPERTS

Two seconds is NOT unreasonable for a group by aggregate query.

Since this is 8.1.7 you have few options except to ensure your
statistics are current; my guess is they are not. Other than that
suggestion without further information on which init parameters are
set and to what values it will be difficult to offer any further
assistance.

David Fitzjarrell

Raj

unread,

Aug 7, 2007, 8:53:42 AM8/7/07

to ORACLE_DBA_EXPERTS

> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

Hello David,

Thanks for your reply as usual.

That query is a part of trigger. On any insert and update command on
table...this query is looking for two paths/route for one train. If it
finds one, it throws error, as there can not be 2 paths/route for one
train.

We are storing the value of count(*) in str_cnt and doing below
mentioned comparison :

IF str_cnt > 1 THEN
RAISE NO_EIND_STR;
END IF;

Please see the parameter value pasted below :

SQL> sho parameter

NAME TYPE VALUE
------------------------------------ -------
------------------------------
active_instance_count integer
always_anti_join string NESTED_LOOPS
always_semi_join string standard
aq_tm_processes integer 0
audit_file_dest string ?/rdbms/audit
audit_trail string NONE
background_core_dump string partial
background_dump_dest string /usr/oracle/app/oracle/
admin/
ZTSS2/bdump
backup_tape_io_slaves boolean FALSE
bitmap_merge_area_size integer 1048576
blank_trimming boolean FALSE
buffer_pool_keep string
buffer_pool_recycle string
commit_point_strength integer 1
compatible string 8.0.5.0.0
control_file_record_keep_time integer 7
control_files string /tablespaces/control_1, /
table
spaces/control_2, /
tablespaces
/control_3
core_dump_dest string /usr/oracle/app/oracle/
admin/
ZTSS2/cdump
cpu_count integer 1
create_bitmap_area_size integer 8388608
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
db_block_buffers integer 4500
db_block_checking boolean FALSE
db_block_checksum boolean FALSE
db_block_lru_latches integer 1
db_block_max_dirty_target integer 4500
db_block_size integer 8192
db_domain string
db_file_direct_io_count integer 64
db_file_multiblock_read_count integer 32
db_file_name_convert string
db_files integer 50
dblink_encrypt_login boolean FALSE
db_name string ZTSS
dbwr_io_slaves integer 0
db_writer_processes integer 1
disk_asynch_io boolean TRUE
distributed_transactions integer 61
dml_locks integer 500
enqueue_resources integer 570
event string 4091 trace name context
foreve
r, level 10; name
systemstate
level 10; name
processstate le
vel 10; name errorstack
level
10
fast_start_io_target integer 4500
fast_start_parallel_rollback string LOW
fixed_date string
gc_defer_time integer 10
gc_files_to_locks string 1-5,8-9=1000EACH:
6=4000:7=800
gc_releasable_locks integer 4500
gc_rollback_locks string 1-8=200EACH
global_names boolean FALSE
hash_area_size integer 2097152
hash_join_enabled boolean TRUE
hash_multiblock_io_count integer 0
hi_shared_memory_address integer 0
hpux_sched_noage integer
hs_autoregister boolean TRUE
ifile file /usr/oracle/app/oracle/
admin/
ZTSS1/pfile/initZTSS.ora
instance_groups string
instance_name string ZTSS2
instance_number integer 2
java_max_sessionspace_size integer 0
java_pool_size string 20000K
java_soft_sessionspace_limit integer 0
job_queue_interval integer 60
job_queue_processes integer 1
large_pool_size string 0
license_max_sessions integer 0
license_max_users integer 0
license_sessions_warning integer 0
lm_locks integer 25627
lm_ress integer 23860
local_listener string
lock_name_space string
lock_sga boolean FALSE
log_archive_dest string
log_archive_dest_state_1 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_1 string
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_duplex_dest string
log_archive_format string %t_%s.dbf
log_archive_max_processes integer 1
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
log_buffer integer 163840
log_checkpoint_interval integer 10000
log_checkpoints_to_alert boolean FALSE
log_checkpoint_timeout integer 1800
log_file_name_convert string
max_commit_propagation_delay integer 700
max_dump_file_size string 10240
max_enabled_roles integer 30
max_rollback_segments integer 49
mts_circuits integer 0
mts_dispatchers string
mts_listener_address string
mts_max_dispatchers integer 5
mts_max_servers integer 20
mts_multiple_listeners boolean FALSE
mts_servers integer 0
mts_service string ZTSS
mts_sessions integer 0
nls_calendar string
nls_comp string
nls_currency string
nls_date_format string DD.MM.YYYY HH24:MI:SS
nls_date_language string
nls_dual_currency string
nls_iso_currency string
nls_language string GERMAN
nls_numeric_characters string
nls_sort string
nls_territory string GERMANY
nls_time_format string
nls_timestamp_format string
nls_timestamp_tz_format string
nls_time_tz_format string
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
open_cursors integer 255
open_links integer 4
open_links_per_instance integer 4
ops_interconnects string
optimizer_features_enable string 8.1.7
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_max_permutations integer 80000
optimizer_mode string CHOOSE
optimizer_percent_parallel integer 0
oracle_trace_collection_name string
oracle_trace_collection_path string ?/otrace/admin/cdf
oracle_trace_collection_size integer 5242880
oracle_trace_enable boolean FALSE
oracle_trace_facility_name string oracled
oracle_trace_facility_path string ?/otrace/admin/fdf
os_authent_prefix string ops$
os_roles boolean FALSE
O7_DICTIONARY_ACCESSIBILITY boolean TRUE
parallel_adaptive_multi_user boolean FALSE
parallel_automatic_tuning boolean FALSE
parallel_broadcast_enabled boolean FALSE
parallel_execution_message_size integer 2148
parallel_instance_group string
parallel_max_servers integer 5
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_server boolean TRUE
parallel_server_instances integer 2
parallel_threads_per_cpu integer 2
partition_view_enabled boolean FALSE
plsql_v2_compatibility boolean FALSE
pre_page_sga boolean FALSE
processes integer 200
query_rewrite_enabled boolean FALSE
query_rewrite_integrity string enforced
rdbms_server_dn string
read_only_open_delayed boolean FALSE
recovery_parallelism integer 0
remote_dependencies_mode string TIMESTAMP
remote_login_passwordfile string NONE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE
replication_dependency_tracking boolean TRUE
resource_limit boolean FALSE
resource_manager_plan string
rollback_segments string r05, r06, r07, r08
row_locking string always
serializable boolean FALSE
serial_reuse string DISABLE
service_names string ZTSS
session_cached_cursors integer 0
session_max_open_files integer 10
sessions integer 225
shadow_core_dump string partial
shared_memory_address integer 0
shared_pool_reserved_size string 6400000
shared_pool_size string 64000000
sort_area_retained_size integer 65536
sort_area_size integer 1048576
sort_multiblock_read_count integer 2
sql_trace boolean FALSE
sql_version string NATIVE
sql92_security boolean FALSE
standby_archive_dest string ?/dbs/arch
star_transformation_enabled string FALSE
tape_asynch_io boolean TRUE
text_enable boolean FALSE
thread integer 2
timed_os_statistics integer 0
timed_statistics boolean TRUE
tracefile_identifier string
transaction_auditing boolean TRUE
transactions integer 247
transactions_per_rollback_segment integer 5
use_indirect_data_buffers boolean FALSE
user_dump_dest string /usr/oracle/app/oracle/
admin/
ZTSS2/udump
utl_file_dir string /usr/users/log/utl_files

Kindly let me hear your view.

Thanks, Raj

Raj

unread,

Aug 7, 2007, 8:57:33 AM8/7/07

to ORACLE_DBA_EXPERTS

On Aug 7, 2:32 pm, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:

> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

Also, forgot to mention that when I am collecting stats, its resulting
in even bad performance. I am using below mentioned commands to
collect the statstics for both the tables:

EXECUTE dbms_stats.gather_table_stats (ownname=>'SAHN',
tabname=>'PPL_Z',estimate_percent=>null,cascade=>true);
EXECUTE dbms_stats.gather_table_stats (ownname=>'SAHN',
tabname=>'PV_BTE',estimate_percent=>null,cascade=>true);

Regards, Rajeev

fitzj...@cox.net

unread,

Aug 7, 2007, 10:10:07 AM8/7/07

to ORACLE_DBA_EXPERTS

On Aug 7, 7:53 am, Raj <rajeev.to...@gmail.com> wrote:
[snipped]

> optimizer_index_caching integer 0
> optimizer_index_cost_adj integer 100

[snipped]

> Kindly let me hear your view.
>
> Thanks, Raj

optimizer_index_caching should be set to a value similar to the Buffer
Cache Hit Ratio to tell Oracle what percentage of index blocks are
likely to be found in the cache.

optimizer_index_cost_adj should be set to 15 or 20; the default
setting of 100 indicates an index scan costs as much as a table scan.
You want to favor index scans in general.

David Fitzjarrell

fitzj...@cox.net

unread,

Aug 7, 2007, 10:18:39 AM8/7/07

to ORACLE_DBA_EXPERTS

> Regards, Rajeev- Hide quoted text -

>
> - Show quoted text -

This indicates either a poorly calculated clustering_factor or the
need for histograms; as your primary key is a single column histograms
should not be the issue, so I suspect it's a flaw in the way Oracle
has calculated the clustering factor. I would suggest you purchase
"Cost-based Oracle Fundamentals", Jonathan Lewis, Apress Publishing,
and read Chapter 5.

David Fitzjarrell

Raj

unread,

Aug 7, 2007, 11:20:22 AM8/7/07

to ORACLE_DBA_EXPERTS

David,

You seems to be correct in your analysys.

SQL> select t.blocks, i.clustering_factor, t.num_rows from user_tables
t, user_indexes i where t.table_name=i.table_name and
t.table_name='PPL_Z';

BLOCKS CLUSTERING_FACTOR NUM_ROWS
---------- ----------------- ----------
34 55 6346
34 68 6346
34 4878 6346

Cluster factor is not proper. For sure I will go through that book,
but can you suggest some ways to correct it in quick go...??

Again I am saying, I will go through that book.

Thanks much.

Regards, Raj

fitzj...@cox.net

unread,

Aug 7, 2007, 11:58:46 AM8/7/07

to ORACLE_DBA_EXPERTS

> Regards, Raj- Hide quoted text -

>
> - Show quoted text -

Which index is showing this 'improper clustering factor'? It may not
be 'improper' at all given the columns indexed and the data
distribution. You could run the following query to see what your
clustering factor could be:

select /*+ cursor_sharing_exact
no_monitoring
no_expand
index(ppl_z, fk1_pzug_i)
noparallel_index(ppl_z, fk1_pzug_i)
*/
sys_op_countchg(substrb(rowid, 1, 15), 32) clf
from ppl_z
where fpl_id is not null;

You might find the clustering_factor to be exactly where it is
supposed to be; you may find otherwise.

We can go further with this after you run the query and post the
results.

David Fitzjarrell

Charles Hooper

unread,

Aug 7, 2007, 12:41:25 PM8/7/07

to ORACLE_DBA_EXPERTS

Just a couple questions for the OP:
If this query is being executed in a trigger, shouldn't there be come
kind of restriction on the two tables, so that Oracle does not need to
always join at least 6,347 rows from PPL_Z with at least 82,649 rows
from PV_BTE? It would seem that you could restrict the number of rows
involved in the join based on the action that caused the trigger to
fire. Is PZUG_ID (or a related column) available to the trigger?

db_block_buffers is set at 4500, which should be reserving roughly
36MB for caching DB block buffers. I dislike reading TKPROF output (I
prefer raw trace data), but it appears that 607 physical reads were
required, which may have accounted for the majority of the 2.03
seconds.

hash_area_size is roughly 2MB, which may be a bit small. While the
query is not using a hash join, such a join *may* be faster than a
nested loops join in this case.

java_pool_size is roughly 20MB, if you are not using server side Java,
consider setting this value to 1MB and using that memory for something
else (maybe DB block buffers).

sort_area_retained_size is not the same value as sort_area_size. In
the book that David references, Jonathan Lewis describes how this
might be a problem during sort result retrieval. You might try
experimenting with different values of sort_area_size to see if it
makes a difference. Oracle 8i performs an implicit sort during a
GROUP BY, and this sort is likely spilling to the temp tablespace.

I noticed this in the parameters: event 4091 trace name context
forever, level 10; name systemstate level 10; name processstate level
10; name errorstack level 10 - any idea what the intention of this
might be? Do you think it may be slowing down the database
performance?

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

Raj

unread,

Aug 8, 2007, 4:53:35 AM8/8/07

to ORACLE_DBA_EXPERTS

Hello David,

Problem seems to be in PZUG_UK index on PPL_Z. Please see the output.

SQL> select t.blocks, i.clustering_factor, t.num_rows, i.index_name

from user_tables t, user_indexes i where t.table_name=i.table_name and
t.table_name='PPL_Z';

BLOCKS CLUSTERING_FACTOR NUM_ROWS INDEX_NAME
---------- ----------------- ---------- ------------------------------
34 55 6346 FK1_PZUG_I
34 68 6346 PZUG_PK
34 4878 6346 PZUG_UK

I am trying to run your query, but getting error Invalid column name :

SQL> select /*+ cursor_sharing_exact
2 no_monitoring
3 no_expand
4 index(PPL_Z,FK1_PZUG_I)
5 noparallel_index(PPL_Z,FK1_PZUG_I)
6 */
7 sys_op_countchg(substrb(rowid, 1, 15), 32) clf
8 from PPL_Z
9 where fpl_id is not null;

sys_op_countchg(substrb(rowid, 1, 15), 32) clf

*
ERROR at line 7:
ORA-00904: invalid column name

Please let me hear from you.

Regards, Raj

fitzj...@cox.net

unread,

Aug 8, 2007, 8:44:48 AM8/8/07

to ORACLE_DBA_EXPERTS

The sys_op_countchg procedure is available in 9iR2 and later releases;
unfortunately it isn't present in 8.1.7 which is the release you're
apparently using (I found this information in another thread of yours;
THIS is precisely why the Oracle release MUST be specified when you
ask your questions).

Simply because your clustering_factor is a large number doesn't make
it suspect; it may be an accurate reflection of the data distribution
in your table/index.

I would start looking at the data distribution in that column, were I
you:

select fpl_id, count(*)
from ppl_z
group by fpl_id;

See if you have exceptionally high counts for selected fpl_id values
relative to the total number of rows in that table; this MAY be a good
candidate for histograms.

David Fitzjarrell

Raj

unread,

Aug 8, 2007, 9:10:24 AM8/8/07

to ORACLE_DBA_EXPERTS

Hello David,

Thanks for your reply once again.

SQL> select fpl_id , count(*) from ppl_z group by fpl_id;

FPL_ID COUNT(*)
---------- ----------
569 1123
571 1055
572 1067
573 855
574 1123
584 1123

With this data, it seems there is no need of histograms... What you
say ??

Also, is it advisable to set clustering factors with the help
dbms_stats.set_index_stats procedure...??

Regards, Rajeev

Raj

unread,

Aug 8, 2007, 9:11:40 AM8/8/07

to ORACLE_DBA_EXPERTS

> K&M Machine-Fabricating, Inc.- Hide quoted text -

>
> - Show quoted text -

Hello Charles,

Thanks for your valuable input.

I will check your suggestions and will let you know in detail.

Regards, Raj

Raj

unread,

Aug 8, 2007, 9:58:59 AM8/8/07

to ORACLE_DBA_EXPERTS

On Aug 7, 6:41 pm, Charles Hooper <hooperc2...@yahoo.com> wrote:

> K&M Machine-Fabricating, Inc.- Hide quoted text -

>
> - Show quoted text -

Charles,

Also please see the execution plan with the help from autotrace :

----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=634 Card=4133 Bytes=
169453)

1 0 FILTER
2 1 SORT (GROUP BY) (Cost=634 Card=4133 Bytes=169453)
3 2 HASH JOIN (Cost=47 Card=82649 Bytes=3388609)
4 3 TABLE ACCESS (FULL) OF 'PPL_Z' (Cost=3 Card=6346
Bytes=69806)

5 3 TABLE ACCESS (FULL) OF 'PV_BTE' (Cost=32 Card=
82649 Bytes=2479470)

Regards, Raj

fitzj...@cox.net

unread,

Aug 8, 2007, 10:27:16 AM8/8/07

to ORACLE_DBA_EXPERTS

You have no method of computing a replacement clustering_factor value
so I would advise against such action. The sys_op_countchg() function
would have provided a possibly more reasonable value for the
clustering_factor and given you something to use as a test value.
Absent that functionality I cannot recommend you change such
statistics.

Charles has brought forth some excellent points; when we hear back
from you on those we can go further.

David Fitzjarrell

Raj

unread,

Aug 8, 2007, 12:35:37 PM8/8/07

to ORACLE_DBA_EXPERTS

On Aug 7, 6:41 pm, Charles Hooper <hooperc2...@yahoo.com> wrote:

> K&M Machine-Fabricating, Inc.- Hide quoted text -

>
> - Show quoted text -

Hello Charles,

Hash join is not making any difference to the performance. Please see
the output below :

I performed the test with the below mentioned parameter :

SQL> sho parameter hash

NAME TYPE VALUE
------------------------------------ -------
------------------------------

hash_area_size integer 10485760
hash_join_enabled boolean TRUE
hash_multiblock_io_count integer 32

SQL> sho parameter optimizer_index

NAME TYPE VALUE
------------------------------------ -------
------------------------------

optimizer_index_caching integer 99
optimizer_index_cost_adj integer 15

SQL> sho parameter java_pool_size

NAME TYPE VALUE
------------------------------------ -------
------------------------------

java_pool_size string 2000K

SQL> sho parameter db_block_buffers

NAME TYPE VALUE
------------------------------------ -------
------------------------------

db_block_buffers integer 9000

SELECT /*+ USE_HASH(PPL_Z,PV_BTE) */ a.fpl_id, a.pzug_id, a.pzv_key,
a.einbruchzeit, a.strecke_id, a.bst_reihenfolge, count(*), b.zn
FROM
PV_BTE a,PPL_Z b WHERE (a.pzug_id=b.pzug_id ) GROUP BY a.fpl_id,
a.pzug_id, a.pzv_key, a.einbruchzeit, a.strecke_id,
a.bst_reihenfolge,
b.zn HAVING count(*)>1

SQL explain plan :

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=625 Card=4133 Bytes=
169453)

1 0 FILTER
2 1 SORT (GROUP BY) (Cost=625 Card=4133 Bytes=169453)
3 2 HASH JOIN (Cost=38 Card=82649 Bytes=3388609)

4 3 TABLE ACCESS (FULL) OF 'PPL_Z' (Cost=3 Card=6346
Bytes=69806)

5 3 TABLE ACCESS (FULL) OF 'PV_BTE' (Cost=32 Card=
82649 Bytes=2479470)

Statistiken
----------------------------------------------------------
449 recursive calls
98 db block gets
697 consistent gets
574 physical reads
10160 redo size
590 bytes sent via SQL*Net to client
319 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
0 rows processed

Please let me know if you have some input on this ??

Regards, Raj

Raj

unread,

Aug 8, 2007, 12:38:34 PM8/8/07

to ORACLE_DBA_EXPERTS

> You have- Hide quoted text -
>
> - Show quoted text -...
>
> read more »

Hello David,

I was planning to buy the book you suggessted, but thought to search
on internet first and luckily got the chapter 5 URL :

http://www.apress.com/ApressCorporate/supplement/1/10081/1590596366-3080.pdf

I am sending this link to the group, so that people can read it (if
they dont want to invest)...:-)

It's really wonderful to go through such unknown things/concepts.

Thanks much.

Regards, Raj

Charles Hooper

unread,

Aug 8, 2007, 1:32:39 PM8/8/07

to ORACLE_DBA_EXPERTS

> SELECT /*+ USE_HASH(PPL_Z,PV_BTE) */ a.fpl_id, a.pzug_id, a.pzv_key,

To find the real source of the problem (and the solution), you will
likely need to use a 10046 trace at level 8, otherwise we will be
making somewhat blind guesses. The following is a snippet of a 10046
trace at level 8 from an Oracle 8.1.7.3 database:
PARSING IN CURSOR #10 len=107 dep=0 uid=78 oct=3 lid=78 tim=369800045
hv=3449954084 ad='9ac4ae0'
select max(schedule_rank) from workorder_audit
where schedule_id = :1
END OF STMT
PARSE #10:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=369800045
WAIT #10: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1
p3=0
WAIT #10: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1
p3=0
EXEC #10:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=369800045
WAIT #10: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1
p3=0
WAIT #10: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1
p3=0
WAIT #10: nam='db file scattered read' ela= 3 p1=3 p2=13011 p3=3
WAIT #10: nam='db file sequential read' ela= 0 p1=3 p2=13018 p3=1
WAIT #10: nam='db file sequential read' ela= 0 p1=3 p2=13021 p3=1
WAIT #10: nam='file open' ela= 0 p1=0 p2=0 p3=0
WAIT #10: nam='db file sequential read' ela= 2 p1=4 p2=151050 p3=1
WAIT #10: nam='db file sequential read' ela= 0 p1=4 p2=151055 p3=1
WAIT #10: nam='db file sequential read' ela= 1 p1=3 p2=165047 p3=1
WAIT #10: nam='db file scattered read' ela= 2 p1=4 p2=209661 p3=4
WAIT #10: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1
p3=0
FETCH #10:c=3,e=8,p=12,cr=115,cu=4,mis=0,r=1,dep=0,og=4,tim=369800053
WAIT #10: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1
p3=0
STAT #10 id=1 cnt=1 pid=0 pos=0 obj=0 op='SORT AGGREGATE '
STAT #10 id=2 cnt=13801 pid=1 pos=1 obj=4010 op='TABLE ACCESS FULL
WORKORDER_AUDIT '

Note the wait events: "'db file scattered read' ela= 3 p1=3 p2=13011
p3=3", "'db file sequential read' ela= 0 p1=3 p2=13018 p3=1" - from
this I can determine the amount of time spent in each operation, I can
determine the object being read, and I can tell the number of blocks
read.

The EXEC and FETCH lines are also helpful:
"EXEC #10:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=369800045"
"FETCH
#10:c=3,e=8,p=12,cr=115,cu=4,mis=0,r=1,dep=0,og=4,tim=369800053"

During the fetch, 3/100 of a second of CPU time was consumed, 8/100 of
a second of elapsed (clock) time was consumed, 12 blocks were read by
a physical read, 115 blocks read by a consistent get, 4 blocks read in
current mode (most efficient), and one row was returned. Something
must have caused the 5/100 seconds difference between the CPU time and
the elapsed time, and that was likely the physical read.

Let's take a look at what you posted.
* Hash_area_size defaults to twice the sort_area_size. Your query has
one sort to disk that happened during the implicit sort of the GROUP
BY, but you did not adjust the sort_area_size - you might try
increasing the size of the sort_area_size.
* The query reads roughly half of the blocks from disk, and the other
half from the buffer cache.
* Redo size appears in the statistics, which is probably a sign of
delayed block cleanout - dirty blocks had to be forced from the buffer
cache to allow the 574 blocks needed by the query to be read into the
buffer cache.
* The explain plan changed from an index lookup with another tablescan
to two tablescans - typically blocks that are read by a full tablescan
end up at the LRU end of the buffer cache, so they are among the first
to be flushed from the buffer cache. This can be addressed.
* 449 recursive calls (SQL execution calls) - those are things that
Oracle is performing in the background for you to execute the query.
This number might have to do with the "event 4091 trace name
context..." that I pointed out in my initial response.

Raj

unread,

Aug 9, 2007, 3:48:46 AM8/9/07

to ORACLE_DBA_EXPERTS

> read more »- Hide quoted text -
>
> - Show quoted text -...

Hello Charles,

Thanks for such a detailed reply and help.

I have removed the 4091 trace event from the init.ora file and have
changed the sort parameters as pasted below :

SQL> sho parameter even

NAME TYPE VALUE
------------------------------------ -------
------------------------------

event string

SQL> sho parameter sort

NAME TYPE VALUE
------------------------------------ -------
------------------------------

nls_sort string
sort_area_retained_size integer 5242880
sort_area_size integer 5242880

Also, I am keeping above mentioned parameters in previous posts )
unchanged.

After putting 10046 trace on session ( ALTER SESSION SET EVENTS '10046
trace name context forever, level 8';), executed the query. Could see
trace file in udump directory. Contents are pasted below :

Dump file /usr/oracle/app/oracle/admin/ZTSS2/udump/ora_28315.trc
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Parallel Server option
JServer Release 8.1.7.3.0 - Production
ORACLE_HOME = /usr/oracle/app/oracle/product/8.1.7
System name: Linux
Node name: mcnode2
Release: 2.2.16
Version: #1 Tue Nov 27 14:47:58 CET 2001
Machine: i?86
Instance name: ZTSS2
Redo thread mounted by this instance: 2
Oracle process number: 14
Unix process pid: 28315, image: oracle@mcnode2 (TNS V1-V3)

*** 2007-08-09 09:12:22.735
*** SESSION ID:(10.21) 2007-08-09 09:12:22.735
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
=====================
PARSING IN CURSOR #1 len=69 dep=0 uid=102 oct=42 lid=102 tim=0
hv=89553408 ad='5390950c'
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8'
END OF STMT
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1
p3=0
*** 2007-08-09 09:12:45.700
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1650815232 p2=1
p3=0
=====================
PARSING IN CURSOR #1 len=280 dep=0 uid=102 oct=3 lid=102 tim=0
hv=4091191153 ad='539d2b54'
SELECT a.fpl_id, a.pzug_id, a.pzv_key, a.einbruchzeit, a.strecke_id,
a.bst_reihenfolge, count(*), b.zn FROM PV_BTE a, PPL_Z b WHERE

( a.pzug_id=b.pzug_id ) GROUP BY a.fpl_id, a.pzug_id, a.pzv_key,

a.einbruchzeit, a.strecke_id, a.bst_reihenfolge, b.zn HAVING
count(*)>1

END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1
p3=0
WAIT #1: nam='global cache cr request' ela= 0 p1=7 p2=21803 p3=11201
WAIT #1: nam='global cache cr request' ela= 0 p1=7 p2=21803 p3=11201
WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=7 p2=21803 p3=1
WAIT #1: nam='global cache cr request' ela= 0 p1=7 p2=24199 p3=11197
WAIT #1: nam='global cache cr request' ela= 0 p1=7 p2=24199 p3=11197
WAIT #1: nam='db file sequential read' ela= 0 p1=7 p2=24199 p3=1
WAIT #1: nam='enqueue' ela= 0 p1=1414791172 p2=3 p3=16
WAIT #1: nam='enqueue' ela= 0 p1=1398013958 p2=0 p3=0
=====================
PARSING IN CURSOR #2 len=36 dep=1 uid=0 oct=3 lid=0 tim=0
hv=1705880752 ad='539b1a7c'
select file# from file$ where ts#=:1
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #2:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=0
STAT #2 id=1 cnt=1 pid=0 pos=0 obj=17 op='TABLE ACCESS BY INDEX ROWID
FILE$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=39 op='INDEX RANGE SCAN '
WAIT #1: nam='row cache lock' ela= 0 p1=4 p2=0 p3=5
WAIT #1: nam='row cache lock' ela= 0 p1=4 p2=0 p3=5
=====================
PARSING IN CURSOR #2 len=94 dep=1 uid=0 oct=3 lid=0 tim=0
hv=1839874543 ad='539acfd0'
select file#,block#,length from uet$ where ts#=:1 and segfile#=:2 and
segblock#=:3 and ext#=:4
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
WAIT #2: nam='global cache cr request' ela= 0 p1=1 p2=3464 p3=462
FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=0
STAT #2 id=1 cnt=0 pid=0 pos=0 obj=13 op='TABLE ACCESS CLUSTER UET$ '
STAT #2 id=2 cnt=1 pid=1 pos=1 obj=9 op='INDEX UNIQUE SCAN '
WAIT #1: nam='enqueue' ela= 0 p1=1414725638 p2=3 p3=16777868
WAIT #1: nam='row cache lock' ela= 0 p1=2 p2=0 p3=5
=====================
PARSING IN CURSOR #2 len=190 dep=1 uid=0 oct=3 lid=0 tim=0
hv=4059714361 ad='53ad1c7c'
select
type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lists,
65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0) from seg$
where ts#=:1 and file#=:2 and block#=:3
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=0
STAT #2 id=1 cnt=0 pid=0 pos=0 obj=14 op='TABLE ACCESS CLUSTER SEG$ '
STAT #2 id=2 cnt=1 pid=1 pos=1 obj=9 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #2 len=254 dep=1 uid=0 oct=2 lid=0 tim=0
hv=4003880563 ad='539a94a4'
insert into seg$
(file#,block#,type#,ts#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,lists,groups,cachehint,bitmapranges,scanhint,
hwmincr, spare1) values (:
1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,0,0,:16,DECODE(:
17,0,NULL,:17))
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
WAIT #2: nam='enqueue' ela= 0 p1=1414332419 p2=14 p3=0
WAIT #2: nam='global cache lock null to x' ela= 0 p1=1 p2=5091 p3=89
WAIT #2: nam='file open' ela= 0 p1=0 p2=0 p3=0
WAIT #2: nam='db file sequential read' ela= 0 p1=1 p2=5091 p3=1
WAIT #2: nam='global cache lock open x' ela= 0 p1=1 p2=3464 p3=462
WAIT #2: nam='db file sequential read' ela= 0 p1=1 p2=3464 p3=1
WAIT #2: nam='global cache lock null to x' ela= 0 p1=1 p2=5953 p3=951
WAIT #2: nam='db file sequential read' ela= 0 p1=1 p2=5953 p3=1
WAIT #2: nam='global cache lock s to x' ela= 0 p1=3 p2=3092 p3=13406
EXEC #2:c=0,e=0,p=3,cr=3,cu=20,mis=0,r=1,dep=1,og=4,tim=0
=====================
PARSING IN CURSOR #2 len=101 dep=1 uid=0 oct=2 lid=0 tim=0
hv=3687396716 ad='539a8474'
insert into uet$
(segfile#,segblock#,ext#,ts#,file#,block#,length)values (:1, :2, :3, :
4, :5, :6, :7)
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=2,cu=1,mis=0,r=1,dep=1,og=4,tim=0
=====================
PARSING IN CURSOR #2 len=56 dep=1 uid=0 oct=7 lid=0 tim=0
hv=1877781575 ad='5399ae98'
delete from fet$ where file#=:1 and block#=:2 and ts#=:3
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
WAIT #2: nam='enqueue' ela= 0 p1=1414332419 p2=12 p3=0
WAIT #2: nam='global cache cr request' ela= 0 p1=1 p2=5059 p3=57
WAIT #2: nam='global cache lock open x' ela= 0 p1=1 p2=5059 p3=57
WAIT #2: nam='db file sequential read' ela= 0 p1=1 p2=5059 p3=1
EXEC #2:c=0,e=0,p=1,cr=4,cu=1,mis=0,r=1,dep=1,og=4,tim=0
STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='DELETE FET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=7 op='INDEX UNIQUE SCAN '
WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0
WAIT #1: nam='enqueue' ela= 0 p1=1398013958 p2=0 p3=0
=====================
PARSING IN CURSOR #2 len=36 dep=1 uid=0 oct=3 lid=0 tim=0
hv=1705880752 ad='539b1a7c'
select file# from file$ where ts#=:1
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #2:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=0
STAT #2 id=1 cnt=1 pid=0 pos=0 obj=17 op='TABLE ACCESS BY INDEX ROWID
FILE$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=39 op='INDEX RANGE SCAN '
=====================
PARSING IN CURSOR #2 len=94 dep=1 uid=0 oct=3 lid=0 tim=0
hv=1839874543 ad='539acfd0'
select file#,block#,length from uet$ where ts#=:1 and segfile#=:2 and
segblock#=:3 and ext#=:4
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #2:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=0
STAT #2 id=1 cnt=0 pid=0 pos=0 obj=13 op='TABLE ACCESS CLUSTER UET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=9 op='INDEX UNIQUE SCAN '
WAIT #1: nam='global cache lock s to x' ela= 0 p1=1 p2=44 p3=13435
WAIT #1: nam='global cache lock s to x' ela= 0 p1=1 p2=44 p3=13435
=====================
PARSING IN CURSOR #2 len=101 dep=1 uid=0 oct=2 lid=0 tim=0
hv=3687396716 ad='539a8474'
insert into uet$
(segfile#,segblock#,ext#,ts#,file#,block#,length)values (:1, :2, :3, :
4, :5, :6, :7)
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=2,cu=2,mis=0,r=1,dep=1,og=4,tim=0
=====================
PARSING IN CURSOR #2 len=56 dep=1 uid=0 oct=7 lid=0 tim=0
hv=1877781575 ad='5399ae98'
delete from fet$ where file#=:1 and block#=:2 and ts#=:3
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=4,cu=1,mis=0,r=1,dep=1,og=4,tim=0
STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='DELETE FET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=7 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #2 len=283 dep=1 uid=0 oct=6 lid=0 tim=0
hv=2249281901 ad='5399cf64'
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:
8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535,
NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15,
hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1 and file#=:2
and block#=:3
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=6,cu=1,mis=0,r=1,dep=1,og=4,tim=0
STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='UPDATE SEG$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=14 op='TABLE ACCESS CLUSTER SEG$ '
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=9 op='INDEX UNIQUE SCAN '
WAIT #1: nam='enqueue' ela= 0 p1=1398013958 p2=0 p3=0
=====================
PARSING IN CURSOR #2 len=36 dep=1 uid=0 oct=3 lid=0 tim=0
hv=1705880752 ad='539b1a7c'
select file# from file$ where ts#=:1
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #2:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=0
STAT #2 id=1 cnt=1 pid=0 pos=0 obj=17 op='TABLE ACCESS BY INDEX ROWID
FILE$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=39 op='INDEX RANGE SCAN '
WAIT #1: nam='row cache lock' ela= 0 p1=4 p2=0 p3=5
WAIT #1: nam='row cache lock' ela= 0 p1=4 p2=0 p3=5
=====================
PARSING IN CURSOR #2 len=94 dep=1 uid=0 oct=3 lid=0 tim=0
hv=1839874543 ad='539acfd0'
select file#,block#,length from uet$ where ts#=:1 and segfile#=:2 and
segblock#=:3 and ext#=:4
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #2:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=0
STAT #2 id=1 cnt=0 pid=0 pos=0 obj=13 op='TABLE ACCESS CLUSTER UET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=9 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #2 len=101 dep=1 uid=0 oct=2 lid=0 tim=0
hv=3687396716 ad='539a8474'
insert into uet$
(segfile#,segblock#,ext#,ts#,file#,block#,length)values (:1, :2, :3, :
4, :5, :6, :7)
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=2,cu=2,mis=0,r=1,dep=1,og=4,tim=0
=====================
PARSING IN CURSOR #2 len=56 dep=1 uid=0 oct=7 lid=0 tim=0
hv=1877781575 ad='5399ae98'
delete from fet$ where file#=:1 and block#=:2 and ts#=:3
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=4,cu=1,mis=0,r=1,dep=1,og=4,tim=0
STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='DELETE FET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=7 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #2 len=283 dep=1 uid=0 oct=6 lid=0 tim=0
hv=2249281901 ad='5399cf64'
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:
8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535,
NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15,
hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1 and file#=:2
and block#=:3
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=6,cu=1,mis=0,r=1,dep=1,og=4,tim=0
STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='UPDATE SEG$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=14 op='TABLE ACCESS CLUSTER SEG$ '
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=9 op='INDEX UNIQUE SCAN '
WAIT #1: nam='enqueue' ela= 0 p1=1398013958 p2=0 p3=0
=====================
PARSING IN CURSOR #2 len=36 dep=1 uid=0 oct=3 lid=0 tim=0
hv=1705880752 ad='539b1a7c'
select file# from file$ where ts#=:1
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #2:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=0
STAT #2 id=1 cnt=1 pid=0 pos=0 obj=17 op='TABLE ACCESS BY INDEX ROWID
FILE$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=39 op='INDEX RANGE SCAN '
=====================
PARSING IN CURSOR #2 len=94 dep=1 uid=0 oct=3 lid=0 tim=0
hv=1839874543 ad='539acfd0'
select file#,block#,length from uet$ where ts#=:1 and segfile#=:2 and
segblock#=:3 and ext#=:4
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #2:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=0
STAT #2 id=1 cnt=0 pid=0 pos=0 obj=13 op='TABLE ACCESS CLUSTER UET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=9 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #2 len=101 dep=1 uid=0 oct=2 lid=0 tim=0
hv=3687396716 ad='539a8474'
insert into uet$
(segfile#,segblock#,ext#,ts#,file#,block#,length)values (:1, :2, :3, :
4, :5, :6, :7)
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=2,cu=2,mis=0,r=1,dep=1,og=4,tim=0
=====================
PARSING IN CURSOR #2 len=56 dep=1 uid=0 oct=7 lid=0 tim=0
hv=1877781575 ad='5399ae98'
delete from fet$ where file#=:1 and block#=:2 and ts#=:3
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=4,cu=1,mis=0,r=1,dep=1,og=4,tim=0
STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='DELETE FET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=7 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #2 len=283 dep=1 uid=0 oct=6 lid=0 tim=0
hv=2249281901 ad='5399cf64'
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:
8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535,
NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15,
hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1 and file#=:2
and block#=:3
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=6,cu=1,mis=0,r=1,dep=1,og=4,tim=0
STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='UPDATE SEG$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=14 op='TABLE ACCESS CLUSTER SEG$ '
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=9 op='INDEX UNIQUE SCAN '
WAIT #1: nam='enqueue' ela= 0 p1=1398013958 p2=0 p3=0
WAIT #1: nam='enqueue' ela= 0 p1=1398013958 p2=0 p3=0
=====================
PARSING IN CURSOR #2 len=36 dep=1 uid=0 oct=3 lid=0 tim=0
hv=1705880752 ad='539b1a7c'
select file# from file$ where ts#=:1
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #2:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=0
STAT #2 id=1 cnt=1 pid=0 pos=0 obj=17 op='TABLE ACCESS BY INDEX ROWID
FILE$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=39 op='INDEX RANGE SCAN '
=====================
PARSING IN CURSOR #2 len=84 dep=1 uid=0 oct=3 lid=0 tim=0 hv=904892542
ad='539d1b08'
select file#,block#,length from fet$ where length>=:1 and ts#=:
2 and file#=:3
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #2:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=0
WAIT #1: nam='row cache lock' ela= 0 p1=1 p2=0 p3=5
WAIT #1: nam='row cache lock' ela= 0 p1=1 p2=0 p3=5
=====================
PARSING IN CURSOR #3 len=63 dep=1 uid=0 oct=3 lid=0 tim=0
hv=2913840444 ad='539b02f8'
select length from fet$ where file#=:1 and block#=:2 and ts#=:3
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #3:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
STAT #3 id=1 cnt=1 pid=0 pos=0 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=7 op='INDEX UNIQUE SCAN '
STAT #2 id=1 cnt=2 pid=0 pos=0 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #2 id=2 cnt=1 pid=1 pos=1 obj=7 op='INDEX UNIQUE SCAN '
WAIT #1: nam='row cache lock' ela= 0 p1=4 p2=0 p3=5
=====================
PARSING IN CURSOR #2 len=94 dep=1 uid=0 oct=3 lid=0 tim=0
hv=1839874543 ad='539acfd0'
select file#,block#,length from uet$ where ts#=:1 and segfile#=:2 and
segblock#=:3 and ext#=:4
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #2:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=0
STAT #2 id=1 cnt=0 pid=0 pos=0 obj=13 op='TABLE ACCESS CLUSTER UET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=9 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #2 len=101 dep=1 uid=0 oct=2 lid=0 tim=0
hv=3687396716 ad='539a8474'
insert into uet$
(segfile#,segblock#,ext#,ts#,file#,block#,length)values (:1, :2, :3, :
4, :5, :6, :7)
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=2,cu=2,mis=0,r=1,dep=1,og=4,tim=0
=====================
PARSING IN CURSOR #2 len=56 dep=1 uid=0 oct=7 lid=0 tim=0
hv=1877781575 ad='5399ae98'
delete from fet$ where file#=:1 and block#=:2 and ts#=:3
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=4,cu=1,mis=0,r=1,dep=1,og=4,tim=0
STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='DELETE FET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=7 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #2 len=283 dep=1 uid=0 oct=6 lid=0 tim=0
hv=2249281901 ad='5399cf64'
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:
8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535,
NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15,
hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1 and file#=:2
and block#=:3
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=6,cu=1,mis=0,r=1,dep=1,og=4,tim=0
STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='UPDATE SEG$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=14 op='TABLE ACCESS CLUSTER SEG$ '
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=9 op='INDEX UNIQUE SCAN '
WAIT #1: nam='direct path write' ela= 0 p1=4 p2=1984 p3=21
WAIT #1: nam='direct path write' ela= 0 p1=4 p2=2006 p3=1
WAIT #1: nam='direct path read' ela= 0 p1=4 p2=2006 p3=1
WAIT #1: nam='direct path read' ela= 0 p1=4 p2=653 p3=128
WAIT #1: nam='direct path read' ela= 0 p1=4 p2=3252 p3=1
WAIT #1: nam='direct path read' ela= 0 p1=4 p2=2862 p3=1
WAIT #1: nam='direct path read' ela= 0 p1=4 p2=1302 p3=1
WAIT #1: nam='direct path read' ela= 0 p1=4 p2=1952 p3=1
WAIT #1: nam='enqueue' ela= 0 p1=1414791172 p2=3 p3=16
WAIT #1: nam='enqueue' ela= 0 p1=1398013958 p2=0 p3=0
=====================
PARSING IN CURSOR #2 len=63 dep=1 uid=0 oct=3 lid=0 tim=0
hv=2913840444 ad='539b02f8'
select length from fet$ where file#=:1 and block#=:2 and ts#=:3
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=0
STAT #2 id=1 cnt=0 pid=0 pos=0 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=7 op='INDEX UNIQUE SCAN '
WAIT #1: nam='row cache lock' ela= 0 p1=1 p2=0 p3=5
WAIT #1: nam='row cache lock' ela= 0 p1=1 p2=0 p3=5
=====================
PARSING IN CURSOR #2 len=63 dep=1 uid=0 oct=3 lid=0 tim=0
hv=2913840444 ad='539b02f8'
select length from fet$ where file#=:1 and block#=:2 and ts#=:3
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=0
STAT #2 id=1 cnt=0 pid=0 pos=0 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=7 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #2 len=63 dep=1 uid=0 oct=3 lid=0 tim=0
hv=2913840444 ad='539b02f8'
select length from fet$ where file#=:1 and block#=:2 and ts#=:3
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=0
STAT #2 id=1 cnt=0 pid=0 pos=0 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=7 op='INDEX UNIQUE SCAN '
WAIT #1: nam='row cache lock' ela= 0 p1=1 p2=0 p3=5
WAIT #1: nam='row cache lock' ela= 0 p1=1 p2=0 p3=5
=====================
PARSING IN CURSOR #2 len=63 dep=1 uid=0 oct=3 lid=0 tim=0
hv=2913840444 ad='539b02f8'
select length from fet$ where file#=:1 and block#=:2 and ts#=:3
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=0
STAT #2 id=1 cnt=0 pid=0 pos=0 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=7 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #2 len=63 dep=1 uid=0 oct=3 lid=0 tim=0
hv=2913840444 ad='539b02f8'
select length from fet$ where file#=:1 and block#=:2 and ts#=:3
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=0
STAT #2 id=1 cnt=0 pid=0 pos=0 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=7 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #2 len=56 dep=1 uid=0 oct=7 lid=0 tim=0
hv=2363676478 ad='53992bf0'
delete from seg$ where ts#=:1 and file#=:2 and block#=:3
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=5,cu=3,mis=0,r=1,dep=1,og=4,tim=0
STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='DELETE SEG$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=14 op='TABLE ACCESS CLUSTER SEG$ '
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=9 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #2 len=63 dep=1 uid=0 oct=2 lid=0 tim=0
hv=3230982141 ad='53991afc'
insert into fet$ (file#,block#,ts#,length) values (:1,:2,:3,:4)
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=1,cu=1,mis=0,r=1,dep=1,og=4,tim=0
=====================
PARSING IN CURSOR #2 len=74 dep=1 uid=0 oct=7 lid=0 tim=0 hv=528349613
ad='5398ef18'
delete from uet$ where ts#=:1 and segfile#=:2 and segblock#=:3 and
ext#=:4
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=5,cu=1,mis=0,r=1,dep=1,og=4,tim=0
STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='DELETE UET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=13 op='TABLE ACCESS CLUSTER UET$ '
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=9 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #2 len=63 dep=1 uid=0 oct=2 lid=0 tim=0
hv=3230982141 ad='53991afc'
insert into fet$ (file#,block#,ts#,length) values (:1,:2,:3,:4)
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=1,cu=1,mis=0,r=1,dep=1,og=4,tim=0
=====================
PARSING IN CURSOR #2 len=74 dep=1 uid=0 oct=7 lid=0 tim=0 hv=528349613
ad='5398ef18'
delete from uet$ where ts#=:1 and segfile#=:2 and segblock#=:3 and
ext#=:4
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=5,cu=1,mis=0,r=1,dep=1,og=4,tim=0
STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='DELETE UET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=13 op='TABLE ACCESS CLUSTER UET$ '
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=9 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #2 len=63 dep=1 uid=0 oct=2 lid=0 tim=0
hv=3230982141 ad='53991afc'
insert into fet$ (file#,block#,ts#,length) values (:1,:2,:3,:4)
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=1,cu=1,mis=0,r=1,dep=1,og=4,tim=0
=====================
PARSING IN CURSOR #2 len=74 dep=1 uid=0 oct=7 lid=0 tim=0 hv=528349613
ad='5398ef18'
delete from uet$ where ts#=:1 and segfile#=:2 and segblock#=:3 and
ext#=:4
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=5,cu=1,mis=0,r=1,dep=1,og=4,tim=0
STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='DELETE UET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=13 op='TABLE ACCESS CLUSTER UET$ '
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=9 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #2 len=63 dep=1 uid=0 oct=2 lid=0 tim=0
hv=3230982141 ad='53991afc'
insert into fet$ (file#,block#,ts#,length) values (:1,:2,:3,:4)
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=1,cu=1,mis=0,r=1,dep=1,og=4,tim=0
=====================
PARSING IN CURSOR #2 len=74 dep=1 uid=0 oct=7 lid=0 tim=0 hv=528349613
ad='5398ef18'
delete from uet$ where ts#=:1 and segfile#=:2 and segblock#=:3 and
ext#=:4
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=5,cu=1,mis=0,r=1,dep=1,og=4,tim=0
STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='DELETE UET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=13 op='TABLE ACCESS CLUSTER UET$ '
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=9 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #2 len=63 dep=1 uid=0 oct=2 lid=0 tim=0
hv=3230982141 ad='53991afc'
insert into fet$ (file#,block#,ts#,length) values (:1,:2,:3,:4)
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=1,cu=1,mis=0,r=1,dep=1,og=4,tim=0
=====================
PARSING IN CURSOR #2 len=74 dep=1 uid=0 oct=7 lid=0 tim=0 hv=528349613
ad='5398ef18'
delete from uet$ where ts#=:1 and segfile#=:2 and segblock#=:3 and
ext#=:4
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #2:c=0,e=0,p=0,cr=5,cu=1,mis=0,r=1,dep=1,og=4,tim=0
STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='DELETE UET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=13 op='TABLE ACCESS CLUSTER UET$ '
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=9 op='INDEX UNIQUE SCAN '
FETCH #1:c=0,e=0,p=579,cr=686,cu=112,mis=0,r=0,dep=0,og=4,tim=0
WAIT #1: nam='log file sync' ela= 0 p1=185 p2=0 p3=0
WAIT #1: nam='log file sync' ela= 0 p1=185 p2=0 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1650815232 p2=1
p3=0
STAT #1 id=1 cnt=0 pid=0 pos=0 obj=0 op='FILTER '
STAT #1 id=2 cnt=82650 pid=1 pos=1 obj=0 op='SORT GROUP BY '
STAT #1 id=3 cnt=82649 pid=2 pos=1 obj=0 op='HASH JOIN '
STAT #1 id=4 cnt=6346 pid=3 pos=1 obj=50660 op='TABLE ACCESS FULL
PPL_Z '
STAT #1 id=5 cnt=82649 pid=3 pos=2 obj=50664 op='TABLE ACCESS FULL
PV_BTE '
XCTEND rlbk=0, rd_only=1

Please let me know your view.

I am also trying to learn how to read this output, and find the
possible problem areas.

Thanks, Raj

Raj

unread,

Aug 9, 2007, 4:21:22 AM8/9/07

to ORACLE_DBA_EXPERTS

> read more »- Hide quoted text -
>
> - Show quoted text -...

It seems, I am doing mistake in taking event trace. I am saying this
because when I am converting *.trc file to *.trf with tkprof it is
showing following output, which is showing 0 seconds for the query
executed :

SELECT a.fpl_id, a.pzug_id, a.pzv_key, a.einbruchzeit, a.strecke_id,
a.bst_reihenfolge, count(*), b.zn
FROM

PV_BTE a, PPL_Z b WHERE ( a.pzug_id=b.pzug_id ) GROUP BY a.fpl_id,

a.pzug_id, a.pzv_key, a.einbruchzeit, a.strecke_id,
a.bst_reihenfolge,
b.zn HAVING count(*)>1

call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0
0 0
Execute 1 0.00 0.00 0 0
0 0

Fetch 1 0.00 0.00 575 548
61 0

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

total 3 0.00 0.00 575 548
61 0

Misses in library cache during parse: 0

Optimizer goal: CHOOSE
Parsing user id: 102

Rows Row Source Operation
------- ---------------------------------------------------
0 FILTER
82650 SORT GROUP BY

82649 HASH JOIN
6346 TABLE ACCESS FULL PPL_Z
82649 TABLE ACCESS FULL PV_BTE

Please suggesst where I could be wrong.

Regardds, Raj

Raj

unread,

Aug 9, 2007, 5:38:43 AM8/9/07

to ORACLE_DBA_EXPERTS

> read more »- Hide quoted text -
>
> - Show quoted text -...

Finally, I got it right. Contents for 10046 trace events are pasted
below :

Dump file /usr/oracle/app/oracle/admin/ZTSS2/udump/ora_22210.trc

Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Parallel Server option
JServer Release 8.1.7.3.0 - Production
ORACLE_HOME = /usr/oracle/app/oracle/product/8.1.7
System name: Linux
Node name: mcnode2
Release: 2.2.16
Version: #1 Tue Nov 27 14:47:58 CET 2001
Machine: i?86
Instance name: ZTSS2
Redo thread mounted by this instance: 2

Oracle process number: 11
Unix process pid: 22210, image: oracle@mcnode2 (TNS V1-V3)

*** 2007-08-09 11:29:15.367
*** SESSION ID:(10.13) 2007-08-09 11:29:15.366

APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
=====================
PARSING IN CURSOR #1 len=69 dep=0 uid=102 oct=42 lid=102

tim=2701058544 hv=89553408 ad='5355d2ac'

ALTER SESSION SET EVENTS '10046 trace name context forever, level 8'
END OF STMT

EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=2701058544

WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1
p3=0

*** 2007-08-09 11:29:33.702
WAIT #1: nam='SQL*Net message from client' ela= 1834 p1=1650815232
p2=1 p3=0
=====================
PARSING IN CURSOR #1 len=319 dep=0 uid=102 oct=3 lid=102
tim=2701060378 hv=2934939745 ad='5395217c'
SELECT /*+ USE_HASH(PV_BTE,PPL_Z) */ a.fpl_id, a.pzug_id, a.pzv_key,

a.einbruchzeit, a.strecke_id, a.bst_reihenfolge, count(*), b.zn FROM

PV_BTE a, PPL_Z b WHERE ( a.pzug_id=b.pzug_id ) GROUP BY a.fpl_id,

a.pzug_id, a.pzv_key, a.einbruchzeit, a.strecke_id,
a.bst_reihenfolge, b.zn HAVING count(*)>1

END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2701060378
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2701060378

WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1
p3=0

WAIT #1: nam='enqueue' ela= 0 p1=1414791172 p2=3 p3=16
WAIT #1: nam='enqueue' ela= 0 p1=1398013958 p2=0 p3=0
=====================

PARSING IN CURSOR #2 len=36 dep=1 uid=0 oct=3 lid=0 tim=2701060407
hv=1705880752 ad='539a6fa4'
select file# from file$ where ts#=:1
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060407
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060407
FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=2701060407
FETCH #2:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060407

STAT #2 id=1 cnt=1 pid=0 pos=0 obj=17 op='TABLE ACCESS BY INDEX ROWID
FILE$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=39 op='INDEX RANGE SCAN '
=====================

PARSING IN CURSOR #2 len=94 dep=1 uid=0 oct=3 lid=0 tim=2701060407
hv=1839874543 ad='539934c0'

select file#,block#,length from uet$ where ts#=:1 and segfile#=:2 and
segblock#=:3 and ext#=:4
END OF STMT

PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060407
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060407
FETCH #2:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060407

STAT #2 id=1 cnt=0 pid=0 pos=0 obj=13 op='TABLE ACCESS CLUSTER UET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=9 op='INDEX UNIQUE SCAN '
=====================

PARSING IN CURSOR #2 len=190 dep=1 uid=0 oct=3 lid=0 tim=2701060407
hv=4059714361 ad='53ad3098'

select
type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lists,
65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0) from seg$
where ts#=:1 and file#=:2 and block#=:3
END OF STMT

PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060407
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060407
FETCH #2:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060407

STAT #2 id=1 cnt=0 pid=0 pos=0 obj=14 op='TABLE ACCESS CLUSTER SEG$ '

STAT #2 id=2 cnt=2 pid=1 pos=1 obj=9 op='INDEX UNIQUE SCAN '
=====================

PARSING IN CURSOR #2 len=254 dep=1 uid=0 oct=2 lid=0 tim=2701060407
hv=4003880563 ad='53991000'

insert into seg$
(file#,block#,type#,ts#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,lists,groups,cachehint,bitmapranges,scanhint,
hwmincr, spare1) values (:
1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,0,0,:16,DECODE(:
17,0,NULL,:17))
END OF STMT

PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060407

WAIT #2: nam='enqueue' ela= 0 p1=1414332419 p2=14 p3=0
WAIT #2: nam='enqueue' ela= 0 p1=1414332419 p2=14 p3=0

EXEC #2:c=0,e=0,p=0,cr=2,cu=3,mis=0,r=1,dep=1,og=4,tim=2701060407
=====================
PARSING IN CURSOR #2 len=101 dep=1 uid=0 oct=2 lid=0 tim=2701060407
hv=3687396716 ad='5398db10'

insert into uet$
(segfile#,segblock#,ext#,ts#,file#,block#,length)values (:1, :2, :3, :
4, :5, :6, :7)
END OF STMT

PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060407
EXEC #2:c=0,e=0,p=0,cr=2,cu=1,mis=0,r=1,dep=1,og=4,tim=2701060407
=====================
PARSING IN CURSOR #2 len=56 dep=1 uid=0 oct=7 lid=0 tim=2701060407
hv=1877781575 ad='53982364'

delete from fet$ where file#=:1 and block#=:2 and ts#=:3
END OF STMT

PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060407
WAIT #2: nam='enqueue' ela= 1 p1=1414332419 p2=12 p3=0
EXEC #2:c=0,e=1,p=0,cr=4,cu=1,mis=0,r=1,dep=1,og=4,tim=2701060408

STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='DELETE FET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=7 op='INDEX UNIQUE SCAN '

WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0

WAIT #1: nam='enqueue' ela= 0 p1=1398013958 p2=0 p3=0
=====================

PARSING IN CURSOR #2 len=36 dep=1 uid=0 oct=3 lid=0 tim=2701060416
hv=1705880752 ad='539a6fa4'
select file# from file$ where ts#=:1
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060416
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060416
FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=2701060416
FETCH #2:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060416

STAT #2 id=1 cnt=1 pid=0 pos=0 obj=17 op='TABLE ACCESS BY INDEX ROWID
FILE$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=39 op='INDEX RANGE SCAN '
WAIT #1: nam='row cache lock' ela= 0 p1=4 p2=0 p3=5
WAIT #1: nam='row cache lock' ela= 0 p1=4 p2=0 p3=5
=====================

PARSING IN CURSOR #2 len=94 dep=1 uid=0 oct=3 lid=0 tim=2701060416
hv=1839874543 ad='539934c0'

select file#,block#,length from uet$ where ts#=:1 and segfile#=:2 and
segblock#=:3 and ext#=:4
END OF STMT

PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060416
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060417
FETCH #2:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060417

STAT #2 id=1 cnt=0 pid=0 pos=0 obj=13 op='TABLE ACCESS CLUSTER UET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=9 op='INDEX UNIQUE SCAN '
=====================

PARSING IN CURSOR #2 len=101 dep=1 uid=0 oct=2 lid=0 tim=2701060417
hv=3687396716 ad='5398db10'

insert into uet$
(segfile#,segblock#,ext#,ts#,file#,block#,length)values (:1, :2, :3, :
4, :5, :6, :7)
END OF STMT

PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060417
EXEC #2:c=0,e=0,p=0,cr=2,cu=2,mis=0,r=1,dep=1,og=4,tim=2701060417
=====================
PARSING IN CURSOR #2 len=56 dep=1 uid=0 oct=7 lid=0 tim=2701060417
hv=1877781575 ad='53982364'

delete from fet$ where file#=:1 and block#=:2 and ts#=:3
END OF STMT

PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060417
EXEC #2:c=0,e=0,p=0,cr=4,cu=1,mis=0,r=1,dep=1,og=4,tim=2701060417

STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='DELETE FET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=7 op='INDEX UNIQUE SCAN '
=====================

PARSING IN CURSOR #2 len=283 dep=1 uid=0 oct=6 lid=0 tim=2701060417
hv=2249281901 ad='5397a958'

update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:
8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535,
NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15,
hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1 and file#=:2
and block#=:3
END OF STMT

PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060417
EXEC #2:c=0,e=0,p=0,cr=6,cu=1,mis=0,r=1,dep=1,og=4,tim=2701060417

STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='UPDATE SEG$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=14 op='TABLE ACCESS CLUSTER SEG$ '
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=9 op='INDEX UNIQUE SCAN '
WAIT #1: nam='enqueue' ela= 0 p1=1398013958 p2=0 p3=0
=====================

PARSING IN CURSOR #2 len=36 dep=1 uid=0 oct=3 lid=0 tim=2701060432
hv=1705880752 ad='539a6fa4'
select file# from file$ where ts#=:1
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060432
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060432
FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=2701060432
FETCH #2:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060432

STAT #2 id=1 cnt=1 pid=0 pos=0 obj=17 op='TABLE ACCESS BY INDEX ROWID
FILE$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=39 op='INDEX RANGE SCAN '
=====================

PARSING IN CURSOR #2 len=94 dep=1 uid=0 oct=3 lid=0 tim=2701060432
hv=1839874543 ad='539934c0'

select file#,block#,length from uet$ where ts#=:1 and segfile#=:2 and
segblock#=:3 and ext#=:4
END OF STMT

PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060432
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060432
FETCH #2:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060432

STAT #2 id=1 cnt=0 pid=0 pos=0 obj=13 op='TABLE ACCESS CLUSTER UET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=9 op='INDEX UNIQUE SCAN '
=====================

PARSING IN CURSOR #2 len=101 dep=1 uid=0 oct=2 lid=0 tim=2701060432
hv=3687396716 ad='5398db10'

insert into uet$
(segfile#,segblock#,ext#,ts#,file#,block#,length)values (:1, :2, :3, :
4, :5, :6, :7)
END OF STMT

PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060432
EXEC #2:c=0,e=0,p=0,cr=2,cu=2,mis=0,r=1,dep=1,og=4,tim=2701060432
=====================
PARSING IN CURSOR #2 len=56 dep=1 uid=0 oct=7 lid=0 tim=2701060432
hv=1877781575 ad='53982364'

delete from fet$ where file#=:1 and block#=:2 and ts#=:3
END OF STMT

PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060432
EXEC #2:c=0,e=0,p=0,cr=4,cu=1,mis=0,r=1,dep=1,og=4,tim=2701060432

STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='DELETE FET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=7 op='INDEX UNIQUE SCAN '
=====================

PARSING IN CURSOR #2 len=283 dep=1 uid=0 oct=6 lid=0 tim=2701060432
hv=2249281901 ad='5397a958'

update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:
8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535,
NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15,
hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1 and file#=:2
and block#=:3
END OF STMT

PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060432
EXEC #2:c=0,e=0,p=0,cr=6,cu=1,mis=0,r=1,dep=1,og=4,tim=2701060432

STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='UPDATE SEG$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=14 op='TABLE ACCESS CLUSTER SEG$ '
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=9 op='INDEX UNIQUE SCAN '
WAIT #1: nam='enqueue' ela= 0 p1=1398013958 p2=0 p3=0
WAIT #1: nam='enqueue' ela= 0 p1=1398013958 p2=0 p3=0
=====================

PARSING IN CURSOR #2 len=36 dep=1 uid=0 oct=3 lid=0 tim=2701060444
hv=1705880752 ad='539a6fa4'
select file# from file$ where ts#=:1
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060444
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060444
FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=2701060444
FETCH #2:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060444

STAT #2 id=1 cnt=1 pid=0 pos=0 obj=17 op='TABLE ACCESS BY INDEX ROWID
FILE$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=39 op='INDEX RANGE SCAN '
WAIT #1: nam='row cache lock' ela= 0 p1=4 p2=0 p3=5
WAIT #1: nam='row cache lock' ela= 0 p1=4 p2=0 p3=5
=====================

PARSING IN CURSOR #2 len=94 dep=1 uid=0 oct=3 lid=0 tim=2701060444
hv=1839874543 ad='539934c0'

select file#,block#,length from uet$ where ts#=:1 and segfile#=:2 and
segblock#=:3 and ext#=:4
END OF STMT

PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060444
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060444
FETCH #2:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060444

STAT #2 id=1 cnt=0 pid=0 pos=0 obj=13 op='TABLE ACCESS CLUSTER UET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=9 op='INDEX UNIQUE SCAN '
=====================

PARSING IN CURSOR #2 len=101 dep=1 uid=0 oct=2 lid=0 tim=2701060444
hv=3687396716 ad='5398db10'

insert into uet$
(segfile#,segblock#,ext#,ts#,file#,block#,length)values (:1, :2, :3, :
4, :5, :6, :7)
END OF STMT

PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060444
EXEC #2:c=0,e=0,p=0,cr=2,cu=2,mis=0,r=1,dep=1,og=4,tim=2701060444
=====================
PARSING IN CURSOR #2 len=56 dep=1 uid=0 oct=7 lid=0 tim=2701060444
hv=1877781575 ad='53982364'

delete from fet$ where file#=:1 and block#=:2 and ts#=:3
END OF STMT

PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060444
EXEC #2:c=0,e=0,p=0,cr=4,cu=1,mis=0,r=1,dep=1,og=4,tim=2701060444

STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='DELETE FET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=7 op='INDEX UNIQUE SCAN '
=====================

PARSING IN CURSOR #2 len=283 dep=1 uid=0 oct=6 lid=0 tim=2701060444
hv=2249281901 ad='5397a958'

update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:
8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535,
NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15,
hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1 and file#=:2
and block#=:3
END OF STMT

PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060444
EXEC #2:c=0,e=1,p=0,cr=6,cu=1,mis=0,r=1,dep=1,og=4,tim=2701060445

STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='UPDATE SEG$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=14 op='TABLE ACCESS CLUSTER SEG$ '
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=9 op='INDEX UNIQUE SCAN '
WAIT #1: nam='enqueue' ela= 0 p1=1398013958 p2=0 p3=0
=====================

PARSING IN CURSOR #2 len=36 dep=1 uid=0 oct=3 lid=0 tim=2701060461
hv=1705880752 ad='539a6fa4'
select file# from file$ where ts#=:1
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060461
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060461
FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=2701060461
FETCH #2:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060461

STAT #2 id=1 cnt=1 pid=0 pos=0 obj=17 op='TABLE ACCESS BY INDEX ROWID
FILE$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=39 op='INDEX RANGE SCAN '
=====================

PARSING IN CURSOR #2 len=94 dep=1 uid=0 oct=3 lid=0 tim=2701060461
hv=1839874543 ad='539934c0'

select file#,block#,length from uet$ where ts#=:1 and segfile#=:2 and
segblock#=:3 and ext#=:4
END OF STMT

PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060461
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060461
FETCH #2:c=0,e=1,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060462

STAT #2 id=1 cnt=0 pid=0 pos=0 obj=13 op='TABLE ACCESS CLUSTER UET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=9 op='INDEX UNIQUE SCAN '
=====================

PARSING IN CURSOR #2 len=101 dep=1 uid=0 oct=2 lid=0 tim=2701060462
hv=3687396716 ad='5398db10'

insert into uet$
(segfile#,segblock#,ext#,ts#,file#,block#,length)values (:1, :2, :3, :
4, :5, :6, :7)
END OF STMT

PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060462
EXEC #2:c=0,e=0,p=0,cr=2,cu=2,mis=0,r=1,dep=1,og=4,tim=2701060462
=====================
PARSING IN CURSOR #2 len=56 dep=1 uid=0 oct=7 lid=0 tim=2701060462
hv=1877781575 ad='53982364'

delete from fet$ where file#=:1 and block#=:2 and ts#=:3
END OF STMT

PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060462
EXEC #2:c=0,e=0,p=0,cr=4,cu=1,mis=0,r=1,dep=1,og=4,tim=2701060462

STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='DELETE FET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=7 op='INDEX UNIQUE SCAN '
=====================

PARSING IN CURSOR #2 len=283 dep=1 uid=0 oct=6 lid=0 tim=2701060462
hv=2249281901 ad='5397a958'

update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:
8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535,
NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15,
hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1 and file#=:2
and block#=:3
END OF STMT

PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060462
EXEC #2:c=0,e=0,p=0,cr=6,cu=1,mis=0,r=1,dep=1,og=4,tim=2701060462

STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='UPDATE SEG$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=14 op='TABLE ACCESS CLUSTER SEG$ '
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=9 op='INDEX UNIQUE SCAN '

WAIT #1: nam='direct path write' ela= 0 p1=4 p2=1334 p3=21
WAIT #1: nam='direct path write' ela= 0 p1=4 p2=1356 p3=1
WAIT #1: nam='direct path read' ela= 0 p1=4 p2=1356 p3=1
WAIT #1: nam='direct path read' ela= 0 p1=4 p2=1953 p3=128
WAIT #1: nam='direct path read' ela= 0 p1=4 p2=652 p3=1

WAIT #1: nam='direct path read' ela= 0 p1=4 p2=3252 p3=1
WAIT #1: nam='direct path read' ela= 0 p1=4 p2=2862 p3=1
WAIT #1: nam='direct path read' ela= 0 p1=4 p2=1302 p3=1

WAIT #1: nam='enqueue' ela= 0 p1=1398013958 p2=0 p3=0
WAIT #1: nam='enqueue' ela= 0 p1=1398013958 p2=0 p3=0
=====================

PARSING IN CURSOR #2 len=63 dep=1 uid=0 oct=3 lid=0 tim=2701060497
hv=2913840444 ad='539a6480'

select length from fet$ where file#=:1 and block#=:2 and ts#=:3
END OF STMT

PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060497
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060497
FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=2701060497

STAT #2 id=1 cnt=0 pid=0 pos=0 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=7 op='INDEX UNIQUE SCAN '
=====================