Tuesday, June 30. 2009
Implement New Modules When You Upgrade from Release 11i to Release 12: Projects Consulting Services with TruTek
Implement Projects and Project Billing
Oracle Projects acts as the bridge between Operations and Finance. Oracle Projects acts as a central repository of project transactions, processes project costs and creates corresponding accounting entries for the Finance Department. For contract projects Oracle Project Billing simplifying client invoicing, improving cash flow and allowing for project profitability to be measured.
Oracle Project Billing allows you to simplify client invoicing, improve cash flow, and measure the profitability of contract projects with support for planning, execution, and analysis. It utilizes information from the work breakdown structure and provides flexible revenue accrual and invoice generation features, enabling you to track unbilled receivables and bill customers for individual projects, as well as internal charge-backs.
• Drive Enterprise Profitability - Streamline invoicing to speed up the payment cycle and track project profitability.
• Improve Cash Flow - Invoice accurately and in a timely manner by having all project information in one centralized location.
• Identify Profitability by Project and Customer - Identify profitability by project and customer to maximize business opportunities.
Implement Project Resource Billing
Oracle Project Resource Management helps you manage the capacity and deployment of people and assets for project work by finding and deploying the most qualified resources to your global projects. Oracle Project Resource Management centralizes information to empower project, resource, and staffing managers to better utilize their single most critical resource: people. The net result is improved customer and employee satisfaction, increased utilization, and higher project and enterprise profitability.
• Manage Resource Demand and Supply - Share resource calendars and schedules to assure availability.
• Efficiently Leverage Human and Intellectual Capital - Maintain a central skills repository.
• Make the Best Use of Resources - Utilize concise definitions of requirements with mandatory and optional skills.
• Streamline Global Operations - Support local work patterns, discontinuous assignments, and local holidays.
Tuesday, June 30. 2009
Implement New Modules When You Upgrade from Release 11i to Release 12: Manufacturing Consulting Services with TruTek
Implement Production Scheduling
Oracle® Production Scheduling, designed for schedulers by schedulers, enables you to take control of your production scheduling problem, and helps you to maximize shop floor throughput while optimizing resource investment. You can optimize the usage of your critical resources, calculate realistic and feasible schedules that the shop floor can execute, and easily determine the schedule that best meets your objectives. In addition, you can leverage the out-of-the-box integration with the Oracle E-Business Suite (discrete, process, and lot-based manufacturing) to implement quickly and obtain immediate value.
• Maximize throughput of bottleneck resources - Simultaneously consider capacity and calendar constraints on machines, crews and tools with automatic floating bottleneck detection to optimally schedule your shop floor. Create feasible schedules using sequence dependent setup minimization and campaign run optimization.
• Improve shop floor performance - Schedulers can minimize the effect of unplanned downtime, easily identify exceptions, and use a powerful drag-and-drop scheduling workbench to quickly adjust schedules.
• Quickly determine the best production schedule - Fast easy simulation enables your schedules to quickly compare scenarios and key performance indicators before deciding which schedule to release for execution. Planners can also review both planned and actual production orders to make their decisions.
• Implement quickly by leveraging out-of-the-box integration - Production Scheduling is fully integrated with the Oracle E-Business Suite, enabling you to start quickly by leveraging existing setup. It supports discrete, process, and lot-based manufacturing without the need for extensive configuration.
Implement Shop Floor Management
Oracle Shop Floor Management enables comprehensive, real-time management of shop floor activities, including complex lot transactions, dynamic routing, end-to-end genealogy of products, and modeling and tracking of operation yield costs. A single repository of shop floor information helps ensure the continuum between planning, scheduling, and execution.
• Manage Complex Lot Transactions - Split or merge lots. Update lot name, product, routing, and quantity. Enable bonus lot creation.
• Track Lot Genealogy - Track products back to raw materials. Enable backward or forward genealogy "surfing."
• Enable Dynamic Routing - Determine routing dynamically, depending on process needs or resource availability. Gain ability to jump to any operation.
• Integrate Planning and the Shop Floor - Support lot-based jobs and operation yields. Schedule orders with network routing. Gain CO-product planning capabilities.
• Gain Yield-Based Operational Costing - Accurately measure operation yield levels, yielded cost of products, and cost variance at operations. Support standard costing methods.
Tuesday, June 30. 2009
Implement New Modules When You Upgrade from Release 11i to Release 12: Supply Chain Management Consulting Services with TruTek
Implement Advanced Supply Chain Planning
• Mixed Mode Manufacturing Production Schedules
• Use Drag and Drop Scheduling
• Generate plans from multiple instances
• Measure key performance indicators against targets
• Define penalty factors and run constrained optimized plans (with Oracle Constraint Based and Optimized Planning)
• Optimize plans across multiple facilities, balancing trade-off between various objectives (e.g. minimize Inventory costs, optimize purchasing costs, maximize customer service or plant capacity)
• Plan for alternative Routings, resource and materials use
Implement Demand Planning
Oracle Demantra Demand Management helps you maximize profitability with superior capabilities to better sense, shape, and respond to demand. Automated analytics ensures that your plans are based on the most accurate, forward forecasts without the need of statistical knowledge. The result is a demand-driven organization with higher service levels and sales, more satisfied customers, and lower inventory and distribution costs.
• Automated forecast analytics—uses mixed model approach to ensure out-of-box forecast accuracy
• Real-time forecast capabilities—allows planners to get instant answers even in the most data-intensive planning scenarios
• Multidimensional data modeling with flexible data hierarchies—enables up-to-date demand visibility for all departments and makes one-number planning feasible
• Chaining, attribute-based forecasting, and shape modeling—predicts new product demand and product phase-out while reducing the overall planning effort
• Dashboards, automated exception management, and workflow—quickly respond to changing market conditions
• Web-based, real-time internal and external collaboration, with role-based security—monitor and respond in real time
• Rapid implementation—faster time to benefit and greater return on investment
Implement Global Order Promising
Fast, accurate order promising is the key to retain existing and attract new customers. Oracle® Global Order Promising enables you to make quick delivery promises your customers can rely on. Oracle's order promising capabilities include robust support for distributed global order promising and multi-level supply-chain Available to Promise (ATP), Capable to Promise (CTP), and Capable to Deliver (CTD). You can consolidate supply and demand information from multiple transaction systems to provide a consolidated global picture of demand and supply. Order promising is accessible from multiple order entry systems or order capture systems (such as web stores and call centers).
• Order promising across different instances
• Order promising at product family level
• Check CTD (Capable To Deliver) using transportation capacity and lead times
• (Capable To Promise) for key components and bottleneck resources
Promise accurately and reliably - You can promise order more accurately when you take all the constraints of your supply chain into consideration (substitutes, alternates, lead times, network, capacity). In addition, you can have a more responsive and reliable promising process when the system is available 24x7 with 'zero downtime.' By combining a database-driven architecture with manufacturing, supplier, and logistics constraints, you can ensure improvements in customer service levels and an increase in fill rates.
Manage key customer and channel commitments - You can also manage allocations of supply to key customers and channels, thereby ensuring that you can meet contractual agreements and service levels in the priority that yields most profitability. Planners can easily configure the system to vary allocations in line with priorities that flow out of your Sales and Operations Planning process.
Promise complex multi-level configurations - Tight integration with Oracle Configurator and Order Management enables accurate order promising of complex configurations. Sourcing and availability can be dependent on the options selected during the configuration process. Multiple levels of models and options, including outsourced configurations are supported, whereas match-and-reserve can quickly identify availability for existing configurations.
Respond to changing conditions - Extensive backlog management enables you to (re)promise a group of orders in a priority sequence. You can also leverage the filtering and manual override capabilities to take care of your most important customers first rather than following the system-suggested recommendations.
Implement Inventory Optimization
Today's tough economic conditions, characterized by increased competition, short product life cycles, increased outsourcing, and increased demand uncertainty, make it more challenging to manage your inventory policies and decide where exactly to hold how much inventory. By taking into account the supply, demand, and lead time variability of your entire supply chain, Oracle® Inventory Optimization enables you to cost effectively balance revenue, cost, customer service levels, and inventory budgets while determining your inventory postponement strategy
• Postponement and risk pooling - Oracle® Inventory Optimization uses postponement and risk pooling to help you determine where to hold inventory to guarantee a desired service level and at what stage of production to hold inventory. It can consider all your manufacturing plants, distribution centers, central warehouses, and even your suppliers and customers. In addition, it considers your bill of materials to calculate how much inventory to hold at the finished goods, subassembly, and raw material level. Oracle® Inventory Optimization solves the complex issue of balancing the time and cost you need to incur to get a product to a customer, without paying high costs to hold every product configuration close to your customer's locations.
• Mitigate supply chain risk by managing supply chain variability - To optimize your inventory investment, you need to account for all of the variability in your supply chain. Demand variability occurs due to seasonal factors, forecast errors, promotions, or new product introductions. Supply variability occurs due to lead-time uncertainties and supplier performance issues such as product quality and unreliable delivery. Capturing the variability in demand and supply enables you to obtain the precise determination of the inventory investment that is required to meet your customer service objectives, at the lowest possible cost, while remaining within your available inventory budget.
• Determine your most profitable channels, products, and customers - Typically, your customer service target is not a single number. You might have different goals for different products, customers, or sales channels. Oracle® Inventory Optimization helps determine the most profitable targets to strive for as well as optimize the use of your scarce resources to focus on achieving the most profitable product and sales mix. You can also define different service levels at various levels as every product, sales channel, and customer does not need to have the same service level.
• For planners, not programmers - Oracle® Inventory Optimization leverages cutting-edge optimization and risk pooling techniques to determine the most optimal solution, yet it was designed For Planners Not ProgrammersTM, empowering your inventory analysts and planners to make decisions intuitively and quickly.
Tuesday, June 30. 2009
Implement New Modules When You Upgrade from Release 11i to Release 12: Human Resources Consulting Options with TruTek
Oracle iRecruitment is a full-cycle recruiting solution that gives managers, recruiters and candidates the ability to manage every phase of finding, recruiting, hiring, and tracking new employees. Focused on the manager-recruiter-candidate hiring relationship, this end-to-end recruitment offering incorporates all aspects of applicant tracking with capabilities that allow managers, recruiters and candidates to fully manage the entire recruiting cycle via a self-service interface.
• Hire More Efficiently - Manage open job vacancies, search for candidates, and manage the interview process, and extend recruiting functionality to managers. Leverage third-party integration for pre-screening and background checks (HireRight). Take advantage of powerful search functionality through Oracle RDBMS.
• Manage and Share Your Candidate Base - Maintain profiles, search for and refer jobs to colleagues, and follow the recruitment process. Use integrated resume extraction capabilities (using ResumeMirror extraction engine) to search for potential candidates.
• Free Up Your Recruiters' Time to Be More Productive - Enable recruiters to reduce administrative tasks and focus on promoting the corporate employment brand. Deploy full requisition management with workflow. Use event driven applicant tracking. Manage job board postings on multiple external websites with built in security considerations.
• Promote Self-Service - Candidates can maintain profiles, search for jobs, refer jobs to colleagues, follow the recruitment process, and more. Utilize pre-built reports and metrics.
Implement Time and Labor
Oracle Time and Labor automates the entire time and attendance record-keeping process and provides an intuitive, web-based interface for time entry and approval. It offers a simplified way to submit, review, track and approve timecards. At the same time, it helps you control costs and time worked, consolidate timecard information, and adhere to organizational time management rules.
• Enter Time Anytime, Anywhere - Enter time via web browser, mobile device, or even offline using a timecard template that can be uploaded later.
• Streamline Management - Enable approvers to search timecards and view summary information. Perform mass approvals. Gain flexible configurations for approvals workflow.
• Enforce Policies - Define rotation plans based on shifts and work plans. Define which employees can charge to which organizations in your company. Support policies for holidays, overtime, and rounding.
• Automate Timecard Creation - Automatically generate a timecard or group of timecards from work schedule, last timecard, or a project stored in Oracle Projects Resource Management.
• Create Single Source of Entry - Improve reporting, extracting, and processing with a single database of employee time-related information. Share data with Oracle Projects, Oracle Human Resources, and Oracle Payroll.
Implement Workforce Scheduling
Oracle Workforce Scheduling is a comprehensive offering that helps organizations accurately forecast and schedule workers to meet both customer service and cost objectives. Based upon the functionality Oracle obtained through its acquisition of TempoSoft's intellectual property, Oracle Workforce Scheduling expands Oracle's human capital management (HCM) footprint and enables companies in the retail and hospitality industries to optimize schedules and meet forecasted workload demand. Oracle Workforce Scheduling is currently available and sold as a stand-alone product. Subsequent releases are scheduled to include integration with Oracle's applications offerings and to extend functionality to other sectors such as retail branch banking, call centers, healthcare and others.
• Optimized Workforce Management - optimized staffing schedules can impact customer satisfaction, drive sales and reduce operating costs
• Reduced production time - produce a weekly labor schedule in minutes rather than several days, providing managers with more time to spend with customers
• Schedule Management - Staffing schedules based upon peak periods can lead to increased customer satisfaction. Organizations benefit from the ability to track key performance indicators that provide visibility into the status of revenue, scheduling and staff productivity
• Leverage Mathematical Modeling - Forecast and demand planning features incorporate a statistical analysis engine to forecast daily and weekly business demand
Implement Compensation Workbench
Compensation Workbench (CWB) allows managers and compensation professionals to plan, model, and allocate budgets and awards on a global basis across multiple currencies. Users can distribute budgets and compensation based on guidelines, see a worksheet view of employees, their performance ratings and total compensation. They can also promote employees and give them new performance ratings and rankings. Analytics provide internal and external comparisons, statistical analysis, compensation history, and overviews of current budgets and allocations so managers can make decisions that support business objectives. All of this is done within a secure environment with a configurable notifications and approvals workflow.
Total Compensation Statement (TCS) is bundled with Compensation Workench. Using Total Compensation Statement, organizations can easily build online or paper based statements that help employees realize the full value of their rewards.
• Globally Manage Total Compensation - Balance equity across a group of employees, regardless of different locations, currencies, business groups or programs. Multiple components of pay including merit and promotion increases, lump sum adjustments, bonuses and stock awards can be viewed and altered at one time.
• Gain Flexibility in Implementing Your Compensation Strategy - Complete coverage is offered for any compensation plan design. Plans can be quickly created, modeled and rolled out to meet changing business needs.
• Enforce Company and Legislative Guidelines - Define target amounts and ranges to enforce compensation and budgetary policies. Guidelines can be based on any number of criteria including job, grade, length of service, performance rating, compa-ratio or range position.
• Drive a Pay for Performance Culture - Use employee performance measures to drive the planning, budgeting and approving of compensation. Guideline matrices can be created to easily manage compensation policies based on performance ratings and rankings.
• Communicate Total Rewards - Use Total Compensation Statement to communicate rewards and benefits to employees. This enables employees to view their total compensation through an easy-to-use self-service interface. Paper based statements allow total rewards to be viewed offline.
• Avoid Integration Woes with a Complete Solution - As part of a larger suite of HCM applications, Compensation Workbench provides seamless integration and a common user experience with modules essential to the compensation planning process: Performance Management, Core HR, Incentive Compensation, Financials, and Payroll.
Implement Learning Management
Oracle Learning Management (OLM) is the best way for Oracle E-Business Suite customers to:
• Improve workforce performance
• Ensure partner channel sales and service readiness
• Increase revenue through training product sales to customers
OLM is an enterprise learning management system (LMS) that lets organizations manage, deliver, and track training participation in online or classroom-based environments. Learners can find the learning they need, and take the learning in the most appropriate delivery mode (in a classroom, in a virtual classroom, or elearning). Managers can automate key business flows - from order processing to training delivery; from performance appraisals to training assessments - and keep tabs on their team's learning and development. Instructors can manage their own schedules, classes, and rosters. Executives can measure the effectiveness of learning activities and ensure alignment with organizational objectives. Learning Administrators can efficiently manage all the catalog objects, learners, and related resources from a centralized system. In sum, turn learning into a business advantage with Oracle Learning Management.
• Develop Your Workforce - Leverage a common, reusable framework for describing learning objectives. Define competencies attained by learner. Automatically update competency profiles. Create learning paths and certifications to guide and manage your workforce learning and development.
• Tie Training to Organizational Goals - Measure the effectiveness of training initiatives.
• Reduce Costs through Online Learning Delivery - Gain a content player that delivers any web-accessible content. Deploy content to a global learning community. Collaborate via forums, chats, and web conferencing.
• Simplify Administration through a Unified Catalog - Create a single catalog for all online, offline, synchronous, and asynchronous learning. Support blended learning. Enable administrators to see the entities they create and directly edit their properties.
• Increase Efficiency by Managing Learning Content - Assemble and deliver learning content rapidly Enable the reuse and assembly of learning content that makes use of mixed media and multiple learning modalities. Deliver the optimal blend of classroom and online training.
• Extend Solution Value by Providing Learning to Customers and Partners - Provide self-service access to learning for customers and partners. Retain one-stop administration. Automate catalog distribution and enrollment.
• Simplify Administration by Efficiently Scheduling and Managing Resources - Place the right resources and equipment in the right locations. Define learning event needs only once. Ensure instructors possess the necessary competencies and experience. Consolidate training initiatives on a scalable, cost effective LMS.
Tuesday, June 30. 2009
Implement New Modules When You ... Posted by Barb Matthews in EBS R12 at 11:54
Implement New Modules When You Upgrade from Release 11i to Release 12: Procurement Consulting Options with TruTek
Oracle iProcurement is the self-service requisitioning application that controls employee spending. Oracle iProcurement provides a Web-based shopping system that allows employees to create, manage, and track their own orders while the Purchasing department retains central control. This helps to ensure that policies and preferred pricing agreements are reflected in every transaction. Easy ordering and seamless workflow provide better service, while non-sourced or off-catalog spending gets the attention it needs from buying professionals.
Oracle iProcurement is among the better known and most widely used tools within the Oracle Advanced Procurement suite. R 12 includes ongoing improvements – some of which improve the cosmetics, while others represent major architectural advances. Major enhancements include:
Oracle Sourcing is the enterprise application that drives more and better sourcing through online collaboration and negotiation. It is a key component of Oracle Advanced Procurement, the integrated suite that can dramatically cut all supply management costs.
Because strategic sourcing is typically time-consuming and complex, few organizations are able to exploit all available sourcing opportunities. Oracle Sourcing increases the sourcing bandwidth of procurement professionals, so they can exploit many more savings opportunities and capture more value from each. Online collaboration and negotiation makes it easy for experts from multiple organizations to exchange information, define requirements, conduct negotiation, and create new contracts. Buying professionals, business experts, and suppliers can all collaborate to create agreements that provide the best terms.
Implement Services Procurement
Oracle Services Procurement R 12 includes advancements that will propel its use beyond its traditional role of automating simple services and contingent labor. From service specification, through contracting, and payment, R 12 has been designed to tame complexity and drive visibility. Note that in some cases other modules (e.g. Procurement Contracts) are required to fully leverage enhancements. With R 12 procurement organizations can:
Implement Oracle Procurement Contracts
Negations don't save money. Procurement organizations save money when they hold suppliers to negotiated terms and conditions. That's the premise behind Oracle Procurement Contracts, namely to equip procurement professionals to close the gap between forecasted savings and realized savings. Major enhancements to R12 include:
Implement Oracle Procurement & Spend Analytics
Gain Visibility into the Complete Procure-to-Pay Process
Oracle iSupplier Portal is the enterprise application that structures all supplier communication through a secure internet-based portal.
Phone calls, faxes, and e-mails with suppliers waste time, introduce errors, and create latency in your supply chain. Oracle iSupplier Portal, with its powerful platform for online collaboration, enables you and your suppliers to become more efficient. Suppliers access the latest information, including purchase orders, delivery information, and payment status. The rich two-way collaboration enables suppliers to submit change requests, ship notices, payments, and profile data. Now you can gain better service, reduce processing costs, and get relief from costly supplier inquiries. The bottom line is that your buying organization has time to focus on what really matters-getting more savings.
Oracle iSupplier Portal was built with the recognition that suppliers' active participation in the procurement process improves efficiency. In many ways it will increase the value of R 12 as a whole by providing a 'supplier facing' view of new capabilities. Major enhancements include:
Monday, June 29. 2009
In today’s environment, highly skilled DBAs and System Administrators are hard to find, especially those willing to work the graveyard shift, or support operations around the world and around the clock. Every organization needs to continue to operate when their DBA takes a vacation or goes to training - or worse, gets sick. TruTek can monitor and correct issues before users even notice there is a problem.
TruTek has remote customers around the world, operating 24 X 7. There are three shifts of DBAs supporting clients at all times. Each customer is assigned a primary DBA and System Administrator. If a critical problem is encountered, TruTek will assign as many support specialists as necessary to solve the problem.
TruTek has developed its own monitoring software that is guaranteed to provide notification. This software is reliable, predictable and it can be easily customized. It monitors the database server, the application, and web servers. This approach minimizes the need for reactive problem management.
The Alert Engine is at the heart of TruTek monitoring. The Alert Engine manages the notification of TruTek consultants and the client’s staff, based on rules defined by the client. TruTek not only monitors the system, it responds and fixes any issues. During the resolution, the client’s staff will be provided a summary of the problem and TruTek can mentor the client staff.
TruTek's remote DBA service is the cost-effective alternative to employing more full-time DBAs or contractors. TruTek allows you to cut your Oracle DBA overhead and improve quality. Remote services provide access to some of the most experienced DBAs and Application DBAs in the country. Proactive scripts and tools help prevent potential problems. Your DBAs will love working with TruTek; they will learn and have additional help for those difficult situations.
Call 877-486-6655 to schedule a demonstration or visit the TruTek booth at any OAUG or IOUG conference.
Try TruTek’s Remote DBA Service, and we guarantee that we can fix your hardest problem, or you get your money back.
TruTek offers remote database services, remote Oracle applications functional support and Unix system administration support.
TruTek's Remote Database service is reliable and guaranteed. TruTek DBAs are certified experts and can solve your problems quickly, instead of waiting for a solution.
TruTek Remote Database services monitor your database, application server, URL, database listener, Oracle Applications (concurrent processing, form and web servers, and web listeners), log files and alert logs. We also monitor the network and machine state.
TruTek not only provides 24 X 7 monitoring, we will also fix your problems and/or mentor your DBAs. Our DBAs are experts with Oracle Applications. TruTek also has an expert development staff ready to solve your toughest application issues.
TruTek's Remote Database Services for Oracle include: Backup and Recovery services, performance tuning, technical support, mentoring, database design and configuration, database audit, monitoring, sizing and capacity planning, and projects including Upgrades, Custom Development, Reporting and Data Warehousing.
Our Remote Database Services can help with critical system upgrades, extra coverage for your DBAs, and temporary DBA support. TruTek's DBA Support Services is the cost effective alternative to employing additional full-time DBAs or contractors.
If you want to improve your DBA effectiveness and quality, call TruTek at (877) 486-6655.
For more information about TruTek and what we offer please visit our website at www.trutek.com.
Tuesday, June 9. 2009
Transportable Database by Robert Freeman Posted by Barb Matthews at 13:06
Don't miss Robert's upcoming classes Oracle Database 11g New Features, August 3-7 in Salt Lake City, and Backup and Recovery Using 10g RMAN in Denver, August 10-14.
I came across this little RMAN feature that I have not written about. So I thought I'd share it with you.
It's called Transportable Database. It's really an off-shoot of Transportable tablespaces except now you can move the whole database kit-and-kabootle over to a brand-new database on any supported Oracle platform. Transportable database is supported by RMAN. This is a new feature starting in Oracle Database 10g Release 2.
The steps to move the database between platforms is pretty straight forward:
1. Verify the prerequisites
2. Identify any external files and directories with DBMS_TDB.CHECK_EXTERNAL.
3. Shutdown (consistent) and restart the source database in READ ONLY mode.
4. Use DBMS_TDB.CHECK_DB to make sure the database is ready to be transported.
5. Run the RMAN convert database command.
6. Copy the converted files to the target database. Note that this implies that you will need 2x the storage on the source database for the converted files.
7. Copy the parameter file to the target database.
8. Adjust configuration files as required (parameter, listener.ora, tnsnames, etc).
9. Fire up the new database!
This can make for a much quicker and easier migration between platforms than the old IMPDP/EXPDP method!
Robert's latest technical book, called OCP Oracle Database 11g Certified Professional Study Guide, is available. It's a prep guide for the Oracle Database 11g OCP Exam (Exam 1Z0-053). Check out this and other great books on our books link.
Tuesday, June 9. 2009
In a recent load test of the Oracle Applications Release 22.214.171.124 Oracle Time and Labor module, we simulated time card entry and approvals for 400 simultaneous users. The generated wait events and subsequent wait times indicated the top three potential performance improvements were:
1. Running Statistics
2. Purging Workflow
3. Resolving Blocking Locks
The first two performance techniques are fairly well understood. These issues can be resolved by periodically running two concurrent programs: Analyze something something and Purge Obsolete Workflow Runtime Data (though Workflow may require additional scripts to deal with data that isn’t in the correct format for the purge program).
The blocking locks are primarily caused by abnormally terminated processes that refuse to release database locks when the process terminates. The typical process to remove blocking locks is performed manually by the DBA at the command line by querying the session and serial number of the process and then killing the specific database session. This manual approach takes time for the user to recognize there is a problem and more time for the DBA to find the session information and kill the session.
The use of Dead Connection Detection will automatically remove any dead database connections for any application that can recognize a dead process. The problem is the database considers the SQL*Net connection to be active, and by default, the Transparent Network Substrate can’t determine the status of the database connection .By setting parameters in the sqlnet.ora and tnsnames.ora files, dead connection detection can easily be enabled.
A common problem in Oracle Applications illustrates this problem. The Human Resources module uses a table named PER_ASSIGNMENT_ALL. Users access forms that lock rows in the table PER_ASSIGNMENT_ALL. When two users, or many times the same user with two forms open querying the same row, access that same row with the intention of updating the row, a blocking lock is created. This can happen when the first form is closed abnormally and the lock is not released. One lock is blocking and the other lock is waiting, but the blocking lock process has terminated and the database doesn’t realize the blocking lock is no longer valid, because DCD has not been enabled.
Oracle Network Basics
TCP/IP is a connection-oriented protocol, and provides packet timeout and retransmission in order to guarantee the safe and sequenced order of data packets. If a timely acknowledgement is not received in response to the probe packet, the TCP/IP stack will retransmit the packet some number of times before timing out. After TCP/IP gives up, then SQL*Net receives notification that the probe failed.
1. Dead Connection Detection
Dead Connection Detection (DCD) is a feature of SQL*Net 2.1 and later, including Oracle Net8. DCD detects when a partner in a SQL*Net V2 client/server or server/server connection has terminated unexpectedly, and releases the resources associated with it.
DCD is initiated on the server when a connection is established. At this time, SQL*Net reads the SQL*Net parameter files and sets a timer to generate an alarm. The timer interval is set by providing a non-zero value in minutes for the SQLNET.EXPIRE_TIME parameter in the sqlnet.ora file.
When the timer expires, SQL*Net on the server sends a "probe" packet to the client. The probe is an empty SQL*Net packet and does not represent any form of SQL*Net level data, but it creates data traffic on the underlying protocol.
If the client end of the connection is still active, the probe is discarded, and the timer mechanism is reset. If the client has terminated abnormally, the server will receive an error from the send call issued for the probe, and SQL*Net on the server will signal the operating system to release the connection's resources.
The client may be running any supported SQL*Net V2 release. DCD is more resource-intensive than similar mechanisms at the protocol level.
With DCD enabled, if the connection is idle for the duration of the time interval specified in minutes by the SQLNET.EXPIRE_TIME parameter, the Server-side process sends a small 10-byte packet to the client. This packet is sent using TCP/IP.
To Configure Dead Connection Detection (DCD)
changing SQLNET.EXPIRE_TIME = 1 (Minutes) to the sqlnet.ora file
With DCD enabled, if the connection is idle for the duration of the time interval specified in minutes by the SQLNET.EXPIRE_TIME parameter, the Server-side process sends a small 10-byte packet to the client. This packet is sent using TCP/IP.
If a timely acknowledgement is not received in response to the probe packet, the TCP/IP stack will retransmit the packet some number of times before timing out. After TCP/IP gives up, then SQL*Net receives notification that the probe failed.
If the client side connection is still connected and responsive, the client sends a response packet back to the database server, resetting the timer, and another packet will be sent when the next interval expires , assuming no other activity on the connection
If the client fails to respond to the DCD probe packet:
• The Server side process is marked as a dead connection and
• The database Process Monitor (PMON) performs the clean-up of the database processes / resources
Dead Connection Detection:
• DCD initiates clean up of OS and database processes that have disconnected / terminated abnormally
• DCD will not initiate clean-up for sessions that are still connected
Client side SQL*Net connections do not enable keepalive for TCP connections by default. However, it is possible to enable this by adding the ENABLE=BROKEN parameter to the SQL*Net connect string in the tnsnames.ora file on the server..
Sample TNS alias to enable keepalive (notice the ENABLE=BROKEN clause):
(LOAD_BALANCE = ON)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = rh8)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rh6)(PORT = 1521)))
For more details, check out Mike's paper Parallel Concurrent Processing Failover and Load Balancing of E-Business Suite Release 11i and Release 12
Tuesday, June 9. 2009
ORA-04031 Errors and Monitoring ... Posted by Barb Matthews in 11g at 07:00
ORA-04031 Errors and Monitoring Shared Pool Subpool Memory Utilization with sgastatx.sql by Tanel Poder
Don't miss Tanel Poder's upcoming seminar Advanced Oracle Troubleshooting Seminar, to be held in Denver June 15-17. If you're on the fence about whether to take this intensive class, take a look at some class feedback.
Since Oracle 9.2 the shared pool can be “partitioned” into multiple parts. This was probably done for relieving shared pool latch contention for crappy applications (which use shared pool latches too much due bad cursor or connection management).
The “partitions” are called shared pool subpools and there can be up to 7 subpools. Each subpool is protected by a separate shared pool latch and each subpool has its own freelists and LRU list. If you are interested in more details, a good starting point is this whitepaper by Oracle.
There are a few different ways for detecting how many subpools you have in use. The more convenient ones are here:
You could query X$KGHLU which has a line for each shared pool subpool and (from 10g) also java pool if it’s defined:
SQL> select count(distinct kghluidx) num_subpools
2 from x$kghlu
3 where kghlushrpool = 1;
The “kghlushrpool” column, which is 1 for shared pool subheaps and 0 for java pool, isn’t there in 9i (and in 9i the java pool apparently is not reported in x$kghlu anyway).
The reason I don’t just count all matching lines from x$kghlu but use count distinct instead is that in Oracle 10.2.0.1 there are 4x more lines reported in this x$table. There’s an additional concept called sub-sub-pool starting from 10.2 where each shared pool sub-pool is split futher into 4 areas (allocations with different expected lifetime/durations go into different sub-sub-pools, but the same sub-pool latch protects all activity in sub-sub pools too). But in 10.2.0.1 the x$kghlu reports all sub-sub-pools too for some reason. The whitepaper from Oracle mentioned above explains this in more detail.
So from above output I see that in my instance all 7 shared pool subpools are in use. Oracle determines the number of needed subpools (during instance startup) based on your shared pool size and cpu_count. IIRC in 9.2 if you had 4 CPUs or more AND the shared_pool_size was bigger than 256 MB then 2 subpools were used, in 10g shared_pool_size had to be bigger for that, 512 MB I think and in 11g its 1GB. I don’t recall the exact threshold values and that’s not really important as you can see yourself how many subpools are in use with the above query.
For the sake of this experiment, I set the _kghdsidx_count variable to 7, this parameter can be used to force the number of subpools you want. In 9.2 days it was actually quite common to set this back to 1 IF you had ORA-4031 errors AND the reason was diagnosed to be free space imbalance between subpools. However since 10g this has been almost unnecessary as Oracle has improved their heap management algorithms.
SQL> @pd kghdsidx
NAME VALUE DESCRIPTION
--------------------------------------------- ------------------------------ ------------------
_kghdsidx_count 7 max kghdsidx count
So far the two above approaches have required access to X$ tables which usually means you need to be logged on as SYSDBA. What if you don’t have such access?
In such case you can work this out pretty reliably by looking into how many of the shared pool latches are actually in use. All 7 latches are always there, even if you have less subpools in use, that number is hardcoded into Oracle. But you can see how many latches have a significant number of gets against them.
In my case it's evident that all latches are in use; they all have a significant number of gets against them:
SQL> select child#, gets
2 from v$latch_children
3 where name = 'shared pool'
4 order by child#;
7 rows selected.
It’s ok to see some latch gets against the latches of unused subheaps, but this number should be much much smaller than others. The reason appears to be that all subheap latches are taken when shared pool is allocated and when shared pool resize operations are done.
For example, this is what I see after setting the number of shared pool subpools to 2 in my test database (and running some hard parsing workload):
And now to the troubleshooting part!
Note that this article doesn’t aim to explain all the basics of ORA-4031 troubleshooting, I’ll talk about the subpool utilization imbalance problem only. If you haven’t read MetaLink Note 396940.1 - “Troubleshooting and Diagnosing ORA-4031 Error” yet, I recommend to do this first and then read my comments here.
As you know, ORA-4031 errors look like this:
ORA-04031: "unable to allocate n bytes of shared memory ("shared pool", "object_name", "alloc type(2,0)" ...)
“n” shows how many bytes we tried to allocate when ended up with the failure. Italic strings can show various different values but essentially they’re just some metadata describing for what did we try to allocate that memory.
Note the two bold pieces. The “shared pool” means that we tried to make the allocation from shared pool (if you have problems with other pools you can see there “large pool”, “streams pool”, “java pool” as well).
The “2″ in “(2,0)” means that the failure happened in shared pool sub pool number 2 and the “0″ shows sub-sub-pool number 0.
Sometimes the error happens just due heavily undersized shared pool (combined bad cursor management or some incorrect parameter values). In such cases you would see the shared pool free memory drop to near-zero in V$SGASTAT.
However, sometimes you can have ORA-4031’s even when you see plenty of free space available in V$SGASTAT. What’s the issue with that?
This case happens mainly for two reasons:
1) Shared pool free memory fragmentation
There is no big enough free chunk available even after flushing out unpinned chunks from LRU list. In other words, you have a lot of small free chunks scattered around in different places in shared pool but there is no single big enough chunk available for acommodating our allocation. I will talk about troubleshooting this problem in a separate post.
2) Unbalanced memory usage / free memory in different shared pool subpools
This is what I’m explaining in this post.
So, how to monitor which subpool has how much free memory available?
V$SGASTAT unfortunately just shows a sum of all subpools:
SQL> select from v$sgastat
2 where pool = 'shared pool'
3 and name = 'free memory';
POOL NAME BYTES
------------ -------------------------- ----------
shared pool free memory 188017360
However, when we look into the source code of GV$SGASTAT we see this (output edited for readability):
SQL> @v gv$sgastat
SQL> desc x$ksmss
There’s an interesting column, KSMDSIDX column - and it’s also what I was looking for! This column contains the subpool number in it!
Before I query by that column, remember that I had set the number of subpools back to 2 in my test instance:
SQL> @pd kghdsidx
Ok, lets see what values we have in that column:SQL> select distinct ksmdsidx from x$ksmss;
Hmm… 1 and 2 look ok as I have subpool 1 and 2 defined in the instance, but why is there a subpool 0 also reported? (from 10g anyway).
This is due to a little feature in Oracle. When you start the instance in 10g, then not all memory reserved for shared pool is immediately given to subpool heaps. Some memory is reserved for individual subpool growth. This allows some subpools to grab more memory than others if they have more allocations after instance startup. This may be useful in cases where due some specific issue some subpool always needs much more memory than others. On the other hand, I have not seen a subpool heap give memory back to some other subpool so if one subpool allocates all of the reserved memory after instance start due some application startup activity, then the other pools may remain too small for the whole lifetime of the instance.
So, if you have ORA-4031 out of shared pool memory errors or suspect that shared pool memory pressure is the cause of some performance problem (like shared pool latch contention and excessive library cache evictions/reloads) then you’d want to monitor shared pool memory breakdown at the subheap level.
And (finally) I can introduce a little script sgastatx.sql which queries X$KSMSS and formats the output for better readability.
The script takes one parameter, what memory allocation reasons to report (% would report all):
I will start with “total” which just reports me the shared pool totals and doesn’t break down by allocation reason.
SQL> @sgastatx total
The bold part above is the total memory reserved for shared pool. The “0 - unused” is the not-yet-used-for-any-subheap part of the memory. And 1 & 2 are the allocations to subheap 1 and 2.
By the way I can confirm these numbers by querying v$sgainfo:
SQL> select from v$sgainfo;
SQL> select current_size from v$sga_dynamic_components where component = 'shared pool';
Continuing with the examples, usually I would be interested in seeing how much free memory each subpool has in it:
SQL> @sgastatx "free memory"
And when there’s not enough free memory in some subpool then you can run sgastatx with % parameter to report all memory users in that subpool. I removed some lines from output for brevity:SQL> @sgastatx %
Of course sometimes you’d want to know how the memory usage breakdown changes over time, for that you’d need to write a little collector script which dumps the data into some table and visualize it later on, like I have done for regular V$SGASTAT data with my PerfSheet tool
« previous page (Page 1 of 1, totaling 9 entries) next page »
Syndicate This Blog
Steven Chan about Collaborate 10 Database SIG Meeting by Barbara Matthews
Sun, 09.05.2010 23:52
Barb, you take amazing notes. Thanks for sharing them with the members of our EBS communi ty who couldn't make it [...]
Jack about Installations Gone Wild - Guest Author Lon White
Fri, 04.09.2009 11:06
Lon, I am getting same RW-2 0019 error while installing Or acle Release 12 on Linux. I ha ve setup my network as D [...]
Dave Hiller about OAM Patch Search Responsibility - Guest Author, Kathy Duret
Mon, 09.02.2009 10:36
This is extremely helpful!