Easily find issues by searching: #<Issue ID>
Example: #1832
Easily find members by searching in: <username>, <first name> and <last name>.
Example: Search smith, will return results smith and adamsmith
| Dev |
|
| QA |
|
| Priority | Major |
| Complexity | Unknown |
| Component | Q Analyzer - Visual Explain Plan |
| Version | 17.0 |
Connection info: Sybase ASE 15.7; 10.1.1.92. Queries:
CREATE TABLE dbo.xml_test ( artist varchar(20) NOT NULL, library varchar(30) NOT NULL, composer varchar(30) NOT NULL, created datetime NOT NULL, updated datetime NULL ) GO ALTER TABLE dbo.xml_test ADD CONSTRAINT PK_xml_test UNIQUE (created, artist, library) WITH max_rows_per_page = 0, reservepagegap = 0 GO declare @period_dt varchar(10) set @period_dt='05/03/2017 12:00:00.000 AM' SELECT * FROM dbo.xml_test xt WHERE xt.created < dateadd(dd, 1, @period_dt) GROUP BY xt.artist go SELECT * FROM dbo.xml_test xt WHERE xt.created < dateadd(dd, 1, '05/03/2017 12:00:00.000 AM') GROUP BY xt.artist
|
|
561 KB
|
|
595 KB
|
|
166 KB
|
|
193 KB
@kin-hong :
I have attached a file with the xml string that is returned by the database. You will notice that it has multiple xml plans in it. If you take a look at AFExplainPlanUtil at line 1161:
if (x < 20 && plans[x] != null) { AFSybaseIQExplainPlan node = new AFSybaseIQExplainPlan(plannum, null, AFSybaseIQExplainPlan.XML_TYPE, plans[x]); plan.add(node); map.put(plannum++, plan); }
In this block the string array has 2 plans in it. plans[0] and plans[1] but the if block only puts the first plan in the map. the second plan is discarded. I am pasting below the plan execution statement in sequence if you wish to view the xml in resultset in a query analzyer:
set plan for show_execio_xml to message on GO declare @period_dt varchar(10) set @period_dt='05/03/2017 12:00:00.000 AM' SELECT * FROM dbo.xml_test xt WHERE xt.created < dateadd(dd, 1, @period_dt) GROUP BY xt.artist GO select showplan_in_xml(0) GO set plan for show_execio_xml off GO
It looks as if the code is attempting to assign each statement one plan, but in this case there are 2 plans for that 1 statement. Some code redesign may be required...
I think the main issue is Sybase ASE produces an explain plan XML for the "set @period_dt=..." statement also. Here is a summary of what I found:
| Script to run | ADS statement parser | Sybase ASE | Comment |
|
set @variable=... |
produces 1 statement | produces 2 plans, one for each statement | need to pick the 2nd plan to process |
|
set @variable1=... set @variable2=... SELECT .... |
produces 1 statement | produces 3 plans, one for each statement | need to pick the 3rd plan to process |
|
set @variable=... |
produces 2 statements | produces 4 plans, one for each statement | need to pick the 2nd and 4th plans to process |
I think the main issue is Sybase ASE produces an explain plan XML for the "set @period_dt=..." statement also. Here is a summary of what I found:
| Script to run | ADS statement parser | Sybase ASE | Comment |
|
set @variable=... |
produces 1 statement | produces 2 plans, one for each statement | need to pick the 2nd plan to process |
|
set @variable1=... set @variable2=... SELECT .... |
produces 1 statement | produces 3 plans, one for each statement | need to pick the 3rd plan to process |
|
set @variable=... |
produces 2 statements | produces 4 plans, one for each statement | need to pick the 2nd and 4th plans to process |
@qa: test on each platform: OS X, Windows & Linux.
@qa: test on each platform: OS X, Windows & Linux.
We will display upto 20 explain plans. When the statements are not separated by statement separators such as "GO" or ";" or which the user has set, we will still parse and show all the explain plans returned. This means that if there is a single statement executed but 5 plans returned we will display all 5 plans now.
Code changes checked in to US trunk, v18, v17 with Committed revision: 54278, 54275, 54277
We will display upto 20 explain plans. When the statements are not separated by statement separators such as "GO" or ";" or which the user has set, we will still parse and show all the explain plans returned. This means that if there is a single statement executed but 5 plans returned we will display all 5 plans now.
Code changes checked in to US trunk, v18, v17 with Committed revision: 54278, 54275, 54277
Verified in ADS v19.0.0-alpha-6 and v18.0.13
Verified in ADS v19.0.0-alpha-6 and v18.0.13
Issue #15204 |
| Closed |
| Fixed |
| Resolved |
Completion |
| No due date |
| Fixed Build v19.0.0-alpha-3, v18.0.12, v17.0.12-6 |
| No time estimate |
@kin-hong :
I have attached a file with the xml string that is returned by the database. You will notice that it has multiple xml plans in it. If you take a look at AFExplainPlanUtil at line 1161:
In this block the string array has 2 plans in it. plans[0] and plans[1] but the if block only puts the first plan in the map. the second plan is discarded. I am pasting below the plan execution statement in sequence if you wish to view the xml in resultset in a query analzyer:
It looks as if the code is attempting to assign each statement one plan, but in this case there are 2 plans for that 1 statement. Some code redesign may be required...