Beiträge getaggt mit Performance Tuning
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.
Create a SQL Profile to let the Optimizer ignore hints in #Oracle
Something I presented recently during an Oracle Database 12c Performance Management and Tuning class. Hints are a double-edged sword; they may do more harm than good. What if hinted SQL comes from an application that you as the DBA in charge can’t modify? You can tell the Optimizer to ignore that nasty hint.
One method is to use alter session set „_optimizer_ignore_hints“=true; This will make the optimizer ignore all hints during that session – also the useful ones, so maybe that is not desirable. The method I show here works on the statement level. The playground:
SQL> select /*+ index (sales,sales_bix) */ max(amount_sold) from sales where channel_id=3;
MAX(AMOUNT_SOLD)
----------------
1782.72
Elapsed: 00:00:04.92
SQL> select plan_table_output from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID 7m2k0y4hy1ngh, child number 0
--------------------------------------
select /*+ index (sales,sales_bix) */ max(amount_sold) from sales where channel_id=3
Plan hash value: 1767991108
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 139K(100)| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES | 17M| 131M| 139K (1)| 00:00:06 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | SALES_BIX | | | | |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("CHANNEL_ID"=3)
The index hint directs the optimizer here to use a bad plan that wouldn’t be used otherwise:
SQL> select max(amount_sold) from sales where channel_id=3;
MAX(AMOUNT_SOLD)
----------------
1782.72
Elapsed: 00:00:01.06
SQL> select plan_table_output from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID ahw4npmjpnu1k, child number 0
--------------------------------------
select max(amount_sold) from sales where channel_id=3
Plan hash value: 1047182207
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 28396 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
|* 2 | TABLE ACCESS FULL| SALES | 17M| 131M| 28396 (1)| 00:00:02 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CHANNEL_ID"=3)
Now the remedy:
begin
dbms_sqltune.import_sql_profile(
name => 'MYPROFILE1',
category => 'DEFAULT',
sql_text => 'select /*+ index (sales,sales_bix) */ max(amount_sold) from sales where channel_id=3',
profile => sqlprof_attr('IGNORE_OPTIM_EMBEDDED_HINTS')
);
end;
/
PL/SQL procedure successfully completed.
SQL> select /*+ index (sales,sales_bix) */ max(amount_sold) from sales where channel_id=3;
MAX(AMOUNT_SOLD)
----------------
1782.72
Elapsed: 00:00:01.05
SQL> select plan_table_output from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 7m2k0y4hy1ngh, child number 0
-------------------------------------
select /*+ index (sales,sales_bix) */ max(amount_sold) from sales where channel_id=3
Plan hash value: 1047182207
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 28396 (100)| |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL| SALES | 17M| 131M| 28396 (1)| 00:00:02 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CHANNEL_ID"=3)
Note
-----
PLAN_TABLE_OUTPUT
----------------------------------------------------
- SQL profile MYPROFILE1 used for this statement
This works for that SQL statement only without having to modify the application. The SQL profile can be removed like this:
SQL> exec dbms_sqltune.drop_sql_profile('MYPROFILE1')
PL/SQL procedure successfully completed.
All the above is not new, but still I think it might be worthwhile to mention it here for your reference, should you encounter some nasty hints once 🙂
Full Database Caching in #Oracle 12c
If your Oracle Database fits into the buffer cache, we will put all segments into it now upon access – no more direct reads:
SQL> connect / as sysdba
Connected.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
SQL> select sum(bytes)/1024/1024 as mb from v$datafile;
MB
----------
1424
SQL> select current_size from v$buffer_pool;
CURRENT_SIZE
------------
1872
The requirements are met here, I don’t need to force anything:
SQL> select force_full_db_caching from v$database;
FORCE_FUL
---------
NO
SQL> select bytes/1024/1024 as mb from dba_segments
where owner='ADAM' and segment_name='SALES';
MB
----------
600
SQL> select count(*) from adam.sales;
COUNT(*)
----------
10000000
SQL> show parameter parallel_degree_policy
NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
parallel_degree_policy string
MANUAL
The table got accessed once and is now loaded into the buffer cache. PARALLEL_DEGREE_POLICY is on the default MANUAL, remember that.
SQL> connect adam/adam
Connected.
SQL> select name,value from v$mystat natural join v$statname
where name in ('physical reads','physical reads direct','session logical reads');
NAME VALUE
-------------------------------------------------- ----------
session logical reads 42
physical reads 0
physical reads direct 0
SQL> select count(*) from sales;
COUNT(*)
----------
10000000
SQL> select name,value from v$mystat natural join v$statname
where name in ('physical reads','physical reads direct','session logical reads');
NAME VALUE
-------------------------------------------------- ----------
session logical reads 75620
physical reads 0
physical reads direct 0
Only logical reads, because the table is in the buffer cache. Also for parallel queries:
SQL> select /*+ parallel (sales,4) */ max(amount_sold) from sales; MAX(AMOUNT_SOLD) ---------------- 5000 SQL> select * from v$pq_sesstat; STATISTIC LAST_QUERY SESSION_TOTAL CON_ID -------------------------------------------------- ---------- ------------- ---------- Queries Parallelized 1 1 0 DML Parallelized 0 0 0 DDL Parallelized 0 0 0 DFO Trees 1 1 0 Server Threads 4 0 0 Allocation Height 4 0 0 Allocation Width 1 0 0 Local Msgs Sent 156 156 0 Distr Msgs Sent 0 0 0 Local Msgs Recv'd 156 156 0 Distr Msgs Recv'd 0 0 0 DOP 4 0 0 Slave Sets 1 0 0 13 rows selected. SQL> select name,value from v$mystat natural join v$statname where name in ('physical reads','physical reads direct','session logical reads'); NAME VALUE -------------------------------------------------- ---------- session logical reads 152410 physical reads 0 physical reads direct 0 SQL> select name,value from v$sysstat where name='physical reads direct'; NAME VALUE -------------------------------------------------- ---------- physical reads direct 21 SQL> select /*+ parallel (sales,4) */ max(amount_sold) from sales; MAX(AMOUNT_SOLD) ---------------- 5000 SQL> select name,value from v$sysstat where name='physical reads direct'; NAME VALUE -------------------------------------------------- ---------- physical reads direct 21
Looks like I did In-Memory Parallel Query although PARALLEL_DEGREE_POLICY is on MANUAL, doesn’t it? Just for comparison, I did the same with an 11g version:
SQL> select banner from v$version;
BANNER
-------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> select sum(bytes)/1024/1024 as mb from v$datafile;
MB
----------
1090
SQL> select current_size from v$buffer_pool;
CURRENT_SIZE
------------
1200
SQL> select bytes/1024/1024 as mb from dba_segments where owner='ADAM' and segment_name='SALES';
MB
----------
528
SQL> select count(*) from adam.sales;
COUNT(*)
----------
10000000
SQL> show parameter parallel_degree_policy
NAME TYPE VALUE
------------------------------------ --------------------------------- ---------
parallel_degree_policy string MANUAL
SQL> connect adam/adam
Connected.
SQL> select name,value from v$mystat natural join v$statname
where name in ('physical reads','physical reads direct','session logical reads');
NAME VALUE
---------------------------------------------------------------------- ----------
session logical reads 26
physical reads 0
physical reads direct 0
SQL> select count(*) from sales;
COUNT(*)
----------
10000000
SQL> select name,value from v$mystat natural join v$statname
where name in ('physical reads','physical reads direct','session logical reads');
NAME VALUE
---------------------------------------------------------------------- ----------
session logical reads 67465
physical reads 67433
physical reads direct 67433
SQL> select /*+ parallel (sales,4) */ max(amount_sold) from sales;
MAX(AMOUNT_SOLD)
----------------
5000
SQL> select * from v$pq_sesstat;
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------------------ ---------- -------------
Queries Parallelized 1 1
DML Parallelized 0 0
DDL Parallelized 0 0
DFO Trees 1 1
Server Threads 4 0
Allocation Height 4 0
Allocation Width 1 0
Local Msgs Sent 116 116
Distr Msgs Sent 0 0
Local Msgs Recv'd 116 116
Distr Msgs Recv'd 0 0
11 rows selected.
SQL> select name,value from v$mystat natural join v$statname
where name in ('physical reads','physical reads direct','session logical reads');
NAME VALUE
---------------------------------------------------------------------- ----------
session logical reads 135115
physical reads 134866
physical reads direct 134866
As you can see, I got direct reads in 11g for both the serial and the parallel query.
Do not confuse this feature with the In-Memory Option, it is much less sophisticated than that. But it doesn’t come with an extra charge at least 🙂
Pickleball spielen auf YouTube
Pickleball spielen 