SqlDbx
You are not logged in.
I'm using SqlDBx Professional v3.30
I did click on both Query Statistics ON/OFF and Query Plan ON/OFF button, then executed my SQL. I still only get the data results without any mention of the Explain/Query Plan information. Where do I go to see that? Thanks.
Offline
What database you use?
Offline
This is an old thread but I had the same problem in version 4.6 so I decided to post the reason for this behaviour, on Oracle environment.
Please consider adding config option for custom PLAN_TABLE path (define custom scheme and plan table name), like TOAD does. Many of us connect to a DB using some Admin user who has rights to select many different schemes, so we use one user to connect, but many other user schemes to execute queries. Since SQLDbx config does not support custom PLAN_TABLE table path, it uses the PLAN_TABLE in the user scheme you're using for connecting to the database and it causes problems.
So for example, if you're using a user USER1 for DB connection, SQLDbx will insert and read Query plan from USER1.PLAN_TABLE, which is OK if you're executing queries from USER1. But if you're connected to USER1, but executing queries in another scheme e.g. USER2, it will still write Query plan to USER1.PLAN_TABLE, but will try to read it from USER2.PLAN_TABLE (!), <b>so it will not work - no Query plan will show up</b>.
By using a "default" query plan table, SQLDbx could always read/write to that table.
Also, info for all others. Your PLAN_TABLE must be defined like this (I've had some old PLAN_TABLE definitions on some schemes so some columns were always missing):
CREATE GLOBAL TEMPORARY TABLE USER1.PLAN_TABLE
(
STATEMENT_ID VARCHAR2 (30),
PLAN_ID NUMBER,
"TIMESTAMP" DATE,
REMARKS VARCHAR2 (4000),
OPERATION VARCHAR2 (30),
OPTIONS VARCHAR2 (255),
OBJECT_NODE VARCHAR2 (128),
OBJECT_OWNER VARCHAR2 (30),
OBJECT_NAME VARCHAR2 (30),
OBJECT_ALIAS VARCHAR2 (65),
OBJECT_INSTANCE INTEGER,
OBJECT_TYPE VARCHAR2 (30),
OPTIMIZER VARCHAR2 (255),
SEARCH_COLUMNS NUMBER,
ID INTEGER,
PARENT_ID INTEGER,
DEPTH INTEGER,
POSITION INTEGER,
COST INTEGER,
CARDINALITY INTEGER,
BYTES INTEGER,
OTHER_TAG VARCHAR2 (255),
PARTITION_START VARCHAR2 (255),
PARTITION_STOP VARCHAR2 (255),
PARTITION_ID INTEGER,
OTHER LONG,
DISTRIBUTION VARCHAR2 (30),
CPU_COST INTEGER,
IO_COST INTEGER,
TEMP_SPACE INTEGER,
ACCESS_PREDICATES VARCHAR2 (4000),
FILTER_PREDICATES VARCHAR2 (4000),
PROJECTION VARCHAR2 (4000),
"TIME" INTEGER,
QBLOCK_NAME VARCHAR2 (30),
OTHER_XML CLOB
)
ON COMMIT PRESERVE ROWS;
Last edited by timeco (2015-01-03 07:07:50)
Offline
SqlDbx does not use any schema specific TABLE_PLAN tables.
It just uses whatever unqualified name TABLE_PLAN means.
It can be table in current schema or public synonym.
Looks like using unqualified name has issues in some instances.
It should not be a problem to add option to specify PLAN_TABLE.
Offline