Skip to Content
0

how to explain a show plan difference executed on two dataservers

Nov 04, 2016 at 07:21 PM

49

avatar image

Hi community,

I'm requesting your inputs about a difference regarding the execution of a sql statement done on my environment VS on client site.

First of all the database has the same seed. We did a bunch on work on it then we send it to the client.

Client is complaining about a slowness, provides me a test case and i was able to isolate a query.

I asked him to execute the script 'sybase-quick-sql-analysissql.txt' to get the showplan and statistics in terms of timing and io.


The client output (sybase-quick-sql-analysis-at-clientlog.txt) is clearly different from mine (sybase-quick-sql-analysis-at-officelog.txt). My assumption is the difference is mainly coming from the worktable + table scan on TRN_HDR_DBF (more than 2M rows).

Now I'm trying to find an explanation to such difference. I compared the dataserver configuration, there are some differences (data cache / statement cache / procedure cache bigger on my dataserver) but I don't if there are any ways to pinpoint that this or these configuration differences explain the behaviour.

I asked the client to execute an 'update index statitstics TRN_HDR_DBF' and rerun the sqls but didn't improve anything.

Last i compare TRN_HDR_DBF optdiag output on their side VS on my side but didn't notice any big differences (columns with histograms have the same number of steps and the metrics are quite similar) -- sorry couldn't upload optdiag output --

Now I'm a bit stuck, I don't know with settings/flags i could activate to get additional information to explain this difference and I'm requesting your help/advices to move forward.

Thanks in advance

Simon

10 |10000 characters needed characters left characters exceeded

Hi,

Something interesting. With my original 'default data cache' configuration (10Gb), the SQL statement was fast.

If I setup 'default data cache' as it's on client dataserver, the query is slow and a TABLE SCAN is selected for TRN_HDR_DBF. Then I used 'set option show_missing_stats on' and execute the query again. I notice statistics were missing for TRN_HDR_DBF.M_PURPOSE. Therefore I ran 'update statistics TRN_HDR_DNF(M_PURPOSE) and rerun the query and it solves my issue.

Is there any flags that will help me understanding why without the stats the optimizer considers a TABLE SCAN and with the statistics he considered to use an INDEX 9and why this index and not another one)

Thanks

Simon

0
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Mark A Parsons Nov 04, 2016 at 08:47 PM
1

One obvious (?) question: Does the client have the same indexes on the TRN_HDR_DBF table?

It does seem kinda odd that the client's stats are up to date (right?) but its estimates for a T1 table scan are so far off:

TableScan
TRN_HDR_DBF (T1)
(VA = 0)
r:2.488e+06 er:84561      <<<==== that's a *BIG* difference
l:837867 el:418926 
p:70622 ep:52370

Something's fishy with the client's stats ...

----------

I have seen differences in caches cause varying query plans. For the client query the optimizer seems to think a table scan using large IOs in the 32K pool will be faster, while your query seems to think an index scan using smaller IOs in the 4KB pool will be faster. ("Duh, Mark!" ?)

Since you already know your current cache settings allow for a more efficient query plan, I'd suggest you try reconfiguring your caches (cache size, # partitions, pool sizes, APF/wash settings, cache type, cache bindings) to look identical to the client, with the objective being to recreate the client's query results. If you can recreate the clients results you can then focus on the differences in the two cache settings ... tweaking yours until you find the threshold at which the query plan changes.

Share
10 |10000 characters needed characters left characters exceeded
Simon SOUVANNARAT Nov 08, 2016 at 03:19 PM
0

Thanks Mark for your answer,

The table TRN_HDR_DBF has the same indices on both dataservers (sp_helpindex TRN_HDR_DBF has the same output). Thank you for your advice, I was able to reproduce the client behaviour by 'mimicing' its defaut data cache configuration

1> sp_cacheconfig
2> go
Cache Name Status Type Config Value Run Value
------------------ -------- -------- -------------- ------------
default data cache Active Default 4096.00 Mb 4096.00 Mb

(1 row affected)
------------ ------------
Total 4096.00 Mb 4096.00 Mb
==========================================================================
Cache: default data cache, Status: Active, Type: Default
Config Size: 4096.00 Mb, Run Size: 4096.00 Mb
Config Replacement: strict LRU, Run Replacement: strict LRU
Config Partition: 4, Run Partition: 4
IO Size Wash Size Config Size Run Size APF Percent
-------- ------------- ------------ ------------ -----------
4 Kb 245760 Kb 1496.00 Mb 1496.00 Mb 20
32 Kb 1048576 Kb 2600.00 Mb 2600.00 Mb 48

Now I have a concern when you say "tweaking yours until you find the threshold at which the query plan changes.". I'm going to run some test by changing my cache configuration but I'm wondering if there are some commands/flags I could use to provide me additional information to understand why it's using a TABLE SCAN on TRN_HDR_DBF and why it'll user an INDEX on the same table if I change my cache configuration?

Thank you

Simon

Share
10 |10000 characters needed characters left characters exceeded