<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
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">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<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>
</body>
</html>