Application of Oracle Architecture Knowledge points

Architecture-related Optimization issues:

1. The database is very large, the traffic is very high, the sharing pool is very small: so the shared pool can not store a lot of parsed SQL statements, resulting in a lot of hard parsing, so the database is very slow. This time to increase the sharing pool. If it is managed automatically, increase the size of the SGA.
2. Set: The host of a database that is not often accessed is 4G of memory, to open up 3G sga,500m PGA, due to the OS operating system memory is not enough, causing the host to run slowly, we want to reduce the size of the SGA.
3. Increase the SGA if a large amount of physical reads occur due to the small data buffers.
4. If the sort uses a temporary tablespace, it means that the PGA is too small and if the system has additional memory, consider allocating a portion of the PGA (typically an OLAP system)
5. If the database has a large number of update operations, resulting in a large number of logs resulting in frequent log switching, during the log switchover process, the database will stall, in order to improve performance, the size of the log file needs to be increased.
6. If an application is unable to use the value for the next module because it always detects ORA-01555 errors, the production fails. Need to check why this SQL execution is so slow. Optimized methods: Index, clean up historical data, make table records smaller, or increase the value of undo_retention (this value is only recommended, not mandatory), or you can increase the undo table space.

Specific SQL Optimizations:

2. Construction Environment + not optimized (cycle speed: 40+ seconds

    sqlplus drop table T purge;create table t (x  int   

    create or replace procedure proc1as begin  for< /span> i in  1  ... 100000   loop execute immediate '  insert into t values (' | | i| | '      /

    exec proc1; 

    Col sql_text format A30 

    Set pagesize 

    Select t.sql_text,t.sql_id,t.parse_calls,t.executions from V$sql t where sql_text like '%insert into T values% ' and R ownum<100; 

 # #resetpool. sql: 
 drop table T purge;create table t (x  int   1000  col sql_text format A30  

4. bound variable, moto Speed: 8+ sec

    create or replace procedure proc2asbegin  for  i in  1  ... 100000   loop execute immediate  "  insert into t Values (: x)   "                Span style= "COLOR: #000000" > using I;        Commit End Loop;end;  /

    select  t.sql_text,t.sql_id,t.parse_calls,t.executions from V$sql t Where Sql_text like  %insert into T values% " Span style= "COLOR: #800000" ";  

6. Static rewrite, car speed: 6+ sec

   Create or Replace procedure Proc3asbegin          for inch 1.. 100000         loop                insert into T values (i);                commit;        End Loop;end; /

8. Batch submission, Speed: 2 seconds

   Create or Replace procedure Proc4asbegin          for inch 1.. 100000         loop                insert into T values (i);        End Loop;commit;end; /

10. Set notation, airplane speed:. 14 seconds

    Select rownum from dual connect by level <=100000;

12. Direct path, rocket speed:. 89 seconds, at 100,000 is. 23 So it takes a quantity to see it quickly.

    Select 1000000;

14. Parallel setup, spaceship Speed: Need to have multi-CPU: This machine measured 1.05 seconds than 6 poor performance, generally in the machine idle and strong performance when used.

    4 Select rownum x from dual connect to level <=1000000;

Application of Oracle Architecture Knowledge points