DBS-C01 Practice Test 44 — 15 Questions

Question 1

A company is using Amazon Redshift for data warehousing. The VACUUM operation is taking longer than expected, and the database administrator wants to identify the tables that require the most vacuuming effort. Which system table should be queried to find the percentage of deleted rows per table?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

SVV_TABLE_INFO. (SVV_TABLE_INFO) is correct because this system table contains tombstone and unsorted statistics, including the percentage of deleted rows per table, which helps identify tables requiring vacuuming. Option A (STL_QUERY) is incorrect because it stores query execution logs, not table statistics. Option B (STV_TBL_PERM) is incorrect as it provides information about permanent table block allocations but not deleted row ratios. Option C (PG_TABLE_DEF) is incorrect because it holds table schema definitions only, not operational metrics.

Answer

STL_QUERY

Answer

STV_TBL_PERM

Answer

PG_TABLE_DEF

Question 2

A company is running an Amazon RDS for PostgreSQL DB instance with Multi-AZ. The database experiences a failover during a maintenance window. After the failover, the application connection pool continues to use the old primary endpoint, causing connection errors. What is the BEST way to ensure application connections automatically redirect to the new primary after a failover?

Accepted Answer

Use the RDS endpoint (CNAME) provided by RDS, which automatically points to the primary instance.. The RDS endpoint (CNAME) provided by Amazon RDS automatically points to the current primary instance. After a failover, RDS updates the CNAME record to point to the new primary, so application connections using the endpoint are seamlessly redirected without manual intervention. This is the simplest and most reliable approach. Option B (Custom Route 53 failover routing) is unnecessary because RDS already provides a managed DNS endpoint. Option C (modifying connection string manually) is not automated and prone to human error. Option D (static IP) is not practical as RDS instances do not have static IPs; the DNS endpoint handles failover transparently.

Answer

Create a custom Route 53 failover routing policy pointing to both DB instances.

Answer

Modify the application connection string to point to the new primary IP address after each failover.

Answer

Configure the application to use a static IP address of the primary instance.

Question 3

A company is using Amazon RDS for MySQL with Multi-AZ deployment. The application team reports increased latency during peak hours. Which AWS service should the database specialist use to identify the root cause?

Accepted Answer

Enable Performance Insights for the RDS instance and analyze the database load.. Performance Insights provides detailed database performance metrics and helps identify bottlenecks such as high load or slow queries. Option B is wrong because AWS Config tracks configuration changes, not performance. Option C is wrong because CloudWatch Metrics Dashboard shows aggregated metrics but lacks the granular database analysis needed for root cause identification. Option D is wrong because Amazon Inspector is a security assessment tool, not a performance monitoring service.

Answer

Enable AWS Config to track configuration changes to the RDS instance.

Answer

Use the CloudWatch Metrics Dashboard to analyze database connections.

Answer

Run an Amazon Inspector assessment on the RDS instance.

Question 4

A company uses Amazon DynamoDB to store session data for a web application. The application is experiencing occasional throttling (ProvisionedThroughputExceededException) during peak hours. The DynamoDB table has on-demand capacity mode. Which combination of steps should be taken to resolve the issue?

Accepted Answer

Review the partition key design and consider using a write sharding pattern to distribute traffic evenly.. Even with on-demand capacity, DynamoDB applies a per-partition throughput limit (3,000 RCU or 1,000 WCU). Throttling occurs when traffic is unevenly distributed across partitions due to a suboptimal partition key. Reviewing the partition key and applying a write sharding pattern (e.g., adding a random suffix) distributes writes evenly, preventing throttling. Option B (DAX) only reduces read load and does not fix write throttling. Option C (auto scaling) is for provisioned mode, not on-demand. Option D (switching to provisioned) does not resolve uneven partition access; proper key design is still needed.

Answer

Enable DynamoDB Accelerator (DAX) to cache the frequently accessed items.

Answer

Enable auto scaling for the table and set minimum and maximum capacity.

Answer

Switch to provisioned capacity and increase the read and write capacity units.

Question 5

A company is migrating an on-premises Oracle database to Amazon RDS for Oracle. The database is 500 GB and has a 4-hour downtime window. Which AWS service should be used to perform the migration with minimal downtime?

Accepted Answer

AWS Database Migration Service (DMS). AWS Database Migration Service (DMS) is the correct choice because it supports ongoing replication (change data capture) to minimize downtime during the migration. Oracle Data Pump is an export/import tool that requires downtime and cannot perform continuous replication. AWS Snowball Edge is for offline bulk data transfer, not suitable for a 4-hour window with minimal downtime. Amazon S3 Transfer Acceleration only speeds up uploads to S3, not a database migration tool. Therefore, DMS is the recommended service for minimal downtime migration.

Answer

Oracle Data Pump using Amazon RDS option groups

Answer

AWS Snowball Edge

Answer

Amazon S3 Transfer Acceleration

Question 6

A database specialist is troubleshooting an Amazon RDS for PostgreSQL instance that is experiencing intermittent connection timeouts. The application logs show errors like 'FATAL: remaining connection slots are reserved for non-replication superuser connections'. The max_connections parameter is set to 100. What should the specialist do to resolve this issue?

Accepted Answer

Modify the 'max_connections' parameter to a higher value and reboot the instance.. The error indicates that the maximum number of connections has been reached. Increasing the max_connections parameter allows more concurrent connections, resolving the issue. Option B is incorrect because the error is about regular connections, not replication slots. Option C is incorrect because the superuser_reserved_connections parameter reserves slots for superusers; the error is about all connection slots being exhausted. Option D is incorrect because RDS Proxy manages connection pooling but does not increase the connection limit.

Answer

Increase the 'max_replication_slots' parameter to allow more replication connections.

Answer

Increase the value of the 'superuser_reserved_connections' parameter.

Answer

Enable RDS Proxy to manage database connections efficiently.

Question 7

An application running on Amazon EC2 is unable to connect to an Amazon RDS for SQL Server DB instance. The security group for the RDS instance allows inbound traffic from the security group of the EC2 instance on port 1433. The network ACLs allow all traffic. What is a likely cause of the connectivity issue?

Accepted Answer

The RDS instance is in a private subnet and does not have a public IP address. If the RDS instance is in a private subnet without a public IP address, the EC2 instance cannot connect using the public DNS endpoint unless the EC2 is in the same VPC. Since the security group and network ACLs are correctly configured, the most likely cause is that the RDS instance is not publicly accessible and the EC2 instance is attempting to connect from outside the VPC or via the public endpoint. Option B is wrong because network ACLs allow all traffic. Option C is wrong because the security group inbound rule is correctly configured to allow traffic from the EC2 security group on port 1433. Option D is wrong because port 1433 is the default SQL Server port, and if it were not, the error message would indicate a timeout, not a connectivity issue.

Answer

The network ACL is blocking the traffic

Answer

The security group inbound rule is incorrectly configured

Answer

The database port is not 1433

Question 8

A financial services company uses Amazon Redshift for analytics. The workload consists of a mix of short-running queries from dashboards and long-running ETL jobs. The company notices that during peak hours, short queries experience high latency due to queueing behind ETL jobs. How can the company reduce the impact of ETL jobs on dashboard queries?

Accepted Answer

Configure workload management (WLM) queues to separate ETL and dashboard queries, and assign different concurrency levels.. Amazon Redshift's Workload Management (WLM) allows you to create separate queues for different query types, such as ETL jobs and dashboard queries. By assigning different concurrency levels to each queue, you prevent long-running ETL jobs from consuming all available slots and blocking short dashboard queries, thereby reducing latency during peak hours.

Answer

Enable concurrency scaling to handle bursts of queries.

Answer

Enable short query acceleration (SQA) to prioritize queries that run under a certain time threshold.

Answer

Increase the number of nodes in the Redshift cluster.

Question 9

A company is migrating an on-premises Oracle database to Amazon RDS for Oracle. The database is 500 GB in size and has a 24/7 uptime requirement. The migration window is limited to 2 hours. Which strategy should be used to minimize downtime?

Accepted Answer

Use AWS Database Migration Service (DMS) with ongoing replication. AWS Database Migration Service (DMS) can perform ongoing replication from the source Oracle database to Amazon RDS for Oracle, allowing a minimal cutover window of under 2 hours. Option A is incorrect because AWS Schema Conversion Tool (SCT) only converts schema and does not handle data migration. Option B is incorrect because performing an on-premises backup and restoring via Oracle RMAN would require significant downtime and may exceed the 2-hour window for a 500 GB database. Option C is incorrect because exporting to a dump file and importing into RDS requires the database to be offline or in read-only mode, causing prolonged downtime.

Answer

Use AWS Schema Conversion Tool (SCT) to convert the schema and copy data

Answer

Take an on-premises backup and restore to RDS using Oracle RMAN

Answer

Export the database to a dump file and import into RDS

Question 10

A company is using Amazon ElastiCache for Redis to cache frequently accessed data. Recently, the application has been experiencing increased latency. The database specialist suspects that the cache hit ratio has decreased. Which CloudWatch metric should the specialist analyze to confirm this suspicion?

Accepted Answer

Monitor 'CacheHits' and 'CacheMisses' metrics to calculate the hit ratio.. The cache hit ratio is calculated as CacheHits / (CacheHits + CacheMisses). Monitoring the 'CacheHits' and 'CacheMisses' CloudWatch metrics allows the specialist to compute the hit ratio and confirm whether it has decreased, which would explain increased latency. Option A is incorrect because CurrConnections shows the number of connections, not the cache hit ratio. Option B is incorrect because Evictions indicates keys being evicted, which can affect the cache but does not directly measure the hit ratio. Option D is incorrect because ReplicationLag measures replication delay, not cache performance.

Answer

Monitor the 'CurrConnections' metric to see if there are too many connections.

Answer

Monitor the 'Evictions' metric to see if keys are being evicted.

Answer

Monitor the 'ReplicationLag' metric to check replication delay.

Question 11

A company runs an Amazon RDS for Oracle DB instance. The database administrator wants to receive an alert when the storage space is below 10% of the allocated storage. Which Amazon CloudWatch metric and alarm threshold should be used?

Accepted Answer

Metric: FreeStorageSpace, Condition: < 10% of 100 GB (10 GB). The correct metric is FreeStorageSpace, which reports the available storage in bytes. To alert when storage is below 10% of allocated, compute the threshold as 10% of the total allocated storage (e.g., 10 GB for 100 GB). Option A defines the condition correctly. Option B is wrong because BinaryLogUsage applies to MySQL, not Oracle. Option C is incorrect as FreeableMemory tracks memory, not storage. Option D is wrong because DiskQueueDepth measures I/O queue depth, not storage capacity.

Answer

Metric: BinaryLogUsage, Condition: > 10%

Answer

Metric: FreeableMemory, Condition: < 10% of total memory

Answer

Metric: DiskQueueDepth, Condition: > 10

Question 12

Refer to the exhibit. A DBA sees the above error log entries for an Amazon RDS for MySQL DB instance. Which action should the DBA take to resolve the 'Too many connections' error?

Accepted Answer

Modify the DB parameter group to increase max_connections. Increasing the max_connections parameter in the DB parameter group allows the MySQL instance to handle more concurrent connections, directly resolving the 'Too many connections' error. Option B is incorrect because rebooting the DB instance does not change the connection limit, only temporarily clears existing connections. Option C is incorrect because modifying the DB security group adjusts network access rules, not the database's internal connection limit. Option D is incorrect because resetting the master user password addresses authentication issues, not the connection limit.

Answer

Reboot the DB instance

Answer

Modify the DB security group to allow more connections

Answer

Reset the DB instance master user password

Question 13

A company is running a MongoDB-compatible Amazon DocumentDB cluster with one writer and two readers. The application writes a large amount of data during batch processing, and after a batch completes, the writer's CPU is high, and the readers have significant replica lag. The team wants to reduce replica lag without affecting the batch performance. What should they do?

Accepted Answer

Increase the instance size of the readers to improve apply throughput. Replica lag in Amazon DocumentDB occurs when readers are unable to apply writes from the writer's oplog quickly enough. Increasing the instance size of the readers provides more CPU and memory resources, improving their apply throughput and reducing lag without affecting the writer's batch performance. Option A is incorrect because changing the storage type to Provisioned IOPS may improve I/O but does not directly address the reader's apply capacity. Option C is incorrect because reducing batch size would lower the write rate and degrade batch performance, which the team wants to avoid. Option D is incorrect because increasing the writer's instance size may help the writer handle more writes, but it does not help readers apply those writes faster; it could even increase the write rate and worsen replica lag.

Answer

Change the storage type to Provisioned IOPS on all instances

Answer

Reduce the batch size to lower the write rate

Answer

Increase the instance size of the writer to handle the batch faster

Question 14

A gaming company uses Amazon DynamoDB to store player scores. The table has a partition key of 'game_id' and a sort key of 'player_id'. The application needs to retrieve the top 10 players for a given game_id based on score (stored as an attribute). The game_id has high cardinality. The team wants to avoid full table scans. Which design pattern is MOST efficient?

Accepted Answer

Create a global secondary index with partition key game_id and sort key score. A global secondary index (GSI) with partition key 'game_id' and sort key 'score' allows DynamoDB to efficiently retrieve the top 10 players for a given game_id by querying the index with the Query API, using ScanIndexForward=false to get items in descending order of score, and Limit=10. This avoids full table scans and leverages GSI's separate throughput capacity. Option C (LSI) could theoretically be used if the table was created with the LSI, but GSIs are preferred because they can be added after table creation, have dedicated throughput, and are more scalable. Additionally, querying an LSI still consumes base table capacity, which may affect performance.

Answer

Query the table by game_id and sort the results in the application

Answer

Use a Scan operation with a filter expression and limit 10

Answer

Create a local secondary index with partition key game_id and sort key score

Question 15

A company is migrating an on-premises SQL Server database to Amazon RDS for SQL Server. During the migration, the DBA notices that the network latency between the source and target is high. Which THREE actions can the DBA take to reduce the impact of network latency on migration performance?

Accepted Answer

Use multiple parallel tasks in AWS DMS. Options B, C, and D are correct. Using multiple parallel tasks in AWS DMS (option B) improves throughput by transferring data in parallel. Using AWS Direct Connect (option C) provides a dedicated, low-latency network connection. Enabling data compression (option D) reduces the amount of data transferred, mitigating latency impact. Option A is incorrect because increasing the RDS instance size does not reduce network latency; it only affects database processing capacity. Option E is incorrect because using a VPN can add overhead and may not reduce latency.

Answer

Increase the size of the RDS instance

Answer

Use AWS Site-to-Site VPN