Roger that. This was a problem that database file systems will eventually solve
with an implicit index on file name.
Some 1960’s file systems (DTSS, SIGMA) that used an ordered insertion name tree
instead of plowing through a linked list of inodes did this just fine, but
presuming fewer entries per directory, the UNIX guys went for the slightly
faster (on creation) linked list.
Tanel wrote up a classic performance track down at Hotsos several years ago
where no waits were traceable to the Oracle engine and he had to get all the
way down to “find the file” in subroutines before the problem became apparent.
At COAT circa 1990 a very useful thing our OS admins did was monitor the number
of entries in any single “folder” for analysis by the DBA and applications team.
At the time, this led Oracle to put the different alerts and logs and so forth
into different trees when we demonstrated it was a problem. It helped convince
them that back then inode table expansion required a machine reboot, so things
weren’t just slow and they actually blew up.
Several years ago Oracle reversed that decision for simplicity as faster
hardware, larger memory, and dynamic inode tree growth made it less noticeable.
I groaned. How big is too big is a slippery slope.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Mladen Gogala
Sent: Saturday, January 13, 2018 7:50 PM
Subject: Re: External table performance
With 20+ millions of directory objects, problems are inevitable. File systems
are not made for that kind of load. The file names in the directory are either
searched sequentially or by binary search, which is OK if there are few
thousands of the directory entries, which fit in memory, but would likely cause
problems with 20+ millions of directory entries. File systems are not meant for
20+ millions of files in a directory.
On 01/12/2018 06:11 PM, Kellyn Pot'Vin-Gorman wrote:
I’d only just heard of it recently, but the DBA experiencing it said it was any
pull from external table to do bulk loads. There’s a number of bugs in Oracle
support for the search criteria: “12.1 external table read”
Bug 21553593 20+ million directory objects causing slow reads from external
Bug 19597583 "external table reads" during local PDB
Tel: (347) 321-1217