Re: Redo Generation

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

--
http://www.freelists.org/webpage/oracle-l