随笔分类 - Oracle 11G新特性
摘要：Using the SQL Tuning AdvisorUse the SQL Tuning Advisor to analyze SQL statements and obtain performance recommedationsSources for SQL Tuning Advisor to analyze:Top Activety:Analyzes the top SQL statements currently activeSQL Tuning Set:Analyzes a set of SQL statements you provideHistorical SQL(AWR):
阅读全文
摘要：AWR能够帮助DBA识别占用资源最多的SQL语句.SQL Tune Advisor执行步骤捕获到占用资源较多的SQLAdvisor对捕获到的、占用资源较多的SQL进行分析Advisor产生建议的SQL执行实施SQLSQL Tuning Advisor:OverviewAutomatic SQL Tuning:OverviewAutomatic SQL Tuning automates the entire SQL tuning process and replaces manual SQL tuning.Optimizer modes:Normal modes:Tuning mode or A
阅读全文
摘要：oracle的智能体现:oracle在AWR执行完成一次快照收集之后，ADDM会自动监控本次收集快照之后发现的影响性能的SQL或者其他组件，然后自动调用相应的Advisor进行分析，被报告出分析结果.ObjectivesAfter completing this lesson,you should be able to :Use the SQL Tuning Advisor to:-Identify SQL statements that are using the most resources-Tune SQL statements that are using the most resou
阅读全文
摘要：oracle搜集表的信息，并不是每张表都搜集,一下若干种情况，会被搜集到:统计信息是空的表，会被搜集达到一定比例的表，会被搜集在搜集表信息的时候，加上CASCADE关键字之后，就会搜集此表相关的索引等信息.Statistics Preferences:OverviewUsing Statistics PreferencesPUBLISH:Used to decide whether to publish the statistics to the dictionary or to store them in a pending area before.publish the statistic
阅读全文
摘要：Optimizer Statistics CollectionWays to collect statistics:-Automatically:Automatic Maintenance Tasks（自动搜集统计数据信息的功能，是自oracle10g以后才有的）查看系统里的job信息SQL> select owner,job_name from dba_scheduler_jobs;OWNER JOB_NAME------------------------------ ------------------------------SYS ...
阅读全文
摘要：/* Oracle optimizer 执行选择方式原理分析*/select * from emp where empno=7369;oracle在执行查询计划的时候，并不是说要执行一次全表扫描和一次索引扫描之后，来决定到底是走哪一条路?而是通过数据字典里的统计数据信息，来分析判断到底是走那一条执行计划.|-full table|-index scandata_table---> num_rows(10000)-1/10000 num_rows(10000)-1/2SQL> create table emp1 as select * from emp;Table created..
阅读全文
摘要：Performance Tuning DataType of data gathered:Cumulative statistics:(自从instance启动以来的的数据，累加值)-Wait events with time information-Time model（基于时间模型的比较）Metrics:Statistics rates（度量指标）Sampled statistics:Active session history(样本指标)Statistics by sessionStatistics by SQLStatistics by serviceOther dimensions
阅读全文
摘要：Instance TuningHave well-defined goalsAllocate memory to database structures.Consider I/O requirements in each part of the database.Tune the operating system for optimal performance of the database.Performance Tuning MethodologyThe tuning steps:Tune from the top down.Tune:1.The design2.The appliatio
阅读全文
摘要：Managing Database PerformanceTuning ActivitiesThe three activites in perfomrance management are:Performance planningInstance tuningSQL tunningPerformance PlanningInvestment optionsSystem architetureScalabilityApplication design principlesWorkload testing,modeling,and implementationDeveloping new app
阅读全文
摘要：Dynamic Performance Statistics
阅读全文
摘要：Managing Memory CommpentsAutomatic Memory Management(AMM)-Enable you to specifiy total memory allocated to instance (including both SGA and PGA)指定MEMORY TARGET参数后，Oracle会自动分配内存.实现此特性的前提就是Oracle所在的操作系统支持自动释放内存.ASSM(自动共享内存管理，共享内存实际上就是SGA)如何启动自动内存管理?1、通过OEM，设置Enable属性2、手工方式修改MEMORY_TARGET参数SQL> show
阅读全文
摘要：本章前言:每秒钟，产生的日志文件多少，如果产生很多的redo log 信息，说明负荷量大差生的原因是DML操作太多.假如oracle database 属于dedicate server，使用top session方式排查数据库性能问题，是比较适合的.根据SESSION_ID,就能找到相关的程序，然后据此进行调整.shared server方式，不适合用在批量交易、运行报表时间很长的系统，而是适合联机交易系统.而单纯的联机交易本身不会消耗太多资源的.所以通过top session方式去排查影响性能的问题所在，是有些困难的.此时，通过top services来分析瓶颈所在.被动式的维护，首先寻找
阅读全文
摘要：10g、11g性能管理方面提供哪些新特性.昨天的执行效率很高，突然今天的性能就下降(有可能昨天的执行计划于今天的执行不同导致的).被动监控与主动监控.大火燃烧了，大火还没有蔓延.ObjectivesAfter completing this lesson,you should be able to :Use Enterprise Manager to monitor performanceUse Automatic Memory Management(AMM)Use the Memory Advisor to size memory buffersView performance-relate
阅读全文
摘要：服务器预警信息.给dump区设置阀值，提前预警.快照太旧,提前预警.instance挂起,提前预警.Automated Maintenance TasksAutotask maintenance processMaintenance Window opens.Autotask background process schedules jobs.Scheduler initiates jobs.Resource Manager limits impact of Autotask jobs.Default Autotask maintenance jobsGathering optimizer s
阅读全文
摘要：Advisory Framework所有的Advisory Framework都是基于AWR(Automatic workload Repository)的.Enterprise Manager and AdvisorsDBMS_AVISOR Package ProcedureDescriptionCREATE_TASKCreates a new task in the repositoryDELETE_TASKDelete a task from the repositoryEXECUTE_TASKInitiates execution of the taskINTERRUPT_TASKS.
阅读全文
摘要：检测出网络瓶颈、CPU瓶颈、锁竞争、I/O问题、内存参数设置、发现应用问题（SQL语句所占资源多，负载重）等，ADDM可以根据上述瓶颈问题，可以给出科学合理的建议.如果oracle配置不当（规划日志文件），ADDM也会给出科学合理的建议.oracle中有很多专家，有的擅长SQL，有的擅长空间管理.假如想请专家分析SQL语句，ADDM就可以主动分析，有的专家需要“请”才会主动服务.假如将ADDM比作医生专家的话，那么ADDM就是全科专家；oracle中有很多其他专家，大部分都是专科医生.Automatic Databae Diagnostic Monitor(ADDM)Runs after ea
阅读全文
摘要：SQL> select tablespace_name,contents from dba_tablespaces;TABLESPACE_NAME CONTENTS------------------------------ ---------SYSTEM PERMANENTSYSAUX PERMANENTUNDOTBS1 UNDOTEMP TEMPORARYUSERS ...
阅读全文
摘要：Oracle Optimizer:OverviewThe Oracle optimizer determines the most efficient execution plan and is the most important step in the processing of any SQL statement.The OptimizerEvaluates expressions and conditionsUses object and system statisticsDecides how to access the dataDecides how to join tablesD
阅读全文
摘要：主动式维护被动是维护(critical)AWR本身就是一个数据库,专门用来存放数据库本身运行信息的,通直接的用户数据、业务数据无关.AWR是oracle自我管理的一个基础.OjbectivesAfter completing this lesson,you should be able to :Manage optimizer statisticsManage the Automatic Workload Repository(AWRUse the Automatic Database Diagnostic Monitor(ADDM)Describe and use the advisory
阅读全文
摘要：Remote database job是11g的新特性Creating a Job Array--1、Declare variables of types sys.job and sys.job_array:DECLARE newjob sys.job; newjobarr sys.job_array;--2、Initialize the job array:BEGIN newjobarr := SYS.JOB_ARRAY();--3、Size the job array to hold the number of jobs needed:newjob...
阅读全文
浙公网安备 33010602011771号