<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hi,<br>
<br>
What I did now is to force statistics gathering, so:<br>
<br>
ANALYZE TABLE AHN_BLCK compute system statistics for table;<br>
BEGIN<br>
dbms_stats.gather_table_stats('PDAL20M','AHN_BLCK',NULL,NULL,FALSE,'FOR
ALL COLUMNS SIZE AUTO',8,'ALL');<br>
END;<br>
<br>
And after doing this the queries are scalable, so in this way I do
not need to wait for the DB to learn...<br>
<br>
Regards,<br>
<br>
O.<br>
<br>
<br>
<div class="moz-cite-prefix">On 11-08-15 16:51, Peter van Oosterom
wrote:<br>
</div>
<blockquote cite="mid:55CA0C0C.6090700@tudelft.nl" type="cite">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<div class="moz-cite-prefix">Hi Oscar,<br>
<br>
It feels like when you fetch the data, this is based on a query
execution plan that does a full table scan to get the blocks.
Even if there is an index, the database may not use this.
However, the database may notice that the actual query execution
was disappointing (collecting statistics), and that after
repeating the same tests, the database behaviour changed its
behaviour and does scale well. <br>
<br>
[others: this was not in email of Oscar, but after repeating the
test the data was fetched in about 0.02 seconds for all sizes
20M, 210M and 2201M. So, also the fetching in case of small
dataset becomes significantly faster form 0.5 vs. 0.02 seconds.]<br>
<br>
Would be good to see the actual query execution plain or force
the database to use the index (with an hint). My hand-on
practical Oracle syntax knowledge is too low to give exact hits
how to do this, but perhaps others can help here.<br>
<br>
Kind regards, Peter.<br>
<br>
<br>
On 11-8-2015 16:26, Oscar Martinez Rubi wrote:<br>
</div>
<blockquote cite="mid:55CA061C.5080603@esciencecenter.nl"
type="cite"> Hi,<br>
<br>
I have investigated a bit more this issue.<br>
<br>
I wanted to see what data does the OCI reader actually reads, so
I executed the pre-selection query out of PDAL, in python. So I
run the attached script (freshly after loading the data).<br>
<br>
The script runs the same exact query as done in PDAL twice and
prints for each run the number of returned blocks, time spent in
the query and time spent to fetch the results.<br>
<br>
the exact query is:<br>
<br>
SELECT <br>
l."OBJ_ID", l."BLK_ID", l."BLK_EXTENT", l."BLK_DOMAIN",
l."PCBLK_MIN_RES", l."PCBLK_MAX_RES", l."NUM_POINTS",
l."NUM_UNSORTED_POINTS", l."PT_SORT_DIM", l."POINTS", b.pc<br>
FROM <br>
AHN_BLCK l, AHN_BASE b, QUERY_POLYGONS g<br>
WHERE<br>
l.obj_id = b.id<br>
AND<br>
SDO_FILTER(l.blk_extent,g.geom) = 'TRUE' AND g.id = 1;<br>
<br>
The results I get are:<br>
<br>
20M run 1: <br>
#blocks: 12<br>
query time[s]: 0.113833904266<br>
fetch time[s]: <b>0.571593046188</b><br>
20M run 2: <br>
#blocks: 12<br>
query time[s]: 0.000102996826172<br>
fetch time[s]: <b>0.500910997391</b><br>
210M run 1: <br>
#blocks: 13<br>
query time[s]: 0.0586049556732<br>
fetch time[s]: <b>5.09832000732</b><br>
210M run 2: <br>
#blocks: 13<br>
query time[s]: 0.000245094299316<br>
fetch time[s]: <b>5.05038785934</b><br>
2201M run 1: <br>
#blocks: 13<br>
query time[s]: 0.070690870285<br>
fetch time[s]: <b>52.4960811138</b><br>
2201M run 2: <br>
#blocks: 13<br>
query time[s]: 0.000225067138672<br>
fetch time[s]: <b>53.1006689072</b><br>
<br>
So, even though the query times and the number of returned
blocks are similar the fetch times are the not. We can see the
scaling issue there. Somehow the fetching is much more expensive
(10x) when points are 10x.<br>
<br>
I also noticed that after a while doing queries the times get
much better and scalable even when I do new queries with other
polygons. So, the first queries suffer of scaling issues but
later it gets better. <br>
<br>
Any idea why?<br>
<br>
Regards,<br>
<br>
O.<br>
<br>
<br>
<br>
<br>
<div class="moz-cite-prefix">On 10-08-15 18:40, Albert Godfrind
wrote:<br>
</div>
<blockquote
cite="mid:8CDC3A42-0C98-461B-9569-33CB19E7D94E@oracle.com"
type="cite">
<div class="">Like many tools that access an Oracle database,
we lack the ability to see what actually happens in the
database at a detailed level, i.e. which actual queries are
sent, and how the database executes them in terms of CPU
use, logical and physical I/Os, network throughput and
latency.</div>
<div class=""><br class="">
</div>
<div class="">So I think it is important to add some debugging
/ tracing facility to let me see what happens:</div>
<div class=""><br class="">
</div>
<div class="">1) An option to make PDAL (actually the OCI
driver here) log each SQL statement it executes, together
with the elapsed time and the number of rows (blocks)
fetched. Obviously we have that statement in the input XML
file, but a trace would put everything in a single log and
include proper measurements.</div>
<div class=""><br class="">
</div>
<div class="">2) More important: an option to make the OCI
driver enable SQL tracing at the database side. This is
simple to do by just issuing an “ALTER SESSION …” statement
before running the queries. The resulting trace will show
all details about execution times as well as resource
consumption (CPU and IO) and wait times. That could be added
as an option in the XML file. Or maybe extend the XML file
with the option to specify a SQ statement to be performed
before each query (we could then use that to manually add
the ALTER SESSION statement.</div>
<div class=""><br class="">
</div>
<div class="">The resulting trace can help isolate the
bottleneck as one of:</div>
<div class=""><br class="">
</div>
<div class="">
<div class="">1) the I/Os in the database, to fetch the
blocks from disk (mostly I/O)</div>
<div class="">2) the network time to pass the blocks to the
PDAL client (network throughput and latency)</div>
<div class="">3) the time to process the blocks in the PDAL
client (mostly CPU)</div>
<div class=""><br class="">
</div>
</div>
<div class="">Albert</div>
<div class=""><br class="">
</div>
<div>
<blockquote type="cite" class="">
<div class="">On 5-Aug-2015, at 12:26, Oscar Martinez Rubi
<<a moz-do-not-send="true"
href="mailto:o.rubi@esciencecenter.nl" class="">o.rubi@esciencecenter.nl</a>>
wrote:</div>
<br class="Apple-interchange-newline">
<div class="">Hi,<br class="">
<br class="">
I did a test to see how good Oracle with PDAL scale with
bigger data sets. I had 3 datasets that are
self-contained with 20M, 210M and 2201M points. I loaded
them in different Oracle DBs with PDAL and laz-perf.
And, for each of them I ran 7 queries (via a pdal
pipeline that preselects blocks, applies a crop and then
write to a LAS file)<br class="">
<br class="">
The results are in the attached file.<br class="">
<br class="">
Regarding the loading, for the 20M I only used one core
(it is only one file) while for the others I used 16
cores, i.e. 16 simult. PDAL instances loading data to
Oracle. I opened an issue in GitHub because I noticed
that in some of the runs the size that I got was too
large, and I do not know what caused that. The attached
numbers are when everything seemed to work and the sizes
were as expected.<br class="">
<br class="">
This message, though, is about the queries. Each query
is run twice in each DB. As you can see in the results
file, for 10x more points in the data set the queries
are 10x slower, at least for the first run (with the
2201M the second run is much faster but this does not
happen with the 210M).<br class="">
<br class="">
Find also attached one of the XML that i used for the
queries (example is for query1). Note that the geometry
is previously inserted in oracle so I can use to
pre-filter blocks with the query option in oci reader<br
class="">
<br class="">
First I though that maybe the query option in the oci
reader in the XML was ignored and that all the blocks of
the dataset were being processed by PDAL (that would
explain 10x more points 10x slower queries) but I ran a
pdal pipeline for query1 with verbose and I saw that the
crop filter "only" processed 120000 points which makes
sense taking into account that region of query 1 only
has 74818 points. Or maybe the crop still process all
the blocks extents but only opens and decompress the
points of the overlapping ones?<br class="">
<br class="">
Any idea what is happening?<br class="">
<br class="">
Regards,<br class="">
<br class="">
O.<br class="">
<span
id="cid:05400F1D-9668-4C4F-86E3-029C914D8D51@fr.oracle.com"><results.txt></span><span
id="cid:3D842102-C6B1-41B1-A545-02B8CFC1CF77@fr.oracle.com"><query1.xml></span></div>
</blockquote>
</div>
<br class="">
<div class="">
<div style="color: rgb(0, 0, 0); font-family: Helvetica;
font-style: normal; font-variant: normal; font-weight:
normal; letter-spacing: normal; line-height: normal;
orphans: 2; text-align: -webkit-auto; text-indent: 0px;
text-transform: none; white-space: normal; widows: 2;
word-spacing: 0px; -webkit-text-stroke-width: 0px;
word-wrap: break-word; -webkit-nbsp-mode: space;
-webkit-line-break: after-white-space;" class="">
<div style="color: rgb(0, 0, 0); font-family: Helvetica;
font-style: normal; font-variant: normal; font-weight:
normal; letter-spacing: normal; line-height: normal;
orphans: 2; text-align: -webkit-auto; text-indent: 0px;
text-transform: none; white-space: normal; widows: 2;
word-spacing: 0px; -webkit-text-stroke-width: 0px;
word-wrap: break-word; -webkit-nbsp-mode: space;
-webkit-line-break: after-white-space;" class=""><span
class="Apple-style-span" style="border-collapse:
separate; border-spacing: 0px;">
<div style="word-wrap: break-word; -webkit-nbsp-mode:
space; -webkit-line-break: after-white-space;"
class=""><span class="Apple-style-span"
style="border-collapse: separate; color: rgb(0, 0,
0); font-family: Helvetica; font-style: normal;
font-variant: normal; font-weight: normal;
letter-spacing: normal; line-height: normal;
orphans: 2; text-align: -webkit-auto; text-indent:
0px; text-transform: none; white-space: normal;
widows: 2; word-spacing: 0px; border-spacing: 0px;
-webkit-text-decorations-in-effect: none;
-webkit-text-stroke-width: 0px;">
<div style="color: rgb(0, 0, 0); font-family:
Helvetica; font-style: normal; font-variant:
normal; font-weight: normal; letter-spacing:
normal; line-height: normal; orphans: 2;
text-align: -webkit-auto; text-indent: 0px;
text-transform: none; white-space: normal;
widows: 2; word-spacing: 0px;
-webkit-text-stroke-width: 0px; word-wrap:
break-word; -webkit-nbsp-mode: space;
-webkit-line-break: after-white-space;" class="">--<br
class="">
<a moz-do-not-send="true"
href="http://www.oracle.com" class=""><img
moz-do-not-send="true" alt="ORACLE"
src="http://www.oracle.com/dm/design/images/oracle_sig_logo.gif"
style="border-width: 0px; border-style:
solid; width: 114px; height: 26px;" class=""></a><br
class="">
<font class="" size="2" color="#666666"
face="Verdana, Arial, Helvetica, sans-serif">Albert
Godfrind | Geospatial technologies | Tel: +33
4 93 00 80 67</font><span
class="Apple-converted-space"> </span><font
class="" size="2" color="#666666"
face="Verdana, Arial, Helvetica, sans-serif">|
Mobile: +33 6 09 97 27 23</font><span
class="Apple-converted-space"> </span><font
class="" size="2" color="#666666"
face="Verdana, Arial, Helvetica, sans-serif">|
Skype:<span class="Apple-converted-space"> </span><a
moz-do-not-send="true"
href="skype:albert-godfrind" class="">albert-godfrind</a></font><br
class="">
<font class="" size="2" color="#666666"
face="Verdana, Arial, Helvetica, sans-serif"><font
class="" color="#ff0000">Oracle</font><span
class="Apple-converted-space"> </span>Server
Technologies<br class="">
400 Av. Roumanille,</font><font class=""
size="2" color="#666666" face="Verdana, Arial,
Helvetica, sans-serif"><span
class="Apple-converted-space"> </span></font><font
class="" size="2" color="#666666"
face="Verdana, Arial, Helvetica, sans-serif">BP
309 </font><span class="Apple-converted-space"> </span><font
class="" size="2" color="#666666"
face="Verdana, Arial, Helvetica, sans-serif">|</font><span
class="Apple-converted-space"> </span><font
class="" size="2" color="#666666"
face="Verdana, Arial, Helvetica, sans-serif">06906
Sophia Antipolis cedex<span
class="Apple-converted-space"> </span></font><font
class="" size="2" color="#666666"
face="Verdana, Arial, Helvetica, sans-serif">|<span
class="Apple-converted-space"> </span></font><font
class="" size="2" color="#666666"
face="Verdana, Arial, Helvetica, sans-serif">France<br
class="">
<a moz-do-not-send="true"
href="http://www.apress.com/9781590598993"
class="">Everything you ever wanted to know
about Oracle Spatial</a></font></div>
<span style="color: rgb(0, 0, 0); font-family:
Helvetica; font-style: normal; font-variant:
normal; font-weight: normal; letter-spacing:
normal; line-height: normal; orphans: 2;
text-align: -webkit-auto; text-indent: 0px;
text-transform: none; white-space: normal;
widows: 2; word-spacing: 0px;
-webkit-text-stroke-width: 0px;"><span
style="font-family: Helvetica; font-style:
normal; font-variant: normal; font-weight:
normal; letter-spacing: normal; line-height:
normal; orphans: 2; text-align: -webkit-auto;
text-indent: 0px; text-transform: none;
white-space: normal; widows: 2; word-spacing:
0px; -webkit-text-stroke-width: 0px;"><span
style="font-family: Helvetica; font-style:
normal; font-variant: normal; font-weight:
normal; letter-spacing: normal; line-height:
normal; orphans: 2; text-align:
-webkit-auto; text-indent: 0px;
text-transform: none; white-space: normal;
widows: 2; word-spacing: 0px;
-webkit-text-stroke-width: 0px;"><span><span><a
moz-do-not-send="true"
href="http://www.locationintelligence.net/dc/"
style="font-family: Helvetica;
font-style: normal; font-variant:
normal; font-weight: normal;
letter-spacing: normal; line-height:
normal; orphans: 2; text-align:
-webkit-auto; text-indent: 0px;
text-transform: none; white-space:
normal; widows: 2; word-spacing: 0px;
-webkit-text-stroke-width: 0px;"
class=""><br
class="Apple-interchange-newline"
style="color: rgb(71, 135, 255);
font-family: Helvetica; font-style:
normal; font-variant: normal;
font-weight: normal; letter-spacing:
normal; line-height: normal;
orphans: 2; text-align:
-webkit-auto; text-indent: 0px;
text-transform: none; white-space:
normal; widows: 2; word-spacing:
0px; -webkit-text-stroke-width: 0px;
text-decoration: underline;">
</a></span></span></span></span></span></span></div>
</span></div>
</div>
<br class="Apple-interchange-newline">
<br class="Apple-interchange-newline">
</div>
<br class="">
</blockquote>
<br>
</blockquote>
<br>
<br>
<pre class="moz-signature" cols="72">--
Peter van Oosterom <a moz-do-not-send="true" class="moz-txt-link-abbreviated" href="mailto:P.J.M.vanOosterom@tudelft.nl">P.J.M.vanOosterom@tudelft.nl</a>
Section GIS technology (room 00-west-520) Department OTB
Faculty of Architecture and the Built Environment, TU Delft
tel (+31) 15 2786950 Julianalaan 134, 2628 BL Delft, NL
fax (+31) 15 2784422 P.O. Box 5043, 2600 GA Delft, NL
<a moz-do-not-send="true" class="moz-txt-link-freetext" href="http://geomatics.tudelft.nl">http://geomatics.tudelft.nl</a> MSc Geomatics
<a moz-do-not-send="true" class="moz-txt-link-freetext" href="http://www.msc-gima.nl">http://www.msc-gima.nl</a> MSc GIMA (Geo-Info Management&Appl)
<a moz-do-not-send="true" class="moz-txt-link-freetext" href="http://www.gdmc.nl">http://www.gdmc.nl</a>
</pre>
</blockquote>
<br>
</body>
</html>