Being an Oracle Professional, I like to share all my Real Life Performance Tuning Challenges and Experiences. The Content and Views on this site are my own and not necessarily those of Oracle. While, I write on my real life experiences, the resolutions mentioned are solely mine. Comments / Criticisms are always a welcome.
I want to refresh my UAT Database from an existing Production Data.
I want to move an Oracle Database to an Autonomous AI Database. This Oracle Database could be anything – an OnPremise or on any Cloud.
I want to move a Non-Oracle Database to an Autonomous AI Database
For 1 above, Autonomous AI Database provides Database Cloning option. It is just few clicks and the job is done. For 2, there are multiple methods and all of these are documented in Autonomous AI Database Documentation. Each of these methods are based on the migration scenario, database size etc. Few months back, Oracle also released a much awaited Tablespace migration option. For 3, there are multiple migration tools available that takes the data from Non-Oracle Sources, converts the data into Oracle format and then loads. All well…
But, what if the scenario is changed a bit i.e.What if, I have to refresh only a subset of Data from Production? Say, my Production is 50 TB and I just need few schema’s that are around 5 TB in size? Cloning an entire 50 TB doesn’t make sense.
One of my customers, a SaaS provider, with 20 to 30 Schema’s, had a requirement to refresh only few schema’s. They were testing few application changes and wanted to test these changes only for these schema’s. Cloning was not a solution here, as they wanted to avoid overriding other schema’s. A new clone and then dropping other schema’s means additional cost, time and efforts. The option for them was to – Take an EXPDP of these Schema’s on to Object Storage and then Import it on UAT. Manual effort and continuous monitoring.
How about a simplified way of achieving this, with an option to stop and resume from where it left? This blog is about a feature released few days back that achieves this, with just few steps. No manual efforts required. No continuous monitoring required and top of it, resilient to failures. What else do we need? Click here for the Documentation Link to know more about this feature.
In this blog, I will walk you through the steps that achieve this with just few steps. Underneath, it uses DBMS_PARALLEL_EXECUTE API to distribute an object into multiple chunks, and process these chunks individually in parallel sessions simulating parallelism. In the past, I have written many blogs about Manual Parallelism using DBMS_PARALLEL_EXECUTE. I call it manual parallelism, as I have a control on the number of threads, I can increase or reduce the number of threads at run-time and I also have an option to just process a failed thread instead of processing an entire object again. Just search on dbms_parallel_execute or manual parallelism on this blog search and you would find much of my challenging tasks accomplished using this API.
With Autonomous AI Database, you just need to run few steps and internally, all the steps needed to create multiple chunks, scheduling these are done automatically for you. So, lets get into the steps.
In this case, I want to move a Schema called MAKROOCT25 from one of my Autonomous AI Database to another Autonomous Database. The schema is in ATPAI Database (Source) and is to be moved to ATPMAY2026 Database (Target).
The steps are :
On my Target, I created a Directory called ATPAI_WALLET. I will download cwallet.sso file of ATPAI Database into this directory.
Then, created a credential that stores the Username and Password of a privileged user from my Source. My process (on Target) will connect to this User via Database Link, to process the chunks.
Finally, we then run an API DBMS_CLOUD_IMPORT and pass the relevant parameters. Internally, this API will create a Database Link for us, connect to specified service, read the remote object(s) and then load the data into our Target.
From Oracle to Oracle, the process will create the required Indexes and Constraints as well.
create directory ATPAI_WALLET as ‘ATPAI_WALLET_DIR’;
begin dbms_cloud.get_object( credential_name =>’VIVEK_OBJ_CRED’, object_uri =>'<Object_Storage_URL>/cwallet.sso’, directory_name=>’ATPAI_WALLET’); end; /
HostName and ServiceName can be extracted from OCI Console (Under Connections Tab for the Source Database) or from TNSNAMES.ORA.
Once this block DBMS_CLOUD_IMPORT is executed, the API will create a database link MAKROOCT25_4M_ATPAI_DB_LINK, connect to the Username that is provided as a part of DBMS_CLOUD.CREATE_CREDENTIAL and then will schedule an Import Job on Target.
What I have observed is, even if I specify HIGH or MEDIUM Service, the data is read using LOW Service Name and this makes sense. DBMS_PARALLEL_EXECUTE itself creates multiple chunks and processes these chunks using parallel threads. This means, a table is read and loaded by these parallel threads. For each parallel thread, a database link network channel is opened. More the number of CPUs, more the number of parallel threads.
You can monitor the progress from DBA_DATA_IMPORT_TASK_STATUS. Sample output is attached.
In case the job fails or doesn’t start, you can also validate whether the Database Link that was created implicitly by the API is working as expected. For this, you can run the following query. The DBLink is created as <TASKNAME>_DB_LINK.
select sysdate from dual@MAKROOCT25_4M_ATPAI_DB_LINK;
If the job fails in-between, you can check it from
SELECT CHUNK_ID, TASK_OWNER, TASK_NAME, STATUS, START_ROWID, END_ROWID, ERROR_MESSAGE FROM DBA_PARALLEL_EXECUTE_CHUNKS;
The best part of this approach is, you can suspend the job and resume it later. For example, if you are moving some large data and it coincides with some critical batch job, you can suspend it temporarily and resume it later. The job will resume from where it left. It is also resilient to failures, meaning, by any chance if the instance crashes or is terminated, the failed job can be restarted and it will load the data from the point it failed.
I don’t want to make this blog too long, hence would conclude here. The idea here was to post about this brilliant feature and the underneath logic that it uses. Is it restricted to only Autonomous AI Database? Answer is NO. You can very well use this logic on Non-Autonomous Databases as well. I wrote about DBMS_PARALLEL_EXECUTE in the past. You can use it for processing large amount of data, in a controlled, efficient manner and with same resiliency.
As a final note, please note that this feature does more than why I posted. You can Migrate/Move data from Non-Oracle Databases to Autonomous AI Databases as well. Autonomous Database will do all the Data-type conversion. You do not need any migration tool to accomplish this. At the time of writing this blog, this feature supports MySQL, PostgreSQL and Amazon Redshift Databases. I am sure, more Non-Oracle Databases would be added to this list. Read the Documentation for more information about this feature.
This is a personal blog. The thoughts and opinions expressed here are entirely my own and do not necessarily represent those of my employer, team, or any organization associated with me. Any code, scripts, or technical examples are shared for learning purposes and should be used with appropriate caution.
Happy to announce that I will be traveling across 6 cities in India speaking on my favorite topic around Database and Application Performance. This event is organized by “All India Oracle User Group” and is scheduled from 20th July 2024. Prominent Speakers from across the globe will be traveling as well. A great opportunity to Learn and to build a Network. For registration, click on the following link.
Autonomous Database was released around March 2018. The first was a Data Warehouse Implementation that supports business Intelligence and Analytical Workload. It was Autonomous Data Warehouse. Next, the transaction workload was added to this family, called as Autonomous Transaction Processing followed by Autonomous JSON and Autonomous APEX.
One of the coolest feature of Autonomous Data Warehouse (ADW) is easy integration with Data Lake, which means, while we can store our data natively within our Autonomous Database – for faster processing, this feature allows us to store and access our data from an external storage. On Cloud, when we talk about external storage, we usually refer to an Object Storage. This is by way of creating an External Table. In this, the Table Metadata is stored in the Data Dictionary and the actual data is stored externally. This allows us to save on Storage cost. Refer to Oracle Documentation for more details on this feature.
External Table can be Non-Partitioned, Partitioned and Hybrid Partitioned. As with Oracle Partitioning that improves Query performance by pruning the partitions that are not needed for our Query. This same improvement applies to External Tables as well. With Partitioned External Table, you get the benefit of Partition Pruning thus scanning only required external data. Remember, Queries on External Data will not be as fast as queries on database tables. However, if I have an Archived Table that is in-frequently accessed and is not performance sensitive, I can always move these to External Storage. And, to improve the query performance on External Storage, External Partitioning is highly recommended. As mentioned earlier, the other benefit of this feature is COST. Hybrid Partitioning is basically mix of both i.e. I can have few historical partitions on an External Storage and latest performance sensitive data within the database.
In this blog, I will talk about External Partition Tables, and subsequent blogs would take this topic further.
Assuming you have a large table with around 5 year of Data. Users frequently query latest data. The table is currently non-partitioned. As the data is growing, I want to ensure consistent performance. How do I do that?
There are multiple strategies, like :
Archive the Data to a New Table and Purge it from the Original Table. Reports requiring latest data would run on the Original Table and Reports on the Historical Data would access the Archived Table.
Partition the Table and leverage the benefits of Partition Pruning. This is the most common strategy.
My topic for today is on Strategy#1 i.e. Archiving to a New Table. This solution can also be applied if a table has only historical data. Anyways, many customers do implement strategy#1 and move the table to some other database, called as Archival Database. With Autonomous Database, We don’t need to do that and we will see this in action. My subsequent blogs will cover both the strategies that can be optimized further on Autonomous Databases.
For this demonstration, I will use LINEORDER table which is under SSB (Sample Star Benchmark) Schema. This schema is created by-default when you provision an Autonomous Database. It’s a huge table. Therefore, I will create another table as Sample 2% and will use it as my Source table. The very first step would be to identify the partition strategy. In this case, monthly partition on LO_ORDERDATE column is a first choice. However, the other requirement is to have sub-partition it on LO_TAX column, based on Tax Codes. So, what we need here is an External Table with Monthly partitions and Sub-Partitions on LO_TAX.
OT OWNER NAME NUM_ROWS BLOCKS
--- -------------------- ------------------------------ ---------- ----------
1T ADMIN LINEORDER 119993317 644744
Next step is to move the data on to the External Storage (an Object Storage). This data will be moved keeping our partitioning strategy in mind. Before I move to the next step, it is important to know that Autonomous Database support many different file formats and structure. On Autonomous Database you can create partitioned external tables from HIVE style partitioned data or from simple folder partitioned data stored on your Cloud Object Store. For example, a Hive format partitioned file will look like :
The advantage of this approach is the partitioned information is available in the data file path name itself and therefore, the partitioned columns need not be part of the actual data. Partition pruning is done directly based on the path name. So, in our case the path name would be :
LINEORDER/MONTH_YEAR=JUN1993/TAX=2/filename.gz
In this case, the files will be compressed so that these occupy less space. MONTH_YEAR is a derived column, but TAX is LO_TAX column of LINEORDER Table. Since these columns and column values are part of my file path name, my CSV files need not contain these columns and column values. With Simple Folder partitioned data, the columns and column values of the partitioned columns have to be in the data files. So, in this case since I am using HIVE style partitioned data, I will dynamically create folders with column name and column values.
In order to access my Object Storage, the very first mandatory requirements is to create a credentials. Alternatively, you can use OCI Resource Principals. I am assuming you have all the required credentials. If not, please follow the documentation.
Next, I will use dbms_cloud.export_data api to export the data into compressed CSV files on to my Object Storage. To speed up the process, I created a CONTROL_TABLE with distinct MONYYYY values from LINEORDER and then scheduled a pl/sql block from three different sessions. This pl/sql block will export the data for a specified Month and a Tax Code. The data inserted into the control table has a condition to_char(lo_orderdate,’MONYYYY’)!=’AUG1998′. I will explain this later.
create table control_table (
partition_name varchar2(100),
partition_number number,
status char(1)
);
insert into control_table(partition_name, status)
SELECT DISTINCT TO_CHAR(LO_ORDERDATE,'MONYYYY') MONTH_YEAR, 'N' FROM LINEORDER WHERE to_char(lo_orderdate,'MONYYYY')!='AUG1998';
COMMIT;
UPDATE control_table set partition_number=rownum;
commit;
DECLARE
FILE_URI_O VARCHAR2(1000);
FILE_URI_F VARCHAR2(128);
FILE_URI VARCHAR2(1000);
L_STATEMENT VARCHAR2(1000);
BEGIN
FOR OUTER_LOOP IN (SELECT PARTITION_NAME MONTH_YEAR, PARTITION_NUMBER FROM CONTROL_TABLE WHERE PARTITION_NUMBER>=1 AND PARTITION_NUMBER<=25 AND STATUS='N' ORDER BY PARTITION_NUMBER)
LOOP
FOR INNER_LOOP IN (SELECT DISTINCT LO_TAX TAX FROM LINEORDER WHERE TO_CHAR(LO_ORDERDATE,'MONYYYY')=OUTER_LOOP.MONTH_YEAR)
LOOP
L_STATEMENT:='select lo_orderkey, lo_linenumber, lo_custkey, lo_partkey, lo_suppkey, lo_orderdate, lo_orderpriority, lo_shippriority, lo_quantity, lo_extendedprice, lo_ordtotalprice, lo_discount, lo_revenue, lo_supplycost, lo_commitdate, lo_shipmode from lineorder where to_char(lo_orderdate,''MONYYYY'')='||''''||OUTER_LOOP.MONTH_YEAR||''''||' and lo_tax='||INNER_LOOP.TAX||'';
FILE_URI_F:='MONTH_YEAR='||OUTER_LOOP.MONTH_YEAR||'/TAX='||INNER_LOOP.TAX||'/';
FILE_URI_O:='https://objectstorage.us-ashburn-1.oraclecloud.com/n/my_tenancy/b/External_Partitions/o/LINEORDER/'||FILE_URI_F;
FILE_URI:=FILE_URI_O||OUTER_LOOP.MONTH_YEAR||'_'||INNER_LOOP.TAX;
DBMS_CLOUD.EXPORT_DATA(CREDENTIAL_NAME => 'MY_CREDENTIAL', FILE_URI_LIST => FILE_URI, QUERY=>L_STATEMENT, FORMAT=> JSON_OBJECT('TYPE' VALUE 'CSV', 'quote' value '"', 'COMPRESSION' VALUE 'GZIP', 'MAXFILESIZE' VALUE '20485760'));
END LOOP;
UPDATE CONTROL_TABLE SET STATUS='Y' WHERE PARTITION_NUMBER=OUTER_LOOP.PARTITION_NUMBER;
COMMIT;
END LOOP;
END;
/
L_STATEMENT variable has the query that is used to export the data. I have the required columns in the SELECT Clause alongwith the relevant WHERE predicate. Each run of the statement will create a CSV file for a required partition and sub-partition. Further, I dynamically create the folders. If the folders doesn’t exist, the api will create it for us else it will create the relevant file within it’s folder. The folder name has the partition column name and it’s value. For example, I will have folder names as:
HIVE Format Folders and Files
Now, I have all the data on to my Object Storage and these are HIVE Format. I have compressed CSV files for each Month and Tax. I will now create an External Partition Table. This is done using dbms_cloud.create_external_part_table api.
This creates our external partition table. The data resides on our Object Storage and the metadata is stored in the data dictionary. Now, you can run your queries against this table and the data will be retrieved from the Object Storage.
Time to run some queries and check the run time plan. Run time pan will help us validate whether the queries are able to leverage the benefits of partition pruning. We will join this external partitioned table to another table with data stored natively and from the run time plan, we see that Adaptive Optimization kicks in as well.
# Query 1
select lo_custkey, lo_orderdate, lo_shipmode, lo_quantity from lineorder_ext_part where month_year='AUG1993' and tax=2 and lo_orderkey=3517992324;
LO_CUSTKEY LO_ORDERDATE LO_SHIPMOD LO_QUANTITY
---------- -------------------- ---------- -----------
29663183 10-AUG-1993 00:00:00 MAIL 9
Elapsed: 00:00:01.75
SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST -bytes +predicate +note +cost +adaptive'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dmak3yzpw71a2, child number 0
-------------------------------------
select lo_custkey, lo_orderdate, lo_shipmode, lo_quantity from
lineorder_ext_part where month_year='AUG1993' and tax=2 and
lo_orderkey=3517992324
Plan hash value: 1982497926
--------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
| 1 | PARTITION LIST SINGLE | | 1 | 3 (0)|
|* 2 | EXTERNAL TABLE ACCESS FULL| LINEORDER_EXT_PART | 1 | 3 (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("MONTH_YEAR"='AUG1993' AND "TAX"=2 AND
"LO_ORDERKEY"=3517992324))
# Query 2
select lo_custkey, c_name, lo_orderdate, lo_shipmode, lo_quantity
from lineorder_ext_part,
vivek.customer
where month_year='DEC1997'
and tax=0
and lo_orderkey=3187415872
and c_custkey = lo_custkey;
LO_CUSTKEY C_NAME LO_ORDERDATE LO_SHIPMOD LO_QUANTITY
---------- ------------------------- -------------------- ---------- -----------
11640322 Customer#011640322 04-DEC-1997 00:00:00 FOB 6
Elapsed: 00:00:02.27
SQL> SQL> @apc
SQL> set echo on
SQL> set lines 200
SQL> set pages 1000
SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST -bytes +predicate +note +cost +adaptive'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6pn53c37c2dfb, child number 0
-------------------------------------
select lo_custkey, c_name, lo_orderdate, lo_shipmode, lo_quantity from
lineorder_ext_part, vivek.customer where month_year='DEC1997' and
tax=0 and lo_orderkey=3187415872 and c_custkey = lo_custkey
Plan hash value: 1739419959
--------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 5 (100)|
|- * 1 | HASH JOIN | | 1 | 5 (0)|
| 2 | NESTED LOOPS | | 1 | 5 (0)|
| 3 | NESTED LOOPS | | 1 | 5 (0)|
|- 4 | STATISTICS COLLECTOR | | | |
| 5 | PARTITION LIST SINGLE | | 1 | 3 (0)|
| * 6 | EXTERNAL TABLE ACCESS FULL| LINEORDER_EXT_PART | 1 | 3 (0)|
| * 7 | INDEX UNIQUE SCAN | CUSTOMER_PK | 1 | 1 (0)|
| 8 | TABLE ACCESS BY INDEX ROWID | CUSTOMER | 1 | 2 (0)|
|- 9 | TABLE ACCESS FULL | CUSTOMER | 1 | 2 (0)|
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C_CUSTKEY"="LO_CUSTKEY")
6 - filter(("MONTH_YEAR"='DEC1997' AND "TAX"=0 AND
"LO_ORDERKEY"=3187415872))
7 - access("C_CUSTKEY"="LO_CUSTKEY")
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
- this is an adaptive plan (rows marked '-' are inactive)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
A query against all the sub-partitions or partitions works as well.
SQL> select lo_orderkey, lo_orderdate, lo_shipmode, lo_quantity from lineorder_ext_part where month_year='AUG1993' and lo_custkey=29663183;
LO_ORDERKEY LO_ORDERDATE LO_SHIPMOD LO_QUANTITY
----------- -------------------- ---------- -----------
3517992324 10-AUG-1993 00:00:00 MAIL 9
2251158885 21-AUG-1993 00:00:00 REG AIR 29
Elapsed: 00:00:12.28
SQL> @apc
SQL> set echo on
SQL> set lines 200
SQL> set pages 1000
SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST -bytes +predicate +note +cost +adaptive'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5m709tc22ua7u, child number 0
-------------------------------------
select lo_orderkey, lo_orderdate, lo_shipmode, lo_quantity from
lineorder_ext_part where month_year='AUG1993' and lo_custkey=29663183
Plan hash value: 146054153
--------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 14 (100)|
| 1 | PARTITION LIST ITERATOR | | 7 | 14 (15)|
|* 2 | EXTERNAL TABLE ACCESS FULL| LINEORDER_EXT_PART | 7 | 14 (15)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("MONTH_YEAR"='AUG1993' AND "LO_CUSTKEY"=29663183))
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
27 rows selected.
From the queries that we executed against this External Partitioned Table, we can clearly see that for the application it doesn’t matter whether the data is fetched from within the database storage or from an External Storage. It is completely transparent to the application. One important point to note here is that these External Tables are READ ONLY and the Data is not managed by the Database.
Now, lets add some more data to this External Partition Table. While generating thh CSV files, I excluded the data for AUGUST 1998. I will not generate the CSV files for AUG1998 and we will see whether I am able to query the data.
DECLARE
FILE_URI_O VARCHAR2(1000);
FILE_URI_F VARCHAR2(128);
FILE_URI VARCHAR2(1000);
L_STATEMENT VARCHAR2(1000);
BEGIN
FOR INNER_LOOP IN (SELECT DISTINCT LO_TAX TAX FROM LINEORDER WHERE TO_CHAR(LO_ORDERDATE,'MONYYYY')='AUG1998')
LOOP
L_STATEMENT:='select lo_orderkey, lo_linenumber, lo_custkey, lo_partkey, lo_suppkey, lo_orderdate, lo_orderpriority, lo_shippriority, lo_quantity, lo_extendedprice, lo_ordtotalprice, lo_discount, lo_revenue, lo_supplycost, lo_commitdate, lo_shipmode from lineorder where to_char(lo_orderdate,''MONYYYY'')='||''''||'AUG1998'||''''||' and lo_tax='||INNER_LOOP.TAX||'';
FILE_URI_F:='MONTH_YEAR=AUG1998/TAX='||INNER_LOOP.TAX||'/';
FILE_URI_O:='https://objectstorage.us-ashburn-1.oraclecloud.com/n/my_tenancy/b/External_Partitions/o/LINEORDER/'||FILE_URI_F;
FILE_URI:=FILE_URI_O||'AUG1998_'||INNER_LOOP.TAX;
DBMS_CLOUD.EXPORT_DATA(CREDENTIAL_NAME => 'MY_CREDENTIAL', FILE_URI_LIST => FILE_URI, QUERY=>L_STATEMENT, FORMAT=> JSON_OBJECT('TYPE' VALUE 'CSV', 'quote' value '"', 'COMPRESSION' VALUE 'GZIP', 'MAXFILESIZE' VALUE '20485760'));
END LOOP;
END;
/
Now, let me run a query against this newly added CSV file. While the files are uploaded and available in the correct HIVE format folders, you can see that Oracle could not access the data and the run time plan says PARTITION LIST EMPTY.
SQL> select lo_orderkey, lo_orderdate, lo_shipmode, lo_quantity from lineorder_ext_part where month_year='AUG1998' and tax=6 and lo_custkey=16287940;
no rows selected
Elapsed: 00:00:00.11
SQL> @apc
SQL> set lines 200
SQL> set pages 1000
SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST -bytes +predicate +note +cost +adaptive'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5s7wagh8za9bx, child number 1
-------------------------------------
select lo_orderkey, lo_orderdate, lo_shipmode, lo_quantity from
lineorder_ext_part where month_year='AUG1998' and tax=6 and
lo_custkey=16287940
Plan hash value: 3670407871
--------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | PARTITION LIST EMPTY | | 1 | 2 (0)|
|* 2 | EXTERNAL TABLE ACCESS FULL| LINEORDER_EXT_PART | 1 | 2 (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("MONTH_YEAR"='AUG1998' AND "TAX"=6 AND
"LO_CUSTKEY"=16287940))
SQL> select count(*) from lineorder_ext_part where month_year='AUG1998';
COUNT(*)
----------
0
As I said earlier, External Tables are READ Only and the Data is not managed by Oracle Database. For this, we need to Refresh the External Table using SYNC_EXTERNAL_PART_TABLE procedure of dbms_cloud. Once done, the newly added CSV files would be considered as a new partition and the data can then be queried.
BEGIN
DBMS_CLOUD.SYNC_EXTERNAL_PART_TABLE(table_name => 'LINEORDER_EXT_PART');
END;
/
SQL> select lo_orderkey, lo_orderdate, lo_shipmode, lo_quantity from lineorder_ext_part where month_year='AUG1998' and tax=6 and lo_custkey=16287940;
LO_ORDERKEY LO_ORDERDATE LO_SHIPMOD LO_QUANTITY
----------- -------------------- ---------- -----------
3004306725 02-AUG-1998 00:00:00 REG AIR 14
Elapsed: 00:00:42.34
SQL> @apc
SQL> set echo on
SQL> set lines 200
SQL> set pages 1000
SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST -bytes +predicate +note +cost +adaptive'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5s7wagh8za9bx, child number 1
-------------------------------------
select lo_orderkey, lo_orderdate, lo_shipmode, lo_quantity from
lineorder_ext_part where month_year='AUG1998' and tax=6 and
lo_custkey=16287940
Plan hash value: 917046205
----------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | PX COORDINATOR | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 2 (0)|
| 3 | PX BLOCK ITERATOR | | 1 | 2 (0)|
|* 4 | EXTERNAL TABLE ACCESS FULL| LINEORDER_EXT_PART | 1 | 2 (0)|
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(("MONTH_YEAR"='AUG1998' AND "TAX"=6 AND
"LO_CUSTKEY"=16287940))
Note
-----
- automatic DOP: Computed Degree of Parallelism is 16 because of degree limit
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
31 rows selected.
Elapsed: 00:00:00.08
SQL> select count(*) from lineorder_ext_part where month_year='AUG1998';
COUNT(*)
----------
99688
Elapsed: 00:00:01.44
SQL>
Wow! a cool feature and easy to use. Isn’t it? Last, with the latest enhancement to this feature, two new hidden columns are added to the External Table. These are file$path and file$name. These columns can be queried to check the Source File Path and Name.
SQL> select lo_orderkey, file$path, file$name from lineorder_ext_part where month_year='AUG1993' and tax=2 and lo_custkey=29663183;
LO_ORDERKEY FILE$PATH FILE$NAME
----------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------
3517992324 https://objectstorage.us-ashburn-1.oraclecloud.com/n/my_tenancy/b/External_Partitions/o/LINEORDER/MONTH_YEAR=AUG1993/TAX=2 AUG1993_2_10_20220704T111509Z.csv.gz
This is very easy to implement on Autonomous Database and is a Cost Saving option. Performance insensitive and historical data can be easily moved to an external storage. Accessing this data is as simple as accessing natively stored data. Do try it out and let me know how it works.
Recently I was working on an issue related to loading data from a flat file on to Autonomous Data Warehouse using dbms_cloud api. While lot has been written on this subject, I thought of writing on this as the challenge here was that the file in this case was without any delimiter. The Original data to be loaded was around 90 Million, but for testing purpose customer was loading around 200k rows.
What is a Delimiter? Delimiter is one or more character that separates two strings. These characters are used to identify different columns.
One of the best blog on this topic is this blog from Nilay Panchal.
Coming back to my topic of this blog. In this case, customer was trying to load 200k rows into a table and the way they were using it, the time it took to load these 200k rows was around 25 minutes which is too huge considering that actual production number of rows would be 90 Million.
As mentioned, the challenge here was that the content in this flat file had no delimiter or any character to identify column break. The original flat file had around 500 columns, but for testing the file was truncated to 5 columns. The idea here is to demonstrate an efficient way of loading the data into a table when the data in the flat file is without any delimiter. The sample data is as under:
CCODEDEBUTRUNSHUNDREDSPLAYER_NAME
IND151119891592151SACHIN TENDULKAR
AUS081219951337841 RICKY PONTING
SA141219951328945 JACQUES KALLIS
SL200720001240038 KUMAR SANGAKARA
WI061219901195334 BRIAN LARA
IND20062011 720227 VIRAT KOHLI
Our very first step will be to create cloud object storage credentials using dbms_cloud.create_credentials. If you have already created a credential, then you can skip this. Check dba_credentials for the list of Credentials.
begin
dbms_cloud.create_credential(
credential_name=>'VIVEK_CREDENTIAL',
username=>'VIVEKS',
password=>'paste_auth_token_here'
);
end;
/
SQL> column owner for a20
SQL> column credential_name for a30
SQL> column username for a20
SQL> select owner, credential_name, username from dba_credentials;
OWNER CREDENTIAL_NAME USERNAME
-------------------- ------------------------------ --------------------
ADMIN VIVEK_CREDENTIAL VIVEKS
I have uploaded by data.txt file that contains the required data to be loaded on the Object Storage. Next Step will be create a table in which the data is to be loaded. For COPY_DATA, you need to ensure the Target Table is already created as the procedure would fail.
Now, I will create by COPY_DATA procedure with the required inputs. It is important to note the format and field_list option. In my case, the date was in DDMMYYYY format and hence I had to specify the dateformat explicitly to allow copy_data to parse the date accurately. Without the dateformat value, my copy_data failed with an error. The other parameter is field_list, which ensures that in the absence of delimiter, the copy_data parses the column values based on the start and end position. You need to ensure that these position are accurate.
begin
dbms_cloud.copy_data(
table_name =>'CRICKETER_DATA',
credential_name =>'VIVEK_CREDENTIAL',
file_uri_list =>'&MY_OBJECT_STORAGE_URL/data.txt',
format => json_object('skipheaders' value '1', 'dateformat' value 'ddmmyyyy'),
field_list =>'CCODE POSITION(1:3), DEBUT POSITION(4:11), RUNS POSITION(12:16), HUNDREDS POSITION(17:18), PLAYER_NAME POSITION(19:34)');
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.50
SQL> SQL> select * from cricketer_data;
CCO DEBUT RUNS HUNDREDS PLAYER_NAME
--- -------------------- ---------- ---------- ----------------
IND 15-NOV-1989 00:00:00 15921 51 SACHIN TENDULKAR
AUS 08-DEC-1995 00:00:00 13378 41 RICKY PONTING
SA 14-DEC-1995 00:00:00 13289 45 JACQUES KALLIS
SL 20-JUL-2000 00:00:00 12400 38 KUMAR SANGAKARA
WI 06-DEC-1990 00:00:00 11953 34 BRIAN LARA
IND 20-JUN-2011 00:00:00 7202 27 VIRAT KOHLI
6 rows selected.
If for some reason the copy_data procedure fails, it creates a error log table, which is reported immediately along with the error number. To drill down to the actual error, you need to query the log table. For example, when I omitted FORMAT option from my COPY_DATA procedure, it failed with following error. I queried the COPY$22_LOG to extract the actual error.
select * from COPY$21_LOG;
RECORD
------------------------------------------------------------------------------------------------------------------------------------
LOG file opened at 01/28/20 05:15:18
Total Number of Files=1
Data File: &MY_OBJECT_STORAGE_URL/data.txt
Log File: COPY$21_331103.log
LOG file opened at 01/28/20 05:15:18
Bad File: COPY$21_331103.bad
Field Definitions for table COPY$Q18IZ07MUUMYRU3IG6MU
Record format DELIMITED BY
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
CCODE CHAR (3)
Record position (1, 3)
Terminated by "|"
DEBUT CHAR (8)
Record position (4, 11)
Terminated by "|"
RUNS CHAR (5)
Record position (12, 16)
Terminated by "|"
HUNDREDS CHAR (2)
Record position (17, 18)
Terminated by "|"
PLAYER_NAME CHAR (16)
Record position (19, 34)
Terminated by "|"
error processing column DEBUT in row 1 for datafile &MY_OBJECT_STORAGE_URL/data.txt
ORA-01858: a non-numeric character was found where a numeric was expected
40 rows selected.
After Sangem 2018, now it’s time for another OTN Yatra, now renamed to Oracle Groundbreaker Yatra. Sangam is hosted every year and had been either in Bangalore or Hyderabad. It is a One City Event. Oracle Groundbreaker Yatra on the other hand is a Multi-City Tour. I assume, last year it was a 6 City Tour. This year, it is scheduled to be hosted in Mumbai, Chennai, Delhi, Bengaluru, Hyderabad, Kolkata, Pune, Ahmedabad, Visakhapatnam and Thiruvananthapuram.
Registration for the Groundbreaker would open soon. Many prominent speakers would be travelling and it should be a never-miss event for the Oracle Database Community (DBA’s, Developers, Architects, Data Scientists etc). Sandesh Rao, Connor Mcdonald, Roy Swonger and Gurmeet Goindi would be speaking on some interesting topic (as always). Roy Swonger from Oracle US would be visiting India for the first time. Every time you attend the sessions from these experts, you tend to learn something new and I am sure, this time as well, you will come out with bags full of Knowledge.
I am also one of the speakers for the Groundbreaker Yatra and would be travelling to 4 cities, apart from Mumbai (as it is my base location). I have opted for the locations, where I am either travelling for the first time for an User Group event or have visited only once or twice. Now, you can guess it :). I will be in Ahmedabad, Kolkata, Visakhapatnam, Thiruvananthapuram and Mumbai. I assume the agenda for some of the locations is already published and for the other locations, is in the final stage.
Now, on my session. I am presenting a Session on “Database and Application Performance ! Then and Now.” It is a 1 hour session. I would be covering some of the Performance Challenges the DBA’s or Developers use to face and still face and how these are automatically taken care in Autonomous Databases. I will cover Optimizer, Parallelism, Oracle 19c and walk through some of the topics around Reinforcement Learning and Data Science. Usually, my sessions are supported by live demo’s. But for a 1 hour session, I am not sure whether I will be able to, but shall try to make it more interesting. See you all in July 2019.
Sangam 2018 is scheduled for 7th and 8th December 2018. I am presenting a session on “SQL Tuning ! Tips, Tools and Techniques” which is on 7th December 2018 and is immediately after Lunch. A 45 minute session doesn’t justify the topic, but will try my level best to cover few interesting real life examples. On 8th December, I will be co-hosting a Fire side chat on Performance along side Tirthankar Lahiri (VP of Product Management, Oracle US), Arup Nanda, GP Gongloor and Karan Dodwal. This is scheduled for 2.55 pm. The only issue is that I may have to leave early to catch my flight back to Mumbai.
North India Chapter – Chandigarh. Mark your calendar for 24th November 2018 as I will be speaking for the entire day on Performance Optimization covering some interesting topics around SQL Optimization, Optimizer, Indexes, Autonomous Databases and many more. This is a Full day event which gives me good amount of time to demonstrate some of the interesting facts. Registration for this event is open. Use the following link to register. See you soon.
Recently, I was working on a customer issue. The issue was a performance comparison between a Competition and an Oracle Database. The performance of Competition was reported to be better than Oracle. Now, this is a classic case of Bad Schema Design and a Badly Written Query. Working on this issue reminded me of a great learning that I had after reading “Expert Oracle Database Architecture” by Thomas Kyte. He wrote about a classic issue with a Pl/SQL Block running in SQL Server and generating wrong results when ported to Oracle Database due to the way these 2 databases compare NULL values. Each of these databases are different. It also reminded me of one of my response to a query from a customer on “A count(*) from a Table is doing a Full Table Scan, even though it has a Unique Index on it”.
As Tom mentioned in his book, every database is different and implement the features differently. Now, the third party tools that connect to each of these different data sources generate queries that syntactically work on all but may not run optimally. Therefore, it is important to understand our Data and design the Schema with all the required constraints and indexes in place.
In this case, customer was running few analytical reports from a Third Party Reporting Tool (Tableau). Customer selected few columns with couple of predicates with Date Range and some product name. The Queries were around 40-50 Lines. Surprisingly, for each of the Predicates, Tableau added few additional predicates and due to these additional predicates, the run time plan was around 2700+ lines long. As an example, I have a table T1 and have to generate a report selecting object id’s and names for any of the 2 TEMPORARY values (Y or N). My query will look like :
select object_id, object_name from t1 where temporary=:b1;
If I have an Index on TEMPORARY Column, Optimizer will come out with an optimal plan based on it’s cost calculation. However, when run from Tableau, it came out with the following query:
select object_id, object_name from t1
where ((temporary=:b1) or (temporary is NULL and :b1 is NULL));
What will be the implication of this added OR conditions ? Let’s see.
SQL> create table t1 as
select * from all_objects; 2
Table created.
SQL> exec dbms_stats.gather_table_stats(user,'T1', method_opt=>'for all columns size auto for columns temporary size 100');
PL/SQL procedure successfully completed.
SQL> create index t1_idx on t1(temporary);
Index created.
SQL> select num_rows, blocks from dba_tables where table_name='T1';
NUM_ROWS BLOCKS
---------- ----------
68417 1375
SQL> variable l_temporary varchar2(1);
SQL> exec :l_temporary:='Y';
PL/SQL procedure successfully completed.
SQL> select object_id, object_name from t1
where ((temporary=:l_temporary) or (temporary is NULL and :l_temporary is NULL));
161 rows selected.
SQL> select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID 7xsahyu4q594y, child number 0
-------------------------------------
select object_id, object_name from t1 where ((temporary=:l_temporary)
or (temporary is NULL and :l_temporary is NULL))
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 375 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 161 | 6923 | 375 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("TEMPORARY"=:L_TEMPORARY OR ("TEMPORARY" IS NULL AND
:L_TEMPORARY IS NULL)))
161 Rows out of 68000, which is less than 1%. Index Access would have been a better option. At customer end, the table was huge with Billions of Rows and with multiple such OR Predicates, the very first observation was the amount of time the Optimizer took to Parse the Query. The Query took around 184 Seconds to run and the observation was that out of 184 Seconds, around 175 Seconds were spent on Parsing. This was identified as a BUG in 18c (BUG#28725660) and the primary cause identified as the change in OR Expansion behaviour in 18c. This BUG is fixed in 19c. Backporting it to 18c would have taken some time, so the other fix that we applied was to add NOT NULL Constraints to some of the columns. From the Data, we could see that none of the columns had NULL values. We checked with the developers and they mentioned that NULL values are not stored in this column. Therefore, it was safe to add these constraints. Continuing with our example above, let’s add a NOT NULL constraint to our Table T1.
SQL> alter table t1 modify temporary not null;
Table altered.
select object_id, object_name from t1
where ((temporary=:l_temporary) or (temporary is NULL and :l_temporary is NULL));
select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1775246573
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 161 | 6923 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_IDX | 161 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TEMPORARY"=:L_TEMPORARY)
The run time plan of the same query is now an Index Scan and is much better than previous. This additional input to the Optimizer was enough to transform the query. You can generate 10053 trace to see the transformation.
NOT NULL constraint combined with Unique Index is required to answer a COUNT(*) query from an Index, which one of my customer asked long time back. This is another case, where NOT NULL constraint helped optimizer come up with an optimal plan.
It is important to optimize the Schema and provide all the critical inputs to the Optimizer. Generic reporting tools would come out with queries that work on all the databases. These generated queries may work on some database and may not work on other. Therefore, the title of this blog “Know your Reporting Tool” :).
On the real customer issue, post adding the constraints to few of the columns, the query response time reduced drastically from 184 Seconds to less than 10 seconds far better than the competition.
#Autonomous #AIOUG Presenting a Full Day Event on 8th September 2018 for North India Oracle User Group in Gurgaon. This is on Oracle Autonomous Database. Would be covering some interesting technical capabilities of Autonomous Databases. I am covering the 2 offerings i.e. Autonomous Data Warehouse and Autonomous Transaction Processing. For Registration, click on the following link :
This being an Oracle User Group Session, focus would be on the Technical Capabilities of ADW / ATP, like Parallel Processing, Concurrency, Optimizer Enhancements and Behaviour and most importantly, Competition.
So, North India Folks : See you all on 8th September 2018.
Sangam, India’s largest All India Oracle User Group Event is back and this time in Hyderabad. Oracle experts across the globe would be speaking and therefore, this is the best opportunity to learn from them and collaborate with other Oracle professionals.
I will be presenting 2 sessions and will be hosting a “Fireside Chat with Performance Tuning Experts” round table discussions. Along side me, there would be Tirthankar Lahiri, VP-Product Management for In-Memory Technologies, Oracle, Karan Dodwal and Kishore Surve.
Oracle Autonomous Database is one of the most talked about technology and many of you would be eager to know more about it. I will be speaking on Autonomous Database on 9th December 2017. At present, it is scheduled for 2 pm India Time. Please do check the schedule, as it can change. Seats are limited. Therefore, to avoid disappointment, kindly book your seats in advance.
Another session of mine is on “Indexing : Facts and Myths”. This session will walk you through some of the common myths and few facts. A must attend session for Technical Folks. Again, due to the limited capacity, it is better to book your seat in advance. This is scheduled for 8th December 2017.
On 9th December 2017, join me and other Oracle Experts for a fireside chat on performance. Bring your questions and get response from the experts. Performance queries are difficult to address without some data or artifacts. If you can carry some of these, that would help us further and the chat would me more meaningful.
Post my previous Blog on Consistent Gets for an Index Scan, there were few other queries that I received over the chat. The queries were related to Clustering Factor of an Index and therefore, the recommendation or comment was that the Columns should be ordered to keep the value of a Clustering Factor as low as possible. The other comment was that High Cardinality or Low Cardinality plays a critical role with an exception for Unique or Primary Key Indexes. Again, Clustering Factor was cited as the reason behind this comment. Thought of clarifying this as well. In my opinion, this is again a MYTH.
The myth that I am going to address here is – Indexes and Column Ordering should be designed such that the Clustering Factor is as low as possible. This means, an Index on (B,A) should be preferred than (A,B) if the clustering factor of (A,B) is higher than (B,A).
Assuming, I have application queries, like :
select C, D from TABLE_A where A=:b1;
select C, D from TABLE_A where B=:b1 and A=:b2;
If I go by the above myth, shall I then create an Index on (B,A) ? Will this index be used for Query#1 ? Remember, Index Skip Scan is an Optimizer decision and is chosen if the leading column is a low cardinality column. The definition of low cardinality would be an Optimizer decision based on the costing. If an index on (B,A) is not used by Query#1, then shall I go ahead and create another Index on A ? Indexes improve query performance but are overhead for DML’s and therefore, less the number of Indexes, better the DML performance would be. Further, if a column is badly clustered, whether it is a leading column or a trailing column, it doesn’t matter and we will see this with an example. Therefore, as I mentioned in earlier and in my previous blog, Column Ordering of an Index should be basis your application queries and should not be designed basis any other table or column level statistics.
In this case, I am creating a table VIVEK_TEST with 100K rows. Column BC stands for BAD_CLUSTERED and will dominate the clustering factor. There are other 2 columns of interest – Object_ID which is a non unique column and RNUM_UNQ which is a Unique Column.
exec dbms_random.seed(0);
create table vivek_test as
with test as
(select * from all_objects where rownum between 1 and 10000)
select trunc((rownum-1)/1000) object_id,
rownum rnum_unq,
round(dbms_random.value(1,100),0) bc,
a.object_name,
a.temporary,
a.created
from test a,
test b
where rownum between 1 and 10000;
exec dbms_stats.gather_table_stats(user,'VIVEK_TEST');
column owner for a30
select owner, num_rows, blocks from dba_tables where table_name='VIVEK_TEST';
OWNER NUM_ROWS BLOCKS
-------------------- ---------- ----------
SCOTT 10000 62
First, I will create 4 Indexes – 2 of these are Single Column Indexes and 2 are Multi-Column.
create index vivek_test_oid on vivek_test(object_id);
create index vivek_test_oid_bc on vivek_test(object_id, bc);
create index vivek_test_bc on vivek_test(bc);
create index vivek_test_bc_oid on vivek_test(bc,object_id);
column index_name for a30
select index_name, num_rows, blevel, leaf_blocks, distinct_keys, clustering_factor
from dba_indexes
where table_name='VIVEK_TEST'
order by 1;
INDEX_NAME NUM_ROWS BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ---------- ---------- ----------- ------------- -----------------
VIVEK_TEST_BC 10000 1 20 100 4642 <-- Single Column on BC (Bad_Clustered)
VIVEK_TEST_BC_OID 10000 1 24 1000 4642 <-- BC is the Leading Column
VIVEK_TEST_OID 10000 1 20 10 56
VIVEK_TEST_OID_BC 10000 1 24 1000 4856 <-- BC is the Trailing Column
As seen in the example above, column ordering doesn’t matter when it comes to the Clustering Factor. My choice of Indexing would be :
VIVEK_TEST_BC_OID if most of my queries are on
BC=:b1;
BC =:b1 and object_id=:b2;
VIVEK_TEST_OID_BC if most of my queries are on
object_id=:b1;
BC=:b1 and object_id=:b2;
Now, I will drop these indexes and create new indexes on the combination of BC and RNUM_UNQ. RNUM_UNQ is 100% Unique Column.
create index vivek_test_rnum on vivek_test(rnum_unq);
create index vivek_test_rnum_bc on vivek_test(rnum_unq, bc);
create index vivek_test_bc on vivek_test(bc);
create index vivek_test_bc_rnum on vivek_test(bc, rnum_unq);
column index_name for a30
select index_name, num_rows, blevel, leaf_blocks, distinct_keys, clustering_factor
from dba_indexes
where table_name='VIVEK_TEST'
order by 1;
INDEX_NAME NUM_ROWS BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ---------- ---------- ----------- ------------- -----------------
VIVEK_TEST_BC 10000 1 20 100 4642 <-- Single Column on BC (Bad_Clustered)
VIVEK_TEST_BC_RNUM 10000 1 26 10000 4642 <-- Leading Column dominates CF
VIVEK_TEST_RNUM 10000 1 21 10000 56
VIVEK_TEST_RNUM_BC 10000 1 26 10000 56
For an Index with a Column, either leading or trailing, with 100% Unique values, the leading column would dictate the calculation of Clustering Factor. This is obvious as for the Clustering factor calculation, the values are sorted on the first_column then on Second_Column. For a better idea of how is it calculated, you may run the following queries :
## CF Calculation for BC, RNUM_UNQ (it matches the value as per DBA_INDEXES)
select sum(block_change) from (
select block_fno, bc, RNUM_UNQ, prev_bfno,
(case when nvl(prev_bfno,0)!=block_fno then 1 else 0 end) block_change from (
select block_fno, bc, RNUM_UNQ, lag(block_fno) over (order by bc, rnum_unq) prev_bfno from (
select dbms_rowid.rowid_block_number(rowid)||'.'||
dbms_rowid.ROWID_TO_ABSOLUTE_FNO(rowid,'&Schema','&table_name') block_fno,
bc, RNUM_UNQ
from VIVEK_TEST
order by bc, RNUM_UNQ, block_fno)
)
);
SUM(BLOCK_CHANGE)
-----------------
4642
## CF Calculation for RNUM_UNQ, BC (it matches the value as per DBA_INDEXES)
select sum(block_change) from (
select block_fno, RNUM_UNQ, bc, prev_bfno,
(case when nvl(prev_bfno,0)!=block_fno then 1 else 0 end) block_change from (
select block_fno, RNUM_UNQ, bc, lag(block_fno) over (order by rnum_unq, bc) prev_bfno from (
select dbms_rowid.rowid_block_number(rowid)||'.'||
dbms_rowid.ROWID_TO_ABSOLUTE_FNO(rowid,'&Schema','&table_name') block_fno,
bc, RNUM_UNQ
from VIVEK_TEST
order by RNUM_UNQ, bc, block_fno)
)
);
SUM(BLOCK_CHANGE)
-----------------
56
This query can be further modified to get the calculation. Remove the SUM(BLOCK_CHANGE) SELECT from the query and you could generate the output with the calculation. For every Index Entry, if the table block is changed, the BLOCK_CHANGE value is set to 1. This query will help you understand the calculation. This holds true for a combination of Non-Unique columns as well. It is not that for column involving Unique Column, the calculation is different and this will be clear if you deep dive into the queries pasted above.
During my session, I also mentioned a FLAW in Clustering Factor. It is that it doesn’t take into consideration the caching effect and to address this, Oracle introduced TABLE_CACHED_BLOCKS preference. I am against setting manually setting clustering_factor to a lower value as it will be overwritten by the next statistics gathering process. Also, I am STRONGLY against CTAS using ORDER BY to improve the clustering_factor. By using CTAS, you can improve the clustering_factor of one index, but it will impact this for other indexes. The best way is to use the TABLE_CACHED_BLOCKS preference as depicted below.
I will use the queries pasted above to come out with the actual clustering_factor and will then set the TABLE_CACHED_BLOCKS preference. For demonstration, I will work on an Index on BC, RNUM_UNQ. This process can be used for any of the indexes.
## I will create a temporary table to hold the clustering_factor calculation
create table cluf_factor as
select bc, blkno,
lag(blkno,1,blkno) over(order by bc) prev_blkno,
case when blkno!=lag(blkno,1,blkno) over(order by bc) or rownum=1
then 1 else null end cluf_ft from
(select bc, rnum_unq, dbms_rowid.rowid_block_number(rowid) blkno
from VIVEK_TEST
where BC is not null
order by bc);
compute sum of cnt on report
break on report
select blkno, count(*) cnt from cluf_factor group by blkno order by 1;
BLKNO CNT
---------- ----------
255883 188
255884 185
255885 185
255886 185
255887 185
255936 182
255937 180
255938 180
255939 180
255940 180
255941 180
255942 180
255943 180
255945 180
255946 180
255947 180
255948 180
255949 180
255950 180
255951 180
258896 180
258897 180
258898 180
258899 180
258900 180
258901 180
258902 180
258903 180
258905 180
258906 180
258907 180
258908 180
258909 180
258910 180
258911 180
260480 180
260481 180
260482 180
260483 180
260484 180
260485 180
260486 180
260487 180
260489 180
260490 180
260491 180
260492 180
260493 180
260494 180
260495 180
260496 180
260497 180
260498 180
260499 180
260500 180
260501 70
----------
sum 10000
56 rows selected.
As per the output, each of the block was touched around 180 times. Each of these touch were considered as it these were read from the Disk. However, Oracle purely works in Memory (Buffer_Cache, Shared_Pool etc) and once the block is read from disk, it is cached in the Buffer_cache thus avoiding the disk i/o. This flaw causes unwanted high clustering_factor and therefore, is addressed by way of TABLE_CACHED_BLOCKS.
TABLE_CACHED_BLOCKS value defaults to 1. Increasing this value (to, say 250) means, clustering_factor for an Index will not be incremented, if the table block being referenced by the current Index entry has been referenced by any of the previous 250 Index Entries. In our case, each of the table blocks were referenced by previous 180 index entries. Therefore, I will change this value to 180.
Clustering Factor is calculated for Unique Indexes as well and is critical for costing. The only time when clustering_factos is not considered or not relevant for Unique or Primary Keys is when querying for all the columns part of the Unique or Primary Key i.e. INDEX UNIQUE SCAN. However, clustering_factor will play a critical role when accessing the Unique or Primary Key using INDEX RANGE SCAN.
With this blog, I tried addressing following Myth’s –
- Indexes and Column Ordering should be designed such that the Clustering Factor is as low as possible.
- Clustering_Factor is irrelevant for Unique and Primary Key Indexes.
- High Cardinality or Low Cardinality play important roles with an exception for Unique & Primary Key.
Be it a Unique Index / Primary Key Index or any other Index, column ordering has to be basis your application queries.
One interesting fact on TABLE_CACHED_BLOCKS : While testing this, I actually created a table with 100k rows. The number of blocks in the table were around 469 and the table block were referenced by around 230 previous index entries. Therefore, I modified this preference to 230 and regathered the statistics. It didn’t work. The same table creation script with 10k rows, everything worked as expected. I am investigating this and will update the blog, once I have something to share.