How can I get full explain and profile plans for a simple SPARQL query?

Starting with Virtuoso Commercial Release 6.4, and Virtuoso Open Source 6.1.5, isql offers 2 new modes for analyzing SPARQL queries:

  1. Translate a SPARQL query into the correspondent SQL: 

    SQL> SET SPARQL_TRANSLATE ON;
SQL> SELECT * FROM <graph> WHERE {?S a ?O};
       ---> Or execute query of your choice
SQL> SET SPARQL_TRANSLATE OFF;

  2. Generate compilation plan of an SQL query: 

    SQL> SET EXPLAIN ON;
SQL> SELECT * FROM TABLE WHERE field = 'text';
     ---> Or execute query of your choice
SQL> SET EXPLAIN OFF;

    • The equivalent is EXPLAIN(), which is much more difficult to use; you cannot just copy-and-paste a query, as all quotes must be doubled within the EXPLAIN(' ... '), as shown below: 

      SQL> explain('select * from table where field = ''text''');

  3. Generate compilation and execution plan of an SQL query: 

    SQL> SET PROFILE ON;
SQL> SELECT * FROM TABLE WHERE field = 'text';
     ---> Or execute query of your choice
SQL> SET PROFILE OFF;

    • The equivalent (available in Virtuoso 7 and later) is PROFILE(), which is much more difficult to use; you cannot just copy-and-paste a query, as all quotes must be doubled within the PROFILE(' ... '), as shown below: 

      SQL> profile('select * from table where field = ''text''');

  4. A more detailed query profile (compilation and execution) with cardinality can be obtained by executing the following following sequence 

    SQL> __dbf_set('enable_qr_comment', 1);             
SQL> __dbf_set('dbf_explain_level', 3);
SQL> SET PROFILE ON;
SQL> SET BLOBS ON;
SQL> SELECT * FROM TABLE WHERE field = 'text';
      ---> Or execute query of your choice
SQL> SET PROFILE OFF;

    Provide the generated plans for analysis.

Example

Here is a simple example of how to combine the two options to get a full explain() plan for a simple SPARQL query:

  1. Assume the following query: 

    SELECT * 
FROM <http://dbpedia.org> 
WHERE 
  {
    ?s a ?o
  } 
LIMIT 10

  2. Use the isql command line tool to connect to your database, and execute: 

    SQL> SET BLOBS ON;
        -- in case output is very large
SQL> SET SPARQL_TRANSLATE ON;
SQL> SELECT * FROM <http://dbpedia.org> WHERE {?s a ?o} LIMIT 10;

SPARQL_TO_SQL_TEXT
VARCHAR
_______________________________________________________________________________

SELECT TOP 10 __id2i ( "s_1_0-t0"."S" ) AS "s",
  __ro2sq ( "s_1_0-t0"."O" ) AS "o"
FROM DB.DBA.RDF_QUAD AS "s_1_0-t0"
WHERE "s_1_0-t0"."G" = __i2idn ( __bft( 'http://dbpedia.org' , 1))
  AND  "s_1_0-t0"."P" = __i2idn ( __bft( 'http://www.w3.org/1999/02/22-rdf-syntax-ns#type' , 1))
OPTION (QUIETCAST)

1 Rows. -- 1 msec.

SQL> SET SPARQL_TRANSLATE OFF;

  3. Use your mouse to select the query output, and paste it after the SET EXPLAIN ON; command. Then hit the ENTER key. 

    SQL> SET EXPLAIN ON;
SQL> SELECT TOP 10 __id2i ( "s_1_0-t0"."S" ) AS "s", __ro2sq ( "s_1_0-t0"."O" ) AS "o"
 FROM DB.DBA.RDF_QUAD AS "s_1_0-t0"
 WHERE "s_1_0-t0"."G" = __i2idn ( __bft( 'http://dbpedia.org' , 1))
   AND  "s_1_0-t0"."P" = __i2idn ( __bft( 'http://www.w3.org/1999/02/22-rdf-syn tax-ns#type' , 1))
 OPTION (QUIETCAST)
;

REPORT
VARCHAR
_______________________________________________________________________________

{ 
from DB.DBA.RDF_QUAD by RDF_QUAD_POGS    4.5e+05 rows   
Key RDF_QUAD_POGS  ASC ($22 "s_1_0-t0.S", $21 "s_1_0-t0.O")
 inlined <col=556 P =  #type >
row specs: <col=554 G =  #http://dbpedia.org >
 
 
After code:
      0: $25 "s" := Call __id2i ($22 "s_1_0-t0.S")
      5: $26 "o" := Call __ro2sq ($21 "s_1_0-t0.O")
      10: BReturn 0
Select (TOP  10 ) ($25 "s", $26 "o", <$24 "<DB.DBA.RDF_QUAD s_1_0-t0>" spec 5>)
}

13 Rows. -- 1 msec.

SQL> SET EXPLAIN OFF;

Related