Difference between reorg-rebuild and index re-create in Sybase ASE

SET SHOWPLAN [ON|OFF] is a session-level command in Sybase ASE, it's not a server-level setting (if it was a server-level setting you'd be able to alter it via sp_configure). Can you run wireshark (or something similar) on the packets being sent from the JDBC client to see if it's setting showplan on as part of the session initialization?

That said - showing the plan should not affect database CPU or memory usage, the plan is generated by the query optimiser anyway. However, showing the plan will increase network utilization.

When a table is first indexed, especially with a clustered index, the records are in order, and there are a minimal number of index pages to find each record. As update, insert and deletes happen, the number of index pages increases. Over time this will impact query performance. It is recommended that dba's run reorgs periodically to collect garbage, compact the database, and compact the index pages. The downside to reorgs, is that prior to 15.7, they block access to the table, so typically must be run during off hours.

1 - What is the underlying cause?

Insert/Update/Delete activity in the database creates additional database pages, and index pages. The more pages the query has to read to get to the data it's looking for, the longer the query will take.

2 - Should reogs be performed on a regular basis?

Probably. Static, or rarely changing tables will need it infrequently, but as the activity on a table increases, the need for reorgs increases as well.

How should I determine when to run?

This depends on the environement you are in. The need for reorgs is directly related to the Insert/Update/Delete activity of the table. You can use the optdiag command to check the health of your tables, take a close look at some of the following values: Index page cluster ratio; Data page cluster ratio; Empty data page count; Empty leaf pages; Deleted row count; Forwarded row count; Index empty leaf page count.

Check the Sybase documentation on optidag and the Sybase documentation on reorg to get a good idea of how to determine when to run reorgs in your environment.