Re: Redo Generation

From: Michael Calisi <oracle455_at_gmail.com>
Date: Tue, 10 Oct 2017 17:27:35 -0400
Message-ID: <CAJkYCMHSYfn+cwRUPWCeuv07kJ+hTh2OptMPj7JHJugCOdx3fw_at_mail.gmail.com>



I don't have a test system for this particular sql stmt

Thanks,

On Tue, Oct 10, 2017 at 5:06 PM, John Thomas <jt2354_at_gmail.com> wrote:

> Hmm.
>
> The precise answer is probably not. Test the statement in an isolated test
> instance. Measure the redo generated, then you will have a good idea.
>
> However you could roughly reckon on the average row size for an insert,
> similar for delete, and maybe an approximation to double the data updated
> for an UPDATE.
>
> Depends how accurate you need to be. Undo generates redo, except using new
> features for temporary segments etc...
>
> Regards,
>
> John
>
> On Tue, 10 Oct 2017 at 19:47 Juan Carlos Reyes Pacheco <
> jcdrpllist_at_gmail.com> wrote:
>
>> I remember a talk about it from TKyte,
>>
>> but maybe sqlplus is not enough, check at the end please.
>>
>> SQL> SET AUTOT ON
>> SQL> SELECT COUNT(*) FROM DBA_OBJECTS WHERE OBJECT_NAME LIKE '%A%';
>> COUNT(*)
>> ----------
>> 24254
>> Execution Plan
>> ----------------------------------------------------------
>> ------------------------------------------------------------
>> ----------------
>> | Id | Operation | Name | Rows | Bytes | Cost
>> (%CPU)|
>> ------------------------------------------------------------
>> ----------------
>> | 0 | SELECT STATEMENT | | 1 | 66 | 280
>> (3)|
>> | 1 | SORT AGGREGATE | | 1 | 66 |
>> |
>> | 2 | VIEW | DBA_OBJECTS | 28346 | 1826K| 280
>> (3)|
>> | 3 | UNION-ALL | | | |
>> |
>> |* 4 | FILTER | | | |
>> |
>> |* 5 | HASH JOIN | | 28343 | 1937K| 277
>> (3)|
>> | 6 | INDEX FULL SCAN | I_USER2 | 305 | 1220 | 1
>> (0)|
>> |* 7 | HASH JOIN | | 28343 | 1826K| 275
>> (2)|
>> | 8 | INDEX FULL SCAN | I_USER2 | 305 | 6710 | 1
>> (0)|
>> |* 9 | TABLE ACCESS FULL| OBJ$ | 28343 | 1217K| 274
>> (2)|
>> | 10 | NESTED LOOPS | | 1 | 29 | 2
>> (0)|
>> |* 11 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | 1
>> (0)|
>> |* 12 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1
>> (0)|
>> |* 13 | HASH JOIN | | 3 | 57 | 3
>> (34)|
>> |* 14 | INDEX FULL SCAN | I_LINK1 | 3 | 45 | 1
>> (0)|
>> | 15 | INDEX FULL SCAN | I_USER2 | 305 | 1220 | 1
>> (0)|
>> ------------------------------------------------------------
>> ----------------
>>
>> Predicate Information (identified by operation id):
>> ---------------------------------------------------
>>
>> 4 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND
>> "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>11 AND
>> "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND
>> "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR
>> BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR
>> "O"."TYPE#"=5 OR
>> "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR
>> "O"."TYPE#"=10 OR
>> "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR
>> "O"."TYPE#"=14 OR
>> "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND ("U"."TYPE#"<>2 AND
>> SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' OR
>> "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_
>> CONTEXT('userenv','current_
>> edition_id')) OR EXISTS (SELECT 0 FROM SYS."USER$"
>> "U2",SYS."OBJ$"
>> "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88
>> AND
>> "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND
>> "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_
>> edition_id')))))
>> 5 - access("O"."SPARE3"="U"."USER#")
>> 7 - access("O"."OWNER#"="U"."USER#")
>> 9 - filter("O"."NAME" LIKE '%A%' AND "O"."LINKNAME" IS NULL AND
>> "O"."TYPE#"<>10 AND "O"."NAME"<>'_NEXT_OBJECT' AND
>> "O"."NAME"<>'_default_auditing_options_' AND
>> BITAND("O"."FLAGS",128)=0)
>> 11 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('us
>> erenv','current_edition_id')))
>> filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('us
>> erenv','current_edition_id')))
>> 12 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND
>> "O2"."OWNER#"="U2"."USER#")
>> 13 - access("L"."OWNER#"="U"."USER#")
>> 14 - filter("L"."NAME" LIKE '%A%')
>>
>> Note
>> -----
>> - 'PLAN_TABLE' is old version
>>
>>
>> Statistics
>> ----------------------------------------------------------
>> 0 recursive calls
>> 0 db block gets
>> 991 consistent gets
>> 985 physical reads
>> 0 redo size
>> 528 bytes sent via SQL*Net to client
>> 500 bytes received via SQL*Net from client
>> 2 SQL*Net roundtrips to/from client
>> 0 sorts (memory)
>> 0 sorts (disk)
>> 1 rows processed
>>
>> SQL>
>>
>> 2017-10-10 14:27 GMT-04:00 Michael Calisi <oracle455_at_gmail.com>:
>>
>>>
>>> Is there a way to determine how much redo will be generated by a
>>> particular sql stmt?
>>>
>>> I have some sql stmt that generate GIGS of redo, and if would be nice
>>> know before it runs.
>>> --
>>>
>>>
>> --
>
> Regards,
>
> John
>
>

-- 
Michael A. Calisi

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Oct 10 2017 - 23:27:35 CEST

Original text of this message