Re: Redo Generation

From: Juan Carlos Reyes Pacheco <jcdrpllist_at_gmail.com>
Date: Tue, 10 Oct 2017 14:47:01 -0400
Message-ID: <CAGYrQyvRLNfTOAONQGCPZTqWLyN_vQiG2A4sbs+xwdckcYoxZg_at_mail.gmail.com>

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.
> --
>
>

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

Received on Tue Oct 10 2017 - 20:47:01 CEST