Oracle Oracle Database Application Development 1Z0-883 Questions & Answers

• Question 31:

  You are using the performance Schema to investigate replication on a slave:

  Mysql> SELECT THREAD_ID threads.NAME, SUM (COUNT_STAR) AS Totalcount, SUM (SUM_TIMER_WAIT) AS Totaltime FROM performance_schema.events_waits_summary_by_thread_by_event_name INNER JOIN performance_schema,threads USING (THREAD_ID) WHERE threads .NAME LIKE `thread/sql/slave\-%' GROUP BY THREAD_ID, threads.NAME;

  

  Assume that all instruments and consumers are enabled and all threads are instrumented. Which two facts can be concluded from the given output?

  A. At most two schemas are being updated concurrently.

  B. The server needs more cores to use all slave threads.

  C. The slave cannot process the relay log fast enough to use all threads.

  D. The slave is configured with slave_parallel_workers = 4.

  Correct Answer: CD

• Question 32:

  You want to create a temporary table named OLD_INVENTORY in the OLD_INVENTORY database on the master server. This table is not to be replicated to the slave server.

  Which two changes would ensure that the temporary table does not propagate to the slave?

  A. Use the replicate-do-db, -- replicate-do-table, or replicate-wild-do-table option with the value equal to OLD_INVENTORY.

  B. Change the binlog_format option to ROW and restart mysqld before you create the OLD_INVENTORY table.

  C. Stop SQL_THREAD on the slave until you have finished using the OLD_INVENTORY temporary table.

  D. Set binlog_format=MIXED with the replicate-ignore-temp-table option.

  E. Use the replicate-ignore-table option with the value equal to OLD_INENTORY.OLD_INVENTORY and restart mysqld before creating the temporary table.

  Correct Answer: AD

• Question 33:

  What are three facts about backups with mysqldump?

  A. Can back up a remote database server

  B. Allow a consistent backup to be taken

  C. Are always faster to restore than binary backups

  D. Are able to back up specific items within a database

  E. Create automatically compressed backups

  F. Will lock all storage engines for duration of backup

  Correct Answer: DEF

• Question 34:

  In a test database, you issue the SELECT ... INTO OUTFILE statement to create a file with your t1 table

  data.

  You then TRUNCATE this table to empty it.

  Mysql> SELECT * INTO OUTFILE `/tmp/t1.sql' from t1; mysql> TRUNCATE t1;

  Which two methods will restore data to the t1 table?

  A. Mysql> LOAD DATA INFILE `/tmp/t1.sql' INTO TABLE t1;

  B. $ mysqladmin u root p h localhost test restore /tmp/t1.sql

  C. $ mysql u root p h localhost test < /tmp/t1.sql

  D. $ mysqlinport u root p h localhost test /tmp/t1.sql

  E. Mysql> INSERT INTO t1 VALUES FROM `/tmp/t1.sql';

  Correct Answer: AD

• Question 35:

  Which two statements are true about setting the per-thread buffers higher than required?

  A. More memory per thread is beneficial in all scenarios.

  B. It causes increased overhead due to initial memory allocation.

  C. It can affect system stability during peak load times, due to swapping.

  D. It requires increasing the thread_cache_size variable.

  Correct Answer: CD

• Question 36:

  You are creating a new server with the same accounts as an existing server. You do this by importing a

  mysqldump file of the mysql database.

  You test whether the import was successful by using the following commands:

  Mysql> select user, host, password from mysql.user;

  

  9 rows in set (0.00 sec)

  Mysql> show grants for `admin'@'%';

  ERROR 1141 (42000): There is no such grant defined for user `admin' on host `%'

  Which command will fix this issue?

  A. CREATE USER `admin' @'%';

  B. GRANT USAGE ON *.* TO `admin'@'%';

  C. FLUSH PRIVILEGES;

  D. FLUSH HOST CACHE;

  E. UPDATE mysql.user SET Create_user_priv = `Y' WHERE user= `admin';

  Correct Answer: C

• Question 37:

  You are investigating the performance of the server and see the following information:

  Events_waits_summary_global_by_event_name in the performance schema shows that the wait/synch/ mutex/sql/LOCK_table_cache event is dominating other wait events.

  The table_open_cache_overflows status variable is 0.

  Which action should be taken to remove the performance bottleneck described here?

  A. Decrease the value of table_definition_cache.

  B. Increase the value of table_definition_cache.

  C. Decrease the value of table_open_cache.

  D. Increase the value of table_open_cache.

  E. Decrease the value of table_open_cache_instances.

  F. Increase the value of table_open_cache_instances.

  Correct Answer: AD

• Question 38:

  Which statement is true about the log-output variable?

  A. It is a static variable and can be set only at MySQL server startup.

  B. It enables and starts the General Query Log.

  C. It sets the target location for the binary logs generated by the MySQL sever.

  D. It specifies output destinations for the slow and General Query logs.

  Correct Answer: D

• Question 39:

  The `allplicationdb' 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 the following information:

  [mysqld] Datadir=/var/lib/mysql/ Innodb_file_per_table=0

  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.

  Shell> is -1 /var/lib/mysql/

  -rw-rw---- 1 mysql mysql 744G Aug 26 14:34 ibdata1 -rw-rw---- 1 mysql mysql 480M Aug 26 14:34

  ib_logfile0 -rw-rw---- 1 mysql mysql 480M Aug 26 14:34 ib_logfile1 ...

  You attempt to free space from ibdata1 by taking a mysqldump of the data_archive table and storting it on

  your backup partition.

  Shell> mysqldump u root p applicationdb data_archive > /backup/data_archive.sql Mysql> DROP TABLE

  data_archive;

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

  A. Execute OPTIMIZE TABLE so that the InnoDB engine frees unused pages on disk back to the file system: Mysql> OPTIMIZE TABLE data_current, data_reports;

  B. Set the server to use its own tablespace, and then alter the table so that data is moved from the shared tablespace to its own: Mysql> SET GLOBAL innodb_file_per_table=1; Mysql> ALTER TABLE data_current ENGINE=InnoDB; Mysql> ALTER TABLE data_repors ENGINE=InnoDB;

  C. Take a backup, stop the server, remove the data files, and restore the backup: Shell> mysqldump u root p applicationdb / > /backup/applicationdb.sql Shell> /etc/init.d/mysql stop Shell> cd /var/lib/mysql/ Shell> rm ibdata1 ib_logfile0 ib_logfile1 Shell> /etc/init.d/mysql start Shell> mysql u root p applicationdb < /backup/applicationdb.sql

  D. Enable compression on the table, causing InnoDB to release unused pages on disk to the file system: Mysql> SET GLOBLE innodb_file_per_table=1; Mysql> SET GLOBLE innodb_file_format=Barramcuda; Mysql> ALTER TABLE data_current ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; Mysql> ALTER TABLE data_history ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

  Correct Answer: D

• Question 40:

  What is true regarding InnoDB locking?

  A. InnoDB row locks may be escalated to page or table-level locks.

  B. InnoDB only uses row locks, not page or table-level locks,

  C. InnoDB uses row and table-level locks, but row locks are not escalates,

  D. InnoDB locks only those rows that are updated.

  E. InnoDB uses row-level or table-level locks depending on the number of rows affected.

  Correct Answer: E