1 For oracle spm and oraclespm

1 For oracle spm and oraclespm
Oracle11g new feature SPM
It helps maintain the SQL statement characteristics and only allows execution of execution plans with improved performance.
Unlike stored outlines, spm is used to stabilize SQL Performance, while store outlines is used to freeze SQL Execution plans.


Event Column
1. Enable optimizer_cature_ SQL _plan_baselines to capture SQL statements
SQL> alter session set optimizer_capture_ SQL _plan_baselines = true;
Session altered.
SQL> select * from objs where object_id = 2;
No rows selected
SQL> select * from objs where object_id = 2;
No rows selected
SQL> alter session set optimizer_capture_ SQL _plan_baselines = false;
Session altered.
2. query dba_ SQL _plan_baselines and determine the SQL spm status.
SQL> select plan_name, SQL _handle, enabled, accepted, fixed,
2 module, SQL _text
3 from dba_ SQL _plan_baselines;
PLAN_NAME SQL _HANDLE enabled accepted fixed module SQL _TEXT
---------------------------------------------------------------------------------------------------------------------------------------------------
SQL _plan_4pzq3z6xcqkpwd0981_3 SQL _4afec3f9bacb4abc yes no SQL * Plus select * from objs where object_id = 2
3. Add indexes and change the environment before running SQL
SQL> alter session set optimizer_capture_ SQL _plan_baselines = true;
Session altered.
SQL> select * from objs where object_id = 2;
No rows selected
SQL> select * from objs where object_id = 2;
No rows selected
SQL> alter session set optimizer_capture_ SQL _plan_baselines = false;
Session altered.
4. query dba_ SQL _plan_baselines
SQL> select plan_name, SQL _handle, enabled, accepted, fixed, module, SQL _text from dba_ SQL _plan_baselines;
PLAN_NAME SQL _HANDLE enabled accepted fixed module SQL _TEXT
---------------------------------------------------------------------------------------------------------------------------------------------------
SQL _PLAN_4pzq3z6xcqkpwbe4c314c SQL _4afec3f9bacb4abc yes no SQL * Plus select * from objs where object_id = 2
SQL _plan_4pzq3z6xcqkpwd0981_3 SQL _4afec3f9bacb4abc yes no SQL * Plus select * from objs where object_id = 2
SQL>
5. Test
SQL>
SQL> set autotrace on
SQL> select * from objs where object_id = 2;
No rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 954894094


--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 1 | 98 | 247 (1) | 00:00:03 |
| * 1 | table access full | OBJS | 1 | 98 | 247 (1) | 00:00:03 |
--------------------------------------------------------------------------


Predicate Information (identified by operation id ):
---------------------------------------------------


1-filter ("OBJECT_ID" = 2)


Note
-----
-SQL plan baseline "SQL _plan_4pzq3z6xcqkpwd0981123" used for this statement




Statistics
----------------------------------------------------------
255 recursive cballs
0 db block gets
1044 consistent gets
879 physical reads
0 redo size
1343 bytes sent via SQL * Net to client
513 bytes encoded ed via SQL * Net from client
1 SQL * Net roundtrips to/from client
28 sorts (memory)
0 sorts (disk)
0 rows processed


SQL> alter session set optimizer_use_ SQL _plan_baselines = false;
Session altered.
SQL> select * from objs where object_id = 2;
No rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 613004408


Bytes -----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -----------------------------------------------------------------------------------------------
| 0 | select statement | 1 | 98 | 2 (0) | 00:00:01 |
| 1 | table access by index rowid | OBJS | 1 | 98 | 2 (0) | 00:00:01 |
| * 2 | index range scan | IDX_OBJSID_OBJS | 1 | 1 (0) | 00:00:01 |
Bytes -----------------------------------------------------------------------------------------------


Predicate Information (identified by operation id ):
---------------------------------------------------


2-access ("OBJECT_ID" = 2)




Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
2 consistent gets
4 physical reads
0 redo size
1343 bytes sent via SQL * Net to client
513 bytes encoded ed via SQL * Net from client
1 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

We can see that when optimizer_user_ SQL _plan_baselines is started, the index should be optimal but not used.
If the plan_name accecpt is no, the index plan is affected.

In oracle, HAVING count (1) group by sex having count (*)> 4; what is having? How to use it?

For example, the following data
Id value
1 2
1 3
2 3
3 5
3 6

You can write a statement to count the group of values.
Select id, sum (value) from table group by id having sum (value)> = 5
The result is
1 5
3 11

In fact, this sentence means
Select id, sum (value) from table where sum (value)> = 5 group by id
However, in oracle, calculated fields cannot be used as conditions, so having is used for representation.
In your example, count (*) is also a truth. Of course, the aggregate functions such as avg, max, and min are the same.

How Does oracle find which package uses a table (I know which table)

If you want to find a table, you need to find the keywords. If you do not know the keywords of the table name. Then there are only a few operations on the table. drop and create cannot be processed in the package. Query select, update, and delete, followed by the table name,
This is how many tables are used in the package and what tables are used.

If, in turn, the number of packages in a table and the storage process are used, it will be simpler. Use Toad for Oracle and name the table F4 in the editor, click used.