Capgemini: What Steps Do You Follow to Reduce a Large PBIX File Size Without Losing Data or Visual Quality?

This is a very common question in Capgemini Power BI, BI Analyst, and Data Engineering interviews. Capgemini frequently works with enterprise clients where PBIX files often grow beyond 300–800MB due to heavy data loads, multiple tables, and complex DAX logic. The interviewer wants to know whether you understand professional optimization techniques instead of simple basic steps.

📌 Why PBIX File Size Becomes Large

A PBIX file grows mainly because of:

• Too many unnecessary columns in fact tables
• High-cardinality text fields (e.g., long descriptions, free-text comments)
• Large number of relationships and lookup tables
• Unoptimized Power Query transformations
• Importing entire history instead of required data
• Uncompressed data types (text columns stored inefficiently)

✅ Step-by-Step Professional Process to Reduce PBIX File Size

1. Remove Unnecessary Columns First

Capgemini emphasizes removing columns that are:

• Not used in visuals
• Not used in relationships
• Not part of any DAX logic
• Not required for business reporting

This alone can reduce file size by 30–40%.

2. Reduce High-Cardinality Columns

Columns with unique values (InvoiceID, GUIDs, long text) consume heavy memory. Replace them with:

• Integer surrogate keys
• Shorter text fields
• Reference dimensions

3. Push Transformations to SQL Instead of Power Query

Power BI compresses data less efficiently when too many transformations happen in Power Query. Creating a SQL view with pre-cleaned data reduces file size significantly.

4. Use Star Schema Instead of Snowflake

A proper star schema:

• Reduces joins
• Improves compression
• Minimizes model size
• Improves refresh speed

5. Disable Auto Date/Time

This hidden feature secretly creates dozens of internal date tables for every date column. Disabling it can reduce file size by 5–10% instantly.

6. Aggregate Fact Tables

Instead of importing granular transactional data, create:

• Daily aggregated tables
• Monthly summary tables
• Quarter-level views

Capgemini often uses aggregations for clients with millions of rows.

7. Remove Unused Measures and Columns

During development, many developers create trial measures or test columns. These should be removed before publishing the final version.

8. Optimize Data Types

Changing:

• Text → Whole Number
• Decimal → Fixed Decimal
• Text → Boolean

can dramatically reduce file size because numeric columns compress far better than text.

💡 Clean Interview-Friendly Answer

“To reduce PBIX file size without losing data, I start by removing unused columns and converting high-cardinality text fields into numeric surrogate keys. I push heavy transformations into SQL views and restructure the model into a star schema. I disable auto date/time, remove unnecessary measures, and optimize data types for better compression. If required, I also create aggregated tables to reduce row-level detail and improve performance. These techniques usually reduce PBIX size by 40–70% while keeping the visuals intact.”

💬 Why Capgemini Asks This

Capgemini deals with global clients where datasets often exceed millions of records. They want analysts and BI developers who understand professional modeling practices, data compression techniques, and enterprise-level optimization strategies that ensure both small file size and high-performance reporting.

Infosys: How Do You Design a Star Schema in Power BI When the Source Data Is Fully Normalized in an OLTP System?

This is one of the most frequently asked questions in Infosys Power BI, Data Analyst, and BI Developer interviews. The interviewer wants to evaluate your understanding of data modeling, dimensional design, and how well you convert transactional source systems into analytical structures suitable for reporting.

✅ What the Interviewer Wants to Hear

Infosys works heavily with enterprise-level clients where the backend systems are usually OLTP databases such as SQL Server, Oracle, SAP, or MySQL. These systems follow high normalization and are not optimized for reporting. Your job in Power BI is to convert this normalized data into a clean, scalable, and fast-performing star schema.

📌 Step-by-Step Process for Designing a Star Schema

1. Understand the Business Process First

Before designing any schema, identify the primary business process such as:

• Sales order processing
• Inventory movement
• Customer transactions
• Financial reporting

Clearly define what metrics or KPIs the report will measure. This forms the foundation for building the fact table.

2. Identify the Fact Table

A fact table typically contains:

• Transaction-level data
• Foreign keys to dimension tables
• Numeric, aggregatable measures (Amount, Quantity, Profit, Time Spent, etc.)

Example fact table: FactSales

FactSales
-----------
SalesID
OrderDate
CustomerID
ProductID
Quantity
SalesAmount
Discount
  

3. Build Dimension Tables

Unlike the normalized OLTP structure, dimensions in a star schema must be:

• Denormalized
• Descriptive
• Human-readable
• Slowly changing where needed (SCD Type 1 or 2)

Common dimensions include:

• DimCustomer
• DimProduct
• DimDate
• DimGeography
• DimEmployee

4. Flatten Normalized Tables

An OLTP system may have tables like:

Customer
CustomerAddress
CustomerContact
CustomerSegment
  

These should be joined into a single dimension table in Power Query or SQL view:

DimCustomer
-----------
CustomerID
CustomerName
Email
Phone
City
State
Country
Segment
  

5. Create Relationships (One-to-Many)

In the star schema:

• Dimensions sit on the “one” side
• Fact tables sit on the “many” side
• Relationships are single-direction (from dimension → fact)

This ensures better performance, predictable DAX behavior, and faster refresh.

6. Optimize the Data Model

• Remove unused columns
• Use surrogate keys instead of natural keys
• Enable date table with proper hierarchy
• Disable auto date/time
• Ensure data types are correct (very important for Infosys interviews)

💡 Simple Interview-Friendly Answer

“When the source data is fully normalized in an OLTP system, I first understand the business process and identify the fact table that holds the core transactions. Next, I build denormalized dimension tables for customers, products, dates, and other descriptive attributes. I flatten the normalized tables using Power Query or SQL views and ensure the relationships follow a proper one-to-many structure with single-direction filters. Finally, I optimize the model by removing unnecessary columns, defining correct data types, and ensuring a clean star schema for fast query performance.”

💬 Why Infosys Asks This Question

Infosys works with global clients where reporting systems must handle millions of rows and strict performance requirements. They want to validate whether you can design efficient, stable, and scalable data models that work in enterprise analytics environments.

Deloitte: Write an SQL Query to Find the Nth Highest Salary Without Using LIMIT, TOP, or OFFSET

This is a very popular question asked in Deloitte SQL and Data Engineer interviews. It tests a candidate’s ability to use ranking functions, subqueries, and advanced SQL logic to solve problems without relying on simple shortcuts.

✅ Approach 1: Using DENSE_RANK()

You can use a window function to assign ranks to salaries and then filter by the required N.

SELECT salary
FROM (
    SELECT 
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) AS ranked_salaries
WHERE rnk = :N;
  

Replace :N with the value of the Nth highest salary you want. For example, to get the 3rd highest salary:

WHERE rnk = 3;
  

✅ Approach 2: Using a Subquery

This method returns the salary for the Nth highest value using a distinct subquery filter.

SELECT DISTINCT salary
FROM employees e1
WHERE :N - 1 = (
    SELECT COUNT(DISTINCT salary)
    FROM employees e2
    WHERE e2.salary > e1.salary
);
  

📌 Explanation in Simple Words

“To find the Nth highest salary, you cannot rely on LIMIT, TOP, or OFFSET in many interviews. Instead, I use a window function such as DENSE_RANK() to rank salaries in descending order and select the row matching the required rank. Alternatively, I can use a correlated subquery that counts how many unique salaries are greater than the current one. When this count equals N−1, that salary is the Nth highest.”

💬 Why Deloitte Asks This

Deloitte expects candidates to be comfortable with window functions, correlated subqueries, filtering logic, and ranking mechanisms. These skills are essential for solving real-world data problems involving complex transformations and analytical queries.

Accenture: Explain the Difference Between CALCULATE and CALCULATETABLE in DAX With an Example

This question is one of the most commonly asked in Accenture Power BI and Data Analyst interviews. The interviewer uses it to test how well you understand evaluation context, filter propagation, and the difference between row context and filter context in real reporting scenarios.

✅ Clear and Simple Explanation

1. What CALCULATE Does

CALCULATE() returns a single scalar value. It modifies the filter context and then evaluates the expression under the new conditions.

• Mostly used for measures
• Outputs one aggregated value
• Best for KPIs like Sales YTD, Profit MTD, etc.

Total Sales = CALCULATE(SUM(Sales))
  

2. What CALCULATETABLE Does

CALCULATETABLE() returns an entire table. You use it when you need a filtered table for iterating, ranking, or advanced calculations.

• Returns a table instead of a single number
• Ideal for filtering rows before using functions like SUMX or RANKX
• Useful for complex data modeling logic

Filtered Sales =
CALCULATETABLE(
    Sales,
    Sales = 2024
)
  

📌 Real Project Example (Accenture-Level Quality)

Scenario:

You want the total sales of the latest year. The latest year keeps changing as new data comes in, so the calculation must be dynamic.

Latest Year Sales =
CALCULATE(
    SUM(Sales),
    Sales = MAX(Sales)
)
  

If you need to extract a filtered table of only the latest year for further calculations, you use:

Latest Year Table =
CALCULATETABLE(
    Sales,
    Sales = MAX(Sales)
)
  

💡 Simple Interview-Friendly Answer

“CALCULATE returns a single value after modifying the filter context, while CALCULATETABLE returns a table after applying filters. I use CALCULATE for measures like Total Sales YTD, and CALCULATETABLE when I need a filtered dataset inside functions such as SUMX, RANKX, or for complex business logic.”

💬 Why Accenture Asks This

Accenture projects involve enterprise models with large datasets. Engineers must understand context transition, filter manipulation, and table vs. scalar output to write efficient measures and optimize report performance.

This question is frequently asked in TCS Power BI interviews, especially for Data Analyst and BI Developer roles. The interviewer wants to test your understanding of data modeling, DAX performance, and optimization techniques in real-world enterprise scenarios.

✅ Step-by-Step Answer

1. Check the Data Model Structure

• Use Star Schema instead of Snowflake.
• Keep fact tables large and dimension tables small.
• Remove unnecessary relationships and avoid many-to-many joins.

2. Reduce Columns and Rows Loaded into Power BI

• Load only required columns using custom SQL views.
• Apply filters at the source (e.g., last 3 years data only).
• Avoid loading high-cardinality text columns.

3. Optimize DAX Measures

• Replace CALCULATE + FILTER combos with optimized versions.
• Avoid row-by-row functions such as EARLIER and iterators unless necessary.
• Use SUMX only on small tables.

4. Improve Query Performance

• Enable Query Folding in Power Query.
• Check the View Native Query option is active.
• Push transformations to SQL instead of Power Query.

5. Manage Visual-Level Performance

• Reduce number of visuals per page (ideal: under 12).
• Use Aggregations for large fact tables.
• Disable auto date/time in Power BI Desktop.

💡 Good Sample Short Answer for Interview

“To optimize a slow Power BI report connected to a 20M+ row table, I first review the data model to ensure it follows a proper star schema. I reduce unnecessary columns and push filters to the source database to minimize rows loaded. I also optimize DAX by replacing expensive iterators with efficient functions. Next, I ensure query folding is maintained in Power Query so heavy transformations happen at the SQL side. Finally, I optimize visuals by limiting complex charts and enabling aggregations for large datasets.”

📌 Why TCS Asks This Question

TCS handles large enterprise datasets, and they expect candidates to understand data modeling, performance tuning, and scalable BI design.

These questions are designed to check your ability to write optimized PL/SQL code, use collections, manage exceptions, and handle large data volumes efficiently.

If you’re preparing for mid-level or senior Oracle roles, this list of advanced PL/SQL interview questions with examples will help you ace both coding and technical discussions.

🔹 Top Advanced PL/SQL Coding Interview Questions (2025 Edition)

1️⃣ What is the difference between an Explicit and an Implicit Cursor?

Answer:

• Implicit Cursor: Automatically created by Oracle for single SQL statements like INSERT, UPDATE, DELETE, SELECT INTO.

• Explicit Cursor: Declared by the developer for queries returning multiple rows.
  Example:

✅ Common in Wipro and Accenture PL/SQL coding rounds.

2️⃣ What is Bulk Collect in PL/SQL and why is it used?

Used for performance improvement when fetching multiple rows.
Instead of fetching one row at a time, BULK COLLECT retrieves data in batches into PL/SQL collections.
Example:

✅ Accenture often asks this for large data-load projects.

3️⃣ What is the difference between FORALL and Bulk Collect?

📌 Infosys interviewers focus on this for optimization logic.

4️⃣ Explain Ref Cursors and their types.

Ref cursors allow passing cursor results between subprograms dynamically.

✅ Common in Infosys advanced coding tests.

5️⃣ How can you handle multiple exceptions in PL/SQL efficiently?

Use named and generic exceptions with WHEN clauses.
Example:

📌 Accenture often gives a block with errors and asks you to correct it.

6️⃣ What are PL/SQL Collections? Explain their types.

• Associative Arrays (INDEX BY tables): Key-value pairs.

• Nested Tables: Similar to arrays, dynamic in size.

• VARRAYs: Fixed-size arrays.

Example (Nested Table):

✅ Infosys & Wipro focus on associative arrays and nested tables.

7️⃣ What is a Cursor FOR Loop and how is it different from explicit cursor handling?

Cursor FOR Loop simplifies cursor management — no need to explicitly open, fetch, or close.

📌 Asked in Accenture intermediate rounds.

8️⃣ Real-Time Scenario: Bulk Update with Error Logging

Question: Update employee bonuses in bulk and log failed updates.

✅ Common in Wipro PL/SQL automation projects.

💡 Pro Tip:

When explaining PL/SQL logic in interviews, add context:

“We implemented bulk updates in a payroll module where performance improved by 60% after using FORALL with SAVE EXCEPTIONS.”

This gives a real-world impact — which impresses senior interviewers instantly.

🧩 Conclusion

These Advanced PL/SQL Coding Interview Questions (2025 Edition) from Infosys, Accenture & Wipro are drawn from actual project-based technical rounds.

Mastering these topics — bulk operations, ref cursors, autonomous transactions, and optimization techniques — will help you stand out as a top-performing Oracle developer.

Practice these scenarios regularly on Oracle LiveSQL or SQL Developer to strengthen your command and speed in PL/SQL interviews.

This post brings you practical interview questions directly from developer rounds in 2025, including project-based examples and error-handling logic you can use in production environments.

🔹 Top PL/SQL Triggers, Packages & Exception Handling Questions (2025 Edition)

1️⃣ What is a Trigger in PL/SQL? When do you use it?

Answer:
A trigger is a stored PL/SQL block that automatically executes in response to a database event such as INSERT, UPDATE, or DELETE.
Used for:

• Auditing data changes

• Enforcing business rules

• Automating logging and calculations

Example (Audit Trigger):

✅ Frequently asked in Capgemini production support interviews.

2️⃣ What are the different types of Triggers in Oracle?

📌 TCS often asks for “real use cases” of compound triggers.

3️⃣ Real-Time Scenario: Prevent Negative Salary Updates

Question: Create a trigger that prevents updating salary to a negative value.

✅ Common business rule scenario used in HCL ERP projects.

4️⃣ What is a Package in PL/SQL? Why use it?

A package is a collection of logically related PL/SQL objects — such as procedures, functions, variables, and cursors — grouped together for reusability and modularity.

Example:

✅ Asked in TCS project design interviews.

5️⃣ What is the difference between a Procedure and a Function?

6️⃣ What is Exception Handling in PL/SQL?

Exception handling lets you gracefully manage runtime errors.
Structure:

✅ Capgemini often gives a code snippet and asks you to identify the correct exception block.

7️⃣ Real-Time Scenario: Log Errors in an Exception Table

Question: Write code to log errors instead of stopping execution.

📌 Used widely in HCL data migration projects.

8️⃣ What is RAISE_APPLICATION_ERROR and why is it used?

Used to define custom errors in PL/SQL.
Example:

✅ TCS often asks to differentiate between system exceptions and user-defined exceptions.

💡 Pro Tip:

When explaining PL/SQL logic in interviews, follow this structure:

“I’ll define the business rule → trigger event → package structure → exception handling → optimization.”

This stepwise explanation shows real-world thinking — exactly what TCS, HCL & Capgemini look for.

🧩 Conclusion

These PL/SQL triggers, packages & exception handling questions (2025 Edition) from TCS, HCL & Capgemini are based on actual developer rounds.
They test your ability to write efficient, modular, and error-proof code — crucial in enterprise systems.

Focus on explaining logic flow and performance improvements instead of just syntax — that’s what differentiates a senior Oracle developer from a beginner.

ERD (Entity Relationship Diagram) questions test your ability to design efficient database schemas, define relationships, and convert real-world business processes into structured database models.

These real-time scenario-based ERD questions are frequently asked in Infosys, Accenture, Capgemini, and Deloitte interviews — ideal for data analysts, BI developers, and DB designers.

🔹 Top ERD (Entity Relationship Diagram) Interview Questions (2025 Edition)

1️⃣ What is an ERD and why is it important?

Answer:
An Entity Relationship Diagram (ERD) is a visual representation of entities, their attributes, and the relationships between them.
It’s used to design the logical structure of a database before actual implementation.
✅ Helps ensure data integrity, reduce redundancy, and improve scalability.

2️⃣ What are the main components of an ERD?

• Entity: A real-world object (e.g., Customer, Order).

• Attribute: Property of an entity (e.g., Name, ID, Price).

• Relationship: Association between entities (e.g., Customer → places → Order).

• Primary Key (PK): Unique identifier of each entity.

• Foreign Key (FK): References PK from another table to form a relationship.

3️⃣ Explain the different types of relationships in ERD.

📌 Many-to-many relationships are usually implemented through a junction (bridge) table.

4️⃣ How do you convert an ERD into database tables?

1. Each entity becomes a table.

2. Each attribute becomes a column.

3. Primary keys and foreign keys are added to define relationships.

Example:
Entities:

• Customer (Customer_ID, Name, Email)

• Order (Order_ID, Order_Date, Customer_ID)

Relationship → One-to-Many

5️⃣ What is the difference between logical and physical data models?

✅ Accenture often tests this difference to check data modeling clarity.

6️⃣ What are normalization and denormalization?

• Normalization:
  Process of organizing data to reduce redundancy.
  Common forms:

  • 1NF: Atomic columns

  • 2NF: Remove partial dependency

  • 3NF: Remove transitive dependency

• Denormalization:
  Combining tables to improve query performance (used in reporting and data warehousing).

📌 Infosys interviewers often ask: “Why would you denormalize a data model?”

7️⃣ How do you represent optional and mandatory relationships in ERD?

• Mandatory Relationship: Shown with a solid line — e.g., Each Order must have a Customer.

• Optional Relationship: Shown with a dashed line — e.g., Each Customer may have a Discount record.

8️⃣ What is cardinality in ERD?

Cardinality defines the number of instances one entity can have in relation to another.
Example:

• A customer can place many orders → (1:N).

• An order belongs to one customer → (N:1).

✅ Cardinality determines foreign key placement in your database.

9️⃣ Real-Time Scenario: Design an ERD for an Online Learning Platform

Entities:

• Student, Course, Instructor, Enrollment
  Relationships:

• Student ↔ Enrollment (1:N)

• Course ↔ Enrollment (1:N)

• Instructor ↔ Course (1:N)

Junction Table: Enrollment(Student_ID, Course_ID, Enroll_Date)

✅ Common real-world scenario asked in Deloitte & Capgemini interviews.

💡 Pro Tip:

During interviews, when asked to “draw an ERD,” explain your approach before design:

“I start by identifying entities from the business use case, then define relationships, keys, and finally convert them into normalized database tables.”

This structured explanation shows professional thinking — and earns extra points.

🧩 Conclusion

These ERD (Entity Relationship Diagram) interview questions (2025 Edition) help you master database design fundamentals, relationships, and real-world modeling scenarios.

Companies like Infosys, Accenture, and Capgemini assess how well you can translate a business process into a clear, scalable data model — not just how you draw diagrams.

Keep practicing with sample case studies (e.g., Hospital Management, Banking System, E-commerce) to strengthen your ERD and normalization skills.

These 2025 interview questions cover the practical side of data warehouse design, ETL workflows, fact/dimension tables, and star-schema implementations commonly used in enterprise analytics projects.

🔹 Top Data Warehousing Real-Time Interview Questions (2025 Edition)

1️⃣ What is a Data Warehouse and how is it different from an OLTP system?

Answer:
A Data Warehouse (DW) is a centralized system for storing historical and analytical data, optimized for reporting.

✅ Common starting question in TCS interviews.

2️⃣ Explain the difference between Star Schema and Snowflake Schema.

• Star Schema:

  • One fact table linked to multiple dimension tables.

  • Simpler, faster queries.

• Snowflake Schema:

  • Dimensions normalized into sub-dimensions.

  • Reduces redundancy, but increases joins.
    ✅ Capgemini often asks which schema you used in your project.

3️⃣ What are Fact and Dimension Tables? Give examples.

• Fact Table: Stores quantitative data (e.g., sales amount, quantity).

• Dimension Table: Stores descriptive attributes (e.g., customer, product, region).
  Example:

• Fact_Sales: Date_Key, Product_Key, Sales_Amount

• Dim_Product: Product_Key, Category, Brand

4️⃣ What are Slowly Changing Dimensions (SCD)?

SCDs handle historical changes in dimension data.
Types:

• Type 1: Overwrite old data (no history)

• Type 2: Add new record (keeps full history)

• Type 3: Add new column (partial history)

Example (Type 2):

✅ Real project question from Infosys DW team.

5️⃣ What is a Data Mart and how is it related to a Data Warehouse?

A Data Mart is a subset of a Data Warehouse — focused on a specific business function.
Example:

• Finance Data Mart

• Sales Data Mart
  ✅ TCS often checks if you understand data segregation for analytics.

6️⃣ How do you load data incrementally in a Data Warehouse?

Incremental load = only new or changed data since last refresh.
Techniques:

• Using Last Updated Date columns

• Change Data Capture (CDC)

• Comparing hash keys
  Example:

7️⃣ What is ETL and what tools are commonly used?

ETL: Extract → Transform → Load.
Popular tools:

• Informatica PowerCenter

• Talend

• SSIS

• Oracle Data Integrator (ODI)

• Azure Data Factory / AWS Glue

✅ Capgemini focuses on data orchestration and automation concepts.

8️⃣ What is Data Staging and why is it used?

The staging area temporarily stores raw extracted data before transformation.
Advantages:

• Reduces load on source systems.

• Supports incremental data validation.

• Simplifies error correction during ETL.

9️⃣ What are Aggregate Tables and why are they used?

Aggregate tables store pre-calculated summaries (e.g., monthly sales totals).
They:

• Improve query performance.

• Reduce computation on live queries.
  Example:

✅ Infosys uses this example in Power BI + Oracle DW interviews.

🔟 How do you ensure data quality in ETL processes?

• Validate source vs. target counts.

• Check for duplicates and nulls.

• Maintain audit logs.

• Use error handling tables for rejected records.

📌 Practical question in real-time DW project interviews.

💡 Pro Tip:

When explaining a project scenario, always describe the data flow:

“We extracted sales data from SAP, transformed it in Informatica, and loaded it into an Oracle Data Warehouse for Power BI dashboards.”

It adds credibility and aligns with real enterprise environments.

🧩 Conclusion

These Data Warehousing real-time interview questions (2025 Edition) reflect what top companies like Infosys, TCS, and Capgemini look for in ETL and BI professionals.

Focus on understanding data modeling concepts, SCD types, and incremental loading techniques — they form the foundation of every data warehouse project.

Consistent practice with ETL tools and SQL-based transformations will help you confidently answer any scenario-based question.

This 2025 guide compiles real Oracle performance tuning and optimization questions, helping you understand how to make SQL queries faster, reduce execution time, and optimize resource utilization in real-world scenarios.

These questions are ideal for Oracle developers, DBAs, and performance engineers preparing for production-level interviews.

🔹 Top Oracle Performance Tuning & Query Optimization Questions (2025 Edition)

1️⃣ What are the key steps in Oracle SQL performance tuning?

Answer:

1. Identify slow-running SQL using AWR/ASH reports.

2. Analyze the EXPLAIN PLAN to check table access paths.

3. Tune the query using indexes, hints, or query rewrites.

4. Monitor execution cost and I/O statistics.
   ✅ This structured answer is often expected in Infosys DBA interviews.

2️⃣ How do you view the execution plan of a SQL query in Oracle?

Use the EXPLAIN PLAN command:

📌 Helps identify full table scans, index scans, and join methods used by the optimizer.

3️⃣ What is the difference between a full table scan and an index scan?

✅ Common question in Accenture SQL optimization rounds.

4️⃣ What are Oracle optimizer modes?

Oracle’s Cost-Based Optimizer (CBO) chooses the best query plan based on statistics.
Modes include:

• ALL_ROWS: Optimizes for throughput (best for large queries).

• FIRST_ROWS: Optimizes for response time (best for OLTP).
  Example:

5️⃣ How can you detect and resolve high CPU usage in queries?

• Identify high-CPU queries using:

   

  SELECT sql_id, cpu_time, executions FROM v$sql ORDER BY cpu_time DESC;

• Tune inefficient queries (missing indexes, complex joins).

• Update optimizer statistics using:

   

  EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

• Break large transactions into smaller batches.

6️⃣ What are bind variables and why are they important?

Bind variables improve performance by allowing query reuse and preventing hard parses.
Example:

✅ A favorite question in Accenture Oracle interview rounds.

7️⃣ How do you identify and fix missing indexes?

• Use EXPLAIN PLAN or AUTOTRACE to check access paths.

• Look for “TABLE ACCESS FULL” on large tables.

• Create appropriate indexes:

   

  CREATE INDEX idx_emp_dept ON employees(department_id);

• Verify improvement via execution cost reduction.

8️⃣ What is a SQL hint and when should it be used?

Hints influence the optimizer’s behavior. Example:

⚠️ Use hints only when the optimizer’s plan is inefficient. Overuse can make SQL non-portable.

9️⃣ What is partitioning and how does it improve performance?

Partitioning splits large tables into smaller, manageable segments.
Types:

• Range Partitioning — based on date or numeric range

• Hash Partitioning — distributes rows evenly

• List Partitioning — based on discrete values

✅ Reduces scan time and improves query parallelism.

Example:

🔟 What are AWR and ASH reports used for?

• AWR (Automatic Workload Repository): Captures database performance statistics.

• ASH (Active Session History): Tracks active sessions in real-time.
  They help identify high-load SQLs, wait events, and resource bottlenecks.

📌 Example:

Generates a detailed report for tuning analysis.

💡 Pro Tips for Oracle Query Optimization:

• Keep statistics up to date using DBMS_STATS.

• Use composite indexes for multi-column filtering.

• Prefer set-based operations over row-by-row loops.

• Use materialized views for heavy aggregation.

• Monitor buffer cache hit ratio and shared pool usage.

🧩 Conclusion

These Oracle performance tuning and optimization questions (2025 Edition) asked in Accenture & Infosys interviews focus on real-world diagnostic and tuning skills.

Mastering execution plans, indexing strategies, and query rewrite logic will make you stand out in both developer and DBA interviews.
Keep practicing on Oracle LiveSQL or a local test environment to sharpen your optimization skills.