Oracle Initialization Parameters & Performance View 1. Database version LEO1@LEO1>select * from v$version; BANNER ——————————————————————————-…
Initialization parameter
Performance View
Database Version
Set up the
Parameter and pass
Analyze the optimal memory size for the database
:
is
A memory adjustment parameter in,
The automatic memory management aspect continues to be enhanced, originally
Can be in
For automatic management and distribution,
That is, it can be automatically managed
And can be automatically managed
, the two parts of the integrated management, automatic adjustment of all memory area size.
The default for
alter system set memory_target= 1000m scope=spfile;
alter system set sga_max_size=600m scope=spfile;
alter system set pga_aggregate_target=400m scope=spfile;
Is to set
How much physical memory does it take up? One is
How much memory space can the region occupy
How much space is there,
is
Upper limit, if only set
There is no set
,
think
Do not use automatic memory management.
If you just set
, no Settings
,
automatically
Set to
.
If both values are set, then
The upper limit of is
.
the
It’s dynamic allocation
.
Size, according to
Run state to reassign
Size of each memory area.
in
Need to be set separately (manual management).
Set up and
and
The relationship between
)
Set to the
value
And the magnitude is equal to
Consistent.
and
If the size is set, these two parameters will be used as the minimum starting value
Set the size,
Not set size
then
The initialization value
Without setting the size,
Set the size
then
The initialization value
and
None of them are set to size
Size is automatically allocated based on database health. However, there is a fixed percentage allocated when the database is started:
)
No setting or equal
(
The default for
)
The default for
It’s canceled in its initial state
The role of total and
Consistent in memory management, full downward compatibility.
(There are also three ways to get it right
and
Size to allocate)
If the value is set, it will be automatically adjusted
In the
Memory area,
Is dependent on
The size is set separately.
and
It can’t grow and shrink automatically.
and
None of them are set,
The size of each memory area should be clearly set, can not automatically adjust the size of each memory area.
It doesn’t automatically grow and contract.
Set up and
This situation and
The same does not use memory for automatic management
and
The values are
By the
Automatic resizing,
and
Is the size of the
Analyze the optimal memory size for the database
:
Recommended memory size
: Memory baseline factor,
:
(Official documentation)
, the total database time required to complete the current database workload (that is, the database time consumed by all users), which is a recommended value that will be based on
The parameters change
:
(Official documentation)
:
The version number of this view snapshot
when
When,
when
When,
The current value
when
When,
when
When,
when
When,
when
When,
Is before
Three quarters, but
To increase the
The price is very high, we can put
Modified into
To save our memory resources
The contents of the parameter file take effect only after the database is restarted
Insufficient permissions
switch
The user
Shut down the instance
Made a few corrections
View to make reasonable adjustments
To improve resource utilization.
By adjusting the parameters
Size of demo
Generate different execution plans
: This parameter is used for
The weight correction in calculating the index cost
The higher the value of, the lower the chance of using the index,
Full table scan is preferred
The lower the value of, the higher the chance of using the index,
Tend to go index
The default value is
Clean up the environment
create
table
records
create
The index
Time execution plan
For sub-consistent read, only data blocks are scanned but index blocks are not
produce
Consistency read
from
Modified into
Prefer to go to the index
Subconsistent read, first scan the index block and then according to
Scanning data blocks
produce
Consistency read
The more times, the more consumed the system
More resources, this is not reasonable, we should avoid in production.
By setting parameters
Different values, demo pairs
Impact of efficiency
This initialization parameter is called how many blocks to read at a time
Multiple data blocks can be read at a time. This parameter value is not infinite, on most platforms
Are all
. general
That is to say
Is the maximum for most operating systems at one time
If there are any other restrictions to come from here
It’s going to subtract the initialization parameters
The reason why the maximum value of
And also for conservative strategy.
) Full table scan
(
) : at this time
Multiple block reads are supported
Index fast full scan
(
) : Multiple block reads are supported when indexes are read in parallel
)
: You can make it bigger, but not as big as possible
) is also affected by the operating system
Limitations of its own
Clean up the environment
create
table
create
table
create
The index
Table size is
(section head
Data),
Table size
(table
Index)
Set multiple read blocks to read at a time
A data block
block
Once again, the statistics will stabilize
block
Approximately equal to the
Time,
Need to read
time
To read all the records.
through
block
Approximately equal to the
Time,
Need to read
time
In order to calculate the final result
Set multiple read blocks to read at a time
A data block
block
From the original
To reduce the
.
If the cost is reduced, the parameters are in effect
time
Time,
From the read
Time is reduced to
Time,
Resource consumption is greatly reduced,
Efficiency has been greatly improved.
From the original
To reduce the
.
The cost is also reduced, which means the parameters are working
time
The index from
Time is reduced to
Time,
The frequency is greatly reduced,
The efficiency of reading naturally increases.
The original link