Skip to Content
0

Performace tuning

Jan 24 at 03:48 PM

127

avatar image

Hello,

i need to estimate Cpu cost of query.

i have found this equation:

Cost = PIO X estimated_pio + LIO X estimated_lio + 100 X CPU/estimated_cpu

From show plan i can get the PIO,LIO, but no CPU.

Please advice.

Thank you.

Jakub. K.

Quest Software.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Mehrab Bucktowar
Jan 24 at 04:11 PM
0

Hi Jakub,

If you turn statistics io on, it should display the cpu cost. See :

https://help.sap.com/viewer/0fd37d130ef34da3974751332d4a11e7/16.0.1.0/en-US/a8ceaf21bc2b1014857eebe9c6627a94.html

In 16.0, it's reported as "CPU cost", Im earlier releases, it's "apf IOs used".

Hope this helps

Mehrab

Show 10 Share
10 |10000 characters needed characters left characters exceeded

Thank you Mehrab,

i am able now to see the apf IOs used.

But another problem is, that we are using showplan.

Is there some way how to use the Statistics IO and Showplan in the same time, or i need to execute the query twice?

If i use it this way, the Show plan is Null:

set plan for show_final_plan_xml, show_execio_xml to message on
set statistics IO ON
set showplan on 
select * from address_2
set showplan off
set statistics IO OFF
select showplan_in_xml(-1)
set plan for show_final_plan_xml, show_execio_xml off

Thank you.

J.

0

Hi Jakub,

That looks ok to me. What do you mean the "Show plan is Null" ?

I ran the following and got both statistics io and showplan on the same run :

1> set statistics io on
2> set showplan on
3> select * from sysusers
4> set showplan off
5> set statistics io off
6> go
Total writes for this command: 0
Total writes for this command: 0
QUERY PLAN FOR STATEMENT 1 (at line 3).
Optimized using Serial Mode

    STEP 1
        The type of query is SELECT.


        1 operator(s) under root


       |ROOT:EMIT Operator (VA = 1)
       |
       |   |SCAN Operator (VA = 0)
       |   |  FROM TABLE
       |   |  sysusers
       |   |  Table Scan.
       |   |  Forward Scan.
       |   |  Positioning at start of table.
       |   |  Using I/O Size 16 Kbytes for data pages.
       |   |  With LRU Buffer Replacement Strategy for data pages.

 suid        uid         gid         name                           environ                                                                                                                                                                                                                                                         user_status
 ----------- ----------- ----------- ------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
<<omitted>>

Table: sysusers scan count 1, logical reads: (regular=3 apf=0 total=3), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 0
(27 rows affected)
Total writes for this command: 0
0
Mehrab Bucktowar

We use the select showplan_in_xml(-1) procedure result for retrieving the Showplan xml and displaying the Show Plan graph from it.

When i set set showplan on i will get for select above Null result.

Please see the pic1/pic2.

pic1.png pic2.png

Thanks. J

pic1.png (35.0 kB)
pic2.png (21.6 kB)
0

We use the select showplan_in_xml(-1) procedure result for retrieving the Showplan xml and displaying the Show Plan graph from it.

When i set set showplan on i will get for select above Null result.

Please see the pic1/pic2.

pic1.png

pic2.png

Thanks. J

pic1.png (35.0 kB)
pic2.png (21.6 kB)
0

The 'set' command can be a bit tricky to use in that some 'set' commands take effect immediately, while other 'set' commands take effect in the next batch (eg, the 'set' commands that enable/disable additional optimizer/query-plan outputs).

While this is relatively easy to do with the 'isql' command line tool (ie, place a 'go' between batches), for GUIs it may require manually running different chunks of code (unless your GUI understands the 'go' command).

Applying this idea to your example code:

set plan for show_final_plan_xml, show_execio_xml to message on
set statistics IO ON
set showplan on 
go
select * from address_2
go
set showplan off
set statistics IO OFF
go
select showplan_in_xml(-1)
go
set plan for show_final_plan_xml, show_execio_xml off
go
0

Hi Mark,

thanks for answer, but anyway i tried this example code on the ASE 15.7 and 16 via isql and our product and i still have no result for Select ShowPlan.

If i use Go statements in between, the isql will not return any result, only output.

Without the Go statements the isql will return Null as result for Select ShowPlan.

J.

0

Jakub,

Can you add TF 3604 to your script and rerun it in isql?

set switch on 3604

- Mehrab

0

Ok Mehab,

i have put the set switch on 3604 as the first line.

Here is the: output.txt

The result for showplan is still the same.

J.

output.txt (7.4 kB)
0

This is how it looks for me. See attached.mytest.txt

mytest.txt (7.3 kB)
0

Please see the second thread bellow.

J

0
Filip Cevela Feb 08 at 12:45 PM
0

Hi Mehrab,

thanks for help, but i have still few questions...

1) So "CPU cost" == "apf IOs used"? Its only table related, isn't maybe better way for me, how to express the 'DB use' for statement:

"Total estimated I/O cost for statement 1 (at line 5): xxxxx ." ?

- Why the apf IOs used is all the time = 0?

2) Why the "select showplan_in_xml(-1) " result is NULL, when "set statistics IO ON" .. i would prefer to have showplan as a result.

3) Why some selects into tables with statistic on doesn't returns <AbstractPlan>,<Costs>? It seems to be useful information for performance debugging...(you can see on attached image.)

4) Why is there no ShowPlan, when i do select into empty table using ADO.Net Client (sampleApp), in iSQL there is.
(you can see on attached image.)

Thank you.

J.


questions.png (288.3 kB)
Share
10 |10000 characters needed characters left characters exceeded