Home » RDBMS Server » Performance Tuning » What info should be gathered while doing sql tuning? (Oracle 10.2.0.4, Redhat Enterprise Linux 5 64bit)
What info should be gathered while doing sql tuning? [message #538857] Tue, 10 January 2012 00:41 Go to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Hi, guys
What will you collect the info if you have several sql to tunning?

Here is a list I collect the info of the sql:
1. execution plan;
2. the table records related to this sql;
3. bind variable values via v$sql_bind_capture;


I want to make sure the infomation I collected are mostly sactified that latter tuning. So at least, dont give client bad impression.

Any suggestion is welcome. Thanks very much.
Re: What info should be gathered while doing sql tuning? [message #538859 is a reply to message #538857] Tue, 10 January 2012 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888.

Regards
Michel
Re: What info should be gathered while doing sql tuning? [message #538860 is a reply to message #538859] Tue, 10 January 2012 01:09 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Hi, Michel
According to this msg, some extra info should be added:
4. sql trace info ;
5. related objects' definition;
6. indexes' definition and indexes info on related objects

Thanks for providing the link. Smile
Re: What info should be gathered while doing sql tuning? [message #538863 is a reply to message #538860] Tue, 10 January 2012 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And statistics on these objects (number of rows, number of distinct keys in indexes...)

Regards
Michel
Re: What info should be gathered while doing sql tuning? [message #538870 is a reply to message #538863] Tue, 10 January 2012 01:43 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Cool. I am now know what to collect.
Thanks very much, Michel. Smile
Re: What info should be gathered while doing sql tuning? [message #538890 is a reply to message #538870] Tue, 10 January 2012 04:43 Go to previous messageGo to next message
Flyby
Messages: 145
Registered: March 2011
Location: Belgium
Senior Member
if possible also wanted/baseline performance
Re: What info should be gathered while doing sql tuning? [message #538925 is a reply to message #538890] Tue, 10 January 2012 07:11 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

I still have a questions.
The client has no test environment or the environment they have are much more differences with PROC DB.
How can I simulate the environment as the PROC DB(without using they're data will better) ?
Is that possible?

Thanks very much.
Re: What info should be gathered while doing sql tuning? [message #538939 is a reply to message #538925] Tue, 10 January 2012 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
They at least must have the same objects.
Then you can export the statistics and import them in their database.
See DBMS_STATS package.

Regards
Michel
Re: What info should be gathered while doing sql tuning? [message #538968 is a reply to message #538939] Tue, 10 January 2012 10:00 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Hi, Michel
So I got the ddls with related sql and these objects' statistics, sql tunning can be putted in my machine?
Re: What info should be gathered while doing sql tuning? [message #538972 is a reply to message #538968] Tue, 10 January 2012 10:42 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.
I forgot to mention, export and import also system statistics (same package).

Regards
Michel
Re: What info should be gathered while doing sql tuning? [message #539022 is a reply to message #538972] Tue, 10 January 2012 17:23 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

very cool tip. i'll try. Smile
Re: What info should be gathered while doing sql tuning? [message #539102 is a reply to message #538972] Wed, 11 January 2012 05:36 Go to previous messageGo to next message
LNossov
Messages: 288
Registered: July 2011
Location: Germany
Senior Member
Hello Michel

I'm very sceptical about SQL tuning without real runtime statistics. What should be a criteria for SQL tuning in this case?

Surely it is possible to check some tuning ideas on the test system. But not more.

Regards
Leonid
Re: What info should be gathered while doing sql tuning? [message #539103 is a reply to message #539022] Wed, 11 January 2012 05:41 Go to previous messageGo to next message
LNossov
Messages: 288
Registered: July 2011
Location: Germany
Senior Member
After import the statistics you have either to disable a job for gathering of stats or to lock the imported statistics. Otherwise they will be regathered.
Re: What info should be gathered while doing sql tuning? [message #539106 is a reply to message #538939] Wed, 11 January 2012 05:49 Go to previous messageGo to next message
LNossov
Messages: 288
Registered: July 2011
Location: Germany
Senior Member
... and the same parameter setting, that are relevant for the optimizer.
Re: What info should be gathered while doing sql tuning? [message #541215 is a reply to message #539106] Mon, 30 January 2012 00:49 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Hi, LNossov
Thanks for reminding. Smile
Re: What info should be gathered while doing sql tuning? [message #541223 is a reply to message #541215] Mon, 30 January 2012 01:11 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Hi, Michel
After trying to do with this way:

1. create a table for store statistics
SQL> EXEC dbms_stats.create_stat_table('SCOTT','SCOTT_STATS','USERS');

PL/SQL procedure successfully completed.


2. export the statistics to the created table
SQL> select count(*) from scott.t;                  

  COUNT(*)
----------
   3225728

11:12:01 SQL>  exec dbms_stats.export_table_stats('SCOTT','T',NULL,'SCOTT_STATS','SCOTT_STATS1',TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06



3. export the table
[oracle@rac1 ~]$ exp scott/tiger tables=scott_stats file=scott_stats.dmp log=scott_stats.log

Export: Release 10.2.0.4.0 - Production on Thu Jan 19 11:14:46 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                    SCOTT_STATS         15 rows exported
Export terminated successfully without warnings.



4. get the related ddl
SQL> select dbms_metadata.get_ddl('TABLE','T','SCOTT') from dual;

DBMS_METADATA.GET_DDL('TABLE','T','SCOTT')
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."T"
   (    "OWNER" VARCHAR2(30),
        "OBJECT_NAME" VARCHAR2(128),
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE,
        "LAST_DDL_TIME" DATE,
        "TIMESTAMP" VARCHAR2(19),

DBMS_METADATA.GET_DDL('TABLE','T','SCOTT')
--------------------------------------------------------------------------------
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"


SQL> select dbms_metadata.get_ddl('INDEX','SCOTT_T_IND','SCOTT') FROM dual;

DBMS_METADATA.GET_DDL('INDEX','SCOTT_T_IND','SCOTT')
--------------------------------------------------------------------------------

  CREATE INDEX "SCOTT"."SCOTT_T_IND" ON "SCOTT"."T" ("OBJECT_ID", "OBJECT_NAME")

  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"



5. import the table to the target db
F:\dumpfile>imp scott/tiger file=scott_stats.dmp log=scott_imp.log


6. create related object with fetched ddl
SQL>   CREATE TABLE "SCOTT"."T"
  2     (    "OWNER" VARCHAR2(30),
  3          "OBJECT_NAME" VARCHAR2(128),
  4          "SUBOBJECT_NAME" VARCHAR2(30),
  5          "OBJECT_ID" NUMBER,
  6          "DATA_OBJECT_ID" NUMBER,
  7          "OBJECT_TYPE" VARCHAR2(19),
  8          "CREATED" DATE,
  9          "LAST_DDL_TIME" DATE,
10          "TIMESTAMP" VARCHAR2(19),
11          "STATUS" VARCHAR2(7),
12          "TEMPORARY" VARCHAR2(1),
13          "GENERATED" VARCHAR2(1),
14          "SECONDARY" VARCHAR2(1)
15     ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
16    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
17    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
18    TABLESPACE "USERS";

SQL> CREATE INDEX "SCOTT"."SCOTT_T_IND" ON "SCOTT"."T" ("OBJECT_ID", "OBJECT_NAME");




7. import the stastics from the table
SQL> exec dbms_stats.import_table_stats('SCOTT','T',NULL,'SCOTT_STATS','SCOTT_STATS1', TRUE);



Compare with the target and the source db's execution plan, I found a little bit differences.



sql 1 in product:
SQL> select object_id, object_name from t where object_id < 100;

6272 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3140600170

--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |  5227 |   153K|    32   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| SCOTT_T_IND |  5227 |   153K|    32   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_ID"<100)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        441  consistent gets
         21  physical reads
          0  redo size
     109627  bytes sent via SQL*Net to client
       5090  bytes received via SQL*Net from client
        420  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       6272  rows processed



sql1 in my db:
SQL> select object_id, object_name from t where object_id < 100;

未选定行


执行计划
----------------------------------------------------------
Plan hash value: 3140600170

--------------------------------------------------------------------------------

| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |             |  5227 |   153K|     0   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| SCOTT_T_IND |  5227 |   153K|     0   (0)| 00:00:01 |

--------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_ID"<100)


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        341  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed



I notice that the Statistics session will different with product and the cost in my db is always 0.

I remember that you remind me the export the system statistics
Quote:

I forgot to mention, export and import also system statistics (same package).


And what's kind of system statistics need to be exported and imported?
Will this action will fix the problem I mentioned?

Sorry for a little bit delay respond.

Thanks very much.





Re: What info should be gathered while doing sql tuning? [message #541259 is a reply to message #541223] Mon, 30 January 2012 04:03 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
You're obviously running with different nls settings. This may affect queries.
Re: What info should be gathered while doing sql tuning? [message #541357 is a reply to message #541259] Mon, 30 January 2012 19:22 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Hi, cookiemonster
I have tried with both the same nls settings.

Nls setting on myrac1:
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
myrac1

SQL> select property_name, property_value from database_properties where property_name like '%NLS%'
  2  order by 1;

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ----------------------------------------
NLS_CALENDAR                   GREGORIAN
NLS_CHARACTERSET               WE8ISO8859P1
NLS_COMP                       BINARY
NLS_CURRENCY                   $
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_DUAL_CURRENCY              $
NLS_ISO_CURRENCY               AMERICA
NLS_LANGUAGE                   AMERICAN
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CHARACTERSET         AL16UTF16

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ----------------------------------------
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NUMERIC_CHARACTERS         .,
NLS_RDBMS_VERSION              10.2.0.4.0
NLS_SORT                       BINARY
NLS_TERRITORY                  AMERICA
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR

20 rows selected.







NLS settings on testdb:
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
testdb

SQL> select property_name, property_value from database_properties where property_name like '%NLS%'
  2  order by 1;  

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ----------------------------------------
NLS_CALENDAR                   GREGORIAN
NLS_CHARACTERSET               WE8ISO8859P1
NLS_COMP                       BINARY
NLS_CURRENCY                   $
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_DUAL_CURRENCY              $
NLS_ISO_CURRENCY               AMERICA
NLS_LANGUAGE                   AMERICAN
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CHARACTERSET         AL16UTF16

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ----------------------------------------
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NUMERIC_CHARACTERS         .,
NLS_RDBMS_VERSION              10.2.0.4.0
NLS_SORT                       BINARY
NLS_TERRITORY                  AMERICA
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR

20 rows selected.


And both dbs located on the same machine with the same user.
But when I fetch the execution plan again,
execution plan on myrac1:
SQL> select object_id, object_name from t where object_id < 100;

6272 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3140600170

--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |  5227 |   153K|    32   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| SCOTT_T_IND |  5227 |   153K|    32   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_ID"<100)


Statistics
----------------------------------------------------------
        293  recursive calls
          0  db block gets
        483  consistent gets
         23  physical reads
          0  redo size
     109627  bytes sent via SQL*Net to client
       5090  bytes received via SQL*Net from client
        420  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
       6272  rows processed


execution plan on testdb:
SQL> select object_id, object_name from t where object_id < 100;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3140600170

--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |  5227 |   153K|    32   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| SCOTT_T_IND |  5227 |   153K|    32   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_ID"<100)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        395  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> 


The Statistics is not correct.But I have alreadly import the statistics into the testdb. Is there anything I am missing?
Re: What info should be gathered while doing sql tuning? [message #541648 is a reply to message #541223] Wed, 01 February 2012 10:09 Go to previous messageGo to next message
LNossov
Messages: 288
Registered: July 2011
Location: Germany
Senior Member
I don't see any differencies in your execution plans. Only in your runtime stats, that depends on real data not on optimizer statistics.
Re: What info should be gathered while doing sql tuning? [message #542039 is a reply to message #541648] Sat, 04 February 2012 02:14 Go to previous message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Ok. I got it. Smile
Thanks very much.

[Updated on: Sat, 04 February 2012 02:17]

Report message to a moderator

Previous Topic: Materialized view to improve Search queries
Next Topic: comparing SQL plans between 2 DB's
Goto Forum:
  


Current Time: Wed Oct 01 23:31:15 CDT 2014

Total time taken to generate the page: 0.11052 seconds