DBA Interview Questions

1-Explain the security practices such as LDAP SSL SSO and certificate management

LDAP: Lightweight Directory Access Protocol
LDAP is a tool for extracting and editing data stored in Active Directory and other compatible directory service providers. Each user account in an AD has several attributes, such as the user’s full name and email address. Extracting this information in a usable format requires LDAP.
LDAP is used in Microsoft’s Active Directory, but can also be used in other tools such as Open LDAP, Red Hat Directory Servers and IBM Tivoli Directory Servers for example.

What is meant by SSO?
Single sign-on (SSO) is an authentication method that enables users to securely authenticate with multiple applications and websites by using just one set of credentials.

What is the difference between LDAP and SSO?
The difference is that LDAP is an application protocol that is used to crosscheck information on the server end. SSO, on the other hand, is a user authentication process, with the user providing access to multiple systems.

SSL (Secure Sockets Layer) and its successor, TLS (Transport Layer Security), are protocols for establishing authenticated and encrypted links between networked computers. Although the SSL protocol was deprecated with the release of TLS 1.0 in 1999, it is still common to refer to these related technologies as “SSL” or “SSL/TLS.”

What is an SSL certificate?
An SSL certificate (also known as a TLS or SSL/TLS certificate) is a digital document that binds the identity of a website to a cryptographic key pair consisting of a public key and a private key. The public key, included in the certificate, allows a web browser to initiate an encrypted communication session with a web server via the TLS and HTTPS protocols. The private key is kept secure on the server, and is used to digitally sign web pages and other documents (such as images and JavaScript files).

An SSL certificate also includes identifying information about a website, including its domain name and, optionally, identifying information about the site’s owner. If the web server’s SSL certificate is signed by a publicly trusted certificate authority (CA), like SSL.com, digitally signed content from the server will be trusted by end users’ web browsers and operating systems as authentic.

2-What is DATA LAKE ?

A data lake is a storage repository that holds a vast amount of raw data in its native format until it is needed for analytics applications. While a traditional data warehouse stores data in hierarchical dimensions and tables, a data lake uses a flat architecture to store data, primarily in files or object storage.

What is the difference between a data lake and a data warehouse?

A data lake is a vast pool of raw data, the purpose for which is not yet defined. A data warehouse is a repository for structured, filtered data that has already been processed for a specific purpose. The two types of data storage are often confused, but are much more different than they are alike.

3-What is Nagios and how it works?

Nagios is an open source monitoring system for computer systems. It was designed to run on the Linux operating system and can monitor devices running Linux, Windows and Unix operating systems (OSes). Nagios software runs periodic checks on critical parameters of application, network and server resources.

4-What is Dynatrace is used for?

Dynatrace enables monitoring of your entire infrastructure including your hosts, processes, and network. You can perform log monitoring and view information such as the total traffic of your network, the CPU usage of your hosts, the response time of your processes, and more.

5-How do you improve/optimize SQL queries?

  • Define business requirements first
  • SELECT fields instead of using SELECT *
  •  Avoid SELECT DISTINCT. By adding more fields, unduplicated records were returned without using SELECT DISTINCT. The database does not have to group any fields, and the number of records is accurate.
  • Create joins with INNER JOIN (not WHERE)
  • Use WHERE instead of HAVING to define filters. HAVING should only be used when filtering on an aggregated field.
  • Use wildcards at the end of a phrase only.
  • Use LIMIT to sample query results.
  • Run your query during off-peak hours to minimize the impact of your analytical queries .
  • Avoid Nested Queries & Views.
  • Use IN predicate while querying Indexed columns. While querying an indexed column, use an IN-list predicate instead of using mathematical operators such as β€˜=’ or logical operators such as AND/OR. The IN predicate can speed up your SQL queries as the query optimizer sorts the IN-list to match the sort sequence of INDEX, leading to faster results.
  • Do pre-staging: If you have queries and procedures that do joins with large tables, it is advisable to create a separate table with this its result. When you join tables ahead of time, the SQL queries that use their result work much faster.
  • Use temp tables: Similarly, if you are joining a large table to a small one, create a temp table that contains only data required for joining with the smaller table. You can do this be selecting data from large table, transferring it to a temp table and joining the small table with this temp table.
  • Avoid using OR in JOINS : JOINS are time consuming as your database has to examine each row for a match. If you also use OR condition in a JOIN, your database will take double the time to match records. As mentioned earlier, use IN operator instead.

6. How do you collect statistics on a table?

analyze table emp compute statistics

or

analyze table emp estimate statistics

7. What is the diff between computing and estimate?

If you use compute, The Full Table Scan will happen, if you use estimate just 10% of the table will be read

12. How do you delete statistics of an object?

table emp delete statistics

13. How do you collect statistics of a user/schema?

exec dbms_stats.gather_schema_stats(Scott)

14. How do you see the statistics of a table?

select num_rows,blocks,empty_blocks from dba_tables where tab_name=’emp’

15. What are chained rows?

These are rows, it spans in multiple blocks