Oracle 1Z0-883 Online Practice Questions and Exam Preparation

Oracle 1Z0-883 Online Questions & Answers

• Question 61:

  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.
  C. The slave cannot process the relay log fast enough to use all threads.

• Question 62:

  Full Atomicity, Consistency, Isolation, Durability (ACID) compliance is a necessity for a new application, which heavily reads and writes data.

  This requires the following config file options:

  Sync_binlog=1 Innodb_flush_log_at_trx_commit=1 Innodb_doublewrite=1

  However, this configuration is expected to introduce disk I/O overhead.

  What three changes will reduce disk I/O overheads?

  A. Use of soft links for database directories on the same physical disk
  B. Use of separate directories on the same physical disk for log files and data files
  C. Placement of InnoDB log files and datadir on separate physical disks
  D. Allocation of RAM to the buffer pool such that more of the data can fit in RAM
  E. Use of delay_key_write=ON for batch index update
  D. Allocation of RAM to the buffer pool such that more of the data can fit in RAM
  E. Use of delay_key_write=ON for batch index update

• Question 63:

  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';
  A. Mysql> LOAD DATA INFILE `/tmp/t1.sql' INTO TABLE t1;

• Question 64:

  Consider the query:

  Mysql> SET @run = 15;

  Mysql> EXPLAIN SELECT objective, stage, COUNT (stage) FROM iteminformation

  WHERE run=@run AND objective='7.1'

  GROUP BY objective,stage

  ORDER BY stage;

  

  The iteminformation table has the following indexes; Mysql> SHOW INDEXES FROM iteminformation:

  

  This query is run several times in an application with different values in the WHERE clause in a growing data set.

  What is the primary improvement that can be made for this scenario?

  A. Execute the run_2 index because it has caused a conflict in the choice of key for this query.
  B. Drop the run_2 index because it has caused a conflict in the choice of key for this query.
  C. Do not pass a user variable in the WHERE clause because it limits the ability of the optimizer to use indexes.
  D. Add an index on the objective column so that is can be used in both the WHERE and GROUP BY operations.
  E. Add a composite index on (run,objective,stage) to allow the query to fully utilize an index.
  B. Drop the run_2 index because it has caused a conflict in the choice of key for this query.

• Question 65:

  Consider the following table:

  REATE TABLE `game' (

  `id' int (10) unsigned NOT NULL AUTO_INCREMENT,

  `keyword' varchar (45) DEFAULT NULL,

  `date' datetime NOT NULL,

  PRIMARY KEY (`id' , `date'),

  UNIQUE KEY `keyword_idx' (`keyword' , `date')

  ) ENGINE=InnoDB DEFAULT CHARSET=latin1

  PARTITION BY RANGE (TO_DAYS (date) ) (

  PARTITION g201301 VALUES LESS THAN (TO_DAYS (`2013-01-01 00:00:00') ),

  PARTITION g201302 VALUES LESS THAN (TO_DAYS (`2013-02-01 00:00:00') ),

  PARTITION g201303 VALUES LESS THAN (TO_DAYS (`2013-03-01 00:00:00') ),

  PARTITION g201304 VALUES LESS THAN (TO_DAYS (`2013-04-01 00:00:00') ),

  PARTITION gMORES VALUES LESS THAN (MAXVALUE) );

  Which method should used to add a new g201305 partition to the table?

  A. ALTER TABLE games REORGANIZE PARTITION (gMORES) INTO g01305 VALUES LESS THAN (TO_DAYS (`2013-05-01 00:00:00') ), gMORES VALUES LESS THAN (MAXVALUE) );
  B. ALTER TABLE games ADD PARTITION g201350 VALUES LESS THAN (TO_DAYS (`2013-05-01 00:00:00') );
  C. ALTER TABLE games COALESCE PARTITION (gMORES) INTO g01305 VALUES LESS THAN (TO_DAYS (`2013-05-01 00:00:00') ), gMORES VALUES LESS THAN (MAXVALUE) );
  D. ALTER TABLE games SPLIT PARTITION (gMORES) INTO g201305 VALUES LESS THAN (TO_DAYS (`2013-05-01 00:00:00') ), gMORES VALUES LESS THAN (MAXVALUE) );
  E. ALTHER TABLE games DROP PATITION gMORES, ADD PARTITION g201305 VALUES LESS THAN (TO_DAYS (`2013-05-01 00:00:00') ), gMORES VALUES LESS THAN (MAXVALUE) );
  B. ALTER TABLE games ADD PARTITION g201350 VALUES LESS THAN (TO_DAYS (`2013-05-01 00:00:00') );

• Question 66:

  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';
  C. FLUSH PRIVILEGES;

• Question 67:

  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.
  E. InnoDB uses row-level or table-level locks depending on the number of rows affected.

• Question 68:

  Which three data components are needed for point-in-time recovery?

  A. The error log
  B. The backup log
  C. The general query log
  D. Binary logs
  E. The data backup
  F. Configuration files
  D. Binary logs
  E. The data backup
  F. Configuration files

• Question 69:

  Which three statements are characteristic of the MEMORY storage engine?

  A. Each table is represented on disk as an.frm file.
  B. Each table has a corresponding.MYI and .MYD file.
  C. It can support foreign keys.
  D. It cannot contain text or BLOB columns.
  E. Table contents are not saved if the server is restarted.
  F. It can support transactions
  A. Each table is represented on disk as an.frm file.
  D. It cannot contain text or BLOB columns.
  E. Table contents are not saved if the server is restarted.

• Question 70:

  What are four capabilities of the mysql client program?

  A. Creating and dropping databases
  B. Creating, dropping, and modifying tables and indexes
  C. Shutting down the server by using the SHUTDOWN command
  D. Creating and administering users
  E. Displaying replication status information
  F. Initiating a binary backup of the database by using the START BACKUP command
  B. Creating, dropping, and modifying tables and indexes
  D. Creating and administering users
  E. Displaying replication status information
  F. Initiating a binary backup of the database by using the START BACKUP command