Posted by FatDBA on December 21, 2022
Christmas came a little early!
Finally, after associated with Oracle Databases and technologies for more than 15 years, and contributing towards the database community for more than 12 years, The FatDBA has finally got the golden email from Oracle Ace Program – “I am officially an Oracle Ace now ♠ ♠ ♠ “ 🙏 🤘 😊
Link to my profile: https://apexapps.oracle.com/pls/apex/r/ace_program/oracle-aces/ace?ace_id=2364&clear=2
Posted in Uncategorized | Leave a Comment »
Posted by FatDBA on December 16, 2022
Recently I was working on a performance problem where customer reported few of their SQL statements going for parallelism even when they are not forcing DOP via any HINT, and all of the referenced table and underlying Indexes were with degree=1
I was asked to take a look, and I immediately checked if Auto DOP was the reason forcing unwanted parallelism, but parallel_degree_policy was set to MANUAL which means the auto DOP, statement queuing and in-memory parallel execution all were disabled.
SQL> show parameter parallel_degree_policy
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string MANUALNext, I thought to verify Table and Indexes general stats or details and I queried DBA_TABLES & DBA_INDEXES for Instances column, and found one of the table was set to value ‘DEFAULT’. If we have a value of DEFAULT set for INSTANCES, it will always force the query use DEFAULT degree of parallelism.
Let me explain the impact of having DEFAULT value for Instances, and how it forces SQL to spawn parallelism. For demo purpose, I am going to create a test table and an index with INSTANCES value set to DEFAULT.
[oracle@oracleontario ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 19 08:23:12 2022
Version 19.15.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
SQL> create table fatdba_table as select * from dba_objects;
Table created.
SQL>
SQL> select count(*) from fatdba_table;
COUNT(*)
----------
74932
SQL>
SQL> create index fatdba_table_idx on fatdba_table(OBJECT_TYPE,object_name) parallel(DEGREE 1 INSTANCES DEFAULT);
Index created.
SQL>
SQL> select index_name,degree,instances from dba_indexes where index_name='FATDBA_TABLE_IDX';
INDEX_NAME DEGREE INSTANCES
------------------------------ ---------------------------------------- ----------------------------------------
FATDBA_TABLE_IDX 1 DEFAULT
SQL>
Alright the stage is set, lets run a SQL statement and force it to use that Index and see its impact on the execution.
SQL> explain plan for select /*+ index_ffs(fatdba_table,fatdba_table_idx) */ count(distinct object_name) from fatdba_table
where OBJECT_TYPE='TABLE';
Explained.
SQL> set linesize 400 pagesize 400
SQL> select * from table(dbms_xplan.display) ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1154043599
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 157 (1)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | 66 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 66 | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 66 | | | Q1,01 | PCWP | |
| 5 | VIEW | VW_DAG_0 | 1558 | 100K| 157 (1)| 00:00:01 | Q1,01 | PCWP | |
| 6 | HASH GROUP BY | | 1558 | 68552 | 157 (1)| 00:00:01 | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 1561 | 68684 | 156 (0)| 00:00:01 | Q1,01 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 1561 | 68684 | 156 (0)| 00:00:01 | Q1,00 | P->P | HASH |
| 9 | PX BLOCK ITERATOR | | 1561 | 68684 | 156 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 10 | INDEX FAST FULL SCAN| FATDBA_TABLE_IDX | 1561 | 68684 | 156 (0)| 00:00:01 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - filter("OBJECT_TYPE"='TABLE')
22 rows selected.
SQL>
As expected, it forced SQL to go with parallelism. Let me set INSTANCE value of the Index to 1 and see what happens next.
SQL> alter index FATDBA_TABLE_IDX noparallel;
Index altered.
SQL> select index_name,degree,instances from dba_indexes where index_name='FATDBA_TABLE_IDX';
INDEX_NAME DEGREE INSTANCES
------------------------------ ---------------------------------------- ----------------------------------------
FATDBA_TABLE_IDX 1 1
SQL> SQL>
SQL> select index_name,degree,instances from dba_indexes where index_name='FATDBA_TABLE_IDX';
INDEX_NAME DEGREE INSTANCES
------------------------------ ---------------------------------------- ----------------------------------------
FATDBA_TABLE_IDX 1 1
SQL> SQL> explain plan for select /*+ index_ffs(fatdba_table,fatdba_table_idx) */ count(distinct object_name)
from fatdba_table where OBJECT_TYPE='TABLE';
Explained.
SQL> select * from table(dbms_xplan.display) ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 3184007477
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 157 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
| 2 | VIEW | VW_DAG_0 | 1558 | 100K| 157 (1)| 00:00:01 |
| 3 | HASH GROUP BY | | 1558 | 68552 | 157 (1)| 00:00:01 |
|* 4 | INDEX FAST FULL SCAN| FATDBA_TABLE_IDX | 1561 | 68684 | 156 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("OBJECT_TYPE"='TABLE')
16 rows selected.
And no parallelism was used after set the value of INSTANCES to 1.
Hope It Helped!
Prashant Dixit
Posted in Advanced, troubleshooting | Tagged: Database, optimization, oracle, performance | Leave a Comment »
Posted by FatDBA on December 4, 2022
Recently one of my friend who started learning OCI told me he is not able to locate Oracle 21c Autonomous database in the list, but can only see 19c. He was using ‘Always Free Autonomous Databases’ to test few things on Oracle Database version 21c.
I was sure it was happening due to region he selected, I mean you can provision free Autonomous Databases only in your home region, and not all regions support the same database versions. The supported version may be 19c-only or 21c-only, depending on the region you have selected.
When checked, found he was trying from the ap-tokyo-1 (Tokyo region – key NRT) where only 19c version is available in ‘Always Free Autonomous Databases’. So, it always depends the region where you are trying creating your Always Free Autonomous Database Oracle Database weather you see 19c or 21c.
Always Free databases can be created in Oracle Cloud Infrastructure accounts that are in a trial period, have paying status, or are always free. More details : https://docs.oracle.com/en-us/iaas/Content/Database/Concepts/adbfreeoverview.htm
Try and sign up for Oracle Cloud Free Tier, trust me its awesome 🙂 🙂 : https://www.oracle.com/cloud/free/
Hope It Helped
Prashant Dixit
Posted in Basics | Tagged: cloud, Database, oracle | Leave a Comment »
Tales From A Lazy Fat DBA 