Shulou(Shulou.com)05/31 Report--

This article shares with you the content of a sample analysis of SQL PROFILE profiles in Oracle. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. Introduction

Oracle system puts forward the concept of profile in order to allocate and use the resources of the system reasonably. A profile is a configuration file that describes how to use the system's resources (mainly CPU resources). The profile is assigned to a database user, and when the user connects to and accesses the database server, the system allocates resources to him according to the profile.

These include:

1. Manage database system resources.

To allocate resource limits using Profile, you must set the initialization parameter resource_limit to true and default to TRUE.

2. Manage database passwords and authentication methods.

By default, the DEFAULT profile is assigned to the user, which is assigned to each user who creates it. However, the file does not have any restrictions on resources, so administrators often need to create their own profiles according to the environment of their database systems.

two。 Profile limit

Profiles can mainly limit the following indicators of the database system.

1) maximum number of concurrent sessions of the user (SESSION_PER_USER)

2) CPU clock limit per session (CPU_PER_SESSION)

3) the CPU clock limit for each call, which includes parsing, executing commands, getting data, and so on. (CPU_PER_CALL)

4) the longest connection time. After the connection time of a session exceeds the specified time, Oracle will automatically disconnect (CONNECT_TIME)

5) the longest idle time. If a session is idle for more than a specified time, Oracle will automatically disconnect (IDLE_TIME)

6) the maximum number of big data blocks that can be read per session (LOGICAL_READS_PER_SESSION)

7) the maximum number of big data blocks that can be read per call (LOGICAL_READS_PER_CALL)

8) maximum capacity of SGA private area (PRIVATE_SGA)

The definition and restrictions on passwords in the profile are as follows:

1) maximum number of failed login attempts (FAILED_LOGIN_ATTEMPTS)

2) the maximum period of validity of the password (PASSWORD_LIFE_TIME)

3) the number of times the password must be changed before it can be reused (PASSWORD_REUSE_MAX)

4) the number of days a password must pass before it can be reused (PASSWORD_REUSE_TIME)

5) the number of days that the account is locked after exceeding the maximum number of failed login attempts

6) specify the name of the function used to determine the password complexity

After specifying the profile, DBA can manually assign the profile to each user. However, the profile does not take effect immediately, but the profile will not take effect until the parameter RESOURCE_LIMIT in the initialization parameter file is set to TRUE.

3. SQL PROFILE

SQL PROFILE is introduced in ORACLE10g to focus on SQL optimization, which makes up for the shortcomings of storage profile.

DBA can use SQL tuning Advisor (STA) or SQL access Advisor (SAA) to identify SQL statements for better performance

These statements can be saved in the SQL tuning set, an AWR snapshot, or in the current library cache, and once the adjustment candidate is identified, these consultants begin to analyze the captured statements for better performance, then generate specialized statement extensions (called SQL configuration files) and rewrite SQL statements for better performance during execution.

Similar to the storage profile, an SQL profile provides the ability to use a better execution plan (if this execution plan

SQL profiles can also be executed in phases like a storage profile, or limited to a specific session, but most important improvements go beyond the storage profile.

SQLProfile is valid for the following type statements:

SELECT statement

UPDATE statement

INSERT statement (valid only when using the SELECT clause)

DELETE statement

CREATE statement (valid only when using the SELECT clause)

MERGE statement (valid only for UPDATE and INSERT operations).

In addition, you must have system permissions such as CREATE ANY SQL PROFILE, DROP ANY SQL PROFILE, and ALTER ANY SQL PROFILE to use SQL Profile.

4. Test one

Create a tabl

Tpcc@TOADDB > create table T1 as selectobject_id,object_name fromdba_objects where rownum create table T2 as select * fromdba_objects; Table created.

Create an index:

Tpcc@TOADDB > create index t2_idx on T2 (object_id); Index created.

Collect statistics:

Tpcc@TOADDB > execdbms_stats.gather_table_stats (user,'t1',cascade= > true,method_opt= > 'forall columns size 1'); PL/SQL procedure successfully completed.tpcc@TOADDB > execdbms_stats.gather_table_stats (user,'t1',cascade= > true,method_opt= > 'forall columns size 1'); PL/SQL procedure successfully completed.

Execute SQL without HINT

Tpcc@TOADDB > set autotrace ontpcc@TOADDB > select t1.recording journal t2.owner from T1 jurisdiction T2 wheret1.object_name like'% T1% 'and t1.object_id=t2.object_id 42 rows selected.Execution Plan---Plan hash value: 1838229974 Murray- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-| 0 | SELECT STATEMENT | | 2500 | 97K | 498 (1) | 00:00:01 | | * 1 | HASH JOIN | 2500 | 97K | 498 (1) | 00:00:01 | | * 2 | TABLE ACCESS FULL | T1 | 2500 | 72500 | 68 (0) | 00:00:01 | 3 | TABLE ACCESS FULL | T2 | 92021 | 988K | 430 (1) | 00:00:01 |-- -Predicate Information (identified byoperation id):-1-access ("T1". "OBJECT_ID" = "T2". "OBJECT_ID" ) 2-filter ("T1". "OBJECT_NAME" LIKE'% T1% 'AND "T1". "OBJECT_NAME" IS NOT NULL) Statistics--- 1 recursive calls 0 dbblock gets 1789 consistent gets 0 physical reads 0 redosize 2350 bytes sent via SQL*Net toclient 573 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 42 rowsprocessed

Execute SQL with Hint

SQL > select / * + use_nl (T1 T2) index (T2) * / T1 owner from T1 and t1.object_id=t2.object_id T2 where t1.object_name like'% T1% 'and t1.object_id=t2.object_id 42 rows selected.Execution Plan---Plan hash value: 1022743391 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 2500 | 97K | 5069 (1) | 00:00:01 | | 1 | NESTED LOOPS | 2500 | 97K | 5069 (1) | 00:00:01 | | 2 | NESTED LOOPS | 2500 | 97K | 5069 (1) | 00:00:01 | * 3 | TABLE ACCESS FULL | T1 | 2500 | 2500 | 68 (0) | 00:00:01 | * 4 | INDEX RANGE SCAN | T2 _ IDX | 1 | | 1 (0) | 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 11 | 2 (0) | 00:00:01 |- -Predicate Information (identified byoperation id):-3-filter ("T1". "OBJECT_NAME" LIKE'% T1% 'AND "T1". "OBJECT_NAME" IS NOT NULL) 4-access ("T1". "OBJECT_ID" = "T2". "OBJECT_ ID ") Statistics--- 1 recursive calls 0 dbblock gets 304 consistent gets 24 physical reads 0 redosize 2350 bytes sent via SQL*Net toclient 573 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 42 rowsprocessed

Use SQL PROFILE

Find the SQL_ID that executes SQL

Tpcc@TOADDB > select sql_id,sql_text from v$sqlwhere sql_text like'% t1.object _ name%' SQL_ID-SQL_TEXT-4zbqykx89yc8vselect t1.owner from T1 T2 wheret1.object_name like'% T1% 'andt1.object_id=t2.object_ id18bphz37dajq9select / * + use_nl (T1 T2) index (T2) * / t1.owner from T1 andt1.object_id=t2.object_id T2 wheret1.object_name like'% T1% 'andt1.object_id=t2.object_id

Run the stored procedure as follows:

Var tuning_task varchar2 (100,100); DECLARE l_sql_id vSecretsession.roomsqlroomid% type; l_tuning_task VARCHAR2 (30); BEGIN lumbsqykx89yc8v4; l_tuning_task: = dbms_sqltune.create_tuning_task (sql_id = > l_sql_id);: tuning_task:=l_tuning_task; dbms_sqltune.execute_tuning_task (l_tuning_task); dbms_output.put_line (l_tuning_task); END / TASK_114PL/SQL procedure successfully completed.

Check the name of task

Tpcc@TOADDB > print tuning_task;TUNING_TASK-TASK_114

View the execution report

Set long 99999col comments format a200SELECT dbms_sqltune.report_tuning_task (: tuning_task) COMMENTS FROM dual COMMENTS-GENERAL INFORMATION SECTION- -Tuning Task Name: TASK_114Tuning Task Owner: TPCCWorkload Type: Single SQL StatementScope: COMPREHENSIVETime Limit (seconds): 1800Completion Status: COMPLETEDStarted at: 03Unipedia 2016 05:27:21Completed at: 03Univer 2016 05:27:21Completed at: 03According to 2016 05mov 27RV 24lle- -Schema Name: TPCCSQL ID: 4zbqykx89yc8vSQL Text: select t1.* T2.owner from t1 T2 where t1.object_name like'% T1% 'and t1.object_id=t2.object_id----FINDINGS SECTION (1 finding)- -1-SQL Profile Finding (see explain planssection below)- -Apotentially better execution plan was found for this statement. Recommendation (estimated benefit: 83.08%)-Consider accepting the recommended SQL profile. Executedbms_sqltune.accept_sql_profile (task_name = > 'TASK_114', task_owner = >' TPCC', replace = > TRUE); Validation results-TheSQL profile was tested by executing both its plan and the original plan andmeasuring their respective execution statistics. A plan may have been only partially executed if the other could be run to completion in lesstime. Original Plan With SQL Profile% Improved-Completion Status: COMPLETE COMPLETE Elapsed Time (s):. 012865. 004556 64.58% CPUTime (s):. 0124. 0045 63.7% User I Time O Time (s): 00 Buffer Gets: 1787 30283. 1% Physical Read Requests: 0 Physical Write Requests: 0 Physical Read Bytes: 0 Physical Write Bytes: 0 Rows Processed: 42 42 Fetches: 42 42 Executions: 1 1 Notes-1.Statistics for the original plan were averaged over 10 executions. 2.Statistics for the SQL profile plan were averaged over 10 executions.----EXPLAIN PLANS SECTION -1-Original With Adjusted Cost--Plan hash value: 1838229974 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 42 | 1680 | 498 (1) | 00:00:01 | | * 1 | HASH JOIN | 42 | 1680 | 498 (1) | 00:00:01 | | * 2 | TABLE ACCESS FULL | T1 | 42 | 1218 | 68 (0) | 00:00:01 | 3 | TABLE ACCESS FULL | T2 | 92021 | 988K | 430 (1) | 00:00:01 |- -Predicate Information (identified byoperation id):- -1-access ("T1". "OBJECT_ID" = "T2". "OBJECT_ID") 2-filter ("T1". "OBJECT_NAME" LIKE'% T1% 'AND "T1". "OBJECT_NAME" IS NOT NULL) 2-Using SQL Profile-Plan hash value: 1022743391- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- -- | 0 | SELECT STATEMENT | | 42 | 1680 | 152 (0) | 00:00:01 | | 1 | NESTED LOOPS | 42 | 1680 | 00:00:01 | | 2 | NESTED LOOPS | | 42 | 1680 | 152 (0) | 00:00:01 | * 3 | TABLE ACCESS FULL | | T1 | 42 | 1218 | 68 (0) | 00:00:01 | | * 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0) | 00:00:01 | 5 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 11 | 2 (0) | 00:00:01 |-- | -- Predicate Information (identified byoperation id):-3-filter ("T1". "OBJECT_NAME" LIKE'% T1% 'AND "T1". "OBJECT_NAME "IS NOT NULL) 4-access (" T1 "." OBJECT_ID "=" T2 "." OBJECT_ID ")-

Accept the analysis suggestion

The implementation method is given in the report, such as the red section above.

Accept the recommendations of the report and verify the following:

Tpcc@TOADDB > execute dbms_sqltune.accept_sql_profile (task_name= > 'TASK_114',task_owner = >' TPCC', replace = > TRUE); PL/SQL procedure successfully completed.

Perform a test

Then execute the original command as follows:

Tpcc@TOADDB > select t1.handwriting t2.owner from T1 authoring T2 wheret1.object_name like'% T1% 'and t1.object_id=t2.object_id 42 rows selected.Execution Plan---Plan hash value: 1022743391 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 42 | 1680 | 152 (0) | 00:00:01 | | 1 | NESTED LOOPS | 42 | 1680 | 00:00:01 | | 2 | NESTED LOOPS | 42 | 1680 | 152 (0) | 00:00:01 | * 3 | TABLE ACCESS FULL | T1 | 42 | 1218 | 68 (0) | 00:00:01 | * 4 | INDEX RANGE SCAN | T2 _ IDX | 1 | | 1 (0) | 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 11 | 2 (0) | 00:00:01 |- -Predicate Information (identified byoperation id):-3-filter ("T1". "OBJECT_NAME" LIKE'% T1% 'AND "T1". "OBJECT_NAME" IS NOT NULL) 4-access ("T1". "OBJECT_ID" = "T2". "OBJECT_ ID ") Note--SQL profile" SYS_SQLPROF_01534b8309b90000 "used for this statement-this is an adaptive planStatistics--- 35 recursive calls 0 dbblock gets 317 consistent gets 1 physical reads 0 redosize 2350 bytes sent via SQL*Net toclient 573 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 42 rowsprocessed

PROFILE,PS enabled: if you add a few more spaces in the execution, it will not affect the effectiveness of the PROFILE.

5. Maintenance operation

Disable command

As follows:

Begin dbms_sqltune.alter_sql_profile (name = > 'SYS_SQLPROF_01534b8309b90000', attribute_name = >' status', value = > 'disabled'); end; /

Enable command

As follows:

Begin dbms_sqltune.alter_sql_profile (name = > 'SYS_SQLPROF_01534b8309b90000', attribute_name = >' status', value = > 'enabled'); end; /

View the PROFILE used

As follows:

SQL > SELECT task_name,status FROMUSER_ADVISOR_TASKS

Delete PROFILE

BEGIN DBMS_SQLTUNE.DROP_SQL_PROFILE (name = > 'SYS_SQLPROF_01534b8309b90000'); END; thank you for reading! This is the end of this article on "sample Analysis of SQL PROFILE profile in Oracle". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.