42.性能管理
1.内存管理
--10g
--pga内存自动管理
sys@ORCL10G 2023-03-05 09:40:57> show parameter pga_a;
NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pga_aggregate_target big integer 371M
sys@ORCL10G 2023-03-05 09:42:14> show parameter work;
NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
fileio_network_adapters string
workarea_size_policy string AUTO
--workarea_size_policy：值是auto
--pga_aggregate_target：大于0
--：满足以上两个，就是内存自动管理
--查看命中率配置内存，当ESTD_PGA_CACHE_HIT_PERCENTAGE第一个值是100，内存取PGA_TARGET_FOR_ESTIMATE值
sys@ORCL10G 2023-03-05 09:45:33> select *from v$pga_target_advice;
PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADV BYTES_PROCESSED ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
----------------------- ----------------- --- --------------- ------------------- ----------------------------- --------------------
48627712 .125 ON 514048 0 100 0
97255424 .25 ON 514048 0 100 0
194510848 .5 ON 514048 0 100 0
291766272 .75 ON 514048 0 100 0
389021696 1 ON 514048 0 100 0
466825216 1.2 ON 514048 0 100 0
544629760 1.4 ON 514048 0 100 0
622434304 1.6 ON 514048 0 100 0
700238848 1.8 ON 514048 0 100 0
778043392 2 ON 514048 0 100 0
1167065088 3 ON 514048 0 100 0
1556086784 4 ON 514048 0 100 0
2334130176 6 ON 514048 0 100 0
3112173568 8 ON 514048 0 100 0
--sga自动管理
--sga_max_size =<具体值>：通常sga_max_size=RAM*3/4
--sga_taget>0
--sga_target<=sga_max_size
--最后列的值不再变化，就是sga的值
sys@ORCL10G 2023-03-05 10:10:24> select * from v$sga_target_advice;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS ESTD_BUFFER_CACHE_SIZE ESTD_SHARED_POOL_SIZE CON_ID
---------- --------------- ------------ ------------------- ------------------- ---------------------- --------------------- ----------
456 .5 14 1.0833 12902 144 208 0
684 .75 13 1 12902 336 224 0
912 1 13 1 12902 576 224 0
1140 1.25 13 1 12902 768 224 0
1368 1.5 13 1 12902 1008 224 0
1596 1.75 13 1 12902 1200 224 0
1824 2 13 1 12902 1440 224 0
sys@ORCL10G 2023-03-05 09:55:27> show parameter sga;
NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM
------------------------------ ----------- ---------------------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1120M
sga_target big integer 1120M
--AMM
--memory_max_target=ram*80%
--memroy_target>0
--pga_aggregate_target和sga_target不需要设置大小
--pga+sga自动管理
sys@ORCL11G 2023-03-05 10:04:06> show parameter memory;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 1536M
memory_target big integer 1536M
shared_memory_address integer 0
sys@ORCL11G 2023-03-05 10:05:05> show parameter pga_agg;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 0
sys@ORCL11G 2023-03-05 10:05:36> show parameter sga_tar;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 0
--oracle12c
--pga最大值可以限制
sys@ORCL 2023-03-05 10:09:47> show parameter pga_a;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 2G
pga_aggregate_target big integer 02.动态性能视图，无效对象和不可用对象
--自动处理无效对象
alter index <> rebuild [tablespace <>];
PL/SQL
select text from user_source where naem = '<>'
--读代码找出错误地方
--重新编译
alter <function|procedure|package> <> compire;
--查找无效对象
select object_name,object_type from dba_objects where status = 'INVALIED';
--测试
scott@ORCL10G 2023-03-05 10:16:00> alter table emp move;
Table altered.
Elapsed: 00:00:00.06
scott@ORCL10G 2023-03-05 10:16:06> select index_name,status from user_indexes;
INDEX_NAME STATUS
------------------------------ --------
PK_DEPT VALID
PK_EMP UNUSABLE
Elapsed: 00:00:00.04
scott@ORCL10G 2023-03-05 10:16:27> alter index PK_EMP rebuild;
Index altered.
Elapsed: 00:00:00.02
scott@ORCL10G 2023-03-05 10:17:10> select index_name,status from user_indexes;
INDEX_NAME STATUS
------------------------------ --------
PK_DEPT VALID
PK_EMP VALID
Elapsed: 00:00:00.01
