GANESH RAVELLA

Oracle Database Developer

Phone: 615-***-****

E-mail: ac7x3e@r.postjobfree.com

EXPERIENCE SUMMARY

** ***** ** ********** ** the field of information technology, proven ability to leverage technology to improve business processes, with special expertise in requirements analysis, database development, debugging, problem solving and user support. Proficient in Database/Infrastructure support, monitoring and performance tuning, data modeling, development of stored procedures, packages, triggers etc. Self-motivated, resourceful and result oriented Team Player. Having good communication and interpersonal skills, a clear understanding of business rules and the ability to work well as a part of project team.

CORE COMPETENCIES

Oracle SQL & PL/SQL:

Proficient in writing SQL queries in Oracle proprietary standard.

Very strong in writing simple and complex SQL queries using DML, DDL, Group functions, grouping sets (Cube, Rollup), Grouping functions, Analytical functions, Connect by for hierarchical queries, Partition by clause towards Reports and Application development.

Experience in data extraction using complex SQL queries using various joins.

Experience in Generating Dynamic SQL and running them at run time.

Written complex SQLs using joins, Nested and correlated sub queries.

Worked extensively on PARTITIONS to enhance performance and archiving of a DW.

Used MERGE statement to conditionally insert or update data depending on its presence.

Extensively experience in designing and developing pl/sql subprograms.

Implemented packages to achieve modularity, easier application design, information hiding, and better performance.

Developed PL/SQL procedures for business rule validations, applying transformations, interfaces to UI screens, and for processing data.

Explored Bulk Loading and Batch Processing features of PL/SQL.

Extensive experience in utilizing Collections to boost performance in PL SQL block.

Improved performance of PL SQL program units by using Bulk collect and Bulk bind (for All) features with collections instead of using cursors, FOR loops.

Proficient in using Cursors, Ref-Cursors, Table Functions, PIPELINED Function, Functions, SQL Types and Sequences.

Experience in Partition Exchange to exchange a partition of a table with another non-partitioned table

Build and maintain SQL scripts, indexes, and complex queries for data analysis and extraction.

Sound knowledge and experience in developing Triggers on various database events.

Effectively made use of Bulk Collect, Multi Table inserts, Merge, PL/SQL tables & Nested tables.

Made use some of the oracle standard packages such as DBMS_OUTPUT, DBMS_ALERT, DBMS_MVIEW, DBMS_DDL, UTL_FILE etc.

Designed and developed the Extraction, Transformation, and Loading (ETL) program to move data from source to target, mapping source data attributes to target data attributes.

Experience in Bulk Data loading using Oracle utilities namely SQL Loader, External Tables and Oracle utilities

Experience in application development like Bulk Collect, For all, Dynamic SQL, Autonomous Transactions, Global Temporary Tables.

Query Optimization/Performance Tuning

Proficient in Query optimization using cost-based optimization techniques using explain plan.

Proficient in tuning PL/SQL programs using DBMS_PROFILER and TKPROF, Auto trace & SQL Trace.

Proficient in creating and managing Indexing, optimizer Hints, Bulk Binds, Bulk Collect, Creation of global temporary tables and table partition.

Worked on performance tuning of many applications. The most successful project decreased run time of the SQL package for application with complex data extraction from multiple large tables from 30 minutes to 10 minutes.

MS SQL Server

Expertise in Performance tuning, Optimization, Data integrity and Statistics by using Spotlight.

Created Indexes (Clustered & non-Clustered) for improvising the performance and fast retrieval of the data.

Used the SentryOne Plan Explorer to view Execution Plan and help tune my SQL Server queries

Experience in DynaTrace Software - Application Performance Monitoring to trace timing and code-level context for all transactions end-to-end, across all tiers, from user clicks to back-end database queries, and back.

Created Data Load Package in SQL Server Integration Services (SSIS) to pull data from one SQL Server

/DB2 table and push it to another SQL Server/DB2 table.

Scripting:

Proficient in automating administration tasks and applying data patches using, SQLPLUS scripts and shell scripts.

Migrated the UNIX shell script to Oracle pl/sql procedures.

Others:

Effective use of agile development process using SCRUM methodologies by daily stand-up meeting, maintain the project dashboard using TFS.

Good experience with waterfall Methodology.

Experience with GitHub, Pvcs, Tortoise CVS, Jira, SalesForce and ServiceNow

Documented code in accordance with company policies, company standards and industry best practices.

Developed Test cases for Unit testing and user interface testing.

Performed unit and integration testing for the front-end application.

Areas of Expertise:

SDLC Methodologies

Requirement Analysis

Design and Development

Maintenance and Bug Fixes

Offshore Coordination

Production Follow-up

Peer Code Review

Create multiple test cases

Unit test

Databases

Oracle 11g, 10g, 9i, SQL Server 2014, DB2,AS/400, Mainframe and Sybase

Programming languages

Oracle PL/SQL, JAVA and UNIX shell.

Web Technologies

Java Script, XML and HTML/HTML5.

Operating systems

Win 7,8 and 10, UNIX (Linux and UNIX) and Mac Os.

Development Tools

TOAD, SQL Developer, BMC SQL Developer, SQL*loader, SQL*plus, Putty,

Oracle Enterprise Manager (OEM), Eclipse, Microsoft SQL Server Management Studio, SQL Navigator, AutoSys, Erwin, Informatica Power Center and Talend.

Other Tools

GitHub, ServiceNow, Jira, SalesForce, CVS, subversion, PVCS and VSS

Performance Tuning

DBMS_PROFILER and TKPROF, Auto trace & SQL Trace. SentryOne, Spotlight, SQL Server Profiler and DynaTrace

Technical/Tools Skills

Professional Experience

Client: J.B. Hunt Transport, Inc. 615 J B Hunt Corporate Dr, Lowell, AR 72745

Duration: Feb 12th, 2018 to Till Date

Application Name: Shared Drive

Role: Sr Oracle PL/SQL Developer/ Database Performance DBA

J.B. Hunt has four business segments (Intermodal, Dedicated Contract Services, Truckload, and Integrated Capacity Solutions) allow us to provide customized freight solutions for customers big and small. J.B. Hunt provide safe and reliable transportation services throughout the United States, Canada and Mexico. And provide safe and reliable transportation services throughout the United States, Canada and Mexico.

•Design, development and maintenance of database system in Oracle, Microsoft SQL Server and IBM DB2.

•Database indexing, SQL query optimization and performance tuning

•Ensure general database best practices are followed by application developers

•Coding, testing, validating and tuning SQL changes

•Design and implement indexes to improve database throughput

•Monitor database activity and identify any potential performance issues using Quest Spotlight and DynaTrace

•I tune the queries, If the nightly/weekly/Monthly jobs are failing due to running more than max run time.

•And DBA team generates the AWR reports and based on these reports we tune the queries that taking too long time.

•We identified bad queries which generated by hibernate, So I am writing the new complex sql queries/ Rewrite the queries as part of tuning.

•Understand the enterprise data model stored in ERWIN.

•Create SQL stored procedures, triggers, tables, user defined functions, views, and indexes

•Provide technical support for database applications

•Data analysis on database

•Data migration using Microsoft SSIS, export/import, etc

•Developing product modules per specification

Environment: Oracle 11g, SQL, PL/SQL, HP QC, Java/J2EE, SAP, Informatica, PVCS Tracker, PVCS Version Manager, JIRA, TOAD, PL/SQL Developer tool, BMC tool and Talend Job.

Client: Tractor Supply Company, Brentwood, TN

Duration: January 2016 – February 2018

Role: Sr Oracle PL/SQL Developer

Project: BOPIS (Buy Online Pickup in Store)

The new Architecture will involve adding messages to the store to notify them of BOPIS transactions. Once a BOPIS order has been placed online, the store needs to be notified of the order to allow that item to be picked and removed from inventory until picked up. These messages will pop up on the stores system to alert them of the order information. Store Pickup lets you buy items online and pick them up in a store. You avoid shipping and scheduled delivery charges and you may be able to get your order faster.

With the new process, when the store does the 5pm closing process the store will need to be balanced. Once balanced this information is passed through the Fin Store for the financial data. At that time the decision will need to be made whether the transaction was an eCommerce or BOPIS order.

Responsibilities:

Write the complex quires to find out and fix the various issues on behalf of the store systems issues like Store Balancing, Detail Errors, Terminal Error, Store/Xcenter/Backroom not closed.

Used DDL and DML to be writing triggers, stored procedures to handle the bopis orders.

Old packages are enhanced/modified as and when new requirements come across.

Implementation of new packages as per requirements.

Created new Procedures, Functions, Triggers, Materialized Views, Packages, Simple, Ref & Traditional Cursors, Dynamic SQL, Table functions as part of Project/Application requirements.

Prepare the unit test cases for all new development objects.

Performed ad set, daily price changes, cleanliness of store, and open box inventory.

Effectively managed items of approximately 60,000 skus to prevent stock outages while decreasing obsolete inventory to a value of under $1M.

Worked in working on Agile process and tools area (Code review, unit test automation, Environment, Service, Incident and Change Management).

Create the new Functional and Technical design documents.

Worked with Remedy or other ticketing software’s (ServiceNow, Salesforce and Jira).

Reviewing requirements in the JIRA and Confluence for production issues and provides time estimates for task completion.

Escalates the issues/Requests through Gateway/ServiceNow software tools.

Environment: Oracle 11g, SQL, PL/SQL, SQL*plus, SQL*Loader, Java/J2EE, SAP, Informatica, PVCS Tracker, PVCS Version Manager, JIRA, TOAD, PL/SQL Developer tool, BMC tool and Talend Job.

Client: TracFone Wireless, Miami, FL

Duration: Aug 28th, 2015 – January 2016

Role: Lead Oracle PL/SQL Developer

Project: Loyalty Rewards application

TracFone Wireless offers its wireless products and services under various brands that include TracFone, NET10 Wireless, Straight Talk, SafeLink Wireless, Telcel America, and Simple Mobile. The Loyalty Rewards application will provide the rewards points for enrollment/ activation/ reactivation/ redemption and port-in through advanced queueing process called event management system for the Straight-Talk customers only for phase 1.

Responsibilities:

Interacting with the system analysts, business users for design & requirement clarifications.

Participated in the daily SCRUM meetings to produce quality deliverables within time.

created the triggers for advanced queuing process

Developed the batch jobs for 6months/12 months/anniversary and ad-hoc reward points.

Used Ref cursors and Collections for accessing complex data resulted from joining of large number of tables.

Created Indexes for faster retrieval of the customer information and enhance the database performance.

Wrote complex SQL queries including inline queries and sub queries for faster data retrieval from multiple tables.

Performed work in Agile transformation and coaching for improved maturity levels and worked in agile modelled software development life cycle.

Worked with Bulk Collects to improve the performance of multi-row queries.

Used Exception Handling extensively for the ease of debugging and displaying the error messages.

Used Quality center for defect creation and tracking purpose

Developed several stored procedures and functions using advanced Oracle concepts such as Bulk Binds, Bulk Collects Ref Cursors to improve performance.

Involved in fine tuning the existing packages for better performance and providing on-going support to existing applications and troubleshooting serious errors when occurred.

Environment: Oracle 11g, SQL, PL/SQL, HP QC, Java/J2EE, SAP, Informatica, PVCS Tracker, PVCS Version Manager, JIRA, TOAD, PL/SQL Developer tool, BMC tool and Talend Job.

Client: Wells Fargo, Winston Salem, NC

Duration: May 11th, 2015 – Aug 28th, 2015

Role: Lead Oracle PL/SQL Developer/Systems Quality Assurance Analyst

Wells Fargo is one of the major financial service providers. It provides personal banking, online banking, investing services, commercial banking, mortgage and consumer finance etc. It has the largest number of online banking customers in the world. With online banking, customers can operate their bank accounts anytime and anywhere, make transactions such as money transfer, pay bills, view online e statements, pay bills via Wells Fargo, and get alerts. As a part of QA team, I was involved for testing the back-end testing, Mainframe application testing, Data loading from different source to the Data Warehouse

Responsibilities:

Oracle PL/SQL Developer/Systems Quality Assurance Analyst responsible of the Quality Assurance and Test Planning Strategy for an enterprise level Operational Data Store for a Fortune 100 financial services company.

Writing simple and complex SQL queries using DML, DDL, Table joins, and Group functions based on the ILMD (Integrated Layer Mapping Document/Mapping Document).

Made use of advanced features such as Analytical Functions.

Using Common Table Expression (With clause) while writing the queries.

Lead Offshore / Onshore test teams.

Created and maintained Test Cases in HP Quality Centre (HPQC) according to the Business Requirements.

Wrote and executed test cases in HP Quality Center for System Testing and User Accepting Testing (UAT).

Adept at Bug Tracking and Defect Reporting using HP Quality Center.

Design and Develop scripts for uploading data from flat files and XML files into database, performing business validations on the data using PL/SQL Procedures.

Involved in various Requirement, Design phase development and Configuration meetings along with business and development teams.

Executed manual and automated regression test cases after the bug fixes as per test scenarios.

Environment: HP QC, Oracle, Informatica, Data WhereHouse, TOAD, PL/SQL Developer tool.

Client: Washington State Health Benefit Exchange, Camp Hill, PA

Duration: November 21st, 2013 – May 8th, 2015

Role: Oracle PL/SQL Developer

Domains: Eligibility, Enrollment and Finance

Team: Production Support/ System performance

Washington Health Benefit Exchange provides affordable health insurance options for families who need health insurance and who do not receive job-related health insurance. Washington Health Benefit Exchange is designed to serve this increasing segment of the population.

As part of national health care reform, the Washington Health Benefit Exchange has created Washington Health plan finder – an easily accessible, online marketplace for individuals, families and small businesses in Washington State to compare and enroll in quality health insurance plans and access important cost savings.

Responsibilities:

Writing stored procedures under tight schedules for successful production data-fixing.

Analyzing the AWR reports with DBA's and SQL tuning to maximize performance Tune.

Reviewing requirements in the JIRA for production issues and provides time estimates for task completion.

Develops data-driven solutions to fight fraud, waste, and abuse in Medicare and Medicaid using Complex SQL queries.

Worked on a project team to identify Medicare and Medicaid records that had been mistakenly modified and restore them to their proper state. Re-submitted them to Client/Customer for approval once the records were fixed.

Worked on project team to write PL/SQL procedures to identify Medicaid and Medicare patients, based on their records, to notify them of important information such as a new drug is available

Created database reports to get open enrollment and enrolled list reports weekly, monthly and yearly

Fine-tuned SQL queries using functional indexes, hints for maximum efficiency and performance.

Supporting all aspects of production, QA, and development Databases (versions 9i and 10g) on Linux

Performed SQL and PL/SQL tuning and Application tuning using various tools like EXPLAIN PLAN, SQL*TRACE, TKPROF and AUTOTRACE.

Extensively involved in using hints to direct the optimizer to choose an optimum query execution plan.

Environment: Oracle 11g, SQL, PL/SQL, SQL*plus, SQL*Loader, Shell Scripting, Informatica, PVCS Tracker, PVCS Version Manager, JIRA, TOAD, PL/SQL Developer tool and BMC tool.

Client: CTB/McGraw-Hill, Nashville, TN

Duration: September 2011 – November 2013

Role: Oracle PL/SQL Developer

Project: OAS (Online assessment System)

CTB/McGraw-Hill is a publisher of educational assessment solutions for the early learner, K–12, and adult basic education markets. CTB has provided many assessments including OAS (Online assessment), TABE (Test of adult basic education), Terra Nova, Las links, Tabe adaptive, Assessments can facilitate learning at many points along the journey. CTB supports administrators, teachers, parents, and students with the information they need to boost learning.

CTB/McGraw-Hill addresses all of your language assessment needs from gauging the abilities of new students and measuring their progress to lesson plans, instructional tools, and training materials to help you throughout the school year.

Key Features:

OAS allows you to manage Web-Based Administration and test Delivery client.

Content Publishing and Custom student data import and export comes to the ETL Systems.

Run the Individual report and Online Summery of each student for the Clint requirement.

Install Software, Download Test and run Reports for each of their individual Organization (Customer).

Schedule Test Session with student suitable Test group and provides Specific.

Accommodations which they need to attend the exam for each student of Organization.

Solved critical production problems and Supported production and development databases

Responsibilities:

Verify in the db and fixing UI issues by running each students' individual or organization score reports and providing to Customer.

Import Customers, User, Organizations, Schools and Student data into the data base using SQL*Loader, Plsql dev tool and Little Informatica.

Write the complex quires to find out and fix the various issues on behalf of the production support.

Used DDL and DML to write triggers, stored procedures to check the test sessions entry and student result verification

Old packages are enhanced/modified as and when new requirements come across.

Implementation of new packages as per requirements.

Created new Procedures, Functions, Triggers, Materialized Views, Packages, Simple, Ref & Traditional Cursors, Dynamic SQL, Table functions as part of Project/Application requirements.

Ad-hoc scripts update daily with a report.

Analyze information to determine, recommend and plan installation of a new system or modification of an existing system.

Environment: Oracle 10g, SQL, PL/SQL, SQL*Loader, Informatica power center, SQL Developer, SQL Navigator, TOAD, UNIX/LINUX, Windows.

Client: Vanguard, Malvran, PA

Duration: November 2008 – September 2011

Role: Oracle PL/SQL Developer

Project: ETF (Exchange Trading Funds) Market Maker Portal

Market Maker portal will provide Market participants (MPs) to be able to register for web access. Once Web access is approved by crew, MPs can authenticate via username/password credentials. Market Maker portal allow crew to approve/reject MPs registration. The ability for MPs to view domestic ETF information (e.g., baskets, basket files); and, the ability for Authorized Participants CAPS) to place create/redeem orders for domestic ETFs via smoke & mirrors trading.

Vanguard’s Global ETF Portal will be the focal point for key Market Participants (MPs), to interact with Vanguard’s Global ETF business. The Portal will enable Traders (AP’s and MP’s), Operations (Compliance, Back-Office), and Sales Traders, access to essential ETF data, as well as affect the create/redeem transactions in Vanguard ETFs electronically.

Key Features:

Purchased/Redeemed

-via a broker who is paid a commission

-over a stock exchange

-at changing market prices throughout the day

Traded price incorporates a market determined premium or discount

Created from a new class of shares in existing Vanguard Index funds (Share Class 7)

Only the Authorized Participants(APs) can purchase/redeem ETF shares from the issuing fund

-AP delivers a basket of individual security positions in exchange for a “Creation unit” of ETFs (e.g., one unit might be 100,000 shares worth $5-10 million.)

Responsibilities:

Creating Oracle objects like tables, types, context, packages, procedures and functions to Add, Maintain, Updates and Deactivates Firms, Desk, Users that can View, Modify and Trade.

Automated sql loader using shell scripting.

Reviewed the UI validations and added required validations to the packages to ensure the ideal data population into the tables.

Involved in development, enhancement and maintenance of PL/SQL interfaces such as defining packages, developing procedures and functions.

Develop the Shell scripts to automate the finance billing file

Defined Views and Materialized views for simplifying reporting queries and performance

Performed Query optimization and PL/SQL tuning to maximize performance

Developed PL /SQL code for updating payment terms

Developed TOAD, SQL*loader scripts to load data in the custom tables.

Environment: SQL, PL/SQL, SQL*plus, SQL*Loader, Oracle 11g, java, Shell Scripting, Informatica. Toad, BMC sql developer tool, Sales force for issues.

Client: Lending Space India (P) Ltd, India.

Duration: January 2006 – November 2008

Role: Java Developer/ Oracle PL/SQL Developer

Project: Lending Space’s comprehensive Loan Origination System (LOS) automates the entire lending process from origination through funding. It goes far beyond rate quotes and loan applications by automating the most complex, inefficient and labor-intensive aspects of lending; including automated underwriting, customizable work flow, compliance, document preparation management, rule-based assignment system, vendor interfaces and customer relationship management.

Reports Generation Document Center

Back Office Interfaces

Contact Management

Desktop Underwriting

Loan Processing

Call Center

Customizable Work flow

Technology Implemented:

The Lending space is implementing great technology in Mortgage Banking, in which we have used 6layer architecture to reduce the network flow and to increase the performance in the application, has Queuing System.

Biz Rules, where we can avoid the re-implementation of coding from client to client.

Call Function Technology, where we can submit only a specific control to business layer instead of submitting the entire page.

Object driven, where we are increasing the performance of the application, all transactions will be screen level, and transactions can be roll backed, Object to Data Base.

Separate layers, which will not allow the user to communicate with persistence store.

Authentication in screen level and control level depends on the roles of the user.

Responsibilities:

Developing packages, procedures, functions, and triggers for the application.

Using UNIX environment for performing the testing

Performing the tests on the newly coded procedures and documenting the same.

Writing technical documents using the functional specifications

Writing PL/SQL code using the technical and functional specifications

Creating Oracle objects like tables, types, context, packages, procedures and functions.

Automated sql loader using shell scripting

Involved in Development of the Server-side applications.

Understanding the requirements using functional documents and client specified documents

Optimized the queries to improve the performance of the application

Environment: ORACLE 8i, PL/SQL, Developer 2000, Java, Servlets, JSP, Struts, JDBC, UNIX, SQL*loader, Unix Shell scripts, Oracle 9i, Unix, Windows, PVCS Tracker, PVCS Version Manager.

Higher Education:

Name of Degree : Master of science

Specialization : Physics

Duration : 2002 - 2004

% Marks : 67%

University : Acharya Nagarjuna University, INDIA

Name of Degree : Bachelor of computer science

Specialization : Computers

Duration : 1999 - 2002

% Marks : 60%

University : Acharya Nagarjuna University, INDIA

Professional Certifications:

Certification Name: Oracle Certified Professional Programmer

License number: TE8SYD5448

Date: December 18, 2010

Contact this candidate