Beiträge getaggt mit OU EMEA Newsletter
Index Competition in #Oracle 12c
Suppose you want to find out which type of index is best for performance with your workload. Why not set up a competition and let the optimizer decide? The playground:
ADAM@pdb1 > select max(amount_sold) from sales where channel_id=9;
MAX(AMOUNT_SOLD)
----------------
5000
ADAM@pdb1 > @lastplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID 3hrvrf1r6kn8s, child number 0
-------------------------------------
select max(amount_sold) from sales where channel_id=9
Plan hash value: 3593230073
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES | 1 | 6 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | BSTAR | 1 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CHANNEL_ID"=9)
20 rows selected.
There is a standard B*tree index on the column CHANNEL_ID that speeds up the SELECT above. I think a bitmap index would be better:
ADAM@pdb1 > create bitmap index bmap on sales(channel_id) invisible nologging;
Index created.
ADAM@pdb1 > alter index bstar invisible;
Index altered.
ADAM@pdb1 > alter index bmap visible;
Index altered.
ADAM@pdb1 > select max(amount_sold) from sales where channel_id=9;
MAX(AMOUNT_SOLD)
----------------
5000
ADAM@pdb1 > @lastplan
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
select max(amount_sold) from sales where channel_id=9
Plan hash value: 2178022915
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES | 1 | 6 | 3 (0)| 00:00:01 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | BMAP | | | | |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("CHANNEL_ID"=9)
21 rows selected.
With this 12c New Feature (two indexes on the same column), I got a smooth transition to the new index type. But this left no choice to the optimizer. What about this?
ADAM@pdb1 > alter index bmap invisible; Index altered. ADAM@pdb1 > alter session set optimizer_use_invisible_indexes=true;
Now both indexes are invisible and the optimizer may choose any of them. Turns out that it likes the bitmap index better here. Instead of watching the execution plans, V$SEGMENT_STATISTICS can also be used to find out:
ADAM@pdb1 > select object_name,statistic_name,value
from v$segment_statistics
where object_name in ('BSTAR','BMAP')
and statistic_name in ('physical reads','logical reads');
OBJECT STATISTIC_NAME VALUE
------ ------------------------------ ----------
BSTAR logical reads 22800
BSTAR physical reads 6212
BMAP logical reads 1696
BMAP physical reads 0
The numbers of BSTAR remain static while BMAP numbers increase. You may also monitor that with DBA_HIST_SEG_STAT across AWR snapshots. Now isn’t that cool? 🙂
Couple of things to be aware of here:
Watch out for more than just physical/logical reads – bitmap indexes may cause a locking problem in an OLTP environment.
Don’t keep the two indexes invisible forever – after you saw which one performs better, drop the other one. Invisible indexes need to be maintained upon DML and therefore slow it down.
What happens to the Standby when you move a datafile on the Primary?
In 12c, we have introduced online datafile movement as a new feature. Now does that impact an existing standby database? I got asked that yesterday during an online webinar. My answer was that I expect no impact at all on the standby database since redo apply doesn’t care about the physical placement of the datafile on the primary. But I added also that this is just an educated guess because I didn’t test that yet. Now I did:
You know, I like to practice what I preach: Don’t believe it, test it! 🙂
Foreign Archived Log in #Oracle – what does it mean?
When you look into V$RECOVERY_AREA_USAGE, you see a strange row at the bottom:
SQL> select * from v$recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 10.18 0 73 0
BACKUP PIECE 0 0 0 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 0 0 0 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
Curious what that could be? You will see values other than zero on a Logical Standby Database:
SQL> connect sys/oracle@logst as sysdba
Connected.
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
LOGICAL STANDBY
SQL> select * from v$recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 14.93 0 9 0
BACKUP PIECE 0 0 0 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 0 0 0 0
FOREIGN ARCHIVED LOG 2.03 0 26 0
AUXILIARY DATAFILE COPY 0 0 0 0
In contrast to a Physical Standby Database, this one writes not only into standby logs but also into online logs while being in standby role. That leads to two different kinds of archive logs:
When DML (like insert and update) is done on the primary 1) that leads to redo entries into online logs 2) that are simultaneously shipped to the standby and written there into standby logs 2) also. The online logs on the primary and the standby logs on the standby will be archived 3) eventually. So far that is the same for both physical and logical standby. But now a difference: Logical standby databases do SQL Apply 4) by logmining the standby or the archive logs that came from the primary. That generates similar DML on the standby which in turn leads LGWR there to write redo into online logs 5) that will eventually get archived 6) as well.
A logical standby could do recovery only with its own archive logs (if there was a backup taken before) but not with the foreign archive logs. Therefore, those foreign archive logs can and do get deleted automatically. V$ARCHIVED_LOG and V$FOREIGN_ARCHIVED_LOG can be queried to monitor the two different kinds of logs.
That was one topic of the course Oracle Database 12c: Data Guard Administration that I delivered as an LVC – therefore the picture. Hope you find it useful 🙂
Pickleball spielen auf YouTube
Pickleball spielen 