One of the similarities between storage index and database index is the
“warming up” process - a process that
needs to take place, before indexes become “optimal” either after a storage
server (in case of storage index) or database server (in case of database
index) is restarted.
When a database server is restarted, the contents associated with the
buffer cache is lost and has to be reloaded, this means for the period of time
while the instance “warms up”, there is a spike in the physical IO activity,
index range scans which greatly benefit from re-using cached data therefore
need to perform additional physical IO for both the index blocks and the table
blocks they reference. The overhead associated with these additional physical
IO slow down the overall performance of these index related execution plans.
The following query is executed after a flushed buffer cache.
c##rajesh@PDB1>
@script02a.sql
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 78xsf8phdu8x0, child number 0
-------------------------------------
select * from t
where object_id = 42
Plan hash
value: 767293772
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time
| Buffers | Reads |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | 1452 |00:00:00.06 | 1468 |
1457 |
| 1 |
TABLE ACCESS BY INDEX ROWID BATCHED| T |
1 | 1452 |00:00:00.06 | 1468 |
1457 |
|* 2 |
INDEX RANGE SCAN
| T_IDX | 1 | 1452 |00:00:00.01 | 16 |
5 |
-------------------------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=42)
There is a considerable amount of physical reads having to reload the
contents of the database buffer cache. If we now re-run the query
c##rajesh@PDB1>
@script02a.sql
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID 78xsf8phdu8x0, child number 0
-------------------------------------
select * from t
where object_id = 42
Plan hash
value: 767293772
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time
| Buffers |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | 1452 |00:00:00.01 | 1468 |
| 1 |
TABLE ACCESS BY INDEX ROWID BATCHED| T |
1 | 1452 |00:00:00.01 | 1468 |
|* 2 |
INDEX RANGE SCAN
| T_IDX | 1 | 1452 |00:00:00.01 | 16 |
----------------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=42)
All the data is now cached and the overall query response time
improves. Once the database instance has
warmed up and much of the database hot, data-set of interest has been cached,
Physical IO’s are minimized and performance improves considerably.
Similarly storage index need to be created on the fly, need a period of
time to warm up and be created/stored in memory to become optimal.
So when an exadata storage server is re-started, the performance
associated with storage index on the server is initially sluggish as well,
while oracle performs additional physical IO off disk to create necessary
storage index in memory, additionally new queries using new column predicates
will also be initially sluggish while any necessary storage index are created
and cached in memory. Note that storage index are created on 1 MB storage
region basis, so that it could well take a period of time for Storage index to
be created across all associated storage regions of the table.
The following query is executed on a relatively new table
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>
@script02b.sql
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
SQL_ID cvp5nb9dvqcyu, child number 0
-------------------------------------
select * from t
where owner ='JYU'
Plan hash value:
1601196873
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |
A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | |
7384 |00:00:00.69 | 2108K| 2108K|
|* 1 |
TABLE ACCESS STORAGE FULL| T
| 1 | 3225K|
7384 |00:00:00.69 | 2108K| 2108K|
-----------------------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
1 - storage("OWNER"='JYU')
filter("OWNER"='JYU')
19 rows
selected.
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 6069080
c##rajesh@PDB1>
The amount of physical IO saved by storage index is zero. However when
the query is re-executed and the associated storage index on “OWNER” column is
fully created.
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>
@script02b.sql
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
SQL_ID cvp5nb9dvqcyu, child number 0
-------------------------------------
select * from t
where owner ='JYU'
Plan hash
value: 1601196873
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |
A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 |
| 7384 |00:00:00.35 | 2108K|
2108K|
|* 1 |
TABLE ACCESS STORAGE FULL| T
| 1 | 3225K|
7384 |00:00:00.35 | 2108K| 2108K|
-----------------------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
1 - storage("OWNER"='JYU')
filter("OWNER"='JYU')
19 rows
selected.
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 12308168704
cell physical
IO interconnect bytes returned by smart scan 7679408
c##rajesh@PDB1>
The amount of physical IO saved by storage index increases considerably
and overall performance improves as a result.
Restarting the database server will result in sluggish performance for
a period of time until most of the hot data is cached. This is also the case
when exadata storage server is re-started, in part while the necessary storage
index are recreated and cached.
No comments:
Post a Comment