Home Page Icon
Home Page
Table of Contents for
Part II Pre-15 Improvements
Close
Part II Pre-15 Improvements
by Carrie King Taylor, Steve Bradley, Naresh Adurty, Brian Taylor
The Official New Features Guide to Sybase ASE 15
Dedications
Contents
Foreword
Acknowledgments
About the Authors
About the Contributing Authors
Introduction
Audience
Scope
Conventions
Disclaimer
Part I New Features Overview
Chapter 1 Exploring the Sybase Galaxy
Part I — New Features Overview
System Maintenance Improvements
Partition Management — Semantic Partitions
Scrollable Cursors
Overview of Changes to Query Processing
Detection and Resolution of Performance Issues in Queries
Computed Columns
Functional Indexes
Capturing Query Processing Metrics
Plan Viewer
Sybase Software Asset Management (SySAM) 2.0
Installation of ASE 15
Part II — Pre-15 Improvements
Multiple tempdb Databases
MDA Tables
Java and XML
The Appendices
Sample Certification Exam
Use Cases
3, 2, 1, Contact!
Chapter 2 System Maintenance Improvements
Recent Pre-ASE 15 Improvements
Multiple tempdb
Native Data Encryption/Security Enhancements
Automatic Database Expansion
The Basics
Job Scheduler
Basic Components
Installation of Job Scheduler
ASE 15 Improvements
Row Locked System Catalogs
Update Statistics
Updates to Partition Statistics
Automatic Update Statistics
Datachange
Why Use datachange?
Datachange, Semantic Partitions, and Maintenance Schedules
Alternate Maintenance Schedule with Semantic Partitions
Local Indexes
Benefits
sp_helpindex
Partition-level Utilities
Partition Configuration Parameters
Utility Benefits from Semantic Partitions
Partition-specific Database Consistency Checks (dbccs)
dbcc checktable Syntax
dbcc checkindex Syntax
dbcc indexalloc Syntax
dbcc tablealloc Syntax
Reorg Partitions
Benefits of Reorg Partitions
Changes to the bcp Utility
Usage
bcp: Client-side Parallelism
Truncate Partitions
Looking Forward — Drop Partition
Very Large Storage System
Disk Init
Large Identifiers
Long Identifiers
Short Identifiers
Unicode Text Support
New Datatypes
New Functions
Deprecated Functions
New Configuration Parameters
Eliminated Configuration Parameters
New Global Variables
Summary
Chapter 3 Semantic Partitions and Very Large Database (VLDB) Support
Introduction
Why Partition Data?
Benefits of Partitioning
Partition Terminology
Semantic Partitions
Configuring ASE for Semantic Partitioning
Partition Support in ASE 15
Partition Types
Range Partitioning
When to Use Range Partitioning
Hash Partitioning
When to Use Hash-based Partitioning
List Partitioning
When to Use List Partitioning
Round-robin Partitioning
When to Use Round-robin Partitioning
Partitioning Strategies
Inserting, Updating, and Deleting Data in Partitions
Inserting Data into Semantic Partitions
Inserting Data into Range Partitions
Inserting Data into Hash Partitions
Inserting Data into List Partitions
Deleting Data from All Semantic Partitions
Updating Data in All Semantic Partitions
Built-in Functions
Data Partition Implementation and Upgrade Strategies
Index Partitioning
Local Index
Clustered Prefixed Index on Range Partitioned Table
Clustered Non-Prefixed Index on Range Partitioned Table
Clustered Prefixed Index on List Partitioned Table
Clustered Non-Prefixed Index on List Partitioned Table
Clustered Prefixed Index on Round-robin Partitioned Table
Clustered Non-Prefixed Index on Round-robin Partitioned Table
Clustered Non-Prefixed Index on Hash Partitioned Table
Clustered Prefixed Index on Hash Partitioned Table
Global Index
Global Nonclustered Prefixed Index on Range Partitioned Table
Global Nonclustered Prefixed Index on List Partitioned Table
Global Nonclustered Prefixed Index on Round-robin Partitioned Table
Global Nonclustered Prefixed Index on Hash Partitioned Table
Query Processor and Partition Support
ASE 15 Optimizer
Partition Maintenance
Altering Data Partitions
Unpartition a Table
Change the Number of Partitions
Add a Partition to a Table
Drop Partitions
Modifications to the Partition Key
Partition Information
Influence of Partitioning on DBA Activities
Influence of Partitioning on Long-time Archival
Summary
Chapter 4 Scrollable Cursors
Introduction
Scrollable Cursor Background
Cursor Scrollability
Cursor-related Global Variables
@@rowcount
@@fetch_status
@@cursor_rows
Changes to the sp_cursorinfo System Procedure
Be Aware of Scrollable Cursor Rules!
Cursor Sensitivity
Demonstration 1: Update to a Row Already Fetched
Demonstration 2: Update to a Row Not Yet Fetched
Cursor Sensitivity — An Exception
Locking Considerations with Cursors
Impact on tempdb Usage
Worktable Materialization with Scrollable Sensitive Cursors
Conclusion of Sensitive vs. Insensitive Cursors
Sybase Engineer’s Insight
Summary
Future Direction
Chapter 5 Overview of Changes to the Query Processing Engine
Introduction
Optimization Goals
allrows_oltp
allrows_mix
allrows_dss
Determining the Current Optimization Goal
Optimization Criteria
merge_join
merge_union_all
merge_union_distinct
multi_table_store_ind
opportunistic_distinct_view
parallel_query
hash_join
Optimization Timeout Limit
Query Processor Improvements
Datatype Mismatch
Partition Elimination and Directed Joins
Tables with Highly Skewed Histogram Values
Group By and Order By
or Queries
Star Queries
Summary
Chapter 6 Detection and Resolution of Query Performance Issues
Introduction
An Approach to Poor Query Performance Diagnosis
Common Query Performance Factors
Eliminating Causes for Sub-Optimal Plan Selection
Find Missing or Invalid Statistics
Consider Range Cell Density on Non-Unique Indexes
Identify Index Needs
Identify Poor Index Strategy
Fragmentation of Data
Resolve Partition Imbalance
Reset Server- or Session-level Options
Overengineered Forceplan
Invalid Use of Index Force
Inefficient Query Plan Forced by Abstract Plan
Query Processor “set options” — The Basics
Query Optimizer Cost Algorithm
ASE 15 vs. 12.5.x Cost Algorithm
Query Processor “set options” — Explored
show_missing_stats
show_elimination
show_abstract_plan
Why Use Abstract Plans for ASE 15?
Application of Optimization Tools
Optimization Goal Performance Analysis
Optimization Criteria Performance Analysis
Optimization Timeout Analysis
Suggested Approach to Fix Optimization Timeout Problems
Detection, Resolution, and Prevention of Partition-related Performance Issues
Data Skew Due to Incorrect Partition Type or Poor Partition Key Selection
Effect of Invalid Statistics on Table Semantically Partitioned
Summary
Chapter 7 Computed Columns
Introduction
Key Concepts
Materialization
Deterministic Property
Relationship between Deterministic Property and Materialization
Deterministic and Materialized Computed Columns
Deterministic and Nonmaterialized Computed Columns
Nondeterministic and Materialized Computed Columns
Nondeterministic and Nonmaterialized Computed Columns
Benefits of Using Computed Columns
Provide Shorthand and Indexing for an Expression
Composing and Decomposing Datatypes
User-defined Sort Order
Rules and Properties of Computed Columns
Sybase Enhancements to Support Computed Columns
Create Table Syntax Change
Alter Table Syntax Change
System Table Changes
Stored Procedure Changes
Summary
Chapter 8 Functional Indexes
Computed Column Index
Purpose
Rules and Properties of a Computed Column Index
Feature Benefits
Feature Limitations
Impacts to tempdb
Impact to Existing Application Code
Determining When to Use a Computed Column Index
Optimizer Statistics
Function-based Index
Purpose
Rules and Properties of a Function-based Index
Feature Benefits
Feature Limitations
Impacts to tempdb
Impact to Existing Application Code
Determining the Use of a Function-based Index
Optimizer Statistics
Behind the Scenes
Getting Index Information
Summary
Chapter 9 Capturing Query Processing Metrics
Alternatives to Query Processing Metrics
Introduction to Query Processing Metrics
Contents of sysquerymetrics
Contents of the sysquerymetrics View
How to Enable QP Metrics Capture
Captured Information Explored
Stored Procedures
Triggers and Views
Execute Immediate
Accessing Captured Plans
How Is the QP Metrics Information Useful?
Identification of Performance Regression
Comparing Metrics for a Specific Query between Running Groups
Comparing Metrics for All Queries between Running Groups
Why Separate the QP Metrics Data by gid?
Syntax Style Matters; Spacing Does Not
Clearing and Saving the Metrics
Relationship between Stats I/O and QP Metrics I/O Counts
Information for Resource Governor
Space Utilization Considerations
Limitations
Summary
Chapter 10 Graphical Plan Viewer
Graphical Plan Viewer from Interactive SQL
Graphical Query Tree Using Set Options
Summary
Chapter 11 Sybase Software Asset Management (SySAM) 2.0
Introduction
Prior to ASE 15
With ASE 15
Components of Asset Management
SySAM Server
SySAM Utility Program — lmutil
SySAM Reporting Tool
System Environment Variables
License File
Options File
Properties File
The SySAM Environment
Standalone License Server
Networked License Server
Redundant License Server
Acquiring Product Licenses
Product Licenses
Try and Buy
License Activation
SySAM Administration
sp_lmconfig
ASE 15 SySAM Upgrade Process
SySAM Reporting
Summary Reports
Server Usage Reports
Raw Data Reports
Summary
Chapter 12 Installation of ASE Servers
Prior to Installation for All Methods
Installation with Resource Files
Notes for Resource File Installation of ASE
Installation of ASE Components with a Resource File
GUI Installation Method with srvbuild Executable
Installation with the Dataserver Executable
Summary
Part II Pre-15 Improvements
Chapter 13 Multiple Temporary Databases
Introduction
Purposes for Multiple Temporary Databases
Prior to ASE 15
With ASE 15
System Catalog Changes
directio Support
update statistics
Insensitive Scrollable Cursors
Semi-sensitive Scrollable Cursors
Sensitive Scrollable Cursors
How to Decide When to Add a Temporary Database
Strategies
What Are Your Needs?
Implementation Steps
Determining Available Temporary Databases
Sample Setup for Temporary Database for “sa” Use Only
Other Issues
Dropping Temporary Databases
Altering a Temporary Database
@@tempdb
Summary
Chapter 14 The MDA Tables
What Are the MDA Tables?
Past Solutions
MDA Table Installation
MDA Table Server Configuration Options
Group: Monitoring
The Parent Switch
The MDA Tables
Changes from ASE 12.5.3
What Is Meant by “stateful” Tables?
Stateful MDA Table Data Management
SQL Use
Useful MDA Table Queries
MDA Alternatives
Summary
Chapter 15 Java, XML, and Web Services in ASE
Introduction
Java in the Database
Installing Java Classes
Creating Java Classes and JARs
Using the installjava Utility
Configuring Memory for Java in the Database
Java Classes as Datatypes
An Example of Table Definition Using a Java Class
Performance Considerations
An Example of Using a Java Class within a Select
Executing Java Methods
Class Static Variables
Recommendations and Considerations
XML in the Database
XML Stored in the Database
Option 1: Store the XML Document into a Text Datatype
Option 2: Store the XML Document into an Image Datatype Using xmlparse
Option 3: Store the XML Document into an Image Datatype Using Compression
Option 4: Store the XML Document Outside the Database
HTML Stored in the Database
Recommendations and Considerations
Performance and Sizing
Description of the Tests
Conclusions
SQL Result Sets Converted to Return an XML Document
Web Services
Web Services Producer
Web Services Consumer
Recommendations and Considerations
Appendix A Sybase ASE 15 Certification Sample Questions and Answers
Answers for Sybase ASE 15 Certification Sample Questions
Appendix B Use Cases
Business Case 1
Option 1: One VLDB with large tables self contained
Architecture Overview
Advantages
Limitations
Option 2: Primary database and an archive database for the largest tables
Architecture Overview
Advantages
Limitations
Option 3: Several small databases, each holding a portion (none overlapping) of the data from the very large tables
Architecture Overview
Advantages
Limitations
Option 4: One VLDB with a large table, partitioned semantically
Architecture Overview
Advantages
Limitations
Business Case 2
Option 1
Advantages
Limitations
Option 2
Advantages
Limitations
Option 3
Advantages
Limitations
Option 4
Advantages
Limitations
Business Case 3
Background on Current Architecture
Proposed Solutions
Option 1
Advantages
Limitations
Option 2
Cursor CSR1
Advantages
Limitations
Option 3
Advantages
Limitations
Index
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
Chapter 12 Installation of ASE Servers
Next
Next Chapter
Chapter 13 Multiple Temporary Databases
Part II Pre-15 Improvements
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset