cancel
Showing results for 
Search instead for 
Did you mean: 

How to get query plan in Sybase IQ for running process

Former Member
0 Kudos

There 100 of processes are running . I want to see query plan for particular connection id  .

like we have in sybase ASE ..   sp_showplan <SPID>

I know we  query plan optio  as below  ( either they will send out to msg file/standrad out)

1) set temporty option query_plan =on'   -- this will work for query running in that session

2) 

set temporary option query_plan_as_html = on

set option query_plan_as_html_directory = '<directory>'

I want to know if something like sp_showplan ...?

Appreciate for your help ...

Regards

Ajay Pandey

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member
0 Kudos

Thanks Tyeb ,Volker and Chris .

Regards

Ajay Pandey

c_baker
Employee
Employee
0 Kudos

First off, If you have

set option public.Query_Plan_As_HTML='off';

and only set the option temporary on your connection, then all query plans you see (and if you also change the directory) will be for your connection.

There is no sp_showplan.  The HTML query plans are IQ's graphical query plans.

You can also use the Query_Name option to add more detail to the HTML plan name (e.g. query name).  This can be very useful when trying to see which parts of a query were sent to worker nodes for DQP in multiplex.

You should also look at the other query plan options:

Query_Plan_After_Run='on';

dml_options10='on';

Query_Detail='on';

Query_Timing='on';

Temporary or permanent does not matter.  Make sure that public.Query_Plan='off';  This only outputs to the .iqmsg file and is not necessary for HTML query plans to work.  It also will fill up the .iqmsg file.

Also, turn on index advisor.  It can help pinpoint problems.  For IQ16, use HGs instead of LFs when suggested.

Chris

former_member194571
Active Participant
0 Kudos

Hi Ajay,

you might want to check out the LastPlanText connection property. It is a SQL Anywhere function, so I don't know if it returns anything helpful on IQ. If it does, I think it's the closest thing to sp_showplan you can get.

This requires a special server option -zp to be set. According to manuals, it can be set for a running server using the sa_server_option system procedure but I was not always successful with it. It can be accompanied by the LastStatement connection property which requires the -zl server option.

For an overview, you can select and filter on the sa_conn_properties result set

> select * from sa_conn_properties () [where ...]

HTH

Volker

tayeb_hadjou
Advisor
Advisor
0 Kudos

Hi Ajay,

I'm not aware of such function or option in current IQ releases.

Alternatively, if your concern is avoid permanent plan setting, then I could suggest set a threshold.

In this way, ONLY long query executions will have plan printed:

QUERY_PLAN_MIN_TIME Option - Reference: Statements and Options - SAP Library

Regards,

Tayeb.