2450023 - Inaccurate outputs for SET OPTION SHOW subcommands using the brief option - SAP ASE | SAP Knowledge Base Article

SAP Knowledge Base Article - Preview

2450023 - Inaccurate outputs for SET OPTION SHOW subcommands using the brief option - SAP ASE

Symptom

  • Inaccurate outputs for the following SET OPTION SHOW subcommands when using the brief option:

set option  show_best_plan  brief
set option  show_final_plan brief
set option  show_lio_costing brief
set option  show_elimination brief
set option  show_parallel brief 

  • "show_best_plan  brief" shows no information about Best Plan. It's only about Final Plan:

set option  show_best_plan  brief
select
         substring(au_lname,1,15)  "Last Name",
         substring(au_fname,1,15) "First Name",
         substring ( title ,1,40)  "Title"
from
         authors a ,titleauthor ta ,titles t
where
         a.au_id = ta.au_id and
         ta.title_id = t.title_id
order by a.au_lname

The best global plan (Pop tree) :

FINAL PLAN ( total cost = 217.5558 ):
        lio=33 pio=2.999999 cpu=765.5587
( PopEmit ( PopSort ( PopMergeJoin ( PopSort ( PopMergeJoin ( PopSort ( PopTabScan titles t ) ) ( PopSort ( PopTabScan titleauthor ta ) ) ) ) ( PopSort ( PopTabScan authors a ) ) ) ) ) cost:217.5558 T(L33,P2.999999,C765.5587) order: {3}

  • "show_final_plan brief" shows no information about Final Plan. it's about NO STATS:

set option  show_final_plan brief
select
         substring(au_lname,1,15)  "Last Name",
         substring(au_fname,1,15) "First Name",
         substring ( title ,1,40)  "Title"
 from
         authors a ,titleauthor ta ,titles t
 where
         a.au_id = ta.au_id and
         ta.title_id = t.title_id
order by a.au_lname
NO STATS on column authors.au_id
NO STATS on column authors.au_lname
NO STATS on column titleauthor.title_id
NO STATS on column titleauthor.au_id
NO STATS on column titles.title_id

  • "show_lio_costing brief" shows no information about show_lio_costing. It's about FINAL PLAN:

set option show_lio_costing brief
select
         substring(au_lname,1,15)  "Last Name",
         substring(au_fname,1,15) "First Name",
         substring ( title ,1,40)  "Title"
 from
         authors a ,titleauthor ta ,titles t
 where
         a.au_id = ta.au_id and
         ta.title_id = t.title_id
 order by a.au_lname
The Cost Summary for best global plan:

FINAL PLAN ( total cost = 217.5558 ):
        lio=33 pio=2.999999 cpu=765.5587

  • "show_elimination brief" shows no information about partition elimination:

set option show_elimination brief
select
         substring(au_lname,1,15)  "Last Name",
         substring(au_fname,1,15) "First Name",
         substring ( title ,1,40)  "Title"
 from
         authors a ,titleauthor ta ,titles t
 where
         a.au_id = ta.au_id and
         ta.title_id = t.title_id

 order by a.au_lname
        Predicate(s) in OptBlock0 for elimination
        START Dumping Predicate Set
 
        END Dumping Predicate Set

        Creating predicate intervals.......

        printing the interval list.....
        ................done

  • "show_parallel brief" shows no information about parallelism:

set option  show_parallel brief
select
         substring(au_lname,1,15)  "Last Name",
         substring(au_fname,1,15) "First Name",
         substring ( title ,1,40)  "Title"
 from
         authors a ,titleauthor ta ,titles t
 where
         a.au_id = ta.au_id and
         ta.title_id = t.title_id

 order by a.au_lname

        Predicate(s) in OptBlock0 for elimination
        START Dumping Predicate Set
 
        END Dumping Predicate Set

        Creating predicate intervals.......

        printing the interval list.....
        ................done


Read more...

Environment

  • SAP Adaptive Server Enterprise (ASE) 15.7
  • SAP Adaptive Server Enterprise 16.0

Product

SAP Adaptive Server Enterprise 12.5 ; SAP Adaptive Server Enterprise 15.7 ; SAP Adaptive Server Enterprise 16.0

Keywords

CR806853, CR#806853, 806853 , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Bug Filed

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP ONE Support launchpad (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.