My Oracle Support Banner

SQL TUNING ADVISORY JOB CAUSING HIGH REDO GENERATION OR Select .. for update statement (Doc ID 2746072.1)

Last updated on APRIL 17, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

Huge archive generation found when SQL Tuning advisory job gets started.

SQL Tuning advisory job gets executed for same query repeated. And this is select .. for update. because of this, huge redo generated.

<sql_id1>    /* SQL Analyze(657, 1) */ /* SQL Analyze(458, 1) */ /* SQL Analyze(435, 1) */ /* SQL Analyze(229, 1) */ /* SQL Analyze(335, 1) */ /* SQL Analyze(85, 1) */ /* SQL Analyze(85, 1) */ /* SQL Analyze(553, 1) */ /* SQL Analyze(260, 1) */ /* SQL Analyze(580, 1) */ /* SQL Analyze(432, 1) */ /* SQL Analyze(579, 1) */ /* SQL Analyze(435, 1) */ /* SQL Analyze(531, 1) */ SELECT "<column>", ROWID FROM <table> FOR UPDATE

<sql_id2>    /* SQL Analyze(657, 1) */ /* SQL Analyze(458, 1) */ /* SQL Analyze(435, 1) */ /* SQL Analyze(229, 1) */ /* SQL Analyze(335, 1) */ /* SQL Analyze(733, 1) */ /* SQL Analyze(85, 1) */ /* SQL Analyze(85, 1) */ /* SQL Analyze(553, 1) */ /* SQL Analyze(260, 1) */ /* SQL Analyze(580, 1) */ /* SQL Analyze(432, 1) */ /* SQL Analyze(435, 1) */ /* SQL Analyze(531, 1) */ SELECT "<column>", ROWID FROM <table> FOR UPDATE

<sql_id3>    /* SQL Analyze(657, 1) */ /* SQL Analyze(458, 1) */ /* SQL Analyze(435, 1) */ /* SQL Analyze(229, 1) */ /* SQL Analyze(335, 1) */ /* SQL Analyze(85, 1) */ /* SQL Analyze(85, 1) */ /* SQL Analyze(553, 1) */ /* SQL Analyze(260, 1) */ /* SQL Analyze(580, 1) */ /* SQL Analyze(432, 1) */ /* SQL Analyze(579, 1) */ /* SQL Analyze(435, 1) */ SELECT "<column>", ROWID FROM <table> FOR UPDATE

 


 

From AWR report >

SQL ordered by Elapsed Time

Elapsed Time(s)  Executions    Elapsed Time per Exec (s)    %Total    %CPU       %IO        SQL Id             SQL Module              SQL Text
3,606.22                0                                                      87.11      46.03      48.48    <sql_id0>   DBMS_SCHEDULER  DECLARE job BINARY_INTEGER := ...
914.03                 10                           91.40                   22.08     32.48       66.95    <sql_id1>   DBMS_SCHEDULER /* SQL Analyze(657, 1) */ /* S...         >>>> queries are from STA             
878.00                   9                           97.56                   21.21     33.13       65.50   <sql_id2>    DBMS_SCHEDULER /* SQL Analyze(657, 1) */ /* S...
825.85                 10                           82.59                   19.95     43.16       56.36    <sql_id3>    DBMS_SCHEDULER /* SQL Analyze(657, 1) */ /* S...

 

Segments by DB Blocks Changes

Owner    Tablespace Name    Object Name    Subobject Name    Obj. Type    Obj#    Dataobj#    DB Block Changes    % of Capture
** MISSING **    TEMP     ** MISSING: -40015572/438272    ** MISSING **    UNDEFINED    4254951724    438272    51,152    41.24 
xxx              xxx                  <table1>                                     TABLE     71158    71158           35,712                    28.79
xxx              xxx                  <table2>                                     INDEX     71331    71331           31,152                    25.12
xxx              xxx                  <table3>                                     TABLE     73305    76395             3,504                     2.83

Changes

 Found high DB Block changes for tables which are part of SQL Tuning advisory query. 

 

To get clear information, enable supplemental logging in database.

Enable supplemental logging on the database following Note 186150.1.

 

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.