Net8 Configuration Net8 is to establish network sessions and transfer data between machines.. Net8 is important to connect the database from remote. Server à server, server à client & application à server vice versa. Ex: 192.168.200.7 – client (Oracle software needed) Entries of S/W: TNSnames.ora& & SQLnet.ora- optional 192.168.200.107 – Server (Oracle software & DB needed) Entries of S/W: TNSname.ora, listener & SQLnet.ora- optional TNSnames.ora Configuration file that defines databases addresses for establishing connections to them like (Host, DB name & port). It located on both client and server systems. SQLnet.ora The sqlnet.ora file is the profile configuration file. It resides on the client machines and the database server.…
-
-
Oracle 10g & 11g Tuning features-19
10g & 11g Tuning features 10G features: ASSM- Automatic segment space management. It’s a query tuning based feature. We can do a rebuild online. Sql> alter table emp enable row movement; Sql> alter table emp shrink space; We can de-fragment the table by using above two methods. Internally, it will move the data block by block and there will be slowness. Sql> alter table emp shrink space compact; – Compact only work, when the table is not used by anyone. Sql> alter table emp shrink space cascade; – It will shrink both table and indexes. We can use advisory to use the better option. Sql> select * from dba_advisor_logs;…
-
Oracle Memory Tuning-18
Memory Tuning Shared pool (LC, DDC) Database buffer cache (Default, Keep & Recycle) Redo buffer cache Library cache: To find out the issue we can use the following methods. V$librarycache , AWR & OEM V$librarycache Columns: Namespace – Object Name Gets – No. of parsing Gethistratio – Ratio of Phrasing Pins – No.of execution plan Pinhistratio – Ratio of execution plan Reloads – No. of rephrasing Invalidation – No. of rephrasing for DDL alter table, Column invalidation. Formula: Sql> select gethistratio100, pinhitration100 from v$librarycache; 90% – Good <90% – Problem on LB. OR Sql> select [sum(reloads)/sum(pins)] from v$librarycache; <5% – Good 5% – Problem on LB. Data Dictionary cache V$rowcache,…
-
Oracle AWR Report – Automatic Workload Repository PT-17
AWR Report – Automatic Workload Repository It will generated a performance related server reports. 10 g Feature. Prior to 10 g ‘Stats pack report’ has used. AWR Stats Pack Text (or) HTML Text Any timeframe with ASH Any timeframe Server level all resources reports Parameters: Statistics_level = Basic/Typical/All – Based on the parameter report will be generated. The information will gathered from following five categories. Base Statistics Matrix Statistics SQL Statistics Active Sessions Advisory Results Physical Reads, CPU Base+ Memory, Object memory utilization, Every past 10 minutes once Logical reads from the query All active session with RAM usage SQL,SQL Tuning, Memory, Segment & Undo Advisory…
-
Oracle Explain plan Performance tuning-16
Explain plan Analyze to gather statistics in Oracle 8i and above, Use DBMS_STATS. ANALYZE TABLE <table> VALIDATE STRUCTURE; — Validate table structure. dbms_stats.set_table_prefs – From 11g onwards. Designed to allow the DBA more control over the freshness of their statistics. DBMS_XPLAN.xxx DISPLAY – to format and display the contents of a plan table. DISPLAY_AWR – to format and display the contents of the execution plan of a stored SQL statement in the AWR. DISPLAY_CURSOR – to format and display the contents of the execution plan of any loaded cursor. DISPLAY_SQL_PLAN_BASELINE – to display one or more execution plans for the SQL statement identified by SQL handle DISPLAY_SQLSET – to…