nerdexam
Oracle

1Z0-888 · Question #58

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…

The correct answer is B. Session variables are not persistent across server restarts. C. The query benefited from sort_buffer_size increase. E. The query benefited from tmp_table_size increase. B is correct because SET sort_buffer_size=2000000 - without the GLOBAL keyword - sets a session-level variable, meaning it only applies to the DBA's current connection and disappears when that session ends or the server restarts. C and E are correct because the DBA's own test…

Installation and Configuration

Question

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? (Choose three.)

Options

  • AGlobal variables are not persistent across server restarts.
  • BSession variables are not persistent across server restarts.
  • CThe query benefited from sort_buffer_size increase.
  • Dsort_buffer_size should match tmp_table_size to be optimal.
  • EThe query benefited from tmp_table_size increase.
  • FThe query benefited from sort_buffer_size and tmp_table_size increases.

How the community answered

(34 responses)
  • A
    6% (2)
  • B
    82% (28)
  • D
    3% (1)
  • F
    9% (3)

Explanation

B is correct because SET sort_buffer_size=2000000 - without the GLOBAL keyword - sets a session-level variable, meaning it only applies to the DBA's current connection and disappears when that session ends or the server restarts. C and E are correct because the DBA's own test queries ran faster due to the larger sort_buffer_size (C), while the application's queries on other connections improved because SET GLOBAL tmp_table_size applied to all new sessions (E) - but crucially, no single query received both benefits simultaneously, which is why F is wrong.

Why the distractors fail: A is incorrect as an absolute statement - global variables can persist across restarts if set in the config file or via SET PERSIST (MySQL 8+); the real problem is the lack of either. D has no basis in MySQL's optimizer design; sort_buffer_size and tmp_table_size are independent settings serving different operations. F fails because the application sessions never saw the sort_buffer_size change (it was session-only on the DBA's connection), so no single query benefited from both.

Memory tip: Think "SESSION = Self only, Session ends it dies; GLOBAL = everyone gets it, but not guaranteed to survive restart without config." The missing GLOBAL on sort_buffer_size is the trap - it looked like a fix in the DBA's session but never reached the application.

Topics

#Variable Persistence#Query Buffer Tuning#Configuration Management#Performance Debugging

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 1Z0-888 Practice