471

CHAPTER 13 Monitoring and Tuning Performance

•FLWait: Number of times IQ had to wait for the lock on the free list (repeated for the temp cache)

•MemLks: Number of times IQ took the memory manager (heap) lock

•MemWts: Number of times IQ had to wait for the memory manager lock

•-threadsdisplays information about processing threads

•cpus: Number of CPUs on system

•Limit: size of thread manager pool

•NTeams: Number of thread teams currently in use

•MaxTms: Largest number of teams that has ever been in use

•NThrds: Current number of existing threads

•Resrvd: Number of threads reserved for system (connection) use

•Free: Number of threads available for assignment

•Locks: Number of locks taken on the thread manager

•Waits: Number of times IQ had to wait for the lock on the thread manager

•-intervalspecifies the reporting interval in seconds. The default is every 60 seconds. The minimum is every 2 seconds. You can usually get useful results by running the monitor at the default interval during a query or time of day with performance problems.

•-append - truncate Append to existing output file or truncate existing output file, respectively. Truncate is the default.

Page 491

Image 491

Contents

Adaptive Server IQ Administration and Performance Guide Document ID Contents Contents 104 100101 103 138 Contents Deleting views 131 Views in the system tables132 135 Vii Viii 277 Using cursors on Select statements in procedures 253255 273 309 Contents Referential integrity is unenforced 284285 299 Managing User IDs and Permissions 323339 352 Xii Xiii 467 448454 459 481 473476 479 Xvi Xvii How to use this bookAudience Documentation for Adaptive Server IQ Guide to using this book To learn how to Read this chapterRelated documents Related documents Xix About This Book Related documents Introduction to Adaptive Server IQ Overview of IQ system administration System administration tasks System administration tasks Security overview Overview of Adaptive Server IQ System AdministrationAdministrative tasks Granting permissions Tools for system administrationTools for system administration Types of users Catalogs and IQ Database server Catalog Store Adaptive Server Anywhere and Adaptive Server IQIQ Store Temporary Store Concurrent operations Stored procedures Stored procedures Adaptive Server IQ stored procedures Stored Procedures for the IQ Store Procedure name Purpose Adaptive Server Enterprise system and catalog procedures System procedure Description Adaptive Server Enterprise system procedures Procedure name Purpose Catalog stored proceduresAdaptive Server Enterprise catalog procedures Catalog procedure Description System table Description System tables and viewsSystem tables and views Sysdomain Syslogin Sysuserauth System view Description Functions Commands and FunctionsCommands and Functions Types of SQL statements Fourth line of the file contains version information Message loggingManipulate date and time data for example, TODAY, Datediff Utility database Utility database Compatibility with earlier versions Compatibility with earlier versions Starting the database server General form for the server command line is as follows Server command linesServer command lines Running Adaptive Server IQ Starting the server on Unix Starting the server on Unix Using the startup utilityTo start the server on Unix using the startup utility Typing the server startup command Starting the server on Windows NT Starting the server on Windows NTStarting the server from the NT Start menu Running the server outside the current session Running the Unix database server as a daemon Using command-line switchesRunning the server as a Windows NT service Using command-line switches Case sensitivity Using configuration files Elora 16M Tcpipport=2367 -gm Gp 4096 path\mydb.db Database started Naming the server and databasesRequired if you do not supply a database Window Sample database on that server Setting memory switches Controlling performance from the command line Concurrent user connections on a particular server Default value of -iqgovernis 2 x the number of CPUs +10This switch as the number of megabytes of wired memory Use Setting switches that affect timing Other performance-related switches Controlling permissions from the command line Setting a maximum Catalog page size Selecting communications protocols Setting up a client/server environment Limiting inactive connections Connections, and Catalog page size Starting a server in forced recovery modeStarting a server from Dbisql Server from Dbisql Log file name has this format Monitoring server activityStarting multiple servers or clients on the same machine Capture server activity in a log file Monitoring server activity Stopping the database server PID Stopping the database server Will be stopped even if there are connections to it Who can stop the server?Shutting down operating system sessions Following example stops a server from Dbisql Starting and stopping databases Starting and stopping databases Starting the asiqdemo database From a command line, type the following commandCommand that executes when you perform these steps is Starting and stopping Sybase Central Starting and stopping Sybase Central Connecting to a plug-in Connecting a plug-in Introduction to connections Introduction to connectionsStopping Sybase Central If you want Consider reading How connections are established This is equivalent to the following connection string Connection parameters specify connectionsConnection parameters are passed as connection strings Following form Simple connection examples To connect from a Unix system Connecting to a database from DbisqlSimple connection examples If you need to start the sample database, enter To connect from a Windows NT system Enter the user ID Connecting to other databases from DbisqlPassword To connect to a database from Dbisql on Unix Connecting to an embedded databaseStart Dbisql by typing at a system command prompt To connect to an embedded database from Dbsql in Windows NT Extra cache needed for Java To connect using a data source Connecting using a data source Connecting to a server on a network Using default connection parameters Database Connecting from Adaptive Server IQ utilitiesNamed server, using the default database You may connect to the wrong server Working with Odbc data sources Working with Odbc data sources DSNs and FILEDSNs Creating and editing Odbc data sources To test an Odbc Data Source Creating and editing Odbc data sourcesYou could also use the host network address. For example To create an Odbc User Data Source Odbc tab Configuring Odbc data sources Prevent Driver Not Capable errors Selecting this Login tabDatabase tab Advanced tab Network tab Which the debugging information is to be saved Enter any additional switches hereCreating a File Data Source Name of the file Using Odbc data sources on Unix Using Odbc data sources on Unix Short Parameter Form Argument Description Connection parameters Connection parameters Connection parameters and their values are case insensitive Not supported in Odbc connectionsParameters Or NO, OFF, 0, or False if false Use the Start parameter to specify cache size Specify database files using DBFSpecify database names using DBN Connection parameter priorities Steps in establishing a connection How Adaptive Server IQ makes connections Unix dblib6 with an operating system-specific extension Locating the interface libraryHow Adaptive Server IQ makes connections Parameter Assembling a list of connection parametersConnection file AutoStop Ignored if the database is already loaded Is already running Ways by supplying network communication parameters Locating a server How Adaptive Server IQ makes connections Locating the database Server name caching for faster connections Interactive SQL connections Connecting from other databases Using an integrated login Using an integrated login To use an integrated login Using integrated loginsEnabling the integrated login feature To map an integrate login using a SQL statement Creating an integrated loginTo map an integrated login from Sybase Central Can connect to a database if all the following are true Connecting from a client applicationFollowing Dbisql statement Security concerns unrestricted database access Setting temporary public options for added security Creating a default integrated login user Network aspects of integrated logins Ensure that your files are valid Troubleshooting startup, shutdown, and connectionsWhat to do if you can’t start Adaptive Server IQ Check environment variables Debugging network communications startup problemsTroubleshooting startup, shutdown, and connections Ensure that network communication software is running What to do if you can’t connect to a database Stopping a database server in an emergency Unix Resolving problems with your Dbisql window on Unix Running Adaptive Server IQ Troubleshooting startup, shutdown, and connections Designing your database Building Your Adaptive Server IQ Databases 100 Using Sybase Central to work with database objectsTools for working with database objects Building Your Adaptive Server IQ Databases 101 Step-by-step overview of database setupUsing Dbisql to work with database objects Working with Database Objects 102 To set up an IQ databaseScheduling data definition tasks 103 Extending data definition privileges 104 Selecting a device typeAllocating space for databases 105 Estimating space and dbspaces required 106 Working with databasesWorking with databases 107 Creating a database 108 109 110 111 Choosing an IQ page size 112 Specifying the size of your databaseChoosing a Catalog page size 113 Choosing a block size for your databaseEnabling Java in the database IQ Store size is 8MB and the Temporary Store is 4MB 114 Adding dbspaces To create a dbspace in Sybase Central 115 116 Dropping dbspaces 117 ======================================================= Working with tables Working with tablesDropping a database Creating tables 119 120 Types of tables 121 Table placementType of table Permitted placement Default placement Automatic index creation for IQ tables 122 Optimizing storage and query performanceData type of the column and its width See the following table for implications of IQ Unique 123 Altering tables To drop a table in Sybase Central Dropping tables124 To create a primary key in Sybase Central Creating primary and foreign keys125 126 To create an unenforced foreign key in Sybase Central Working with viewsTable information in the system tables 127 128 Creating viewsWorking with views Definition in the database system tables To create a view in Sybase Central Using views129 Alter View statement maintains the permissions on the view Modifying viewsPermissions on views An existing view definition 131 Deleting viewsViews in the system tables 132 Working with indexesIntroduction to indexes Working with indexes 133 Creating indexesIndexes in the system tables 134 Removing indexes 135 Overview of indexesAdaptive Server IQ index types Indexing 136 Overview of indexes 137 Benefits over traditional indexesAdaptive Server IQ Indexes Region column of the salesorder table Creating Adaptive Server IQ indexesCreate Index statement Creating Adaptive Server IQ indexes To create an index with Sybase Central Creating an index with Sybase CentralCreating indexes concurrently 139 140 Choosing an index typeChoosing an index type 141 Number of unique values in the indexTypes of queries Type of Query Usage Recommended Index Type 142Query type/index 143 Only the default index supports the following data typesIndexing criteria disk space usage Data types in the index 144 Adaptive Server IQ index typesCombining index types Adaptive Server IQ index types Recommended use Default column indexLowFast LF index type Projections on few rows Additional indexes HighGroup HG index typeAdvantages and disadvantages of LowFast Comparison with other indexes HG advantages/disadvantages Advantages Disadvantages Advantages and disadvantages of HighGroupAutomatic creation of HighGroup index 147 148 HighNonGroup HNG index typeAdvantages and disadvantages of HighNonGroup 149 Optimizing performance for ad hoc joinsComparison to other indexes HighGroup index is also appropriate for an HNG column Criteria to identify Index to select Selecting an index150 151 Using join indexesAdding column indexes after inserting data Join indexes improve query performance Join hierarchy overview When a join becomes ad hocHow join indexes are used for queries Relationships in join indexes 153 Columns in the join index 154 Join hierarchy in query resolutionOne-to-many relationship Custid Lname 155 156 Multiple table joins and performance 157 Steps in creating a join index 158 Synchronizing join indexesPrivileges needed to create a join index 159 Defining join relationships between tablesPerformance hints for synchronization 160 Using foreign referencesExamples of join relationships in table definitions 161 Specifying the join type when creating a join indexSpecifying relationships when creating a join index 162 Parameters of this command areIssuing the Create Join Index statement 163 To add a join index in Sybase Central Creating a join index in Sybase CentralTypes of join hierarchies 164 165 Linear joinsStar joins 166 You can create this table using the following commandsAnother table 167 Modifying tables included in a join index 168 Inserting or deleting from tables in a join index 169 Table versioning controls access to join indexesEstimating the size of a join index 170 Estimating the size of a join index 171 Import and export overviewImport and export methods Databases File Format Description Input and output data formatsImport and export overview 172 Moving Data In and Out of Databases Scheduling database updatesPermissions for modifying data Specifying an output format for Interactive SQL 174 Using output redirectionExporting data from a database Exporting data from a database 175 Bulk loading data using the Load Table statementNull value output Quotes on OFF Escapes on OFF Escape Character character Bulk loading data using the Load Table statement176 Format ’ascii’ ’binary’ Delimited by string Binary with Null Byte Prefix 1 2 4 ’delimiter-string’ 177 178 179 180 Start ROW ID number 181Block Factor number Block Size number Preview on OFF ROW Delimited by ’delimiter-string’ Here is a Windows NT example On default is NATIVE. You can also specifySpecifies the byte ordering during reads. This Endian platforms like DEC ALPHA, and Windows NT 183 Table. The default is 0 for no limitSpecifies the maximum number of rows to insert into Options work together 184 185 Following Windows NT example sets the column delimiter forCarriage return \x0d With the Limit option, and takes precedence over it 186 187 Interpreting notification messagesMemory message This message displays memory usage information 188 Main IQ Store blocks messagesIQ Temporary Store blocks message Main buffer cache activity message See the description for the Main buffer cache message above Temporary buffer cache messageControlling message logging These lines display information about the Temp buffer cache 190 Using the Insert statementUsing the Insert statement Inserting specified values row by row Into the lorderkey column in the lineitem table Inserting selected rows from the databaseComplete description of Adaptive Server IQ data types Following example adds 1995-06-09 into the lshipdate column 192 Inserting from a different databaseInserting selected rows from the database To insert data directly from Adaptive Server Enterprise Issue a Commit to commit the insertLineitem table in the current database 193 194 195 Importing data interactivelyInserting into tables of a join index 196 Inserting into primary and foreign key columnsInserting into primary and foreign key columns 197 Partial-width insertions 198 Partial-width insertion rulesPartial-width insertions 199 QueryAdditional columns Instead of the correct number 200 Start ROW ID option in the second Load Table statement 201 Index 202 Converting data on insertionConverting data on insertion 203 Conversion options for loading from flat files Inserting data from pre-Version 12 Adaptive Server IQLoad conversion options 204 205 Data conversions in IQ IQ conversions for comparison operations 206 IQ conversions for arithmetic operations 207 208 Column width issuesUsing the Ascii conversion option 209 210 Date OptionSubstitution of Null or blank characters Specifying the Date Format 211 212 Setting Year specified as Years assumedDatetime conversion option 213 Specifying the format for Datetime conversions 10 Datetime format options 214Formatting times 215 Where Blanks indicates that blanks convert to NullsWorking With Nulls 216 Other factors affecting the display of dataOther factors affecting the display of data 217 Matching Adaptive Server Enterprise data typesUnsupported Adaptive Server Enterprise data types 218 Adaptive Server Enterprise data type equivalentsMatching Adaptive Server Enterprise data types Adaptive Server Enterprise Datatype IQ Datatype 219Adaptive Server Adaptive Server IQ Enterprise Datatype 13 Character data types 15 DATE/TIME data types Handling conversion errors on data import220 Creating indexes Tuning bulk loading of dataImproving load performance during database definition Optimizing for the number of distinct values Adding dbspaces Setting server startup optionsAdjusting your environment at load time Tuning bulk loading of data 223 Reducing Main IQ Store space use in incremental loads 224 Changing data using UpdateChanging data using Update 225 Deleting data 226 Importing data by replicationImporting data by replication 227 228 229 Overview of proceduresAdaptive Server IQ Statements, are also available in batches 230 Benefits of proceduresIntroduction to procedures Benefits of procedures 231 Creating proceduresUsing Procedures and Batches 232 Calling proceduresDropping procedures Introduction to procedures 233 Permissions to execute proceduresReturning procedure results in parameters Employee ID Salary Returning procedure results in result sets234 235 Introduction to user-defined functionsCreating user-defined functions Fullname empfname, emplname Calling user-defined functionsIntroduction to user-defined functions 236 237 Dropping user-defined functionsPermissions to execute user-defined functions Following statement revokes permission to use the function 238 Introduction to batchesIntroduction to batches Dbisql and batches Control statements239 Control statement Syntax Using compound statementsControl statements 240 241 Declarations in compound statements 242 Atomic compound statements 243 Structure of proceduresSQL statements allowed in procedures 244 Declaring parameters for proceduresStructure of procedures 245 Passing parameters to proceduresPassing parameters to functions Or as follows 246 Returning results from proceduresReturning a value using the Return statement Returning results from procedures 247 Returning results as procedure parameters Orders variable that is returned to the calling environment Declared in the procedure argument listShow the number of orders placed by the customer with ID CustomerID parameter is declared as an in parameter. This 249 Returning result sets from procedures 250 Returning multiple result sets from proceduresReturning variable result sets from procedures Procedure 251 Using cursors in procedures 252 Using cursors in proceduresCursor management overview Cursor positioning 253 Using cursors on Select statements in procedures 254 255 Errors and warnings in procedures 256 Default error handling in proceduresErrors and warnings in procedures 257 258 Error handling with on Exception Resume On Exception Resume Begin 259 260 Default handling of warnings in procedures 261 Using exception handlers in procedures 262 263 Nested compound statements and exception handlers 264 Using the Execute Immediate statement in proceduresFollowing statement executes the InnerProc procedure 265 Transactions and savepoints in proceduresSome tips for writing procedures Check if you need to change the command delimiter Some tips for writing procedures Remember to delimit statements within your procedureUse fully-qualified names for tables in procedures Specifying dates and times in procedures 267 Verifying procedure input argumentsStatements allowed in batches 268 Calling external libraries from proceduresUsing Select statements in batches 269 Creating procedures and functions with external calls 270 External function declarationsFollowing form for Microsoft Visual C++ 271 How parameters are passed to the external function 272 Special considerations when passing character types 273 Data integrity overviewHow data can become invalid 274 Integrity constraints belong in the databaseData integrity overview Data integrity tools How database contents get changed 276 SQL statements for implementing integrity constraints 277 Using table and column constraintsUsing Unique constraints on columns or tables 278 Using IQ Unique constraint on columnsUsing Check conditions on columns Using table and column constraints 279 Column Check conditions from user-defined data typesOption 280 Using Check conditions on tablesWorking with column constraints in Sybase Central Modifying and deleting Check conditions 281 Declaring entity and referential integrity 282 Enforcing entity integrityIf a client application breaches entity integrity 283 Primary keys enforce entity integrityDeclaring referential integrity 284 How you define foreign keysReferential integrity is unenforced 285 Integrity rules in the system tables 286 Integrity rules in the system tables Transactions are logical units of work Performance and other aspects of database administrationOverview of transactions and versioning Introduction to transactions Overview of transactions and versioning Using transactionsStarting transactions Completing transactions 289 Options in DbisqlCommitting a transaction writes data to disk Transactions and Versioning 290 Introduction to concurrencySubdividing transactions How concurrency works in IQ Concurrency and IQ Multiplex Concurrency for backupsWhy concurrency benefits you Introduction to versioning 292 Table-level versioningOne writer and multiple readers at the table level Only one writer at a time 293 294 Multiple writers and readers in a database 295 Transactions use committed data 296 Timing of commits on read transactions affects versions 297 Hold cursors span transactions 298 How Adaptive Server IQ keeps track of versionsVersioning of temporary tables 299 Versioning prevents inconsistenciesHow locking works Locks for DML operations 300 Locks for DDL operationsHow locking works 301 302 Isolation levelsPrimary keys and locking Isolation levels 303 Checkpoints, savepoints, and transaction rollback Checkpoints, savepoints, and transaction rollback Checkpoints aid in recoveryWhen checkpoints occur Checkpoints 305 Savepoints within transactionsReleasing savepoints Following scenario Rolling back to a savepointAutomatic and user-defined savepoints Naming and nesting savepoints Effect of rollback System recoveryRolling back transactions What causes a rollback 308 How transaction information aids recovery 309 Performance implications 310 Overlapping versions and deletionsPerformance implications 311 Cursors in transactions Cursors in transactions Cursors and versioningCursor sensitivity Cursor scrolling Controlling message logging for cursors Cursor command syntax and examplesHold cursors Positioned operations 314 315 Adaptive Server IQ international featuresInstallation to handle international language issues Introduction to international languages and character sets 316 Using the default collationCharacter set questions and answers Introduction to international languages and character sets 317 Understanding character sets in softwarePieces in the character set puzzle International Languages and Character Sets Database server software messages Applications can cause Language issues in client/server computing318 319 Code pages in Windows and Windows NT 320 Ansi and OEM code pages in Windows and Windows NT 321 Multibyte character sets 322 Sorting characters using collationsInternational aspects of case sensitivity First-byte collation orderings for multibyte character sets 323 Understanding localesIntroduction to locales Case insensitivity of identifiers 324 Understanding the locale languageUnderstanding locales Alternative Language label Label ISO639 language code Understanding the locale character set325 Character set Label Iana label Description Equivalent Iana labels and a description326 327 Displaying collations Setting the Sqllocale environment variableUnderstanding collations Understanding the locale collation label Collation Label Type Description Supplied collationsFollowing collations are supplied with Adaptive Server IQ 329 330 331 Ansi or OEM? 332 333 334 335 Understanding character set translation Using multibyte collationsUnderstanding character set translation Character translation for database messages To use character translation for database messages 337 338 Connection strings and character setsAvoiding character-set translation Language Character set Collation internals339 340 Comment linesTitle line Collation internals 341 Specification DescriptionCollation sequence section Following are some sample lines for a collation 342 Encodings sectionThat will be sorted together Following is part of the Shift-JIS collation file 343 Properties section International language and character set tasks Finding the default collationConfiguring your character set environment To configure your character set environment To determine the locale of a database server You can determine locale information using system functionsDetermining locale information 345 346 Setting localesCreating a database with a named collation Your current machine 347 List the supplied collation sequences Or on Unix Starting a database server using character set translationUsing Odbc code page translation To enable character-set translation on a database server 349 Character set translation for Sybase Central and DbisqlCreating a custom collation Set the Dbisql option CHAROEMTranslation to a value of OFF 350 To create a database with a custom collation Compatibility issuesCreating a database with a custom collation 351 352 Performance issuesPerformance issues 353 An overview of database permissionsManage user IDs 354 DBA authority overviewAn overview of database permissions Managing User IDs and Permissions Resource authority overviewOwnership permissions overview Table and views permissions overview 356 Managing individual user IDs and permissionsGroup permissions overview 357 Changing a passwordWith the following command Creating new users 358 Granting DBA and resource authority 359 Granting permissions on tables and views To grant user permission on tables in Sybase Central Granting users the right to grant permissionsContext of groups in Permissions of groups 360 361 Granting permissions on proceduresTo grant user permissions on procedures in Sybase Central 362 Revoking user permissions 363 To create a group with a name and passwordManaging groups Creating groups To create a group in Sybase Central Granting group membership to usersManaging groups 364 365 Permissions of groupsReferring to tables owned by groups 366 Groups without passwords 367 Database object names and prefixesSpecial groups 368 Database object names and prefixes 369 Using views and procedures for extra securityFollowing command will now work 370 Using views for tailored securityUsing views and procedures for extra security 371 Using procedures for tailored securityGrant permission for the Sales Manager to examine this view Be modified is strictly defined How user permissions are assessedManaging the resources connections use How user permissions are assessed 373 Temporary Store 374 Users and permissions in the system tablesUsers and permissions in the system tables Limits the number of prepared 375 Default ContentsViews Default Contents 376 377 Backup protects your data Backing up your database Types of backupsData in backups Backing up your database 379 Transaction log in backup, restore, and recoveryBackup and Data Recovery Selecting archive devices Distribution of backup dataDisk backup requirements Tape backup requirements 381 Preparing for backupObtaining DBA privileges Rewinding tapes Estimating Media Capacity Retaining old disk backupsTwo ways to run Backup You can run Backup in two ways 383 Concurrency and backupsBackup statement To back up an IQ database, use the following syntax 384 Specifying the type of backupSpecifying operator presence Specifying archive devices 385 386 387 388 Backup ExamplesOther backup options Waiting for Tape Devices 389 Recovery from errors during backupChecking for backup space Recovery attempts 390 After you complete a backupPerforming backups with non-Sybase products Do not specify the Stacker or Size parameters 391 Performing system-level backupsShutting down the database 392 Restoring from a system-level backupPerforming system-level backups Backing up the right files For this size database It takes about this long Validating your database393 Orphaned block statistic Meaning Interpreting resultsValidating your database 394 395 Concurrency issues for spiqcheckdb 396 Before you restoreRestoring your databases Restoring your databases 397 Restore accommodates dbspace changes Keeping the database unchanged between restores Restoring disk backup filesRestoring tape backup files Specifying files for an incremental restore 399 Restore statementRestoring from a compatible backup To restore a database, use the following syntax 400 Moving database files 401 402 403 Adjusting data sources and configuration filesRestoring in the correct order Displaying header information 404 Set, and the restore order, are as followsSet must be restored first, and must be in the first device 405 Renaming the transaction log after you restoreDblog command-line utility Switch Description Maintaining a transaction log or mirror Validating the database after you restoreRestore requires exclusive write access Transaction log utility options 407 Displaying header information 408 Recovery from errors during restoreUsing Symbolic Links Unix Only 409 Unattended backup 410 Getting information about backups and restoresLocating the backup log Getting information about backups and restores 411 Content of the backup log 412 Maintaining the backup logViewing the backup log in Sybase Central Recording dbspace names 413 Determining your data backup and recovery strategy 414 Scheduling routine backupsDetermining the type of backup Determining your data backup and recovery strategy 415 Designating Backup and Restore ResponsibilitiesImproving performance for backup and restore Increasing the number of archive devices Balancing system load Spooling backup dataIncreasing memory used during backup Eliminating data verification 417 Controlling the size of the Catalog Store 418 Designing for performance DBA can tune performance by adjusting resource usageYour hardware and software configuration Introduction to performance terms 420 Overview of memory usePaging increases available memory Overview of memory use 421 Utilities to monitor swappingServer memory Managing System Resources 422 Managing buffer cachesDetermining the sizes of the buffer caches 423 Operating system and other applicationsAdaptive Server IQ memory overhead Memory requirements for loads Multi-user database access424 Raw partitions versus file systems Buffer caches and physical memory Adaptive Server IQ main and temp buffer caches425 Memory available for buffer caches Example Example of setting buffer cache sizes426 Other considerations 427 Setting buffer cache sizesMethods of adjusting buffer cache sizes To change the buffer cache sizes permanently Setting buffer cache size database options428 Specifying page size Setting buffer cache size server switchesSetting the page size Normally you change the buffer cache sizes by setting 430 Block size Data compression Decrease buffer cache settingsAdjust blocking factor for loads Saving memory Use the following guideline to determine Block Factor IQ command line option changesActively using the database Optimizing for large numbers of users 433 Command and set rlimfdmax=4096 in /etc/systemIQ Temp space System parameters 434 Platform-specific memory options 435 Recommendations for small memory configurationsPhysical size of memory on the system Options that can provide more memory 436 437 You must then restart the server with the following commandManaging large buffer caches on HP Controlling file system buffering 438 Options for Java-enabled databasesOther ways to get more memory 439 Process threading model 440 Insufficient threads errorIQ options for managing thread usage Process threading model 441 Balancing I/ORaw I/O on Unix operating systems Balancing I/O Using disk stripingSetting up disk striping on Unix Setting up disk striping on Windows NT 443 Internal stripingRecommendations for disk striping 444 Disk striping optionDisk striping hints Across multiple disks Using multiple dbspacesTemporary data with the Create Dbspace command Transaction log file 446 Strategic file locationsMessage log 447 Working space for inserting, deleting, and synchronizing 448 Options for tuning resource useOptions for tuning resource use Restricting concurrent queries 449 Limiting a query’s memory useLimiting queries by rows returned Forcing cursors to be non-scrolling Prefetching cache pages Limiting the number of cursorsLimiting the number of statements Lowering a connection’s priority Disk caching Restricting database accessOther ways to improve resource use Optimizing for typical usage Indexing tips Using RAM diskIndexing tips Picking the right index type 453 Using join indexesAllowing enough disk space for deletions Managing database size and structure Managing database size and structureManaging the size of your database Denormalizing for performance 455 Denormalization has risksDisadvantages of denormalization Performance benefits of denormalization Improving your queries Improving your queriesDeciding to denormalize Tips for structuring queries 457 Planning queriesQuery evaluation options 458 Setting query optimization options 459 Network performanceImproving large data transfers 460 Isolate heavy network usersNetwork performance 461 Put small amounts of data in small packets 462 Put large amounts of data in large packets 463 Process at the server levelFilter as much data as possible at the server level 464 Set to control resource use, see , Managing System Getting information using stored proceduresOther chapters of this guide for more tuning hints Viewing the Adaptive Server IQ environment 466 Viewing the Adaptive Server IQ environment 467 Monitoring and Tuning PerformanceMonitoring the buffer caches Starting the buffer cache monitor 468 Monitoring the buffer caches 469 470 471 Into dummytablename Stop Monitor Stopping the buffer cache monitorExamining and saving monitor results 472 473 Examples of monitor resultsBuffer cache 474 475 -contentionresults for the main cache are-contentionresults for the temp cache are Results for the memory manager are 476 Avoiding buffer manager thrashingAvoiding buffer manager thrashing 477 Monitoring paging on Windows NT systemsMonitoring paging on Unix systems Here is an example 478 479 System utilities to monitor CPU use 480 System utilities to monitor CPU use 481 Client/server interfaces to Adaptive Server IQData server for client applications Restrictions for creating and running these applications 482 Client/server interfaces to Adaptive Server IQ 483 Configuring IQ Servers with DseditInterfaces file Adaptive Server IQ as a Data Server 484 Using the Dsedit utilityOpening a Directory Services session Starting Dsedit Select a server entry in the Server box Adding a server entryAdding or changing the server address Interfaces file sql.ini 486 Network Settings, in the Control Panel 487 Verifying the server addressRenaming a server entry 488 Sybase applications and Adaptive Server IQOpen Client applications and Adaptive Server IQ Deleting server entries 489 Setting up Adaptive Server IQ as an Open ServerSystem requirements Starting the database server as an Open Server 490 Configuring your database for use with Open ClientSetting up Adaptive Server IQ as an Open Server Option Set to Characteristics of Open Client and jConnect connections491 492 Characteristics of Open Client and jConnect connectionsTo change the option settings for TDS connections 493 Servers with multiple databasesTo connect to the livecredit server, use this syntax 494 495 Index 496 Index WINLATIN1 497 Datetime 498 Odbc Unix 499 500 DDL 501 Pipenotconnected 502 503 See Also Dbisql 504 505 506 Null 507 TCP/IP 508 See Also stored 509 Rawdetect 510 511 512 513 514 515 516

Sybase 12.4.2 manual 471

Models: 12.4.2

CHAPTER 13 Monitoring and Tuning Performance

471