Parquet file size of an Oracle table

Hello, lately I haven’t contributed to my site because I am working on projects with Azure, Azure databricks.

Anyways, I am analyzing the size of an on premise oracle table vs the copy of it on the azure blob storage.

I ingested an oracle (v19c) table using Azure ADF as a parquet file on Azure Blob storage. I found that the file compressed over 80% in size. I didn’t filter any rows.

I have not seen any articles to validate that it will compress an oracle table over 80%.

What have you seen so far? Add your feedback

Thanks

Read Full Post »

2014 in review

The WordPress.com stats helper monkeys prepared a 2014 annual report for this blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 11,000 times in 2014. If it were a concert at Sydney Opera House, it would take about 4 sold-out performances for that many people to see it.

Click here to see the complete report.

Read Full Post »

Oracle 11g Memory Advisor

One of the issues that Database DBA face is oracle database memory set correctly or do I need to increase it.

Oracle 11g has cool solution.

Querying from table: v$memory_target_advice , tells if increasing memory size would provide any advantage, if so how much should you increase.

Here is the example:

Review the results in the screen shot. Currently database has a memory_target of 2048 MB (First line shows this).It can be compared with DB initialization parameter MEMORY_TARGET.

Line 2 shows that increasing memory size to 2560 MB (which is 25% more) did not add further benefit which can be seen in the column : EST_DB_TIME_FACTOR value 1. However, in line 3 if Memory size is 3072 (i.e. 50% more ) estimated time factor is 0.9602 that means 1 sec process can be done in 0.9602 sec this is an improvement.

Even if you increase memory by 75%, there is no additional benefit, but there is very minor benefit when memory is  double.

In this scenario increasing memory by 50% is adding benefit for the database.

Read Full Post »

Oracle 11g Flexible Architecture

Find attached Oracle 11g recommended directory structure.

Read Full Post »

LISTAGG vs Stragg vs WM_CONCAT

One of the Oracle Database 11g features is to provide concatenated data for each group called LISTAGG. In prior versions we used different ways to achieve same results. Some of those are STRAGG and 10g introduced undocumented function WM_CONCAT.

Below I illustrated the differences in performance using these different function. I used same huge data set and same environment.

First look at ListAgg Vs WM_Concat

DEV> select id,listagg(paramlist,’;’) within group (order by paramlist)  as agg_data

2  from KP_AGG_TEST

3  group by id;

287485 rows selected.

Elapsed: 00:00:43.59

Execution Plan

———————————————————

Plan hash value: 2522236266

—————————————————————————————–

Id  | Operation          | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

—————————————————————————————–

0 | SELECT STATEMENT   |             |   287K|  7860K|       |  5030   (2)| 00:01:01 |

1 |  SORT GROUP BY     |             |   287K|  7860K|    30M|  5030   (2)| 00:01:01 |

2 |   TABLE ACCESS FULL| KP_AGG_TEST |   876K|    23M|       |  1137   (2)| 00:00:14 |

—————————————————————————————–

Statistics

———————————————————

67  recursive calls

20  db block gets

4077  consistent gets

8320  physical reads

0  redo size

11625136  bytes sent via SQL*Net to client

134407  bytes received via SQL*Net from client

19167  SQL*Net roundtrips to/from client

0  sorts (memory)

1  sorts (disk)

    287485  rows processed

DEV> select id,wm_concat(paramlist) from KP_AGG_TEST

2  group by id;

287485 rows selected.

Elapsed: 00:29:54.60

Execution Plan

———————————————————-

Plan hash value: 2522236266

——————————————————————————————

| Id  | Operation          | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

——————————————————————————————

|   0 | SELECT STATEMENT   |             |   287K|  7860K|       |  5030   (2)| 00:01:01 |

|   1 |  SORT GROUP BY     |             |   287K|  7860K|    30M|  5030   (2)| 00:01:01 |

|   2 |   TABLE ACCESS FULL| KP_AGG_TEST |   876K|    23M|       |  1137   (2)| 00:00:14 |

——————————————————————————————

Statistics

———————————————————-

51  recursive calls

3449842  db block gets

323291  consistent gets

6328  physical reads

0  redo size

103733023  bytes sent via SQL*Net to client

41398092  bytes received via SQL*Net from client

862457  SQL*Net roundtrips to/from client

0  sorts (memory)

1  sorts (disk)

287485  rows processed

As you can see, WM_CONCAT took longer than List Agg.

Now let’s look at LISTAGG vs STRAGG

Query 1: With below ListAgg query, aggregated data is returned in the order.

DEV> select id,listagg(paramlist,’;’) within group (order by paramlist)  as agg_data

  2  from KP_AGG_TEST

  3  GROUP BY ID;

287485 rows selected.

Elapsed: 00:00:44.95

Execution Plan

———————————————————-

Plan hash value: 2522236266

——————————————————————————————

| Id  | Operation          | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

——————————————————————————————

|   0 | SELECT STATEMENT   |             |   287K|  7860K|       |  5030   (2)| 00:01:01 |

|   1 |  SORT GROUP BY     |             |   287K|  7860K|    30M|  5030   (2)| 00:01:01 |

|   2 |   TABLE ACCESS FULL| KP_AGG_TEST |   876K|    23M|       |  1137   (2)| 00:00:14 |

——————————————————————————————

Statistics

———————————————————-

         67  recursive calls

         30  db block gets

       4077  consistent gets

      13152  physical reads

          0  redo size

   11644303  bytes sent via SQL*Net to client

     134406  bytes received via SQL*Net from client

      19167  SQL*Net roundtrips to/from client

          0  sorts (memory)

          1  sorts (disk)

     287485  rows processed

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Query 2: Note that below STRAGG Query doesn’t return aggregated data in the order. Per returned data, this is little diff from listagg

 Dev> select id, stragg(paramlist) from kp_agg_test

  2  group by id;

287485 rows selected.

Elapsed: 00:02:37.82

Execution Plan

———————————————————-

Plan hash value: 2522236266

——————————————————————————————

| Id  | Operation          | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

——————————————————————————————

|   0 | SELECT STATEMENT   |             |   287K|  7860K|       |  5030   (2)| 00:01:01 |

|   1 |  SORT GROUP BY     |             |   287K|  7860K|    30M|  5030   (2)| 00:01:01 |

|   2 |   TABLE ACCESS FULL| KP_AGG_TEST |   876K|    23M|       |  1137   (2)| 00:00:14 |

——————————————————————————————

Statistics

———————————————————-

     287538  recursive calls

         22  db block gets

    1786494  consistent gets

       6341  physical reads

          0  redo size

   18532148  bytes sent via SQL*Net to client

     134406  bytes received via SQL*Net from client

      19167  SQL*Net roundtrips to/from client

          0  sorts (memory)

          1  sorts (disk)

     287485  rows processed

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Query 3:Below query returns aggregated data in the order, same as listagg.

 

10-NOV-11 LV_DEV@GALVD501> select id, stragg_order_by_item(paramlist) from kp_agg_test

  2  group by id;

287485 rows selected.

Elapsed: 00:02:46.53

Execution Plan

———————————————————-

Plan hash value:2522236266

——————————————————————————————

| Id  | Operation          | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

——————————————————————————————

|   0 | SELECT STATEMENT   |             |   287K|  7860K|       |  5030   (2)| 00:01:01 |

|   1 |  SORT GROUP BY     |             |   287K|  7860K|    30M|  5030   (2)| 00:01:01 |

|   2 |   TABLE ACCESS FULL| KP_AGG_TEST |   876K|    23M|       |  1137   (2)| 00:00:14 |

——————————————————————————————

Statistics

———————————————————-

     287539  recursive calls

         30  db block gets

    1786488  consistent gets

       9938  physical reads

          0  redo size

   11904394  bytes sent via SQL*Net to client

     134406  bytes received via SQL*Net from client

      19167  SQL*Net roundtrips to/from client

     287485  sorts (memory)

          1  sorts (disk)

     287485  rows processed

 

Results Comparision

 

Query 1: Elapsed time is less compare to 2 and 3 below

Query 2:

–   Elapsed time is high

–   Recursive calls, consistent gets high

–   CPU/Cost is same as listagg.

–   High Bytes sent via SQL*Net

– Result set was not in the order as Query 1

 

Query 3: 

 

–   Elapsed time is high

–   Recursive calls, consistent gets high

–   CPU/Cost is same as listagg.

–   High Bytes sent via SQL*Net

– Result set was same as Query 1

 

Conclusion:

Avoid using WM_CONCAT, which is moreover not documented.

If you are using stragg, you can switch them to LISTAGG. It is difficult to change lot of queries that are already present and at atleast  start using LISTAGG over STRAGG. LISTAGG provides options like ordering, customized delimiter.

 

 

 

Read Full Post »