Oracle Oracle Certifications 1Z0-888 Questions & Answers

• Question 71:

  On a master server that is using statement-based replication, a table of log data has become very large. You decide to delete 100,000 rows.

  Which two methods can be independently invoked to ensure that the delete is properly propagated to the slave? (Choose two.)

  A. Change the replication mode to MIXED before issuing any delete statements when the LIMIT clause is used.

  B. If the data modification is non-deterministic, the query optimizer will resolve any potential issues.

  C. Use the LIMIT clause to limit the deletion to 100,000 rows.

  D. Use the LIMIT clause in conjunction with the ORDER BY clause.

  Correct Answer: AD

• Question 72:

  You have a server that has very limited memory but has a very large table.

  You will use mysqldump to back up this table.

  Which option will ensure mysqldump will process a row at a time instead of buffering a set of rows?

  A. --tab

  B. --single-transaction

  C. --quick

  D. --skip-buffer

  Correct Answer: C

  Reference: http://dev.mysql.com/doc/refman/4.1/en/mysqldump.html

• Question 73:

  You are investigating the performance of a query which selects data from an InnoDB table. Consider this Performance Schema diagnostics output for the query:

  

  Which statement is true about the output?

  A. The time the query took is the sum of all timer_wait values.

  B. The query did not find its table in the table definition cache.

  C. The query read data from the data file rather than directly from the buffer pool.

  D. The event with event_id = 8945 is a child of the event with event_id=8944.

  Correct Answer: B

• Question 74:

  The `applicationdb' is using InnoDB and consuming a large amount of file system space. You have a / backup partition available on NFS where backups are stored.

  You investigate and gather this information:

  

  Three tables are stored in the InnoDB shared tablespace and the details are as follows:

  The table data_current has 1,000,000 rows. The table data_reports has 1,500,000 rows. The table data_archive has 4,500,000 rows.

  

  You attempt to free space from ibdata1 by taking a mysqldump of the data_archive table and storing it on your backup partition.

  

  Unfortunately, this action does not free any actual disk space back to the file system and the server disk

  space is running out.

  Which set of actions will allow you to free disk space back to the file system?

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  Correct Answer: A

• Question 75:

  You have a config file for a running DB with this excerpt:

  [mysqld]

  tmp_table_size=16M

  sort_buffer_size=256k

  To address a query performance problem of connecting to the DB from an application on another host, you

  log in and make these changes to the DB:

  mysql> SET GLOBAL tmp_table_size=32000000;

  mysql> SET sort_buffer_size=2000000;

  This solves the problem with your queries. However, later the DB instance is restarted and the

  performance problem returns.

  Which three best describe this scenario?

  A. Global variables are not persistent across server restarts.

  B. Session variables are not persistent across server restarts.

  C. The query benefited from sort_buffer_size increase.

  D. sort_buffer_size should match tmp_table_size to be optimal.

  E. The query benefited from tmp_table_size increase.

  F. The query benefited from sort_buffer_size and tmp_table_size increases.

  Correct Answer: BCE

• Question 76:

  This output is from a SHOW SLAVE STATUS:

  

  What would cause the SQL_Delay variable to have a value of 360?

  A. The master has performed a large transaction, which will take 360 seconds to complete on the slave.

  B. The network latency between the master and the slave is 360 milliseconds.

  C. The slave will need an estimated 360 seconds to update the remaining contents from the relay log.

  D. The slave was configured for delayed replication with a delay of six minutes.

  Correct Answer: D

  Reference: https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/show-slave-status.html

• Question 77:

  In which order does MySQL process an incoming INSERT statement?

  A. It checks whether the user is authorized to perform the query, writes to the binary log, and then optimizes it.

  B. It checks whether the user is authorized to perform the query, optimizes it, and then writes to the binary log.

  C. It writes the query to the binary log, optimizes it, and then checks whether the user is authorized to perform the query.

  D. It optimizes the query, checks whether the user is authorized to perform it, and then writes to the binary log.

  Correct Answer: C

• Question 78:

  You are using replication and the binary log files on your master server consume a lot of disk space.

  Which two steps should you perform to safely remove some of the older binary log files? (Choose two.)

  A. Execute the PURGE BINARY LOGS NOT USED command.

  B. Edit the .index file to remove the files you want to delete.

  C. Ensure that none of the attached slaves are using any of the binary logs you want to delete.

  D. Remove all of the binary log files that have a modification date earlier than today.

  E. Use the command PURGE BINARY LOGS and specify a binary log file name or a date and time to remove unused files.

  Correct Answer: CE

  A: To safely purge binary log files, follow this procedure:

  1.

  On each slave server, use SHOW SLAVE STATUS to check which log file it is reading.

  2.

  Obtain a listing of the binary log files on the master server with SHOW BINARY LOGS.

  3.

  Determine the earliest log file among all the slaves. This is the target file. If all the slaves are up to date,

  this is the last log file on the list.

  4.

  Make a backup of all the log files you are about to delete. (This step is optional, but always advisable.)

  5.

  Purge all log files up to but not including the target file.

  Syntax:

  PURGE { BINARY | MASTER } LOGS

  { TO `log_name' | BEFORE datetime_expr }

• Question 79:

  Consider the CHECK TABLE command.

  In which two situations should this command be used? (Choose two.)

  A. to find out why a query takes a long time to execute on a given table

  B. to make sure a table has no structural problems

  C. to improve performance by updating index distributing statistics on InnoDB tables

  D. to repair table structure problem

  E. to make sure that no table indexes are corrupted

  Correct Answer: BE

  The CHECK TABLE statement performs an integrity check on table structure and contents. It works for MyISAM and InnoDB tables. For MyISAM tables, it also updates the index statistics. If the table is a view, CHECK TABLE verifies the view definition. If the output from CHECK TABLE indicates that a table has problems, the table should be repaired.

• Question 80:

  An administrator installs MySQL to run under a mysql OS account. The administrator decides to disable logins to the mysql account by using /nologin or /bin/false as the user's shell setting.

  Which statement is true?

  A. The mysql user needs a login and its home directory must be the base directory of the installation.

  B. The OS needs to allow logging in as mysql so that administrative tasks can be performed.

  C. This prevents mysqld from starting when standard startup scripts are used.

  D. This prevents creation of a command shell with the mysql account, while allowing mysqld to run.

  Correct Answer: A