Archive

Posts Tagged ‘ASE’

Optimization Goals

July 22nd, 2012 No comments

Query — How to set the optimization Goals?

Answer — Optimization goals allow you to choose an optimization strategy that best fits your query environment:

• allrows_mix – the default goal, and the most useful goal in a mixed-query environment. allows_mix balances the needs of OLTP and DSS query environments.
• allrows_dss – the most useful goal for operational DSS queries of medium to high complexity. Currently, this goal is provided on an experimental basis.
• allrows_oltp – the optimizer considers only nested-loop joins.

At the server level, use sp_configure. For example:sp_configure “o

ptimization goal”, 0, “allrows_mix”
At the session level, use set plan optgoal. For example:

set plan optgoal allrows_dss

At the query level, use a select or other DML command. For example:
select * from A order by A.a plan “(use optgoal allrows_dss)”
In general, you can set query-level optimization goals using select, update, and delete statements. However, you cannot set query-level optimization goals in pure insert statements, although you can set optimization goals in insert…select
statements.

Courtesy: Sybooks

Categories: ASE Tags: , ,

Query Processing and Abstract plan of stored procedure

July 22nd, 2012 No comments

Query — When does the optimization of statements in a stored procedure transpired, at compile time or at run time?

Answer — ASE 15.0.2 and later defers the query optimization of stored procedures until it execute the statement.

 

Query — Why does stored procedure runs slower at first execute?

Answer — Stored procedure runs slower at first execute because it performs the optimization of query and creates query plan and stores the query plan in cache. Hence, In subsequent  run it picks up the existing query plan from the cache and runs faster.

 

Query — What is the difference between stored procedure’s query plans of ASE 12.5.0 and ASE 15.0.2/later?

Answer — ASE versions before 15.0.2 used to create query plan of stored procedure at compile time wherein values of variables were not available, thereby sometimes stored procedure would not run as expected.

However, in versions 15.0.2 and later,execution engine step has been segregated into 2 steps 1. Procedural execution engine and 2. Query execution engine

The procedural engine executes command statements such as create table, execute procedure, and declare cursor directly. For data manipulation language (DML) statements, such as select, insert, delete, and update, the engine sets up the execution environment for all query plans and calls the query execution engine.

The query execution engine executes the ordered steps specified in the query plan provided by the code generator, which have the values of variables used in stored procedure. Hence, now query plans of stored procedure are more accurate than query plans of stored procedure in previous versions.

Courtesy: Sybooks

Physical read and Logical read

July 22nd, 2012 No comments

Query —  What is the difference between Physical and logical reads?

Answer — Physical reads are defined as pages read from the disk.

Logical reads are defined as pages read from the main memory (RAM/cache).

Index coverage

July 22nd, 2012 No comments

Query — What is  “Index Coverage of the query”?

Answer — It is defined as whether the query can be satisfied by retrieving data from the index pages without accessing the data pages . ASE can use indexex that covers query, even if no where clause are included in the query.

 

Query —  Can a query use index even if no where clause are included in the query?

Answer — Yes, as described above ASE can use indexes that covers query, even if no where clause are included in the query.

 

Query processing and Abstract Plans

July 22nd, 2012 No comments

Query –> What are the 6 modules of query processor?

Answer –>

1. Parser

2. Normalization

3. Preprocessor

4. Optimizer

5. Code generator

6. (1) Procedural execution engine

6. (2) Query execution engine

Query   –> At which level in Query Processor Modules we can determine if the statement may benefit from using the statement cache?

Answer  –> Level 2 (Normalization)

 

Query  –> What does Normalization infers while query parsing (Query Processor Modules) ?

Answer  –> Normalization involves determining column and table names, transforming the query tree into conjugate normal form (CNF), and resolving datatypes.

Query –> Define “Query Plan” ?

Answer –> Query Plan consists of  retrieval tactics and an ordered set of execution steps, which retrieve the data needed by the query.

Courtesy: sybooks

Categories: ASE Tags: , , ,

Freudenberg IT Creates a Center of Excellence for SAP HANA® and SAP® Sybase ASE

July 11th, 2012 No comments

Freudenberg IT (FIT) announced today the creation of its Advanced Databases Center of Excellence (COE). The Advanced Databases COE will allow customers to leverage the business advantages of the latest SAP® database platforms, including SAP HANA® and SAP® Sybase Adaptive Server® Enterprise (SAP Sybase ASE),while minimizing the cost, risk, and complexity of a database platform change.
Read more: http://www.timescolonist.com/business/Freudenberg+Creates+Center+Excellence+HANA+Sybase/6910324/story.html

 
 
Categories: ASE, HANA, News, SAP Tags: , , , ,

Bhavnagar Energy Company Limited Selects SAP Sybase ASE

July 8th, 2012 No comments

SAP AG has announced that Bhavnagar Energy Company Limited (BECL) has selected SAP Enterprise Resource Planning (ERP) running on Sybase Adaptive Server Enterprise (ASE). BECL will get a streamlined end-to-end experience, from integrated product experience to customer service, support and lifecycle management with SAP Business Suite application on Sybase ASE.

Full story @ http://biztech2.in.com/news/erp/bhavnagar-energy-company-limited-selects-sap-sybase-ase/138572/0

http://www.moneycontrol.com/news/technology/bhavnagar-energy-company-limited-selects-sap-sybase-ase_726124.html

 

Categories: ASE, News, SAP Tags: , , ,

dbcc checkdb vs dbcc checkstorage

June 25th, 2012 No comments

Checkstorage will detect allocation errors, it is a reasonable substitute for dbcc checkalloc (checkstorage will report a fair number of issues that checkalloc will not, many of them trivial things, and checkalloc may be able to detect a few odd conditions
checkstorage does not.

What checkstorage won’t catch are issues with index tree (keys out of order, index entries that point to missing rows, rows
that are not indexed).  So checkstorage is not a good substitute for checkdb.

Source :www & sybooks.

Performance Issue

June 24th, 2012 No comments

Few days back I have faced performance issue in one of our prod data server. I would like to share here.

User was running batch for pushing 90000 rows in a database and batch was not moved from last 1.5 hrs.

On login in the server I found response of the server was not good, it was taking more time to execute a simple query as usual. In first glance, it was looking like user job is hogging the resources, as user job spid was in syslogshold and not moved from long time.

We do some analysis and finally found the cpu usage for server was 100%. ( I used sp_monitor). I concluded that this high cpu usage is slowing down the server performance.

The next task was finding the query which was taking more cpu time. As server was on 15 version, I ran the below sql querry for mon tables for getting the high cpu usage.

select top 10  s.SPID, s.CpuTime, t.LineNumber, t.SQLText from master..monProcessStatement s, master..monProcessSQLText t where s.SPID = t.SPID order by s.CpuTime DESC

http://sybaseblog.com/sybasewiki/index.php?title=Query_-_Which_currently_executing_queries_are_consuming_the_most_CPU_%3F

We asked application team to check the reported spid and if possible, please abort the tran. There was select queries which were  taking maximum cpu . As they requested us to kill, we aborted/killed from data server.

After few seconds, data server cpu started fluctuating from 50 to 100% and finally it was below 50%.

Application batch of 10K inserts moved very quickly and finally issue resolved.

You can get full details on MDA queries @ http://sybaseblog.com/sybasewiki/index.php?title=Category:MDA_Table_Query

Thanks.

Sybase ASE Database: Cost Considerations and Advantages

June 22nd, 2012 No comments

Recently SAP contracted with IDC to conduct a study of relational database management (RDBMS) users to determine the cost factors encountered by those users in running a relational database, and the extent to which (if any) Sybase ASE could save users money in running their systems. IDC recruited 12 organizations that were willing to let us examine deeply their costs in running both Sybase ASE and other RDBMS products.

IDC asked a number of detailed questions regarding the organizations’ use of database software, their staffing costs, their hardware costs, and their software license and maintenance costs. IDC then analyzed the data using a well-established five-year model for calculating total cost of ownership (TCO), and came to some compelling conclusions.

Read More : http://blogs.sap.com/sme/2012/06/21/sybase-ase-database-cost-considerations-and-advantages/#comment-3116

Categories: ASE, News Tags: , , , ,