- Responsible Technical Lead, proficient in Oracle Development and Design. 8.5 Years of experience in Development and Support Projects.
- Passionate and motivated, with a drive for excellence having excellent communication skills.
- Worked in Development and Support application for Healthcare and Telecom industry.
- Write, update, and maintain Oracle database packages, procedures, functions, triggers, views.
- Performance Monitoring of Database SQL and designs causing abnormal CPU, Memory consumption using AWR Report and Oracle internal tables.
- Oracle Database migration from 11g to 12c for ten offshore database with minimal downtime with no failures.
- Experience on Performance Tuning and Query Optimization to improve the performance of PL/SQL and SQL Queries by techniques like Partitioning, Indexes, Bulk binds, Materialized Views, Explain plan, Optimizer hints, SQL Trace and Tkprof utility.
- Extensive experience with SQL tuning by considering execution plan components like Cardinality, Access method, Join method, Join type, Join order, Partition Pruning and Parallel Execution. Using the same understanding that the optimizer to has while creating a plan for the given query.
- Experience in Tracing and Resolving Oracle backend session slowness using the knowledge of Dynamic internal oracle views for a live or history session/process. v$session, v$active session history, v$sqlstat, dba hist % views etc.
- Worked on heavy Data loads using multiple efficient ways like SQL* Loader, External Table and shell scripting.
- Implementation, design and maintenance of solution using High level OAQ (Streams) understanding. Subscriber and Notification based Oracle Advanced Queue implementation which process > 20 million clinics data every day.
- Proficient in using advanced features of PL/SQL programming like Collections (Associated arrays, Nested tables and Varrays),Table Functions, PipelinedTableFunctions, REF Cursor - and Dynamic SQL(using EXECUTE IMMEDIATE).
- Experience in data import/export of different file formats like TXT, XLS, CSV, and XML. Hands-on experience on the UNIX/AIX database servers, for creating and executing Oracle scripts.
- Experienced in writing complex shell scripts and CRONTAB scheduling for UNIX servers for monitoring and maintaining Oracle database jobs.
- Experienced with SMTP and HTTP call operation from Oracle internal functions.
- Very well aware of all Oracle Metadata tables information storing Oracle object information and then utilizing it for creating various dynamic projects or tools.
- Worked on most widely used Oracle DB supported tools like PLSQL Developer, TOAD, and SQL Developer.
- Supported and developed Oracle code for applications front-ending with Java and .NET programs.
- Compile and document programs developed and subsequent code reviews.
- Assign, coordinate, and review work and activities of other associates working on Database scripts.
- Interacting with customers on new requirement, coordinating in its design and implementation.
- Supporting all phases of a SDLC, from development, unit test, to Production roll-out of code .
Languages: SQL, PL/SQL, Shell scripting, HTML
RDBMS: Oracle database 11g/10g/12c
Development tools: TOAD
Oracle SQL Developer: SQL*Plus, PL/SQL Developer
SQL Server Management studio: WinSCP, SSH (Putty), Cygwin, X Manager, Erwin, DELPHIX
Version control tools: Microsoft Studio TFS 2015, Clear Case.
Operating Systems: Windows NT/2000/XP/Vista/Windows server-2008, UNIX (HP, AIX), Sun Solaris-10.
Documentation Tools: MS Power point, MS Visio 2010
Confidential, Brentwood, TN
Sr. Oracle Developer/Data Modelling Architect/Performance Tuning
Environment: Oracle 11g/12c, Windows server-2005, SSIS, SSRS, .NET Entity Framework, AIX Database Server.
- Participated in Requirement analysis, writing High-Level and Detailed Design documents. Created flow diagrams with MS Visio, prototypes and other design artifacts.
- Participated in Data modeling activities for creating, and maintaining, conceptual, logical and physical data models for OLTP system using Erwin.
- Oracle Database migration from 11g to 12c for ten offshore non-production database with minimal downtime and no failures.
- Involved in design/developing/testing complex PL/SQL programs and ETL processes based on change in requirement or to correct a defect in data caused by bug in .NET code.
- Mainly involved in coding Complex PL/SQL Packages, Functions, Triggers, and Views.
- Designed and created oracle objects for SANDBOX project which is incorporated in Production schema. Users have the ability to change coverage details for a clinic in SANDBOX. This triggers a daily run which verifies and moves this updated data in Production schema, then calls .NET web service to pause all the BPEs (Business Process Engines) present on 20 application servers. Once the data is replicated in Production schema, all BPEs are turned on and all impacted claims till date are triggered for correction.
- Reviewed and updated many existing Oracle objects for better performance by re-writing them without impacting the functional output.
- Managed the setup of Application IPs access from Oracle, by configuring ACLs using DBMS NETWORK ACL ADMIN.
- Used BULK Collect, Global Temporary tables to improve transactions on tables having millions of records.
- Helped develop a reporting framework which uses Oracle Advanced queue notification based triggers. Here user can trigger and queue multiple reports which run asynchronously in backend and provides users the link to extract same report multiple times by storing them in another table.
- Helped develop a table cleanup framework for fast growing but redundant tables. This framework just needs the table name and filter criteria defined. The cleanup job runs daily and clear huge amount of data from these tables, saving lot of space for the database.
- Designed and implemented tables using Range, Range + Hash Reference Partitioning to meet the transaction speed required by business.
- Worked with User defined exception handling to catch and handle various data limitations.
- Worked on implementation of Advance Compression on new partition for the existing tables to improve performance as well as reduce storage requirement.
- Identified and rebuilt Compressed indexes which improved the performance up to ten times in many cases.
- Analyzing the AWR/ADDM report to find slow/resource intensive queries degrading system performance. Then resolving these by re-writing SQL or PL/SQL object, indexes, hints, or making necessary change in data-model.
- Involved in Development and design of new CRs provided by users.
- Support in deploying and verifying production deployments.
- Interacting and assigning business critical work to Off-shore teammates in India for effective response on issues.
Oracle Developer/Database Administrator
Environment: Oracle 10g/11g, PL/SQL Developer, SQL Developer, TOAD, UNIX Shell scripting, HP-UX, Windows XP, Clear Case.
- Involved in analyzing and designing oracle objects based on new requirements from the clients.
- Closely worked with Functional/End-Users to understand the system requirements and develop Functional or Technical specifications for development purpose.
- Developed back-end PL/SQL code to implement business rules through triggers, stored procedures, functions, and packages using TOAD or Oracle SQL Developer.
- Worked with Java developers to implement their needs to interact and extract data from Oracle database using procedures, packages, functions with CURSOR as return values.
- Optimized and tuned PL/SQL to reduce the loading process by using Bulk SQL and Utilized SQL*Loader to perform bulk data loads into database tables from external data files.
- Used of bind variables in SQL queries to improve the performance by avoiding hard parsing wherever required.
- Created Materialized views to build data warehouse tables for improving the execution speed of reports and queries. Used MV’s fast refresh mechanism to get the changes quickly form source databases.
- Created Confidential to interact with various teams sharing data to be viewed on user interface.
- Applied table partition technique to improve the performance of huge data tables. Especially, used Range partition techniques to the advantage of greater ability storing and traversing data faster.
- Improved query response time by making use of advanced PL-SQL components GT Tables.
- Developed UNIX Shell scripts interact with Oracle, to load data from huge CDR (call data records) files. The CDR files were sent in TEXT file format throughout the day summing up to about 50 GB of files every day. These records were read and loaded into database tables by threading parallel jobs in Unix using SQL* Loader utility.
- This data was used by DOT (Department of Telecom) to trace calls for required cell numbers, as it contained various customer and geographic details along with the call duration.
- Used FTP/SFTP for communicating with other teams internal server, to FTP files, process and load them based defined parameters/delimiters using SQL* loader. Also, sending out communications via emails for successful/failed processing using mail utility in UNIX.
- Used various file formatting commands as awk, cat, head, tail, grep along with basic UNIX commands like ls, ftp, function, cp, mv and flow control commands like if-fi, while, break etc.
- Worked as development as well as production support team for 24x7 support model.
- Involved in Code Review, Code Walk Through, and Unit Testing.