oracle sql tuning tips

: = Time_limit_default,Ask_name in VARCHAR2: = NULL,Description in VARCHAR2: = NULL)RETURN VARCHAR2; Case:DECLAREMy_task_name VARCHAR2 (30);My_sqltext CLOB;BEGINMy_sqltext: = ' SELECT 1 from dual ';My_task_name: = Dbms_sqltune. Create_tuning_task (Sql_text = My_sqltext,Bind_list=>sql_binds (ANYDATA. Convertnumber (9)),user_name = ' Noap ',Scope=> ' comprehensive ',Time_limit = 600,Task_name = ' Sql_tuning_test ',Description=> ' TUNING TASK ');END; BEG

to go. In the development, we have to do some optimization of business logic, to avoid complex logic operations, especially to avoid a very large number of cycles of business logic processing, on the one hand we want to simplify business logic, some business logic to the program to deal with, on the other hand we can use SQL very clever to achieve a lot of logic complex requirements, Avoid having to do a lot of complex logic processing, rather than

August 14, 2017, a city of Oracle related Scheduler ETL extraction speed is very slow, the average SQL statement execution time of about 9 seconds, if shown:650) this.width=650; "Src=" http://img.blog.csdn.net/20170814133720856?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqvd3d3x3h1zv94aq==/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Center "width=" "style=" border:0px;vertical-align:mi

is much higher than that of the composite Index query efficiency, while in 11.2.0.3 version Verify that the ad hoc query efficiency of the common composite index is more efficient than the efficiency of the bitmap index) Create TableT (name_id, gender not NULL, location not NULL, Age_range not NULL, data) as Selectrownum, Decode ( Floor(Dbms_random.value (0,2)),0,'M',1,'F') gender, Ceil (Dbms_random.value (0, -) Location , decode ( Floor(Dbms_random.value (0,4)),0,' Child',1,' Young',2,'Middle

add data to the table (delete, update operations, and so on), add the address of the new record to the index, which slows down the new data. Presumably, to be able to read it, it's not exactly right. Knowing the index, we can add the appropriate index above the table to improve the performance of the query statement.You can look at an index as a unique table, SQL query, if the field is indexed field will be indexed to query, then the index of this un

1. New tuning Set Object ***************************************** ---Authorize grant administer any SQL TUNING SET to Scott;---Delete the existing stsbegin Dbms_sqltune. Drop_sqlset (sqlset_name = ' ocpyang_sts '); end;/---new Stsbegin dbms_sqltune. Create_sqlset (sqlset_name = ' ocpyang_sts ', Sqlset_owner = ' SCOTT ', description = ' ocpyangtest '); end;/---Vi

Label:A recent project involves data-table queries of large data volumes. The total data table is about 700 million-2 billion, the primary key to establish a global unique index, the partition policy is hash partition + range partition, most of the time the query condition hit record more than million, a single return to the previous XX record. The following experiences are summed up in the tuning process:(1) Minimize the interval limit, even if the a

The SQL Tuning toolset (SQL Tuning set,sts) is an integral part of the Oracle 10g SQL Tuning Advisor feature. Each tuning toolset contains o

Select * from Table (dbms_xplan.display_cursor (sql_id = ' 9s7pt2ay4t3jg '));DeclareL_result_name Varchar2 (30);L_task_name Varchar2 (£): = ' task_name_9s7pt2ay4t3jg_1 ';L_sqlid Varchar2 (£): = ' 9S7PT2AY4T3JG ';BeginL_result_name: = Dbms_sqltune.create_tuning_task (sql_id = L_sqlid,Scope = ' comprehensive ',Time_limit = 60,Task_name = L_task_name,Description = ' Task to tune a query on a specified table ');Dbms_sqltune.set_tuning_task_parameter (Task_name = L_task_name,Parameter = ' Time_limit

Oracle SQL Tuning Health Check ScriptWe focus on the performance of database system, the main task of database tuning is to optimize SQL. Good data architecture design, with application system middleware and writing a beautiful SQL