Databases Archives

Alexey Brysov asked:

Say goodbye to endless night shifts spent in the server room! Farewell to degrading manual jobs sull'archiviatura, transfer and recovery of data. Bye-bye the sleepless after hours! Most do not have to spend all that cronometrino achieve the maintenance and daily operation on a general database to Oracle, however complex it is. Rental of  invisible assistant to monitor databases instead of you! The DBA Easy Control for Oracle is http://www.dbaeasy.com/ be trained in minutes and makes a perfect time to say what to do. Being as becoming professional developers of the database, easy control of a DBA assistant highly educated and professional format to a coordinator database busy. You can check the log carefully for errors, look at the free space – based on your threshold, keep an eye on access rights, look around the archive log and control many other parameters important for multiple databases to Oracle.  relax downloading daily operation to a server quick! The DBA Easy Control automates routine operations of preparing an application to SQL to be performed manually or periodically by the system programmer. The tables of transfer, sections and subsections, index reconstruction automatically calculate the space required for a transfer, add and resize files and do many other important tasks simultaneouslythanks parallel simultaneous execution of the work.  see what 's that turn at any moment! The DBA Easy Control at any time allows instant access to basic information data. Long delays and no need to connect to any database for an update on its condition! The DBA Easy Control provides information about free space in tables and limits, user roles and privileges as well as information about how they were obtained, the current settings for verification of the system and, given the allocation and revocation of and roles and privileges of this and many more. You can search information easily to many databases. More databases to direct, the more important control of DBA becomes easy as your irreplaceable assistant!  hire a secretary for personnel with technical basis! Being a coordinator accurate, easy to control DBA maintains the strain of the history of change for all databases. The easy access to the history of change is essential when studying the issues cariate error on failure, on and on breaches of security. The DBA Easy Control keeps all documentation and manuals in a single repository, allowing all users access to these system files. The DBA Easy Control for  is not a human being and can not be assumed to do your work. You can 't pay him for the work for you, but it can buy to help with your daily routine and make your work easier, which is more effective. Http://www.dbaeasy.com/ call for more information and load programs object the free evaluation version.

Visit Las Vegas!

Sagar Patil asked:

Statspack is een set van prestatie-monitoring en rapportage nutsbedrijven door Oracle voor Oracle8i en hoger. Een gebruiker wordt automatisch aangemaakt door de installatie script – gebruiker, PERFSTAT, is eigenaar van alle objecten die nodig zijn bij dit pakket. Voor meer informatie over Statspack, lees de documentatie in het bestand $ ORACLE_HOME / RDBMS / admin / spdoc.txt.

Installeer Statspack

Voor het installeren van het pakket, ofwel wijziging van de ORACLE_HOME RDBMS / admin directory, of volledig de ORACLE_HOME / RDBMS / admin directory bij het aanroepen van de installatie script SPCREATE.

Als u de installatie script, moet u gebruik maakt van SQL * Plus en sluit als een gebruiker met SYSDBA voorrecht. Bijvoorbeeld, start SQL * Plus, dan:

Op UNIX: SunOS / HP-UX / Linux

SQL> connect / AS SYSDBA

SQL > @? / RDBMS / admin / spcreate

Op Windows: XP/NT/2000/2003

SQL> connect / AS SYSDBA

SQL > @% ORACLE_HOME% rdbmsadminspcreate

De SPCREATE installeren script drie andere scripts. Deze scripts worden automatisch, zodat u niet hoeft te draaien hen:

* SPCUSR: Maakt de gebruiker privileges en subsidies

* SPCTAB: Maakt de tabellen

* SPCPKG: Maakt het pakket

configureren Statspack

Hoe te lokaliseren Ststapack het huidige niveau?

– Kijk op tafel PERFSTST. STATS $ TALK

of

– Uitvoeren spreport.sql en u zult merken dat alongwith de snapids vermeld

STATS $ TALK zal blijken niveau voor elk Momentopname geregistreerd

Change Level

uitvoeren statspack.snap (i_snap_level => 7, i_modify_parameter => 'waar');

Niveaus> = 0 algemene prestaties Statistieken

Niveaus> = 5 Aanvullende gegevens: SQL statements

Niveaus> = 6 Aanvullende gegevens: Plannen SQL en SQL Plan Usage

Niveaus > = 10 Aanvullende Statistieken: ouder en kind hang

Met Statspack (het verzamelen van gegevens)

sqlplus perfstat

– Neem een performance momentopname

uitvoeren statspack.snap;

— – krijg je een lijst van snapshots

kolom snap_time formaat A21

selecteer snap_id, to_char (snap_time, 'maandag dd, jjjj hh24: mm: ss ") snap_time

van SP $ momentopname;

Running een rapport Prestaties

— –

– Voer het Statspack verslag:

@? / RDBMS / admin / spreport.sql

Zoek Hard slaan SQL uit StatPack Reposistory

1. Inloggen als gebruiker op PERFSTAT database.

Het zal niet werken tenzij U login als PERFSTAT gebruiker.

2. Zoek met behulp van DBID

"selecteer dbid uit stats $ sql_summary"

3. Zoek MIN (SNAP_ID) pBgnSnap & Max (SNAP_ID) pEndSnap uit

selecteer min (snap_id), max (snap_id), min (snap_time), max (snap_time) uit stats $ momentopname
< br /> waarin to_number (to_char (snap_time, 'HH24'))> 10 en to_number (to_char (snap_time, 'HH24')) <13 en trunc (snap_time) = trunc (sysdate) Toon Alle SQL Stmts volgorde van Logische Leest selecteren e.hash_value "E. HASH_VALUE" , e.module "Module"

, e.buffer_gets – NVL (b.buffer_gets, 0) "Buffer Gets"

, e.executions – NVL (b.executions, 0) "executies"

, Ronde (decoderen ((e.executions – NVL (b.executions, 0)), 0, to_number (NULL)

, (e.buffer_gets – NVL (b.buffer_gets, 0)) /

(e.executions – NVL (b.executions, 0))), 3) "Gets / Uitvoering"

, Ronde (100 * (e . buffer_gets – NVL (b.buffer_gets, 0)) / sp920.getGets (: pDbID,: pInstNum,: pBgnSnap,: pEndSnap, "NO"), 3) "Procent van Total"

, round ((e.cpu_time – NVL (b.cpu_time, 0)) / 1000000,3) "CPU (s)"

, Ronde ((e.elapsed_time – NVL (b.elapsed_time, 0 )) / 1000000,3) "Verstreken (s)"

, Ronde (e.fetches – NVL (b.fetches, 0)) "haalt"

, sp920 . getSQLText (e.hash_value, 400) "SQL statement"

uit stats $ sql_summary e

, stats $ sql_summary b

waar b. snap_id (+) =: pBgnSnap

en b.dbid (+) = e.dbid

en b.instance_number (+) = e.instance_number
< br /> en b.hash_value (+) = e.hash_value

en b.address (+) = e.address

en b.text_subset (+) = e . text_subset

en e.snap_id =: pEndSnap

en e.dbid =: pDbId

en e.instance_number =: pInstNum

Om met 3 desc

Toon SQL Stmts waar SQL_TEXT als'% '

selecteer

e.hash_value "E. HASH_VALUE "

, e.module" Module "

, e.buffer_gets – NVL (b.buffer_gets, 0)" Buffer Gets "

, e.executions – NVL (b.executions, 0) "executies"

, Ronde (decoderen ((e.executions – NVL (b.executions, 0)), 0, to_number (NULL) < br />
, (e.buffer_gets – NVL (b.buffer_gets, 0)) /

(e.executions – NVL (b.executions, 0))), 3) " Gets / Uitvoering "

, Ronde (100 * (e.buffer_gets – NVL (b.buffer_gets, 0)) / sp920.getGets (: pDbID,: pInstNum,: pBgnSnap,: pEndSnap," NO '), 3) "Procent van Total"

, Ronde ((e.cpu_time – NVL (b.cpu_time, 0)) / 1000000,3) "CPU (s)"

, Ronde ((e.elapsed_time – NVL (b.elapsed_time, 0)) / 1000000,3) "Verstreken (s)"

, Ronde (e.fetches – NVL (b . haalt, 0)) "haalt"

, sp920.getSQLText (e.hash_value, 400) "SQL statement"

uit stats $ sql_summary e
< br />, stats $ sql_summary b

waar b.snap_id (+) =: pBgnSnap

en b.dbid (+) = e.dbid
< br /> en b.instance_number (+) = e.instance_number

en b.hash_value (+) = e.hash_value

en b.address (+) = e . adres

en b.text_subset (+) = e.text_subset

en e.snap_id =: pEndSnap

en e.dbid = 2863128100

en e.instance_number =: pInstNum

en sp920.getSQLText (e.hash_value, 400), zoals'% ZPV_DATA% '

bestelling door 3 desc

ophalen gehele SQL + uitvoeringsplan uit Statspack voor een tabel

ophalen SQL plan moet je statspack werken op niveau 7

1. sprepsql.sql

De SQL-verslag (sprepsql.sql) is een rapport voor een specifieke SQL statement. De SQL-verslag wordt meestal uitgevoerd na onderzoek van de hoge belasting SQL delen van de volksgezondheid report.The SQL rapport bevat gedetailleerde statistieken en gegevens voor een enkel SQL statement (zoals geïdentificeerd door de hashwaarde in Statspack verslag).
< br /> 2. Hashwaarde is bekend

– Selecteer * uit STATS $ SQLTEXT waar hash_value = '% uit stats pack%' bestellen per stuk;

– Voor een object eerst de object_id < br />
SELECT * FROM sys.obj $ where naam = "transactie"

selecteer snap_time

snap_id,

plan_hash_value,

OBJECT #,

OBJECT_NAME,

BEDIENING,

opties,

COST ,

IO_COST,

CARDINALITY,

STANDPUNT,

CPU_COST,

OPTIMIZER ,

SEARCH_COLUMNS,

bytes,

DISTRIBUTIE,

TEMP_SPACE,

ACCESS_PREDICATES ,

FILTER_PREDICATES

uit stats SQL_PLAN $ a, $ STATS TALK b waarin bezwaar #='&& object_id 'en a.snap_id = b.snap_id;

http://OracleDbaSupport.co.uk is een blog-site van Sagar Patil, een onafhankelijke consultant Oracle met een groot inzicht in hoe de Oracle-database-engine & Oracle Applications samenwerken.

Ik ben een Oracle Certified RAC DBA met meer dan tien jaar ervaring in de ondersteuning van productie en de ontwikkeling instances van Oracle databases.

– Expert in gebouw 10g RAC systemen alsmede rman, data guard backup en recovery-strategieën voor de productie, het testen en de ontwikkeling systemen.

– Instructeur voor ontwikkelaars op Oracle architectuur, Oracle9i / 10g functies, tuning methodologieën, database replicatie, PL / SQL en Oracle HTTP-server.

– Gebruikers van Statspack, SQL Trace, OEM Performance Manager, Quest Central, Quest Foglight, Quest SQL effect, Bennchamark fabriek, Quest SQL Optimizer, TOAD en aangepaste scripts.

– Schrijver van gedetailleerde normen en praktijken voor Oracle installeert, upgrades, tuning & back-ups op Oracle databases en PL / SQL.Developer, ontwerper en uitvoerder van de Ramp terugwinning back-up procedures

– Leader over grote projecten in alle fasen van ontwikkeling, testen en ondersteuning

Ik kan worden ingehuurd op een korte mededeling, te hanteren strategie, ontwerp, implementatie, trouble-shooting, DBA dekken, remote monitoring, en opleiding.

Bezoek www.oracledbasupport.co.uk voor Oracle DBA uw behoeften.

Domain Name Registration

Karthick Thanigaimani asked:

Accessing Data of Non-Oracle Databases from Oracle

 

 

Introduction:

This document gives an overview of the Heterogeneous connections and the steps for configuration to access the data of non-oracle databases from Oracle database environment.

ACCESSING NON-ORACLE DATABASES FROM ORACLE

There may be some requirements to access the Data residing in different flavors of Databases like MS-SQL, Access, and Sybase from the Oracle database. This can be achieved with the help of creating Heterogeneous Services to connect to non-oracle flavors of the database and also integrate the data residing in them. The HS (Heterogeneous Services) is created with the help of the ODBC drivers for that particular flavor of the database (e.g. For MS-Access we need MS driver for Access). Generic Connectivity is implemented by using a Heterogeneous Services ODBC agent. An ODBC agent is included as part of the Oracle system and is installed in the same ORACLE_HOME and resides in the folder HS.

The following steps needs to be performed in order to configure a Heterogeneous connection in the Oracle Database:

1. Preparing the Non-oracle environment from where in the data needs to be integrated into the Oracle database.

2. Creation of the ODBC connection.

3. Test the ODBC drivers to ensure that connectivity is made to the non-Oracle database.

4. Ensure the Global_names parameter in Oracle database is set to False.

5. Configure the Heterogeneous services. This is done with the help of creating an initodbc.ora.

 7. Modify the Listener.ora and the TNSNAMES.ORA file so that to connect to the Database.

 8. Restart the Listener or START the listener if a new one has been created specific for the new connection.

9. Creation of the Database Link to connect to the HS connection.

10. Test the connection using thee DB Link.

Let us see the above steps in a brief fashion. Here we can consider of having a MS-Access Database from where in the data needs to be accessed into Oracle database:

Create the MS-Access database or copy the same to the local server where in the Database is hosted.

Create the ODBC connection. This can be done as follows:

Click on START à Control Panel à Administrative Tools and then open Data Sources (ODBC)

Click on the SYSTEM DSN Tab and then click on the ADD button.

Select Microsoft Access Driver (*.mdb) and then click on FINISH.

After that, enter the Data source name using which you would like to connect to the MS-Access Database (here in our case it’s ChryslerMDB). Also, select the MS-Access file by clicking on SELECT button and then click on OK to complete the configuration.

Check for the connectivity and confirm whether the ODBC connection is working fine.

Once this is done, Check for the GLOBAL_NAMES parameter in the Oracle database and make sure that it is FALSE. The following query can be used for the same :

********************************************************************************

SQL> select name,value from v$parameter where name like ‘global_names%’;

NAME

—————————————————————-

VALUE

—————————————————————————-

global_names

FALSE

SQL>

*******************************************************************************

Once this is done, we need to create the INIT.ORA file for the HSODBC connection that needs to be created. This needs to be done in %ORACLE_HOME%\ oracle\ora92\hs\admin folder. (In UNIX, it will be $ ORACLE_HOME\ oracle\ora92\hs\admin). Create a file saying init.ora file with the following contents : In our case the file should be named as initchryslermdb.ora

HS_FDS_CONNECT_INFO = chryslermdb

HS_FDS_TRACE_LEVEL = 0

In the above chryslermdb id the non-oracle database connection string or the DSN name that we created in Step 2.

Once this is done, the next step is to update the Listener.ORA file. We can use the same listener that is been used by the database to access the ODBC connection or a different one can also be configured. Here we will create a new listener for the ODBC connection.

Edit the Listener.ora file in ORACLE_HOME\ora92\network\admin folder and add the  following entries :

LISTENER_MSACCESS =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1522))

      )

    )

  )

SID_LIST_LISTENER_MSACCESS =

  (SID_LIST =

    (SID_DESC =

          (SID_NAME = PLSExtProc)

          (ORACLE_HOME = C:\oracle\ora92)

          (PROGRAM = extproc)

        )

              

        (SID_DESC =

              (ORACLE_HOME = C:\oracle\ora92)

              (SID_NAME = chryslermdb)

              (PROGRAM = HSODBC)

    )

  )

In the above SID_NAME ChryslerMDB is the SID name that we have given for non-oracle database (e.g. MS-ACCESS or MS-SQL). Once this is done start the Listener by executing the following command at the command line .

C:\Documents and Settings\impactadm\Desktop>lsnrctl start listener_msaccess

LSNRCTL for 32-bit Windows: Version 9.2.0.7 – Production on 15-AUG-2007 13

55

(c) Copyright 1998 Oracle Corporation.  All rights reserved.

Starting tnslsnr: please wait…

Then the following message will be seen :

STATUS of the LISTENER

————————

Alias                     listener_msaccess

Version                   TNSLSNR for 32-bit Windows: Version 9.2.0.7 – Pr

tion

Start Date                15-AUG-2007 13:05:56

Uptime                    0 days 0 hr. 0 min. 0 sec

Trace Level               off

Security                  OFF

SNMP                      OFF

Listener Parameter File   C:\oracle\ora92\network\admin \listener.ora

Listener Log File         C:\oracle\ora92\network\log\listener_msaccess.log

Services Summary…

  DEV           has 1 service handler(s)

  IMPACT02              has 1 service handler(s)

  IMPORT_access         has 1 service handler(s)

  PLSExtProc            has 1 service handler(s)

The command completed successfully

C:\Documents and Settings\impactadm\Desktop>

Note : If you are using the same listener, then just execute lsnrctl reload command at the command prompt. In case of windows machines, you can just go to SERVICES and restart the listener.

7. The next step is to update the TNSNAMES.ORA file.

Update the TNSNAMES.ORA file with the entries similar to the following :

#ACCESS DB for Chrysler

Chryslermdb.world =

  (DESCRIPTION =

    (ADDRESS_LIST =

        (ADDRESS =

          (COMMUNITY = tcp.world)

          (PROTOCOL = TCP)

          (Host = )

          (Port = 1522)

          )

    )

    (CONNECT_DATA = (service_name = chryslermdb)

    )

   (HS=ok)

  )

Once the entire configuration is done, check the connectivity by doing a tnsping command.

The next step is to create a Database link to this ODBC connection. This can be done by as follows:

Login to the database and execute the following query :

CREATE DATABASE LINK TEST1 CONNECT USING ‘CHRYSLERMDB.WORLD’;

Here TEST1 is the DB Link name and CHRYSLERMDB.WORLD is the name of the DSN entry in the TNSNAMES.ORA file.

Once this is done, we can do any operations on the Non-oracle database by executing the commands from Oracle database. For e.g.

SELECT * FROM [email protected];

Note : We have to make sure that the SID name in the TNSNAMES.ORA and LISTENER.ORA and the one in the USING clause of the Create Database Link matches, otherwise we will not be able to connect to the database and will get some errors.  Also, the init.ora file at the %ORACLE_HOME%\ oracle\ora92\hs\admin should be created with the correct SID failing which will lead to errors.

*****************************************************************************************

Converg Media

Anne Granger asked:

& lt; strong& gt; Oracle Enterprise Database11g & lt; /strong& gt; the Virtual has private DATA base function, around security characteristics to your data base. Virtual private DATA base or VPD is very helpful in situations, if they do not können data base functions and standard object privileges an application safety requirements. They können the Virtual private DATA base politics too simply or complex its, abhängig from the Höhe the security, which you need, over on the data base. & lt; br/& gt; & lt; br/& gt; They können a safe virtual private data base, around them reliably before unauthorized access. Virtual private data base becomes in the environment, where several users access to the same data base and only certain information to the Verfà ¼ gung each group should stand. The best way, around your virtual private data base is während the Durchfà ¼ hrung of safety features the production or the development. The Höhe of security is very high, like you your data base secures, instead of it with control another application. & lt; br/& gt; & lt; br/& gt; Best way is, around security with the opinions and tables of the data base. It is in such a way conceived that security is those, whether you access to the data is referred directly or indirectly. What is more? They können auà Ÿ erdem specify, Sicherheits Policies fà ¼ r a set of statements, eliminates the necessity for the development of safety guidelines individually fà ¼ r all Erklärungen. It is also möglich, several Maà Ÿ took fà ¼ to r a group of opinions, synonym or tables. & lt; br/& gt; & lt; br/& gt; A new feature admits as & lt; strong& gt; Masking column & lt; /strong& gt; private DATA base is, that the disadvantages of the column meaning also with Virtual. Main problem at the column level Virtual private DATA base security was that it beschränkt the lines, the data enthält fà ¼ r sensitive columns. But with the column masking the data of all of these lines is indicated, if have the sensitive column zero value. In this way further information to the Verfà ¼ gung stands fà ¼ for r authorized users and only the sensitive information is hidden. & lt; br/& gt; & lt; br/& gt; Virtual private DATA base knows more security, by her security in the column or line by the combination with VPD application context function. Gewährleistung of security with such deep level became also as & lt; strong& gt; feinkörniger ACCESS control & lt; /strong& gt; or & lt; strong& gt; FGAC & lt; /strong& gt; , where it a line or a column also separately. Whenever DML or DDL inquiry by the user of Oracle data base dynamically a ändert the inquiry, before data fetch or data manipulates. However, then the user becomes not à ¼ more ber the safety procedures on bake-ends, like it is transparency fà ¼ r the users and whenever he or she is authorized access to the data only the information. Darà ¼ more ber outside do not need you to its application code ändern, whenever you want, ändern you the Sicherheits Policies. à „ ndern it simply the Virtual private DATA base politics to gewähren or the entrance to a part of the data base. Irrelevantly the fact that it a source, around which connection to the data base, which heià Ÿ t, unabhängig of the fact whether you an application, SQL or to Web interface, does not give it Möglichkeit, by which security of your application können is infected. & lt; br/& gt; & lt; br/& gt; Different other kinds of VPD politics, like for example & lt; strong& gt; Static one, Shared-und context sensitive & lt; /strong& gt; are also used, around a better Maà Ÿ at security. They können context sensitive and static Maà Ÿ took, the several data base objects. Common Maà Ÿ took wà ¼ rde your overhead costs on RH-Ausfà ¼ hrung political functions repetitive with each retrieval query. & lt; br/& gt; & lt; br/& gt; & lt; strong& gt; & lt; /Strong& gt; & lt; br/& gt; & lt; br/& gt;

Domain Name Registration

Sagar Patil asked:

Whenever a valid SQL statement? processed Oracle has to decide how to retrieve the necessary data. This decision can be using one of two methods:? ? of? The rule-based optimizer (RBO) – this method? used if the server does not have statistics on the objects reported by the statement. This pi? non? supported by Oracle and desupported in future releases. Cost based optimizer (CBO) – this method? used if the statistics are present. The CBO checks several possible programs for implementation and select the one with the most? low cost, where the cost relates to system resources. An oracle CBO avr? a shot on whether an effect? of? of optimizer_index_cost_adj? of? of? of? parameter of the init oracle? set to the wrong value. I found this issue while working with the media that the client applications using SAP CRM / BW at the top of the database of oracle makes layers. The total size of the database was in excess of 4 tera Bytes.I took a SQL performing the most? defective for the analysis here. A "sight" VBAP_VAPMA "? based on the data VAPMA and VBAP, VBAP constantly in the segments listed above waiting. I could see that the optimizer_index_cost_adj is encouraging the exploration of index even if they are implicated pi? defective COMPLETE above exploration of the table. I did some calculations to prove "to point.SELECT; AEDAT", "AUART", "ERDAT", "ERNAM", "KONDM", "KUNNR", "MATKL", "MATNR" , "NETWR", "POSNR", "VBELN", "VKORG", "WAERK", "ZZAD_LINE_STATUS", "ZZCDO", "ZZCDO_P", "ZZKONDM_P" From SAPR3. " VBAP_VAPMA "WHERE" MANDT "=" to a0AND; AEDAT ">" of a1AND; AUART "=" to a2AND; KONDM "=" to a3AND; VKORG "=" to a4AND; ZZCDO "> =: Optimizer_index_cost_adj the value of a5Current? set to "regulatory 10th, Il? of? of Optimizer_index_cost_adj = 100? change the program execution by the index; VBAP ~ Z3 "to postpone the full optimizer Mode = CHOOSE DECLARATION of exploration. Optimizer_index_cost_adj = 10SELECT 2 313894 TO TABLE ACCESS BY INDEX ROWID SAPR3.VAPMA 1 49 .4 many nested 2 206 313893.8 TABLE OF ACCESS BY INDEX ROWID SAPR3.VBAP 3 K 174 K 312568.2 EXPLORATION OF THE RANGE OF CONTENTS SAPR3.VBAP ~ Z3 15 m. 100758 EXPLORATION OF THE RANGE OF CONTENTS SAPR3.VAPMA ~ Z01 1 3 (Oracle suggested the default value) Optimizer Mode = CHOOSE CHOSEN DECLARATION of Optimizer_index_cost_adj = 100 2 From 577409TABLE ACCESS INDEX ROWID SAPR3.VAPMA 1 49 4 cycles 2 206 577409 Nestled ACCESS FULL TABLE OF SAPR3.VBAP 3 K 174 K 564153 EXPLORATION OF THE RANGE OF CONTENTS SAPR3.VAPMA ~ Z01 1 3 Far? simple calculations on how Oracle is evaluating the costs of running here. Please note these are not precise formulations. Complete exploration of Table Approximate cost: 484,193 UnadjustedCost here are calculated as "IO CPU/1000 + + 1.5 * NetIO" but a formula would be simple (no of blocks / DB_FILE_MULTIBLOCK_READCOUNT) (no of blocks / DB_FILE_MULTIBLOCK_READCOUNT) = 3,873,549 blocks / 8 = 484193How for fall implementation cost: Increases DB_FILE_MULTIBLOCK_READCOUNT 32 + Reorg the table, the cost of "Scan" FULL; cadre? at 82,000 giving an increase of 5 times in an exploration of IO.Cost index: 149483 are registered valueIt are using a non-unique index; SAPR3.VBAP ~ Z3 "defined on the columns MANDT, ZZBU_DIR, ZZBU_EDITION.There are the only 160 distinct values on this index at 15.9 million files – "MANDT selections, ZZBU_DIR, ZZBU_EDITION by SAPR3.vbap" Move to increase the cost of exploration of the range blevel = + (black sheet for key Avg * (num_rows * selectivity?)) = (> 1,188,451 of real value); FTSWe that have governed Optimizer_index_cost_adj = 10 so the actual cost that we set? = 1,188,451 * 10/100 = 118845.1 that? 10% of the true value of overheadFinal cost index should include efforts to Previous = cost of access for data blocks + (Avg_data_blks_per_key * (Clustering_fact / blks totals in Table)) = 149483Conclusion: We must leave the Oracle Optimizer decide on a better path for the performance that forced to choose the indexes continuously. Putting default value for "optimizer_index_cost_adj" must be followed with the stats and the updated cost based optmizer depends very much on the right stats. http://OracleDbaSupport.co.uk? a blog of Sagar Patil, an independent adviser on Oracle with a great understanding of how the engine & database Oracle, Oracle Applications work together.

Website Hosting