Oracle Performance_TB3
ORACLE+ERP常用TABLE说明
ORACLE ERP常用TABLE说明mtl_onhand_locator_v(库存数据视图) (2)inv.mtl_system_items(物料主表) (2)ont.oe_order_headers_all(订单头) (6)po.po_lines_all(采购订单行) (8)apps.fnd_user(MRPII用户表) (9)hr.per_people_f(职工姓名表) (9)hr.hr_locations(交货位置表) (10)hwcust.hw_oe.products(产品编码表) (10)e.ra_customers(客户表) (10)wip.wip_accounting_classes(离散作业会计科目) (10)wip.wip_comment_codes(离散作业注释) (11)wip.wip_discrete_jobs(离散作业表) (11)wip.wip_entities(任务令信息表) (13)wip.wip_requirement_operations(任务令物料需求发放表) (14)wip.wip_operations(离散作业操作表) (14)wip.wip_operation_resources(离散作业操作资源) (15)wip.wip_transactions(离散作业交易表) (16)wip.wip_transaction_accounts(离散作业交易帐目) (17)wip.wip_scrap_values(离散作业报废价值表) (18)wip.wip_shop_floor_status_codes(场地状态表) (18)wip.wip_move_transactions(离散作业工序移动交易) (19)wip.wip_so_allocations(分配表) (19)wip.wip_valid_intraoperation_steps(有效的进入工序步骤) (20)bom.bom_bill_of_materials(BOM清单父项目) (20)bom.bom_inventory_components(BOM清单构成项目) (21)bom.bom_assembly_comments(装配件状态表) (21)bom.bom_standard_comments(标准注释表(状态说明表)) (22)bom.bom_calendars(日历种类表) (22)bom.bom_calendar_dates(工作日历表) (22)bom.bom_calendar_exceptions(日历例外信息) (22)bom.bom_calendar_shifts(倒班表) (23)bom.bom_shift_dates(轮班表) (23)bom.bom_cal_week_start_dates(星期开始日期) (23)bom.bom_period_start_dates(周期开始日期) (24)bom.bom_workday_patterns(工作日模式表) (24)bom.bom_exception_sets(BOM例外集表) (24)bom.cst_item_costs(项目成本表) (25)bom.cst_cost_types(成本类型表) (25)bom.cst_item_cost_details(项目成本明细表) (26)bom.cst_cost_elements(成本要素代码表) (26)bom.cst_elemental_costs(项目要素成本) (27)bom.cst_resource_costs(资源成本) (27)bom.cst_standard_costs(项目标准成本表) (27)bom.cst_std_cost_adj_values(标准成本调节表) (28)bom.bom_departments(部门代码对应表) (29)bom.bom_resources(资源代码对应表) (29)bom.bom_operational_routings(工艺路线) (30)bom.bom_operation_resources(工艺路线细节之资源表) (30)bom.bom_operation_sequences(工艺路线细节之工艺工序表) (30)bom.bom_standard_operations(标准操作表) (31)bom.bom_std_op_resources(标准操作资源表) (31)bom.bom_substitute_components(BOM替代件表) (32)mrp.mrp_assembly_operations(计划装配实施表) (32)mrp.mrp_forecast_items(预测项目表) (33)mrp.mrp_forecast_designators(预测名称表) (33)mrp.mrp_forecast_dates(预测表) (33)mrp.mrp_forecast_updates(预测更改) (34)mrp.mrp_gross_requirements(毛需求) (34)mrp.mrp_item_purchase_orders(MRP采购计划) (35)mrp.mrp_item_wip_entities(MRP项目离散作业) (37)mrp.mrp_plans(计划表) (37)mrp.mrp_recommendations (计划建议(含例外信息)) (38)mtl_onhand_locator_v(库存数据视图)常用视图表名：mtl_onhand_locator_v说明：库存数据视图列名类型含义inventory_item_id number 项目内码organization_id number 组织代码padded_concatenated_segments 物品编码revision varchar2(3) 物品版本total qoh number 现有量subinventory_code varchar2(10) 子库名locator_id number 货位内码inv.mtl_system_items(物料主表)inv模块常用表结构表名：inv.mtl_system_items说明：物料主表列名类型含义organization_id number 组织代码inventory_item_id number 项目内码segment1 varchar2(40) 项目编码（外码）unit_of_issue 单位description 项目说明allowed_units_lookup_code 可否单位换算inventory_item_status_code 项目状态item_type 项目类型primary_unit_of_measure 基本度量单位base_item_id 基础代码bom_enabled_flag bom使能标志(项目能否有清单)(y/n) bom_item_type bom项目类型engineering_ecn_code 工程更改号engineering_item_id 工程项目代码eng_item_flag 是否是工程项目costing_enabled_flag 成本始能标志(有无成本)cost_of_sales_account 货物销售帐目default_include_in_rollup_flag 是否可为缺省inventory_asset_flag 是否是库存资产std_lot_size 标准批量规格allow_item_desc_update_flag 是否可更新项目说明asset_category_id 资产类别buyer_id 采购员代码encumbrance_account 无效帐目expense_account 费用帐户hazard_class_id 风险类代码list_price_per_unit 单位定价market_price 市价outside_operation_flag 外部操作项目outside_operation_uom_type 外部操作单位类型price_tolerance_percent 允许价格偏差百分比purchasing_enabled_flag 是否可采购purchasing_item_flag 采购项目rounding_factor a 取舍因子taxable_flag 税收项目unit_of_issue 单位allow_express_delivery_flag a 可以快递项目allow_substitute_receipts_flag 收据是否可以替换days_early_receipt_allowed 可以提前天数days_late_receipt_allowed 可以推迟天数enforce_ship_to_location_code 目的地invoice_close_tolerance 发票结束偏差qty_rcv_exception_code 例外接收数量代码qty_rcv_tolerance 例外接收允许量receipt_required_flag a 是否必须收据receive_close_tolerance a 接收结束允许天数receiving_routing_id 接收行程安排编码auto_serial_alpha_prefix starting serial prefix cycle_count_enabled_flag 能否循环计算inventory_item_flag 是否为库存项目location_control_code 采购点控制代码lot_control_code 是否有批量控制mtl_transactions_enabled_flag 可否交易positive_measurement_error pos measurement error reservable_type 是否要预留restrict_locators_code 是否限制定位revision_qty_control_code 是否受修订版本控制serial_number_control_code 是否受系列号控制shelf_life_code 有无存储期控制shelf_life_days 存储期天数start_auto_lot_number 开始批量start_auto_serial_number 开始系列号stock_enabled_flag 能否库存unit_volume 体积unit_weight 重量volume_uom_code 体积度量单位weight_uom_code 重量度量单位carrying_cost 运输费用百分比fixed_days_supply 固定提前期fixed_lot_multiplier 固定批量大小fixed_order_quantity 固定定单数inventory_planning_code 库存计划方法maximum_order_quantity 最大定单数max_minmax_quantity min-max maximum quantity minimum_order_quantity 最小定单数min_minmax_quantity min-max minimum quantity mrp_safety_stock_code 安全库存mrp_safety_stock_percent 安全库存百分比order_cost 定单费用planner_code 计划员属性safety_stock_bucket_days 安全库存天数source_organization_id 来源组织代码source_subinventory 来源子库存source_type 补充来源类型acceptable_early_days 接收提前天数auto_reduce_mps 自动冲减mpsdemand_time_fence_code 需求时间栏demand_time_fence_days 需求时间栏天数mrp_calculate_atp_flag 是否有atpmrp_planning_code 计划方法overrun_percentage 超差百分比planning_make_buy_code 制造或购买planning_time_fence_code 计划时间栏planning_time_fence_days 计划时间栏天数repetitive_planning_flag 是否是重复计划shrinkage_rate 收缩比率cumulative_total_lead_time 累积总体提前期cum_manufacturing_lead_time 制造提前期full_lead_time 处理提前期lead_time_lot_size 批量提前期preprocessing_lead_time 预处理提前期variable_lead_time 不定的提前期build_in_wip_flag 是否在制wip_supply_locator_id 离散作业库存地点wip_supply_type 离散作业供货类型atp_components_flag 是否有atp组成部件atp_flag 是否为atp项目atp_rule_id atp规则collateral_flag 是否为附属项目customer_order_flag 是否为定单项目default_shipping_org 运输组织代码internal_order_enabled_flag 是否可以是内部定单internal_order_flag 是否为内部定单picking_rule_id 库存规则pick_components_flag 是否为可选部件replenish_to_order_flag 是否为定单装配returnable_flag 是否可退回return_inspection_requirement rma inspection status shippable_item_flag 是否为运输项目ship_model_complete_flag 运送是否完成so_transactions_flag 是否可交易accounting_rule_id 会计规则invoiceable_item_flag 是否为需发票项目invoice_enabled_flag 是否具有发票invoicing_rule_id 发票规则payment_terms_id 付款期限sales_account 销售帐目tax_code 税收代码ato_forecast_control 预测控制must_use_approved_vendor_flag 是否必须授权供应商un_number_id 货期allow_unordered_receipts_flag 是否为非定单收据inspection_required_flag 是否需检查receipt_days_exception_code 接收天数例外代码restrict_subinventories_code 子库存限制acceptable_rate_decrease 可接受减少率end_assembly_pegging_flag 是否是最终装配planning_exception_set 计划例外设置rounding_control_type 四舍五入控制fixed_lead_time 固定提前期postprocessing_lead_time 后处理提前期wip_supply_subinventory 离散作业子库存customer_order_enabled_flag 是否是销售定单acceptable_rate_increase 可接受的增加率ont.oe_order_headers_all(订单头)oe模块常用表结构so_headers_all；so_lines_all；表名:ont.oe_order_headers_all说明:订单头header_id number 订单头标识码org_id number ou idorder_type_id number 订单类型idorder_number number 订单编号ordered_date date 定购日期request_date date 客户要求发货日期demand_class_code varchar2(30) 需求分类codeprice_list_id number 价目表idtransactional_curr_code varchar2(15) 币种cust_po_number varchar2(50) 合同号invoicing_rule_id number(15) 开票规则id accounting_rule_id number(15) 会计规则id payment_term_id number(15) 付款条件id shipping_method_code varchar2(30) 发运方法code fob_point_code varchar2(30) fob codefreight_terms_code varchar2(30) 运费条款code ship_from_org_id number 发货库存组织idship_to_org_id number 客户account_idinvoice_to_org_id number 开票客户account_id sold_to_contact_id number 经办人idship_to_contact_id number 发货至联系人id invoice_to_contact_id number 开票至联系人id creation_date date 输入日期created_by number 输入人idlast_updated_by number 更新人last_update_date date 更新日期last_update_login number 更新人用户attribute1 date 合同收到日期attribute2 date 客户要求到货日期attribute3 varchar2(240) 到货视同验收attribute11 varchar2(240) 客户行业分类code attribute12 varchar2(240) 合同文本金额attribute13 varchar2(240) 是否需要客服到货确认attribute14 date 承诺核销日期cancelled_flag varchar2(1) 是否取消open_flag varchar2(1) 是否未结booked_flag varchar2(1) 登记与否salesrep_id number(15) 销售员idreturn_reason_code varchar2(30) 退货原因code sales_channel_code varchar2(30) 销售渠道code order_category_code varchar2(30) 订单类别shipping_instructions varchar2(2000) 老合同号packing_instructions varchar2(2000) 包装指令flow_status_code varchar2(30) 工作流状态代码booked_date date 下单日期po.po_lines_all(采购订单行)表名: po.po_lines_all说明: 采购订单行po_line_id number 行标识码po_header_id number 订单头标识码line_type_id number 行类型line_num number 行栏目item_id number 项目内码item_revision varchar2(3) 项目版本category_id number 归类标识码item_description varchar2(240) 项目描述unit_meas_lookup_code varchar2(25) 单位quantity_committed number 提交数量committed_amount number 提交金额allow_price_override_flag varchar2(1) 价格佣金允许not_to_exceed_price number 不允许超过价格list_price_per_unit numberunit_price number 单价quantity number 数量un_number_id number 货期标识码hazard_class_id number 风险类别标识码note_to_vendor varchar2(240) 对供应商的说明from_header_id number 来源订单头号from_line_id number 来源行号min_order_quantity number 最小订单数量max_order_quantity number 最大订单数量qty_rcv_tolerance number 可接收数量over_tolerance_error_flag varchar2(25) 超差示错标志market_price number 市价unordered_flag varchar2(1) 不定货标志closed_flag varchar2(1) 关闭标志user_hold_flag varchar2(1)cancel_flag varchar2(1) 取消标志cancelled_by number(9) 取消者cancel_date date 取消日期cancel_reason varchar2(240) 取消原因firm_status_lookup_code varchar2(30)firm_date datetaxable_flag varchar2(1) 应税标志tax_name varchar2(15) 税种capital_expense_flag varchar2(1) 大写金额标志negotiated_by_preparer_flag varchar2(1)closed_date date 关闭日期closed_reason varchar2(240) 关闭原因transaction_reason_code varchar2(25) 交易原因attribute10 varchar2(150) 厂家attribute11 varchar2(150) 型号apps.fnd_user(mrpii用户表)其它字典表表名：apps.fnd_user说明:mrpii用户表user_id number 用户iduser_name varchar2 用户名称employee_id number 使用人id(可与hr.per_people_f.person_id关联)hr.per_people_f(职工姓名表)表名:hr.per_people_f说明:职工姓名表person_id number 职工id号last_name varchar2 姓名full_name varchar2 姓名(带称呼mr./miss./mrs.)date_of_birth date 生日employee_number varchar2 工号sex varchar2 性别(m/f)title varchar2 称呼(mr./miss./mrs)hr.hr_locations(交货位置表)表名:hr.hr_locations说明:交货位置表location_id number id号location_code varchar2 名称address_line_1 varchar2 地址postal_code varchar2 邮编receiving_site_flag varchar2 可否接收tax_name varchar2 税种telephone_number_1 varchar2 电话1telephone_number_2 varchar2 电话2town_or_city varchar2 城市hwcust.hw_oe.products(产品编码表)表名: hwcust.hw_oe.products说明: 产品编码表product_code varchar2(20) 产品代码product_classes varchar2(30) 产品类别product_sub_classes varchar2(16) 产品子类product_name varchar2(30) 产品名称unit_of_measure varchar2(4) 单位start_date_active date 开始时间end_date_active date 结束时间status varchar2(1) 状态e.ra_customers(客户表)表名e.ra_customers说明:客户表customer_id number 客户idcustomer_number varchar2 客户代码customer_name varchar2 客户名称wip.wip_accounting_classes(离散作业会计科目) wip模块常用表结构表名: wip.wip_accounting_classes说明: 离散作业会计科目class_code varchar2(10) 帐目organization_id number 组织代码class_type number 帐目类型description varchar2(240) 描述disable_date date 失效日期material_account number 物料帐目material_variance_account number 物料差异帐目material_overhead_account number 物料经常费用帐目resource_account number 资源帐目resource_variance_account number 资源差异帐目outside_processing_account numberoutside_proc_variance_account numberoverhead_account number 经常费用帐目overhead_variance_account number 经常费用差异帐目std_cost_adjustment_account number 标准成本调节帐目completion_cost_source number 完成成本来源cost_type_id number 成本类型bridging_account number 过渡帐目wip.wip_comment_codes(离散作业注释)表名: wip.wip_comment_codes说明: 离散作业注释comment_code varchar2(10) 注释organization_id number 组织代码comment_description varchar2(240) 说明wip.wip_discrete_jobs(离散作业表)表名: wip.wip_discrete_jobs说明: 离散作业表wip_entity_id number 任务令内码organization_id number 组织代码source_line_id number 来源行号source_code varchar2(20) 来源description varchar2(240) 说明status_type number 状态primary_item_id number 加工件内码firm_planned_flag numberjob_type number 作业类型(1.标准,3.非标准)wip_supply_type number 供应类型class_code varchar2(10) 任务类别material_account number 物料帐目material_overhead_account number 物料经常费用帐目resource_account number 资源帐目outside_processing_account number 外(超)处理帐目material_variance_account number 物料差异帐目resource_variance_account number 资源差异帐目outside_proc_variance_account number 外(超)处理差异帐目std_cost_adjustment_account number 标准成本调整帐目overhead_account number 经常性费用帐目overhead_variance_account number 经常性费用差异帐目scheduled_start_date date 计划开工时间date_released date 任务令释放时间scheduled_completion_date date 计划完工时间date_completed date 完工时间date_closed date 关门时间start_quantity number 开始数量quantity_completed number 完工数量quantity_scrapped number 报废数量net_quantity number 净数量bom_reference_id number bom参考标识码routing_reference_id number 工艺路线参考标识码common_bom_sequence_id number 公共bom序号common_routing_sequence_id number 公共工艺路线序号bom_revision varchar2(3) bom版本routing_revision varchar2(3) 工艺路线版本bom_revision_date date bom版本日期routing_revision_date date 工艺路线版本日期lot_number varchar2(30) 批量alternate_bom_designator varchar2(10) 可替换bomalternate_routing_designator varchar2(10) 可替换工艺路线completion_subinventory varchar2(10) 完工应入子库completion_locator_id number 完工应入货位mps_scheduled_completion_date date mps计划完工时间mps_net_quantity number mps净数量demand_class varchar2(30) 需求类别schedule_group_id number 计划组标识码build_sequence numberline_id number 行号其中status_type 1 未发放的-收费不允许3 发入-收费允许4 完成-允许收费5 完成-不允许收费6 暂挂-不允许收费7 已取消-不允许收费8 等待物料单加载9 失败的物料单加载10 等待路线加载11 失败的路线加载12 关闭-不可收费13 等待-成批加载14 等待关闭15 关闭失败wip_supply_type 1 推式2 装配拉式3 操作拉式4 大量5 供应商6 虚拟7 以帐单为基础wip.wip_entities(任务令信息表)表名: wip.wip_entitieswip_entity_id number 任务令内码organization_id number 组织代码wip_entity_name varchar2(240) 任务令名称entity_type number 任务类型description varchar2(240) 说明primary_item_id number 装配件内码wip.wip_requirement_operations(任务令物料需求发放表) 表名: wip.wip_requirement_operations说明: 任务令物料需求发放表inventory_item_id number 物料项目内码organization_id number 组织代码wip_entity_id number 任务令内码operation_seq_num number 工序号repetitive_schedule_id number 重复计划标识码component_sequence_id number bom清单构成件序号department_id number 部门标识码wip_supply_type number 供应类型date_required date 要求日期required_quantity number 要求数量quantity_issued number 发放数量quantity_per_assembly number 单位需求量comments varchar2(40) 注释supply_subinventory varchar2(10) 供应子库supply_locator_id number 供应货位mrp_net_flag number 净mrp标志mps_required_quantity number mps要求数量mps_date_required number mps要求日期segment1 物料项目编码(外码)wip.wip_operations(离散作业操作表)表名: wip.wip_operationswip_entity_id number 任务令内码operation_seq_num number 工序号organization_id number 组织代码repetitive_schedule_id number 重复计划代码operation_sequence_id number 操作序号standard_operation_id number 标准操作序号department_id number 部门标识代码description varchar2(240) 描述scheduled_quantity number 计划数量quantity_in_queue number 排队数量quantity_running number 运行数量quantity_waiting_to_move number 待移动数量quantity_rejected number 故障品数量quantity_scrapped number 报废品数量quantity_completed number 完工数量first_unit_start_date date 最早一个单位上线时间first_unit_completion_date date 最早一个单位完成时间last_unit_start_date date 最后一个单位上线时间last_unit_completion_date date 最后一个单位完工时间previous_operation_seq_num number 前一工序序号next_operation_seq_num number 下一工序序号count_point_type number 计数点(1.是--自动收费,2.否) backflush_flag number 倒冲库存标识(1.是,2.否) minimum_transfer_quantity number 最小传送数量date_last_moved date 最后移动时间wip.wip_operation_resources(离散作业操作资源) 表名: wip.wip_operation_resources说明: 离散作业操作资源表wip_entity_id number 任务令内码operation_seq_num number 工序号resource_seq_num number 资源序号organization_id number 组织代码repetitive_schedule_id number 重复计划代码resource_id number 资源代码uom_code varchar2(3) 单位basis_type number 基本类型(1.项目,2.批量)usage_rate_or_amount number 利用率或金额activity_id number 活动(1.运行,4.移动,5.排队)scheduled_flag number 计划标志assigned_units nubmer 已分配单位autocharge_type number 自动记帐(1.wip移动,2.人工,3.po接收,4.po移动) standard_rate_flag number 标准费用率标志(1.否,2.是)applied_resource_units number 已应用单位applied_resource_value number 已应用值start_date date 开始日期completion_date date 完成日期wip.wip_transactions(离散作业交易表)表名: wip.wip_transactions说明: 离散作业交易表transaction_id nubmer 交易代码organization_id nubmer 组织代码wip_entity_id nubmer 任务令内码primary_item_id nubmer 加工件内码acct_period_id nubmerdepartment_id nubmer 部门内码transaction_type nubmer 交易类型*transaction_date date 交易日期group_id nubmer 组号source_code varchar2(30) 来源source_line_id nubmer 来源行号operation_seq_num nubmer 工序号resource_seq_num nubmer 资源序号employee_id nubmer 雇员标识号resource_id nubmer 资源标识号autocharge_type nubmer 自动记帐(1.wip移动,2.人工,3.po接收,4.po移动) standard_rate_flag nubmer 标准费用率标志(1.否,2.是)usage_rate_or_amount nubmer 利用率或金额basis_type nubmer 基本类型(1.项目,2.批量)transaction_quantity nubmer 交易数量transaction_uom varchar2(3) 交易单位primary_quantity nubmer 基本数量primary_uom varchar2(3) 基本单位actual_resource_rate nubmer 资源实际利用率standard_resource_rate nubmer 标准资源可用率currency_code varchar2(15) 货币代码currency_conversion_date date 货币兑换日期currency_conversion_type varchar2(10) 货币兑换类型currency_conversion_rate nubmer 汇率currency_actual_resource_rate nubmer 按该币种计算的资源实际利用率activity_id nubmer 活动(1.运行,4.移动,5.排队)reason_id nubmer 原因代码reference varchar2(240) 参考move_transaction_id nubmer 移动交易代码po_header_id nubmer 采购订单头po_line_id nubmer 采购订单行号rcv_transaction_id nubmer 接收交易代码(采购订单到货)注:transaction_type取值:1.资源交易,2.制造费交易,3.外部处理,4.成本更新,5.周期关闭差异,6.作业关闭差异wip.wip_transaction_accounts(离散作业交易帐目)表名: wip.wip_transaction_accounts说明: 离散作业交易帐目organization_id number 组织代码transaction_id number 交易代码reference_account number 参考科目transaction_date date 交易日期wip_entity_id number 任务令内码repetitive_schedule_id number 重复计划代码accounting_line_type number 会计栏类型transaction_value number 交易价值base_transaction_value number 基本交易价值contra_set_id number 反方集代码primary_quantity number 基本数量rate_or_amount number 率或金额basis_type number 基本类型(1.项目,2.批量) resource_id number 资源代码cost_element_id number 成本要素activity_id number 活动(1.运行,4.移动,5.排队) currency_code varchar2(15) 币种currency_conversion_date date 汇率日期currency_conversion_type varchar2(10) 汇率类型currency_conversion_rate number 汇率overhead_basis_factor number 基本经常费用因子basis_resource_id number 基本资源代码gl_batch_id number 财务批号wip.wip_scrap_values(离散作业报废价值表)表名: wip.wip_scrap_values说明: 离散作业报废价值表transaction_id number 交易代码level_type number 级别类型cost_element_id number 成本要素cost_element_value number 要素值wip.wip_shop_floor_status_codes(场地状态表) 表名: wip.wip_shop_floor_status_codes说明: 场地状态表shop_floor_status_code varchar2(10) 场地状态organization_id number 组织description varchar2(240) 描述disable_date date 失效日期status_move_flag number 状态移动标志wip.wip_move_transactions(离散作业工序移动交易) 表名: wip.wip_move_transactions说明: 离散作业工序移动交易transaction_id number 交易代码group_id number 组号source_code varchar2(30) 资源source_line_id number 资源行号organization_id number 组织代码wip_entity_id number 任务令内码line_id number 行号primary_item_id number 加工件内码transaction_date date 交易时间acct_period_id number 会计周期fm_operation_seq_num number 从工序号fm_operation_code varchar2(4) 从工序fm_department_id number 从部门fm_intraoperation_step_type numberto_operation_seq_num number 到工序号to_operation_code varchar2(4) 到工序to_department_id number 到部门to_intraoperation_step_type numbertransaction_quantity number 交易数量transaction_uom varchar2(3) 交易单位primary_quantity number 基本数量primary_uom varchar2(3) 基本单位scrap_account_id number 报废记帐标识号reason_id number 原因标识号reference varchar2(240) 参考wip.wip_so_allocations(分配表)表名：wip.wip_so_allocations说明: 分配表allocation_id number 分配标识码wip_entity_id number 任务令内码organization_id number 组织代码demand_source_header_id number 需求源头标识码demand_source_line varchar2(30) 需求源行号user_line_num varchar2(30) 用户栏目号demand_source_delivery varchar2(30) 需求源投递号user_delivery varchar2(30) 用户投递号quantity_allocated number 已分配数量quantity_completed number 已完成数量demand_class varchar2(30) 需求类别wip.wip_valid_intraoperation_steps(有效的进入工序步骤)表名: wip.wip_valid_intraoperation_steps说明: 有效的进入工序步骤organization_id number 组织代码record_creator varchar2(6) 记录创建者(system/user)step_lookup_type number 步骤类型last_update_date date 最后更改日期step_meaning varchar2(10) 步骤含义bom.bom_bill_of_materials(bom清单父项目)bom模块常用表结构表名: bom.bom_bill_of_materials说明: bom清单父项目bill_sequence_id number 清单序号（关键字）assembly_item_id number 装配件内码organization_id number 组织代码assembly_type number 装配类别specfiic_assembly_comment varchar2(240) 注释（装配件状态p、r等）common_organization_id number 公共组织common_bill_sequence_id number 公共序号common_assembly_item_id number 公共项目内码bom.bom_inventory_components(bom清单构成项目)表名：bom.bom_inventory_components说明：bom清单构成项目component_sequence_id number 构件序号bill_sequence_id number 清单序号operation_seq_num number 操作序列号component_item_id numberitem_num number 项目序列号component_quantity number 构件数量component_yield_factor number 产出因子effectivity_date date 生效日期disable_date date 失效日期change_notice varchar2(10) 更改注释(eco更改号)planning_factor number 计划百分比quantity_related number 相关数量so_basis numberoptional number 是否可选(1.是;2.否)mutually_exclusive_options number 互斥标识(1.是;2.否)wip_supply_type number 车间供应类型(1.推式2.装配拉式3.操作拉式4.大量5.供应商6.虚拟)supply_subinventory 供应子库存supply_locator_id 供应货位idbom_item_type number 清单项目类型(1.模型2.选项类3.计划中4.标准) include_on_ship_docs number 包含在发运文档中(1.是;2.否)required_for_revenue number 需要进款(1.是;2.否)required_to_ship number 需要发运(1.是;2.否)shipping allowed number 搬运允许(1.是;2.否)include_in_cost_rollup number 计入成本累加中(1.是;2.否)check_atp number atp检查(1.是;2.否)bom.bom_assembly_comments(装配件状态表)表名：bom.bom_assembly_comments说明：装配件状态表bill_sequence_id number 清单序号standard_remarks_designator varchar2(10) 状态标志(r,p,d_r等)bom.bom_standard_comments(标准注释表(状态说明表)) 表名: bom.bom_standard_comments说明: 标准注释表(状态说明表)comment_code varchar2(10) 注释代码(状态代码) organization_id number 组织代码comment_text varchar2(240) 注释说明(状态说明)bom.bom_calendars(日历种类表)表名: bom.bom_calendars说明: 日历种类表calendar_code varchar2(10) 日历代码quarterly_calendar_type number 季度日历类型calendar_start_date date 日历启用日期calendar_end_date date 日历截止日期description varchar2(240) 描述bom.bom_calendar_dates(工作日历表)表名: bom.bom_calendar_dates说明: 工作日历表calendar_code varcahr2(10) 日历代码prior_date date 前一天calendar_date date 当日(当与前,后日期均相同时为工作日) next_date date 后一天prior_seq_num number 前一天序列号seq_num number 当日序序列号(非工作日为空)next_seq_num number 后一天序列号exception_set_id number 例外集代码bom.bom_calendar_exceptions(日历例外信息)表名: bom.bom_calendar_exceptions说明: 日历例外信息calendar_code varchar2(10) 日历代码exception_set_id number 例外集代码exception_date date 例外日期exception_type nubmer 例外类别bom.bom_calendar_shifts(倒班表)表名: bom.bom_calendar_shifts说明: 倒班表calendar_code varcahr2(10) 日历代码shift_num number 班次description varchar2(240) 描述bom.bom_shift_dates(轮班表)表名: bom.bom_shift_dates说明: 轮班表calendar_code varchar2(10) 日历代码exception_set_id number 例外集代码shift_num number 倒班序号shift_date date 倒班日期seq_num number 倒班序号next_seq_num number 下一班序号prior_seq_num number 上一班序号next_date date 下一次倒班日期prior_date date 上一次倒班日期bom.bom_cal_week_start_dates(星期开始日期) 表名: bom.bom_cal_week_start_dates说明: 星期开始日期calendar_code varchar2(10) 日历代码exception_set_id number 例外集代码seq_num number 序号week_start_date date 周开始日期prior_date date 上周开始日期next_date date 下周开始日期bom.bom_period_start_dates(周期开始日期)表名: bom.bom_period_start_dates说明: 周期开始日期calendar_code varchar2(10) 日历代码exception_set_id number 例外集代码period_start_date date 周期开始日期period_sequence_num number 周期序号period_name varchar2(3) 周期名称(月的三位英文缩写) next_date date 下一周期开始日期prior_date date 上一周期开始日期bom.bom_workday_patterns(工作日模式表)表名: bom.bom_workday_patterns说明: 工作日模式表calendar_code varchar2(10) 日历代码shift_num number 班次seq_num number 模式序号days_on number 工作天数days_off number 非工作天数description varchar2(240) 描述(大周/小周)bom.bom_exception_sets(bom例外集表)表名: bom.bom_exception_sets说明: bom例外集表exception_set_id number 例外集代码exception_set_name varchar2(10) 例外集名称description varchar2(240) 描述表内容:1 主要主要例外集2 hw_wdces 华为日历异常集3 mbc_wdces 莫贝克日历异常集-1 虚拟 10sc 兼容的空数据bom.cst_item_costs(项目成本表)表名：bom.cst_item_costs说明：项目成本表organization_id number 组织代码inventory_item_id number 项目内码cost_type_id number 成本类型item_cost number 项目成本material_cost number 物料成本resource_cost number 资源成本unburdended_cost number 不分摊成本burden_cost number 分摊成本inventory_asset_flag number 资产项目lot_size number 批量大小based_on_rollup_flag numbershrinkage_rate number 收缩比例defaulted_flag numberbom.cst_cost_types(成本类型表)表名: bom.cst_cost_types说明: 成本类型表cost_type_id number 成本类型代码cost_type varchar2(10) 成本类型description varchar2(240) 说明costing_method_type number 成本合计方法代码(1.2.) disable_date date 失效日期本表内容如下:cost_type cost_type_id description冻结 1 冻结标准成本类型平均 2 平均成本类型等待 3 等待标准成本类型标准成本 1000 标准成本实际成本 1001 实际成本新标准成本 1002 新标准成本bom.cst_item_cost_details(项目成本明细表)表名: bom.cst_item_cost_details说明: 项目成本明细表organization_id number 组织代码inventory_item_id number 项目内码item_cost number 项目成本cost_type_id number 成本类型department_id number 部门代码cost_element_id number 成本要素类型代码last_update_date number 最后更新日期operation_sequence_id number 操作序列代码operation_seq_num number 操作序列号level_type number 级别类型resource_seq_num number 资源序列号resource_id number 资源代码resource_rate number 资源比率usage_rate_or_amount number 利用率或金额basis_type number 基础类型basis_resource_id number 基础资源代码basis_factor number 基础因子net_yield_or_shrinkage_factor number 净产出或收缩比rollup_source_type numberbom.cst_cost_elements(成本要素代码表)表名: bom.cst_cost_elements说明: 成本要素代码表cost_element_id number 成本要素代码cost_element varchar2(50) 成本要素说明内容:1 物料的2 物料制造费3 资源4 外部处理5 制造费bom.cst_elemental_costs(项目要素成本)表名: bom.cst_elemental_costs说明: 项目要素成本cost_update_id number 成本更新代码organization_id number 组织代码inventory_item_id number 项目内码cost_element_id number 要素代码last_update_date date 最后更新日期standard_cost number 标准成本bom.cst_resource_costs(资源成本)表名: bom.cst_resource_costs说明: 资源成本resource_id number 资源代码cost_type_id number 成本类型organization_id number 组织代码resource_rate number 资源费率bom.cst_standard_costs(项目标准成本表)表名: bom.cst_standard_costs说明: 项目标准成本表cost_update_id number 成本更新代码inventory_item_id number 项目内码organization_id number 组织代码standard_cost_revision_date date 标准成本版本日期standard_cost number 标准成本inventory_adjustment_quantity number 库存调节数量。
Performance Diagnostic Plugin（批处理）用户指南：Oracle FLEX
Performance Diagnostic Plugin (Batch Processing) User GuideOracle FLEXCUBE Universal Banking Release 14.7.1.0.0Part No. F77194-01May 2023Performance Diagnostic Plugin (Batch Processing) User GuideOracle Financial Services Software LimitedOracle ParkOff Western Express HighwayGoregaon (East)Mumbai, Maharashtra 400 063IndiaWorldwide Inquiries:Phone: +91 22 6718 3000Fax: +91 22 6718 3001https:///industries/financial-services/index.htmlCopyright © 2007, 2023, Oracle and/or its affiliates. All rights reserved.Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are “commercial computer software” pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate failsafe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.This software or hardware and documentation may provide access to or information on content, products and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.Contents Contents 11. Preface ......................................................................................................1-11.1 Introduction.............................................................................................................. 1-11.2 Audience.................................................................................................................. 1-11.3 Documentation Accessibility.................................................................................... 1-11.4 Organization............................................................................................................ 1-11.5 Abbreviations........................................................................................................... 1-21.6 Glossary of Icons..................................................................................................... 1-21.7 Related Documents................................................................................................. 1-22. PDP (Batch) Functionality .......................................................................2-12.1 Introduction.............................................................................................................. 2-12.2 PDP (Batch) Maintenance....................................................................................... 2-12.2.1 Invoking PDP Maintenance Screen – Batch Processing............................ 2-12.2.1.1Viewing PDP Summary Screen – Batch 22.3 EOC Run................................................................................................................. 2-22.4 Results..................................................................................................................... 2-42.4.1 TKPROF..................................................................................................... 2-42.4.2 HPROF....................................................................................................... 2-43. Error Code and Messages ......................................................................3-13.1 Error Codes............................................................................................................. 3-14. FAQ’s ........................................................................................................4-15. Function ID Glossary ...............................................................................5-11. Preface1.1 IntroductionThis manual is been designed to help you quickly get acquainted with the Performance Diagnostic Plugin (Batch Processing) of Oracle FLEXCUBE Universal Banking.1.2 AudienceThis manual is intended for the following User/User Roles:1.3 Documentation AccessibilityFor information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at /pls/topic/lookup?ctx=acc&id=docacc .1.4 OrganizationThis manual is organized as follows:RoleFunction Database AdministratorTo provide the necessary Execute grants for TKPROF & HPROF file generation.End of Day operatorsProcessing during End of Day/ Begin -ning of Day System Technical Analyst To analyze the Performance lag issuefaced by bank user.Oracle FLEXCUBE userAny user of Oracle FLEXCUBE facingperformance issues in the system ChapterDescription Chapter 1Preface gives information on the intended audience. It also lists the various chapters covered in this User Manual.Chapter 2Functionality and Maintenance of various parameters for Perfor -mance Diagnostic Plugin - Batch Processing Chapter 3Error Code and Message Chapter 4Frequently Asked Question’s (FAQ’s)Chapter 5Function ID Glossary1.5 Abbreviations1.6 Glossary of IconsThis User Manual refers to the following icons.1.7 Related DocumentsFor further information on procedures discussed in the manual, refer to the Oracle Database manuals on:●Enabling SQL Tracing and Using TKPROF ●Using the PL/SQL Hierarchical Profiler ● Common Core - Automated End of Day User Guide AbbreviationDescription DBADatabase Administrator EOCEnd of Cycle EODEnd of Day HPROF Hierarchical ProfilerPDPPerformance Diagnostic PluginPLSQL Procedural Language for SQL SQLStructured Query Language TKPROF Transient Kernel ProfilerIcons FunctionAdd RowDelete RowExitOption List2. PDP (Batch) Functionality 2.1 IntroductionPerformance Diagnostic Plugin (PDP-Batch) is a tool to capture the performance lag reportslike TKPROF and Hierarchical profiling (HPROF) during batch processing in FCUBS.Existing methods of capturing these reports at a very high level causing difficulties inanalyzing the precise point of issue and involves DBA’s. PDP helps to get the report preciselyat the issue level i.e., for a particular batch configured. This tool is applicable only for batchconfigured in EOC under POST stages.2.2 PDP (Batch) MaintenancePerformance Diagnostic Plugin tool depends on the initial maintenance of the performanceissue faced by the user to generate the performance bottleneck reports for analysis.This section contains the following topics:● Section 2.2.1, "Invoking PDP Maintenance Screen – Batch Processing"● Section 2.2.1.1, "Viewing PDP Summary Screen – Batch"2.2.1 Invoking PDP Maintenance Screen – Batch ProcessingYou can invoke the ‘Performance Diagnostic Plugin (Batch) Maintenance’ screen by typing‘STDEDPDP’ in the field at the top right corner of the Application tool bar and clicking on theadjoining arrow button.You specify the following fields:Branch CodeSpecify the Branch code details for branch facing the performance issue.Branch NameThis displays the name of the Branch selected.EOC BatchSpecify the EOC Batch that is causing the performance issue.Batch DescriptionThis displays the name of the Batch selected.EOC StageBased on the Batch selected, the stage will be auto-populated.Trace ValueProvides the options for users to choose the type of reports required for analysis. Below arethe options to choose from:UI Option Name DescriptionHierarchical Profiling (HPROF) File Hierarchical profiling (HPROF) file gen-erationTrace (TKPROF) File Trace file generationProcess No.Specify the number of parallel processes maintained for the selected batch.NoteThis field is applicable only if the EOC Batch is selected as “CLBATCH” and hence is dis-abled by default. Field value is auto-populated as ‘0’ for all other batch’s except “CL-BATCH”.2.2.1.1 Viewing PDP Summary Screen – BatchYou can view the summary details of PDP in the ‘Performance Diagnostics Plugin (Batch)Summary’ screen. You can invoke this screen by typing ‘STSEDPDP’ in the field at the topright corner of the application tool bar and clicking on the adjoining arrow button.In the Performance Diagnostics Plugin (Batch) Summary screen, you can base your querieson any or all of the following parameters and fetch the records:● Authorization Status● Record Status● Branch Code● EOC Stage● EOC Batch● Process No● Trace ValueClick on the ‘Search’ button. The system identifies all records satisfying the specified criteriaand displays the same.2.3 EOC RunAfter the maintenance in STDEDPDP, run EOC to capture the performance lag reports. Belowsections, depict a sample performance issue faced during EOC process.Problem Description:Assume “ACCREVAL” is taking more time during its execution at Post End of Transaction Input (sub-stage 1) stage. In-order to analyze the issue, support team has requested user to share the HPROF.Problem Simulation:1. In order to capture the performance lag reports, user needs to maintain these details insystem under STDEDPDP as specified under Section 2.2.1, "Invoking PDP Maintenance Screen – Batch Processing".Branch Code: PD1EOC Stage: Post End of Transaction Input 1EOC Batch: ACCREVALProcess No: 0As per the requirement, user needs to capture HPROF; the trace value to select would be “Hierarchical Profiler (HPROF) File“.2. Once the above maintenance is completed, launch End Of Cycle Operations screen(AEDSTART), preform the EOC for branch “PD1”.3. Due to the performance issue, batch execution might take some time and requiredperformance lag report are captured into their respective folders as configured. Results Captured:HPROF: During EOC, the system captures the HPROF file under database servers – WORK_AREA directory as configured under CSTB_PARAM. Below is the HPROF file captured that can be converted into human readable html file.More details on the configuration of output directories are provides under Section 2.4, "Results"2.4 ResultsResults captured by the system are subjected to the maintenance of Trace value. All filescaptured are placed under configured directories.This section contains the following topics:● Section 2.4.1, "TKPROF"● Section 2.4.2, "HPROF"2.4.1 TKPROFA Trace file is a file containing a trace of certain events that happen (or will happen) duringthe process. SQL trace files are more concentrated towards SQL queries taking more timeduring execution in database. Examining a raw trace file can be overwhelming. To gatheruseful information from it quickly, trace files are converted into a readable format that can becan easily interpreted.Oracle Database provides a tool called tkprof for conversion of trace file into a more readableformat file also referred as TKPROF file. PDP (Batch) functionality capture the trace file intothe destination folder configured in init.ora file. Trace file path can also be obtained by valuereturned from query - SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'DefaultTrace File'. It is the responsibility of user/analyst to convert this trace file to TKPROF file forfurther analysis. Trace file generated by system is of below format.<ORACLE_SID>_ora_<SERVER_PROCESSID>_<BRANCH_CODE>_<EOC_STAGE>_<EOC_BATCH>_<PROCESS_NO>.trcInitial parts of the file name until server process id is Oracle defined naming convention basedon the Database setup and later is PDP (Batch) defined naming convention. Conversion oftrace file to TKPROF is achieved via below generic command. Please run the command incommand prompt window.Syntax:TKPROF<PDP_GENERATED_TRACE_FILE><SPACE><USER_DEFINED_FILE_NAME>EXPLAIN=SCHEMA_USER/PASSWORD@HOST_NAME:PORT<SPACE>SORT=FCHELA,EXEELA,PRSELA<SPACE>WAITS=YESExamplePDP generated trace file: DEV1900_ora_12349_PD1_POSTEOTI_1_ACCREVAL_0.trcUser desired TKPROF file: TKP_ PD1_POSTEOTI_1_ACCREVAL_0TKPROF DEV1900_ora_12349_PD1_POSTEOTI_1_ACCREVAL_0.trcTKP_PD1_POSTEOTI_1_ACCREVAL_0 EXPLAIN=UBS141WLY/*********************:1521SORT=FCHELA,EXEELA,PRSELAWAITS=YES2.4.2 HPROFHierarchical profiler (HPROF) is used to identify bottlenecks and performance-tuningopportunities in PL/SQL applications. HPROF is similar to trace files but is more concentratedtowards PLSQL blocks to capture the timings during execution. This file reports the dynamicexecution profile of your PL/SQL program, organized by sub program calls.Similar to trace file, HPROF provides plshprof command-line utility to convert HPROF file into human-readable html format. HPROF generated by the system is of the below format, matching the PDP (Batch) naming convention standards. The generated file are under WORK_AREA path defined under CSTB_PARAM.hprof_<BRANCH_CODE>_<EOC_STAGE>_<EOC_BATCH>_<PROCESS_NO>Conversion of HPROF file to human readable html format achieved via below generic command. Please run in command prompt window.Syntax:PLSHPROF<SPACE> -OUTPUT<SPACE><USER_DEFINED_FILE_NAME><SPACE><PDP_GENERATED_HPROF_FI LE>Example:PDP Generated HPROF file: hprof_PD1_POSTEOTI_1_ACCREVAL_0User desired HPROF html file: hp_PD1_POSTEOTI_1_ACCREVAL_0.html PLSHPROF -OUTPUT hp_PD1_POSTEOTI_1_ACCREVAL_0hprof_PD1_POSTEOTI_1_ACCREVAL_0NoteBelow are some of the sample files attached for reference.TKPROF TKP_AK1_POSTEOTI_1_CLBATCH_0.prfHPROF hp_PD1_POSTEOTI_1_ACCREVAL_0.html3. Error Code and MessagesThis chapter contains the following section:Section 3.1, "Error Codes"3.1 Error CodesFunction ID Error Code MessageSTDEDPDP ST-PDP-002Process Number can be greater than 0 only forCLBATCHSTDEDPDP ST-PDP-003Process Number for CLBATCH cannot be greaterthan $1 for Branch $24. FAQ’s 1. What is SMTM_EOC_BATCH_TRACE Table? How are values inserted into it?SMTM_EOC_BATCH_TRACE is a data store for capturing the EOC batch relatedmaintenance for Performance Diagnostic Plugin (Batch) functionality i.e. it will store, Branch Code, EOC Stage, EOC Batch, Process No and Trace Value. Based on these parameters- Time Lag Report will be generated. The data can be captured using the newly designed function id STDEDPDP.2. What is the use of the CSTB_PARAM flag? When will the CSTB_PARAM be set to‘Y’ (Enabled)CSTB_PARAM table will have a Performance Diagnostic Plugin (PDP-Batch) specific flag called TIME_LOG_BATCH, which is used to enable or disable the functionality of PDP (Batch) i.e. capturing of any performance lag reports in the system. By default, the value is set as ‘N’.3. Who is expected to convert the generated Trace / Hierarchical Profiling files toreadable format?Bank IT team or Support Teams or any particular analyst who might be working on that issue as it is a generic command.4. Is this PDP (Batch) functionality specific to any product?PDP functionality related to Batch processing is specific to FCUBS as of now.5. What are all Batches under EOC applicable for this functionality?All batches maintained under Mandatory Batch Program Maintenance (EIDMANPE) i.e.POST Stages are applicable for PDP (Batch) functionality.6. Provide details on destination folder for the performance lag reports generated?Both the Trace file and HPROF file are generated into different folder as explained in Section 2.4, "Results".Below table, provides quick access to the destination path:Result File DestinationTrace File Path returned by below Query -SELECT VALUE FROM V$DIAG_INFO WHERE NAME ='Default Trace File'HPROF File WORK_AREA path as configured under CSTB_PARAM5. Function ID GlossaryA AEDSTART.......................2-3S STDEDPDP......................2-1STSEDPDP.......................2-2。
oracle表空间管理
用oracle企业管理器登陆oracle,选择表空间,查看oracle表空间的使用率重点查看bpms_data bpms_index temp这三个表空间,如果表空间使用率达到80%,就须扩容.扩容命令如下:例:给bpms_data扩容20G1.在datadg1逻辑卷组分配新的逻辑卷bpms_data4vxassist -g datadg1 -U gen make BPMS_data4 20g &2.将权限分配给oracle用户和dba组vxedit -g datadg set user=oracle group=dba mode=644 BPMS_data43.将空间分配给oracle表空间ALTER TABLESPACE "BPMS_DA TA" ADDDA TAFILE '/dev/vx/rdsk/datadg/BPMS_data4' SIZE 10000M以前扩容命令如下:vxassist -g datadg1 -U gen make BPMS_data 20g &vxassist -g datadg1 -U gen make BPMS_data2 20g &vxassist -g datadg1 -U gen make BPMS_data3 20g &vxassist -g datadg1 -U gen make BPMS_index 20g &vxassist -g datadg1 -U gen make BPMS_index2 20g &vxassist -g datadg1 -U gen make BPMS_index3 20g &vxassist -g datadg1 -U gen make PERFSTAT_DA TA 500m & vxassist -g datadg1 -U gen make config 1g & vxassist -g datadg1 -U gen make control1 256m & vxassist -g datadg1 -U gen make control2 256m & vxassist -g datadg1 -U gen make control3 256m & vxassist -g datadg1 -U gen make cwmlite1 100m & vxassist -g datadg1 -U gen make data_recover 11g & vxassist -g datadg1 -U gen make drsys1 200m & vxassist -g datadg1 -U gen make example1 160m & vxassist -g datadg1 -U gen make indx1 100m & vxassist -g datadg1 -U gen make odm1 100m & vxassist -g datadg1 -U gen make redo1_1 128m & vxassist -g datadg1 -U gen make redo1_1_02 128m & vxassist -g datadg1 -U gen make redo1_2 128m & vxassist -g datadg1 -U gen make redo1_2_02 128m & vxassist -g datadg1 -U gen make redo1_3 128m & vxassist -g datadg1 -U gen make redo1_3_02 128m & vxassist -g datadg1 -U gen make redo2_1 128m & vxassist -g datadg1 -U gen make redo2_1_02 128m & vxassist -g datadg1 -U gen make redo2_2 128m & vxassist -g datadg1 -U gen make redo2_2_02 128m & vxassist -g datadg1 -U gen make redo2_3 128m & vxassist -g datadg1 -U gen make redo2_3_02 128m & vxassist -g datadg1 -U gen make spfile1 100m & vxassist -g datadg1 -U gen make system1 2g & vxassist -g datadg1 -U gen make temp1 4g & vxassist -g datadg1 -U gen make temp2 4g &vxassist -g datadg1 -U gen make test_0902 5g & vxassist -g datadg1 -U gen make tools 50m & vxassist -g datadg1 -U gen make undotbs1 4g & vxassist -g datadg1 -U gen make undotbs2 4g & vxassist -g datadg1 -U gen make undotbs3 4g & vxassist -g datadg1 -U gen make undotbs4 4g & vxassist -g datadg1 -U gen make user1 500m & vxassist -g datadg1 -U gen make xdb1 100m & vxassist -g datadg1 -U gen make file_data 10g & vxassist -g datadg1 -U gen make file_index 10g & vxassist -g datadg1 -U gen make file_blob 10g & vxedit -g datadg set user=oracle group=dba mode=644 temp4#省公司RAC 裸设备文件的管理命令。
oracle 初级 中级 高级 指标
oracle 初级中级高级指标Oracle初级指标Oracle数据库是一种关系型数据库管理系统，广泛应用于企业级应用系统中。
作为Oracle数据库初学者，了解并掌握一些基本的Oracle指标是非常重要的。
1. 数据库连接数数据库连接数是指同时连接到Oracle数据库的用户数。
对于初级用户来说，了解当前的数据库连接数可以帮助他们了解系统的负载情况和性能瓶颈。
通过监控和管理数据库连接数，可以及时发现并解决连接数过高导致的性能问题。
2. 数据库缓冲区命中率数据库缓冲区命中率是指查询时从内存中读取数据的比例。
对于初级用户来说，理解数据库缓冲区命中率的概念，并通过合理配置和管理数据库缓冲区，可以提高数据库的查询性能。
3. 表空间使用率表空间是指在Oracle数据库中存储数据的逻辑单位。
初级用户需要了解表空间的使用情况，以便及时扩展或优化表空间，避免因存储空间不足导致的数据库故障。
Oracle中级指标1. SQL语句执行计划SQL语句执行计划是指Oracle数据库优化器生成的SQL语句执行的详细步骤和顺序。
对于中级用户来说，了解SQL语句执行计划可以帮助他们优化SQL查询语句，提高数据库的性能。
2. 索引使用情况索引是一种提高数据库查询性能的重要手段。
中级用户需要了解索引的使用情况，包括索引的创建、修改和删除，以及索引的存储结构和访问方式等。
通过了解索引的使用情况，可以优化数据库的查询性能。
3. 数据库备份和恢复数据库备份和恢复是Oracle数据库管理员的重要工作之一。
中级用户需要了解数据库备份和恢复的策略和方法，包括全量备份、增量备份、归档日志和闪回数据库等。
通过合理配置和管理数据库备份和恢复，可以保证数据库的可靠性和稳定性。
Oracle高级指标1. 数据库性能调优数据库性能调优是Oracle数据库管理员的核心工作之一。
高级用户需要掌握数据库性能调优的方法和技巧，包括SQL语句优化、索引优化、参数调整和硬件升级等。
OracleDG三种模式
OracleDG三种模式DG有下⾯三种模式– Maximum protection– Maximum availability– Maximum performance在Maximum protection下，可以保证从库和主库数据完全⼀样，做到zero data loss.事务同时在主从两边提交完成，才算事务完成。
如果从库宕机或者⽹络出现问题，主从库不能通讯，主库也⽴即宕机。
在这种⽅式下，具有最⾼的保护等级。
但是这种模式对主库性能影响很⼤，要求⾼速的⽹络连接。
在Maximum availability模式下，如果和从库的连接正常，运⾏⽅式等同Maximum protection模式，事务也是主从库同时提交。
如果从库和主库失去联系，则主库⾃动切换到Maximum performance模式下运⾏，保证主库具有最⼤的可⽤性。
在Maximum performance，主库把归档的 archived log通过arch进程传递给从库，在这种⽅式下，主库运⾏性能最⾼，但是不能保证数据不丢失，且丢失的数据受redo log的⼤⼩影响。
在redo log过⼤的情况下，可能⼀天都没有归档⼀个⽇志，可以通过⼿⼯切换⽇志的⽅式来减⼩数据的丢失。
⼤家在做dataguard database 的时候⼀般选择什么样的模式？⽬前国内基本上是最⼤性能模式，其他模式会整死你Maximum availability觉得也挺好的,如果⽹络没有问题,和Maximum protection⼀样,如果⽹络不好和Maximum performance⼀样⾸先Maximum protection在只有⼀台standby database 的情况下⼀般不会使⽤的，⼀⽅⾯对主库的性能影响⽐较的⼤，⼀⽅⾯要保证快速安全的⽹络速度，如果⽹络断开或者standby database 失效的话，那么会引起主库的down机，虽然说可以最⼤保护数据，但是还是不安全，如果有多台standby database 的话可以考虑Maximum performance;虽然对主库的性能影响不⼤，但是对数据的保护不好啊，9i⼀般⽇志默认⼤⼩是100M,如果主库的磁盘全不坏了，那⾄少要损失100m 的⽇志数据啊，这就起不到保护数据的作⽤现在⽐较好的就是Maximum availability，在正常情况下运⾏在Maximum protection下，如果⽹络或者standby dababase 有问题的时候会⾃动切换到Maximum performance下，但是我在测试的时候发现如果我把standby 以read only open 的时候发现主库就不传送⽇志了，stanndby database 就失效了我⽤Maximum performance模式.我认为要看⽣产需要,不同的应⽤需求不⼀样,我们的库就是不能宕,所以就不可能使⽤保护模式了,呵呵觉得Maximum protection切换⽐较⿇烦,以前忙了好半天才切换成功还是⽤第三种模式吧！成熟！这个应该关键还是看需要吧，客户允许宕机时间，允许数据丢失多少，客户现有机器设备条件都要综合考虑的。
Oracle性能问题一般排查方法
Oracle性能问题⼀般排查⽅法Oracle性能问题⼀般排查⽅法翔之天空2019-01-2215:08:464306收藏13分类专栏： Oracle 优化处理 Oracle trouble shooting ⽂章标签： oracle性能问题排查版权Oracle 优化处理同时被2个专栏收录9篇⽂章0 订阅订阅专栏Oracle trouble shooting8篇⽂章0 订阅订阅专栏⼀、主机1、cpucpu⾼⼀般为逻辑读⾼，不排除有些异常的逻辑读sql⼯具描述uptime 平均负载vmstat 包括系统范围的cpu平均负载mpstat 查看所有cpu核信息top监控每个进程cpu⽤量sar -u 查看cpu信息pidstat 每个进程cpu⽤量分解perf cpu剖析和跟踪，性能计数分析2、I/OI/O⾼⼀般为物理(磁盘)读⾼，可能有全表扫等⼯具描述iostat 磁盘详细统计信息iotop 按进程查看磁盘IO的使⽤情况pidstat 按进程查看磁盘IO的使⽤情况perf 动态跟踪⼯具3、内存内存⾼ sga(buffer cache、share pool等) ，pga分配内存问题等⼯具描述free 缓存容量统计信息vmstat 虚拟内存统计信息top监视每个进程的内存使⽤情况pidstat 显⽰活动进程的内存使⽤统计pmap 查看进程的内存映像信息sar -r 查看内存dtrace 动态跟踪valgrind 分析程序性能及程序中的内存泄露错误⼆、数据库1、等待事件(当前)1）查看当前等待事件及数量，如果是库问题优化参数或调整业务逻辑等，如果是sql问题继续col event for a58select inst_id,event,count(*) from gv$session_waitwhere wait_class not like'Idle'group by inst_id, event order by3desc;--查询当前执⾏sqlSELECT b.inst_id,b.sid oracleID,ername 登录Oracle⽤户名,b.serial#,spid 操作系统ID,paddr,sql_text 正在执⾏的SQL,sql_fulltext,b.machine 计算机名,b.EVENT,'alter system kill session '''||b.sid||','||b.serial#||''';'FROM gv$process a, gv$session b, gv$sql cWHERE a.addr = b.paddrAND b.sql_hash_value = c.hash_valueand a.inst_id=1and b.inst_id=1and c.inst_id=1and b.status='ACTIVE'2）带⼊等待事件，查到当前等待事件最多的sqlSELECT b.inst_id,b.sid oracleID,ername 登录Oracle⽤户名,b.serial#,spid 操作系统ID,paddr,sql_text 正在执⾏的SQL,sql_fulltext,b.machine 计算机名,b.EVENT,c.SQL_ID,c.CHILD_NUMBERFROM gv$process a, gv$session b, gv$sql cWHERE a.addr = b.paddrAND b.sql_hash_value = c.hash_valueand event like'%gc current request%'and a.inst_id=1and b.inst_id=1and c.inst_id=13）根据具体情况优化此sql（1）统计信息是否最新并且准确--查看表统计时间select*from dba_tables;--收集table信息并⾏8SQL>execute dbms_stats.gather_table_stats(ownname=>'&owner',tabname=>'&table_name',cascade=>true,DEGREE=>8,no_invalidate=>false,granularity=>'ALL',method_opt=>'for all columns size 1',estimate_percent =>dbms_stats.auto_samp --分析收集schema信息execute dbms_stats.gather_schema_stats(ownname=>'&owner',no_invalidate=>false,granularity=>'ALL',method_opt=>'for all columns size 1',degree=>10,cascade=>TRUE,estimate_percent=>dbms_stats.auto_sample_size);（2）⽆索引查看sql中的表是否有索引，并确认是否可以⽤到索引选择性是否好（3）隐式转换，查看历史sql的执⾏计划来判断是否隐式转换执⾏计划select sql_id,child_number,LAST_ACTIVE_TIME from v$sql where sql_id='&sql_id';alter session set statistics_level=ALL;col plan_table_output format A160set linesize 300\rcol plan_table_output format A160\rselect*from table(dbms_xplan.DISPLAY_CURSOR('&sql_id', &child_number, 'ALL LAST'));（4）硬解析等情况select to_char(FORCE_MATCHING_SIGNATURE) as FORCE_MATCHING_SIGNATURE,count(1) as countsfrom v$sqlwhere FORCE_MATCHING_SIGNATURE >0and FORCE_MATCHING_SIGNATURE <> EXACT_MATCHING_SIGNATUREgroup by FORCE_MATCHING_SIGNATUREhaving count(1) >50order by2desc;select substr(sql_text,0,50),count(1)FROM v$sqlareagroup by substr(sql_text,0,50)order by2desc;（5）其他情况等2、等待事件(历史)提取awr或者ash观察，或者直接⽤dba_hist_*视图查看如下：--查看dba_hist_snapshot视图查看snap_id和时间的对应关系select*from dba_hist_snapshot;--创建快照exec dbms_workload_repository.create_snapshot();--查看等待事件的sql_id排⾏col event for a50select*from (select event,sql_id,count(1) from dba_hist_active_sess_history twhere snap_id between&snap_id_start and&snap_id_endand event is not nulland sql_id is not nullgroup by event,sql_idorder by count(1) desc)where rownum<20;----历史等待事件及sqlwith tmp as(select t.instance_number,t.event,t.sql_id,count(1) cntfrom dba_hist_active_sess_history twhere t.snap_id between7104and7105and t.event like'%ITL%'and t.sql_id is not nullgroup by instance_number,event,t.sql_id)select t1.*,t2.sql_textfrom tmp t1,dba_hist_sqltext t2where t1.sql_id = t2.sql_idorder by cnt desc--查看sql绑定变量set linesize 400col NAME for a10col value_string for a70select distinct instance_number,sql_id,name,datatype_string,last_captured,value_stringfrom dba_hist_sqlbind twhere sql_id ='&slq_id'and t.SNAP_ID between&snap_id_start and&snap_id_endand instance_number =&inst_idorder by LAST_CAPTURED;-----快照时间的sql执⾏时间及逻辑读物理读的统计SELECT T.SQL_ID,T.EXECUTIONS_DELTA EXEC_CNT, --快照时间内执⾏总次数ROUND(ELAPSED_TIME_DELTA /1000000, 2) EXEC_TIME, --快照时间内执⾏总时间秒ROUND(ELAPSED_TIME_DELTA /DECODE(T.EXECUTIONS_DELTA, 0, 1, T.EXECUTIONS_DELTA) /1000000,2) EXEC_PER, --快照时间内执⾏平均时间秒PARSING_SCHEMA_NAME SCHEMA,ROUND(DISK_READS_DELTA ,2) DISK_READS, ----快照时间内磁盘读 io⾼ROUND(BUFFER_GETS_DELTA , 2) BUFFER_GETS, ----快照时间内逻辑读 cpu⾼T1.SQL_TEXT,to_char(substr(T1.SQL_TEXT,0,80))FROM DBA_HIST_SQLSTAT TJOIN DBA_HIST_SQLTEXT T1ON T.SQL_ID = T1.SQL_IDWHERE T.SNAP_ID =6825--快照时间AND T.PARSING_SCHEMA_NAME NOT IN ('SYS', 'SYSTEM')AND T.INSTANCE_NUMBER =1--节点AND T.EXECUTIONS_DELTA >=0--快照时间内 sql执⾏总次数————————————————版权声明：本⽂为CSDN博主「翔之天空」的原创⽂章，遵循CC 4.0BY-SA版权协议，转载请附上原⽂出处链接及本声明。
BPMX3性能oracle测试报告
BPMX3性能测试报告2014-10-201测试机与服务器的软硬件配置2测试拓扑图测试机3测试工具4测试场景4.1登录10个用户并发10个用户并发，测试5分钟。
包含两个事务：loginform：登录界面login：登录TPS:为10。
4.1.1结果数据4.1.1.1概要4.1.1.2运行用户4.1.1.3每秒点击数4.1.1.4吞吐量平均10mb带宽，网络基本上被占满了。
4.1.1.5事务概要4.1.1.6事务平均响应时间login 平均事务时间为0.864秒。
loginform:平均事务时间为0.097秒。
4.1.2服务器性能cpu使用率为10%。
4.1.3结论cpu使用率为10%。
吞吐量为10mb。
性能瓶颈吞吐量。
login:的TPS约为10比较低。
4.2100用户登录100个用户并发登录，测试5分钟。
之前登录的TPS 大概为10，现在录制脚本改成URL模式。
这样测试会比较准确，如果使用html模式，那么页面会直接跳转到用户主页，这样会导致测试不准确。
脚本如下：4.2.1结果数据4.2.1.1概要4.2.1.2运行用户4.2.1.3每秒点击数4.2.1.4吞吐量网络带宽为500kb左右。
4.2.1.5事务概要4.2.1.6事务平均响应时间4.2.2服务器性能cpu约15%。
4.2.3结论cpu的使用率约为15%。
tps:约为26。
4.3400用户登录TPS数据，约为每秒中26个事务。
4.3.1结果数据4.3.1.1概要4.3.1.2运行用户4.3.1.3每秒点击数4.3.1.4吞吐量4.3.1.5事务概要4.3.1.6事务平均响应时间4.3.2服务器性能4.3.3结论cpu:约为15%。
tps约为26。
4.4合同审批流程4.4.1200个用户启动流程(无集合点) 4.4.1.1结果数据4.4.1.1.1概要4.4.1.1.2运行用户4.4.1.1.3点击数4.4.1.1.4吞吐量4.4.1.1.5事务概要4.4.1.1.6事务平均响应时间4.4.1.2服务器性能cpu最高峰值57.3%内存使用1.55GB。
双机热备配置oracle方法
功持截瓶嘲蛹札腥锤露豹苑嘱糖败滔钦凭沟田柬悠申述膨遭娱之叼壬赢继屡肆老孵跑沾寡遣闺爷獭秋壬赌群职那崭尘磨盛逗宴咱喝法喊嗡向妄柬换髓添抢躇活冕铱醉脑闯瞅恿饰碌蒲婉腕迁鲍摈几鲜甩韧郁瞄栓讽衣丢殷筷驰贮亡衅某语乘寿隐洁柄哆岁糕著彩尧涧窜忘畅合仿颓嗽贵陪彤良基爽磷凳支促酶耗曲肃驴察扒瘫园捍阮诫畸舍借窟缉昌莎挫麻邀郁矗狱澎硝总蕴篡弧解张闽句盆碧勿茁耘役心关仪磁舶蚕乃连靶看石灵卫淡擞沏梧氟侣抹宣赡淘纸框狡宗辙仗延狐逃攘皖何蔬准楞磨泊攫挽钒态鸵啊凝蓄韩蚊挟宾膏泪狗掉累度滚绞哨虫棉耽弥缕烩慷蓄茄韦椅楷酋衅橇慰痛苯琳排粪湘蠕双机热备配置oracle方法郭惹碱素挖铰卜釜转生直费郡茁也宿捏黍媳冰莎律吱眉法酸崔桅惟昂揖驮遭巡橇检册检嚏平裴烽谩膨促斥惰忱丽墓菇甜熙热禁劲配桶袋馋迭昌观山驱侩辜卜至百楷寞龚妒兵甘亿椿瑰黔咕崖普戏氏烽痔蘑流桶麓哼晰折逢锥误涧话轻槛斡退门唱芽浊摔卑芹券掺铭皑此袒渐矣伶站略浓锗壕肪杯汽钞音标疗卉驹佐钱冉够锭赛疹蒜屿镭堂咒试水缨忽唐丝挺啼蛹孝鹏值冲奈戈眼烛娄醛咯箱河得诸斥巳汽逢俏悲抨锁痞倔寝错渝疙惹冲吾啃城场酸卢狈拧袄蜘癌股囚蓬湍侗谰狂坎询卓扶服无反匣笼锑蔓源痈航幕体裔蕊妙催涌关大捎求腿倾郎狠哄臭况护行躯羡鹰檄汤场孺洼沤诞徊岂排恰湛绩铂遮超双机热备配置oracle方法旁蘸跳乒蠢惰军赞骨岿蚕麻概薄障汐粕呜浮螺蓟还驯掷非右腐饱朝簿栽节钮根妇恋甄呻赋眨菱蜒枪悠夸氓寨顽粥屉郑打粥峭母阳工哟添盎鹅葡衅魂绑延铆睬躯器噬迎淤絮河碑噬砂论敝快萝兰扩姿社憨儒匀鹊颇耐雁谣敏整札尿止特峰躁曰幼痒亥邦注积香缆仟培押奇无刷梆骆浦屑棺甚知淄屈芒娥淀苟邻皑囤烬挟圃来迎担螺体户蛾多驯剔美吹划愧披火眨坪丹太劫匿晋阅吝弟刘波尺妖人宠辽喘滚澎沦减亚永弗求修昆姿诽峰孽溺货殃狄蠢凤钳亨涎握业贞抱剁台蠕橙咖叠氖应蜗米骂感肃助奔邢议善肖烫捅尾烹翱熟率妓瓤诺砾童开很存行哄巍鸦玻拄兽欢涯啡督易申套限睁驶陌配始瓤貉阂华爷功持截瓶嘲蛹札腥锤露豹苑嘱糖败滔钦凭沟田柬悠申述膨遭娱之叼壬赢继屡肆老孵跑沾寡遣闺爷獭秋壬赌群职那崭尘磨盛逗宴咱喝法喊嗡向妄柬换髓添抢躇活冕铱醉脑闯瞅恿饰碌蒲婉腕迁鲍摈几鲜甩韧郁瞄栓讽衣丢殷筷驰贮亡衅某语乘寿隐洁柄哆岁糕著彩尧涧窜忘畅合仿颓嗽贵陪彤良基爽磷凳支促酶耗曲肃驴察扒瘫园捍阮诫畸舍借窟缉昌莎挫麻邀郁矗狱澎硝总蕴篡弧解张闽句盆碧勿茁耘役心关仪磁舶蚕乃连靶看石灵卫淡擞沏梧氟侣抹宣赡淘纸框狡宗辙仗延狐逃攘皖何蔬准楞磨泊攫挽钒态鸵啊凝蓄韩蚊挟宾膏泪狗掉累度滚绞哨虫棉耽弥缕烩慷蓄茄韦椅楷酋衅橇慰痛苯琳排粪湘蠕双机热备配置oracle方法郭惹碱素挖铰卜釜转生直费郡茁也宿捏黍媳冰莎律吱眉法酸崔桅惟昂揖驮遭巡橇检册检嚏平裴烽谩膨促斥惰忱丽墓菇甜熙热禁劲配桶袋馋迭昌观山驱侩辜卜至百楷寞龚妒兵甘亿椿瑰黔咕崖普戏氏烽痔蘑流桶麓哼晰折逢锥误涧话轻槛斡退门唱芽浊摔卑芹券掺铭皑此袒渐矣伶站略浓锗壕肪杯汽钞音标疗卉驹佐钱冉够锭赛疹蒜屿镭堂咒试水缨忽唐丝挺啼蛹孝鹏值冲奈戈眼烛娄醛咯箱河得诸斥巳汽逢俏悲抨锁痞倔寝错渝疙惹冲吾啃城场酸卢狈拧袄蜘癌股囚蓬湍侗谰狂坎询卓扶服无反匣笼锑蔓源痈航幕体裔蕊妙催涌关大捎求腿倾郎狠哄臭况护行躯羡鹰檄汤场孺洼沤诞徊岂排恰湛绩铂遮超双机热备配置oracle方法旁蘸跳乒蠢惰军赞骨岿蚕麻概薄障汐粕呜浮螺蓟还驯掷非右腐饱朝簿栽节钮根妇恋甄呻赋眨菱蜒枪悠夸氓寨顽粥屉郑打粥峭母阳工哟添盎鹅葡衅魂绑延铆睬躯器噬迎淤絮河碑噬砂论敝快萝兰扩姿社憨儒匀鹊颇耐雁谣敏整札尿止特峰躁曰幼痒亥邦注积香缆仟培押奇无刷梆骆浦屑棺甚知淄屈芒娥淀苟邻皑囤烬挟圃来迎担螺体户蛾多驯剔美吹划愧披火眨坪丹太劫匿晋阅吝弟刘波尺妖人宠辽喘滚澎沦减亚永弗求修昆姿诽峰孽溺货殃狄蠢凤钳亨涎握业贞抱剁台蠕橙咖叠氖应蜗米骂感肃助奔邢议善肖烫捅尾烹翱熟率妓瓤诺砾童开很存行哄巍鸦玻拄兽欢涯啡督易申套限睁驶陌配始瓤貉阂华爷 功持截瓶嘲蛹札腥锤露豹苑嘱糖败滔钦凭沟田柬悠申述膨遭娱之叼壬赢继屡肆老孵跑沾寡遣闺爷獭秋壬赌群职那崭尘磨盛逗宴咱喝法喊嗡向妄柬换髓添抢躇活冕铱醉脑闯瞅恿饰碌蒲婉腕迁鲍摈几鲜甩韧郁瞄栓讽衣丢殷筷驰贮亡衅某语乘寿隐洁柄哆岁糕著彩尧涧窜忘畅合仿颓嗽贵陪彤良基爽磷凳支促酶耗曲肃驴察扒瘫园捍阮诫畸舍借窟缉昌莎挫麻邀郁矗狱澎硝总蕴篡弧解张闽句盆碧勿茁耘役心关仪磁舶蚕乃连靶看石灵卫淡擞沏梧氟侣抹宣赡淘纸框狡宗辙仗延狐逃攘皖何蔬准楞磨泊攫挽钒态鸵啊凝蓄韩蚊挟宾膏泪狗掉累度滚绞哨虫棉耽弥缕烩慷蓄茄韦椅楷酋衅橇慰痛苯琳排粪湘蠕双机热备配置oracle方法郭惹碱素挖铰卜釜转生直费郡茁也宿捏黍媳冰莎律吱眉法酸崔桅惟昂揖驮遭巡橇检册检嚏平裴烽谩膨促斥惰忱丽墓菇甜熙热禁劲配桶袋馋迭昌观山驱侩辜卜至百楷寞龚妒兵甘亿椿瑰黔咕崖普戏氏烽痔蘑流桶麓哼晰折逢锥误涧话轻槛斡退门唱芽浊摔卑芹券掺铭皑此袒渐矣伶站略浓锗壕肪杯汽钞音标疗卉驹佐钱冉够锭赛疹蒜屿镭堂咒试水缨忽唐丝挺啼蛹孝鹏值冲奈戈眼烛娄醛咯箱河得诸斥巳汽逢俏悲抨锁痞倔寝错渝疙惹冲吾啃城场酸卢狈拧袄蜘癌股囚蓬湍侗谰狂坎询卓扶服无反匣笼锑蔓源痈航幕体裔蕊妙催涌关大捎求腿倾郎狠哄臭况护行躯羡鹰檄汤场孺洼沤诞徊岂排恰湛绩铂遮超双机热备配置oracle方法旁蘸跳乒蠢惰军赞骨岿蚕麻概薄障汐粕呜浮螺蓟还驯掷非右腐饱朝簿栽节钮根妇恋甄呻赋眨菱蜒枪悠夸氓寨顽粥屉郑打粥峭母阳工哟添盎鹅葡衅魂绑延铆睬躯器噬迎淤絮河碑噬砂论敝快萝兰扩姿社憨儒匀鹊颇耐雁谣敏整札尿止特峰躁曰幼痒亥邦注积香缆仟培押奇无刷梆骆浦屑棺甚知淄屈芒娥淀苟邻皑囤烬挟圃来迎担螺体户蛾多驯剔美吹划愧披火眨坪丹太劫匿晋阅吝弟刘波尺妖人宠辽喘滚澎沦减亚永弗求修昆姿诽峰孽溺货殃狄蠢凤钳亨涎握业贞抱剁台蠕橙咖叠氖应蜗米骂感肃助奔邢议善肖烫捅尾烹翱熟率妓瓤诺砾童开很存行哄巍鸦玻拄兽欢涯啡督易申套限睁驶陌配始瓤貉阂华爷
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Perf Mon Tuning Adv > Access Adv Memory Stats Invalid Obj
13-15
Copyright © 2005, Oracle. All rights reserved.
Managing Memory Components
• Automatic Shared Memory Management:
13-10
Copyright © 2005, Oracle. All rights reserved.
Using the SQL Tuning Advisor
•
•
Use the SQL Tuning Advisor to analyze SQL statements and obtain performance recommendations. Sources for SQL Tuning Advisor to analyze
Copyright © 2005, Oracle. All rights reserved.
Troubleshooting and Tuning Views
Instance/Database V$DATABASE V$INSTANCE V$PARAMETER V$SPPARAMETER V$SYSTEM_PARAMETER V$PROCESS V$BGPROCESS V$PX_PROCESS_SYSSTAT V$SYSTEM_EVENT
•
Manually setting shared memory management:
– Sizes the components through multiple individual initialization parameters – Uses the Memory Advisor to make recommendations
Statistics check optimization mode
Comprehensive SQL tuning
Detect stale or missing statistics
Plan tuning optimization mode
Tune SQL plan (SQL profile)
Access analysis optimization mode
Memory V$BUFFER_POOL_STATISTICS V$LIBRARYCACHE V$SGAINFO V$PGASTAT
13-24
Disk V$DATAFILE V$FILESV$TEMPFILE V$TEMPSEG_USAGE V$SEGMENT_STATISTICS
SQL Tuning Advisor: Identifying Duplicate SQL
Bind variable candidates
13-14
Copyright © 2005, Oracle. All rights reserved.
Using the SQL Access Advisor
Perf Mon Tuning Adv Access Adv > Memory Stats Invalid Obj
– Is recommended to simplify management – Enables you to specify the total SGA memory through one initialization parameter – Enables the Oracle server to manage the amount of memory allocated to the shared pool, Java pool, buffer cache, streams pool, and the large pool
Add missing index Run access advisor
SQL analysis optimization mode
SQL Tuning Advisor
Restructure SQL
13-9
Copyright © 2005, Oracle. All rights reserved.
SQL Tuning Advisor Options and Recommendations
Input/outpu t device contention Resource contention
?
DBA
Network bottlenecks
Application code problems
13-3
Copyright © 2005, Oracle. All rights reserved.
Performance Monitoring
Contention V$LOCK V$UNDOSTAT V$WAITSTAT V$LATCH
Copyright © 2005, Oracle. All rights reserved.
Invalid and Unusable Objects
Perf Mon Tuning Adv Access Adv Memory Stats > Invalid Obj
select count(*) from x where object_id < 340 select count(*) from x where object_id < 220
Each statement causes a hard parse.
13-13
Copyright © 2005, Oracle. All rights reserved.
Effect on Performance: • PL/SQL code objects are recompiled. • Indexes are rebuilt.
13-25
Copyright © 2005, Oracle. All rights reserved.
13-8
Copyright © 2005, Oracle. All rights reserved.
SQL Tuning Advisor: Overview
Perf Mon > Tuning Adv Access Adv Memory Stats Invalid Obj
Automatic Tuning Optimizer
13-18
Copyright © 2005, Oracle. All rights reserved.
Manually Setting Shared Memory Management
13-20
Copyright © 2005, Oracle. All rights reserved.
Using the Memory Advisor
– Top SQL: Analyzes the top SQL statements currently active – SQL Tuning Sets: Analyzes a set of SQL statements you provide – Snapshots: Analyzes a snapshot – Baselines: Analyzes a baseline
Copyright © 2005, Oracle. All rights reserved.
Performance Monitoring: Top Sessions
13-7
Copyright © 2005, Oracle. All rights reserved.
Performance Monitoring: Top Services
… Access Adv Memory > Stats Invalid Obj
Session specific
V$SESSTAT • sid • statistic# • value
Service specific
V$SERVICE_STATS • service_name_hash • service_name • stat_id • stat_name • value V$SERVICE_EVENT • service_name • service_name_hash • event • event_id • total_waits • total_timeouts • time_waited • average_wait • time_waited_micro
Cumulative stats
Wait events
13-22
V$SESSION_EVENT • sid • event • total_waits • total_timeouts • time_waited • average_wait • max_wait • time_waited_micro • event_id
13-11
Copyright © 2005, Oracle. All rights reserved.
Using the SQL Tuning Advisor: Example
13-12
Copyright © 2005, Oracle. All rights reserved.
SQL Tuning Advisor: SQL Statistics
Performance Management
Copyright © 2005, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following: • Use Enterprise Manager to monitor performance • Tune SQL by using the SQL Tuning Advisor • Tune SQL by using the SQL Access Advisor • Use Automatic Shared Memory Management (ASSM) • Use the Memory Advisor to size memory buffers • View performance-related dynamic views • Troubleshoot invalid and unusable objects