随笔分类 - Perf Tun
摘要：placheholder
阅读全文
摘要：placeholder
阅读全文
摘要：placeholder
阅读全文
摘要：placeholder
阅读全文
摘要：ObjectivesAfter completing this lesson,you should be able to do the following:Set up Database Resource ManagerAssign users to Resource Manager groupsCreate resource plans within groups更加强大的手段来控制每个用户或者每组用户资源使用情况.
阅读全文
摘要：Temporary tablespace里面只有temporary segment,没有 permanent segement.对temporary tablespace的操作，不会更新data dictionary,执行速度会非常快.因为Permanent 类型的segment进行更新的话，都需要更新数据字典.不同的用户可以使用不同的临时表空间.不同的表空间可以分步在不同的磁盘上；假如不同的用户具有不同的临时表空间，在进行大数据排序的时候，就可以减少同一个磁盘的I/O读写，提高性能.一个segment可以被不同的用户使用,使用里面不同的extent.为不同的用户设置不同的临时表空间.Temp
阅读全文
摘要：OEMPGA Target Advice HistogramsV$PGA_TARGET_ADVICE_HISTOGRAM predicts how histograms shown in V$SQL_WORKAREA_HISTOGRAM evolve.STATISTICS_LEVEL must be set to at least TYPICAL.Auto PGA and OEMOverviewThe automatic sort area management feature is :Easier to set up and size than the *_AREA_SIZEP parame
阅读全文
摘要：PGA Target Advice StatisticsV$PGA_TARGET_ADVICE predicts how cache hit percentages shown in V$PGASTAT evolve.STATISTICS_LEVEL must be set to at least TYPICAL.（提示:如果想使V$PGA_TARGET_ADVICE工作，必须将STATISTIC_LEVEL修改成至少TYPICAL级别）SQL> select * from v$PGA_TARGET_ADVICE;PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FA
阅读全文
摘要：cache hit percentage这个参数，可以作为DBA衡量PGA相关参数指标是否合理的一个重要标准.SQL> SELECT low_optimal_size "Low",High_optimal_size "Hight",optimal_executions "Opt",onepass_executions "One",multipasses_executions "Multi",total_executions "Total" 2 FROM v$sql_wo
阅读全文
摘要：Auto Sort Area ManagementkParameter for automatic sort area management:-PGA_AGGREGATE_TARGET(Ranges from 10MB to 4000GB)-WORKAREA_SIZE_POLICY （AUTO | MANUAL）(Oracle 推荐使用自动化管理)Replaces all *_AREA_SIZE parametersSQL> show parameter PGA_AGGREGATENAME TYPE VALUE---...
阅读全文
摘要：ObjectivesAfter completing this lesson,you should be to do the following:List the operations that use temporary space.Create and monitor temporary tablespaces.Identify actions that use the temporary tablespaceDescribe and different disk sorts and memory sortsIdentify the SQL operations that require
阅读全文
摘要：Generate the Execution PlanCan be used without tracingNeeds the plan_table table utlxplan.sqlCreate the explain plan:SQL> explain plan for 2 select * from my_all_objects;Explained.Query plan_table TableQuery plan_table to display the execution plans:Query plan_table directly.Use script utlxpls.sq
阅读全文
摘要：Understanding OptimizerThe query optimizer performs the following steps:The optimizer generates a set fo potential plans for the SQL statement based on available access paths and hints.The optimizer estimates the cost of each plan based o statistics in the daa dictionary for the data distribution an
阅读全文
摘要：Hint帮助Optimizer得到最优的查询结果.Using Hints in SQLSELECT * FORM employees;SELECT /*+ First_rows */ FROM employees;Optimizer Plan StabilityUsers can stabilize execution plans,to force applications to use a desired SQL access path.A consistent execution path is thereby maintained through database changes.Thi
阅读全文
摘要：ObjectivesAfter completing this lesson,you should be able to do the following:Control optimizer optionsUse optimizer hintsEmploye plan stabilityUse store outlines(在10g,11g已经过时)Use SQL Trace and TKPROFOverviewThe purpose of this lesson is:To provide mthods to determine the resources used by SQL state
阅读全文
摘要：Query dba_tables for IOTDBA_TABLES->IOTSQL> SELECT table_name,iot_name,iot_type FROM dba_tables WHERE table_name LIKE '%IOT%';TABLE_NAME IOT_NAME IOT_TYPE------------------------------ ------------------------------ ------------SYS_IOT_OVER_12334 ...
阅读全文
摘要：Index-Organized TablesIOT表要求必须有主键.heap表可以没有主键.没有主键的表，不能称之为IOT表.IOT and Heap TablesCompared to heap tables,IOTs have:Faster key-based access to table dataReduced storage requirementsSecondary indexes and logical rowidsIOTs have the following restrictions:-Must have a primary key-Cannot be clusteredCr
阅读全文
摘要：Compressed IndexesCREATE INDEX t_idx on t(owner,object_type,object_name);SQL> ANALYZE INDEX t_idx validate structure;Index analyzed.SQL> create table idx_stats 2 as 3 select 'noncompressed' what,a.* from index_stats a;Table created.lab1->dataSQL> drop index t_idx;Index dropped.SQ
阅读全文
摘要：ObjectivesAfter completing this lesson,you should be able to do the following:Explain the role of the DBA in tuning applications.Move tables using the ALTER TABLE command.Redefine a table onlineCreate different types of indexesBuild and manage index-organized tablesExplain and plan OLTP,DSS,and hybr
阅读全文
摘要：ObjectivesAfter completing this lesson,you should be able to do the following:Create materialized viewsRefresh materialized viewsCreate nested materialized viewsCreate UNION ALL materialized viewsExplain the use of query rewritesEnable and control query rewritesMaterialized ViewsInstantiations of a
阅读全文
浙公网安备 33010602011771号