Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Vitor.

Asked: March 06, 2017 - 1:04 pm UTC

Last updated: November 02, 2022 - 4:26 am UTC

Version: 12

Viewed 1000+ times

You Asked

Hello Tom,


We have acquired an Exadata Server and would like to clarify some issues that I believe are product myths.

1 - After removing indexes, can queries get faster?

2- I have always used indexes on the columns identified as FKs. Is this still a good practice?

3 - Removing FK's can improve SELECT operations? And what about DML's?

4 - I believe I should continue to insert indexes where Cost is improved. Should I continue to target myself through Cost?


Thanks

and Connor said...

1 - After removing indexes, can queries get faster?

Maybe :-)

Whether you have Exadata or not is not important. What *is* important for *any* index is the following decision:

Does the benefit of having the index outweigh the cost of having the index ?

On Exadata, there is a good *chance* that you will need less indexes because some queries that used to need indexes actually run better without them (due to storage indexes, HCC, smart scan etc etc). But the same decision process applies - is it better with or without the index, and if it is better, can I justify the index cost.

2- I have always used indexes on the columns identified as FKs. Is this still a good practice?

Maybe :-)

An index on a FK column *might* be useful for locking considerations (if you update the parent table), and it *might* be useful just as a normal index. Once again:

Does the benefit of having the index outweigh the cost of having the index ?

where "benefit" in this case is not just performance, but *perhaps* concurrency as well

3 - Removing FK's can improve SELECT operations? And what about DML's?

How does removing FK's improve select ? Not sure what you mean here.

4 - I believe I should continue to insert indexes where Cost is improved. Should I continue to target myself through Cost?

See (1) and (2), but I'll add this. Your users dont really care about "Cost" - they care about business needs, and that normally equates to response time. I've never had a user call me and say "The *cost* is too high"...they call and say "Its too slow!!!". So focus your energies on giving better response times to the users - whilst keeping in mind, that if an index gives a better response time, then that's great, but it also has costs (space, cpu, DML speed etc). You need to trade one for the other.

Rating

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Update PK

Rajeshwaran, Jeyabal, March 09, 2017 - 7:02 am UTC

....
An index on a FK column *might* be useful for locking considerations (if you update the parent table),
.....

I hope this should be specified clearly like this.
....
An index on a FK column *might* be useful for locking considerations (if you update the parent table PRIMARY KEYs), 
.....


if we update the parent table PK we end up with locking implications on child table, if no updates to parent table PK then no locking implications on child table.

So here is my test case to demonstrate that.
demo@ORA12C> set feedback off
demo@ORA12C> drop table c purge;
demo@ORA12C> drop table p purge;
demo@ORA12C> create table p(x int primary key,y int);
demo@ORA12C> create table c(x references p, y int);
demo@ORA12C> insert into p values(1,1);
demo@ORA12C> insert into p values(2,1);
demo@ORA12C> insert into c values(1,1);
demo@ORA12C> commit;
demo@ORA12C> set feedback 6
demo@ORA12C>
demo@ORA12C> update c set y=2
  2  where x =1;

1 row updated.

demo@ORA12C>
demo@ORA12C> declare
  2     pragma autonomous_transaction;
  3  begin
  4     update p set x=3
  5     where x =2;
  6     rollback;
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4


demo@ORA12C> rollback ;

Rollback complete.


if no updates to parent table PK, then no child table implications.

demo@ORA12C> update c set y=2
  2  where x =1;

1 row updated.

demo@ORA12C>
demo@ORA12C> declare
  2     pragma autonomous_transaction;
  3  begin
  4     update p set y=3
  5     where x =2;
  6     rollback;
  7  end;
  8  /

PL/SQL procedure successfully completed.

demo@ORA12C>
demo@ORA12C>
demo@ORA12C> rollback;

Rollback complete.

demo@ORA12C>

on Smart Scans

Rajeshwaran, Jeyabal, May 14, 2019 - 1:35 pm UTC

Team,

Got an access to the Exadata environment and started reading this book "Expert Oracle Exadata" by Kerry Osborne and Tanel poder. Was able to understand what does offloading means from that book. The ability to reduce the volume of data transfered from storage tier to database tier makes the difference.

set serveroutput off
alter session set statistics_level=all;
show parameter cell_offload_processing
alter session set cell_offload_processing=false;
select max(created) from big_table where owner ='OUTLN';
select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
alter session set cell_offload_processing=true;
select max(created) from big_table where owner ='OUTLN';
select * from table( dbms_xplan.display_cursor(format=>'allstats last'));

c##rajesh@TEST_DB> set serveroutput off
c##rajesh@TEST_DB> alter session set statistics_level=all;

Session altered.

c##rajesh@TEST_DB> show parameter cell_offload_processing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offload_processing              boolean     TRUE
c##rajesh@TEST_DB> alter session set cell_offload_processing=false;

Session altered.

c##rajesh@TEST_DB> select max(created) from big_table where owner ='OUTLN';

MAX(CREATED
-----------
19-JUL-2018

c##rajesh@TEST_DB> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  77rszvaytsv1a, child number 0
-------------------------------------
select max(created) from big_table where owner ='OUTLN'

Plan hash value: 599409829

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |      1 |        |      1 |00:00:19.50 |    2024K|   2024K|
|   1 |  SORT AGGREGATE            |           |      1 |      1 |      1 |00:00:19.50 |    2024K|   2024K|
|*  2 |   TABLE ACCESS STORAGE FULL| BIG_TABLE |      1 |  11595 |  11595 |00:00:19.50 |    2024K|   2024K|
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"='OUTLN')


19 rows selected.

c##rajesh@TEST_DB> alter session set cell_offload_processing=true;

Session altered.

c##rajesh@TEST_DB> select max(created) from big_table where owner ='OUTLN';

MAX(CREATED
-----------
19-JUL-2018

c##rajesh@TEST_DB> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  77rszvaytsv1a, child number 1
-------------------------------------
select max(created) from big_table where owner ='OUTLN'

Plan hash value: 599409829

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |      1 |        |      1 |00:00:00.15 |    2024K|   2024K|       |       |          |
|   1 |  SORT AGGREGATE            |           |      1 |      1 |      1 |00:00:00.15 |    2024K|   2024K|       |       |          |
|*  2 |   TABLE ACCESS STORAGE FULL| BIG_TABLE |      1 |  11595 |  11595 |00:00:00.15 |    2024K|   2024K|  1025K|  1025K| 6170K (0)|
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage("OWNER"='OUTLN')
       filter("OWNER"='OUTLN')


20 rows selected.

c##rajesh@TEST_DB> 


based on the above demo. with cell_offload_processing = true - can see that filter is getting applied at storage layer (from the predicate section). Since the offloading is all about reduce the volume of data transfered from storage tier to database tier - doesn't that sound like reducing in the logical IO? If so i dont see any changes in the "buffers" from the plan ( with cell_offload_processing = true) - kindly advice.

(BTW: The big_table is a copy of all_objects with 100M rows in it)
Connor McDonald
May 22, 2019 - 12:44 am UTC

Buffers is buffer gets. If both operations are doing direct path reads, which is most likely the case, then you'd expect similar (and small) buffer gets for both.

on Smart Scans

Rajeshwaran Jeyabal, May 27, 2019 - 12:50 pm UTC

If both operations are doing direct path reads, which is most likely the case, then you'd expect similar (and small) buffer gets for both.

In the first execution, smart scan was disabled, so we need to pass over all the blocks to the DB server for filter.

In the first execution, smart scan was enabled, we pass over the predicates to the storage server and filtering is done on storage server & that in-turn reduce the volume of data sent back to DB server. Don't that leads to reduce buffers gets? Please clarify.
Connor McDonald
May 29, 2019 - 6:24 am UTC

buffer gets = "read from disk, into db buffer cache, to client"

direct read (even without exadata) = "read from disk, skip buffer cache, to client"


on Smart Scans

Rajeshwaran Jeyabal, May 29, 2019 - 9:58 am UTC

...direct read (even without exadata) = "read from disk, skip buffer cache, to client"...

Thanks. missed out that point - direct path reads will skip the buffer cache - the above followup helps.

Smart scans on Compressed segments

Rajeshwaran, Jeyabal, March 27, 2020 - 11:14 am UTC

Team,

was reading this book -Expert Oracle Exadata chapter#4 - Storage indexes, page no# 122

<quote>
When historical tables/partitions are
compressed, the problem becomes even more noticeable, as the reduced size of the compressed
tables/partitions will be even less likely to trigger the serial direct path reads and
hence those segments are not read via direct path reads and thus disabling storage indexes.
</quote>

Here is my testcase (on 12.2 database)- even after the compression is applied and the segment size look
much smaller than (less than 10% of buffer cache)the size of the buffer cache - still smart scan happens and storage indexes kicks in.

I dont see the above behaviour happens as quoted in the book, kindly advice.

c##rajesh@PDB1> col name format a20
c##rajesh@PDB1> select name, round(value/1024/1024,2) size_mb
  2  from v$sga
  3  order by name;

NAME                    SIZE_MB
-------------------- ----------
Database Buffers           2048
Fixed Size                  8.4
In-Memory Area             1408
Redo Buffers               71.6
Variable Size              3472

c##rajesh@PDB1> create table t
  2  as
  3  select *
  4  from big_table;

Table created.

c##rajesh@PDB1> exec show_space('T');
Unformatted Blocks .....................               0
FS1 Blocks (0-25) ......................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks ............................         182,654
Total Blocks............................         188,416
Total Bytes.............................   1,543,503,872
Total MBytes............................           1,472
Unused Blocks...........................           5,094
Unused Bytes............................      41,730,048
Last Used Ext FileId....................              24
Last Used Ext BlockId...................      17,219,584
Last Used Block.........................           3,098

PL/SQL procedure successfully completed.

c##rajesh@PDB1> $ type script.sql
set termout off
select * from big_table where owner ='JYU';
set termout on


c##rajesh@PDB1> conn c##rajesh/demo@PDB1
Connected.
c##rajesh@PDB1> col name format a60
c##rajesh@PDB1> col value format 999999999999999
c##rajesh@PDB1>
c##rajesh@PDB1> select s.name,m.value
  2  from v$statname s ,
  3      v$mystat m
  4  where s.statistic# = m.statistic#
  5  and s.name in ('cell physical IO bytes saved by storage index',
  6  'cell physical IO interconnect bytes returned by smart scan' );

NAME                                                                    VALUE
------------------------------------------------------------ ----------------
cell physical IO bytes saved by storage index                               0
cell physical IO interconnect bytes returned by smart scan                  0

c##rajesh@PDB1> @script.sql
c##rajesh@PDB1> select s.name,m.value
  2  from v$statname s ,
  3      v$mystat m
  4  where s.statistic# = m.statistic#
  5  and s.name in ('cell physical IO bytes saved by storage index',
  6  'cell physical IO interconnect bytes returned by smart scan' );

NAME                                                                    VALUE
------------------------------------------------------------ ----------------
cell physical IO bytes saved by storage index                       501932032
cell physical IO interconnect bytes returned by smart scan            1275912

c##rajesh@PDB1>
c##rajesh@PDB1> alter table t compress for archive high;

Table altered.

c##rajesh@PDB1> alter table t move online parallel 8;

Table altered.

c##rajesh@PDB1> exec show_space('T');
Unformatted Blocks .....................               0
FS1 Blocks (0-25) ......................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               7
Full Blocks ............................           9,626
Total Blocks............................           9,760
Total Bytes.............................      79,953,920
Total MBytes............................              76
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................              24
Last Used Ext BlockId...................      26,553,344
Last Used Block.........................             544

PL/SQL procedure successfully completed.

c##rajesh@PDB1> exec dbms_stats.gather_table_stats(user,'T',degree=>4,no_invalidate=>false);

PL/SQL procedure successfully completed.

c##rajesh@PDB1>
c##rajesh@PDB1> conn c##rajesh/demo@PDB1
Connected.
c##rajesh@PDB1>
c##rajesh@PDB1> col name format a60
c##rajesh@PDB1> col value format 999999999999999
c##rajesh@PDB1> select s.name,m.value
  2  from v$statname s ,
  3      v$mystat m
  4  where s.statistic# = m.statistic#
  5  and s.name in ('cell physical IO bytes saved by storage index',
  6  'cell physical IO interconnect bytes returned by smart scan' );

NAME                                                                    VALUE
------------------------------------------------------------ ----------------
cell physical IO bytes saved by storage index                               0
cell physical IO interconnect bytes returned by smart scan                  0

c##rajesh@PDB1> @script.sql
c##rajesh@PDB1> select s.name,m.value
  2  from v$statname s ,
  3      v$mystat m
  4  where s.statistic# = m.statistic#
  5  and s.name in ('cell physical IO bytes saved by storage index',
  6  'cell physical IO interconnect bytes returned by smart scan' );

NAME                                                                    VALUE
------------------------------------------------------------ ----------------
cell physical IO bytes saved by storage index                       526852096
cell physical IO interconnect bytes returned by smart scan            1295984

c##rajesh@PDB1>

Connor McDonald
March 30, 2020 - 12:56 am UTC

There's a few things in play here

a) some platforms/versions use small_table_threshold NOT 5x small_table_threshold
b) we also take into account the number of blocks already in the cache, and how dirty they are

and of course, we reserve the right to modify this algorithm as we choose.

And in your case above....there is also the issue of your script querying "big_table" not "t" :-)

Smart scans on Compressed segments

Rajeshwaran, Jeyabal, March 30, 2020 - 3:11 pm UTC

....And in your case above....there is also the issue of your script querying "big_table" not "t" :-)....

thanks for that - it helps. post that changes, it works expected.

c##rajesh@PDB1> create table t
  2  nologging
  3  as
  4  select *
  5  from big_table;

Table created.

c##rajesh@PDB1>
c##rajesh@PDB1> exec show_space('T');
Unformatted Blocks .....................               0
FS1 Blocks (0-25) ......................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks ............................         182,648
Total Blocks............................         188,416
Total Bytes.............................   1,543,503,872
Total MBytes............................           1,472
Unused Blocks...........................           5,100
Unused Bytes............................      41,779,200
Last Used Ext FileId....................              24
Last Used Ext BlockId...................      18,268,160
Last Used Block.........................           3,092

PL/SQL procedure successfully completed.

c##rajesh@PDB1> conn c##rajesh/demo@PDB1
Connected.
c##rajesh@PDB1>
c##rajesh@PDB1> $ type script.sql
set termout off
select * from t where owner ='JYU';
set termout on

c##rajesh@PDB1> select s.name,m.value
  2  from v$statname s ,
  3      v$mystat m
  4  where s.statistic# = m.statistic#
  5  and s.name in ('cell physical IO bytes saved by storage index',
  6  'cell physical IO interconnect bytes returned by smart scan' );

NAME                                                                    VALUE
------------------------------------------------------------ ----------------
cell physical IO bytes saved by storage index                               0
cell physical IO interconnect bytes returned by smart scan                  0

c##rajesh@PDB1> @script.sql
c##rajesh@PDB1> @script.sql
c##rajesh@PDB1> @script.sql
c##rajesh@PDB1> @script.sql
c##rajesh@PDB1> select s.name,m.value
  2  from v$statname s ,
  3      v$mystat m
  4  where s.statistic# = m.statistic#
  5  and s.name in ('cell physical IO bytes saved by storage index',
  6  'cell physical IO interconnect bytes returned by smart scan' );

NAME                                                                    VALUE
------------------------------------------------------------ ----------------
cell physical IO bytes saved by storage index                      3842637824
cell physical IO interconnect bytes returned by smart scan            5940000

c##rajesh@PDB1> alter table t compress for archive high;

Table altered.

c##rajesh@PDB1> alter table t move online parallel 8;

Table altered.

c##rajesh@PDB1> exec show_space('T');
Unformatted Blocks .....................               0
FS1 Blocks (0-25) ......................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               4
Full Blocks ............................           9,621
Total Blocks............................           9,752
Total Bytes.............................      79,888,384
Total MBytes............................              76
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................              24
Last Used Ext BlockId...................      23,909,120
Last Used Block.........................             536

PL/SQL procedure successfully completed.

c##rajesh@PDB1> exec dbms_stats.gather_table_stats(user,'T',degree=>4,no_invalidate=>false);

PL/SQL procedure successfully completed.

c##rajesh@PDB1> conn c##rajesh/demo@PDB1
Connected.
c##rajesh@PDB1>
c##rajesh@PDB1> col name format a60
c##rajesh@PDB1> col value format 999999999999999
c##rajesh@PDB1>
c##rajesh@PDB1> select s.name,m.value
  2  from v$statname s ,
  3      v$mystat m
  4  where s.statistic# = m.statistic#
  5  and s.name in ('cell physical IO bytes saved by storage index',
  6  'cell physical IO interconnect bytes returned by smart scan' );

NAME                                                                    VALUE
------------------------------------------------------------ ----------------
cell physical IO bytes saved by storage index                               0
cell physical IO interconnect bytes returned by smart scan                  0

c##rajesh@PDB1> @script.sql
c##rajesh@PDB1> @script.sql
c##rajesh@PDB1> @script.sql
c##rajesh@PDB1> @script.sql
c##rajesh@PDB1> @script.sql
c##rajesh@PDB1> select s.name,m.value
  2  from v$statname s ,
  3      v$mystat m
  4  where s.statistic# = m.statistic#
  5  and s.name in ('cell physical IO bytes saved by storage index',
  6  'cell physical IO interconnect bytes returned by smart scan' );

NAME                                                                    VALUE
------------------------------------------------------------ ----------------
cell physical IO bytes saved by storage index                               0
cell physical IO interconnect bytes returned by smart scan            1894136

c##rajesh@PDB1>


Connor McDonald
March 31, 2020 - 12:46 am UTC

glad its all working as expected

on smart scan

Rajeshwaran, Jeyabal, May 19, 2020 - 7:46 am UTC

Team,

please see this, we have 5 to 6 table join (all partition wise full scan and Hash join).
Howcome it is possible to have "actual" greater than "eligible" in this case?
what are the other possible cases, where "actual" greater than "eligible"

c##rajesh@PDB1> select executions_delta,buffer_gets_delta,
  2          io_offload_elig_bytes_total as eligible,
  3          io_interconnect_bytes_total as actual,
  4          (100*(io_offload_elig_bytes_total-io_interconnect_bytes_total))/io_offload_elig_bytes_total as "IO_SAVED_%"
  5  from dba_hist_sqlstat
  6  where sql_id ='5rq1znsvhm97f'
  7  and plan_hash_value = 1446997658
  8  /

EXECUTIONS_DELTA BUFFER_GETS_DELTA   ELIGIBLE        ACTUAL IO_SAVED_%
---------------- ----------------- ---------- ------------- ----------
               1           2795067 2205818880   59397791744 -2592.7774

Connor McDonald
June 10, 2020 - 1:06 am UTC

Can we see the plan and the sqltext

If its sensitive, you can email it to asktom_us@oracle.com

on smart scan

Rajeshwaran, Jeyabal, June 16, 2020 - 6:34 am UTC

Here is my test case (modelled like my application sql) to show the above scenario ( "actual" is greater than "eligible")

create table t1 parallel 4 as select a.*, rownum as id from all_objects a, all_users b;
create table t2 parallel 4 as select * from t1;
create table t3 parallel 4 as select * from t1;
create table t4 parallel 4 as select * from t1;
create table t5 parallel 4 as select * from t1;

alter table t1 noparallel;
alter table t2 noparallel;
alter table t3 noparallel;
alter table t4 noparallel;
alter table t5 noparallel;
exec dbms_stats.gather_table_stats(user,'T1',degree=>4,no_invalidate=>false);

c##rajesh@PDB1> set serveroutput off
c##rajesh@PDB1> set feedback only sql_id
c##rajesh@PDB1> select t1.owner, t2.object_id, t3.object_type, t4.object_name, t5.created
  2  from t1, t2, t3,t4, t5
  3  where t1.id =t2.id
  4  and t2.id =t3.id (+)
  5  and t3.id  = t4.id(+)
  6  and t4.id =t5.id (+);

28673294 rows selected.

SQL_ID: 8hfac5s8qwb3x
c##rajesh@PDB1> set feedback 6;
c##rajesh@PDB1> select sql_id,child_number,ceil(cpu_time/1000000) cpu_time, ceil(elapsed_time/1000000) elapsed_time,
  2         io_cell_offload_eligible_bytes as eligible,
  3         io_interconnect_bytes as actual,
  4         round( (100 * ( io_cell_offload_eligible_bytes -io_interconnect_bytes))
  5         /io_cell_offload_eligible_bytes ,2) as IO_Saved
  6     from gv$sql
  7     where sql_id ='8hfac5s8qwb3x'
  8     and child_number=0;

SQL_ID        CHILD_NUMBER   CPU_TIME ELAPSED_TIME   ELIGIBLE     ACTUAL   IO_SAVED
------------- ------------ ---------- ------------ ---------- ---------- ----------
8hfac5s8qwb3x            0        129          154 2.4934E+10 3.6685E+10     -47.13

c##rajesh@PDB1>

Connor McDonald
June 17, 2020 - 8:11 am UTC

Negative would indicate that the "io_interconnect_bytes" figure is excessive. The ratio formula assumes that the only thing going across the interconnect is data your query needs, thus the comparison with 'io_cell_offload_eligible_bytes'

And for many queries that will indeed be the case, eg

--
-- My "dba_objects" has 30340 rows, so I'm multiplying to get to around 1,000,000 rows
--

SQL> create table t1s as select a.*, rownum as id from dba_objects a,
  2    ( select 1 from dual connect by level < 1000000 / 30340 );
  
SQL> create table t2s as select * from t1s;

Table created.

SQL> create table t3s as select * from t1s;

Table created.

SQL> create table t4s as select * from t1s;

Table created.

SQL> create table t5s as select * from t1s;

Table created.

SQL>
SQL> begin
  2  for i in (
  3    select /*+ findme3 */ t1.owner, t2.object_id, t3.object_type, t4.object_name, t5.created
  4    from t1s t1, t2s t2, t3s t3,t4s t4, t5s t5
  5    where t1.id =t2.id
  6    and t2.id =t3.id (+)
  7    and t3.id  = t4.id(+)
  8    and t4.id =t5.id (+)
  9  )
 10  loop
 11    null;
 12  end loop;
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL> select sql_id, child_number from gv$sql where sql_text like 'SELECT%findme3%';

SQL_ID        CHILD_NUMBER
------------- ------------
4qcy6k7kjmbmt            0

SQL> select sql_id,child_number,ceil(cpu_time/1000000) cpu_time, ceil(elapsed_time/1000000) elapsed_time,
  2           io_cell_offload_eligible_bytes as eligible,
  3             io_interconnect_bytes as actual,
  4             round( (100 * ( io_cell_offload_eligible_bytes -io_interconnect_bytes))
  5             /io_cell_offload_eligible_bytes ,2) as IO_Saved
  6         from gv$sql
  7         where sql_id ='4qcy6k7kjmbmt'
  8         and child_number=0;

SQL_ID        CHILD_NUMBER   CPU_TIME ELAPSED_TIME   ELIGIBLE     ACTUAL   IO_SAVED
------------- ------------ ---------- ------------ ---------- ---------- ----------
4qcy6k7kjmbmt            0          2            2  787570688  102411880         87



Let me take the demo and now crank up the size of the tables

SQL> create table t1 as select a.*, rownum as id from dba_objects a,
  2    ( select 1 from dual connect by level < 28673294 / 30340 );

Table created.

SQL> create table t2 as select * from t1;

Table created.

SQL> create table t3 as select * from t1;

Table created.

SQL> create table t4 as select * from t1;

Table created.

SQL> create table t5 as select * from t1;

Table created.

SQL>
SQL> begin
  2  for i in (
  3    select /*+ findme2 */ t1.owner, t2.object_id, t3.object_type, t4.object_name, t5.created
  4    from t1, t2, t3,t4, t5
  5    where t1.id =t2.id
  6    and t2.id =t3.id (+)
  7    and t3.id  = t4.id(+)
  8    and t4.id =t5.id (+)
  9  )
 10  loop
 11    null;
 12  end loop;
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL>
SQL> select sql_id, child_number from gv$sql where sql_text like 'SELECT%findme2%';

SQL_ID        CHILD_NUMBER
------------- ------------
c4ncm7k1fqzgs            0

SQL> select sql_id,child_number,ceil(cpu_time/1000000) cpu_time, ceil(elapsed_time/1000000) elapsed_time,
  2  io_cell_offload_eligible_bytes as eligible,
  3             io_interconnect_bytes as actual,
  4             round( (100 * ( io_cell_offload_eligible_bytes -io_interconnect_bytes))
  5             /io_cell_offload_eligible_bytes ,2) as IO_Saved
  6         from gv$sql
  7         where sql_id ='c4ncm7k1fqzgs'
  8         and child_number=0;

SQL_ID        CHILD_NUMBER   CPU_TIME ELAPSED_TIME   ELIGIBLE     ACTUAL   IO_SAVED
------------- ------------ ---------- ------------ ---------- ---------- ----------
c4ncm7k1fqzgs            0        113          119 2.2580E+10 3.0096E+10     -33.29


and now the formula looks wrong.

The difference is that now just "real" data goes across the interconnect. We might need to read/write temporary data. Look at the stats for the two queries above

SQL> select * from v$sqlstats
  2  where sql_id = '4qcy6k7kjmbmt'
  3  @pr
==============================
SQL_TEXT                      : SELECT /*+ findme3 */ T1.OWNER, T2.OBJECT_ID, T3.OBJECT_TYPE, T4.OBJECT_NAME, T5.CREATED FROM T1S
T1, T2S T2, T3S T3,T4S T4, T5S T5 WHERE T1.ID =T2.ID AND T2.ID =T3.ID (+) AND T3.ID = T4.ID(+) AND T4.ID =T5.ID (+)
SQL_FULLTEXT                  : SELECT /*+ findme3 */ T1.OWNER, T2.OBJECT_ID, T3.OBJECT_TYPE, T4.OBJECT_NAME, T5.CREATED FROM T1S
T1, T2S T2, T3S T3,T4S T4, T5S T5 WHERE T1.ID =T2.ID AND T2.ID =T3.ID (+) AND T3.ID = T4.ID(+) AND T4.ID =T5.ID (+)
SQL_ID                        : 4qcy6k7kjmbmt
LAST_ACTIVE_TIME              : 17-JUN-20
LAST_ACTIVE_CHILD_ADDRESS     : 000000026CA5D590
PLAN_HASH_VALUE               : 3020598475
PARSE_CALLS                   : 1
DISK_READS                    : 96139         
DIRECT_WRITES                 : 0            <==== NOTHING DUMPED TO TEMP
DIRECT_READS                  : 96139        
BUFFER_GETS                   : 98890
ROWS_PROCESSED                : 1000000
SERIALIZABLE_ABORTS           : 0
FETCHES                       : 10001
EXECUTIONS                    : 1
END_OF_FETCH_COUNT            : 1
LOADS                         : 1
VERSION_COUNT                 : 1
INVALIDATIONS                 : 0
PX_SERVERS_EXECUTIONS         : 0
CPU_TIME                      : 1894227
ELAPSED_TIME                  : 1981066
AVG_HARD_PARSE_TIME           : 25714
APPLICATION_WAIT_TIME         : 5169
CONCURRENCY_WAIT_TIME         : 0
CLUSTER_WAIT_TIME             : 0
USER_IO_WAIT_TIME             : 73023
PLSQL_EXEC_TIME               : 0
JAVA_EXEC_TIME                : 0
SORTS                         : 0
SHARABLE_MEM                  : 52680
TOTAL_SHARABLE_MEM            : 52680
TYPECHECK_MEM                 : 0
IO_CELL_OFFLOAD_ELIGIBLE_BYTES: 787570688
IO_INTERCONNECT_BYTES         : 102411880
PHYSICAL_READ_REQUESTS        : 805
PHYSICAL_READ_BYTES           : 787570688
PHYSICAL_WRITE_REQUESTS       : 0
PHYSICAL_WRITE_BYTES          : 0
EXACT_MATCHING_SIGNATURE      : 15423674400639829405
FORCE_MATCHING_SIGNATURE      : 15423674400639829405
IO_CELL_UNCOMPRESSED_BYTES    : 641465903
IO_CELL_OFFLOAD_RETURNED_BYTES: 102411880
DELTA_PARSE_CALLS             : 1
DELTA_DISK_READS              : 96139
DELTA_DIRECT_WRITES           : 0
DELTA_DIRECT_READS            : 96139
DELTA_BUFFER_GETS             : 98890
DELTA_ROWS_PROCESSED          : 1000000
DELTA_FETCH_COUNT             : 10001
DELTA_EXECUTION_COUNT         : 1
DELTA_PX_SERVERS_EXECUTIONS   : 0
DELTA_END_OF_FETCH_COUNT      : 1
DELTA_CPU_TIME                : 1894227
DELTA_ELAPSED_TIME            : 1981066
DELTA_APPLICATION_WAIT_TIME   : 5169
DELTA_CONCURRENCY_TIME        : 0
DELTA_CLUSTER_WAIT_TIME       : 0
DELTA_USER_IO_WAIT_TIME       : 73023
DELTA_PLSQL_EXEC_TIME         : 0
DELTA_JAVA_EXEC_TIME          : 0
DELTA_SORTS                   : 0
DELTA_LOADS                   : 0
DELTA_INVALIDATIONS           : 0
DELTA_PHYSICAL_READ_REQUESTS  : 805
DELTA_PHYSICAL_READ_BYTES     : 787570688
DELTA_PHYSICAL_WRITE_REQUESTS : 0
DELTA_PHYSICAL_WRITE_BYTES    : 0
DELTA_IO_INTERCONNECT_BYTES   : 102411880
DELTA_CELL_OFFLOAD_ELIG_BYTES : 787570688
DELTA_CELL_UNCOMPRESSED_BYTES : 641465903
CON_ID                        : 225
CON_DBID                      : 1119628177
OBSOLETE_COUNT                : 0
AVOIDED_EXECUTIONS            : 0
DELTA_AVOIDED_EXECUTIONS      : 0

PL/SQL procedure successfully completed.

SQL> select * from v$sqlstats
  2  where sql_id = 'c4ncm7k1fqzgs'
  3  @pr
==============================
SQL_TEXT                      : SELECT /*+ findme2 */ T1.OWNER, T2.OBJECT_ID, T3.OBJECT_TYPE, T4.OBJECT_NAME, T5.CREATED FROM T1,
T2, T3,T4, T5 WHERE T1.ID =T2.ID AND T2.ID =T3.ID (+) AND T3.ID = T4.ID(+) AND T4.ID =T5.ID (+)
SQL_FULLTEXT                  : SELECT /*+ findme2 */ T1.OWNER, T2.OBJECT_ID, T3.OBJECT_TYPE, T4.OBJECT_NAME, T5.CREATED FROM T1,
T2, T3,T4, T5 WHERE T1.ID =T2.ID AND T2.ID =T3.ID (+) AND T3.ID = T4.ID(+) AND T4.ID =T5.ID (+)
SQL_ID                        : c4ncm7k1fqzgs
LAST_ACTIVE_TIME              : 17-JUN-20
LAST_ACTIVE_CHILD_ADDRESS     : 000000037D139930
PLAN_HASH_VALUE               : 2294840145
PARSE_CALLS                   : 1
DISK_READS                    : 3574555
DIRECT_WRITES                 : 818261           <==== LOTS OF TEMP NEEDED
DIRECT_READS                  : 3574555
BUFFER_GETS                   : 2759119
ROWS_PROCESSED                : 28666575
SERIALIZABLE_ABORTS           : 0
FETCHES                       : 286666
EXECUTIONS                    : 1
END_OF_FETCH_COUNT            : 1
LOADS                         : 1
VERSION_COUNT                 : 1
INVALIDATIONS                 : 0
PX_SERVERS_EXECUTIONS         : 0
CPU_TIME                      : 112444822
ELAPSED_TIME                  : 118497074
AVG_HARD_PARSE_TIME           : 29178
APPLICATION_WAIT_TIME         : 35242
CONCURRENCY_WAIT_TIME         : 223
CLUSTER_WAIT_TIME             : 0
USER_IO_WAIT_TIME             : 7817524
PLSQL_EXEC_TIME               : 0
JAVA_EXEC_TIME                : 0
SORTS                         : 0
SHARABLE_MEM                  : 52680
TOTAL_SHARABLE_MEM            : 52680
TYPECHECK_MEM                 : 0
IO_CELL_OFFLOAD_ELIGIBLE_BYTES: 22579560448
IO_INTERCONNECT_BYTES         : 30096064328
PHYSICAL_READ_REQUESTS        : 30880
PHYSICAL_READ_BYTES           : 29282951168
PHYSICAL_WRITE_REQUESTS       : 6450
PHYSICAL_WRITE_BYTES          : 6703300608
EXACT_MATCHING_SIGNATURE      : 10184766243625374875
FORCE_MATCHING_SIGNATURE      : 10184766243625374875
IO_CELL_UNCOMPRESSED_BYTES    : 21658331939
IO_CELL_OFFLOAD_RETURNED_BYTES: 16689373000
DELTA_PARSE_CALLS             : 0
DELTA_DISK_READS              : 0
DELTA_DIRECT_WRITES           : 0
DELTA_DIRECT_READS            : 0
DELTA_BUFFER_GETS             : 0
DELTA_ROWS_PROCESSED          : 0
DELTA_FETCH_COUNT             : 0
DELTA_EXECUTION_COUNT         : 0
DELTA_PX_SERVERS_EXECUTIONS   : 0
DELTA_END_OF_FETCH_COUNT      : 0
DELTA_CPU_TIME                : 0
DELTA_ELAPSED_TIME            : 0
DELTA_APPLICATION_WAIT_TIME   : 0
DELTA_CONCURRENCY_TIME        : 0
DELTA_CLUSTER_WAIT_TIME       : 0
DELTA_USER_IO_WAIT_TIME       : 0
DELTA_PLSQL_EXEC_TIME         : 0
DELTA_JAVA_EXEC_TIME          : 0
DELTA_SORTS                   : 0
DELTA_LOADS                   : 0
DELTA_INVALIDATIONS           : 0
DELTA_PHYSICAL_READ_REQUESTS  : 0
DELTA_PHYSICAL_READ_BYTES     : 0
DELTA_PHYSICAL_WRITE_REQUESTS : 0
DELTA_PHYSICAL_WRITE_BYTES    : 0
DELTA_IO_INTERCONNECT_BYTES   : 0
DELTA_CELL_OFFLOAD_ELIG_BYTES : 0
DELTA_CELL_UNCOMPRESSED_BYTES : 0
CON_ID                        : 225
CON_DBID                      : 1119628177
OBSOLETE_COUNT                : 0
AVOIDED_EXECUTIONS            : 0
DELTA_AVOIDED_EXECUTIONS      : 0


Questions about Consistent gets during smart scan

Rajeshwaran, Jeyabal, June 23, 2020 - 10:10 am UTC

Team - thanks for the excellent response to the above question. got one more question while working/reading about Chapter#2 -Smart scan from the book "Expert Oracle Exadata"

This is about a demo from Exadata X7 - quater rack with Oracle 18c on it

c##rajesh@PDB1> select ms.value bytes
  2  from v$mystat ms, v$statname sn
  3  where sn.statistic# = ms.statistic# and
  4  sn.name = 'cell physical IO bytes saved by storage index';

     BYTES
----------
         0
c##rajesh@PDB1> set autotrace traceonly explain statistics
c##rajesh@PDB1> select /*+test_query3*/ owner, sum(object_id)
  2  from big_table
  3  where owner ='EDM'
  4  group by owner;


Execution Plan
----------------------------------------------------------
Plan hash value: 3409261826

----------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           | 37122 |   507K|   494K  (1)| 00:00:20 |
|   1 |  SORT GROUP BY NOSORT      |           | 37122 |   507K|   494K  (1)| 00:00:20 |
|*  2 |   TABLE ACCESS STORAGE FULL| BIG_TABLE | 37122 |   507K|   494K  (1)| 00:00:20 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage("OWNER"='EDM')
       filter("OWNER"='EDM')


Statistics
----------------------------------------------------------
        373  recursive calls
          0  db block gets
    1816699  consistent gets
    1816056  physical reads
          0  redo size
        429  bytes sent via SQL*Net to client
        546  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         85  sorts (memory)
          0  sorts (disk)
          1  rows processed

c##rajesh@PDB1> set autotrace off
c##rajesh@PDB1>
c##rajesh@PDB1> select ms.value bytes, ( ms.value/8192) as blocks
  2  from v$mystat ms, v$statname sn
  3  where sn.statistic# = ms.statistic# and
  4  sn.name = 'cell physical IO bytes saved by storage index';

     BYTES     BLOCKS
---------- ----------
2755788800     336400

c##rajesh@PDB1> select sql_id from v$sql where sql_text like 'select /*+test_query3*/ owner%';

SQL_ID
-------------
2547da6d65f2w

c##rajesh@PDB1> select sql_id, plan_hash_value plan_hash, executions execs, buffer_gets,
  2  ceil( io_cell_offload_returned_bytes / 8192 ) offload_rtn_blks,
  3  ceil( io_interconnect_bytes / 8192 ) io_interconnect_blks,
  4  ( ( io_interconnect_bytes - io_cell_offload_returned_bytes ) / 8192 ) block_served_blocks
  5  from v$sqlstats
  6  where sql_id ='2547da6d65f2w'
  7  /

SQL_ID         PLAN_HASH      EXECS BUFFER_GETS OFFLOAD_RTN_BLKS IO_INTERCONNECT_BLKS BLOCK_SERVED_BLOCKS
------------- ---------- ---------- ----------- ---------------- -------------------- -------------------
2547da6d65f2w 3409261826          1     1816699             4359                 4401                  42

c##rajesh@PDB1> exec show_space('BIG_TABLE');
Unformatted Blocks .....................               0
FS1 Blocks (0-25) ......................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................              41
Full Blocks ............................       1,815,973
Total Blocks............................       1,820,728
Total Bytes.............................  14,915,403,776
Total MBytes............................          14,224
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................              82
Last Used Ext BlockId...................         118,784
Last Used Block.........................           2,832

PL/SQL procedure successfully completed.

c##rajesh@PDB1>


Given that only 4400 blocks were returned to the database server, why do buffer_gets, consistent gets and physical reads all seem to indicate that the entire table was read? "
Connor McDonald
July 28, 2020 - 5:16 am UTC

I checked with the Exadata team.

The stats are adjusted to in order to remain compatible with non-Exadata environments, and for backward compatibility.

Questions about Consistent gets during smart scan

Rajeshwaran, Jeyabal, July 13, 2020 - 4:07 pm UTC

Team:

Did i miss something here? are you looking for more information to the above follow up?

Kindly let me know if this need to be raised as a new question.
Connor McDonald
July 14, 2020 - 3:46 am UTC

Constant prodding via the site and via email will definitely change the priority ....but perhaps not in the direction you're expecting

Questions about Consistent gets during smart scan

Rajeshwaran, Jeyabal, July 28, 2020 - 5:22 am UTC

...
The stats are adjusted to in order to remain compatible with non-Exadata environments, and for backward compatibility.
....


Connor - thanks for the update, sorry not getting you on the above. could you please explain a bit more?
Connor McDonald
July 28, 2020 - 8:03 am UTC

If you run your demo on a non-exadata, you will get the same results for consistent gets / physical reads.

This is the measure of what it would take to satisfy the query.

The exadata storage server improves that, but the database tier reports the stats as if there were no storage server in play.

data dictionary to identify "Autonomous" database.

Rajeshwaran, Jeyabal, October 31, 2022 - 1:21 pm UTC

Team,

do we have any data dictionary to say that we connected to an Autonomous database or non-autonomous database ? if so what dictionary it is?

Here i connected to an Autonomous database, but i dont have any clue about where to find it.

rajesh@PDB1> @printtbl 'select * from product_component_version'
PRODUCT                       : "Oracle Database 19c Enterprise Edition "
VERSION                       : "19.0.0.0.0"
VERSION_FULL                  : "19.17.0.1.0"
STATUS                        : "Production"
-----------------

PL/SQL procedure successfully completed.

rajesh@PDB1> @printtbl 'select * from v$version'
BANNER                        : "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production"
BANNER_FULL                   : "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.1.0"
BANNER_LEGACY                 : "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production"
CON_ID                        : "0"
-----------------

PL/SQL procedure successfully completed.

rajesh@PDB1> @printtbl 'select * from v$cell'

PL/SQL procedure successfully completed.

Connor McDonald
November 02, 2022 - 4:26 am UTC

select cloud_identity
from v$pdbs;

gives you heaps of information.

Note that this column only exists on cloud databases, so you'll need to run it with dynamic SQL to make it work for all environments

data dictionary to identify "Autonomous" database.

Rajeshwaran, Jeyabal, November 02, 2022 - 6:41 am UTC

Thanks that helps.

....Note that this column only exists on cloud databases...

could see that this column (cloud_identity) exists on on-prem database as well, but it is empty. Here is the one from my test database running on my local machine.

demo@PDB1> set linesize 71
demo@PDB1> desc v$pdbs
 Name                                Null?    Type
 ----------------------------------- -------- -------------------------
 CON_ID                                       NUMBER
 DBID                                         NUMBER
 ....
 ....
 GUID_BASE64                                  VARCHAR2(30)
 RECOVERY_TARGET_PDB_INCARNATION#             NUMBER
 CLOUD_IDENTITY                               VARCHAR2(4000)
 CLOSE_TIME                                   TIMESTAMP(3) WITH TIME ZO
                                              NE

demo@PDB1> select cloud_identity
  2  from v$pdbs;

CLOUD_IDENTITY
-----------------------------------------------------------------------


demo@PDB1>