Interview Questions.

Q1. What Are Indexes Used For?

Indexes permit faster get right of entry to to the rows of a desk.

You can build indexes for a single column or for a sequence of columns.

The definition of indexes determines whether the column value of different rows inside the listed columns should be precise or now not (UNIQUE or NON-UNIQUE index).

An assigned index name and the table names need to be precise. Therefore, there can be numerous indexes with the identical call for every database person or scheme, however no longer for the equal desk.

Q2. Is There Any Specific Size Limit For A Sap Maxdb Database?

One can configure a most of 255 information volumes within the SAP MaxDB standard format (parameter: VOLUMENO_BIT_COUNT or ConverterVolumeIDLayout= 8). The most size of a statistics extent can be 128 GB. However the most overall size of the statistics volumes can be 32 TB. The log place also can use a most of 32 TB.

Q3. Why Must Sql Locks Be Set On Database Objects?

If several transactions need to get admission to the same items in parallel, those accesses need to be synchronized by means of the SQL lock management.

Since the database machine lets in concurrent transactions for the identical database gadgets, locks are required to isolate individual transactions.

To lock an object way to lock this item from sure varieties of use via different transactions.

Q4. Is A Shared Lock Assigned To Each Data Record Of The Table During A Full Table Scan As Long As The Query Is Running?

In isolation stage 1, a lock is briefly used for each report and is eliminated before the following record.

In isolation stage three, a desk shared lock is used.

Q5. What Is An Exclusive Lock?

If an special lock on a database object is assigned to a transaction, different transactions can't get admission to this item.

Transactions that want to test whether or not distinct locks exist or to set one of a kind or shared locks collide with the existing one of a kind lock of the alternative transaction. They cannot get entry to the locked object.

Exclusive locks may be set on individual table traces or at the entire table.

Q6. Can I Check Individual Indexes For Consistency?

As of Version @8, character indexes can be checked for consistency. For extra facts about the assertion CHECK INDEX.

Q7. Can An Index Become Larger Than The Table To Which It Belongs?

Yes. This is viable, for instance, if the index is created on a touch used field (that is, a discipline packed with too little statistics), but the primary key calls for an excessive amount of reminiscence.

The key records is saved in a discounted form at index stage in the B* tree of the table, even as the secondary index ought to store each entire number one key.

Q8. How Is Data Accessed Using An Index?

If the SQL optimizer evaluates get entry to the use of the index as the best solution, the primary keys of the desk that in shape the index key fields used are decided within the table tree.

The corresponding rows are read from the table using this list of number one keys.

Q9. Which Lock Objects Do Exist In Sap Maxdb?

Three kinds of locks exist in SAP MaxDB:

Record locks (ROW):

Individual lines of a table are locked.

Table locks (TAB):

The complete desk is locked.

Catalog locks (SYS):

Database catalog entries are locked.

Q10. Is It Recommended To Configure All Data Volumes In The Same Lun?

It is recommended to distribute the data volumes across numerous LUNs. As in line with the various reviews, about 5 LUNs may be configured for every LUN.

Q11. Is There A Central Transaction In The Sap System For All Administrative Database Activities?

In the MaxDB environment, you may use the transaction DB50: Database Assistant to reveal the database.

You can use transaction LC10: liveCache Assistant to monitor a liveCache. In SAP Releases as of Release 7.0, you get right of entry to those transactions the usage of transaction DB59.

As of SAP Release 7.Zero the DBA Cockpit (transaction dbacockpit) is to be had as a principal access to the database or liveCache management.

Q12. Is It Recommended To Create The Sap Maxdb Volumes On Raw Devices Or Files?

The volumes of the kind "File" and of the kind "Raw" can be defined on UNIX.

A uncooked device is a hard disk or its any element which is not controlled by way of the operating device. Data volumes of the type "uncooked tool" can be configured for databases on UNIX.

Since the administrative attempt that is required for record systems does no longer practice and so the get right of entry to to raw gadgets is usually faster.

As the running gadget does not have to check the consistency of the record device on uncooked gadgets, so it can commonly boot quicker.

Because of the above mentioned blessings, it is encouraged to use raw devices on UNIX structures. However, volumes of the sort "File" on UNIX also are supported.

Volumes of the sort "File" are the encouraged trendy in LINUX.

Q13. Can I Create Several Indexes Simultaneously?

You can create several indexes simultaneously. However, considering that handiest one index build may be accomplished in parallel (by way of numerous server duties), we advocate (to speed up the creation of indexes) that you ensure that indexes on massive tables are commenced most effective if no other CREATE INDEX is active, when you are growing numerous indexes simultaneously. You can create indexes on small tables, although a CREATE INDEX announcement is already active.

Q14. Can Indexes Fragment In Sap Maxdb?

No. SAP MaxDB does not have an index fragmentation problem like that of Oracle (Note 771929).  Here, indexes are stored most suitable completely, and storage area is at once allocated to the freespace once more.

Q15. Is The Primary Key For Sap Maxdb Also Stored In A Separate Index-b* Tree?

Each database table has a primary key (primary index). The primary key is both described via the person or generated by the gadget. A user-defined number one key can include one or extra columns. The number one key have to have a unique fee for every row of the table.

The primary secret's implemented immediately on the records tree, because of this there may be no separate number one key tree. There is not any ROWID or some thing similar. The particular identity of a file is the primary key (for a couple of keys, that is the combination of fields which can be defined because the primary key).

Q16. What Is The Technical Structure Of Indexes?

Indexes, like tables, are carried out as B* trees in SAP MaxDB. These include a root node that bureaucracy a tree with the following nodes.

The whole index key and the references to the desk information are within the lowest degree of the index tree, otherwise called the leaf degree. The database system does no longer use bodily gadgets to become aware of these references, however rather those are saved inside the primary key of the information information. The statistics file is recognized by means of the primary key (the physical object of this facts document is decided through the converter).

Since get admission to to the information does now not follow the sequence Primary key -> Data, however instead Data -> Primary key, it's also called an inversion.

The idea in the back of this is that the relational desk layout provides for the fact that all facts is dependent on a completely unique primary key.

While the get entry to Primary key -> Data continually collects one or no rows, the get admission to Data -> Primary key collects no, one or a couple of rows.

Q17. How Large Is It Preferred To Configure The Io Buffer Cache?

The database parameter CACHE_SIZE or (as of Version 7.7.03) the database parameter CacheMemorySize need to be used to configure the IO buffer cache.

The converter cache and the records cache of an SAP MaxDB database are included within the IO buffer cache.

The database overall performance is substantially prompted by means of the size of the IO buffer cache. The larger the size of the IO buffer cache, the fewer time-eating difficult disk accesses have to be achieved.

The data extent this is to be processed in day-to-day business and the application decide the scale of the IO buffer cache that is to be set.

It is normally encouraged to process all the statistics in a device this is up and jogging, with out having access to the tough disk which is in the records cache. However it's far regularly not possible within the BI surroundings and OLTP environment.

It should be feasible to preserve all data this is to be processed inside the IO buffer cache, whilst the use of the SAP liveCache era. Generally, the consequences of the Quick Sizer must be used to configure the IO buffer cache for SAP liveCache technology.

The following applies to the IO buffer cache: the bigger, the better (supplied that enough bodily reminiscence is available).

It must be mentioned that a heap memory is also allocated by way of the database in addition to the IO buffer cache. The general memory intake of an SAP MaxDB database may be determined using the statistics from the gadget desk MEMORYALLOKATORSTATISTICS.

The range of successful and failed accesses to the records cache determines the statistics cache hit ratio. The hit ratio shows whether the scale of the statistics cache is well configured. However, if incredible applications are running at the time of the evaluation then the records cache hit ratio does not provide sufficient information. For example, all through year-stop remaining the hit ratio might also deteriorate due to the fact this statistics does not need to be held within the cache permanently. After without delay restarting the database, it isn't indicated by means of the facts cache hit ratio that whether the machine is well configured, because all statistics have to first be loaded into the cache.

The settings for the dimensions of the IO buffer cache which has been attempted and examined by means of many OLTP clients and BW customers for SAP systems are as follows:

OLTP NON-UNICODE: 1% of the information quantity

OLTP UNICODE: 2% of the records volume

BW NON-UNICODE: 2% of the information extent

BW UNICODE: 4% of the records volume
 

Q18. Does The Data Get Distributed Evenly On All Volumes, If A New Data Volume Is Added?

This mechanism may be activated the use of parameter EnableDataVolumeBalancing, as of MaxDB Version 7.7.06.09.

When the parameter EnableDataVolumeBalancing (deviating from the default) is set to cost YES, all of the information receives implicitly allotted frivolously to all facts volumes as soon as a brand new facts extent is both added or deleted.

An even distribution of information is brought on at some point of the restart.

Q19. Must Locks Be Explicitly Requested Or Is Implicit Locking Possible?

Locks may be asked implicitly via the database gadget or explicitly by using you (the use of the applicable SQL statements).

A) Requesting locks implicitly:

All enhancing SQL statements (for example, INSERT, UPDATE, DELETE) constantly request an exclusive lock.

You can choose the lock operation mode by specifying an isolation level whilst you open the database session.

Depending on the specified isolation degree, locks are then implicitly requested through the database device while the SQL statements are processed.

B) Requesting locks explicitly:

You can assign locks explicitly to a transaction the usage of the LOCK statement, and you may lock man or woman desk strains by way of specifying a LOCK option in an SQL statement. This method is viable at each isolation degree. In addition, you may use the LOCK option to briefly exchange the isolation degree of an SQL assertion.

Q20. What Search Strategies Are There With Indexes?

You will discover a description of the techniques used by the SQL optimizer under 'Strategy' in the thesaurus of the SAP MaxDB documentation.

Q21. Do I Need To Change A Data Volume Configuration Which Does Not Correspond To The Recommendations Of This Note?

No, it isn't recommended to exchange the present configurations. In case of the incidence of any serious I/O overall performance issues, one need to analyze the issues in detail to determine their actual cause.

Q22. What Do I Have To Bear In Mind With Regard To Views When Using Hints?

Hints can be applied to single-desk perspectives. However, apart from a few exceptions (consisting of ORDERED), you can't practice recommendations to sign up for perspectives.

Q23. What Administration Tools Are Available?

SAP recommends which you use the MaxDB tools DBMGUI/Database Studio, DBMCLI and SAP CCMS for the administration duties.

Q24. What Is A Parallel Index Build?

The data for the index is study in parallel through several server obligations to carry out the index build as fast as viable. Only one parallel index build may be executed at a time - if several CREATE INDEX statements are carried out on the same time, these different indexes are then processed with the aid of only one server mission. This is enormously slower than a parallel index construct. Therefore, you have to usually make sure that indexes are most effective created successively.

Q25. How Large Is It Preferred To Configure The Data Volumes Of A Sap Maxdb Database?

The most suitable use of the I/O device is essential for I/O overall performance. Thus, it will become beneficial to distribute the volumes evenly across the to be had I/O channels.

The parallelism of the I/O receives tormented by the number of information volumes.

Windows:

The asynchronous I/O of the running machine is used on Windows.

UNIX:

On UNIX the number of configured I/O threads determines the parallelism with which the SAP MaxDB/liveCache database transfers the I/O requests to the operating system.

O SAP MaxDB version lower than Version 7.7

The quantity of volumes * number of I/O threads for every extent (_IOPROCS_PER_DEV), gives the variety of I/O threads.

O SAP MaxDB Version 7.7 or better

Volumes * (general of low/med/high queues for each volume), gives the wide variety of I/O threads. But it may get constrained by using the database parameter MaxIOPoolWorkers.

The wide variety of threads receives extended through some of threads that was configured too excessive. This effects in attaining the bounds of the operating gadget resources.

It is recommended to use the subsequent method to determine the dimensions of SAP MaxDB statistics volumes: 'rectangular root of the gadget size in GB, rounded up'.

Examples:

10 GB: four statistics volumes

50 GB: 8 information volumes

a hundred GB: 10 information volumes

200 GB: 15 facts volumes

500 GB: 23 records volumes

1 TB: 32 information volumes

It is favored to have the identical size for all of the facts volumes.

Q26. Does Maxdb Support The Use Of External Backup Tools?

Yes. Max DB supports some of backup tools from third parties, including Networker, Netbackup from Veritas, and so forth.

Which unique gear are supported, and how they may be blanketed in a MaxDB surroundings is defined inside the MaxDB documentation (Note 767598). Search the glossary for the key-word "outside backup device".

Scripts within the database supervisor additionally allow you to apply all different backup equipment that are capable of procedure backups from pipes.

Q27. What Advantage Does A Backup Using The Maxdb Backup Tool Offer Compared To A File System Backup?

It is only feasible to execute a file system backup while the database is in the offline status. The length of time for which the database is unavailable for operation depends on the dimensions of the dataset.

In assessment, you may lower back up the database even as it's far in the on-line reputation the usage of the MaxDB backup tools, in parallel with production operation. You can use a backup generated online the use of database equipment for a device replica.

A record gadget backup of the volumes (statistics and log) backs up all information that is saved within the quantity.

In evaluation, a backup the usage of the MaxDB tools only backs up the facts of a converter version, which can be required for a recuperation.

When you create a file device backup, the device does no longer perform a checksum-check at the blocks.

While the backup is being created the usage of the MaxDB equipment, the gadget executes consistency checks at the records.

Even whilst you returned up the log volumes the usage of a report system backup, you want to additionally again up the log area the usage of the database tools, to make certain that the log region may be launched once more for overwriting.

A record gadget backup isn't always included inside the MaxDB backup records.

Backups executed the use of the MaxDB equipment integrate the backup records, and this makes restoring the database within the event of a restoration a easy process.

Q28. Is There Any Limited Number Of Simultaneous Sessions Of A Sap Maxdb Database?

No, there is no restrict for the number of simultaneous sessions of an SAP MaxDB database. The database parameter MaxUserTasks is used to configure the wide variety of database sessions that can be logged on simultaneously to the database.

OLTP:

The wide variety of database customers in OLTP structures have to be configured to at least 2 x <number_SAP processes > + four.

BW:

The range of database users in OLTP systems need to be configured to at least 3 x <number_SAP processes > + four.

Java applications:

The maximum variety of connections to the database inside the connection pool is determined for every J2EE example (NW 7.1 is the default 70). The sum of connections (connection pool) of all J2EE instances + four is used to calculate the wide variety of parallel person sessions (MaxDB parameter: MaxUserTasks).

LiveCache:

The method which is used to calculate the fee for the database parameter MaxUserTasks for liveCaches in SCM machine four.1 and decrease is:

2 x <number_SAP processes > + four

The formulation which is applied for liveCaches in SCM system five.Zero and above is:

3 x <number_SAP processes > + four

One also can seek advice from 757914.

Q29. Can Additional Cpus Be Assigned To The Database In Live Operation?

MaxDB Version 7.Eight presents the choice to apply the parameter UseableCPUs to dynamically upload extra CPUs to the database or to reduce the number of the use of CPUs. The parameter MaxCPUs maintains to manipulate the maximum range of CPUs for use.

Q30. How Are Locks On Joins Handled?

The SQL locks on joins are handled within the same way as locks on tables. There is no difference.

Q31. Are Locks Set During Create Index?

Yes. Up to and together with SAP MaxDB Version 7.6, a lock is set at the applicable tables at some point of the index creation.

As of SAP MaxDB Version @7, the gadget sets a lock for the whole length of the index creation best if the following situations observe:

if it's miles a UNIQUE index

if the transaction that executes the CREATE INDEX declaration has already set other locks.

Q32. Are Logs Also Saved During A Database Backup?

No. A database backup using the MaxDB tools most effective backups up the data that is saved inside the facts volumes. This consists of the actual use statistics and the "before pictures", so that it's far viable to restore a constant database the usage of handiest the database backup.

The records this is stored within the log quantity isn't backed up in the course of a information backup.

Q33. Where Can You Find The Information About The Configuration Of Sap Maxdb Volumes Of The Type "record"?

The performance of the database is greatly motivated with the aid of the rate with which the database device can read records from the facts volumes and might write data to the volumes. For making sure good overall performance at the same time as working the database later, one ought to see 993848 (Direct I/O mount alternatives for LiveCache/MaxDB) for data approximately growing and configuring volumes of the kind "File".

Q34. What Do I Do If The Data Area Is Full?

When there may be no more reminiscence to be had inside the database, the database hangs. You must make loose reminiscence to be had in the shape of a brand new information volume. You can add new volumes whilst the database is on-line.

More records about this subject matter is available is in the MaxDB documentation (Note 767598) within the glossary, under the keyword "records volume" or "db_addvolume".

Q35. Which Isolation Levels Are Available?

Isolation level zero:

In isolation degree 0 (uncommitted), strains are examine with out an implicit request of shared locks (dirty read). This does no longer make certain that after a line is study once more inside a transaction, it has the same nation as whilst it became examine the primary time due to the fact it can were modified by a concurrent transaction within the interim. This does not make certain that study statistics are simply committed inside the database.

When you insert, alternate, and delete lines, the facts facts are completely locked. The locks are retained until the transaction ends to ensure that no concurrent adjustments can be made.

The SAP device is operated at isolation stage 0 due to the fact a separate lock mechanism that is at a higher stage than the lock mechanism of the database is carried out for the SAP application.

Isolation stage 1:

If an SQL announcement is used for a records retrieval, it's miles ensured for each read line that (while a line is study) no different transaction holds an distinctive lock for this line. The shared lock is eliminated after the document become examine. If lock collisions arise, a lock (REQ ROW SHARE) is asked and the transaction need to wait until this lock is assigned to it earlier than it can get right of entry to the document.

Isolation level 15:

Isolation stage 15 ensures that the resulting set isn't modified at the same time as it's far being processed. Reading backwards and positioning within the resulting set creates precise outcomes. When you insert, change, or delete strains, unique locks for the applicable strains are implicitly assigned to the transaction; those different locks are d simplest when the transaction ends.

Isolation level 2:

Isolation stage 2 protects towards the non-repeatable read. A file this is examine several times within a transaction obtains usually the identical values.

If isolation stage 2 or 20 (repeatable) is specific, before processing starts offevolved, shared locks are implicitly requested for all of the tables which can be addressed by means of a SQL declaration for the records retrieval. When you insert, change, or delete lines, exclusive locks for the applicable traces are implicitly assigned to the transaction; these special locks are d only when the transaction ends.

Isolation degree three:

If isolation level 3 or 30 (serializable) is certain, a table shared lock is implicitly assigned to the transaction for each desk this is addressed with the aid of an SQL declaration. These shared locks may be d only when the transaction ends. Isolation degree 3 protects towards 3 extraordinary get admission to types (grimy read, non-repeatable read, and phantom), however isn't appropriate for an operation with a high degree of parallelism. When you insert, change, or delete lines, distinct locks for the applicable lines are implicitly assigned to the transaction; those unique locks are d simplest while the transaction ends.

New isolation level as of the use of MVCC:

When you begin the use of those new isolation ranges, the isolation levels cited above are not available.

Isolation degree 50:

Isolation level 50 corresponds to COMMITTED READ.

Isolation degree 60:

Isolation stage 60 corresponds to "Serializable" that is recognised from Oracle. This is a lesser requirement than ISO/ANSI "Serializable".

The antique isolation levels which can be decrease than three are mapped to the brand new isolation degree 5@The antique isolation stage 3 is mapped to the new isolation stage 60.

Q36. What Is The Isolation Level?

The isolation stage defines whether and in which manner locks are implicitly asked or d.

The selected isolation degree affects the degree of parallelism of concurrent transactions and the information consistency:

The lower the fee of the isolation stage, the higher the diploma of parallelism and the lower the degree of the guaranteed consistency.

If there are concurrent accesses to the identical dataset, numerous inconsistencies (inclusive of grimy examine, non-repeatable examine, and phantom) may arise at distinct isolation levels.

For SAP packages (ABAP stack), the isolation level is defined in the XUSER report. The ABAP programs run at isolation level zero.

In NetWeaver (Java stack), facts resources are used for the application logon. These information resources can be configured using the NetWeaver. You can freely pick out the isolation degree. If the records source does no longer specify an express isolation stage, default isolation level 1 (read dedicated) is used.

Q37. What Is A Shared Lock?

Shared locks allow different transactions to carry out study accesses however now not to carry out write accesses to the locked item. Other transactions can set further shared locks on this item, however no one of a kind locks.

Shared locks may be set on person table traces or on the entire table.

Q38. What Does The Following Sql Error -1000 Mean: Too Many Lock Requests?

A transaction terminates because too many SQL locks were requested. The configured maximum value (MaxSQLLocks) has been reached. The gadget can assign no similarly SQL locks.

If these issues often occur inside the gadget, test the price of the database parameter MaxSQLLocks.

Q39. How Can I Speed Up The Index Build?

You can speed up the index build on massive tables by means of making sure that simplest one CREATE INDEX assertion is lively, and consequently numerous server duties are sporting out the index build.

The statistics cache have to be configured sufficiently large, in order that ideally all statistics for the index build may be loaded in the cache.

Q40. What Is The Central Scheduling Calendar?

The imperative CCMS scheduling calendar incorporates transactions DB13 and DB13C.

You can use those transactions to agenda the following sports:

Back up the dataset region and the log region

Check backups for completeness

Update the optimizer statistics

Database shape assessments

Q41. Can I Create An Index On A View?

No. View tables are views on tables. The tables which are worried in view tables are called base tables.

These views on base tables are implemented as SELECT statements on the bottom tables. Technically, views tables may be compared to stored SELECT statements.

Therefore, no indexes may be created on view tables, however they may be created on the base tables of view tables.

Q42. What Is An Index?

You can create an index (additionally referred to as secondary key) to speed up the search for database information in a table. An index is a database item that may be defined for a single column or a sequence of columns of a database table.

In technical terms, indexes are statistics structures (consisting of one or extra inverting lists), which store parts of the statistics of a desk in a separate B* tree shape. This storage types the data in line with the inverting key fields that were used. Due to this form of storage, the table information can be accessed quicker the usage of the indexed columns than with out the relevant index.

Indexes, unlike tables, do not include any unbiased commercial enterprise records, and consequently can constantly be created again with the aid of the desk. For instance, that is applicable if corruption takes place at the index because of hardware troubles.

Q43. Does The Sap Maxdb Use Consistent Reading?

Consistent reading has no longer been supported but in the OLTP operation and OLAP operation. The lock control of SAP MaxDB could be changed in more present day variations because of the MVCC implementation. Currently, we can not specify in which SAP MaxDB version this function may be used productively.

Q44. When Should You Increase The Database Parameter Maxsqllocks (maxlocks)?

MaxSQLLocks values that are too excessive result in lengthy search processes inside the lock lists. Therefore, if that is possible, reduce the write transactions by using using common commits.

You can boom MaxSQLLocks if one of the following conditions often occur:

There is a high wide variety of LOCK LIST ESCALATIONS

the range of LOCK LIST MAX USED ENTRIES equals the price of MaxSQLLocks.

The wide variety of LOCK LIST MAX USED ENTRIES is close to the fee of MaxSQLLocks and the write transactions cannot be decreased.
 

Q45. Why May It Take A Long Time Until A Task Is Available Again After You Cancel The User Task To Cancel The Creation Of An Index?

If you cancel a CREATE INDEX declaration, the cancel indicator is ready for the consumer task. However, before the mission can be re-released, it should clear all index lists that have been created before by way of the server responsibilities for the index build.

This guarantees that every one of the structures which are not required are deleted from the gadget when you cancel a CREATE INDEX declaration. This may additionally make the effort relying at the number and size of the index lists.

Q46. Must I Carry Out An Update Statistics For Indexes?

No. You aren't required to explicitly create the records for the indexes by carrying out an UPDATE STATISTICS.

Q47. How Do I Perform A Database Backup?

To perform a database backup, use the MaxDB backup tools DBMGUI or DBMCLI. You can use the scheduling calendar (DB13 or DB13C) to schedule a backup for a selected point in time and execute it implicitly.

It is possible to execute record system backups of MaxDB volumes whilst the database is offline best, although we propose this best in superb instances (along with in case you use snapshots or cut up-mirrors).

Q48. Is There Any Specific Limit For The Number Of Data Volumes?

A maximum of 255 data volumes can be configured within the SAP MaxDB widespread format.

Q49. How Should The Database Parameter Maxcpu Be Set For Dual Core Cpus?

It is suggested to apply the wide variety of cores as a basis for calculating MAXCPU (as of Version 7.7., this is MaxCPUs), due to the fact the twin center CPUs have two cores with separate execution devices (a separate L1 cache and now and again even a separate L2 cache).

One also can see FAQ 936058: MaxDB Runtime Environment, for facts about placing the database parameter MAXCPU (MaxCPUs).

Q50. Can Administration Tasks Be Automated?

You can use the CCMS scheduling calendar or the MaxDB equipment to automate both administration duties that want to run often (such as automated log backups, Update Statistics) or that handiest need to be run when required (along with extending the data vicinity).