Read Consistency
Your system might spend excessive time rolling back changes to blocks in order to maintain a consistent view. Consider the following scenarios:
- If there are
many small transactions and an active long-running query is running in the
background on the same table where the changes are happening, then the query
might need to roll back those changes often, in order to obtain a
read-consistent image of the table. Compare the following
V$SYSSTATstatistics to determine whether this is happening:- consistent changes statistic indicates the number of times a database block has rollback entries applied to perform a consistent read on the block. Workloads that produce a great deal of consistent changes can consume a great deal of resources.
- consistent gets statistic counts the number of logical reads in consistent mode.
- If there are few
very, large rollback segments, then your system could be spending a lot of
time rolling back the transaction table during delayed block cleanout in order
to find out exactly which SCN a transaction was committed. The ratio of the
following
V$SYSSTATstatistics should be close to 1:ratio = transaction tables consistent reads undo records applied / transaction tables consistent read rollbacks
A solution is to create more, smaller rollback segments, or to use automatic undo management.
Paul Baumgartel
MortgageSight Holdings,
LLC
[EMAIL PROTECTED]
-----Original Message-----
From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 30, 2001 12:47 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Tkprof outputLee,
This query seems suspect
UPDATE VM_LIVE.SINGLE_CUSTOMER_HISTORY SCH SET VISIBLE=1
WHERE
ACXIOM_CUSTOMER_KEY = :b1 AND VERSION_NO = :b2because of this
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 39562 4.55 7.22 10897 118687 1 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 39562 4.55 7.22 10897 118687 1 1It's reading a ton of blocks to operate on ONE record. What's the table structure here? What's the index structure? Cardinality?
The buffer gets in the other queries are suspect too. What's your blocksize? It's reading a ton of blocks to arrive at the result.
-----Original Message-----
From: Robertson Lee - lerobe [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, August 30, 2001 11:56 AM
To: Multiple recipients of list ORACLE-L
Subject: Tkprof outputApologies for the length of the mail.
This query is running for a mad amount of time, anyone any ideas.
Code and tkprof out put shown below.
Huge TIA
Lee (who must learn more about such things !!!)
DECLARE CURSOR TEMP_CDS IS
SELECT ACXIOM_CUSTOMER_KEY,
VERSION_NO,
ADDRESS_OCCUPANCY_KEY
FROM CUSTOMER_DETAIL_SOURCE
WHERE VISIBLE=1;
COUNTER NUMBER(8);
BEGIN
COUNTER:=0;
FOR I IN TEMP_CDS
LOOP
UPDATE &SCHEMA..SINGLE_CUSTOMER SC
SET VISIBLE = 1
WHERE ACXIOM_CUSTOMER_KEY=I.ACXIOM_CUSTOMER_KEY
AND VERSION_NO =I.VERSION_NO;
UPDATE &SCHEMA..SINGLE_CUSTOMER_HISTORY SCH
SET VISIBLE = 1
WHERE ACXIOM_CUSTOMER_KEY=I.ACXIOM_CUSTOMER_KEY
AND VERSION_NO =I.VERSION_NO;
UPDATE &SCHEMA..ADDRESS_OCCUPANCY AO
SET VISIBLE = 1
WHERE ADDRESS_OCCUPANCY_KEY = I.ADDRESS_OCCUPANCY_KEY;
COUNTER := COUNTER + 1;
IF (COUNTER = 50000)
THEN
COUNTER:=0;
COMMIT;
END IF;
END LOOP;
COMMIT;
Sort options: prsela exeela fchela
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
UPDATE VM_LIVE.SINGLE_CUSTOMER SC SET VISIBLE=1
WHERE
ACXIOM_CUSTOMER_KEY = :b1 AND VERSION_NO = :b2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 39562 15.51 398.98 56555 181085 40672 39562
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 39562 15.51 398.98 56555 181085 40672 39562
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 39 (VM_LIVE) (recursive depth: 1)
Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT GOAL: CHOOSE
0 UPDATE OF 'SINGLE_CUSTOMER'
0 TABLE ACCESS (BY INDEX ROWID) OF 'SINGLE_CUSTOMER'
0 INDEX (UNIQUE SCAN) OF 'SINGLE_CUSTOMER_PK' (UNIQUE)
********************************************************************************
UPDATE VM_LIVE.ADDRESS_OCCUPANCY AO SET VISIBLE=1
WHERE
ADDRESS_OCCUPANCY_KEY = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 39562 12.57 186.88 57285 124038 40726 39562
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 39562 12.57 186.88 57285 124038 40726 39562
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 39 (VM_LIVE) (recursive depth: 1)
Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT GOAL: CHOOSE
0 UPDATE OF 'ADDRESS_OCCUPANCY'
0 INDEX (UNIQUE SCAN) OF 'I_ADDRESS_OCCUPANCY_I4' (UNIQUE)
********************************************************************************
UPDATE VM_LIVE.SINGLE_CUSTOMER_HISTORY SCH SET VISIBLE=1
WHERE
ACXIOM_CUSTOMER_KEY = :b1 AND VERSION_NO = :b2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 39562 4.55 7.22 10897 118687 1 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 39562 4.55 7.22 10897 118687 1 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 39 (VM_LIVE) (recursive depth: 1)
Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT GOAL: CHOOSE
0 UPDATE OF 'SINGLE_CUSTOMER_HISTORY'
0 INDEX (UNIQUE SCAN) OF 'SINGLE_CUSTOMER_HISTORY_PK' (UNIQUE)
********************************************************************************
SELECT ACXIOM_CUSTOMER_KEY,VERSION_NO,ADDRESS_OCCUPANCY_KEY
FROM
CUSTOMER_DETAIL_SOURCE WHERE VISIBLE = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 39562 1.51 2.04 392 39618 0 39562
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 39562 1.51 2.04 392 39618 0 39562
Misses in library cache during parse: 0
Parsing user id: 39 (VM_LIVE) (recursive depth: 1)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 118686 32.63 593.08 124737 423810 81399 79125
Fetch 39562 1.51 2.04 392 39618 0 39562
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 158248 34.14 595.12 125129 463428 81399 118687
Misses in library cache during parse: 0
Misses in library cache during execute: 1
4 user SQL statements in session.
0 internal SQL statements in session.
4 SQL statements in session.
3 statements EXPLAINed in this session.
********************************************************************************
Trace file: ora_349778.trc
Trace file compatibility: 7.03.02
Sort options: prsela exeela fchela
1 session in tracefile.
4 user SQL statements in trace file.
0 internal SQL statements in trace file.
4 SQL statements in trace file.
4 unique SQL statements in trace file.
3 SQL statements EXPLAINed using schema:
VM_LIVE.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
158286 lines in trace file.
The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged. If the reader
of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or
copying of this communication is strictly prohibited.
If you have received this communication in error, please
re-send this communication to the sender and delete the
original message or any copy of it from your computer
system.
