how to explain a show plan difference executed on two dataservers

[Former Member]

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

[Former Member]

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

[Former Member]

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