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.
Imagine, we communicate with our Database in Business Language and Database responding with a result that matches the Business criteria. Natural Language Processing (NLP) a branch of Artificial Intelligence has this capability of generating a matching result based on the question asked.
SELECT AI, released early this year on Autonomous Database, allow business users to interact with the database and then convert it into Oracle SQL. Under the hood, it leverages generative AI with Large Language Models (LLM) to covert the user input into SQL.
In this blog, I will not write much about what it is, how to configure it and so on…I would request reading Oracle Autonomous Database Documentation that goes through these details (like, Creating Credentials, Creating Profiles for various AI providers etc.) and is regularly updated.
In this blog, I will walk you through few Interesting examples or analysis on Indian Premier League (IPL). For my Indian Readers, IPL is not something that will need an introduction. Most of the Indians are Cricket Fans. In fact, all Cricket playing nation may know about IPL. If not, then a brief introduction is here.
Before I get into some analysis, I thought of sharing something important i.e.How is SELECT AI able to generate a meaningful query, especially if the column names are a bit cryptic ? Remember, LLM’s are models that we need to train and to train these models, we need to provide it all the relevant details. Let us go through two examples.
In this case, I have a Table TEST_VIVEK with two columns X and Y. X has the Customer Names and Y contains Company Names.
CREATE TABLE TEST_VIVEK ( X VARCHAR2(32), Y VARCHAR2(64));
INSERT INTO TEST_VIVEK VALUES(‘VIVEK’,’ABC CORPORATION’); INSERT INTO TEST_VIVEK VALUES(‘TOM’,’XYZ INC.’); INSERT INTO TEST_VIVEK VALUES(‘DICK’,’LLM PRIVATE LIMITED’); INSERT INTO TEST_VIVEK VALUES(‘HARRY’,’AI INDIA’); COMMIT;
Now, How do I provide meaningful names to these columns (X & Y)? One way is to create a View.
CREATE OR REPLACE VIEW TEST_VIVEK_VW AS SELECT X CUSTOMER, Y COMPANY FROM TEST_VIVEK;
While creating a profile, I provide TEST_VIVEK_VW as the Object to be used. Let us ask few questions (using SQL Developer).
And the Output
The other method is using an in-built database feature COMMENTS. These basically allows you to provide meaningful description and notes about TABLE(S) and COLUMNS. Marty Gubar has a nice blog with an explanation on how to make use of COMMENTS to ensure LLM’s come-up with right query. See this blog.
Time to get into some Analysis on Indian Premier League. I downloaded the data from one of the cricket websites. The files were in CSV format. I loaded it into my ADB using Data Transform. There are two tables IPL_MATCHES and IPL_DETAILS. IPL_MATCHES is a kind of Primary table with 1 Row per match and IPL_DETAILS is a Child Table with Ball-by-Ball Data. Both these tables has a column MATCH_ID. I created a Primary Key on this column on IPL_MATCHES Table and then a Foreign Key on IPL_DETAILS.
The COMMENTS for both the tables and all the relevant columns:
comment on table IPL_MATCHES is ‘contains IPL Season, City, date, player of the match, stadium, winner, target runs, target overs, match result’; comment on column IPL_MATCHES.MATCH_ID is ‘Match ID. Join this column to other Tables’; comment on column IPL_MATCHES.IPL_SEASON is ‘IPL Season. Each IPL season will have multiple matches’; comment on column IPL_MATCHES.CITY is ‘City where the IPL match was played’; comment on column IPL_MATCHES.MATCH_DATE is ‘Date when the IPL Match was played’; comment on column IPL_MATCHES.MATCH_TYPE is ‘Type of the IPL Match’; comment on column IPL_MATCHES.PLAYER_OF_THE_MATCH is ‘Name of the Player who won the player of the Match award’; comment on column IPL_MATCHES.VENUE is ‘Stadium where the IPL match was played’; comment on column IPL_MATCHES.TEAM1 is ‘Team 1 playing the ipl match’; comment on column IPL_MATCHES.TEAM2 is ‘Team 2 playing the ipl match’; comment on column IPL_MATCHES.TOSS_WINNER is ‘Team that won the Toss’; comment on column IPL_MATCHES.TOSS_DECISION is ‘Decision taken by the Team winning the toss’; comment on column IPL_MATCHES.WINNER is ‘Winner of the IPL Match’; comment on column IPL_MATCHES.RESULT is ‘Result of the IPL Match’; comment on column IPL_MATCHES.RESULT_MARGIN is ‘Margin by which a team won the match’; comment on column IPL_MATCHES.TARGET_RUNS is ‘Target Runs or Runs required to win’; comment on column IPL_MATCHES.TARGET_OVERS is ‘Target Overs’; comment on column IPL_MATCHES.SUPER_OVER is ‘super over’; comment on column IPL_MATCHES.METHOD is ‘Win Type’; comment on column IPL_MATCHES.UMPIRE1 is ‘Umpire 1’; comment on column IPL_MATCHES.UMPIRE2 is ‘Umpire 2’;
comment on table IPL_DETAILS is ‘Contains ball by ball Detail of all the ipl matches, Batsman Name, Bowler Name, runs scored, player dismissed’; comment on column IPL_DETAILS.MATCH_ID is ‘Match ID. Join this column to other Tables’; comment on column IPL_DETAILS.INNING is ‘Inning Number’; comment on column IPL_DETAILS.BATTING_TEAM is ‘Team Batting’; comment on column IPL_DETAILS.BOWLING_TEAM is ‘Team Bowling’; comment on column IPL_DETAILS.OVER_NUMBER is ‘Over Number’; comment on column IPL_DETAILS.BALL_NUMBER is ‘Ball Number’; comment on column IPL_DETAILS.BATTER is ‘Batsman’; comment on column IPL_DETAILS.BOWLER is ‘Bowler’; comment on column IPL_DETAILS.NON_STRIKER is ‘Non Striker’; comment on column IPL_DETAILS.RUNS_OFF_BAT is ‘Runs scored’; comment on column IPL_DETAILS.EXTRA_RUNS is ‘Extra Runs’; comment on column IPL_DETAILS.TOTAL_RUNS is ‘Total Runs on a Ball’; comment on column IPL_DETAILS.EXTRAS_TYPE is ‘Extras’; comment on column IPL_DETAILS.IS_WICKET is ‘Wicket Taken. 1 is Yes and 0 is No’; comment on column IPL_DETAILS.PLAYER_DISMISSED is ‘Batsman Dismissed, Player Dismissed’; comment on column IPL_DETAILS.DISMISSAL_KIND is ‘Dismissal Type’; comment on column IPL_DETAILS.FIELDER is ‘Fielder’;
Object List in the CREATE_PROFILE API has the two Tables IPL_MATCHES and IPL_DETAILS. One additional parameter that I have is COMMENTS:TRUE. Once done, we can go through some interesting analysis.
begin dbms_cloud_ai.set_profile( profile_name => ‘VIVEK_AI_PROFILE’ ); end; /
Indian Premier League started in 2008 and the latest League concluded on 26th May 2024.
First Ever Match was played on 18th April 2008. Latest Season of IPL was 2024 and the final match was played between Kolkata Night Riders and Rajasthan Royals on 26th May 2024.
Next, let us get the count of matches played in each of the IPL Season’s. This is typically a count of matches group by Season.
Number of matches in 2011, 2012 and 2013 were more than the other previous seasons. Then these count dropped before going up in 2022. Any specific reasons for this? Let’s see.
We can now see that the number of Teams in 2011, 2012 and 2013 were more than previous seasons. The format of this league is that each team has to play against each other. Hence, as a new team is added to the league, the number of matches go up. In 2014, number of teams reduced back to 8. Season 2022 onwards, we again see an increase in this number.
Next, let’s see the top 5 Batsman with most runs. This will be computed from IPL_DETAILS table as the data here is ball by ball. This means, we have to take a sum of run scored by a batsman on each ball. Now. if you again scroll up and see the column names in IPL_DETAILS, we do not have a column called BATSMAN, nor we have a column called Runs Scored. We do have BATTER and RUNS_OFF_BAT. We passed the relevant information via COMMENTS.
The Query SELECT AI came up with:
As per the analysis, Virat Kohli (V Kohli) is the top batsman with highest number of runs. Next, let’s check top 5 Bowler’s having dominated and dismissed Virat Kohli the most.
Seasonwise IPL Winners and Player of the Final Match.
Let’s ask a bit complex question. I want to calculate the Batting Strike Rate. This is basically computed as the Sum of Runs Scored divided by the Number of Balls faced. The numbers are per 100 ball i.e.if the strike rate is 150, this means, at an average a Batsman Scores 150 runs in 100 Balls. Now, the way our data is stored, we have Runs_Off_Bat and Ball_Number column. This means, I will have to take a sum of Runs_off_Bat, divide it by Count of Ball_Number and this value will then have to be multiplied by 100. To be honest, I had to play around with this question to get it right and after 3 or 4 attempts, I was able to get it.
In a game of Cricket, Strike Rate doesn’t change every few minutes. It only changes during or after a match. Therefore, the next task that I would want to try is creating a Materialized View with a pre-computed Strike Rate, adding it to the object list and then running a simple query to get the Strike Rate. Once I am done, will post the results as an update to this blog. We can play around with this data and perform some more analysis. I actually did many more, but to make this blog short thought of publishing only few of them. I actually wanted to demonstrate the SELECT AI capabilities and how easily it was able to get us the required details. Hope you enjoy this.
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.
Autonomous Data Warehouse (ADW) was announced in March 2018 and then Autonomous Transaction Processing (ATP) is the latest addition to the Autonomous Database family. ATP is for Mixed Workload and therefore provides 4 Service Names. These are :
HIGH :Highest level of CPU and IO Resources. Concurrency with this Service Name depends upon the number of OCPUs and will scale accordingly.
MEDIUM :Lowest level of CPU and IO Resources. Concurrency depends on the number of CPU’s and will scale accordingly.
LOW :Least amount of CPU and IO Resources. Again, Concurrency depends on the number of CPU’s and the Scaling is directly proportional to the number of OCPU’s.
ADW provides these three Service Names as well. However, unlike ADW (see my previous blog), the definition of these Services are different. In ADW, HIGH provides highest level of Resources but less concurrency, whereas, in ATP, HIGH do provide highest level of Resources but also provides Highest level of Concurrency. Since ATP is designed for Mixed Workload, it provides an additional Service Name PARALLEL. It provides high level of resources but lowest concurrency and is useful for Batch Processes or Heavy Reporting.
Now, many of the technical folks have raised this question on “Why do we need Both? Can’t we use ATP to take care of Data Warehouse kind of a load?”. ATP very well does that by providing an option to run Mixed Workload. However, there are many implementations that are purely Data Warehouse. The problem is that the amount of data that we generate, Warehouse Databases have grown massive in size supporting large amount of complex queries involving many nested joins and expecting sub-second response time. Further, these Databases house data from many different sources. Optimization Strategy for an OLTP and Data Warehouses are completely different. Therefore, an OLTP kind of a setup cannot (in most of the cases) provide the response time needed for DWH Queries.
Row Store v/s Columnar Store
Now, coming back to ATP v/s ADW argument. In an ADW, the data loaded is by default compressed to Oracle Hybrid Columnar Compression, whereas ATP uses a Row Store Format. What this means is that in an ADW, the data stored at disk is in a Columnar format, which is a proven format for speeding up Analytics kind of a Workload where we query few columns. All Data Warehouse Implementations, like Redshift, Google Big Query etc. store data in Columnar format. On the other hand, ATP stores the data in Row format which is ideal for OLTP that demands Single or few row(s) updates or deletes or queries small number of rows.
Since the data in ADW is stored Compressed in Columnar format, it allows scanning of Billions of Rows per CPU cycle. The data for a particular column is stored contiguously making it possible to scan through large number of rows in a single CPU cycles. This technique is called SIMD (Single Instruction Multiple Dataset) processing. To meet a sub-second response time, this technique is plays a significant role.
Such large scans (scanning billion of rows per second) is not possible with Row Store and one of the reasons for this can be found in one of my blog published in 2015 which is still very much relevant. Skipping of Columns consume CPU Cycles which is demonstrated in the blog post that can be found here.
Through this short blog, I wanted to clear the confusion around ADW and ATP. Both have their own use cases and thus are optimized to manage different workloads. Both of these Autonomous Databases have some exciting features. Stay tuned for more on these.
I recently presented a Full Day Tech Event on Autonomous databases (ADB) for the North India Chapter of All India Oracle User Group. Since most of the attendees were Database Administrators, it was important to cover technical aspects of ADB. While Oracle doesn’t publicize the Internals of ADB, I spoke about some of my experiences based on many of POC’s.
Many DBA’s still assume that 18c Database is Autonomous? Therefore it is Important to know What is Autonomous Database and What it is not? First thing to note is that 18c is not Autonomous. 18c is just like any other Oracle Database. Autonomous uses the features of Oracle Database 18c, plus 12c, plus 11g, plus 10g and so on. Autonomous Database ensures that all these features work together. So, 18c is one of the Building Blocks of 18c. Autonomous uses all the features that Oracle Developed for almost 2 decades. The other important underlying technology is Oracle Exadata, which is the fastest Oracle Database Machine with redundancy built-in at each layer. Again, Oracle had been working on many features that makes this machine a powerful database machine, in terms of Performance, Reliability and Scalability. The Journey had been long from Smart Scans to Direct-to-wire protocol to Smart Fusion Block Transfer and the enhancements continue. One important thing to note is – Customers do have an option to move to 18c on Exadata within their own Data Centre. Then does it mean, this combination is Autonomous? The answer is NO. The two components are critical, but what makes it Autonomous is the Cloud Automation with Machine Learning.
During the session, I covered the Performance features and the way Oracle controls resource utilization. The topic that came up was Oracle Database Resource Managers. Database Administrators were more inquisitive to know more about this and how it is been implemented in ADB.
Resource Manager-
Resource Managers or Consumer Groups or Service names are the integral part of Autonomous Databases as they manage the workload based on the performance characteristics of the Application. For example, in an Autonomous Data Warehouse there are 3 pre-created Service Names. These are –
HIGH – Highest Resources and Lowest Concurrency. Maximum Queries that can run are 3.
MEDIUM – Less Resources and High Concurrency. Maximum Queries would depend upon the number of OCPU’s.
LOW – Least resources and Highest Concurrency. Maximum Queries – twice the number of OCPU’s and therefore, would depend upon the number of OCPU’s.
Autonomous Transaction Processing (ATP) introduced a new Service – PARALLEL. In the context of ATP, the definition for HIGH, MEDIUM and LOW is different that in ADW. I will cover these in other blog post.
Now, the game becomes more interesting with HIGH and MEDIUM Consumer Groups. While the maximum number of Queries that can run from HIGH is 3 but with concurrency i.e. with concurrent Queries running from MEDIUM and LOW, this number can be less than 3. Same is the case with MEDIUM. With MEDIUM, the maximum numbers would depend on the number of OCPU’s, but actual concurrent queries that can run below the maximum allowed would depend upon the queries running from other consumer groups. For LOW, it is twice the number of OCPU’s and is irrespective of number of queries running from other consumer groups. All these are managed efficiently by pre-configured Resource Managers. Resource Manager, as the name implies, manages the resources. When the load on the system is light, there is very little need for managing the resources. However, as the load increases and system becomes more busy, managing the resources become more important. One of the Primary Goal of Autonomous Database is to ensure consistent performance (always). Therefore, these restrictions. However, as mentioned earlier, other than HIGH, the maximum for other consumer groups depends upon the number of OCPU’s. So, if you have a highly concurrent environment or during peak load with high concurrency, you can scale up more OCPU’s and scale down as well. By the way, Scaling OCPU’s UP and DOWN is Online and is irrespective of the Storage. Let me digress for a moment here. In case of Autonomous Database, if you need additional Storage, you can add it Online and this increase does not mandate adding OCPU’s. Same with the OCPU’s, you don’t need to add Storage. Both are independent. This is not the case with the competition. For example, with AWS Redshift or Microsoft SQL Data Warehouse, since these technologies work on Shared Nothing Architecture, additional Storage means adding more OCPU’s as well. What this means is – Even if you have good amount of CPU resources available and need is to add only the Storage, you will have to add a compute node as well. A storage without a compute is like a DEAD duck. It does nothing on disk unless connected to a Compute Mode.
Coming back to the Resource Manager. As mentioned, resource manager is automatically created and the Application owner has to only make sure that the application queries or the reports are connected to one of the consumer groups based on workload patterns or application requirements.
Why Resource Manager (RM)?
As mentioned earlier, the primary goal of a RM is to Manage System Resources. If resources were unlimited, there would be no need to manage them. Without these, all database connections are considered equal and under heavy load all sessions are impacted equally. There are various components of RM, like Consumer Groups – RM allocate resources to Consumer Groups and not to Sessions, Plan Directives – Resource allocations are actually assigned to Plan Directives and a Consumer group is then assigned a Directive, then finally Resource Plan – a collection of Directives that determines how the resources are to be allocated and managed. Resource Plan is set at the Database or System Level using RESOURCE_MANAGER_PLAN.
In terms of mapping rules, all user sessions (except SYS and SYSTEM) are mapped to a Consumer Group. By default, these are mapped to OTHER_GROUPS, which is a mandatory consumer group. There are 2 types of Session Attributes – Login Attributes and Runtime Attributes. Runtime Attributes is assigned at Runtime and can ne changed anytime during the life cycle of the session. This is basically decided by the application. Login Attributes is tied to the connection and cannot be changed during the lifecycle.
Resource Manager, in ADB uses Login Attribute. Therefore, the application connection is to a particular Service Name has to be pre-decided based on the application requirement.
The maximum number of Queries that can be executed using MEDIUM and LOW Service Names scales linearly. As per Oracle Documentation, for a 16 OCPU Machine, the maximum with MEDIUM is 20 and LOW is 32 (twice the number of OCPU’s). As you scale-up, say to 32 OCPU’s, the maximum for these will Scale as well to 40 for MEDIUM and 64 for LOW.
At present, will restrict this blog post to Resource Manager. Will write on something interesting soon.
#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 2017 was a great hit with around 800+ attendees. The Organizers did a great job in managing the show effectively. As mentioned in my earlier post, this year I presented sessions on “Indexing : Facts and Myth” on 8th December and “Autonomous Database” on 9th December. Apart from these, I also hosted a “Fireside Chat on Database/Application Performance” along with other speakers including Tirthankar Lahiri, VP for Oracle In-Memory Technologies Product Management. Andrew Holdsworth, VP for Oracle Real World Performance joined us as well. Together, we could address some of the queries raised by the participants of this Fire side chat. We had around 100+ attendees for the fire side chat.
While there is always a demand for a technical session and I rightly guessed a descent crowd for my Indexing Session. However, I was surprised to see a full house (around 200+) attendance for the session on Autonomous Database. This clearly means that the Technical Community wanted to know more about this interesting new technology, which is world’s first Autonomous Database. The session was very interactive and I tried responding to many of the queries, including the top most concern on the DBA Role.
My presentation kicked off with a Q&A on some of the Automated Features Oracle introduced since Oracle 9i. In my opinion, Automatic Segment Space Management (ASSM) introduced in Oracle 9i was the very first self-tuning feature as it dynamically adapts to (re)configuration of RAC Nodes without any changes required. This shows that Oracle’s journey to Autonomous Database started more than a Decade ago. Remember, Oracle 9i was released in 2001. Since then, Oracle introduced many features that reduced the burden off the DBA’s. All these features had one thing in common – AUTOMATIC. Automation is obviously one of the key drivers when it comes to Autonomous Database.
During the session, I also discussed about the difference between Automatic and Autonomous. Many organizations has introduced some or the other Automation to reduce or eliminate some of the mundane tasks. Certain amount of Automation can be done, however, to make a critical database entirely Autonomous, Full end-to-end Automation that too Automation of Complex tasks is required.
The underlying database for Autonomous DB is Oracle 18c. However, many were confused that Oracle 18c is an Autonomous Database. Therefore, it is important to know that Oracle 18c alone is not an Autonomous Database.
So, Autonomous Database is made up of multiple component. The core underlying database version is 18c, which is integrated with Oracle Cloud and then uses some specialized tooling and automation that Oracle has created on cloud and some of them developed over the years. Machine Learning algorithm is used at every layer to make it more proactive.
Exadata has been a proven innovation when it comes to running an Oracle Database. Autonomous Database runs on Exadata, which further provides a healthy, highly available and best performance database for any kind of workload.
I can write more about Autonomous Database, but would want to hold for some other part. Thought of writing on this, as it generated a huge interest during Sangam and this excited me a lot. 🙂
Would be happy to respond to any of the queries related to Autonomous Database.
