Here you have the best Oracle 1z0-908 practice exam questions
Examine this statement, which executes successfully:
You want to improve the performance of this query:
Which change enables the query to succeed while accessing fewer rows?
To improve the performance of a query that filters based on a range of integer values, adding an index on the column involved in the WHERE clause is effective. In this case, adding an index on the Population column will enable the database to locate the rows corresponding to the specified range more efficiently. A SPATIAL INDEX is not appropriate here because Population is an integer type and spatial indexes are designed for geometrical data types. Thus, the correct change to improve query performance is to add an index on the Population column.
Which three are characteristics of a newly created role? (Choose three.)
A newly created role in MySQL 8.0 can be dropped using the DROP ROLE statement, is created as a locked account, and can be granted to user accounts. It is not stored in a mysql.role table, but rather in the mysql.user table. It cannot be protected with a password.
You have configured GTID-based asynchronous replication with one master and one slave.
A user accidentally updated some data on the slave.
To fix this, you stopped replication and successfully reverted the accidental changes.
Examine the current GTID information:
You must fix GTID sets on the slave to avoid replicating unwanted transactions in case of failover.
Which set of actions would allow the slave to continue replicating without erroneous transactions?
To ensure the slave continues replicating without erroneous transactions, we need to reset the GTID sets correctly. The GTIDs executed by the master go up to 10300, while the slave has executed up to 10167. By resetting the GTID sets to only include the transactions up to 10167, the slave can safely continue replicating without replaying the accidental transactions. Thus, the correct approach would be to execute RESET MASTER to clear the GTID state and then set the global gtid_purged to the appropriate range executed by the slave (1-10167).
The data in this instance is transient; no backup or replication will be required. It is currently under performing.
The database size is static and including indexes is 19G.
Total system memory is 32G.
After profiling the system, you highlight these MySQL status and global variables:
The OS metrics indicate that disk is a bottleneck.
Other variables retain their default values.
Which three changes will provide the most benefit to the instance? (Choose three.)
To improve the performance of the MySQL instance given that the disk is identified as the bottleneck, several changes can be made. Increasing the buffer pool size to 24G can reduce disk I/O by allowing more data to be cached in memory, which is beneficial given the 32G total system memory. Disabling innodb_doublewrite can also help reduce disk I/O, understanding that it introduces a risk of data corruption, which is acceptable in this case since the data is transient. Placing the innodb_undo_directory in /dev/shm, a memory-based filesystem, can further reduce disk I/O and improve performance, though it also comes with a potential risk of running out of space for undo logs under heavy write operations. These changes target the identified bottleneck and align with the given scenario where data durability is not a primary concern.
Which statement is true about InnoDB persistent index statistics?
Updating index statistics can be an I/O expensive operation because the process often involves reading a significant amount of data from storage to gather the necessary statistics, which can lead to high input/output operations, especially for large tables.