Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

optimizer changes ASE 15.02 ESD#4 vs ESD#2 ?

22 posts in Performance and Tuning Last posting was on 2008-07-29 15:24:40.0Z
Isabella Posted on 2008-06-06 14:35:38.0Z
From: Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
User-Agent: Thunderbird 1.5.0.12 (X11/20070530)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: optimizer changes ASE 15.02 ESD#4 vs ESD#2 ?
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48494b3a$1@forums-1-dub>
Date: 6 Jun 2008 07:35:38 -0700
X-Trace: forums-1-dub 1212762938 10.22.241.152 (6 Jun 2008 07:35:38 -0700)
X-Original-Trace: 6 Jun 2008 07:35:38 -0700, vip152.sybase.com
Lines: 187
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10909
Article PK: 89520

Hello All,
We are in processing to switch to ASE 15.02 from ASE 12.5 Linux , we
ran our tests in April against ASE15.02 ESD#2, meanwhile after we aplied
the last ESD#4 in late April, a we found some issues with one of the
queries...We didn't re-test everything against new ESD#4.
Here is the the SQL and the showplan output in both ESD,
Mentioned running the SQL in ESD #2 after forcing the optimizer to user
dss goal , takes < 1 min !! big improvement from ASE 12.5.3 BUT running
same SQL in ASE 15.02 ESD #4 , optimizer is doing a FULL tables scan
and takes > 40 MIN !!!
What can be wrong , I delete and update index stats in Ase 15.02
ESD#4, the biggest table is partition and see size
name rowtotal reserved data index_size unused
--------- -------- ----------- ---------- ---------- --------
63936221 13516222 KB 7846458 KB 5640878 KB 28886 KB


ASE 15.02 ESD #2 , exec time < 1min


select m.media_type, m.mounted,
2> archive, file_id, format, fsize,
uncomp_fsize,
3> stream, archive_status, status,
compression
4> from mfs_files f
5> join mfs_volume v on v.volume_id =
f.volume_id
6> join mfs_media m on v.media_id =
m.media_id
7> order by file_id
8> plan '(use optgoal allrows_dss)'
9> go

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SELECT.

9 operator(s) under root

|ROOT:EMIT Operator (VA = 9)

|
| |SEQUENCER Operator (VA = 8) has 2 children.
| |
| | |STORE Operator (VA = 4)
| | | Worktable2 created, in allpages locking mode, for
REFORMATTING.
| | | Creating clustered index.
| | |
| | | |INSERT Operator (VA = 3)
| | | | The update mode is direct.
| | | |
| | | | |HASH JOIN Operator (VA = 2) (Join Type: Inner Join)
| | | | | Using Worktable1 for internal storage.
| | | | | Key Count: 1
| | | | |
| | | | | |SCAN Operator (VA = 0)
| | | | | | FROM TABLE
| | | | | | mfs_media
| | | | | | m
| | | | | | 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.
| | | | |
| | | | | |SCAN Operator (VA = 1)
| | | | | | FROM TABLE
| | | | | | mfs_volume
| | | | | | v
| | | | | | 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.
| | | |
| | | | TO TABLE
| | | | Worktable2.
| |
| | |NESTED LOOP JOIN Operator (VA = 7) (Join Type: Inner Join)
| | |
| | | |SCAN Operator (VA = 5)
| | | | FROM TABLE
| | | | mfs_files
| | | | f
| | | | Index : mfs_files_file_id
| | | | Forward Scan.
| | | | Positioning at index start.
| | | | Using I/O Size 16 Kbytes for index leaf pages.
| | | | With LRU Buffer Replacement Strategy for index
leaf pages.
| | | | Using I/O Size 16 Kbytes for data pages.
| | | | With MRU Buffer Replacement Strategy for data pages.
| | |
| | | |SCAN Operator (VA = 6)
| | | | FROM TABLE
| | | | Worktable2.
| | | | Using Clustered Index.
| | | | Forward Scan.
| | | | Positioning by key.
| | | | Using I/O Size 16 Kbytes for data pages.
| | | | With LRU Buffer Replacement Strategy for data pages.


Total estimated I/O cost for statement 1 (at line 1): 571497253.


*************************************************************************************

NOW ASE 15.02 ESD#4 , exec time > 40 min , FULL table SCAN !!

select m.media_type, m.mounted,
2> archive, file_id, format, fsize,
uncomp_fsize,
3> stream, archive_status, status,
compression
4> from mfs_files f
5> join mfs_volume v on v.volume_id =
f.volume_id
6> join mfs_media m on v.media_id =
m.media_id
7> order by file_id
8> plan '(use optgoal allrows_dss)'
9> go

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SELECT.

6 operator(s) under root

|ROOT:EMIT Operator (VA = 6)
|
| |SORT Operator (VA = 5)
| | Average Row width is 56.846985
| | Using Worktable3 for internal storage.
| |
| | |HASH JOIN Operator (VA = 4) (Join Type: Inner Join)
| | | Using Worktable2 for internal storage.
| | | Key Count: 1
| | |
| | | |HASH JOIN Operator (VA = 2) (Join Type: Inner Join)
| | | | Using Worktable1 for internal storage.
| | | | Key Count: 1
| | | |
| | | | |SCAN Operator (VA = 0)
| | | | | FROM TABLE
| | | | | mfs_media
| | | | | m
| | | | | 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.
| | | |
| | | | |SCAN Operator (VA = 1)
| | | | | FROM TABLE
| | | | | mfs_volume
| | | | | v
| | | | | 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.
| | |
| | | |SCAN Operator (VA = 3)
| | | | FROM TABLE
| | | | mfs_files
| | | | f
| | | | Table Scan.
| | | | Forward Scan.
| | | | Positioning at start of table.
| | | | Using I/O Size 16 Kbytes for data pages.
| | | | With MRU Buffer Replacement Strategy for data pages.


Total estimated I/O cost for statement 1 (at line 1): 369276437.

*************************************************************************


dbMethods Posted on 2008-06-07 15:52:44.0Z
Message-ID: <484AAEB6.7030806@nospam.com>
From: dbMethods <dbmethods@nospam.com>
User-Agent: Thunderbird 1.5.0.14 (Windows/20071210)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
To: Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
Subject: Re: optimizer changes ASE 15.02 ESD#4 vs ESD#2 ?
References: <48494b3a$1@forums-1-dub>
In-Reply-To: <48494b3a$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 7 Jun 2008 08:52:44 -0700
X-Trace: forums-1-dub 1212853964 10.22.241.152 (7 Jun 2008 08:52:44 -0700)
X-Original-Trace: 7 Jun 2008 08:52:44 -0700, vip152.sybase.com
Lines: 200
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10910
Article PK: 89521

Can you post
sp_spaceused mfs_files
go
sp_helpindex mfs_files
go
sp_help mfs_files
go

In this query, does the result set contain large object, such text or image?

Isabella wrote:
> Hello All,
> We are in processing to switch to ASE 15.02 from ASE 12.5 Linux , we
> ran our tests in April against ASE15.02 ESD#2, meanwhile after we aplied
> the last ESD#4 in late April, a we found some issues with one of the
> queries...We didn't re-test everything against new ESD#4.
> Here is the the SQL and the showplan output in both ESD,
> Mentioned running the SQL in ESD #2 after forcing the optimizer to user
> dss goal , takes < 1 min !! big improvement from ASE 12.5.3 BUT running
> same SQL in ASE 15.02 ESD #4 , optimizer is doing a FULL tables scan
> and takes > 40 MIN !!!
> What can be wrong , I delete and update index stats in Ase 15.02 ESD#4,
> the biggest table is partition and see size
> name rowtotal reserved data index_size unused
> --------- -------- ----------- ---------- ---------- --------
> 63936221 13516222 KB 7846458 KB 5640878 KB 28886 KB
>
>
> ASE 15.02 ESD #2 , exec time < 1min
>
>
> select m.media_type, m.mounted,
> 2> archive, file_id, format, fsize,
> uncomp_fsize,
> 3> stream, archive_status, status,
> compression
> 4> from mfs_files f
> 5> join mfs_volume v on v.volume_id =
> f.volume_id
> 6> join mfs_media m on v.media_id =
> m.media_id
> 7> order by file_id
> 8> plan '(use optgoal allrows_dss)'
> 9> go
>
> QUERY PLAN FOR STATEMENT 1 (at line 1).
>
>
> STEP 1
> The type of query is SELECT.
>
> 9 operator(s) under root
>
> |ROOT:EMIT Operator (VA = 9)
> |
> | |SEQUENCER Operator (VA = 8) has 2 children.
> | |
> | | |STORE Operator (VA = 4)
> | | | Worktable2 created, in allpages locking mode, for
> REFORMATTING.
> | | | Creating clustered index.
> | | |
> | | | |INSERT Operator (VA = 3)
> | | | | The update mode is direct.
> | | | |
> | | | | |HASH JOIN Operator (VA = 2) (Join Type: Inner Join)
> | | | | | Using Worktable1 for internal storage.
> | | | | | Key Count: 1
> | | | | |
> | | | | | |SCAN Operator (VA = 0)
> | | | | | | FROM TABLE
> | | | | | | mfs_media
> | | | | | | m
> | | | | | | 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.
> | | | | |
> | | | | | |SCAN Operator (VA = 1)
> | | | | | | FROM TABLE
> | | | | | | mfs_volume
> | | | | | | v
> | | | | | | 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.
> | | | |
> | | | | TO TABLE
> | | | | Worktable2.
> | |
> | | |NESTED LOOP JOIN Operator (VA = 7) (Join Type: Inner Join)
> | | |
> | | | |SCAN Operator (VA = 5)
> | | | | FROM TABLE
> | | | | mfs_files
> | | | | f
> | | | | Index : mfs_files_file_id
> | | | | Forward Scan.
> | | | | Positioning at index start.
> | | | | Using I/O Size 16 Kbytes for index leaf pages.
> | | | | With LRU Buffer Replacement Strategy for index
> leaf pages.
> | | | | Using I/O Size 16 Kbytes for data pages.
> | | | | With MRU Buffer Replacement Strategy for data pages.
> | | |
> | | | |SCAN Operator (VA = 6)
> | | | | FROM TABLE
> | | | | Worktable2.
> | | | | Using Clustered Index.
> | | | | Forward Scan.
> | | | | Positioning by key.
> | | | | Using I/O Size 16 Kbytes for data pages.
> | | | | With LRU Buffer Replacement Strategy for data pages.
>
>
> Total estimated I/O cost for statement 1 (at line 1): 571497253.
>
>
> *************************************************************************************
>
>
> NOW ASE 15.02 ESD#4 , exec time > 40 min , FULL table SCAN !!
>
> select m.media_type, m.mounted,
> 2> archive, file_id, format, fsize,
> uncomp_fsize,
> 3> stream, archive_status, status,
> compression
> 4> from mfs_files f
> 5> join mfs_volume v on v.volume_id =
> f.volume_id
> 6> join mfs_media m on v.media_id =
> m.media_id
> 7> order by file_id
> 8> plan '(use optgoal allrows_dss)'
> 9> go
>
> QUERY PLAN FOR STATEMENT 1 (at line 1).
>
>
> STEP 1
> The type of query is SELECT.
>
> 6 operator(s) under root
>
> |ROOT:EMIT Operator (VA = 6)
> |
> | |SORT Operator (VA = 5)
> | | Average Row width is 56.846985
> | | Using Worktable3 for internal storage.
> | |
> | | |HASH JOIN Operator (VA = 4) (Join Type: Inner Join)
> | | | Using Worktable2 for internal storage.
> | | | Key Count: 1
> | | |
> | | | |HASH JOIN Operator (VA = 2) (Join Type: Inner Join)
> | | | | Using Worktable1 for internal storage.
> | | | | Key Count: 1
> | | | |
> | | | | |SCAN Operator (VA = 0)
> | | | | | FROM TABLE
> | | | | | mfs_media
> | | | | | m
> | | | | | 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.
> | | | |
> | | | | |SCAN Operator (VA = 1)
> | | | | | FROM TABLE
> | | | | | mfs_volume
> | | | | | v
> | | | | | 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.
> | | |
> | | | |SCAN Operator (VA = 3)
> | | | | FROM TABLE
> | | | | mfs_files
> | | | | f
> | | | | Table Scan.
> | | | | Forward Scan.
> | | | | Positioning at start of table.
> | | | | Using I/O Size 16 Kbytes for data pages.
> | | | | With MRU Buffer Replacement Strategy for data pages.
>
>
> Total estimated I/O cost for statement 1 (at line 1): 369276437.
>
> *************************************************************************


Isabella Posted on 2008-06-09 15:10:51.0Z
Message-ID: <484D47FB.1040007@nrc-cnrc.gc.ca>
From: Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
User-Agent: Thunderbird 1.5.0.12 (X11/20070530)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
To: dbMethods <dbmethods@nospam.com>
Subject: Re: optimizer changes ASE 15.02 ESD#4 vs ESD#2 ?
References: <48494b3a$1@forums-1-dub> <484AAEB6.7030806@nospam.com>
In-Reply-To: <484AAEB6.7030806@nospam.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 9 Jun 2008 08:10:51 -0700
X-Trace: forums-1-dub 1213024251 10.22.241.152 (9 Jun 2008 08:10:51 -0700)
X-Original-Trace: 9 Jun 2008 08:10:51 -0700, vip152.sybase.com
Lines: 377
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10911
Article PK: 89525

Hello,
please see answerers to your questions:

Any suggestions what's going on ?

Isabella
sp_spaceused mfs_files
2> go
name rowtotal reserved data index_size unused
--------- -------- ----------- ---------- ---------- --------
mfs_files 67707267 14406056 KB 8356950 KB 6021132 KB 27974 KB

sp_helpindex mfs_files
2> go
Object has the following indexes

index_name index_keys index_description
index_max_rows_per_page index_fillfactor index_reservepagegap
index_created index_local
----------------- ----------------- --------------------
----------------------- ---------------- --------------------
------------------- ------------
mfs_files_crc volume_id, fname nonclustered, unique
0 0 0 Jun 5 2008 9:25AM
Global Index
mfs_files_file_id file_id, status nonclustered
0 0 0 Jun 5 2008 9:25AM
Global Index
dataset_name dataset_name nonclustered
0 0 0 Jun 5 2008 9:25AM
Global Index
ingest_date ingest_date nonclustered
0 0 0 Jun 5 2008 9:25AM
Global Index

(4 rows affected)
index_ptn_name index_ptn_seg
---------------------------- -------------
mfs_files_crc_1648005871 default
mfs_files_file_id_1648005871 default
dataset_name_1648005871 default
ingest_date_1648005871 default

sp_help mfs_files
2> go
Name Owner Object_type Create_date
--------- ----- ----------- -------------------
mfs_files dbo user table Apr 23 2005 4:20PM

(1 row affected)
Column_name Type Length Prec Scale Nulls Default_name Rule_name
Access_Rule_name Computed_Column_object Identity
-------------- -------- ------ ---- ----- ----- ------------ ---------
---------------- ---------------------- ----------
volume_id binary 4 NULL NULL 0 NULL NULL
NULL NULL 0
file_id varchar 255 NULL NULL 0 NULL NULL
NULL NULL 0
fname varchar 255 NULL NULL 0 NULL NULL
NULL NULL 0
fsize int 4 NULL NULL 0 NULL NULL
NULL NULL 0
file_crc binary 4 NULL NULL 1 NULL NULL
NULL NULL 0
format char 1 NULL NULL 0 NULL NULL
NULL NULL 0
compression char 1 NULL NULL 0 NULL NULL
NULL NULL 0
uncomp_fsize int 4 NULL NULL 0 NULL NULL
NULL NULL 0
uncomp_fcrc binary 4 NULL NULL 1 NULL NULL
NULL NULL 0
status char 1 NULL NULL 0 NULL NULL
NULL NULL 0
archive_status char 1 NULL NULL 1 NULL NULL
NULL NULL 0
ingest_date datetime 8 NULL NULL 1 NULL NULL
NULL NULL 0
dataset_name varchar 255 NULL NULL 1 NULL NULL
NULL NULL 0
Object has the following indexes


index_name index_keys index_description
index_max_rows_per_page index_fillfactor index_reservepagegap
index_created index_local
----------------- ----------------- --------------------
----------------------- ---------------- --------------------
------------------- ------------
mfs_files_crc volume_id, fname nonclustered, unique
0 0 0 Jun 5 2008 9:25AM
Global Index
mfs_files_file_id file_id, status nonclustered
0 0 0 Jun 5 2008 9:25AM
Global Index
dataset_name dataset_name nonclustered
0 0 0 Jun 5 2008 9:25AM
Global Index
ingest_date ingest_date nonclustered
0 0 0 Jun 5 2008 9:25AM
Global Index

(4 rows affected)
index_ptn_name index_ptn_seg
---------------------------- -------------
mfs_files_crc_1648005871 default
mfs_files_file_id_1648005871 default
dataset_name_1648005871 default
ingest_date_1648005871 default
No defined keys for this object.

(4 rows affected)
name type partition_type partitions partition_keys
--------- ---------- -------------- ---------- --------------
mfs_files base table roundrobin 10 NULL


(1 row affected)
partition_name partition_id pages row_count segment create_date
-------------------- ------------ ------ --------- -------
-------------------
mfs_files_1068527809 1068527809 417860 6770726 default Jun 5 2008
10:33AM
mfs_files_1084527866 1084527866 417843 6770727 default Jun 5 2008
10:33AM
mfs_files_1100527923 1100527923 417848 6770727 default Jun 5 2008
10:33AM
mfs_files_1116527980 1116527980 417847 6770727 default Jun 5 2008
10:33AM
mfs_files_1132528037 1132528037 417841 6770727 default Jun 5 2008
10:33AM
mfs_files_1148528094 1148528094 417823 6770727 default Jun 5 2008
10:33AM
mfs_files_1164528151 1164528151 417830 6770727 default Jun 5 2008
10:33AM
mfs_files_1180528208 1180528208 417863 6770727 default Jun 5 2008
10:33AM
mfs_files_1196528265 1196528265 417869 6770726 default Jun 5 2008
10:33AM
mfs_files_1212528322 1212528322 417851 6770726 default Jun 5 2008
10:33AM


Partition_Conditions
--------------------
NULL


Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ratio(Min/Avg)
----------- ----------- ----------- --------------------
--------------------
417847 417869 417823 1.000053
0.999943
Lock scheme Datarows
The 'ascinserts' attribute is not applicable to tables with datarow or
datapage lock schemes.


exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap
ascinserts
------------ -------------- ---------- ----------------- ------------
-----------
0 0 0 0 0
0

(1 row affected)
concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg
------------------------- --------------------- -------------------
0 0 0

dbMethods wrote:
> Can you post
> sp_spaceused mfs_files
> go
> sp_helpindex mfs_files
> go
> sp_help mfs_files
> go
>
> In this query, does the result set contain large object, such text or
> image?
>
>
> Isabella wrote:
>> Hello All,
>> We are in processing to switch to ASE 15.02 from ASE 12.5 Linux , we
>> ran our tests in April against ASE15.02 ESD#2, meanwhile after we
>> aplied the last ESD#4 in late April, a we found some issues with one
>> of the queries...We didn't re-test everything against new ESD#4.
>> Here is the the SQL and the showplan output in both ESD,
>> Mentioned running the SQL in ESD #2 after forcing the optimizer to
>> user dss goal , takes < 1 min !! big improvement from ASE 12.5.3 BUT
>> running same SQL in ASE 15.02 ESD #4 , optimizer is doing a FULL
>> tables scan and takes > 40 MIN !!!
>> What can be wrong , I delete and update index stats in Ase 15.02
>> ESD#4, the biggest table is partition and see size
>> name rowtotal reserved data index_size unused
>> --------- -------- ----------- ---------- ---------- --------
>> 63936221 13516222 KB 7846458 KB 5640878 KB 28886 KB
>>
>>
>> ASE 15.02 ESD #2 , exec time < 1min
>>
>>
>> select m.media_type, m.mounted,
>> 2> archive, file_id, format, fsize,
>> uncomp_fsize,
>> 3> stream, archive_status, status,
>> compression
>> 4> from mfs_files f
>> 5> join mfs_volume v on v.volume_id =
>> f.volume_id
>> 6> join mfs_media m on v.media_id =
>> m.media_id
>> 7> order by file_id
>> 8> plan '(use optgoal allrows_dss)'
>> 9> go
>>
>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>
>>
>> STEP 1
>> The type of query is SELECT.
>>
>> 9 operator(s) under root
>>
>> |ROOT:EMIT Operator (VA = 9)
>> |
>> | |SEQUENCER Operator (VA = 8) has 2 children.
>> | |
>> | | |STORE Operator (VA = 4)
>> | | | Worktable2 created, in allpages locking mode, for
>> REFORMATTING.
>> | | | Creating clustered index.
>> | | |
>> | | | |INSERT Operator (VA = 3)
>> | | | | The update mode is direct.
>> | | | |
>> | | | | |HASH JOIN Operator (VA = 2) (Join Type: Inner
>> Join)
>> | | | | | Using Worktable1 for internal storage.
>> | | | | | Key Count: 1
>> | | | | |
>> | | | | | |SCAN Operator (VA = 0)
>> | | | | | | FROM TABLE
>> | | | | | | mfs_media
>> | | | | | | m
>> | | | | | | 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.
>> | | | | |
>> | | | | | |SCAN Operator (VA = 1)
>> | | | | | | FROM TABLE
>> | | | | | | mfs_volume
>> | | | | | | v
>> | | | | | | 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.
>> | | | |
>> | | | | TO TABLE
>> | | | | Worktable2.
>> | |
>> | | |NESTED LOOP JOIN Operator (VA = 7) (Join Type: Inner
>> Join)
>> | | |
>> | | | |SCAN Operator (VA = 5)
>> | | | | FROM TABLE
>> | | | | mfs_files
>> | | | | f
>> | | | | Index : mfs_files_file_id
>> | | | | Forward Scan.
>> | | | | Positioning at index start.
>> | | | | Using I/O Size 16 Kbytes for index leaf pages.
>> | | | | With LRU Buffer Replacement Strategy for index
>> leaf pages.
>> | | | | Using I/O Size 16 Kbytes for data pages.
>> | | | | With MRU Buffer Replacement Strategy for data
>> pages.
>> | | |
>> | | | |SCAN Operator (VA = 6)
>> | | | | FROM TABLE
>> | | | | Worktable2.
>> | | | | Using Clustered Index.
>> | | | | Forward Scan.
>> | | | | Positioning by key.
>> | | | | Using I/O Size 16 Kbytes for data pages.
>> | | | | With LRU Buffer Replacement Strategy for data
>> pages.
>>
>>
>> Total estimated I/O cost for statement 1 (at line 1): 571497253.
>>
>>
>> *************************************************************************************
>>
>>
>> NOW ASE 15.02 ESD#4 , exec time > 40 min , FULL table SCAN !!
>>
>> select m.media_type, m.mounted,
>> 2> archive, file_id, format, fsize,
>> uncomp_fsize,
>> 3> stream, archive_status, status,
>> compression
>> 4> from mfs_files f
>> 5> join mfs_volume v on v.volume_id =
>> f.volume_id
>> 6> join mfs_media m on v.media_id =
>> m.media_id
>> 7> order by file_id
>> 8> plan '(use optgoal allrows_dss)'
>> 9> go
>>
>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>
>>
>> STEP 1
>> The type of query is SELECT.
>>
>> 6 operator(s) under root
>>
>> |ROOT:EMIT Operator (VA = 6)
>> |
>> | |SORT Operator (VA = 5)
>> | | Average Row width is 56.846985
>> | | Using Worktable3 for internal storage.
>> | |
>> | | |HASH JOIN Operator (VA = 4) (Join Type: Inner Join)
>> | | | Using Worktable2 for internal storage.
>> | | | Key Count: 1
>> | | |
>> | | | |HASH JOIN Operator (VA = 2) (Join Type: Inner Join)
>> | | | | Using Worktable1 for internal storage.
>> | | | | Key Count: 1
>> | | | |
>> | | | | |SCAN Operator (VA = 0)
>> | | | | | FROM TABLE
>> | | | | | mfs_media
>> | | | | | m
>> | | | | | 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.
>> | | | |
>> | | | | |SCAN Operator (VA = 1)
>> | | | | | FROM TABLE
>> | | | | | mfs_volume
>> | | | | | v
>> | | | | | 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.
>> | | |
>> | | | |SCAN Operator (VA = 3)
>> | | | | FROM TABLE
>> | | | | mfs_files
>> | | | | f
>> | | | | Table Scan.
>> | | | | Forward Scan.
>> | | | | Positioning at start of table.
>> | | | | Using I/O Size 16 Kbytes for data pages.
>> | | | | With MRU Buffer Replacement Strategy for data
>> pages.
>>
>>
>> Total estimated I/O cost for statement 1 (at line 1): 369276437.
>>
>> *************************************************************************


IQ Rules Posted on 2008-06-10 01:56:38.0Z
From: IQ Rules <dbmethods@nospam.com>
User-Agent: Thunderbird 2.0.0.14 (Macintosh/20080421)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: optimizer changes ASE 15.02 ESD#4 vs ESD#2 ?
References: <48494b3a$1@forums-1-dub> <484AAEB6.7030806@nospam.com> <484D47FB.1040007@nrc-cnrc.gc.ca>
In-Reply-To: <484D47FB.1040007@nrc-cnrc.gc.ca>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <484ddf56@forums-1-dub>
Date: 9 Jun 2008 18:56:38 -0700
X-Trace: forums-1-dub 1213062998 10.22.241.152 (9 Jun 2008 18:56:38 -0700)
X-Original-Trace: 9 Jun 2008 18:56:38 -0700, vip152.sybase.com
Lines: 381
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10918
Article PK: 89529

Any idea why you have roundrobin partition here? Do you have parallel
query feature turn on here? And number of engines stay the same
before/after EBF upgrade.

I am curious this query might be improved with the nonclustered index on
(file_id, volume_id) or (volume_id, file_id).

If you have allrows_mix goal in server level, any reason you need to set
session level to allrows_dss? This query is a 3 table joins.

Isabella wrote:
> Hello,
> please see answerers to your questions:
>
> Any suggestions what's going on ?
>
> Isabella
> sp_spaceused mfs_files
> 2> go
> name rowtotal reserved data index_size unused
> --------- -------- ----------- ---------- ---------- --------
> mfs_files 67707267 14406056 KB 8356950 KB 6021132 KB 27974 KB
>
> sp_helpindex mfs_files
> 2> go
> Object has the following indexes
>
> index_name index_keys index_description
> index_max_rows_per_page index_fillfactor index_reservepagegap
> index_created index_local
> ----------------- ----------------- --------------------
> ----------------------- ---------------- --------------------
> ------------------- ------------
> mfs_files_crc volume_id, fname nonclustered, unique
> 0 0 0 Jun 5 2008 9:25AM Global Index
> mfs_files_file_id file_id, status nonclustered
> 0 0 0 Jun 5 2008 9:25AM Global Index
> dataset_name dataset_name nonclustered
> 0 0 0 Jun 5 2008 9:25AM Global Index
> ingest_date ingest_date nonclustered
> 0 0 0 Jun 5 2008 9:25AM Global Index
>
> (4 rows affected)
> index_ptn_name index_ptn_seg
> ---------------------------- -------------
> mfs_files_crc_1648005871 default
> mfs_files_file_id_1648005871 default
> dataset_name_1648005871 default
> ingest_date_1648005871 default
>
> sp_help mfs_files
> 2> go
> Name Owner Object_type Create_date
> --------- ----- ----------- -------------------
> mfs_files dbo user table Apr 23 2005 4:20PM
>
> (1 row affected)
> Column_name Type Length Prec Scale Nulls Default_name Rule_name
> Access_Rule_name Computed_Column_object Identity
> -------------- -------- ------ ---- ----- ----- ------------ ---------
> ---------------- ---------------------- ----------
> volume_id binary 4 NULL NULL 0 NULL NULL
> NULL NULL 0
> file_id varchar 255 NULL NULL 0 NULL NULL
> NULL NULL 0
> fname varchar 255 NULL NULL 0 NULL NULL
> NULL NULL 0
> fsize int 4 NULL NULL 0 NULL NULL
> NULL NULL 0
> file_crc binary 4 NULL NULL 1 NULL NULL
> NULL NULL 0
> format char 1 NULL NULL 0 NULL NULL
> NULL NULL 0
> compression char 1 NULL NULL 0 NULL NULL
> NULL NULL 0
> uncomp_fsize int 4 NULL NULL 0 NULL NULL
> NULL NULL 0
> uncomp_fcrc binary 4 NULL NULL 1 NULL NULL
> NULL NULL 0
> status char 1 NULL NULL 0 NULL NULL
> NULL NULL 0
> archive_status char 1 NULL NULL 1 NULL NULL
> NULL NULL 0
> ingest_date datetime 8 NULL NULL 1 NULL NULL
> NULL NULL 0
> dataset_name varchar 255 NULL NULL 1 NULL NULL
> NULL NULL 0
> Object has the following indexes
>
>
> index_name index_keys index_description
> index_max_rows_per_page index_fillfactor index_reservepagegap
> index_created index_local
> ----------------- ----------------- --------------------
> ----------------------- ---------------- --------------------
> ------------------- ------------
> mfs_files_crc volume_id, fname nonclustered, unique
> 0 0 0 Jun 5 2008 9:25AM Global Index
> mfs_files_file_id file_id, status nonclustered
> 0 0 0 Jun 5 2008 9:25AM Global Index
> dataset_name dataset_name nonclustered
> 0 0 0 Jun 5 2008 9:25AM Global Index
> ingest_date ingest_date nonclustered
> 0 0 0 Jun 5 2008 9:25AM Global Index
>
> (4 rows affected)
> index_ptn_name index_ptn_seg
> ---------------------------- -------------
> mfs_files_crc_1648005871 default
> mfs_files_file_id_1648005871 default
> dataset_name_1648005871 default
> ingest_date_1648005871 default
> No defined keys for this object.
>
> (4 rows affected)
> name type partition_type partitions partition_keys
> --------- ---------- -------------- ---------- --------------
> mfs_files base table roundrobin 10 NULL
>
>
> (1 row affected)
> partition_name partition_id pages row_count segment create_date
> -------------------- ------------ ------ --------- -------
> -------------------
> mfs_files_1068527809 1068527809 417860 6770726 default Jun 5 2008
> 10:33AM
> mfs_files_1084527866 1084527866 417843 6770727 default Jun 5 2008
> 10:33AM
> mfs_files_1100527923 1100527923 417848 6770727 default Jun 5 2008
> 10:33AM
> mfs_files_1116527980 1116527980 417847 6770727 default Jun 5 2008
> 10:33AM
> mfs_files_1132528037 1132528037 417841 6770727 default Jun 5 2008
> 10:33AM
> mfs_files_1148528094 1148528094 417823 6770727 default Jun 5 2008
> 10:33AM
> mfs_files_1164528151 1164528151 417830 6770727 default Jun 5 2008
> 10:33AM
> mfs_files_1180528208 1180528208 417863 6770727 default Jun 5 2008
> 10:33AM
> mfs_files_1196528265 1196528265 417869 6770726 default Jun 5 2008
> 10:33AM
> mfs_files_1212528322 1212528322 417851 6770726 default Jun 5 2008
> 10:33AM
>
>
> Partition_Conditions
> --------------------
> NULL
>
>
> Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ratio(Min/Avg)
> ----------- ----------- ----------- --------------------
> --------------------
> 417847 417869 417823 1.000053 0.999943
> Lock scheme Datarows
> The 'ascinserts' attribute is not applicable to tables with datarow or
> datapage lock schemes.
>
>
> exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap
> ascinserts
> ------------ -------------- ---------- ----------------- ------------
> -----------
> 0 0 0 0 0
> 0
>
> (1 row affected)
> concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg
> ------------------------- --------------------- -------------------
> 0 0 0
>
> dbMethods wrote:
>> Can you post
>> sp_spaceused mfs_files
>> go
>> sp_helpindex mfs_files
>> go
>> sp_help mfs_files
>> go
>>
>> In this query, does the result set contain large object, such text or
>> image?
>>
>>
>> Isabella wrote:
>>> Hello All,
>>> We are in processing to switch to ASE 15.02 from ASE 12.5 Linux ,
>>> we ran our tests in April against ASE15.02 ESD#2, meanwhile after we
>>> aplied the last ESD#4 in late April, a we found some issues with one
>>> of the queries...We didn't re-test everything against new ESD#4.
>>> Here is the the SQL and the showplan output in both ESD,
>>> Mentioned running the SQL in ESD #2 after forcing the optimizer to
>>> user dss goal , takes < 1 min !! big improvement from ASE 12.5.3 BUT
>>> running same SQL in ASE 15.02 ESD #4 , optimizer is doing a FULL
>>> tables scan and takes > 40 MIN !!!
>>> What can be wrong , I delete and update index stats in Ase 15.02
>>> ESD#4, the biggest table is partition and see size
>>> name rowtotal reserved data index_size unused
>>> --------- -------- ----------- ---------- ---------- --------
>>> 63936221 13516222 KB 7846458 KB 5640878 KB 28886 KB
>>>
>>>
>>> ASE 15.02 ESD #2 , exec time < 1min
>>>
>>>
>>> select m.media_type, m.mounted,
>>> 2> archive, file_id, format, fsize,
>>> uncomp_fsize,
>>> 3> stream, archive_status, status,
>>> compression
>>> 4> from mfs_files f
>>> 5> join mfs_volume v on v.volume_id =
>>> f.volume_id
>>> 6> join mfs_media m on v.media_id =
>>> m.media_id
>>> 7> order by file_id
>>> 8> plan '(use optgoal allrows_dss)'
>>> 9> go
>>>
>>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>>
>>>
>>> STEP 1
>>> The type of query is SELECT.
>>>
>>> 9 operator(s) under root
>>>
>>> |ROOT:EMIT Operator (VA = 9)
>>> |
>>> | |SEQUENCER Operator (VA = 8) has 2 children.
>>> | |
>>> | | |STORE Operator (VA = 4)
>>> | | | Worktable2 created, in allpages locking mode, for
>>> REFORMATTING.
>>> | | | Creating clustered index.
>>> | | |
>>> | | | |INSERT Operator (VA = 3)
>>> | | | | The update mode is direct.
>>> | | | |
>>> | | | | |HASH JOIN Operator (VA = 2) (Join Type: Inner
>>> Join)
>>> | | | | | Using Worktable1 for internal storage.
>>> | | | | | Key Count: 1
>>> | | | | |
>>> | | | | | |SCAN Operator (VA = 0)
>>> | | | | | | FROM TABLE
>>> | | | | | | mfs_media
>>> | | | | | | m
>>> | | | | | | 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.
>>> | | | | |
>>> | | | | | |SCAN Operator (VA = 1)
>>> | | | | | | FROM TABLE
>>> | | | | | | mfs_volume
>>> | | | | | | v
>>> | | | | | | 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.
>>> | | | |
>>> | | | | TO TABLE
>>> | | | | Worktable2.
>>> | |
>>> | | |NESTED LOOP JOIN Operator (VA = 7) (Join Type: Inner
>>> Join)
>>> | | |
>>> | | | |SCAN Operator (VA = 5)
>>> | | | | FROM TABLE
>>> | | | | mfs_files
>>> | | | | f
>>> | | | | Index : mfs_files_file_id
>>> | | | | Forward Scan.
>>> | | | | Positioning at index start.
>>> | | | | Using I/O Size 16 Kbytes for index leaf pages.
>>> | | | | With LRU Buffer Replacement Strategy for index
>>> leaf pages.
>>> | | | | Using I/O Size 16 Kbytes for data pages.
>>> | | | | With MRU Buffer Replacement Strategy for data
>>> pages.
>>> | | |
>>> | | | |SCAN Operator (VA = 6)
>>> | | | | FROM TABLE
>>> | | | | Worktable2.
>>> | | | | Using Clustered Index.
>>> | | | | Forward Scan.
>>> | | | | Positioning by key.
>>> | | | | Using I/O Size 16 Kbytes for data pages.
>>> | | | | With LRU Buffer Replacement Strategy for data
>>> pages.
>>>
>>>
>>> Total estimated I/O cost for statement 1 (at line 1): 571497253.
>>>
>>>
>>> *************************************************************************************
>>>
>>>
>>> NOW ASE 15.02 ESD#4 , exec time > 40 min , FULL table SCAN !!
>>>
>>> select m.media_type, m.mounted,
>>> 2> archive, file_id, format, fsize,
>>> uncomp_fsize,
>>> 3> stream, archive_status, status,
>>> compression
>>> 4> from mfs_files f
>>> 5> join mfs_volume v on v.volume_id =
>>> f.volume_id
>>> 6> join mfs_media m on v.media_id =
>>> m.media_id
>>> 7> order by file_id
>>> 8> plan '(use optgoal allrows_dss)'
>>> 9> go
>>>
>>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>>
>>>
>>> STEP 1
>>> The type of query is SELECT.
>>>
>>> 6 operator(s) under root
>>>
>>> |ROOT:EMIT Operator (VA = 6)
>>> |
>>> | |SORT Operator (VA = 5)
>>> | | Average Row width is 56.846985
>>> | | Using Worktable3 for internal storage.
>>> | |
>>> | | |HASH JOIN Operator (VA = 4) (Join Type: Inner Join)
>>> | | | Using Worktable2 for internal storage.
>>> | | | Key Count: 1
>>> | | |
>>> | | | |HASH JOIN Operator (VA = 2) (Join Type: Inner Join)
>>> | | | | Using Worktable1 for internal storage.
>>> | | | | Key Count: 1
>>> | | | |
>>> | | | | |SCAN Operator (VA = 0)
>>> | | | | | FROM TABLE
>>> | | | | | mfs_media
>>> | | | | | m
>>> | | | | | 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.
>>> | | | |
>>> | | | | |SCAN Operator (VA = 1)
>>> | | | | | FROM TABLE
>>> | | | | | mfs_volume
>>> | | | | | v
>>> | | | | | 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.
>>> | | |
>>> | | | |SCAN Operator (VA = 3)
>>> | | | | FROM TABLE
>>> | | | | mfs_files
>>> | | | | f
>>> | | | | Table Scan.
>>> | | | | Forward Scan.
>>> | | | | Positioning at start of table.
>>> | | | | Using I/O Size 16 Kbytes for data pages.
>>> | | | | With MRU Buffer Replacement Strategy for data
>>> pages.
>>>
>>>
>>> Total estimated I/O cost for statement 1 (at line 1): 369276437.
>>>
>>> *************************************************************************
>>>


Isabella Posted on 2008-06-10 14:46:59.0Z
Message-ID: <484E93E3.8080002@nrc-cnrc.gc.ca>
From: Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
User-Agent: Thunderbird 1.5.0.12 (X11/20070530)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
To: IQ Rules <dbmethods@nospam.com>
Subject: Re: optimizer changes ASE 15.02 ESD#4 vs ESD#2 ?
References: <48494b3a$1@forums-1-dub> <484AAEB6.7030806@nospam.com> <484D47FB.1040007@nrc-cnrc.gc.ca> <484ddf56@forums-1-dub>
In-Reply-To: <484ddf56@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 10 Jun 2008 07:46:59 -0700
X-Trace: forums-1-dub 1213109219 10.22.241.152 (10 Jun 2008 07:46:59 -0700)
X-Original-Trace: 10 Jun 2008 07:46:59 -0700, vip152.sybase.com
Lines: 390
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10920
Article PK: 89531


IQ Rules wrote:
> Any idea why you have roundrobin partition here? Do you have parallel
> query feature turn on here? And number of engines stay the same
> before/after EBF upgrade.

yes numbner of engines same, roundrobin table parttions mfs_files has
67 mill rows , make sens to have it partitions , round robin is the only
option available with our ASE license
>
> I am curious this query might be improved with the nonclustered index on
> (file_id, volume_id) or (volume_id, file_id).
>
> If you have allrows_mix goal in server level,
exec time for the query takes > 40 min ESD #2 with opt goal mix
BUT with opt goal dss takes < 1 min !
in ESD #4 none of the above options are working , the optimizer is doing
a full tables scan and filling up tempdb =6GB and never completes..
any reason you need to set
> session level to allrows_dss? This query is a 3 table joins.
>
>
> Isabella wrote:
>> Hello,
>> please see answerers to your questions:
>>
>> Any suggestions what's going on ?
>>
>> Isabella
>> sp_spaceused mfs_files
>> 2> go
>> name rowtotal reserved data index_size unused
>> --------- -------- ----------- ---------- ---------- --------
>> mfs_files 67707267 14406056 KB 8356950 KB 6021132 KB 27974 KB
>>
>> sp_helpindex mfs_files
>> 2> go
>> Object has the following indexes
>>
>> index_name index_keys index_description
>> index_max_rows_per_page index_fillfactor index_reservepagegap
>> index_created index_local
>> ----------------- ----------------- --------------------
>> ----------------------- ---------------- --------------------
>> ------------------- ------------
>> mfs_files_crc volume_id, fname nonclustered, unique
>> 0 0 0 Jun 5 2008 9:25AM Global Index
>> mfs_files_file_id file_id, status nonclustered
>> 0 0 0 Jun 5 2008 9:25AM Global Index
>> dataset_name dataset_name nonclustered
>> 0 0 0 Jun 5 2008 9:25AM Global Index
>> ingest_date ingest_date nonclustered
>> 0 0 0 Jun 5 2008 9:25AM Global Index
>>
>> (4 rows affected)
>> index_ptn_name index_ptn_seg
>> ---------------------------- -------------
>> mfs_files_crc_1648005871 default
>> mfs_files_file_id_1648005871 default
>> dataset_name_1648005871 default
>> ingest_date_1648005871 default
>>
>> sp_help mfs_files
>> 2> go
>> Name Owner Object_type Create_date
>> --------- ----- ----------- -------------------
>> mfs_files dbo user table Apr 23 2005 4:20PM
>>
>> (1 row affected)
>> Column_name Type Length Prec Scale Nulls Default_name
>> Rule_name Access_Rule_name Computed_Column_object Identity
>> -------------- -------- ------ ---- ----- ----- ------------
>> --------- ---------------- ---------------------- ----------
>> volume_id binary 4 NULL NULL 0 NULL NULL
>> NULL NULL 0
>> file_id varchar 255 NULL NULL 0 NULL NULL
>> NULL NULL 0
>> fname varchar 255 NULL NULL 0 NULL NULL
>> NULL NULL 0
>> fsize int 4 NULL NULL 0 NULL NULL
>> NULL NULL 0
>> file_crc binary 4 NULL NULL 1 NULL NULL
>> NULL NULL 0
>> format char 1 NULL NULL 0 NULL NULL
>> NULL NULL 0
>> compression char 1 NULL NULL 0 NULL NULL
>> NULL NULL 0
>> uncomp_fsize int 4 NULL NULL 0 NULL NULL
>> NULL NULL 0
>> uncomp_fcrc binary 4 NULL NULL 1 NULL NULL
>> NULL NULL 0
>> status char 1 NULL NULL 0 NULL NULL
>> NULL NULL 0
>> archive_status char 1 NULL NULL 1 NULL NULL
>> NULL NULL 0
>> ingest_date datetime 8 NULL NULL 1 NULL NULL
>> NULL NULL 0
>> dataset_name varchar 255 NULL NULL 1 NULL NULL
>> NULL NULL 0
>> Object has the following indexes
>>
>>
>> index_name index_keys index_description
>> index_max_rows_per_page index_fillfactor index_reservepagegap
>> index_created index_local
>> ----------------- ----------------- --------------------
>> ----------------------- ---------------- --------------------
>> ------------------- ------------
>> mfs_files_crc volume_id, fname nonclustered, unique
>> 0 0 0 Jun 5 2008 9:25AM Global Index
>> mfs_files_file_id file_id, status nonclustered
>> 0 0 0 Jun 5 2008 9:25AM Global Index
>> dataset_name dataset_name nonclustered
>> 0 0 0 Jun 5 2008 9:25AM Global Index
>> ingest_date ingest_date nonclustered
>> 0 0 0 Jun 5 2008 9:25AM Global Index
>>
>> (4 rows affected)
>> index_ptn_name index_ptn_seg
>> ---------------------------- -------------
>> mfs_files_crc_1648005871 default
>> mfs_files_file_id_1648005871 default
>> dataset_name_1648005871 default
>> ingest_date_1648005871 default
>> No defined keys for this object.
>>
>> (4 rows affected)
>> name type partition_type partitions partition_keys
>> --------- ---------- -------------- ---------- --------------
>> mfs_files base table roundrobin 10 NULL
>>
>>
>> (1 row affected)
>> partition_name partition_id pages row_count segment create_date
>> -------------------- ------------ ------ --------- -------
>> -------------------
>> mfs_files_1068527809 1068527809 417860 6770726 default Jun 5
>> 2008 10:33AM
>> mfs_files_1084527866 1084527866 417843 6770727 default Jun 5
>> 2008 10:33AM
>> mfs_files_1100527923 1100527923 417848 6770727 default Jun 5
>> 2008 10:33AM
>> mfs_files_1116527980 1116527980 417847 6770727 default Jun 5
>> 2008 10:33AM
>> mfs_files_1132528037 1132528037 417841 6770727 default Jun 5
>> 2008 10:33AM
>> mfs_files_1148528094 1148528094 417823 6770727 default Jun 5
>> 2008 10:33AM
>> mfs_files_1164528151 1164528151 417830 6770727 default Jun 5
>> 2008 10:33AM
>> mfs_files_1180528208 1180528208 417863 6770727 default Jun 5
>> 2008 10:33AM
>> mfs_files_1196528265 1196528265 417869 6770726 default Jun 5
>> 2008 10:33AM
>> mfs_files_1212528322 1212528322 417851 6770726 default Jun 5
>> 2008 10:33AM
>>
>>
>> Partition_Conditions
>> --------------------
>> NULL
>>
>>
>> Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ratio(Min/Avg)
>> ----------- ----------- ----------- --------------------
>> --------------------
>> 417847 417869 417823 1.000053 0.999943
>> Lock scheme Datarows
>> The 'ascinserts' attribute is not applicable to tables with datarow or
>> datapage lock schemes.
>>
>>
>> exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap
>> ascinserts
>> ------------ -------------- ---------- ----------------- ------------
>> -----------
>> 0 0 0 0 0
>> 0
>>
>> (1 row affected)
>> concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg
>> ------------------------- --------------------- -------------------
>> 0 0 0
>>
>> dbMethods wrote:
>>> Can you post
>>> sp_spaceused mfs_files
>>> go
>>> sp_helpindex mfs_files
>>> go
>>> sp_help mfs_files
>>> go
>>>
>>> In this query, does the result set contain large object, such text or
>>> image?
>>>
>>>
>>> Isabella wrote:
>>>> Hello All,
>>>> We are in processing to switch to ASE 15.02 from ASE 12.5 Linux ,
>>>> we ran our tests in April against ASE15.02 ESD#2, meanwhile after we
>>>> aplied the last ESD#4 in late April, a we found some issues with one
>>>> of the queries...We didn't re-test everything against new ESD#4.
>>>> Here is the the SQL and the showplan output in both ESD,
>>>> Mentioned running the SQL in ESD #2 after forcing the optimizer to
>>>> user dss goal , takes < 1 min !! big improvement from ASE 12.5.3
>>>> BUT running same SQL in ASE 15.02 ESD #4 , optimizer is doing a
>>>> FULL tables scan and takes > 40 MIN !!!
>>>> What can be wrong , I delete and update index stats in Ase 15.02
>>>> ESD#4, the biggest table is partition and see size
>>>> name rowtotal reserved data index_size unused
>>>> --------- -------- ----------- ---------- ---------- --------
>>>> 63936221 13516222 KB 7846458 KB 5640878 KB 28886 KB
>>>>
>>>>
>>>> ASE 15.02 ESD #2 , exec time < 1min
>>>>
>>>>
>>>> select m.media_type, m.mounted,
>>>> 2> archive, file_id, format, fsize,
>>>> uncomp_fsize,
>>>> 3> stream, archive_status, status,
>>>> compression
>>>> 4> from mfs_files f
>>>> 5> join mfs_volume v on v.volume_id
>>>> = f.volume_id
>>>> 6> join mfs_media m on v.media_id =
>>>> m.media_id
>>>> 7> order by file_id
>>>> 8> plan '(use optgoal allrows_dss)'
>>>> 9> go
>>>>
>>>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>>>
>>>>
>>>> STEP 1
>>>> The type of query is SELECT.
>>>>
>>>> 9 operator(s) under root
>>>>
>>>> |ROOT:EMIT Operator (VA = 9)
>>>> |
>>>> | |SEQUENCER Operator (VA = 8) has 2 children.
>>>> | |
>>>> | | |STORE Operator (VA = 4)
>>>> | | | Worktable2 created, in allpages locking mode, for
>>>> REFORMATTING.
>>>> | | | Creating clustered index.
>>>> | | |
>>>> | | | |INSERT Operator (VA = 3)
>>>> | | | | The update mode is direct.
>>>> | | | |
>>>> | | | | |HASH JOIN Operator (VA = 2) (Join Type:
>>>> Inner Join)
>>>> | | | | | Using Worktable1 for internal storage.
>>>> | | | | | Key Count: 1
>>>> | | | | |
>>>> | | | | | |SCAN Operator (VA = 0)
>>>> | | | | | | FROM TABLE
>>>> | | | | | | mfs_media
>>>> | | | | | | m
>>>> | | | | | | 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.
>>>> | | | | |
>>>> | | | | | |SCAN Operator (VA = 1)
>>>> | | | | | | FROM TABLE
>>>> | | | | | | mfs_volume
>>>> | | | | | | v
>>>> | | | | | | 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.
>>>> | | | |
>>>> | | | | TO TABLE
>>>> | | | | Worktable2.
>>>> | |
>>>> | | |NESTED LOOP JOIN Operator (VA = 7) (Join Type: Inner
>>>> Join)
>>>> | | |
>>>> | | | |SCAN Operator (VA = 5)
>>>> | | | | FROM TABLE
>>>> | | | | mfs_files
>>>> | | | | f
>>>> | | | | Index : mfs_files_file_id
>>>> | | | | Forward Scan.
>>>> | | | | Positioning at index start.
>>>> | | | | Using I/O Size 16 Kbytes for index leaf pages.
>>>> | | | | With LRU Buffer Replacement Strategy for index
>>>> leaf pages.
>>>> | | | | Using I/O Size 16 Kbytes for data pages.
>>>> | | | | With MRU Buffer Replacement Strategy for data
>>>> pages.
>>>> | | |
>>>> | | | |SCAN Operator (VA = 6)
>>>> | | | | FROM TABLE
>>>> | | | | Worktable2.
>>>> | | | | Using Clustered Index.
>>>> | | | | Forward Scan.
>>>> | | | | Positioning by key.
>>>> | | | | Using I/O Size 16 Kbytes for data pages.
>>>> | | | | With LRU Buffer Replacement Strategy for data
>>>> pages.
>>>>
>>>>
>>>> Total estimated I/O cost for statement 1 (at line 1): 571497253.
>>>>
>>>>
>>>> *************************************************************************************
>>>>
>>>>
>>>> NOW ASE 15.02 ESD#4 , exec time > 40 min , FULL table SCAN !!
>>>>
>>>> select m.media_type, m.mounted,
>>>> 2> archive, file_id, format, fsize,
>>>> uncomp_fsize,
>>>> 3> stream, archive_status, status,
>>>> compression
>>>> 4> from mfs_files f
>>>> 5> join mfs_volume v on v.volume_id
>>>> = f.volume_id
>>>> 6> join mfs_media m on v.media_id =
>>>> m.media_id
>>>> 7> order by file_id
>>>> 8> plan '(use optgoal allrows_dss)'
>>>> 9> go
>>>>
>>>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>>>
>>>>
>>>> STEP 1
>>>> The type of query is SELECT.
>>>>
>>>> 6 operator(s) under root
>>>>
>>>> |ROOT:EMIT Operator (VA = 6)
>>>> |
>>>> | |SORT Operator (VA = 5)
>>>> | | Average Row width is 56.846985
>>>> | | Using Worktable3 for internal storage.
>>>> | |
>>>> | | |HASH JOIN Operator (VA = 4) (Join Type: Inner Join)
>>>> | | | Using Worktable2 for internal storage.
>>>> | | | Key Count: 1
>>>> | | |
>>>> | | | |HASH JOIN Operator (VA = 2) (Join Type: Inner Join)
>>>> | | | | Using Worktable1 for internal storage.
>>>> | | | | Key Count: 1
>>>> | | | |
>>>> | | | | |SCAN Operator (VA = 0)
>>>> | | | | | FROM TABLE
>>>> | | | | | mfs_media
>>>> | | | | | m
>>>> | | | | | 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.
>>>> | | | |
>>>> | | | | |SCAN Operator (VA = 1)
>>>> | | | | | FROM TABLE
>>>> | | | | | mfs_volume
>>>> | | | | | v
>>>> | | | | | 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.
>>>> | | |
>>>> | | | |SCAN Operator (VA = 3)
>>>> | | | | FROM TABLE
>>>> | | | | mfs_files
>>>> | | | | f
>>>> | | | | Table Scan.
>>>> | | | | Forward Scan.
>>>> | | | | Positioning at start of table.
>>>> | | | | Using I/O Size 16 Kbytes for data pages.
>>>> | | | | With MRU Buffer Replacement Strategy for data
>>>> pages.
>>>>
>>>>
>>>> Total estimated I/O cost for statement 1 (at line 1): 369276437.
>>>>
>>>> *************************************************************************
>>>>


peter collard Posted on 2008-06-10 18:41:11.0Z
Message-ID: <484ecac6@forums-1-dub>
From: peter collard <peterrrrrr@glossop.org>
Subject: Re: optimizer changes ASE 15.02 ESD#4 vs ESD#2 ?
Newsgroups: sybase.public.ase.performance+tuning
References: <48494b3a$1@forums-1-dub>
Lines: 204
User-Agent: KNode/0.10.4
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7Bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 10 Jun 2008 11:41:11 -0700
X-Trace: forums-1-dub 1213123271 10.22.241.152 (10 Jun 2008 11:41:11 -0700)
X-Original-Trace: 10 Jun 2008 11:41:11 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10921
Article PK: 89532

As the query has no sargs (search arguments) and the joins are not supported
by indexes very well it comes down to using an index rather than sorting.
At this point optdiag stats might well give the game away.

I suspect the partitioning is probably a bit of a red herring.

Isabella wrote:

> Hello All,
> We are in processing to switch to ASE 15.02 from ASE 12.5 Linux , we
> ran our tests in April against ASE15.02 ESD#2, meanwhile after we aplied
> the last ESD#4 in late April, a we found some issues with one of the
> queries...We didn't re-test everything against new ESD#4.
> Here is the the SQL and the showplan output in both ESD,
> Mentioned running the SQL in ESD #2 after forcing the optimizer to user
> dss goal , takes < 1 min !! big improvement from ASE 12.5.3 BUT running
> same SQL in ASE 15.02 ESD #4 , optimizer is doing a FULL tables scan
> and takes > 40 MIN !!!
> What can be wrong , I delete and update index stats in Ase 15.02
> ESD#4, the biggest table is partition and see size
> name rowtotal reserved data index_size unused
> --------- -------- ----------- ---------- ---------- --------
> 63936221 13516222 KB 7846458 KB 5640878 KB 28886 KB
>
>
> ASE 15.02 ESD #2 , exec time < 1min
>
>
> select m.media_type, m.mounted,
> 2> archive, file_id, format, fsize,
> uncomp_fsize,
> 3> stream, archive_status, status,
> compression
> 4> from mfs_files f
> 5> join mfs_volume v on v.volume_id =
> f.volume_id
> 6> join mfs_media m on v.media_id =
> m.media_id
> 7> order by file_id
> 8> plan '(use optgoal allrows_dss)'
> 9> go
>
> QUERY PLAN FOR STATEMENT 1 (at line 1).
>
>
> STEP 1
> The type of query is SELECT.
>
> 9 operator(s) under root
>
> |ROOT:EMIT Operator (VA = 9)
> |
> | |SEQUENCER Operator (VA = 8) has 2 children.
> | |
> | | |STORE Operator (VA = 4)
> | | | Worktable2 created, in allpages locking mode, for
> REFORMATTING.
> | | | Creating clustered index.
> | | |
> | | | |INSERT Operator (VA = 3)
> | | | | The update mode is direct.
> | | | |
> | | | | |HASH JOIN Operator (VA = 2) (Join Type: Inner
> | | | | |Join)
> | | | | | Using Worktable1 for internal storage.
> | | | | | Key Count: 1
> | | | | |
> | | | | | |SCAN Operator (VA = 0)
> | | | | | | FROM TABLE
> | | | | | | mfs_media
> | | | | | | m
> | | | | | | 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.
> | | | | |
> | | | | | |SCAN Operator (VA = 1)
> | | | | | | FROM TABLE
> | | | | | | mfs_volume
> | | | | | | v
> | | | | | | 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.
> | | | |
> | | | | TO TABLE
> | | | | Worktable2.
> | |
> | | |NESTED LOOP JOIN Operator (VA = 7) (Join Type: Inner
> | | |Join)
> | | |
> | | | |SCAN Operator (VA = 5)
> | | | | FROM TABLE
> | | | | mfs_files
> | | | | f
> | | | | Index : mfs_files_file_id
> | | | | Forward Scan.
> | | | | Positioning at index start.
> | | | | Using I/O Size 16 Kbytes for index leaf pages.
> | | | | With LRU Buffer Replacement Strategy for index
> leaf pages.
> | | | | Using I/O Size 16 Kbytes for data pages.
> | | | | With MRU Buffer Replacement Strategy for data
> | | | | pages.
> | | |
> | | | |SCAN Operator (VA = 6)
> | | | | FROM TABLE
> | | | | Worktable2.
> | | | | Using Clustered Index.
> | | | | Forward Scan.
> | | | | Positioning by key.
> | | | | Using I/O Size 16 Kbytes for data pages.
> | | | | With LRU Buffer Replacement Strategy for data
> | | | | pages.
>
>
> Total estimated I/O cost for statement 1 (at line 1): 571497253.
>
>
>
*************************************************************************************
>
> NOW ASE 15.02 ESD#4 , exec time > 40 min , FULL table SCAN !!
>
> select m.media_type, m.mounted,
> 2> archive, file_id, format, fsize,
> uncomp_fsize,
> 3> stream, archive_status, status,
> compression
> 4> from mfs_files f
> 5> join mfs_volume v on v.volume_id =
> f.volume_id
> 6> join mfs_media m on v.media_id =
> m.media_id
> 7> order by file_id
> 8> plan '(use optgoal allrows_dss)'
> 9> go
>
> QUERY PLAN FOR STATEMENT 1 (at line 1).
>
>
> STEP 1
> The type of query is SELECT.
>
> 6 operator(s) under root
>
> |ROOT:EMIT Operator (VA = 6)
> |
> | |SORT Operator (VA = 5)
> | | Average Row width is 56.846985
> | | Using Worktable3 for internal storage.
> | |
> | | |HASH JOIN Operator (VA = 4) (Join Type: Inner Join)
> | | | Using Worktable2 for internal storage.
> | | | Key Count: 1
> | | |
> | | | |HASH JOIN Operator (VA = 2) (Join Type: Inner Join)
> | | | | Using Worktable1 for internal storage.
> | | | | Key Count: 1
> | | | |
> | | | | |SCAN Operator (VA = 0)
> | | | | | FROM TABLE
> | | | | | mfs_media
> | | | | | m
> | | | | | 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.
> | | | |
> | | | | |SCAN Operator (VA = 1)
> | | | | | FROM TABLE
> | | | | | mfs_volume
> | | | | | v
> | | | | | 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.
> | | |
> | | | |SCAN Operator (VA = 3)
> | | | | FROM TABLE
> | | | | mfs_files
> | | | | f
> | | | | Table Scan.
> | | | | Forward Scan.
> | | | | Positioning at start of table.
> | | | | Using I/O Size 16 Kbytes for data pages.
> | | | | With MRU Buffer Replacement Strategy for data
> | | | | pages.
>
>
> Total estimated I/O cost for statement 1 (at line 1): 369276437.
>
> *************************************************************************


IQ Rules Posted on 2008-06-10 23:34:06.0Z
From: IQ Rules <dbmethods@nospam.com>
User-Agent: Thunderbird 2.0.0.14 (Macintosh/20080421)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: optimizer changes ASE 15.02 ESD#4 vs ESD#2 ?
References: <48494b3a$1@forums-1-dub>
In-Reply-To: <48494b3a$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <484f0f6e$1@forums-1-dub>
Date: 10 Jun 2008 16:34:06 -0700
X-Trace: forums-1-dub 1213140846 10.22.241.152 (10 Jun 2008 16:34:06 -0700)
X-Original-Trace: 10 Jun 2008 16:34:06 -0700, vip152.sybase.com
Lines: 208
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10923
Article PK: 89534

Here is what I found.

join mfs_volume v on v.volume_id = f.volume_id
mfs_files_crc volume_id, fname nonclustered, unique

Could be index mfs_files_crc fragmented?

Can you run/post optdiag result, reindex (drop/recreate) mfs_files_crc,
re-run this sql?

I also noticed this.
In DSS mode, ASE 15 uses Hash Join.
In Mix mode, it uses Sort Merge, i/o counts about the same, but more i/o
on tempdb.

The table I test, Lock scheme Datarows, same as yours.
And I don't have roundrobin partition.

Isabella wrote:
> Hello All,
> We are in processing to switch to ASE 15.02 from ASE 12.5 Linux , we
> ran our tests in April against ASE15.02 ESD#2, meanwhile after we aplied
> the last ESD#4 in late April, a we found some issues with one of the
> queries...We didn't re-test everything against new ESD#4.
> Here is the the SQL and the showplan output in both ESD,
> Mentioned running the SQL in ESD #2 after forcing the optimizer to user
> dss goal , takes < 1 min !! big improvement from ASE 12.5.3 BUT running
> same SQL in ASE 15.02 ESD #4 , optimizer is doing a FULL tables scan
> and takes > 40 MIN !!!
> What can be wrong , I delete and update index stats in Ase 15.02 ESD#4,
> the biggest table is partition and see size
> name rowtotal reserved data index_size unused
> --------- -------- ----------- ---------- ---------- --------
> 63936221 13516222 KB 7846458 KB 5640878 KB 28886 KB
>
>
> ASE 15.02 ESD #2 , exec time < 1min
>
>
> select m.media_type, m.mounted,
> 2> archive, file_id, format, fsize,
> uncomp_fsize,
> 3> stream, archive_status, status,
> compression
> 4> from mfs_files f
> 5> join mfs_volume v on v.volume_id =
> f.volume_id
> 6> join mfs_media m on v.media_id =
> m.media_id
> 7> order by file_id
> 8> plan '(use optgoal allrows_dss)'
> 9> go
>
> QUERY PLAN FOR STATEMENT 1 (at line 1).
>
>
> STEP 1
> The type of query is SELECT.
>
> 9 operator(s) under root
>
> |ROOT:EMIT Operator (VA = 9)
> |
> | |SEQUENCER Operator (VA = 8) has 2 children.
> | |
> | | |STORE Operator (VA = 4)
> | | | Worktable2 created, in allpages locking mode, for
> REFORMATTING.
> | | | Creating clustered index.
> | | |
> | | | |INSERT Operator (VA = 3)
> | | | | The update mode is direct.
> | | | |
> | | | | |HASH JOIN Operator (VA = 2) (Join Type: Inner Join)
> | | | | | Using Worktable1 for internal storage.
> | | | | | Key Count: 1
> | | | | |
> | | | | | |SCAN Operator (VA = 0)
> | | | | | | FROM TABLE
> | | | | | | mfs_media
> | | | | | | m
> | | | | | | 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.
> | | | | |
> | | | | | |SCAN Operator (VA = 1)
> | | | | | | FROM TABLE
> | | | | | | mfs_volume
> | | | | | | v
> | | | | | | 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.
> | | | |
> | | | | TO TABLE
> | | | | Worktable2.
> | |
> | | |NESTED LOOP JOIN Operator (VA = 7) (Join Type: Inner Join)
> | | |
> | | | |SCAN Operator (VA = 5)
> | | | | FROM TABLE
> | | | | mfs_files
> | | | | f
> | | | | Index : mfs_files_file_id
> | | | | Forward Scan.
> | | | | Positioning at index start.
> | | | | Using I/O Size 16 Kbytes for index leaf pages.
> | | | | With LRU Buffer Replacement Strategy for index
> leaf pages.
> | | | | Using I/O Size 16 Kbytes for data pages.
> | | | | With MRU Buffer Replacement Strategy for data pages.
> | | |
> | | | |SCAN Operator (VA = 6)
> | | | | FROM TABLE
> | | | | Worktable2.
> | | | | Using Clustered Index.
> | | | | Forward Scan.
> | | | | Positioning by key.
> | | | | Using I/O Size 16 Kbytes for data pages.
> | | | | With LRU Buffer Replacement Strategy for data pages.
>
>
> Total estimated I/O cost for statement 1 (at line 1): 571497253.
>
>
> *************************************************************************************
>
>
> NOW ASE 15.02 ESD#4 , exec time > 40 min , FULL table SCAN !!
>
> select m.media_type, m.mounted,
> 2> archive, file_id, format, fsize,
> uncomp_fsize,
> 3> stream, archive_status, status,
> compression
> 4> from mfs_files f
> 5> join mfs_volume v on v.volume_id =
> f.volume_id
> 6> join mfs_media m on v.media_id =
> m.media_id
> 7> order by file_id
> 8> plan '(use optgoal allrows_dss)'
> 9> go
>
> QUERY PLAN FOR STATEMENT 1 (at line 1).
>
>
> STEP 1
> The type of query is SELECT.
>
> 6 operator(s) under root
>
> |ROOT:EMIT Operator (VA = 6)
> |
> | |SORT Operator (VA = 5)
> | | Average Row width is 56.846985
> | | Using Worktable3 for internal storage.
> | |
> | | |HASH JOIN Operator (VA = 4) (Join Type: Inner Join)
> | | | Using Worktable2 for internal storage.
> | | | Key Count: 1
> | | |
> | | | |HASH JOIN Operator (VA = 2) (Join Type: Inner Join)
> | | | | Using Worktable1 for internal storage.
> | | | | Key Count: 1
> | | | |
> | | | | |SCAN Operator (VA = 0)
> | | | | | FROM TABLE
> | | | | | mfs_media
> | | | | | m
> | | | | | 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.
> | | | |
> | | | | |SCAN Operator (VA = 1)
> | | | | | FROM TABLE
> | | | | | mfs_volume
> | | | | | v
> | | | | | 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.
> | | |
> | | | |SCAN Operator (VA = 3)
> | | | | FROM TABLE
> | | | | mfs_files
> | | | | f
> | | | | Table Scan.
> | | | | Forward Scan.
> | | | | Positioning at start of table.
> | | | | Using I/O Size 16 Kbytes for data pages.
> | | | | With MRU Buffer Replacement Strategy for data pages.
>
>
> Total estimated I/O cost for statement 1 (at line 1): 369276437.
>
> *************************************************************************


Isabella Posted on 2008-06-12 16:57:21.0Z
From: Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
User-Agent: Thunderbird 1.5.0.12 (X11/20070530)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: optimizer changes ASE 15.02 ESD#4 vs ESD#2 ?
References: <48494b3a$1@forums-1-dub> <484f0f6e$1@forums-1-dub>
In-Reply-To: <484f0f6e$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48515571$1@forums-1-dub>
Date: 12 Jun 2008 09:57:21 -0700
X-Trace: forums-1-dub 1213289841 10.22.241.152 (12 Jun 2008 09:57:21 -0700)
X-Original-Trace: 12 Jun 2008 09:57:21 -0700, vip152.sybase.com
Lines: 228
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10938
Article PK: 89549

I re-run 'reorg rebuid mfs_files " and run my SQL same showplan : full
table scan on mfs_files , which will fill up 6GB tempdb and the SQL will
hangup here....
Seems that the issue is the missing reformatting choice from ESD#4, in
ESD#2 as you see is using reformatting and mfs_files_file_id index .
I 'm not able to force reformatting in ESD#4....
Ref: optdiag, the output is too big to fit here, I can send you the file (?)
Thank you,
Isabella


Isabella

IQ Rules wrote:
> Here is what I found.
>
> join mfs_volume v on v.volume_id = f.volume_id
> mfs_files_crc volume_id, fname nonclustered, unique
>
> Could be index mfs_files_crc fragmented?
>
> Can you run/post optdiag result, reindex (drop/recreate) mfs_files_crc,
> re-run this sql?
>
> I also noticed this.
> In DSS mode, ASE 15 uses Hash Join.
> In Mix mode, it uses Sort Merge, i/o counts about the same, but more i/o
> on tempdb.
>
> The table I test, Lock scheme Datarows, same as yours.
> And I don't have roundrobin partition.
>
>
> Isabella wrote:
>> Hello All,
>> We are in processing to switch to ASE 15.02 from ASE 12.5 Linux , we
>> ran our tests in April against ASE15.02 ESD#2, meanwhile after we
>> aplied the last ESD#4 in late April, a we found some issues with one
>> of the queries...We didn't re-test everything against new ESD#4.
>> Here is the the SQL and the showplan output in both ESD,
>> Mentioned running the SQL in ESD #2 after forcing the optimizer to
>> user dss goal , takes < 1 min !! big improvement from ASE 12.5.3 BUT
>> running same SQL in ASE 15.02 ESD #4 , optimizer is doing a FULL
>> tables scan and takes > 40 MIN !!!
>> What can be wrong , I delete and update index stats in Ase 15.02
>> ESD#4, the biggest table is partition and see size
>> name rowtotal reserved data index_size unused
>> --------- -------- ----------- ---------- ---------- --------
>> 63936221 13516222 KB 7846458 KB 5640878 KB 28886 KB
>>
>>
>> ASE 15.02 ESD #2 , exec time < 1min
>>
>>
>> select m.media_type, m.mounted,
>> 2> archive, file_id, format, fsize,
>> uncomp_fsize,
>> 3> stream, archive_status, status,
>> compression
>> 4> from mfs_files f
>> 5> join mfs_volume v on v.volume_id =
>> f.volume_id
>> 6> join mfs_media m on v.media_id =
>> m.media_id
>> 7> order by file_id
>> 8> plan '(use optgoal allrows_dss)'
>> 9> go
>>
>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>
>>
>> STEP 1
>> The type of query is SELECT.
>>
>> 9 operator(s) under root
>>
>> |ROOT:EMIT Operator (VA = 9)
>> |
>> | |SEQUENCER Operator (VA = 8) has 2 children.
>> | |
>> | | |STORE Operator (VA = 4)
>> | | | Worktable2 created, in allpages locking mode, for
>> REFORMATTING.
>> | | | Creating clustered index.
>> | | |
>> | | | |INSERT Operator (VA = 3)
>> | | | | The update mode is direct.
>> | | | |
>> | | | | |HASH JOIN Operator (VA = 2) (Join Type: Inner
>> Join)
>> | | | | | Using Worktable1 for internal storage.
>> | | | | | Key Count: 1
>> | | | | |
>> | | | | | |SCAN Operator (VA = 0)
>> | | | | | | FROM TABLE
>> | | | | | | mfs_media
>> | | | | | | m
>> | | | | | | 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.
>> | | | | |
>> | | | | | |SCAN Operator (VA = 1)
>> | | | | | | FROM TABLE
>> | | | | | | mfs_volume
>> | | | | | | v
>> | | | | | | 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.
>> | | | |
>> | | | | TO TABLE
>> | | | | Worktable2.
>> | |
>> | | |NESTED LOOP JOIN Operator (VA = 7) (Join Type: Inner
>> Join)
>> | | |
>> | | | |SCAN Operator (VA = 5)
>> | | | | FROM TABLE
>> | | | | mfs_files
>> | | | | f
>> | | | | Index : mfs_files_file_id
>> | | | | Forward Scan.
>> | | | | Positioning at index start.
>> | | | | Using I/O Size 16 Kbytes for index leaf pages.
>> | | | | With LRU Buffer Replacement Strategy for index
>> leaf pages.
>> | | | | Using I/O Size 16 Kbytes for data pages.
>> | | | | With MRU Buffer Replacement Strategy for data
>> pages.
>> | | |
>> | | | |SCAN Operator (VA = 6)
>> | | | | FROM TABLE
>> | | | | Worktable2.
>> | | | | Using Clustered Index.
>> | | | | Forward Scan.
>> | | | | Positioning by key.
>> | | | | Using I/O Size 16 Kbytes for data pages.
>> | | | | With LRU Buffer Replacement Strategy for data
>> pages.
>>
>>
>> Total estimated I/O cost for statement 1 (at line 1): 571497253.
>>
>>
>> *************************************************************************************
>>
>>
>> NOW ASE 15.02 ESD#4 , exec time > 40 min , FULL table SCAN !!
>>
>> select m.media_type, m.mounted,
>> 2> archive, file_id, format, fsize,
>> uncomp_fsize,
>> 3> stream, archive_status, status,
>> compression
>> 4> from mfs_files f
>> 5> join mfs_volume v on v.volume_id =
>> f.volume_id
>> 6> join mfs_media m on v.media_id =
>> m.media_id
>> 7> order by file_id
>> 8> plan '(use optgoal allrows_dss)'
>> 9> go
>>
>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>
>>
>> STEP 1
>> The type of query is SELECT.
>>
>> 6 operator(s) under root
>>
>> |ROOT:EMIT Operator (VA = 6)
>> |
>> | |SORT Operator (VA = 5)
>> | | Average Row width is 56.846985
>> | | Using Worktable3 for internal storage.
>> | |
>> | | |HASH JOIN Operator (VA = 4) (Join Type: Inner Join)
>> | | | Using Worktable2 for internal storage.
>> | | | Key Count: 1
>> | | |
>> | | | |HASH JOIN Operator (VA = 2) (Join Type: Inner Join)
>> | | | | Using Worktable1 for internal storage.
>> | | | | Key Count: 1
>> | | | |
>> | | | | |SCAN Operator (VA = 0)
>> | | | | | FROM TABLE
>> | | | | | mfs_media
>> | | | | | m
>> | | | | | 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.
>> | | | |
>> | | | | |SCAN Operator (VA = 1)
>> | | | | | FROM TABLE
>> | | | | | mfs_volume
>> | | | | | v
>> | | | | | 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.
>> | | |
>> | | | |SCAN Operator (VA = 3)
>> | | | | FROM TABLE
>> | | | | mfs_files
>> | | | | f
>> | | | | Table Scan.
>> | | | | Forward Scan.
>> | | | | Positioning at start of table.
>> | | | | Using I/O Size 16 Kbytes for data pages.
>> | | | | With MRU Buffer Replacement Strategy for data
>> pages.
>>
>>
>> Total estimated I/O cost for statement 1 (at line 1): 369276437.
>>
>> *************************************************************************


Sherlock, Kevin Posted on 2008-06-12 17:28:31.0Z
From: "Sherlock, Kevin" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <48494b3a$1@forums-1-dub> <484f0f6e$1@forums-1-dub> <48515571$1@forums-1-dub>
Subject: Re: optimizer changes ASE 15.02 ESD#4 vs ESD#2 ?
Lines: 254
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48515cbf$1@forums-1-dub>
Date: 12 Jun 2008 10:28:31 -0700
X-Trace: forums-1-dub 1213291711 10.22.241.152 (12 Jun 2008 10:28:31 -0700)
X-Original-Trace: 12 Jun 2008 10:28:31 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10939
Article PK: 89550

I'm just curious. You are using the AP syntax here. Can you test this by
using the "set plan optgoal allrows_dss" technique and eliminate the AP
syntax at the end of the SQL?

IE:

set showplan on
go
set statement_cache off
go
set plan optgoal allrows_dss
go
select m.media_type, m.mounted,
archive, file_id, format, fsize, uncomp_fsize,
stream, archive_status, status, compression
from mfs_files f
join mfs_volume v on v.volume_id = f.volume_id
join mfs_media m on v.media_id = m.media_id
order by file_id
go

"Isabella" <isabella.ghiurea@nrc-cnrc.gc.ca> wrote in message
news:48515571$1@forums-1-dub...
>
>
> I re-run 'reorg rebuid mfs_files " and run my SQL same showplan : full
> table scan on mfs_files , which will fill up 6GB tempdb and the SQL will
> hangup here....
> Seems that the issue is the missing reformatting choice from ESD#4, in
> ESD#2 as you see is using reformatting and mfs_files_file_id index .
> I 'm not able to force reformatting in ESD#4....
> Ref: optdiag, the output is too big to fit here, I can send you the file
> (?)
> Thank you,
> Isabella
>
>
> Isabella
> IQ Rules wrote:
>> Here is what I found.
>>
>> join mfs_volume v on v.volume_id = f.volume_id
>> mfs_files_crc volume_id, fname nonclustered, unique
>>
>> Could be index mfs_files_crc fragmented?
>>
>> Can you run/post optdiag result, reindex (drop/recreate) mfs_files_crc,
>> re-run this sql?
>>
>> I also noticed this.
>> In DSS mode, ASE 15 uses Hash Join.
>> In Mix mode, it uses Sort Merge, i/o counts about the same, but more i/o
>> on tempdb.
>>
>> The table I test, Lock scheme Datarows, same as yours.
>> And I don't have roundrobin partition.
>>
>>
>> Isabella wrote:
>>> Hello All,
>>> We are in processing to switch to ASE 15.02 from ASE 12.5 Linux , we
>>> ran our tests in April against ASE15.02 ESD#2, meanwhile after we aplied
>>> the last ESD#4 in late April, a we found some issues with one of the
>>> queries...We didn't re-test everything against new ESD#4.
>>> Here is the the SQL and the showplan output in both ESD,
>>> Mentioned running the SQL in ESD #2 after forcing the optimizer to user
>>> dss goal , takes < 1 min !! big improvement from ASE 12.5.3 BUT running
>>> same SQL in ASE 15.02 ESD #4 , optimizer is doing a FULL tables scan
>>> and takes > 40 MIN !!!
>>> What can be wrong , I delete and update index stats in Ase 15.02 ESD#4,
>>> the biggest table is partition and see size
>>> name rowtotal reserved data index_size unused
>>> --------- -------- ----------- ---------- ---------- --------
>>> 63936221 13516222 KB 7846458 KB 5640878 KB 28886 KB
>>>
>>>
>>> ASE 15.02 ESD #2 , exec time < 1min
>>>
>>>
>>> select m.media_type, m.mounted,
>>> 2> archive, file_id, format, fsize,
>>> uncomp_fsize,
>>> 3> stream, archive_status, status,
>>> compression
>>> 4> from mfs_files f
>>> 5> join mfs_volume v on v.volume_id =
>>> f.volume_id
>>> 6> join mfs_media m on v.media_id =
>>> m.media_id
>>> 7> order by file_id
>>> 8> plan '(use optgoal allrows_dss)'
>>> 9> go
>>>
>>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>>
>>>
>>> STEP 1
>>> The type of query is SELECT.
>>>
>>> 9 operator(s) under root
>>>
>>> |ROOT:EMIT Operator (VA = 9)
>>> |
>>> | |SEQUENCER Operator (VA = 8) has 2 children.
>>> | |
>>> | | |STORE Operator (VA = 4)
>>> | | | Worktable2 created, in allpages locking mode, for
>>> REFORMATTING.
>>> | | | Creating clustered index.
>>> | | |
>>> | | | |INSERT Operator (VA = 3)
>>> | | | | The update mode is direct.
>>> | | | |
>>> | | | | |HASH JOIN Operator (VA = 2) (Join Type: Inner
>>> Join)
>>> | | | | | Using Worktable1 for internal storage.
>>> | | | | | Key Count: 1
>>> | | | | |
>>> | | | | | |SCAN Operator (VA = 0)
>>> | | | | | | FROM TABLE
>>> | | | | | | mfs_media
>>> | | | | | | m
>>> | | | | | | 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.
>>> | | | | |
>>> | | | | | |SCAN Operator (VA = 1)
>>> | | | | | | FROM TABLE
>>> | | | | | | mfs_volume
>>> | | | | | | v
>>> | | | | | | 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.
>>> | | | |
>>> | | | | TO TABLE
>>> | | | | Worktable2.
>>> | |
>>> | | |NESTED LOOP JOIN Operator (VA = 7) (Join Type: Inner
>>> Join)
>>> | | |
>>> | | | |SCAN Operator (VA = 5)
>>> | | | | FROM TABLE
>>> | | | | mfs_files
>>> | | | | f
>>> | | | | Index : mfs_files_file_id
>>> | | | | Forward Scan.
>>> | | | | Positioning at index start.
>>> | | | | Using I/O Size 16 Kbytes for index leaf pages.
>>> | | | | With LRU Buffer Replacement Strategy for index
>>> leaf pages.
>>> | | | | Using I/O Size 16 Kbytes for data pages.
>>> | | | | With MRU Buffer Replacement Strategy for data
>>> pages.
>>> | | |
>>> | | | |SCAN Operator (VA = 6)
>>> | | | | FROM TABLE
>>> | | | | Worktable2.
>>> | | | | Using Clustered Index.
>>> | | | | Forward Scan.
>>> | | | | Positioning by key.
>>> | | | | Using I/O Size 16 Kbytes for data pages.
>>> | | | | With LRU Buffer Replacement Strategy for data
>>> pages.
>>>
>>>
>>> Total estimated I/O cost for statement 1 (at line 1): 571497253.
>>>
>>>
>>> *************************************************************************************
>>>
>>> NOW ASE 15.02 ESD#4 , exec time > 40 min , FULL table SCAN !!
>>>
>>> select m.media_type, m.mounted,
>>> 2> archive, file_id, format, fsize,
>>> uncomp_fsize,
>>> 3> stream, archive_status, status,
>>> compression
>>> 4> from mfs_files f
>>> 5> join mfs_volume v on v.volume_id =
>>> f.volume_id
>>> 6> join mfs_media m on v.media_id =
>>> m.media_id
>>> 7> order by file_id
>>> 8> plan '(use optgoal allrows_dss)'
>>> 9> go
>>>
>>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>>
>>>
>>> STEP 1
>>> The type of query is SELECT.
>>>
>>> 6 operator(s) under root
>>>
>>> |ROOT:EMIT Operator (VA = 6)
>>> |
>>> | |SORT Operator (VA = 5)
>>> | | Average Row width is 56.846985
>>> | | Using Worktable3 for internal storage.
>>> | |
>>> | | |HASH JOIN Operator (VA = 4) (Join Type: Inner Join)
>>> | | | Using Worktable2 for internal storage.
>>> | | | Key Count: 1
>>> | | |
>>> | | | |HASH JOIN Operator (VA = 2) (Join Type: Inner Join)
>>> | | | | Using Worktable1 for internal storage.
>>> | | | | Key Count: 1
>>> | | | |
>>> | | | | |SCAN Operator (VA = 0)
>>> | | | | | FROM TABLE
>>> | | | | | mfs_media
>>> | | | | | m
>>> | | | | | 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.
>>> | | | |
>>> | | | | |SCAN Operator (VA = 1)
>>> | | | | | FROM TABLE
>>> | | | | | mfs_volume
>>> | | | | | v
>>> | | | | | 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.
>>> | | |
>>> | | | |SCAN Operator (VA = 3)
>>> | | | | FROM TABLE
>>> | | | | mfs_files
>>> | | | | f
>>> | | | | Table Scan.
>>> | | | | Forward Scan.
>>> | | | | Positioning at start of table.
>>> | | | | Using I/O Size 16 Kbytes for data pages.
>>> | | | | With MRU Buffer Replacement Strategy for data
>>> pages.
>>>
>>>
>>> Total estimated I/O cost for statement 1 (at line 1): 369276437.
>>>
>>> *************************************************************************


Isabella Posted on 2008-06-12 19:26:31.0Z
From: Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
User-Agent: Thunderbird 1.5.0.12 (X11/20070530)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: optimizer changes ASE 15.02 ESD#4 vs ESD#2 ?
References: <48494b3a$1@forums-1-dub> <484f0f6e$1@forums-1-dub> <48515571$1@forums-1-dub> <48515cbf$1@forums-1-dub>
In-Reply-To: <48515cbf$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48517867@forums-1-dub>
Date: 12 Jun 2008 12:26:31 -0700
X-Trace: forums-1-dub 1213298791 10.22.241.152 (12 Jun 2008 12:26:31 -0700)
X-Original-Trace: 12 Jun 2008 12:26:31 -0700, vip152.sybase.com
Lines: 317
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10940
Article PK: 89551

Hi Kevin,
same same exec plan,see bellow, full table scan .

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SELECT.

6 operator(s) under root

|ROOT:EMIT Operator (VA = 6)

|
| |SORT Operator (VA = 5)
| | Average Row width is 57.023285
| | Using Worktable3 for internal storage.
| |
| | |HASH JOIN Operator (VA = 4) (Join Type: Inner Join)
| | | Using Worktable2 for internal storage.
| | | Key Count: 1
| | |
| | | |HASH JOIN Operator (VA = 2) (Join Type: Inner Join)
| | | | Using Worktable1 for internal storage.
| | | | Key Count: 1
| | | |
| | | | |SCAN Operator (VA = 0)
| | | | | FROM TABLE
| | | | | mfs_media
| | | | | m
| | | | | 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.
| | | |
| | | | |SCAN Operator (VA = 1)
| | | | | FROM TABLE
| | | | | mfs_volume
| | | | | v
| | | | | 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.
| | |
| | | |SCAN Operator (VA = 3)
| | | | FROM TABLE
| | | | mfs_files
| | | | f
| | | | Table Scan.
| | | | Forward Scan.
| | | | Positioning at start of table.
| | | | Using I/O Size 16 Kbytes for data pages.
| | | | With MRU Buffer Replacement Strategy for data pages.





Sherlock, Kevin wrote:
> I'm just curious. You are using the AP syntax here. Can you test this by
> using the "set plan optgoal allrows_dss" technique and eliminate the AP
> syntax at the end of the SQL?
>
> IE:
>
> set showplan on
> go
> set statement_cache off
> go
> set plan optgoal allrows_dss
> go
> select m.media_type, m.mounted,
> archive, file_id, format, fsize, uncomp_fsize,
> stream, archive_status, status, compression
> from mfs_files f
> join mfs_volume v on v.volume_id = f.volume_id
> join mfs_media m on v.media_id = m.media_id
> order by file_id
> go
>
>
> "Isabella" <isabella.ghiurea@nrc-cnrc.gc.ca> wrote in message
> news:48515571$1@forums-1-dub...
>>
>> I re-run 'reorg rebuid mfs_files " and run my SQL same showplan : full
>> table scan on mfs_files , which will fill up 6GB tempdb and the SQL will
>> hangup here....
>> Seems that the issue is the missing reformatting choice from ESD#4, in
>> ESD#2 as you see is using reformatting and mfs_files_file_id index .
>> I 'm not able to force reformatting in ESD#4....
>> Ref: optdiag, the output is too big to fit here, I can send you the file
>> (?)
>> Thank you,
>> Isabella
>>
>>
>> Isabella
>> IQ Rules wrote:
>>> Here is what I found.
>>>
>>> join mfs_volume v on v.volume_id = f.volume_id
>>> mfs_files_crc volume_id, fname nonclustered, unique
>>>
>>> Could be index mfs_files_crc fragmented?
>>>
>>> Can you run/post optdiag result, reindex (drop/recreate) mfs_files_crc,
>>> re-run this sql?
>>>
>>> I also noticed this.
>>> In DSS mode, ASE 15 uses Hash Join.
>>> In Mix mode, it uses Sort Merge, i/o counts about the same, but more i/o
>>> on tempdb.
>>>
>>> The table I test, Lock scheme Datarows, same as yours.
>>> And I don't have roundrobin partition.
>>>
>>>
>>> Isabella wrote:
>>>> Hello All,
>>>> We are in processing to switch to ASE 15.02 from ASE 12.5 Linux , we
>>>> ran our tests in April against ASE15.02 ESD#2, meanwhile after we aplied
>>>> the last ESD#4 in late April, a we found some issues with one of the
>>>> queries...We didn't re-test everything against new ESD#4.
>>>> Here is the the SQL and the showplan output in both ESD,
>>>> Mentioned running the SQL in ESD #2 after forcing the optimizer to user
>>>> dss goal , takes < 1 min !! big improvement from ASE 12.5.3 BUT running
>>>> same SQL in ASE 15.02 ESD #4 , optimizer is doing a FULL tables scan
>>>> and takes > 40 MIN !!!
>>>> What can be wrong , I delete and update index stats in Ase 15.02 ESD#4,
>>>> the biggest table is partition and see size
>>>> name rowtotal reserved data index_size unused
>>>> --------- -------- ----------- ---------- ---------- --------
>>>> 63936221 13516222 KB 7846458 KB 5640878 KB 28886 KB
>>>>
>>>>
>>>> ASE 15.02 ESD #2 , exec time < 1min
>>>>
>>>>
>>>> select m.media_type, m.mounted,
>>>> 2> archive, file_id, format, fsize,
>>>> uncomp_fsize,
>>>> 3> stream, archive_status, status,
>>>> compression
>>>> 4> from mfs_files f
>>>> 5> join mfs_volume v on v.volume_id =
>>>> f.volume_id
>>>> 6> join mfs_media m on v.media_id =
>>>> m.media_id
>>>> 7> order by file_id
>>>> 8> plan '(use optgoal allrows_dss)'
>>>> 9> go
>>>>
>>>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>>>
>>>>
>>>> STEP 1
>>>> The type of query is SELECT.
>>>>
>>>> 9 operator(s) under root
>>>>
>>>> |ROOT:EMIT Operator (VA = 9)
>>>> |
>>>> | |SEQUENCER Operator (VA = 8) has 2 children.
>>>> | |
>>>> | | |STORE Operator (VA = 4)
>>>> | | | Worktable2 created, in allpages locking mode, for
>>>> REFORMATTING.
>>>> | | | Creating clustered index.
>>>> | | |
>>>> | | | |INSERT Operator (VA = 3)
>>>> | | | | The update mode is direct.
>>>> | | | |
>>>> | | | | |HASH JOIN Operator (VA = 2) (Join Type: Inner
>>>> Join)
>>>> | | | | | Using Worktable1 for internal storage.
>>>> | | | | | Key Count: 1
>>>> | | | | |
>>>> | | | | | |SCAN Operator (VA = 0)
>>>> | | | | | | FROM TABLE
>>>> | | | | | | mfs_media
>>>> | | | | | | m
>>>> | | | | | | 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.
>>>> | | | | |
>>>> | | | | | |SCAN Operator (VA = 1)
>>>> | | | | | | FROM TABLE
>>>> | | | | | | mfs_volume
>>>> | | | | | | v
>>>> | | | | | | 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.
>>>> | | | |
>>>> | | | | TO TABLE
>>>> | | | | Worktable2.
>>>> | |
>>>> | | |NESTED LOOP JOIN Operator (VA = 7) (Join Type: Inner
>>>> Join)
>>>> | | |
>>>> | | | |SCAN Operator (VA = 5)
>>>> | | | | FROM TABLE
>>>> | | | | mfs_files
>>>> | | | | f
>>>> | | | | Index : mfs_files_file_id
>>>> | | | | Forward Scan.
>>>> | | | | Positioning at index start.
>>>> | | | | Using I/O Size 16 Kbytes for index leaf pages.
>>>> | | | | With LRU Buffer Replacement Strategy for index
>>>> leaf pages.
>>>> | | | | Using I/O Size 16 Kbytes for data pages.
>>>> | | | | With MRU Buffer Replacement Strategy for data
>>>> pages.
>>>> | | |
>>>> | | | |SCAN Operator (VA = 6)
>>>> | | | | FROM TABLE
>>>> | | | | Worktable2.
>>>> | | | | Using Clustered Index.
>>>> | | | | Forward Scan.
>>>> | | | | Positioning by key.
>>>> | | | | Using I/O Size 16 Kbytes for data pages.
>>>> | | | | With LRU Buffer Replacement Strategy for data
>>>> pages.
>>>>
>>>>
>>>> Total estimated I/O cost for statement 1 (at line 1): 571497253.
>>>>
>>>>
>>>> *************************************************************************************
>>>>
>>>> NOW ASE 15.02 ESD#4 , exec time > 40 min , FULL table SCAN !!
>>>>
>>>> select m.media_type, m.mounted,
>>>> 2> archive, file_id, format, fsize,
>>>> uncomp_fsize,
>>>> 3> stream, archive_status, status,
>>>> compression
>>>> 4> from mfs_files f
>>>> 5> join mfs_volume v on v.volume_id =
>>>> f.volume_id
>>>> 6> join mfs_media m on v.media_id =
>>>> m.media_id
>>>> 7> order by file_id
>>>> 8> plan '(use optgoal allrows_dss)'
>>>> 9> go
>>>>
>>>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>>>
>>>>
>>>> STEP 1
>>>> The type of query is SELECT.
>>>>
>>>> 6 operator(s) under root
>>>>
>>>> |ROOT:EMIT Operator (VA = 6)
>>>> |
>>>> | |SORT Operator (VA = 5)
>>>> | | Average Row width is 56.846985
>>>> | | Using Worktable3 for internal storage.
>>>> | |
>>>> | | |HASH JOIN Operator (VA = 4) (Join Type: Inner Join)
>>>> | | | Using Worktable2 for internal storage.
>>>> | | | Key Count: 1
>>>> | | |
>>>> | | | |HASH JOIN Operator (VA = 2) (Join Type: Inner Join)
>>>> | | | | Using Worktable1 for internal storage.
>>>> | | | | Key Count: 1
>>>> | | | |
>>>> | | | | |SCAN Operator (VA = 0)
>>>> | | | | | FROM TABLE
>>>> | | | | | mfs_media
>>>> | | | | | m
>>>> | | | | | 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.
>>>> | | | |
>>>> | | | | |SCAN Operator (VA = 1)
>>>> | | | | | FROM TABLE
>>>> | | | | | mfs_volume
>>>> | | | | | v
>>>> | | | | | 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.
>>>> | | |
>>>> | | | |SCAN Operator (VA = 3)
>>>> | | | | FROM TABLE
>>>> | | | | mfs_files
>>>> | | | | f
>>>> | | | | Table Scan.
>>>> | | | | Forward Scan.
>>>> | | | | Positioning at start of table.
>>>> | | | | Using I/O Size 16 Kbytes for data pages.
>>>> | | | | With MRU Buffer Replacement Strategy for data
>>>> pages.
>>>>
>>>>
>>>> Total estimated I/O cost for statement 1 (at line 1): 369276437.
>>>>
>>>> *************************************************************************
>
>


IQ Rules Posted on 2008-06-13 13:30:44.0Z
Sender: 2b23.48517554.1804289383@sybase.com
From: IQ Rules
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: optimizer changes ASE 15.02 ESD#4 vs ESD#2 ?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <48527684.5870.1681692777@sybase.com>
References: <48517867@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 13 Jun 2008 06:30:44 -0700
X-Trace: forums-1-dub 1213363844 10.22.241.41 (13 Jun 2008 06:30:44 -0700)
X-Original-Trace: 13 Jun 2008 06:30:44 -0700, 10.22.241.41
Lines: 4
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10944
Article PK: 89556

1. If you remove order by clause, does it still table scan
here?
2. When you created the roundrobin partition, after upgrade
to ESD 4?


Isabella Posted on 2008-06-13 16:10:09.0Z
From: Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
User-Agent: Thunderbird 1.5.0.12 (X11/20070530)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: optimizer changes ASE 15.02 ESD#4 vs ESD#2 ?
References: <48517867@forums-1-dub> <48527684.5870.1681692777@sybase.com>
In-Reply-To: <48527684.5870.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48529be1$1@forums-1-dub>
Date: 13 Jun 2008 09:10:09 -0700
X-Trace: forums-1-dub 1213373409 10.22.241.152 (13 Jun 2008 09:10:09 -0700)
X-Original-Trace: 13 Jun 2008 09:10:09 -0700, vip152.sybase.com
Lines: 19
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10946
Article PK: 89558


IQ Rules wrote:
> 1. If you remove order by clause, does it still table scan
> here?

yes tables for optgoal allrows_dss
no table scan for optgoal allrows_mix: using mfs_files

Index : mfs_files_crc

59> | | | Keys are:
60> | | | volume_id ASC

> 2. When you created the roundrobin partition, after upgrade
> to ESD 4?

in ASE 12.5.2 we have this tb already partition roundrobin 10
partitions, I upgrade to ASE 15.2 ESAD #2 and repartitions roundrobin
again, I applied ESD #4 and the SQL with opt goal mix or dss is
behaving a bit odder


IQ Rules Posted on 2008-06-13 20:56:58.0Z
Sender: 690e.4852dc75.1804289383@sybase.com
From: IQ Rules
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: optimizer changes ASE 15.02 ESD#4 vs ESD#2 ?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4852df1a.6958.1681692777@sybase.com>
References: <48529be1$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 13 Jun 2008 13:56:58 -0700
X-Trace: forums-1-dub 1213390618 10.22.241.41 (13 Jun 2008 13:56:58 -0700)
X-Original-Trace: 13 Jun 2008 13:56:58 -0700, 10.22.241.41
Lines: 21
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10951
Article PK: 89562

Can you add force index and with order clause?

select m.media_type, m.mounted,
2> archive, file_id,
format, fsize,
uncomp_fsize,
3> stream, archive_status,
status,
compression
4> from mfs_files f (index
mfs_files_crc)
5> join mfs_volume v on
v.volume_id =
f.volume_id
6> join mfs_media m on
v.media_id =
m.media_id
7> order by file_id
8> plan '(use optgoal
allrows_dss)'
9> go


Isabella Posted on 2008-06-13 21:49:56.0Z
From: Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
User-Agent: Thunderbird 1.5.0.12 (X11/20070530)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: optimizer changes ASE 15.02 ESD#4 vs ESD#2 ?
References: <48529be1$1@forums-1-dub> <4852df1a.6958.1681692777@sybase.com>
In-Reply-To: <4852df1a.6958.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4852eb84$1@forums-1-dub>
Date: 13 Jun 2008 14:49:56 -0700
X-Trace: forums-1-dub 1213393796 10.22.241.152 (13 Jun 2008 14:49:56 -0700)
X-Original-Trace: 13 Jun 2008 14:49:56 -0700, vip152.sybase.com
Lines: 32
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10953
Article PK: 89564

This is exactly what I'm doing in ESD #2 and works nice.
But in ESD#4 will not work anymore , I get a full table scan.
So my only workaround is to get the AP from ESD#2 into ESD#4 for this
query....but still doesn't answer my main issues :
What has been changed in ESD#4 optimizer to lose the performance from
ESD #2?
Isabella

IQ Rules wrote:
> Can you add force index and with order clause?
>
> select m.media_type, m.mounted,
> 2> archive, file_id,
> format, fsize,
> uncomp_fsize,
> 3> stream, archive_status,
> status,
> compression
> 4> from mfs_files f (index
> mfs_files_crc)
> 5> join mfs_volume v on
> v.volume_id =
> f.volume_id
> 6> join mfs_media m on
> v.media_id =
> m.media_id
> 7> order by file_id
> 8> plan '(use optgoal
> allrows_dss)'
> 9> go


kutlumustafa Posted on 2008-06-25 08:51:54.0Z
From: kutlumustafa@gmail.com
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: optimizer changes ASE 15.02 ESD#4 vs ESD#2 ?
Date: Wed, 25 Jun 2008 01:51:54 -0700 (PDT)
Organization: http://groups.google.com
Lines: 631
Message-ID: <27769458-9e2a-494e-9d01-822f8483d76c@59g2000hsb.googlegroups.com>
References: <48529be1$1@forums-1-dub> <4852df1a.6958.1681692777@sybase.com> <4852eb84$1@forums-1-dub>
NNTP-Posting-Host: 88.250.17.67
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-9
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1214383915 22414 127.0.0.1 (25 Jun 2008 08:51:55 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 25 Jun 2008 08:51:55 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: 59g2000hsb.googlegroups.com; posting-host=88.250.17.67; posting-account=v8ZWuQoAAAD0fSmFJel94MB9odGENl8F
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.gamma.ru!Gamma.RU!postnews.google.com!59g2000hsb.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10973
Article PK: 89584

On 14 Haziran, 00:49, Isabella <isabella.ghiu...@nrc-cnrc.gc.ca>

wrote:
> This is exactly what I'm doing in  ESD #2 and works nice.
> But in ESD#4 will not work anymore , I get a full table scan.
> So my only workaround is to get the AP from ESD#2 into ESD#4 for this
> query....but still doesn't answer my main issues :
> What has been changed in ESD#4 optimizer to lose  the performance  from
> ESD #2?
> Isabella
>
>
>
> IQ Rules wrote:
> > Can you add force index and with order clause?
>
> > select m.media_type, m.mounted,
> > 2>                                  archive, file_id,
> > format, fsize,
> > uncomp_fsize,
> > 3>                                  stream, archive_status,
> > status,
> > compression
> > 4>                              from mfs_files f (index
> > mfs_files_crc)
> > 5>                                 join mfs_volume v on
> > v.volume_id =
> > f.volume_id
> > 6>                                 join mfs_media m on
> > v.media_id =
> > m.media_id
> > 7>                              order by file_id
> > 8>                              plan  '(use optgoal
> > allrows_dss)'
> > 9> go- Alıntıyı gizle -
>
> - Alıntıyı göster -

Hi All,

we encountered same similar issues after upgraded from 15.02 esd#2 to
esd#4.
you may find the esd#2 and esd#4 plans and sp_help output below.


sp_help in esd#2 for 2 tables
Name Owner Object_type Create_date
--------- ----- ----------- -------------------
BEYANNAME dbo user table Nov 14 2007 12:13AM
Column_name Type Length Prec Scale Nulls Default_name
Rule_name Access_Rule_name Computed_Column_object Identity
----------------- -------- ------ ---- ----- ----- ------------
--------- ---------------- ---------------------- ----------
OID char 14 NULL NULL 0 (null)
(null) (null) (null) 0
PAKETOID char 14 NULL NULL 0 (null)
(null) (null) (null) 0
BEYANNAMETANIMOID char 14 NULL NULL 0 (null)
(null) (null) (null) 0
DOSYAADI varchar 50 NULL NULL 0 (null)
(null) (null) (null) 0
VERGINO char 10 NULL NULL 0 (null)
(null) (null) (null) 0
VDKODU char 6 NULL NULL 0 (null)
(null) (null) (null) 0
DONEM char 12 NULL NULL 0 (null)
(null) (null) (null) 0
DURUM smallint 2 NULL NULL 0 (null)
(null) (null) (null) 0
YUKLEMEZAMANI char 14 NULL NULL 0 (null)
(null) (null) (null) 0
TAHAKKUKOID char 14 NULL NULL 0 (null)
(null) (null) (null) 0
DONEMTIP int 4 NULL NULL 0 (null)
(null) (null) (null) 0
DONEMBASLANGIC char 8 NULL NULL 0 (null)
(null) (null) (null) 0
DONEMBITIS char 8 NULL NULL 0 (null)
(null) (null) (null) 0
BEYANIMYOK smallint 2 NULL NULL 0 (null)
(null) (null) (null) 0
TAHAKKUKFISNO char 20 NULL NULL 0 (null)
(null) (null) (null) 0
GECICIDONEM smallint 2 NULL NULL 0 (null)
(null) (null) (null) 0
MESAJVAR smallint 2 NULL NULL 0 (null)
(null) (null) (null) 0
IHBARNAMEKESILDI smallint 2 NULL NULL 0 (null)
(null) (null) (null) 0
Object has the following indexes

index_name index_keys index_description
index_max_rows_per_page index_fillfactor index_reservepagegap
index_created index_local
------------- -------------------------- --------------------
----------------------- ---------------- --------------------
------------------- ------------
BEYANNAME_IX2 PAKETOID
nonclustered 0
0 0 Nov 14 2007 3:38AM Global Index
BEYANNAME_IX3 BEYANNAMETANIMOID, VDKODU
nonclustered 0
0 0 Nov 14 2007 3:47AM Global Index
BEYANNAME_IX4 VERGINO
nonclustered 0
0 0 Nov 14 2007 3:38AM Global Index
BEYANNAME_IX1 OID nonclustered,
unique 0 0 0
Nov 14 2007 3:40AM Global Index
index_ptn_name index_ptn_seg
----------------------- -------------
BEYANNAME_IX2_570638245 default
BEYANNAME_IX3_570638245 default
BEYANNAME_IX4_570638245 default
BEYANNAME_IX1_570638245 default
No defined keys for this object.
name type partition_type partitions partition_keys
--------- ---------- -------------- ---------- --------------
BEYANNAME base table roundrobin 1 (null)

partition_name partition_id pages row_count segment
create_date
------------------------- ------------ -------- --------- -------
-------------------
BEYANNAME_REORG_570638245 570638245 15759419 147501062 default Nov
14 2007 12:13AM

Partition_Conditions
--------------------
(null)

Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ratio(Min/
Avg)
----------- ----------- ----------- ------------------------
------------------------
15759419 15759419 15759419 1.0
1.0
Lock scheme Datarows
The 'ascinserts' attribute is not applicable to tables with datarow or
datapage lock schemes.

exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap
ascinserts
------------ -------------- ---------- ----------------- ------------
-----------
0 0 0 0 0
0
concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg
------------------------- --------------------- -------------------
15 0 0




Name Owner Object_type Create_date
---------------- ----- ----------- -------------------
KIMLIK_BILGILERI dbo user table Sep 29 2004 2:40PM
Column_name Type Length Prec Scale Nulls Default_name
Rule_name Access_Rule_name Computed_Column_object Identity
--------------------- ------- ------ ---- ----- ----- ------------
--------- ---------------- ---------------------- ----------
OID char 14 NULL NULL 0 (null)
(null) (null) (null) 0
BEYANNAME_OID char 14 NULL NULL 0 (null)
(null) (null) (null) 0
TIP int 4 NULL NULL 0 (null)
(null) (null) (null) 0
SIFAT int 4 NULL NULL 0 (null)
(null) (null) (null) 0
VERGINO char 10 NULL NULL 0 (null)
(null) (null) (null) 0
SOYADI varchar 30 NULL NULL 0 (null)
(null) (null) (null) 0
ADI varchar 30 NULL NULL 0 (null)
(null) (null) (null) 0
TC_KIMLIK_NO char 11 NULL NULL 0 (null)
(null) (null) (null) 0
TIC_SICIL_NO varchar 20 NULL NULL 0 (null)
(null) (null) (null) 0
E_POSTA varchar 100 NULL NULL 0 (null)
(null) (null) (null) 0
IRTIBAT_TEL_ALAN_KODU char 3 NULL NULL 0 (null)
(null) (null) (null) 0
IRTIBAT_TEL_NO char 7 NULL NULL 0 (null)
(null) (null) (null) 0
Object has the following indexes

index_name index_keys index_description
index_max_rows_per_page index_fillfactor index_reservepagegap
index_created index_local
-------------------- -------------- --------------------
----------------------- ---------------- --------------------
------------------- ------------
KIMLIK_BILGILERI_IX1 OID nonclustered,
unique 0 0 0
Nov 10 2007 1:20AM Global Index
KIMLIK_BILGILERI_IX2 BEYANNAME_OID
nonclustered 0
0 0 Nov 10 2007 7:24AM Global Index
index_ptn_name index_ptn_seg
------------------------------- -------------
KIMLIK_BILGILERI_IX1_1396196993 default
KIMLIK_BILGILERI_IX2_1396196993 default
No defined keys for this object.
name type partition_type partitions partition_keys
---------------- ---------- -------------- ---------- --------------
KIMLIK_BILGILERI base table roundrobin 1 (null)

partition_name partition_id pages row_count segment
create_date
--------------------------- ------------ -------- --------- -------
-------------------
KIMLIK_BILGILERI_1396196993 1396196993 25145887 409120994 default
Nov 9 2007 6:45AM

Partition_Conditions
--------------------
(null)

Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ratio(Min/
Avg)
----------- ----------- ----------- ------------------------
------------------------
25145887 25145887 25145887 1.0
1.0
Lock scheme Datarows
The 'ascinserts' attribute is not applicable to tables with datarow or
datapage lock schemes.

exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap
ascinserts
------------ -------------- ---------- ----------------- ------------
-----------
0 0 0 0 0
0
concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg
------------------------- --------------------- -------------------
15 0 0


sp_help in esd#4 for the same tables

Name Owner Object_type Create_date
--------- ----- ----------- -------------------
BEYANNAME dbo user table Nov 14 2007 12:13AM
Column_name Type Length Prec Scale Nulls Default_name
Rule_name Access_Rule_name Computed_Column_object Identity
----------------- -------- ------ ---- ----- ----- ------------
--------- ---------------- ---------------------- ----------
OID char 14 NULL NULL 0 (null)
(null) (null) (null) 0
PAKETOID char 14 NULL NULL 0 (null)
(null) (null) (null) 0
BEYANNAMETANIMOID char 14 NULL NULL 0 (null)
(null) (null) (null) 0
DOSYAADI varchar 50 NULL NULL 0 (null)
(null) (null) (null) 0
VERGINO char 10 NULL NULL 0 (null)
(null) (null) (null) 0
VDKODU char 6 NULL NULL 0 (null)
(null) (null) (null) 0
DONEM char 12 NULL NULL 0 (null)
(null) (null) (null) 0
DURUM smallint 2 NULL NULL 0 (null)
(null) (null) (null) 0
YUKLEMEZAMANI char 14 NULL NULL 0 (null)
(null) (null) (null) 0
TAHAKKUKOID char 14 NULL NULL 0 (null)
(null) (null) (null) 0
DONEMTIP int 4 NULL NULL 0 (null)
(null) (null) (null) 0
DONEMBASLANGIC char 8 NULL NULL 0 (null)
(null) (null) (null) 0
DONEMBITIS char 8 NULL NULL 0 (null)
(null) (null) (null) 0
BEYANIMYOK smallint 2 NULL NULL 0 (null)
(null) (null) (null) 0
TAHAKKUKFISNO char 20 NULL NULL 0 (null)
(null) (null) (null) 0
GECICIDONEM smallint 2 NULL NULL 0 (null)
(null) (null) (null) 0
MESAJVAR smallint 2 NULL NULL 0 (null)
(null) (null) (null) 0
IHBARNAMEKESILDI smallint 2 NULL NULL 0 (null)
(null) (null) (null) 0
Object has the following indexes

index_name index_keys index_description
index_max_rows_per_page index_fillfactor index_reservepagegap
index_created index_local
------------- -------------------------- --------------------
----------------------- ---------------- --------------------
------------------- ------------
BEYANNAME_IX2 PAKETOID
nonclustered 0
0 0 Nov 14 2007 3:38AM Global Index
BEYANNAME_IX3 BEYANNAMETANIMOID, VDKODU
nonclustered 0
0 0 Nov 14 2007 3:47AM Global Index
BEYANNAME_IX4 VERGINO
nonclustered 0
0 0 Nov 14 2007 3:38AM Global Index
BEYANNAME_IX1 OID nonclustered,
unique 0 0 0
Nov 14 2007 3:40AM Global Index
index_ptn_name index_ptn_seg
----------------------- -------------
BEYANNAME_IX2_570638245 default
BEYANNAME_IX3_570638245 default
BEYANNAME_IX4_570638245 default
BEYANNAME_IX1_570638245 default
No defined keys for this object.
name type partition_type partitions partition_keys
--------- ---------- -------------- ---------- --------------
BEYANNAME base table roundrobin 1 (null)

partition_name partition_id pages row_count segment
create_date
------------------------- ------------ -------- --------- -------
-------------------
BEYANNAME_REORG_570638245 570638245 15759310 147519645 default Nov
14 2007 12:13AM

Partition_Conditions
--------------------
(null)

Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ratio(Min/
Avg)
----------- ----------- ----------- ------------------------
------------------------
15759310 15759310 15759310 1.0
1.0
Lock scheme Datarows
The 'ascinserts' attribute is not applicable to tables with datarow or
datapage lock schemes.

exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap
ascinserts
------------ -------------- ---------- ----------------- ------------
-----------
0 0 0 0 0
0
concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg
------------------------- --------------------- -------------------
15 0 0





Name Owner Object_type Create_date
---------------- ----- ----------- -------------------
KIMLIK_BILGILERI dbo user table Sep 29 2004 2:40PM
Column_name Type Length Prec Scale Nulls Default_name
Rule_name Access_Rule_name Computed_Column_object Identity
--------------------- ------- ------ ---- ----- ----- ------------
--------- ---------------- ---------------------- ----------
OID char 14 NULL NULL 0 (null)
(null) (null) (null) 0
BEYANNAME_OID char 14 NULL NULL 0 (null)
(null) (null) (null) 0
TIP int 4 NULL NULL 0 (null)
(null) (null) (null) 0
SIFAT int 4 NULL NULL 0 (null)
(null) (null) (null) 0
VERGINO char 10 NULL NULL 0 (null)
(null) (null) (null) 0
SOYADI varchar 30 NULL NULL 0 (null)
(null) (null) (null) 0
ADI varchar 30 NULL NULL 0 (null)
(null) (null) (null) 0
TC_KIMLIK_NO char 11 NULL NULL 0 (null)
(null) (null) (null) 0
TIC_SICIL_NO varchar 20 NULL NULL 0 (null)
(null) (null) (null) 0
E_POSTA varchar 100 NULL NULL 0 (null)
(null) (null) (null) 0
IRTIBAT_TEL_ALAN_KODU char 3 NULL NULL 0 (null)
(null) (null) (null) 0
IRTIBAT_TEL_NO char 7 NULL NULL 0 (null)
(null) (null) (null) 0
Object has the following indexes

index_name index_keys index_description
index_max_rows_per_page index_fillfactor index_reservepagegap
index_created index_local
-------------------- -------------- --------------------
----------------------- ---------------- --------------------
------------------- ------------
KIMLIK_BILGILERI_IX1 OID nonclustered,
unique 0 0 0
Nov 10 2007 1:20AM Global Index
KIMLIK_BILGILERI_IX2 BEYANNAME_OID
nonclustered 0
0 0 Nov 10 2007 7:24AM Global Index
index_ptn_name index_ptn_seg
------------------------------- -------------
KIMLIK_BILGILERI_IX1_1396196993 default
KIMLIK_BILGILERI_IX2_1396196993 default
No defined keys for this object.
name type partition_type partitions partition_keys
---------------- ---------- -------------- ---------- --------------
KIMLIK_BILGILERI base table roundrobin 1 (null)

partition_name partition_id pages row_count segment
create_date
--------------------------- ------------ -------- --------- -------
-------------------
KIMLIK_BILGILERI_1396196993 1396196993 25145866 409170888 default
Nov 9 2007 6:45AM


query plan in esd#2
QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is EXECUTE.
Executing a newly cached statement.


QUERY PLAN FOR STATEMENT 1 (at line 0).


STEP 1
The type of query is DECLARE.


QUERY PLAN FOR STATEMENT 2 (at line 1).


STEP 1
The type of query is SELECT.

5 operator(s) under root

|ROOT:EMIT Operator (VA = 5)
|
| |NESTED LOOP JOIN Operator (VA = 4) (Join Type: Inner Join)
| |
| | |NESTED LOOP JOIN Operator (VA = 2) (Join Type: Left
Outer Join)
| | |
| | | |SCAN Operator (VA = 0)
| | | | FROM TABLE
| | | | ebyn..BEYANNAME
| | | | t0
| | | | Index : BEYANNAME_IX2
| | | | Forward Scan.
| | | | Positioning by key.
| | | | Keys are:
| | | | PAKETOID ASC
| | | | Using I/O Size 2 Kbytes for index leaf pages.
| | | | With LRU Buffer Replacement Strategy for index
leaf pages.
| | | | Using I/O Size 2 Kbytes for data pages.
| | | | With LRU Buffer Replacement Strategy for data
pages.
| | |
| | | |SCAN Operator (VA = 1)
| | | | FROM TABLE
| | | | ebyn..KIMLIK_BILGILERI
| | | | t2
| | | | Index : KIMLIK_BILGILERI_IX2
| | | | Forward Scan.
| | | | Positioning by key.
| | | | Keys are:
| | | | BEYANNAME_OID ASC
| | | | Using I/O Size 2 Kbytes for index leaf pages.
| | | | With LRU Buffer Replacement Strategy for index
leaf pages.
| | | | Using I/O Size 2 Kbytes for data pages.
| | | | With LRU Buffer Replacement Strategy for data
pages.
| |
| | |SCAN Operator (VA = 3)
| | | FROM TABLE
| | | ebyn..PAKET
| | | t1
| | | Index : PAKET_IX
| | | Forward Scan.
| | | Positioning by key.
| | | Keys are:
| | | OID ASC
| | | Using I/O Size 2 Kbytes for index leaf pages.
| | | With LRU Buffer Replacement Strategy for index leaf
pages.
| | | Using I/O Size 2 Kbytes for data pages.
| | | With LRU Buffer Replacement Strategy for data pages.



QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET OPTION OFF.


QUERY PLAN FOR STATEMENT 2 (at line 2).


STEP 1
The type of query is SET OPTION OFF.


query plan in esd#4
QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SELECT.

10 operator(s) under root

|ROOT:EMIT Operator (VA = 10)
|
| |RESTRICT Operator (VA = 9)(0)(0)(3)(0)(0)
| |
| | |SEQUENCER Operator (VA = 8) has 2 children.
| | |
| | | |STORE Operator (VA = 2)
| | | | Worktable1 created, in allpages locking mode,
for REFORMATTING.
| | | | Creating clustered index.
| | | |
| | | | |INSERT Operator (VA = 1)
| | | | | The update mode is direct.
| | | | |
| | | | | |SCAN Operator (VA = 0)
| | | | | | FROM TABLE
| | | | | | ebyn..KIMLIK_BILGILERI
| | | | | | t2
| | | | | | Table Scan.
| | | | | | Forward Scan.
| | | | | | Positioning at start of table.
| | | | | | Using I/O Size 16 Kbytes for data pages.
| | | | | | With MRU Buffer Replacement Strategy for
data pages.
| | | | |
| | | | | TO TABLE
| | | | | Worktable1.
| | |
| | | |NESTED LOOP JOIN Operator (VA = 7) (Join Type:
Inner Join)
| | | |
| | | | |NESTED LOOP JOIN Operator (VA = 5) (Join Type:
Left Outer Join)
| | | | |
| | | | | |SCAN Operator (VA = 3)
| | | | | | FROM TABLE
| | | | | | ebyn..BEYANNAME
| | | | | | t0
| | | | | | Table Scan.
| | | | | | Forward Scan.
| | | | | | Positioning at start of table.
| | | | | | Using I/O Size 16 Kbytes for data pages.
| | | | | | With MRU Buffer Replacement Strategy for
data pages.
| | | | |
| | | | | |SCAN Operator (VA = 4)
| | | | | | FROM TABLE
| | | | | | Worktable1.
| | | | | | Using Clustered Index.
| | | | | | Forward Scan.
| | | | | | Positioning by key.
| | | | | | Using I/O Size 16 Kbytes for data pages.
| | | | | | With LRU Buffer Replacement Strategy for
data pages.
| | | |
| | | | |SCAN Operator (VA = 6)
| | | | | FROM TABLE
| | | | | ebyn..PAKET
| | | | | t1
| | | | | Index : PAKET_IX
| | | | | Forward Scan.
| | | | | Positioning by key.
| | | | | Keys are:
| | | | | OID ASC
| | | | | Using I/O Size 2 Kbytes for index leaf
pages.
| | | | | With LRU Buffer Replacement Strategy for
index leaf pages.
| | | | | Using I/O Size 2 Kbytes for data pages.
| | | | | With LRU Buffer Replacement Strategy for
data pages.


Total estimated I/O cost for statement 1 (at line 1): 2147483647.


QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET OPTION OFF.


QUERY PLAN FOR STATEMENT 2 (at line 2).


STEP 1
The type of query is SET OPTION OFF.


and the query is;
select
t0.OID,t0.PAKETOID,t0.DOSYAADI,t0.VERGINO,t0.VDKODU,t0.DONEM,t0.DURUM,t0.YUKLEMEZAMANI,t0.TAHAKKUKOID,t0.BEYANNAMETANIMOID,t0.DONEMTIP,t0.DONEMBASLANGIC,t0.DONEMBITIS,t0.BEYANIMYOK,t1.DOSYAADI,t0.TAHAKKUKFISNO,t0.GECICIDONEM,t0.MESAJVAR,t0.IHBARNAMEKESILDI,t2.TC_KIMLIK_NO,t1.GONDERENOID
from ebyn..BEYANNAME t0,
ebyn..PAKET t1,
ebyn..KIMLIK_BILGILERI t2
where t1.OID = "01f8x7x2ka16hq" and
t1.OID = t0.PAKETOID and
t0.OID *= t2.BEYANNAME_OID and
t2.TIP = 1


Kind Regards,

Mustafa Kutlu


Isabella Posted on 2008-06-25 16:21:16.0Z
Message-ID: <4862707B.6050700@nrc-cnrc.gc.ca>
From: Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
User-Agent: Thunderbird 1.5.0.12 (X11/20070530)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
To: kutlumustafa@gmail.com
Subject: Re: optimizer changes ASE 15.02 ESD#4 vs ESD#2 ?
References: <48529be1$1@forums-1-dub> <4852df1a.6958.1681692777@sybase.com> <4852eb84$1@forums-1-dub> <27769458-9e2a-494e-9d01-822f8483d76c@59g2000hsb.googlegroups.com>
In-Reply-To: <27769458-9e2a-494e-9d01-822f8483d76c@59g2000hsb.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-9; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 25 Jun 2008 09:21:16 -0700
X-Trace: forums-1-dub 1214410876 10.22.241.152 (25 Jun 2008 09:21:16 -0700)
X-Original-Trace: 25 Jun 2008 09:21:16 -0700, vip152.sybase.com
Lines: 631
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10974
Article PK: 89585

Hi ,

Did you open a case with TSybase about the issue ? I have one open
already I'm waiting for them to be able to reproduce the 'bug' ...I
think may help if other peoples are having same issue...


cheers,
Isabella

kutlumustafa@gmail.com wrote:
> On 14 Haziran, 00:49, Isabella <isabella.ghiu...@nrc-cnrc.gc.ca>
> wrote:
>> This is exactly what I'm doing in ESD #2 and works nice.
>> But in ESD#4 will not work anymore , I get a full table scan.
>> So my only workaround is to get the AP from ESD#2 into ESD#4 for this
>> query....but still doesn't answer my main issues :
>> What has been changed in ESD#4 optimizer to lose the performance from
>> ESD #2?
>> Isabella
>>
>>
>>
>> IQ Rules wrote:
>>> Can you add force index and with order clause?
>>> select m.media_type, m.mounted,
>>> 2> archive, file_id,
>>> format, fsize,
>>> uncomp_fsize,
>>> 3> stream, archive_status,
>>> status,
>>> compression
>>> 4> from mfs_files f (index
>>> mfs_files_crc)
>>> 5> join mfs_volume v on
>>> v.volume_id =
>>> f.volume_id
>>> 6> join mfs_media m on
>>> v.media_id =
>>> m.media_id
>>> 7> order by file_id
>>> 8> plan '(use optgoal
>>> allrows_dss)'
>>> 9> go- Alýntýyý gizle -
>> - Alýntýyý göster -
>
> Hi All,
>
> we encountered same similar issues after upgraded from 15.02 esd#2 to
> esd#4.
> you may find the esd#2 and esd#4 plans and sp_help output below.
>
>
> sp_help in esd#2 for 2 tables
> Name Owner Object_type Create_date
> --------- ----- ----------- -------------------
> BEYANNAME dbo user table Nov 14 2007 12:13AM
> Column_name Type Length Prec Scale Nulls Default_name
> Rule_name Access_Rule_name Computed_Column_object Identity
> ----------------- -------- ------ ---- ----- ----- ------------
> --------- ---------------- ---------------------- ----------
> OID char 14 NULL NULL 0 (null)
> (null) (null) (null) 0
> PAKETOID char 14 NULL NULL 0 (null)
> (null) (null) (null) 0
> BEYANNAMETANIMOID char 14 NULL NULL 0 (null)
> (null) (null) (null) 0
> DOSYAADI varchar 50 NULL NULL 0 (null)
> (null) (null) (null) 0
> VERGINO char 10 NULL NULL 0 (null)
> (null) (null) (null) 0
> VDKODU char 6 NULL NULL 0 (null)
> (null) (null) (null) 0
> DONEM char 12 NULL NULL 0 (null)
> (null) (null) (null) 0
> DURUM smallint 2 NULL NULL 0 (null)
> (null) (null) (null) 0
> YUKLEMEZAMANI char 14 NULL NULL 0 (null)
> (null) (null) (null) 0
> TAHAKKUKOID char 14 NULL NULL 0 (null)
> (null) (null) (null) 0
> DONEMTIP int 4 NULL NULL 0 (null)
> (null) (null) (null) 0
> DONEMBASLANGIC char 8 NULL NULL 0 (null)
> (null) (null) (null) 0
> DONEMBITIS char 8 NULL NULL 0 (null)
> (null) (null) (null) 0
> BEYANIMYOK smallint 2 NULL NULL 0 (null)
> (null) (null) (null) 0
> TAHAKKUKFISNO char 20 NULL NULL 0 (null)
> (null) (null) (null) 0
> GECICIDONEM smallint 2 NULL NULL 0 (null)
> (null) (null) (null) 0
> MESAJVAR smallint 2 NULL NULL 0 (null)
> (null) (null) (null) 0
> IHBARNAMEKESILDI smallint 2 NULL NULL 0 (null)
> (null) (null) (null) 0
> Object has the following indexes
>
> index_name index_keys index_description
> index_max_rows_per_page index_fillfactor index_reservepagegap
> index_created index_local
> ------------- -------------------------- --------------------
> ----------------------- ---------------- --------------------
> ------------------- ------------
> BEYANNAME_IX2 PAKETOID
> nonclustered 0
> 0 0 Nov 14 2007 3:38AM Global Index
> BEYANNAME_IX3 BEYANNAMETANIMOID, VDKODU
> nonclustered 0
> 0 0 Nov 14 2007 3:47AM Global Index
> BEYANNAME_IX4 VERGINO
> nonclustered 0
> 0 0 Nov 14 2007 3:38AM Global Index
> BEYANNAME_IX1 OID nonclustered,
> unique 0 0 0
> Nov 14 2007 3:40AM Global Index
> index_ptn_name index_ptn_seg
> ----------------------- -------------
> BEYANNAME_IX2_570638245 default
> BEYANNAME_IX3_570638245 default
> BEYANNAME_IX4_570638245 default
> BEYANNAME_IX1_570638245 default
> No defined keys for this object.
> name type partition_type partitions partition_keys
> --------- ---------- -------------- ---------- --------------
> BEYANNAME base table roundrobin 1 (null)
>
> partition_name partition_id pages row_count segment
> create_date
> ------------------------- ------------ -------- --------- -------
> -------------------
> BEYANNAME_REORG_570638245 570638245 15759419 147501062 default Nov
> 14 2007 12:13AM
>
> Partition_Conditions
> --------------------
> (null)
>
> Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ratio(Min/
> Avg)
> ----------- ----------- ----------- ------------------------
> ------------------------
> 15759419 15759419 15759419 1.0
> 1.0
> Lock scheme Datarows
> The 'ascinserts' attribute is not applicable to tables with datarow or
> datapage lock schemes.
>
> exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap
> ascinserts
> ------------ -------------- ---------- ----------------- ------------
> -----------
> 0 0 0 0 0
> 0
> concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg
> ------------------------- --------------------- -------------------
> 15 0 0
>
>
>
>
> Name Owner Object_type Create_date
> ---------------- ----- ----------- -------------------
> KIMLIK_BILGILERI dbo user table Sep 29 2004 2:40PM
> Column_name Type Length Prec Scale Nulls Default_name
> Rule_name Access_Rule_name Computed_Column_object Identity
> --------------------- ------- ------ ---- ----- ----- ------------
> --------- ---------------- ---------------------- ----------
> OID char 14 NULL NULL 0 (null)
> (null) (null) (null) 0
> BEYANNAME_OID char 14 NULL NULL 0 (null)
> (null) (null) (null) 0
> TIP int 4 NULL NULL 0 (null)
> (null) (null) (null) 0
> SIFAT int 4 NULL NULL 0 (null)
> (null) (null) (null) 0
> VERGINO char 10 NULL NULL 0 (null)
> (null) (null) (null) 0
> SOYADI varchar 30 NULL NULL 0 (null)
> (null) (null) (null) 0
> ADI varchar 30 NULL NULL 0 (null)
> (null) (null) (null) 0
> TC_KIMLIK_NO char 11 NULL NULL 0 (null)
> (null) (null) (null) 0
> TIC_SICIL_NO varchar 20 NULL NULL 0 (null)
> (null) (null) (null) 0
> E_POSTA varchar 100 NULL NULL 0 (null)
> (null) (null) (null) 0
> IRTIBAT_TEL_ALAN_KODU char 3 NULL NULL 0 (null)
> (null) (null) (null) 0
> IRTIBAT_TEL_NO char 7 NULL NULL 0 (null)
> (null) (null) (null) 0
> Object has the following indexes
>
> index_name index_keys index_description
> index_max_rows_per_page index_fillfactor index_reservepagegap
> index_created index_local
> -------------------- -------------- --------------------
> ----------------------- ---------------- --------------------
> ------------------- ------------
> KIMLIK_BILGILERI_IX1 OID nonclustered,
> unique 0 0 0
> Nov 10 2007 1:20AM Global Index
> KIMLIK_BILGILERI_IX2 BEYANNAME_OID
> nonclustered 0
> 0 0 Nov 10 2007 7:24AM Global Index
> index_ptn_name index_ptn_seg
> ------------------------------- -------------
> KIMLIK_BILGILERI_IX1_1396196993 default
> KIMLIK_BILGILERI_IX2_1396196993 default
> No defined keys for this object.
> name type partition_type partitions partition_keys
> ---------------- ---------- -------------- ---------- --------------
> KIMLIK_BILGILERI base table roundrobin 1 (null)
>
> partition_name partition_id pages row_count segment
> create_date
> --------------------------- ------------ -------- --------- -------
> -------------------
> KIMLIK_BILGILERI_1396196993 1396196993 25145887 409120994 default
> Nov 9 2007 6:45AM
>
> Partition_Conditions
> --------------------
> (null)
>
> Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ratio(Min/
> Avg)
> ----------- ----------- ----------- ------------------------
> ------------------------
> 25145887 25145887 25145887 1.0
> 1.0
> Lock scheme Datarows
> The 'ascinserts' attribute is not applicable to tables with datarow or
> datapage lock schemes.
>
> exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap
> ascinserts
> ------------ -------------- ---------- ----------------- ------------
> -----------
> 0 0 0 0 0
> 0
> concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg
> ------------------------- --------------------- -------------------
> 15 0 0
>
>
> sp_help in esd#4 for the same tables
>
> Name Owner Object_type Create_date
> --------- ----- ----------- -------------------
> BEYANNAME dbo user table Nov 14 2007 12:13AM
> Column_name Type Length Prec Scale Nulls Default_name
> Rule_name Access_Rule_name Computed_Column_object Identity
> ----------------- -------- ------ ---- ----- ----- ------------
> --------- ---------------- ---------------------- ----------
> OID char 14 NULL NULL 0 (null)
> (null) (null) (null) 0
> PAKETOID char 14 NULL NULL 0 (null)
> (null) (null) (null) 0
> BEYANNAMETANIMOID char 14 NULL NULL 0 (null)
> (null) (null) (null) 0
> DOSYAADI varchar 50 NULL NULL 0 (null)
> (null) (null) (null) 0
> VERGINO char 10 NULL NULL 0 (null)
> (null) (null) (null) 0
> VDKODU char 6 NULL NULL 0 (null)
> (null) (null) (null) 0
> DONEM char 12 NULL NULL 0 (null)
> (null) (null) (null) 0
> DURUM smallint 2 NULL NULL 0 (null)
> (null) (null) (null) 0
> YUKLEMEZAMANI char 14 NULL NULL 0 (null)
> (null) (null) (null) 0
> TAHAKKUKOID char 14 NULL NULL 0 (null)
> (null) (null) (null) 0
> DONEMTIP int 4 NULL NULL 0 (null)
> (null) (null) (null) 0
> DONEMBASLANGIC char 8 NULL NULL 0 (null)
> (null) (null) (null) 0
> DONEMBITIS char 8 NULL NULL 0 (null)
> (null) (null) (null) 0
> BEYANIMYOK smallint 2 NULL NULL 0 (null)
> (null) (null) (null) 0
> TAHAKKUKFISNO char 20 NULL NULL 0 (null)
> (null) (null) (null) 0
> GECICIDONEM smallint 2 NULL NULL 0 (null)
> (null) (null) (null) 0
> MESAJVAR smallint 2 NULL NULL 0 (null)
> (null) (null) (null) 0
> IHBARNAMEKESILDI smallint 2 NULL NULL 0 (null)
> (null) (null) (null) 0
> Object has the following indexes
>
> index_name index_keys index_description
> index_max_rows_per_page index_fillfactor index_reservepagegap
> index_created index_local
> ------------- -------------------------- --------------------
> ----------------------- ---------------- --------------------
> ------------------- ------------
> BEYANNAME_IX2 PAKETOID
> nonclustered 0
> 0 0 Nov 14 2007 3:38AM Global Index
> BEYANNAME_IX3 BEYANNAMETANIMOID, VDKODU
> nonclustered 0
> 0 0 Nov 14 2007 3:47AM Global Index
> BEYANNAME_IX4 VERGINO
> nonclustered 0
> 0 0 Nov 14 2007 3:38AM Global Index
> BEYANNAME_IX1 OID nonclustered,
> unique 0 0 0
> Nov 14 2007 3:40AM Global Index
> index_ptn_name index_ptn_seg
> ----------------------- -------------
> BEYANNAME_IX2_570638245 default
> BEYANNAME_IX3_570638245 default
> BEYANNAME_IX4_570638245 default
> BEYANNAME_IX1_570638245 default
> No defined keys for this object.
> name type partition_type partitions partition_keys
> --------- ---------- -------------- ---------- --------------
> BEYANNAME base table roundrobin 1 (null)
>
> partition_name partition_id pages row_count segment
> create_date
> ------------------------- ------------ -------- --------- -------
> -------------------
> BEYANNAME_REORG_570638245 570638245 15759310 147519645 default Nov
> 14 2007 12:13AM
>
> Partition_Conditions
> --------------------
> (null)
>
> Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ratio(Min/
> Avg)
> ----------- ----------- ----------- ------------------------
> ------------------------
> 15759310 15759310 15759310 1.0
> 1.0
> Lock scheme Datarows
> The 'ascinserts' attribute is not applicable to tables with datarow or
> datapage lock schemes.
>
> exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap
> ascinserts
> ------------ -------------- ---------- ----------------- ------------
> -----------
> 0 0 0 0 0
> 0
> concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg
> ------------------------- --------------------- -------------------
> 15 0 0
>
>
>
>
>
> Name Owner Object_type Create_date
> ---------------- ----- ----------- -------------------
> KIMLIK_BILGILERI dbo user table Sep 29 2004 2:40PM
> Column_name Type Length Prec Scale Nulls Default_name
> Rule_name Access_Rule_name Computed_Column_object Identity
> --------------------- ------- ------ ---- ----- ----- ------------
> --------- ---------------- ---------------------- ----------
> OID char 14 NULL NULL 0 (null)
> (null) (null) (null) 0
> BEYANNAME_OID char 14 NULL NULL 0 (null)
> (null) (null) (null) 0
> TIP int 4 NULL NULL 0 (null)
> (null) (null) (null) 0
> SIFAT int 4 NULL NULL 0 (null)
> (null) (null) (null) 0
> VERGINO char 10 NULL NULL 0 (null)
> (null) (null) (null) 0
> SOYADI varchar 30 NULL NULL 0 (null)
> (null) (null) (null) 0
> ADI varchar 30 NULL NULL 0 (null)
> (null) (null) (null) 0
> TC_KIMLIK_NO char 11 NULL NULL 0 (null)
> (null) (null) (null) 0
> TIC_SICIL_NO varchar 20 NULL NULL 0 (null)
> (null) (null) (null) 0
> E_POSTA varchar 100 NULL NULL 0 (null)
> (null) (null) (null) 0
> IRTIBAT_TEL_ALAN_KODU char 3 NULL NULL 0 (null)
> (null) (null) (null) 0
> IRTIBAT_TEL_NO char 7 NULL NULL 0 (null)
> (null) (null) (null) 0
> Object has the following indexes
>
> index_name index_keys index_description
> index_max_rows_per_page index_fillfactor index_reservepagegap
> index_created index_local
> -------------------- -------------- --------------------
> ----------------------- ---------------- --------------------
> ------------------- ------------
> KIMLIK_BILGILERI_IX1 OID nonclustered,
> unique 0 0 0
> Nov 10 2007 1:20AM Global Index
> KIMLIK_BILGILERI_IX2 BEYANNAME_OID
> nonclustered 0
> 0 0 Nov 10 2007 7:24AM Global Index
> index_ptn_name index_ptn_seg
> ------------------------------- -------------
> KIMLIK_BILGILERI_IX1_1396196993 default
> KIMLIK_BILGILERI_IX2_1396196993 default
> No defined keys for this object.
> name type partition_type partitions partition_keys
> ---------------- ---------- -------------- ---------- --------------
> KIMLIK_BILGILERI base table roundrobin 1 (null)
>
> partition_name partition_id pages row_count segment
> create_date
> --------------------------- ------------ -------- --------- -------
> -------------------
> KIMLIK_BILGILERI_1396196993 1396196993 25145866 409170888 default
> Nov 9 2007 6:45AM
>
>
> query plan in esd#2
> QUERY PLAN FOR STATEMENT 1 (at line 1).
>
>
> STEP 1
> The type of query is EXECUTE.
> Executing a newly cached statement.
>
>
> QUERY PLAN FOR STATEMENT 1 (at line 0).
>
>
> STEP 1
> The type of query is DECLARE.
>
>
> QUERY PLAN FOR STATEMENT 2 (at line 1).
>
>
> STEP 1
> The type of query is SELECT.
>
> 5 operator(s) under root
>
> |ROOT:EMIT Operator (VA = 5)
> |
> | |NESTED LOOP JOIN Operator (VA = 4) (Join Type: Inner Join)
> | |
> | | |NESTED LOOP JOIN Operator (VA = 2) (Join Type: Left
> Outer Join)
> | | |
> | | | |SCAN Operator (VA = 0)
> | | | | FROM TABLE
> | | | | ebyn..BEYANNAME
> | | | | t0
> | | | | Index : BEYANNAME_IX2
> | | | | Forward Scan.
> | | | | Positioning by key.
> | | | | Keys are:
> | | | | PAKETOID ASC
> | | | | Using I/O Size 2 Kbytes for index leaf pages.
> | | | | With LRU Buffer Replacement Strategy for index
> leaf pages.
> | | | | Using I/O Size 2 Kbytes for data pages.
> | | | | With LRU Buffer Replacement Strategy for data
> pages.
> | | |
> | | | |SCAN Operator (VA = 1)
> | | | | FROM TABLE
> | | | | ebyn..KIMLIK_BILGILERI
> | | | | t2
> | | | | Index : KIMLIK_BILGILERI_IX2
> | | | | Forward Scan.
> | | | | Positioning by key.
> | | | | Keys are:
> | | | | BEYANNAME_OID ASC
> | | | | Using I/O Size 2 Kbytes for index leaf pages.
> | | | | With LRU Buffer Replacement Strategy for index
> leaf pages.
> | | | | Using I/O Size 2 Kbytes for data pages.
> | | | | With LRU Buffer Replacement Strategy for data
> pages.
> | |
> | | |SCAN Operator (VA = 3)
> | | | FROM TABLE
> | | | ebyn..PAKET
> | | | t1
> | | | Index : PAKET_IX
> | | | Forward Scan.
> | | | Positioning by key.
> | | | Keys are:
> | | | OID ASC
> | | | Using I/O Size 2 Kbytes for index leaf pages.
> | | | With LRU Buffer Replacement Strategy for index leaf
> pages.
> | | | Using I/O Size 2 Kbytes for data pages.
> | | | With LRU Buffer Replacement Strategy for data pages.
>
>
>
> QUERY PLAN FOR STATEMENT 1 (at line 1).
>
>
> STEP 1
> The type of query is SET OPTION OFF.
>
>
> QUERY PLAN FOR STATEMENT 2 (at line 2).
>
>
> STEP 1
> The type of query is SET OPTION OFF.
>
>
> query plan in esd#4
> QUERY PLAN FOR STATEMENT 1 (at line 1).
>
>
> STEP 1
> The type of query is SELECT.
>
> 10 operator(s) under root
>
> |ROOT:EMIT Operator (VA = 10)
> |
> | |RESTRICT Operator (VA = 9)(0)(0)(3)(0)(0)
> | |
> | | |SEQUENCER Operator (VA = 8) has 2 children.
> | | |
> | | | |STORE Operator (VA = 2)
> | | | | Worktable1 created, in allpages locking mode,
> for REFORMATTING.
> | | | | Creating clustered index.
> | | | |
> | | | | |INSERT Operator (VA = 1)
> | | | | | The update mode is direct.
> | | | | |
> | | | | | |SCAN Operator (VA = 0)
> | | | | | | FROM TABLE
> | | | | | | ebyn..KIMLIK_BILGILERI
> | | | | | | t2
> | | | | | | Table Scan.
> | | | | | | Forward Scan.
> | | | | | | Positioning at start of table.
> | | | | | | Using I/O Size 16 Kbytes for data pages.
> | | | | | | With MRU Buffer Replacement Strategy for
> data pages.
> | | | | |
> | | | | | TO TABLE
> | | | | | Worktable1.
> | | |
> | | | |NESTED LOOP JOIN Operator (VA = 7) (Join Type:
> Inner Join)
> | | | |
> | | | | |NESTED LOOP JOIN Operator (VA = 5) (Join Type:
> Left Outer Join)
> | | | | |
> | | | | | |SCAN Operator (VA = 3)
> | | | | | | FROM TABLE
> | | | | | | ebyn..BEYANNAME
> | | | | | | t0
> | | | | | | Table Scan.
> | | | | | | Forward Scan.
> | | | | | | Positioning at start of table.
> | | | | | | Using I/O Size 16 Kbytes for data pages.
> | | | | | | With MRU Buffer Replacement Strategy for
> data pages.
> | | | | |
> | | | | | |SCAN Operator (VA = 4)
> | | | | | | FROM TABLE
> | | | | | | Worktable1.
> | | | | | | Using Clustered Index.
> | | | | | | Forward Scan.
> | | | | | | Positioning by key.
> | | | | | | Using I/O Size 16 Kbytes for data pages.
> | | | | | | With LRU Buffer Replacement Strategy for
> data pages.
> | | | |
> | | | | |SCAN Operator (VA = 6)
> | | | | | FROM TABLE
> | | | | | ebyn..PAKET
> | | | | | t1
> | | | | | Index : PAKET_IX
> | | | | | Forward Scan.
> | | | | | Positioning by key.
> | | | | | Keys are:
> | | | | | OID ASC
> | | | | | Using I/O Size 2 Kbytes for index leaf
> pages.
> | | | | | With LRU Buffer Replacement Strategy for
> index leaf pages.
> | | | | | Using I/O Size 2 Kbytes for data pages.
> | | | | | With LRU Buffer Replacement Strategy for
> data pages.
>
>
> Total estimated I/O cost for statement 1 (at line 1): 2147483647.
>
>
> QUERY PLAN FOR STATEMENT 1 (at line 1).
>
>
> STEP 1
> The type of query is SET OPTION OFF.
>
>
> QUERY PLAN FOR STATEMENT 2 (at line 2).
>
>
> STEP 1
> The type of query is SET OPTION OFF.
>
>
> and the query is;
> select
> t0.OID,t0.PAKETOID,t0.DOSYAADI,t0.VERGINO,t0.VDKODU,t0.DONEM,t0.DURUM,t0.YUKLEMEZAMANI,t0.TAHAKKUKOID,t0.BEYANNAMETANIMOID,t0.DONEMTIP,t0.DONEMBASLANGIC,t0.DONEMBITIS,t0.BEYANIMYOK,t1.DOSYAADI,t0.TAHAKKUKFISNO,t0.GECICIDONEM,t0.MESAJVAR,t0.IHBARNAMEKESILDI,t2.TC_KIMLIK_NO,t1.GONDERENOID
> from ebyn..BEYANNAME t0,
> ebyn..PAKET t1,
> ebyn..KIMLIK_BILGILERI t2
> where t1.OID = "01f8x7x2ka16hq" and
> t1.OID = t0.PAKETOID and
> t0.OID *= t2.BEYANNAME_OID and
> t2.TIP = 1
>
>
> Kind Regards,
>
> Mustafa Kutlu


IQ Rules Posted on 2008-06-26 02:58:49.0Z
From: IQ Rules <dbmethods@nospam.com>
User-Agent: Thunderbird 2.0.0.14 (Macintosh/20080421)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: optimizer changes ASE 15.02 ESD#4 vs ESD#2 ?
References: <48529be1$1@forums-1-dub> <4852df1a.6958.1681692777@sybase.com> <4852eb84$1@forums-1-dub> <27769458-9e2a-494e-9d01-822f8483d76c@59g2000hsb.googlegroups.com>
In-Reply-To: <27769458-9e2a-494e-9d01-822f8483d76c@59g2000hsb.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-9; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <486305e9$1@forums-1-dub>
Date: 25 Jun 2008 19:58:49 -0700
X-Trace: forums-1-dub 1214449129 10.22.241.152 (25 Jun 2008 19:58:49 -0700)
X-Original-Trace: 25 Jun 2008 19:58:49 -0700, vip152.sybase.com
Lines: 28
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10977
Article PK: 89588

select
t0.OID,t0.PAKETOID,t0.DOSYAADI,t0.VERGINO,t0.VDKODU,t0.DONEM,t0.DURUM,t0.YUKLEMEZAMANI,t0.TAHAKKUKOID,t0.BEYANNAMETANIMOID,t0.DONEMTIP,t0.DONEMBASLANGIC,t0.DONEMBITIS,t0.BEYANIMYOK,t1.DOSYAADI,t0.TAHAKKUKFISNO,t0.GECICIDONEM,t0.MESAJVAR,t0.IHBARNAMEKESILDI,t2.TC_KIMLIK_NO,t1.GONDERENOID
from ebyn..BEYANNAME t0,
ebyn..PAKET t1,
ebyn..KIMLIK_BILGILERI t2
where t1.OID = "01f8x7x2ka16hq" and
t1.OID = t0.PAKETOID and
t0.OID *= t2.BEYANNAME_OID and
t2.TIP = 1

Do you have index (OID) on ebyn..PAKET?
Do you have index (TIP, BEYANNAME_OID) on ebyn..KIMLIK_BILGILERI?

How many rows have t2.TIP = 1 in t2 table?


Does it make any difference here with allrows_mix plan?

select
t0.OID,t0.PAKETOID,t0.DOSYAADI,t0.VERGINO,t0.VDKODU,t0.DONEM,t0.DURUM,t0.YUKLEMEZAMANI,t0.TAHAKKUKOID,t0.BEYANNAMETANIMOID,t0.DONEMTIP,t0.DONEMBASLANGIC,t0.DONEMBITIS,t0.BEYANIMYOK,t1.DOSYAADI,t0.TAHAKKUKFISNO,t0.GECICIDONEM,t0.MESAJVAR,t0.IHBARNAMEKESILDI,t2.TC_KIMLIK_NO,t1.GONDERENOID
from ebyn..BEYANNAME t0,
ebyn..PAKET t1,
ebyn..KIMLIK_BILGILERI t2
where t1.OID = "01f8x7x2ka16hq" and
t1.OID = t0.PAKETOID and
t0.OID *= t2.BEYANNAME_OID and
t2.TIP = 1
plan '(use optgoal allrows_mix)'


IQ Rules Posted on 2008-06-13 00:10:56.0Z
From: IQ Rules <dbmethods@nospam.com>
User-Agent: Thunderbird 2.0.0.14 (Macintosh/20080421)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: optimizer changes ASE 15.02 ESD#4 vs ESD#2 ?
References: <48494b3a$1@forums-1-dub> <484f0f6e$1@forums-1-dub> <48515571$1@forums-1-dub>
In-Reply-To: <48515571$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4851bb10$1@forums-1-dub>
Date: 12 Jun 2008 17:10:56 -0700
X-Trace: forums-1-dub 1213315856 10.22.241.152 (12 Jun 2008 17:10:56 -0700)
X-Original-Trace: 12 Jun 2008 17:10:56 -0700, vip152.sybase.com
Lines: 5
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10943
Article PK: 89554

Run this,

$ optdiag statistics dbanme..mfs_files -o mfs_files_optdiag.out -U...

It could be data skew on column mfs_files, optdiag should show more clues.


Isabella Posted on 2008-06-13 16:21:14.0Z
From: Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
User-Agent: Thunderbird 1.5.0.12 (X11/20070530)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: optimizer changes ASE 15.02 ESD#4 vs ESD#2 ?
References: <48494b3a$1@forums-1-dub> <484f0f6e$1@forums-1-dub> <48515571$1@forums-1-dub> <4851bb10$1@forums-1-dub>
In-Reply-To: <4851bb10$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48529e7a$1@forums-1-dub>
Date: 13 Jun 2008 09:21:14 -0700
X-Trace: forums-1-dub 1213374074 10.22.241.152 (13 Jun 2008 09:21:14 -0700)
X-Original-Trace: 13 Jun 2008 09:21:14 -0700, vip152.sybase.com
Lines: 12
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10948
Article PK: 89559


IQ Rules wrote:
> Run this,
>
> $ optdiag statistics dbanme..mfs_files -o mfs_files_optdiag.out -U...
>
> It could be data skew on column mfs_files, optdiag should show more clues.

Yes I have the outpout file , it 's a big file , which part I should
paste it here ?
Thank you,
Isabella


Isabella Posted on 2008-07-23 18:47:43.0Z
From: Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
User-Agent: Thunderbird 1.5.0.12 (X11/20070530)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: optimizer changes ASE 15.02 ESD#4 vs ESD#2 ?
References: <48494b3a$1@forums-1-dub> <484f0f6e$1@forums-1-dub> <48515571$1@forums-1-dub> <4851bb10$1@forums-1-dub> <48529e7a$1@forums-1-dub>
In-Reply-To: <48529e7a$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48877ccf$1@forums-1-dub>
Date: 23 Jul 2008 11:47:43 -0700
X-Trace: forums-1-dub 1216838863 10.22.241.152 (23 Jul 2008 11:47:43 -0700)
X-Original-Trace: 23 Jul 2008 11:47:43 -0700, vip152.sybase.com
Lines: 21
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:11059
Article PK: 89670

Hi IQ Rules,
FYI: Sybase tech support was able to reproduce this behavior /bug ,
will see what coming from engineers now.
cheers,
Isabella

Isabella wrote:
> IQ Rules wrote:
>> Run this,
>>
>> $ optdiag statistics dbanme..mfs_files -o mfs_files_optdiag.out -U...
>>
>> It could be data skew on column mfs_files, optdiag should show more
>> clues.
>
>
> Yes I have the outpout file , it 's a big file , which part I should
> paste it here ?
> Thank you,
> Isabella


Cory Sane Posted on 2008-07-24 00:56:12.0Z
From: "Cory Sane" <cory!=sane>
Newsgroups: sybase.public.ase.performance+tuning
References: <48494b3a$1@forums-1-dub> <484f0f6e$1@forums-1-dub> <48515571$1@forums-1-dub> <4851bb10$1@forums-1-dub> <48529e7a$1@forums-1-dub> <48877ccf$1@forums-1-dub>
In-Reply-To: <48877ccf$1@forums-1-dub>
Subject: Re: optimizer changes ASE 15.02 ESD#4 vs ESD#2 ?
Lines: 31
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6001.18000
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6001.18000
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4887d32c$1@forums-1-dub>
Date: 23 Jul 2008 17:56:12 -0700
X-Trace: forums-1-dub 1216860972 10.22.241.152 (23 Jul 2008 17:56:12 -0700)
X-Original-Trace: 23 Jul 2008 17:56:12 -0700, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:11061
Article PK: 89672

Isabella,

Please post the CR #.

Cory Sane
[TeamSybase]

"Isabella" <isabella.ghiurea@nrc-cnrc.gc.ca> wrote in message
news:48877ccf$1@forums-1-dub...
> Hi IQ Rules,
> FYI: Sybase tech support was able to reproduce this behavior /bug , will
> see what coming from engineers now.
> cheers,
> Isabella
>
>
> Isabella wrote:
>> IQ Rules wrote:
>>> Run this,
>>>
>>> $ optdiag statistics dbanme..mfs_files -o mfs_files_optdiag.out -U...
>>>
>>> It could be data skew on column mfs_files, optdiag should show more
>>> clues.
>>
>>
>> Yes I have the outpout file , it 's a big file , which part I should
>> paste it here ?
>> Thank you,
>> Isabella


Isabella Posted on 2008-07-29 15:24:40.0Z
From: Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
User-Agent: Thunderbird 1.5.0.12 (X11/20070530)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: optimizer changes ASE 15.02 ESD#4 vs ESD#2 ?
References: <48494b3a$1@forums-1-dub> <484f0f6e$1@forums-1-dub> <48515571$1@forums-1-dub> <4851bb10$1@forums-1-dub> <48529e7a$1@forums-1-dub> <48877ccf$1@forums-1-dub> <4887d32c$1@forums-1-dub>
In-Reply-To: <4887d32c$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <488f3638$1@forums-1-dub>
Date: 29 Jul 2008 08:24:40 -0700
X-Trace: forums-1-dub 1217345080 10.22.241.152 (29 Jul 2008 08:24:40 -0700)
X-Original-Trace: 29 Jul 2008 08:24:40 -0700, vip152.sybase.com
Lines: 44
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:11074
Article PK: 89683

Here is from SYbase Tech Support :

Change Request(CR) 538918 has been filed for the problem you are
seeing between ESD #2 and 4. The PSE also found a difference in the new
ESD #6 behavior.


Isabella

Cory Sane wrote:
> Isabella,
>
> Please post the CR #.
>
> Cory Sane
> [TeamSybase]
>
> "Isabella" <isabella.ghiurea@nrc-cnrc.gc.ca> wrote in message
> news:48877ccf$1@forums-1-dub...
>> Hi IQ Rules,
>> FYI: Sybase tech support was able to reproduce this behavior /bug ,
>> will see what coming from engineers now.
>> cheers,
>> Isabella
>>
>>
>> Isabella wrote:
>>> IQ Rules wrote:
>>>> Run this,
>>>>
>>>> $ optdiag statistics dbanme..mfs_files -o mfs_files_optdiag.out -U...
>>>>
>>>> It could be data skew on column mfs_files, optdiag should show more
>>>> clues.
>>>
>>>
>>> Yes I have the outpout file , it 's a big file , which part I should
>>> paste it here ?
>>> Thank you,
>>> Isabella
>