Feed aggregator

Question - increase size of number column

Tom Kyte - Mon, 2024-03-25 12:21
We just hit 2.1 billion row count on a table with primary key INT. This is the worse thing to happen :( Any one know if we can do alter without requiring space on the DB for the entire table?
Categories: DBA Blogs

SQL Performance differences with STATS gather

Tom Kyte - Mon, 2024-03-25 12:21
We have seen in many situations in our environment where a SQL was running badly but the plan for the query has not changed. When we gather stats for the associated table.we see that same query performs significantly better. However there is no change in PHV of the execution plan. My Question is if the PHV is staying same then that means execution plan remains the same then why is the performance varying.Are table statistics used by the optimizer even after plan is generated?
Categories: DBA Blogs

AUD$ EXP/TRUNCATE/IMP (feasibility)

Tom Kyte - Mon, 2024-03-25 12:21
We are going to MOVE the TBS of AUD$ table in PROD. Purpose: AUD$ table is totally fragmented and the CLEANUP / PURGE runs very slow - even with max 1.000.000 batch size but as per test in our test environment we had some issues regarding using API (DBMS_AUDIT_MGMT) to MOVE TBS on AUD$. And we are using STANDARD AUDIT TRAIL! SELECT * FROM dba_audit_mgmt_config_params where audit_trail ='STANDARD AUDIT TRAIL' and parameter_name='DB AUDIT TABLESPACE'; DB AUDIT TABLESPACE CLARITYAUDIT <b>STANDARD AUDIT TRAIL</b> But the MOVE worked now after we got some action plan from oracle support to fix the issue in TEST env. so that the MOVE went through via API. Now we are planning to do the TBS MOVE of AUD in PROD (<b>online</b>!). But we need to have a fallback plan, in case the MOVE hangs, or/and the data in AUD table get inconsistence. so the fallback plan is: 1) EXP the data in a downtime (disable audit trail) and keep the dump file on the server. but with parameter "DATA_ONLY" - as metadata (table DDL) would still be there. 2) run the MOVE TBS on PROD via API (DMBS). 3) if it goes through and AUD$ is accessible and purgeable, we are good - if not, we need to truncate the data in AUD$ and IMP the SAVED data (as per EXP/dump file) - again with parameter "DATA_ONLY" So i hope thats clear enough. The question is now if step 3 would work or not - we are also planning the to test the step 3 in our TEST env. but we are concerned , If this action plan (especially step 3) could impact the PROD - in case we needed to go for the fallback plan (!?). We would also appreciate any other action plan/ option to save and recover data in AUD$ , in the above scenario. Thank you! Ali
Categories: DBA Blogs

How does Oracle Database provide the user$.user#

Tom Kyte - Mon, 2024-03-25 12:21
Hi Toms, eventually, after many years, I came across e question I never realized. Indeed I have to face a customer, who uses the user$.user# for application purposes. Will say after creating a user, that application stores the user# within application tables columns, say USR_ID, which, subsequently leads to the need that user$.user# has to match the USR_ID. In consequence, if you have to migrate that application via epxdp / impdp (we have to, as we migrate from Solaris to Linux) these IDs won't match anymore as the users on the new database are created with different user$.user#. You do not have to tell me that THAT application needs "some redesign"... However, I have some questions regarding user$.user#. As far as I have seen / read, when creating a new user using the usual "create user" statement the new users user# is provided by oracle rdbms as the user# of "_NEXT_USER". _NEXT_USERs user# serves as a high water mark, even when dropping the user again _NEXT_USERs user# won't decrease (looks like an Oracle maintained sequence is used), so creating and dropping users leades to unused ranges of numbers in user$.user#. Questions: - Which sequence does provide the number of _NEXT_USER? - Is there any way to reset it? - or is there any way to influence the user$.user# or the number that is provided by rdbms to be stored as user$.user#? => I assume this may result in corruption but perhaps there is a way. Thanks and best regards - dietmar
Categories: DBA Blogs

LLM Structured Output for Function Calling with Ollama

Andrejus Baranovski - Mon, 2024-03-25 09:40
I explain how function calling works with LLM. This is often confused concept, LLM doesn't call a function - LLM retuns JSON response with values to be used for function call from your environment. In this example I'm using Sparrow agent, to call a function. 

 

Der Aufschlag im #Pickleball

The Oracle Instructor - Sun, 2024-03-24 12:24

Das Beste vorweg: Der Aufschlag im Pickleball ist leicht zu lernen und auszuführen. Anders als etwa im Tennis oder Tischtennis, wo man typischerweise viel Zeit mit der Übung des Aufschlags verbringt, um konkurrenzfähig zu sein. Die Regeln erschweren es zudem beträchtlich, dass der Aufschlag zur spielentscheidenden „Waffe“ werden kann. Asse oder zwingender Vorteil nach dem Aufschlag sind daher ziemlich selten.

Regeln

Die Regeln zur Positionierung gelten sowohl für den Volley-Serve als auch für den Drop-Serve:

Zum Zeitpunkt, wenn der Ball beim Aufschlag auf den Schläger trifft, müssen die Füße des aufschlagenden Spielers hinter der Grundlinie und innerhalb verlängerten Linien der jeweiligen Platzhälfte sein:

Der Oberkörper des Spielers und der Ball dürfen sich dabei innerhalb des Spielfelds befinden. Außerdem darf der Spieler das Spielfeld betreten, unmittelbar nachdem der Ball den Schläger verlassen hat.

Vorher aber nicht:

Schon das Berühren der Grundlinie mit der Fußspitze beim Aufschlag ist ein Fehler.

Man darf auch nicht beliebig weit außen stehen:

Der linke Fuß ist hier außerhalb der verlängerten Linien der linken Platzhälfte, weshalb dieser Aufschlag nicht regelgerecht wäre.

Wer schlägt wann wohin auf, und was ist mit der NVZ?

Es muss jeweils das diagonal gegenüberliegende Feld getroffen werden, wobei der Ball nicht in der NVZ landen darf. Die Linien gehören dabei zur NVZ: Ein Ball auf die hintere Linie der NVZ ist ein Fehler. Genauso gehören die Linien zum Aufschlagfeld: Ein Ball auf die Grundlinie oder ein Ball auf die Außenlinie ist also kein Fehler. Wird der Punkt gewonnen, wechselt der Aufschläger mit seinem Partner die Seite. Verliert der erste Aufschläger die Rally, macht sein Partner von der Seite weiter, wo er grad steht. Verliert auch der zweite Aufschläger die Rally, wechselt der Aufschlag auf das andere Team. Die Zählweise und die jeweilige Positionierung der Spieler hab ich in diesem Artikel behandelt.

Volley-Serve

Das ist derzeit der beliebteste Aufschlag und ursprünglich auch der einzig erlaubte Aufschlag. Der Ball wird dabei aus der Hand aufgeschlagen.

Der Schlägerkopf muss dabei eine Bewegung von unten nach oben ausführen, wie in 4-3 zu sehen.

Der Schlägerkopf darf sich zum Zeitpunkt des Auftreffen des Balls nicht über dem Handgelenk befinden (4-1 zeigt die korrekte Ausführung, 4-2 ist ein häufig zu beobachtender Fehler).

Außerdem muss der Ball unterhalb der Taille des Aufschlägers getroffen werden, wie in 4-3 zu sehen.

Das obige Bild stammt aus dem Official Rulebook der USAP.

Alle Regeln zum Volley-Serve sollen im Grunde sicherstellen, dass dieser Aufschlag eben nicht zum spielentscheidenden Vorteil wird. Im folgenden Clip sehen wir einen Aufschlag von Anna Leigh Waters, der momentan besten Spielerin der Welt:

Diese Art des Aufschlags ist bei den Pros am häufigsten zu sehen: Volley-Serve, Top-Spin, tief ins Feld gespielt. Wir sehen aber auch, dass ihre Gegenspielerin den Aufschlag ohne große Mühe returniert. Asse oder direkt aus dem Aufschlag resultierender Punktgewinn sind im Pickleball ziemlich selten. Ganz im Gegensatz etwa zu Tennis und Tischtennis.

Drop-Serve

Diese Art des Aufschlags ist erst seit 2021 erlaubt. Abgesehen von den oben beschriebenen Regeln zur Positionierung der Füße gibt es beim Drop-Serve nur eine weitere Regel: Der Ball muss aus der Hand fallengelassen werden. Hochwerfen oder nach unten Stoßen/Werfen des Balls ist nicht erlaubt. Insbesondere darf der Ball auf beliebige Art geschlagen werden. Das macht diesen Aufschlag besonders Einsteigerfreundlich, weil man kaum die Regeln verletzen kann.

Ich habe dem Drop-Serve bereits diesen Artikel gewidmet.

Abgesehen von den Regeln – wie sollte man aufschlagen?

Es gibt hier zwei grundsätzliche Herangehensweisen:

Die einen sagen, weil man mit dem Aufschlag ohnehin selten einen direkten Punkt macht, sollte man ihn nur möglichst sicher in das hintere Drittel des Felds reinspielen:

Ins hintere Drittel, weil die Rückschläger sonst zu leicht einen starken Return spielen und die Aufschläger hinten halten können. Mit der gelben Zielzone ist es unwahrscheinlich, dass der Aufschlag aus geht.

Die anderen (zu denen ich auch gehöre) sagen: Mit dem Aufschlag kann man ruhig etwas Risiko eingehen. Schließlich kann das Return-Team keinen Punkt machen. Es ist okay, wenn von 10 Aufschlägen 2 ausgehen und die übrigen 8 es den Rückschlägern schwer machen, uns hinten zu halten. Der eine oder andere direkte Punkt sollte auch dabei sein. Darum sehen meine Zielzonen so aus:

Die meisten Aufschläge gehen Richtung Zielzone 1, ab und an mal einer nach 2 und 3. Die roten Zonen sind deutlich näher an den Linien als die gelbe, was natürlich die Gefahr eines Ausballs erhöht.

Im Allgemeinen kann man zum Aufschlag im Pickleball sagen:

Länge ist wichtiger als Härte oder Spin. Ein entspannt in hohem Bogen ins hintere Drittel des Aufschlagfelds gelobbter Ball macht dem Rückschläger mehr Probleme als ein harter Topspin in die Mitte. Kurze Aufschläge sind sporadisch eingesetzt als Überraschungswaffe gut, ansonsten erleichtern sie es dem Rückschläger nur, nach vorn an die NVZ zu kommen.

ALW_Serve
Categories: DBA Blogs

Grid Infrastructure --- OCR and Voting Disks Location at Installation

Hemant K Chitale - Sun, 2024-03-24 04:30

 In Oracle Grid Infrastructure, the OCR (Oracle Cluster Registry) and Voting "Disks" must be on Shared Storage accessible by all the nodes of the Cluster.  Typically, these are on ASM.

In ASM, a DiskGroup is created for the disks that hold the OCR.  

Normally, an ASM DiskGroup may use External Redundancy (Mirroring or other protection against Physical Disk or LUN failure is provided by the underlying Storage) or Normal Redundancy (Two-Way Mirroring, i.e. two Disks or LUN devices) or High Redundancy (Three-Way Mirroring with three Disks).

However, for the OCR and Voting "Disks" (i.e. Voting File), Normal Redundancy requires three Disks or LUN devices where three Voting Files and one OCR (Primary and Secondary copy) are created.  High Redundancy requires five Disks or LUN devices where five Voting Files and once OCR (with one Primary and two Secondary copies) are created.

In Test or Lab environments, you might have created your OCR/Vote DiskGroup on ASM storage with External Redundancy so as to not have to provision 3 or 5 disks.


However, in the 19c Lab environment with 2 Virtual Box VMs that I recently built on my Home PC, I created 5 ASM Disks of 2GB each (using ASMLib instead of udev persistent naming)  to hold the OCR + VOTE DiskGroup.  I then selected High Redundancy for the consequent DiskGroup.

This is the Installer Screen :



This is the subsequent output from running root.sh from the Grid ORACLE_HOME towards the end of the installation :

[datetime] CLSRSC-482: Running command: '/u01/app/grid/product/19.3/bin/ocrconfig -upgrade grid grid'

CRS-4256: Updating the profile

Successful addition of voting disk 6c3ea5fbf0254fd5bfd489fc5c674409.

Successful addition of voting disk ff3b9da031064fccbfab4b57933f12e1.

Successful addition of voting disk 44e50015bcf24f7cbfc1b9348fdbe568.

Successful addition of voting disk de64da366c164f5cbfba2761df5948d5.

Successful addition of voting disk 4485ff5940384f85bf524a81090c6bd8.

Successfully replaced voting disk group with +OCR_VOTE.

CRS-4256: Updating the profile

CRS-4266: Voting file(s) successfully replaced

##  STATE    File Universal Id                File Name Disk group

--  -----    -----------------                --------- ---------

 1. ONLINE   6c3ea5fbf0254fd5bfd489fc5c674409 (/dev/oracleasm/disks/OCR_VOTE_DISK_1) [OCR_VOTE]

 2. ONLINE   ff3b9da031064fccbfab4b57933f12e1 (/dev/oracleasm/disks/OCR_VOTE_DISK_2) [OCR_VOTE]

 3. ONLINE   44e50015bcf24f7cbfc1b9348fdbe568 (/dev/oracleasm/disks/OCR_VOTE_DISK_3) [OCR_VOTE]

 4. ONLINE   de64da366c164f5cbfba2761df5948d5 (/dev/oracleasm/disks/OCR_VOTE_DISK_4) [OCR_VOTE]

 5. ONLINE   4485ff5940384f85bf524a81090c6bd8 (/dev/oracleasm/disks/OCR_VOTE_DISK_5) [OCR_VOTE]

Located 5 voting disk(s).


Thus it did create 5 Voting "Disks" (Voting Files).

After the installation is completed, I verified this again  

from the first node "srv1":

[root@srv1 ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     491684
         Used space (kbytes)      :      84232
         Available space (kbytes) :     407452
         ID                       : 1183403784
         Device/File Name         :  +OCR_VOTE
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded

[root@srv1 ~]# crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   6c3ea5fbf0254fd5bfd489fc5c674409 (/dev/oracleasm/disks/OCR_VOTE_DISK_1) [OCR_VOTE]
 2. ONLINE   ff3b9da031064fccbfab4b57933f12e1 (/dev/oracleasm/disks/OCR_VOTE_DISK_2) [OCR_VOTE]
 3. ONLINE   44e50015bcf24f7cbfc1b9348fdbe568 (/dev/oracleasm/disks/OCR_VOTE_DISK_3) [OCR_VOTE]
 4. ONLINE   de64da366c164f5cbfba2761df5948d5 (/dev/oracleasm/disks/OCR_VOTE_DISK_4) [OCR_VOTE]
 5. ONLINE   4485ff5940384f85bf524a81090c6bd8 (/dev/oracleasm/disks/OCR_VOTE_DISK_5) [OCR_VOTE]
Located 5 voting disk(s).
[root@srv1 ~]#


and from the second node "srv2" :


[root@srv2 ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     491684
         Used space (kbytes)      :      84232
         Available space (kbytes) :     407452
         ID                       : 1183403784
         Device/File Name         :  +OCR_VOTE
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded

[root@srv2 ~]# crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   6c3ea5fbf0254fd5bfd489fc5c674409 (/dev/oracleasm/disks/OCR_VOTE_DISK_1) [OCR_VOTE]
 2. ONLINE   ff3b9da031064fccbfab4b57933f12e1 (/dev/oracleasm/disks/OCR_VOTE_DISK_2) [OCR_VOTE]
 3. ONLINE   44e50015bcf24f7cbfc1b9348fdbe568 (/dev/oracleasm/disks/OCR_VOTE_DISK_3) [OCR_VOTE]
 4. ONLINE   de64da366c164f5cbfba2761df5948d5 (/dev/oracleasm/disks/OCR_VOTE_DISK_4) [OCR_VOTE]
 5. ONLINE   4485ff5940384f85bf524a81090c6bd8 (/dev/oracleasm/disks/OCR_VOTE_DISK_5) [OCR_VOTE]
Located 5 voting disk(s).
[root@srv2 ~]#



Note : Whether I create the DiskGroup with Normal or High Redundancy, it will still show only 1 OCR because there is only 1 Primary OCR location (Normal or High Redundancy will automatically create 1 or 2 Secondary OCR copy).


It is possible to add another location for OCR in this manner (where I add to the FRA DiskGroup):

[root@srv1 ~]# ocrconfig -add +FRA
[root@srv1 ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     491684
         Used space (kbytes)      :      84232
         Available space (kbytes) :     407452
         ID                       : 1183403784
         Device/File Name         :  +OCR_VOTE
                                    Device/File integrity check succeeded
         Device/File Name         :       +FRA
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded

[root@srv1 ~]#

[root@srv2 ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     491684
         Used space (kbytes)      :      84232
         Available space (kbytes) :     407452
         ID                       : 1183403784
         Device/File Name         :  +OCR_VOTE
                                    Device/File integrity check succeeded
         Device/File Name         :       +FRA
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded

[root@srv2 ~]#


Furthermore, each node of the Cluster has a Local Cluster Registry (that is called an OLR) :

[root@srv1 ~]# ocrcheck -local
Status of Oracle Local Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     491684
         Used space (kbytes)      :      83144
         Available space (kbytes) :     408540
         ID                       : 1343496565
         Device/File Name         : /u01/app/grid_base/crsdata/srv1/olr/srv1_19.olr
                                    Device/File integrity check succeeded

         Local registry integrity check succeeded

         Logical corruption check succeeded

[root@srv1 ~]#

[root@srv2 ~]# ocrcheck -local
Status of Oracle Local Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     491684
         Used space (kbytes)      :      83132
         Available space (kbytes) :     408552
         ID                       : 1914984123
         Device/File Name         : /u01/app/grid_base/crsdata/srv2/olr/srv2_19.olr
                                    Device/File integrity check succeeded

         Local registry integrity check succeeded

         Logical corruption check succeeded

[root@srv2 ~]#

If you are worried about Failure Groups for the OCR_VOTE DiskGroup, you can see that the FailureGroups are automatically created for this High Redundancy DiskGroup :

SQL> l
  1  select g.name, d.disk_number, d.label, d.failgroup
  2  from v$asm_diskgroup g right join v$asm_disk d
  3  on g.group_number = d.group_number
  4  where g.name = 'OCR_VOTE'
  5* order by 1,2
SQL> /

NAME         DISK_NUMBER LABEL              FAILGROUP
------------ ----------- ------------------ ----------------
OCR_VOTE               0 OCR_VOTE_DISK_1    OCR_VOTE_0000
OCR_VOTE               1 OCR_VOTE_DISK_2    OCR_VOTE_0001
OCR_VOTE               2 OCR_VOTE_DISK_3    OCR_VOTE_0002
OCR_VOTE               3 OCR_VOTE_DISK_4    OCR_VOTE_0003
OCR_VOTE               4 OCR_VOTE_DISK_5    OCR_VOTE_0004

SQL>





Categories: DBA Blogs

Index Usage – 2

Jonathan Lewis - Wed, 2024-03-20 04:19

In the first part of this series I introduced Index Usage Tracking and the view dba_index_usage – a feature that appeared in 12.2 as a replacement for index monitoring and the view dba_object_usage. In this note I’ll give a quick sketch of the technicalities of the implementation and comments on how to test and use the feature. Actual tests, results and observations will have to wait until part 3.

A not very deep dive

There are three parameters relating to Index Usage Tracking (iut), shown below with their default values:

  • _iut_enable [TRUE]
  • _iut_max_entries [30000]
  • _iut_stat_collection_type [SAMPLED]

The feature is, as you can see, enabled by default; the tracking, however, is “sampled”, which seems to mean that a tiny number of executions end up being tracked. I can’t find any information about how the sampling is done, and having tried a few tests that executed thousands of statements in a couple of minutes without capturing any details of index usage I’ve given up trying and done all my testing with “_iut_stat_collection_type” set to ALL.

SQL> alter session set "_iut_stat_collection_type"=all;

According to a note on MOS (Doc ID 2977302.1) it doesn’t matter whether you set this parameter for the session or the system the effect is the same; and I found that this seemed to be true in my testing on Oracle 19.11 – either way the effect appeared across all sessions connecting to the PDB, though it didn’t seem to persist across a database restart.

The parameter _iut_max_entries probably limits the amount of global memory allowed for collecting stats about indexes. You might ask whether the 30,000 is per PDB or for the entire instance; I suspect it’s for the instance as a whole, but I’m not going to run up a test to scale on that. While I know of several 3rd party applications holding far more indexes than this, the number is probably sufficient for most investigations.

There are eight objects visibly related to Index Usage Tracking: three views, one table, three memory structures and one latch:

  • dba_index_usage – the user (dba) friendly view of the accumulated statistics of index usage
  • cdb_index_usage – the cdb equivalent of the above
  • v$index_usage_info – a view (holding one row) summarising the current tracking status
  • sys.wri$_index_usage – the main table behind the xxx_index_usage views above; the views join this table to obj$ and user$, so dropped indexes (and users) disappear from the views.
  • x$keiut_info – the memory structure (held in the shared pool) behind the v$index_usage_info
  • x$keiut – a structure holding a brief summary for each index actively being tracked. This is generated on demand in the session/process memory and my guess is that it’s an extract or summary of a larger memory structure in the shared pool holding the full histogram data for each index.
  • htab_keiutsg – a (small) memory allocation reported by v$sgastat in the shared pool. In my 19.11 the memory size was initially 512 bytes, and in a test with 140 indexes showing up in x$keiut the memory reported was still only 512 bytes (so it’s not a simple list of pointers, more likely a set of pointers to pointers/arrays.
  • “keiut hash table modification” – a single parent latch which I assume protects the htab_keiutsg memory. It’s possible that this latch is used to add an entry to the x$keiut structure (or, rather, the larger structure behind it) when an index is first tracked by the software, and that each entry in that larger structure is then protected by its own mutex to minimise collision time on updates as the stats are updated (or cleared after flushing).

Given that there’s a limit of 30,000 for iut_max_entries and only a small memory allocation for the keiut hash table, it does sound as if Oracle could end up walking a fairly long linked list or array to find the correct entry to update, which makes me wonder about two things: first, have I missed something obvious, secondly will Oracle skip updating the stats if the alternative means waiting for a mutex? There’s also the question of whether Oracle simply stops collecting when the limit is reached or whether there’s some sort LRU algorithm that allows it to discard entries for rarely used indexes to get maximum benefit from the available limit.

Another thought that goes with the 30,000 limit. I can find the merge statement that Oracle uses to update the wri$_index_usage table when the stats are flushed from memory to table (an activity that takes place every 15 minutes, with no obvious parameter to change the timing). In my19.11 instance its sql_id is 5cu0x10yu88sw, and it starts with the text:

merge into 
        sys.wri$_index_usage iu
using 
        dual
on      (iu.obj# = :objn)
when matched then 
        update set
                iu.total_access_count = iu.total_access_count + :ns,
                iu.total_rows_returned = iu.total_rows_returned + :rr,
                iu.total_exec_count = iu.total_exec_count + :ne,
...

This statement updates the table one row at a time (which you can confirm if you can find it in v$sql and compare rows_processed with executions). This could take a significant amount of time to complete on a system with a very large number of indexes.

The other thing that comes with finding the merge statement is that I couldn’t find any indication that there is a delete statement – either in v$sql, or in the Oracle executable. Spreading the search a little further I queried dba_dependencies and found that the package dbms_auto_index_internal references wri$_index_usage and various of the “autotask” packages – so perhaps there’s something a couple of layers further down the PL/SQL stack that generates dynamic SQL to delete tracking data. On the other hand, there are entries in my copy of wri$_index_usage where the last_used column has dates going back to September 2021, and there are a number of rows where the reported index has been dropped.

Testing the feature.

The most significant difficulty testing the mechanism is that it flushes the in-memory stats to the table every 15 minutes, and it’s only possible to see the histogram of index usage from the table. Fortunately it is possible to use oradebug to force mmon to trigger a flush, but I found in my Oracle 19.11 PDB I had to have a session logged into the server as the owner of the Oracle executable, and logged into the cdb$root as the SYS user (though a couple of colleagues had different degrees of success on different versions of Oracle and O/S). The following is a cut and paste after logging in showing appropriate calls to oradebug:

SQL> oradebug setorapname mmon
Oracle pid: 31, Unix process pid: 11580, image: oracle@linux19c (MMON)
SQL> oradebug call keiutFlush
Function returned 0
SQL> 

Initially I had assumed I could log on as a rather more ordinary O/S user and connect as SYS to the PDB, but this produced an unexpected error when I tried to execute the flush call:

SQL> oradebug call keiutFlush
ORA-32519: insufficient privileges to execute ORADEBUG command: OS debugger privileges required for client

In my testing, then, I’m going to open three sessions:

  • End-user session – a session to execute some carefully designed queries.
  • cdb$root SYS session – a session to flush stats from memory to disc.
  • PDB SYS session – a session to show the effects of the end-user activity (reporting figures from x$keiut_info, x$keiut, and dba_index_usage)

I’ll be running some simple tests, covering select, insert, update, delete and merge statements with single-column indexes, multi-column indexes, locally partitioned indexes, single table queries, nested loop joins, range scans, fast full scans, skip scans, inlist iterators, union views, stats collection and referential integrity. For each test I’ll describe how the index will be used, then show what the stats look like. Given that what we really need to see are the changes in x$keiut and dba_index_usage I’ll only show the complete “before / after” values in one example here. In part 3 of the series you’ll have to trust that I can do the arithmetic and report the changes correctly.

Example

From the end-user session I have a table created with the following code:

rem
rem     Script:         iut_01.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Mar 2024
rem     Purpose:        
rem
rem     Last tested 
rem             19.11.0.0

create table t1 as
select
        rownum                                  id,
        mod(rownum-1,10000)                     n1,
        trunc((rownum - 1)/20)                  col1,
        trunc((rownum - 1)/10)                  col2,
        rownum - 1                              col3,
        cast(rpad(rownum,25) as varchar2(25))   v1,
        cast(rpad('x',80,'x') as varchar2(80))  padding
from
        all_objects
where
        rownum <= 50000
/

create index t1_pk on t1(id);
create index t1_n1 on t1(n1);
create index t1_i1 on t1(col1, col2, col3);

From the cdb$root logged on as oracle (executable owner) and connected as SYS:

SQL> startup force
ORACLE instance started.

Total System Global Area 1476391568 bytes
Fixed Size                  9134736 bytes
Variable Size             822083584 bytes
Database Buffers          637534208 bytes
Redo Buffers                7639040 bytes
Database mounted.
Database opened.
SQL> oradebug setorapname mmon
Oracle pid: 31, Unix process pid: 27738, image: oracle@linux19c (MMON)
SQL> oradebug call keiutFlush
Function returned 0

From an ordinary O/S user, connected to the PDB as SYS:

SQL> select index_stats_collection_type, alloc_elem_count, active_elem_count, flush_count, last_flush_time from x$keiut_info;

INDEX_STATS_COLLECTION_TYPE ALLOC_ELEM_COUNT ACTIVE_ELEM_COUNT FLUSH_COUNT LAST_FLUSH_TIME
--------------------------- ---------------- ----------------- ----------- ----------------------------
                          1                0                 0           2 19-MAR-24 10.53.50.584 PM

1 row selected.

SQL> alter session set "_iut_stat_collection_type"=all;

Session altered.

SQL> select index_stats_collection_type, alloc_elem_count, active_elem_count, flush_count, last_flush_time from x$keiut_info;

INDEX_STATS_COLLECTION_TYPE ALLOC_ELEM_COUNT ACTIVE_ELEM_COUNT FLUSH_COUNT LAST_FLUSH_TIME
--------------------------- ---------------- ----------------- ----------- ----------------------------
                          0                0                 0           2 19-MAR-24 10.53.50.584 PM

1 row selected.


Note how the index_stats_collection_type changes from 1 to 0 after the “alter session”. I don’t know why the flush_count showed up as 2 when I had only flushed once – but perhaps the second flush is a side effect of altering the collection type.

From an ordinary end-user session

SQL> set feedback only
SQL> select n1 from t1 where id between 1 and 5;

5 rows selected.

SQL> select n1 from t1 where id between 1 and 5;

5 rows selected.

SQL> select n1 from t1 where id between 1 and 50;

50 rows selected.

These queries will use the index t1_pk in an index range scan to access the table by rowid.

From the PDB / SYS

SQL> select index_stats_collection_type, alloc_elem_count, active_elem_count, flush_count, last_flush_time from x$keiut_info;

INDEX_STATS_COLLECTION_TYPE ALLOC_ELEM_COUNT ACTIVE_ELEM_COUNT FLUSH_COUNT LAST_FLUSH_TIME
--------------------------- ---------------- ----------------- ----------- ----------------------------
                          0                1                 1           2 19-MAR-24 10.53.50.584 PM

1 row selected.

SQL> select objnum, objname, num_starts, num_execs, rows_returned from x$keiut;

    OBJNUM OBJNAME                          NUM_STARTS  NUM_EXECS ROWS_RETURNED
---------- -------------------------------- ---------- ---------- -------------
    208077 TEST_USER.T1_PK                           3          3            60

1 row selected.

In the x$keiut_info you can see that Oracle has now allocated one “element”, and has one “active” element. Checking x$keiut (which will report some details of each active element) we can see that my t1_pk index has been used in 3 statement executions, starting a scan a total of 3 times (which matches our expectation) with a total of 60 (= 5 + 5 + 50) rows returned. Of course all we could infer from this one row is that we have returned an average of 20 rows per start, and an average of one start per execution.

From the cdb$root SYS

SQL> oradebug call keiutFlush
Function returned 0

From the PDB SYS (using Tom Kyte’s “print_table”)

SQL> execute print_table(q'[select * from dba_index_usage where name = 'T1_PK' and owner = 'TEST_USER']')
OBJECT_ID                      : 208077
NAME                           : T1_PK
OWNER                          : TEST_USER
TOTAL_ACCESS_COUNT             : 3
TOTAL_EXEC_COUNT               : 3
TOTAL_ROWS_RETURNED            : 60
BUCKET_0_ACCESS_COUNT          : 0
BUCKET_1_ACCESS_COUNT          : 0
BUCKET_2_10_ACCESS_COUNT       : 2
BUCKET_2_10_ROWS_RETURNED      : 10
BUCKET_11_100_ACCESS_COUNT     : 1
BUCKET_11_100_ROWS_RETURNED    : 50
BUCKET_101_1000_ACCESS_COUNT   : 0
BUCKET_101_1000_ROWS_RETURNED  : 0
BUCKET_1000_PLUS_ACCESS_COUNT  : 0
BUCKET_1000_PLUS_ROWS_RETURNED : 0
LAST_USED                      : 19-mar-2024 23:08:02

From the data saved in the table we can see that we’ve logged 3 accesses, of which 2 accesses returned (individually) something between 2 and 10 rows (rowids) for a total of 10 rows (5 + 5) and one access returned (individually) something between 11 and 100 rows (rowids) for a total of 50 rows.

Of course we can say confidently that the one larger access actually did return 50 rows; but looking at nothing but these figures we can’t infer that the other two access returned 5 rows each, it could have been one query returning 2 rows and the other returning 8, or 3 and 7, or 4 and 6, but we do get a reasonable indication of the volume of data from the breakdown of 0, 1, 2 – 10, 11 – 100, 101 – 1000, 1000+

You might note that we can also see our flush time (reported below) reappearing as the last_used date and time – so we know that we are looking at current statistics.

From the PDB / SYS (again)

SQL> select index_stats_collection_type, alloc_elem_count, active_elem_count, flush_count, last_flush_time from x$keiut_info;

INDEX_STATS_COLLECTION_TYPE ALLOC_ELEM_COUNT ACTIVE_ELEM_COUNT FLUSH_COUNT LAST_FLUSH_TIME
--------------------------- ---------------- ----------------- ----------- ----------------------------
                          0                1                 1           3 19-MAR-24 11.08.02.013 PM

1 row selected.

SQL> select objnum, objname, num_starts, num_execs, rows_returned from x$keiut;

    OBJNUM OBJNAME                          NUM_STARTS  NUM_EXECS ROWS_RETURNED
---------- -------------------------------- ---------- ---------- -------------
    208077 TEST_USER.T1_PK                           0          0             0

1 row selected.

The x$keiut_info shows that a third flush has taken place (and any index flushed at that time will have its last_used set very near to that flush time – the merge command uses sysdate, so the last_used could be a tiny bit after the last_flush_time). It still shows an “active” element and when we check x$keiut we find that t1_pk is still listed but the stats have been reset to zero across the board.

If we were to repeat the flush command the active count would drop to zero and the t1_pk entry would disappear from x$keiut. (Oracle doesn’t remove an element until an entire tracking period has passed with no accesses – a typical type of “lazy” strategy aimed at avoiding unnecessary work.)

That’s all for now – if there are any questions put them in the comments and if their answers belong in this note I’ll update the note.

SPM and GTTs

Tom Kyte - Tue, 2024-03-19 03:26
Howdy, I'm wondering about how SPM and things like https://blogs.oracle.com/optimizer/post/what-is-add-verified-spm would be impacted by the presence of global temporary tables within the query(s). I've been looking for documentation that would outline how SQL plan management would behave when dealing with queries relying on GTTs but I haven't had any luck so far. Basically I'm curious how reliably baselines, evolving, etc can/do work when dealing with queries that could have wildly different data sets based on GTTs within the query. Cheers,
Categories: DBA Blogs

Kubernetes Network Policy by using Cilium – Beginner Level

Yann Neuhaus - Tue, 2024-03-19 02:20

In my blog post series about Kubernetes networking, I wrote here that by default, all pods can communicate together in a “vanilla” Kubernetes. Whatever they belong to the same namespace or not. To provide network separation, we need to implement Kubernetes Network Policy.

If your Kubernetes cluster is multi-tenant this is going to be a requirement. Multi-tenant means the same Kubernetes cluster host several applications. Each of them is in their own namespace. However the product owners are different for each application. In this case you’ll need to isolate each application from each other. It would be very bad if from a pod of your application you could reach and connect to a pod of another application and the other way around. To provide this networking isolation we need to configure and deploy Network Policy. They are often the pet peeve of many Kubernetes Administrator that are not familiar with networking. If it is your case, this blog post is for you. I’ll give you all the basics to understand it completely.

Ingress and Egress

Kubernetes Network Policy is like a police officer in front of your pods. It allows or denies traffic entering or exiting the pod. This is the first concept to grasp, the traffic can flow in 2 directions: entering or exiting. In traditional networking we call it incoming and outgoing (sometimes it is inbound and outbound) traffic. In Kubernetes we are talking about ingress and egress but they are the same thing.

Ingress or egress traffic depends on the point of reference. We basically apply a Network Policy to a pod. Cilium calls it an endpoint but it is the same. For this pod, the direction of traffic is egress when it comes from the pod to the outside. An ingress traffic is for traffic coming from outside and entering into this pod. The drawing below illustrates that concept:

Network Policies ingress egress definition

On the left, the police officer looks to the pod so she will stop and filter any traffic coming from that pod before it goes outside. On the right, he looks to the outside with his back to the pod. He will then filter and stop any traffic from outside before it enters that pod.

Labels

As you may know, pods get their IP Address from Cilium when they start. A pod by design can be deleted and recreated which means its IP Address will change. In traditional networking, we filter traffic with firewalls or Access Control List (ACL). This layer 3 filtering is based on the IP Address. In Kubernetes we then have to use another way than IP Addresses and so we use labels instead. We set a label to each pod and if pods use the same label then they belong to the same group. It is the case for the replicas of a front end or a back end pod that will share the same label. We apply a Network Policy to a group of pods based on this label. Let’s look at this on a drawing:

Network Policies labels definition

In our example we have 2 applications in 2 separate namespaces. Each application has 2 Front End (FE) and 2 Back End (BE). In the namespace app1, the FE have the same label “app: frontend1″ and the BE have the label “app:backend1″. We apply the same to the namespace app2.

Cilium Network Policy

Kubernetes provides an object called NetworkPolicy that is implemented by the Container Network Interface (CNI). Cilium is using its own object called CiliumNetworkPolicy that extends the capabilities of that standard Network Policy object. You can learn more about it from this Isovalent blog post.

A Network Policy is a yaml file where we configure to which pod labels it applies as well as the ingress and/or egress rules we apply to them. When we apply that yaml file in our cluster, we set the police officer in front of the pods door.

Kubernetes Network Policy ingress rule

Let’s look at an example and start with an ingress rule. We will use the simple CiliumNetworkPolicy below:

apiVersion: "cilium.io/v2"
kind: CiliumNetworkPolicy
metadata:
  name: "ingress-backend-rule"
  namespace: app1
spec:
  endpointSelector:
    matchLabels:
      app: backend1
  ingress:
  - fromEndpoints:
    - matchLabels:
        app: frontend1

This CiliumNetworkPolicy applies to any pod in the namespace app1 that has the label “app: backend1“. Only one ingress rule is applied here where traffic from all pods with the label “app: frontend1” is allowed. Any other ingress traffic will be discarded. Below is the drawing showing it in a visual way:

Network Policies ingress rules definition

When any traffic in this Kubernetes cluster reach the pods BE 1 or BE 2 in the namespace app1, the ingress rules of our Network Policy applies. Only traffic coming from the pods FE 1 or FE 2 in the namespace app1 will be allowed to enter because they have the authorized label.

Kubernetes Network Policy egress rule

Let’s continue with a simple egress rule. We will use the CiliumNetworkPolicy below:

apiVersion: "cilium.io/v2"
kind: CiliumNetworkPolicy
metadata:
  name: "egress-frontend-rule"
  namespace: app1
spec:
  endpointSelector:
    matchLabels:
      app: frontend1
  egress:
  - toEndpoints:
    - matchLabels:
        app: backend1

This CiliumNetworkPolicy applies to any pod in the namespace app1 that has the label “app: frontend1“. Only one egress rule is applied here where traffic to pods with the label “app: backend1” is allowed. Any other egress traffic from there pods will be discarded. Below is the drawing showing it in a visual way:

Network Policies egress rules definition

When traffic from the pods FE 1 or FE 2 in the namespace app1 is going out, the egress rule of our Network Policy applies. Only traffic to pods BE 1 and BE 2 will be allowed as they have the authorized label.

Wrap up

We’ve covered the basics of Kubernetes Network Policy by explaining what are its key components: Ingress, egress, labels and the CiliumNetworkPolicy object. Then we’ve seen a simple example of ingress and egress rules and illustrated them with a drawing. These basics should get you started in the world of Kubernetes Network Policy.

With the simple ingress and egress rules above did we completely isolate the traffic flow between FE and BE in app1? The answer is no because from BE 1 or BE 2 I can still reach any pods in the cluster. There is only an ingress rule but no egress rule. The same, any traffic can reach FE 1 or FE 2 because there is only an egress rule but no ingress rule. I would then need to configure an ingress rule and an egress rule (the same as above) into the same CiliumNetworkPolicy. Congratulations, you’ve just entered into the complex world of Kubernetes Network Policy!

L’article Kubernetes Network Policy by using Cilium – Beginner Level est apparu en premier sur dbi Blog.

Pickleball Übung: Drop Spiel 7-11

The Oracle Instructor - Mon, 2024-03-18 11:52

Einer der wichtigsten Schläge im Pickleball ist der 3rd Shot Drop – also der dritte Schlag einer Rally, wo das aufschlagende Team mittels eines kurzen Balls in die NVZ nach vorn kommen will.

Leider ist das auch ein ziemlich schwieriger Ball, weshalb er häufig geübt werden sollte.

Wenn es euch geht wie mir, findet ihr Spiele um Punkte viel spannender als Übungen. Darum hab ich mir dieses Spiel ausgedacht.

Es geht mit vier, drei oder sogar nur zwei Teilnehmern. Die Beschreibung ist für vier Spieler.

Spieler A und B sollen den Drop Shot üben. Sie stehen so, wie im normalen Spiel das aufschlagende Team vor dem 3. Schlag steht. Spieler C und D stehen so, wie im normalen Spiel das rückschlagende Team nach dem Return steht – nämlich an der NVZ. Hier in der Übung starten C und D jede Rally. Zuerst spielt D einen langen Ball diagonal. A versucht einen Drop Shot. Anschließend rücken A und B nach vorn:

Je nachdem, wie gut der Drop Shot war, kommen sie gleich nach vorn oder rücken allmählich durch die Transition-Zone vor.

Das Spiel geht mit Rally-Scoring, also sowohl Team AB als auch Team CD können jederzeit Punkte machen. C und D starten abwechselnd die Rally. Ist also der erste Punkt ausgespielt, beginnt nun C:

Für C und D ist es etwas leichter, Punkte zu machen als für A und B. Darum gewinnen C und D mit 11 Punkten, während A und B schon mit 7 Punkten gewinnen.

Sind nur drei Spieler am Start, übt einer den Drop Shot. Er wechselt dabei jeweils die Seite. Die Gegner dürfen nur auf diese Seite spielen.

Bei zwei Spielern spielt man nur auf einer Hälfte des Platzes.

Das Spiel hat für beide Teams einen guten Übungseffekt, denn diese Schläge sind typischerweise die kritischen Schläge jedes Ballwechsels bei fortgeschrittenen Spielern – und man spielt/übt eben nur diese.

Durch das Scoring bleibt die Motivation hoch. Bei unseren bisherigen Drop Spielen hat sich gezeigt, dass nach relativ kurzer Zeit häufiger das Drop Team mit 7 Punkten gewinnt. Das ist aber auch ganz okay so, finde ich. Denn das gibt ja das Feedback, dass man es mit dem Drop Shot richtig macht.

Categories: DBA Blogs

Number Data type declaration with different length and performance impact

Tom Kyte - Mon, 2024-03-18 09:06
1. I have few number column with data type declared as Number, Number (5), Integer, Numeric(10). I know in few cases the maximum data is 2 digits and I see that is declared as Number(38)/ NUMBER / Numeric(30) /Integer if i don't declare as number(2), instead if i declare as ( Number(38)/ NUMBER / Numeric(30) /Integer) will there be any performance issue when I have a table with millions of records and that is used in updating the data or used in Where clause 2. Varchar2 I have a column with 1 character (Y/N) if i declare this as Varchar2(1 CHAR) instead of VARCHAR2(1 BYTE). Will there be any performance issue when we use this column in where condition for millions of records? 3. IS it advisable to use ANSI Datatypes in table declaration or always preferable to use Oracle Data types, will there be any performance issue? Please advise
Categories: DBA Blogs

Update Partition table from another Partition table Performance issue

Tom Kyte - Mon, 2024-03-18 09:06
Hi I am migrating from Sybase IQ to Oracle 19C. there are many updates happening from one or multiple tables. My Target_TBL Table has 18 Million records per partition and there are 1000's of Partitions. (Partitioned by VersionID). APP_ID is one of the another key column in this table. I have 10 Partitioned tables which are partitioned by APP_ID which has around 10 Million to 15 Million Records. I have 5 non-partitioned Lookup tables which are smaller in size. I have rewritten all the Update statements to Merge in Oracle 19C, all the updates happen for one VersionID only which is in the where clause, and I join the source table using APP_ID and other keycolumn to update 70 to 100% of the records in each updates 1. Target table has a different key column to update the table from partitioned Source tables which are 10 to 15 Million. i have to do this by 10 different Merge Statements 2. Target Tables have different key columns to update from Non-partitioned Lookup table , I have to do this 5 different merge statements In sybase IQ all the multiple updates are completed in 10 Minutes, in Oracle 19C it takes more than 5 hours. I have enabled parallel Query and Parallel DML also. A) Can you suggest a better way to handle these kind of updates B) In few places the explain plan shows (PDML disabled because single fragment or non partitioned table used) . C) I leave the large Source table updates to go with has join's D) I Force the Lookup source table updates to use Neste Loop. Is this good or Not ? E) if i need to use indexes, can i go with local/global Other key column reference for Lookup tables. Appreciate any other suggestions to handle these scenarios. example <code> Merge INTO Target_TBL USING SOURCE_A ON (SOURCE_A.APP_ID=Target_TBL.APP_ID and SOURCE_A.JOB_ID=Target_TBL.JOB_ID) When Matched then update set Target_TBL.email=SOURCE_A.email Where Target_TBL.VersionID = 100 and SOURCE_A.APP_ID = 9876; Merge INTO Target_TBL USING SECOND_B ON (SECOND_B.APP_ID=Target_TBL.APP_ID and SECOND_B.DEPT_ID=Target_TBL.DEPT_ID) When Matched then update set Target_TBL.salary=SECOND_B.salary Where Target_TBL.VersionID = 100 and SECOND_B.APP_ID = 9876; Merge INTO Target_TBL USING Lookup_C ON (Lookup_C.Country_ID=Lookup_C.Country_ID) When Matched then update set Target_TBL.Amount_LOCAL=Lookup_C.Amount_LOCAL Where Target_TBL.VersionID = 100; </code>
Categories: DBA Blogs

Gather STATS on Partitioned Table and Parallel for Partitioned Table

Tom Kyte - Mon, 2024-03-18 09:06
hi I have a Partitioned(List) table by a VERSION_ID, which has around 15 million per partition. We have daily partitioned ID created bulk insert for 15 Million rows with 500 columns and then have 10 updates(MERGE UPDATE) for multiple columns from multiple other tables. is it good to gather stats after insert once and then after multiple update once. What is good practice for performance in gather stats for these partitioned table scenarios's second question, when i use merge on partition table from other partioned table, i am seeing the below in explain plan when i use Parallel DML hint. PDML disabled because single fragment or non partitioned table used
Categories: DBA Blogs

DR setup involving replicated database

Tom Kyte - Mon, 2024-03-18 09:06
Howdy, The current set up I'm looking at is an OLTP production system running Oracle 19.20 (4 instance RAC) with active data guard. This system is seeding a data warehouse running Oracle 19.20 by way of Oracle GoldenGate via an integrated extract. At present the warehouse does not have a DR solution in place and that's the point of the post. I'm wondering what the best solution would be for a warehouse DR strategy when GoldenGate is in play like this. I assume data guard again but happy to hear other thoughts. The bulk of the questions I have involve the GoldenGate component. I'm not sure how that would need to be set up / configured in order to minimize the complexity in any role transitions from either the transactional or warehouse (or both); and what scenarios can be handled seamlessly and which would require manual intervention. Thanks a bunch! Cheers,
Categories: DBA Blogs

Locate an Error in Wrapped PL/SQL

Pete Finnigan - Mon, 2024-03-18 08:46
I had a conversation a few weeks ago with someone who asked me how to find a missing table when you have a wrapped PL/SQL file and cannot see the source code and you install it and it gives an....[Read More]

Posted by Pete On 18/03/24 At 01:00 PM

Categories: Security Blogs

Getting started with Greenplum – 6 – Why Greenplum?

Yann Neuhaus - Mon, 2024-03-18 06:56

Now that we have the basic knowledge for operating a Greenplum cluster we should talk about why Greenplum can be option and what are the benefits of such a system. Again, here are the previous posts: Getting started with Greenplum – 1 – Installation, Getting started with Greenplum – 2 – Initializing and bringing up the cluster, Getting started with Greenplum – 3 – Behind the scenes, Getting started with Greenplum – 4 – Backup & Restore – databases, Getting started with Greenplum – 5 – Recovering from failed segment nodes.

First of all, Greenplum is for data analytics and data warehousing. It is not meant as an OLTP system. Greenplum also describes itself as an MPP system, which means “Massively Parallel Processing”. The idea behind that is quite simple: Use as many hosts as you can, all of them with their own CPUs, memory disks, and operating system, process the required work in parallel on those nodes, combine the results and give it back to the client. This is also known as a “shared nothing” architecture.

Coming back to the setup we’ve running now:

                                        |-------------------|
                             |------6000|primary---------   |
                             |          |     Segment 1 |   |
                             |      7000|mirror<------| |   |
                             |          |-------------------|
                             |                        | |
            |-------------------|                     | |
            |                   |                     | |
        5432|   Coordinator     |                     | |
            |                   |                     | |
            |-------------------|                     | |
                             |                        | |
                             |          |-------------------|
                             |------6000|primary ------ |   |
                                        |     Segment 2 |   |
                                    7000|mirror<--------|   |
                                        |-------------------|

We have the coordinator node in front, this one receives all the client requests (the coordinator host can also be fault tolerant by adding a standby coordinator host). Work is then distributed to the amount of segment nodes you have available in the cluster. In my case this are just small VMs running on the same host so it will not give me any benefit performance wise. In a real setup all those nodes would run either on bare metal nodes or at least on different host nodes in a virtualized setup. The more segment nodes get added to the cluster, the more compute resources become available to be utilized. A critical part in such a setup is of course the networking. All the traffic goes through the network and the faster the network between the Greenplum nodes (which is called the interconnect) the better the whole cluster will perform.

Let’s assume we got all these building blocks right, do we need to consider more points to get most out of such a setup? Yes, we do.

In PostgreSQL all tables are heap organized. With Greenplum you can choose between heap oriented and append optimized tables. Heap oriented tables should be used when you expect frequent updates and deletes, append oriented tables should be used for initial load tables which only receive bulk inserts after loading.

The following statements create two simple tables, one heap organized, the other one append optimized, both distributed by id:

postgres=# create table t1 ( id int primary key
                , dummy text 
                ) 
                using heap
                distributed by (id);
                
CREATE TABLE
postgres=# create table t2 ( id int primary key
                , dummy text 
                ) 
                using ao_row
                distributed by (id);
CREATE TABLE

Populating both tables with the same amount of data and comparing the size of the tables give this:

postgres=# \timing
Timing is on.
postgres=# insert into t1 select i, i::text from generate_series(1,1000000) i;
INSERT 0 1000000
Time: 2639.981 ms (00:02.640)
postgres=# insert into t2 select i, i::text from generate_series(1,1000000) i;
INSERT 0 1000000
Time: 2878.901 ms (00:02.879)
postgres=# select pg_relation_size('t1');
 pg_relation_size 
------------------
         44072960
(1 row)

Time: 1.394 ms
postgres=# select pg_relation_size('t2');
 pg_relation_size 
------------------
         25226336
(1 row)

Time: 2.035 ms

The append optimized table is much smaller than the traditional heap organized table even without compression. The reason is that the tuple headers are much smaller for append optimized tables. This is also reason why they should not be used for frequent update and delete operations. Append only is only meant for bulk loading and bulk insert operations.

Another option you have if you go for append optimized tables is columnar storage but again, consider when you to use them: Columnar storage is read optimized and is not mean for write heavy operations. If you only access a small number of columns this can reduce the required I/O significantly. A table which is organized by columns is created like this:

postgres=# create table t4 ( id int primary key
                , dummy text 
                ) 
                using ao_row
                distributed by (id);
CREATE TABLE

On top of all that you can chose to go for compression and partitioning, which gives you even more choice on how you want to layout your data. Connectors are something you might want to look as well.

Coming back to the initial question: Why Greenplum?

If you have the requirement for massive parallel data processing and you want to have a system which very much feels like a standard PostgreSQL for the clients, Greenplum is a valid option. As there is an open source edition give it a try and explore the possibilities. There is a lot of choice for various use cases and access patterns.

L’article Getting started with Greenplum – 6 – Why Greenplum? est apparu en premier sur dbi Blog.

FastAPI File Upload and Temporary Directory for Stateless API

Andrejus Baranovski - Sun, 2024-03-17 09:32
I explain how to handle file upload with FastAPI and how to process the file by using Python temporary directory. Files placed into temporary directory are automatically removed once request completes, this is very convenient for stateless API. 

 

If you think I’m geeky, you should meet my friend.

The Anti-Kyte - Sat, 2024-03-16 11:14

I’d like to talk about a very good friend of mine.
Whilst he’s much older than me ( 11 or 12 weeks at least), we do happen to share interests common to programmers of a certain vintage.

About a year ago, he became rather unwell.
Since then, whenever I’ve gone to visit, I’ve taken care to wear something that’s particular to our friendship and/or appropriately geeky.

At one point, when things were looking particularly dicey, I promised him, that whilst “Captain Scarlet” was already taken, if he came through he could pick any other colour he liked.
As a life-long Luton Town fan, his choice was somewhat inevitable.
So then, what follows – through the medium of Geeky T-shirts – is a portrait of my mate Simon The Indestructable Captain Orange…

When we first met, Windows 3.1 was still on everyone’s desktop and somewhat prone to hanging at inopportune moments. Therefore, we are fully aware of both the origins and continuing relevance of this particular pearl of wisdom :

Fortunately, none of the machines Simon was wired up to in the hospital seemed to be running any version of Windows so I thought he’d be reassured by this :

Whilst our first meeting did not take place on a World riding through space on the back of a Giant Turtle ( it was in fact, in Milton Keynes), Simon did earn my eternal gratitude by recommending the book Good Omens – which proved to be my gateway to Discworld.
The relevance of this next item of “Geek Chic” is that, when Simon later set up his own company, he decided that it should have a Latin motto.
In this, he was inspired by the crest of the Ankh-Morpork Assassins’ Guild :

His motto :

Nil codex sine Lucre

…which translates as …

No code without payment

From mottoes to something more akin to a mystic incantation, chanted whenever you’re faced with a seemingly intractable technical issue. Also, Simon likes this design so…

As we both know, there are 10 types of people – those who understand binary and those who don’t…

When confronted by something like this, I am able to recognise that the binary numbers are ASCII codes representing alphanumeric characters. However, I’ve got nothing on Simon, a one-time Assembler Programmer.
Whilst I’m mentally removing my shoes and socks in preparation to translate the message, desperately trying to remember the golden rule of binary maths ( don’t forget to carry the 1), he’ll just come straight out with the answer (“Geek”, in this case).

Saving the geekiest to last, I’m planning to dazzle with this on my next visit :

Techie nostalgia and a Star Wars reference all on the one t-shirt. I don’t think I can top that. Well, not for now anyway.

Pages

Subscribe to Oracle FAQ aggregator