Discussions

Expand all | Collapse all

Question about 'EXPLAIN CALCITE'

  • 1.  Question about 'EXPLAIN CALCITE'

    Posted 03-09-2019 02:57
    I used 'explain calcite' to see the results.
    I want to make a Query Tree.
    Is there a way to know if all of these results are going on inside the GPU or only a few parts of the GPU?
    What I want to know is if the Sort, Join, Filtering, Project, Aggregates, TableScan, etc. are all happening in the GPU.
    And I want to know is which part of the query runs on the GPU and which part runs on the CPU.

    I'm sorry that I've been asking so many small questions lately...


    #Core

    ------------------------------
    Jeon Woohyuck
    [[Unknown]]
    ------------------------------


  • 2.  RE: Question about 'EXPLAIN CALCITE'

    Posted 03-09-2019 13:22
    Hi @Jeon Woohyuck,

    don't worry about asking questions; the purpose of a community forum is also that.
    On Omnisci almost all the work is done by the GPU, so except for final projection and sort on query1 everything is running on GPU if there are enough resources (memory) to process your query.

    If there isn't enough memory to keep the data needed by the query on GPU the entire execution will fall back on CPU; this kind of behavior is because the software has been developed from the ground up to run queries at interactive speeds, so it's not using the GPU as a co-processor to just accelerate some operations.


    ------------------------------
    Candido Dessanti
    Dba
    Crismatica consulting
    Rome
    ------------------------------



  • 3.  RE: Question about 'EXPLAIN CALCITE'

    Posted 03-10-2019 00:05
    Thank you @Candido Dessanti

    I would also like to know the 'plan operator-wise cost' from the results from the 'explain calcite'.
    Can I see the cost of running these queries?​

    Like below

    If not, do I need to use '---enable-debug-timer=on' to analyze the costs directly in the log file?

    ------------------------------
    Jeon Woohyuck
    [[Unknown]]
    ------------------------------



  • 4.  RE: Question about 'EXPLAIN CALCITE'

    Posted 03-10-2019 01:05
    Hi @Jeon Woohyuck,

    Oracle database has some different kind of indexes (e.g. , bitmap, bitmap join indexes, index-organized tables), multiple kind of join (e.g., nested loops, hash join, sort/merge join, etc.) and lots of query transformation (e.g. vector transformation, star transformation) and so on (I could continue for a while) so it needs a Cost-Based Optimizer to choose the best plan to run the query, so it basically assign a cost for each step based on operation and estimated cardinality then sum up those costs then choose the plan with the lower one.
    On the Omnisci database, there aren't indexes and the hash join is always preferred to a loop join, and the software is optimized for analytic workloads, so at the moment a proper CBO isn't really needed (anyway internally something is evaluated) so there isn't an output of costs (it wouldn't make sense).
    With the enable debug timer option, you just get how much time took every step needed to execute the query, but we are talking about parse (calcite), optime (reg-), caches population, execution, and final projection.


    ------------------------------
    Candido Dessanti
    Dba
    Crismatica consulting
    Rome
    ------------------------------